Introduction to Database and SQL

Introduction to Database and SQL is an undergraduate course, listed as DSC 301.
Lectures and homework centers around an online store (products, manufacturers, customers, and orders). Course materials contain optional project activity ideas and materials.

Course Description

This course is an introduction to Database Design and Structured Query Language (SQL). Students are exposed to relational databases including table design, relationships, dependencies, and normalization forms. Additionally, the course will cover data modeling using Entity-Relation (ER) models. Data models found in business, medicine, biology, and science will be considered.

This course is an introduction to Database Design and Structured Query Language (SQL). Students will be exposed to relational databases including table design, relationships, dependencies, and normalization forms. Additionally, the course will cover data modeling using Entity-Relation (ER) models. Data models found in business, medicine, biology, and science will be considered.

DSC 301 is required for Data Science major and also counts toward data science and computer science minors. DSC 301 satisfies an Advanced Studies requirement in the Core.

Learning Outcomes

1. Recognize the utility of databases in the modern era
2. Define and identify the main characteristics of relational databases
3. Select, insert, update, and delete database records using SQL
4. Summarize data using SQL aggregate functions
5. Combine and manipulate data from multiple related tables using various table joins with one-to-one, one-to-many, and many-to-many relationships
6. Create data models to aid in the design process and to communicate design
7. Design database(s) that ensure data integrity and consistency by applying normalization rules
8. Write efficient queries, optimize a database, and apply performance tuning techniques including: creating indexes and writing stored procedures.
9. Create stored programs, triggers, events, transactions, and views
10. Identify big data and big data technologies (distributed storage, NoSQL, Hadoop)

Upon successful completion of this course, students will be able to:

1. Describe the structure of relational databases
2. Select, insert, update, and delete records in database
3. Ensure data integrity and consistency
4. Apply normalization forms to database tables
5. Summarize rows of data using aggregate functions, and segment aggregations according to specified variables
6. Combine and manipulate data from multiple tables across a database

Topics

The course will cover

1. Relational database management systems (RDBMS)
2. Table design, including primary/foreign keys, and referential data integrity
3. Tables and Views
4. Table Relationships (inner and outer joins)
5. Entity-Relationship (ER) models
6. Structured Query Language (SQL) Selecting, inserting, updating, and deleting data Summarizing and grouping data Filtering and sorting data
7. SQL Performance Tuning
8. Database security and user management
9. Distributed storage, NoSQL, Hadoop, MapReduce

** Notes on some topics may be missing from repo.

Assessment