What this exam tests and how to study it

Read this before anything else

This course has one job: get you through the COMP 378 final exam. Every lesson, callout, and runnable exercise that follows is reverse-engineered from that exam. Spend nine minutes here building a map of the test, and every later lesson tells you *why* it exists.

The final is 100 marks, split into two parts:

- Part A — 15 multiple-choice questions (30 marks, 2 each). These spread across all 12 chapters and test a single best definition: *what is a thin client*, *which rule says no primary-key attribute may be null*, *which concurrency approach is optimistic*. Broad but shallow — you win these by knowing the one precise definition, not by reasoning. - Part B — 8 short-answer questions (70 marks). This is where the exam is actually decided, and it is not shallow. You will be asked to *produce* artifacts and *justify* choices, not recognize a definition.

The three skills that carry 70% of the marks

The Part B short-answer marks are dominated by three skills. If you can do these three cold, you pass.

| Skill | Where it shows on the exam | Why it dominates | |---|---|---| | Write SQL queries (SELECT / JOIN / GROUP BY / HAVING / subquery / aggregate / set-ops) | Part B Q3 alone is six sub-queries, plus Q4a; Chapters 5 & 6 are almost entirely *write-the-query* problems | The single highest-yield skill — one question is six queries | | Normalize to 3NF (find functional dependencies → spot partial / transitive deps → decompose) | Part B Q1 is a full 3NF transform | Guaranteed — a complete decomposition problem appears every sitting | | ER / EER modeling (entities, relationships, cardinality, weak entities, associative entities, super/subtypes) | Part A Q2, Q4, Q5 and Part B Q2 (list entities / relationships / associative / weak) | Tested as both MCQ recognition and structured production |

The remaining marks reward broad definitional coverage: every chapter contributes a MCQ or two, and the other short-answers are conceptual (recovery techniques, three-tier architecture, data marts + ETL, NoSQL types + a data-quality program). So your study must be wide — skip no chapter — but deep on SQL, normalization, and modeling. That is exactly how the modules are weighted: Modules 4, 5, and 6 (normalization + SQL) are the course's center of gravity.

The study loop: recall → produce → justify

MCQs reward one habit; short-answers reward a different, harder one. Train both, in this order, on every topic:

1. Recall — pull the precise definition back *cold*, in your own words. (Wins Part A. The recall Q→A items and checkpoint gates in every lesson drill exactly this.)
2. Produce — actually *make* the artifact: write the query, decompose the relation to 3NF, list the entities and relationships for a narrative. Recognizing a correct query is worthless on Part B; you must compose one from a blank editor. (This is why the SQL lessons hand you a real database and a write-mode editor instead of a multiple-choice list.)
3. Justify — explain *why*: why this is an associative entity and not a weak one, why a LEFT OUTER JOIN (not INNER) is needed to show a work-center with zero products, why this dependency is transitive. Part B questions routinely end in "...and explain" or "...justify your answer."

Worked example of the loop, on one fact. Take *entity integrity*. Recall: "no part of a primary key may be null." Produce: write it as a constraint — a PRIMARY KEY declaration that the database enforces. Justify: if a PK attribute were null, a row could not be uniquely identified, so the relation could hold indistinguishable duplicates — the rule exists to guarantee unique addressability of every row. Notice the same fact answers a Part A MCQ (recall), a Part B "write the CREATE TABLE" (produce), and a Part B "why" (justify). Most exam facts work this way, which is why the loop is the whole method.

One platform reality: you practice on real Postgres

Every runnable exercise in this course executes against a real PostgreSQL database in your browser — the registrar, sales, HR/projects, and (later) the data-warehouse star schema, pre-loaded with rows. This is the single biggest lever for exam transfer: when the practice tables *are* the exam's tables, your query practice carries over one-to-one.

There is one seam to know about now. The COMP 378 textbook and lecture notes lean Oracle dialect, but the platform runs Postgres. The exam accepts standard SQL, but your runnable answers will *error* if you type Oracle idioms. The most common translations:

| Textbook / Oracle | Postgres (what you type here) | |---|---| | MINUS | EXCEPT | | VARCHAR2(n) | VARCHAR(n) | | SYSDATE | CURRENT_DATE / CURRENT_TIMESTAMP | | ROWNUM <= 10 | LIMIT 10 | | MODIFY column | ALTER COLUMN column TYPE |

You will also meet two join *styles* in exam answers: the legacy comma-join + WHERE (FROM a, b WHERE a.id = b.aid) and modern JOIN ... ON. Learn to *read* the comma-join (exam keys use it constantly) but *write* the explicit JOIN ... ON — it is clearer and harder to get wrong. Both produce identical results.