Memra
academic · intermediate

COMP 378 — Introduction to Database Management

Model it, normalize it, and query it in real SQL — exam-ready

A full database course: design with the E-R and enhanced E-R models, decompose to third normal form, and write real SQL — from single-table SELECTs to multi-table joins, subqueries, set operations, and views — executed live against real Postgres in your browser. Covers transactions, indexing, data warehousing, and big data, aligned to the COMP 378 exam (query writing, 3NF decomposition, and E-R modeling).

Start course →
0 / 61 lessons

Orientation & How to Pass COMP 378

  1. What this exam tests and how to study it 9 min

The Database Environment & Development Process

  1. Data, Information & Metadata 9 min
  2. File Processing vs. the Database Approach 11 min
  3. DBMS, Data Models & the Relational Database 10 min
  4. Schemas & the Three-Schema (ANSI) Architecture 11 min
  5. The Database Development Process: SDLC, Prototyping, Agile & Operational vs. Informational 12 min

Conceptual Data Modeling (the E-R Model)

  1. Business rules & good data definitions 9 min
  2. Entities: strong vs weak, type vs instance 11 min
  3. The attribute taxonomy 11 min
  4. Relationships: degree (unary/binary/ternary) & cardinality 12 min
  5. Associative entities (resolving M:N) 11 min
  6. Modeling workshop: narrative → entities, relationships, associative & weak 12 min

The Enhanced E-R Model (EER)

  1. Supertypes, Subtypes & Inheritance 11 min
  2. Specialization vs Generalization 10 min
  3. Constraints: Completeness & Disjointness 12 min
  4. Entity Clusters & Universal/Packaged Data Models 11 min

Logical Design, the Relational Model & Normalization

  1. The Relational Model: Relations, Keys & Nulls 10 min
  2. Integrity Constraints: Domain, Entity & Referential 11 min
  3. Well-Structured Relations & the Three Anomalies 9 min
  4. Transforming EER Diagrams into Relations (the 7 Rules) 12 min
  5. Functional Dependencies & Determinants 10 min
  6. Normal Forms: 1NF → 2NF → 3NF 11 min
  7. Normalization Workshop: Decompose to 3NF 12 min
  8. From 3NF to a Running Table: CREATE TABLE with Constraints 12 min

SQL — Defining & Querying One Table

  1. The SQL Environment: DDL / DML / DCL, Catalog & Schema 9 min
  2. Data Types, CHECK, DEFAULT & Schema Evolution (ALTER / DROP) 11 min
  3. Modifying Data: INSERT / UPDATE / DELETE 10 min
  4. SELECT … FROM … WHERE: Projection, Filtering, DISTINCT & Predicates 12 min
  5. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX (and the three COUNTs) 11 min
  6. GROUP BY & HAVING: Per-Category Aggregates 11 min
  7. Presenting Results: ORDER BY, LIMIT/OFFSET & Column Aliases 9 min
  8. Indexes & Query Performance (Intro) 10 min

Advanced SQL: Multi-Table, Subqueries, Views & Programmability

  1. Joins I: INNER JOIN, Equi-Join, Natural Join (and the Comma-Join) 11 min
  2. Joins II: OUTER (LEFT/RIGHT/FULL), SELF JOIN & CROSS JOIN 12 min
  3. Multi-Table Aggregation: 3–4 Table Joins with GROUP BY 11 min
  4. Non-Correlated Subqueries: IN / NOT IN 11 min
  5. Correlated Subqueries & EXISTS / NOT EXISTS 12 min
  6. Set Operations: UNION, INTERSECT & EXCEPT (MINUS) 10 min
  7. Derived Tables & CASE Expressions 11 min
  8. Views (Dynamic vs. Materialized) & WITH CHECK OPTION 11 min
  9. Triggers, Stored Procedures & SQL/PSM 10 min

Databases in Applications

  1. Client/Server & Multi-Tier Architecture 11 min
  2. Middleware, APIs & Database Access from Code 10 min
  3. Transactions & ACID 12 min
  4. Concurrency Control I: Locking (Pessimistic) 11 min
  5. Concurrency Control II: Versioning (Optimistic) 9 min
  6. Database Security in Applications 11 min

Physical Database Design & Infrastructure

  1. Physical Design: Fields, Data Types, Denormalization & Partitioning 11 min
  2. File Organizations & Indexing (deep) 12 min
  3. Backup & Recovery 11 min
  4. Data Dictionaries, Repositories & Cloud (IaaS/PaaS/SaaS/DBaaS) 9 min

Data Warehousing & Dimensional Modeling

  1. Why Warehouse? Operational vs. Informational & the Four Defining Traits 10 min
  2. Warehouse Architectures & Data Marts 11 min
  3. The Star Schema: Facts, Dimensions, Grain & Surrogate Keys 12 min
  4. Data Integration & the ETL Process 11 min

Big Data, Analytics & Database Administration

  1. Big Data: the Vs, Schema-on-Read & the Hadoop Ecosystem 11 min
  2. NoSQL Databases: the Four Types, ACID vs. BASE & CAP 11 min
  3. Analytics: Descriptive, Predictive & Prescriptive 10 min
  4. OLAP & Data Mining 12 min
  5. Data Administration vs. Database Administration 9 min
  6. Data Quality, Governance & Master Data Management 12 min
NORMAL ~/memra/learn/comp-378 utf-8 LF