Introduction to Database Management Systems (DBMS)
A foundational course designed to teach you how to model, build, and interact with modern databases. Learn to design efficient schemas using ER diagrams, write standard ANSI SQL queries, and understand core concepts like normalization, ACID properties, and the role of NoSQL.
The Data Dilemma: File Systems vs. DBMS Identify the limitations and redundancies of traditional file-based data storage. Define what a Database Management System (DBMS) is and its core advantages. Explain the concept of data independence. Introduction to the Relational Model Define the components of a relational database: tables, rows (tuples), and columns (attributes). Explain the purpose of Primary Keys for unique identification. Understand how Foreign Keys establish relationships between tables. Database Architecture & Modern Cloud DBs Describe the standard client-server architecture of a database. Differentiate between self-hosted databases and managed cloud databases. Identify 2026 modern database practices, such as serverless database environments. Exploring Relational Database Engines Compare popular RDBMS engines like PostgreSQL, MySQL, and SQLite. Understand the concept of ANSI standard SQL and dialect differences. Identify the appropriate database engine for various business sizes. Entities, Attributes, and Relationships Define entities and differentiate them from attributes. Identify the appropriate attributes for a given business entity. Draft a basic Entity-Relationship (ER) diagram. Mapping Cardinality and Constraints Determine One-to-One (1:1) and One-to-Many (1:N) relationships. Determine Many-to-Many (M:N) relationships and the need for junction tables. Apply cardinality constraints to ER diagrams. Translating ER Diagrams to Relational Schemas Convert visual entities into database table structures. Map physical foreign keys to resolve visual relationships. Resolve Many-to-Many relationships into physical junction tables. Applied Data Modeling Challenge Synthesize ER modeling skills to design a complete schema from scratch. Validate the schema against business requirements. Identify missing relationships or orphaned entities. The Cost of Bad Design: Data Anomalies Identify insertion, update, and deletion anomalies in poorly designed tables. Explain how data redundancy impacts database performance and integrity. Define the overarching goal of database normalization. First Normal Form (1NF) Define the rules of First Normal Form (1NF). Eliminate repeating groups and arrays from table columns. Ensure all attributes contain atomic (indivisible) values. Second Normal Form (2NF) Define the rules of Second Normal Form (2NF). Identify composite primary keys. Resolve partial dependencies by moving data to new tables. Third Normal Form (3NF) Define the rules of Third Normal Form (3NF). Identify and remove transitive dependencies. Apply 1NF, 2NF, and 3NF to a denormalized dataset. Introduction to SQL and the SELECT Statement Write basic SELECT queries to extract data from a single table. Select specific columns versus selecting all columns (*). Use column aliases (AS) to format output headers. Filtering Data with WHERE Filter rows based on specific conditions using the WHERE clause. Combine multiple conditions using AND, OR, and NOT. Use IN and LIKE operators for pattern matching. Sorting and Limiting Results Sort query results using the ORDER BY clause (ASC/DESC). Restrict the number of returned rows using LIMIT or FETCH FIRST. Combine WHERE, ORDER BY, and LIMIT in a single query. Modifying Data: INSERT, UPDATE, DELETE Add new records to a table using the INSERT INTO statement. Modify existing records safely using the UPDATE statement. Remove records using the DELETE statement. Aggregating Data Apply core aggregate functions: COUNT, SUM, and AVG. Determine minimum and maximum values using MIN and MAX. Understand how NULL values interact with aggregate functions. Grouping Results with GROUP BY Group aggregated data into categories using the GROUP BY clause. Filter grouped data using the HAVING clause. Differentiate between the execution order of WHERE and HAVING. Connecting Data with INNER JOIN Explain the concept of joining relational tables. Write INNER JOIN queries to extract matching data from two or more tables. Use table aliases to resolve column name ambiguity. Outer Joins and Modern JSON Queries Differentiate between LEFT, RIGHT, and FULL outer joins. Write LEFT JOIN queries to find unmatched records. Extract values from JSON data types using modern SQL functions. Understanding Database Transactions Define a database transaction and its purpose in business logic. Use COMMIT to permanently save transaction changes. Use ROLLBACK to undo changes in case of an error. The ACID Properties Define Atomicity and Consistency in the context of database states. Explain Isolation and how it prevents concurrent transaction conflicts. Understand Durability and data persistence after system failures. Introduction to Database Indexes Explain how indexes improve data retrieval speed. Identify the trade-offs of indexing, including slower write operations. Recognize when to apply indexes to a column. Beyond Relational: Introduction to NoSQL Compare the strict schema of SQL databases with the flexible schema of NoSQL. Identify common NoSQL paradigms (Document, Key-Value, Graph). Evaluate business scenarios to choose between SQL and NoSQL databases.