Memra

academic · intermediate · ≈ 8 h · Athabasca University

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).

0 / 61 lessons
Reviews stop being scheduled for after it.

up next

What this exam tests and how to study it

9 m

Start course

Orientation & How to Pass COMP 378

0 / 1 lesson
  1. What this exam tests and how to study it 9 m

The Database Environment & Development Process

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

Conceptual Data Modeling (the E-R Model)

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

The Enhanced E-R Model (EER)

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

Logical Design, the Relational Model & Normalization

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

SQL — Defining & Querying One Table

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

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

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

Databases in Applications

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

Physical Database Design & Infrastructure

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

Data Warehousing & Dimensional Modeling

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

Big Data, Analytics & Database Administration

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