Schema Design and Normalization Lab

Retrieval Prompts

1. Define a functional dependency X -> Y in one sentence.
2. State the defining property of 1NF, 2NF, 3NF, and BCNF in one sentence each.
3. State what makes a decomposition lossless and what makes it dependency-preserving.
4. State the translation rules for an N:M relationship from ER to tables, in one sentence.
5. Explain in one sentence what makes a weak entity weak and how it is translated.

Normalize This Schema

You are given a single table that a well-meaning junior dumped in:

student_enrollment(
    student_id, student_name, student_email,
    course_code, course_title, instructor, instructor_office,
    semester, grade
)

And these facts hold:

• student_id -> student_name, student_email
• course_code -> course_title, instructor
• instructor -> instructor_office
• (student_id, course_code, semester) -> grade

Tasks:

1. Identify all candidate keys.
2. State which normal form the table is currently in and which update anomalies it suffers.
3. Decompose to 3NF (and to BCNF if different). Show each decomposition step and cite the FD that triggered it.
4. Write CREATE TABLE statements for the 3NF result, with primary keys and foreign keys.
5. For each table, briefly justify that it is in 3NF.

Design from Requirements

Design a schema for a ride-share dispatcher:

• riders request rides from one location to another
• drivers accept rides; a ride has exactly one driver and one rider
• riders and drivers rate each other after a ride (1-5 stars, optional comment)
• drivers earn badges (e.g., "100 rides," "5-star week"); same badge can be earned multiple times with a date
• payment is captured per ride, with an amount, currency, method, and status

Deliverables:

1. An ER diagram (Mermaid or plain text).
2. Mechanical translation to relational schema (list the tables and their columns).
3. CREATE TABLE statements in PostgreSQL with PRIMARY KEY, FOREIGN KEY (with explicit ON DELETE), NOT NULL, UNIQUE, and CHECK constraints.
4. One UNIQUE constraint and one CHECK constraint that each catch a real business rule, with a sentence explaining each.
5. A justification that the final schema is in 3NF.

Denormalization Decision

For the ride-share schema, identify two candidate denormalizations that could be justified under read pressure (e.g., a materialized driver_stats view or a ride.rider_name copy). For each:

• State the specific read query it accelerates.
• State the specific anomaly risk it introduces.
• State how you would keep it consistent (trigger, materialized view + refresh cadence, application write path).
• State the rollback plan if it becomes a bug source.

Evidence Check

This page is complete only if, given any unnormalized schema and a set of FDs, you can:

• identify candidate keys and current normal form
• decompose to 3NF/BCNF with losslessness and dependency-preservation checks
• write the final DDL with appropriate constraints
• given a read pressure, choose denormalization only when the tradeoff is written down

Integrated Modeling Worksheet

Turn this requirement into a relational design:

A restaurant takes reservations for tables. A guest can have many reservations. A reservation may request multiple tables for large parties. Servers are assigned to table sections by shift. Orders belong to reservations or walk-in tables. Each order has items, quantities, prices at time of sale, and payment status.

Tasks:

1. Extract entities, attributes, relationships, cardinalities, and optionality.
2. Draw an ER diagram and mark weak or associative entities.
3. Convert the ER model to relational schemas with primary keys, foreign keys, uniqueness constraints, and checks.
4. Identify at least five functional dependencies.
5. Normalize one intentionally bad table into 3NF and explain the anomaly removed by each split.

Evidence check: include the requirements trace. Every table must point back to a requirement or a normalization decision.