Introduction to Relational Databases

Overview

Database management systems are a crucial part of most large-scale industry and open-source systems. This course will introduce you to important concepts of database systems and design. We will learn what relational databases are, what they are used for, the theory underlying their design, and how to query and modify a database using the declarative SQL language. At the end of the course, you will be able to: - Describe what relational databases are, and how they are used. - Master the Relational Database Model. - Demonstrate proficiency in formal relational database theory. - Demonstrate comprehensive SQL skills. - Apply database knowledge to practical problems. Software Requirements: Jupyter Notebooks, SQL

Syllabus

Module 1: Introduction to Relational Databases

Welcome to Introduction to Relational Databases! In this course, we will cover the foundational topics of databases including Database Overview, Relational Algebra, and SQL. This module introduces relational databases, their components, and real-world applications. You will explore database schemas (relations, attributes, keys), and develop an understanding of essential concepts like records, data retrieval/modification, and primary/foreign keys. Real-world examples support practical understanding and schema design.

Module 2: Relational Algebra

This module explores the fundamental concepts and operations of relational algebra and the theoretical foundation of relational databases. Students will first learn the core elements of relational algebra, such as relations, tuples, and operations. The module then delves into basic operations (selection and projection) and advanced operations techniques (joins and division). The module also covers composite and aggregation operations and touches on query optimization techniques. By the end, students will have a strong theoretical understanding to work with relational databases effectively.

Module 3: Introduction to SQL (Foundations)

This module introduces SQL (Structured Query Language), the industry-standard language for managing relational databases. Students will first learn the role of SQL and its categories, then dive deeper into Data Definition Language (DDL) for creating/modifying schemas and finally practice Data Manipulation Language (DML) to query and modify data using the Chinook database.

Module 4: Complex SQL

This module builds on foundational SQL skills, introducing advanced querying techniques for complex data retrieval and analysis. Students will learn subqueries (correlated and non-correlated), multi-table joins (INNER, OUTER, CROSS, SELF), aggregations with GROUP BY and HAVING, and set operations (UNION, INTERSECT, EXCEPT). The module also covers derived tables, common table expressions (CTEs), views, indexing, and database access control.

Summative Course Assessment

This module contains the summative course assessment that has been designed to evaluate your understanding of the course material and assess your ability to apply the knowledge you have acquired throughout the course.