Database Design & Normalization

Before normalization makes sense, you need a clear picture of what a relation is and what can go wrong without careful design.

A relation (what SQL calls a table) is a set of tuples, each of the same type. Every tuple is a row; every attribute is a typed column. The schema of a relation is its name plus the list of attributes and their domains: Student(student_id, name, email, major). The schema is a contract — it constrains what data can exist.

The central promise of the relational model is that if you design schemas correctly and use integrity constraints, the database will always reflect a consistent snapshot of reality. Violations of this promise manifest as three classic anomalies:

1. Update anomaly: A fact is stored redundantly. Updating it in one row without updating all other rows leaves contradictory data. Example: storing a department's building in every employee row. If the department moves, you must update every row — miss one and the database disagrees with itself.

2. Insertion anomaly: You cannot record a fact without also recording an unrelated fact. Example: in a combined Employee(emp_id, name, dept_id, dept_name, building) table, you cannot add a new department until it has at least one employee.

3. Deletion anomaly: Deleting one fact inadvertently destroys another. Deleting the last employee in a department deletes all knowledge of that department.

These anomalies are the symptoms. Normalization is the cure — but to prescribe the cure, we first need a precise diagnosis.

-- A badly designed table exhibiting all three anomalies:
CREATE TABLE enrollment_bad (
  student_id   INT,
  student_name VARCHAR(100),
  course_id    VARCHAR(10),
  course_name  VARCHAR(100),
  instructor   VARCHAR(100),
  instructor_office VARCHAR(20)
);
-- Problem: instructor_office depends only on instructor,
-- not on (student_id, course_id). Updating one row's
-- instructor_office without touching others => contradiction.

Before writing a single CREATE TABLE, you should model the domain using an Entity-Relationship (ER) diagram, introduced by Peter Chen in 1976. ER modeling is a conceptual-level tool: it captures what needs to be stored before worrying about how to store it in tables.

Entities are distinguishable objects: Student, Course, Professor. Each entity has attributes: Student has student_id, name, email. One attribute (or a minimal set) is designated the primary key — the unique identifier.

Relationships are associations between entities: a Student enrolls in a Course; a Professor teaches a Course. Relationships have cardinality constraints:

• One-to-one (1:1): Each person has at most one passport.
• One-to-many (1:N): A department has many employees; each employee belongs to one department.
• Many-to-many (M:N): Students enroll in many courses; courses have many students.

Relationships can themselves have attributes: Enrollment(student, course, grade) — the grade belongs to the relationship, not to either entity alone.

Weak entities have no key of their own and depend on a strong entity for identification. Example: Section(section_number, semester) is a weak entity identified only relative to a Course.

Participation constraints indicate whether an entity must participate in a relationship (total participation, drawn as a double line) or may (partial participation, single line).

The standard translation of ER to relational schema follows mechanical rules:

• Each strong entity becomes a table with its attributes.
• Each many-to-many relationship becomes a separate table whose primary key is the combination of the two entity keys.
• Each 1:N relationship is usually represented by a foreign key in the "many" side table pointing to the "one" side.
• Weak entities become tables with a composite key: own partial key + owner's primary key.

-- ER -> SQL translation example
-- Entities
CREATE TABLE Student (
  student_id  SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(200) UNIQUE NOT NULL
);

CREATE TABLE Course (
  course_id   VARCHAR(10) PRIMARY KEY,  -- e.g. '6.033'
  title       VARCHAR(200) NOT NULL,
  credits     INT NOT NULL CHECK (credits > 0)
);

-- M:N relationship becomes its own table
CREATE TABLE Enrollment (
  student_id  INT REFERENCES Student(student_id),
  course_id   VARCHAR(10) REFERENCES Course(course_id),
  grade       CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

Functional dependencies (FDs) are the mathematical heart of normalization. They make precise the intuition that "this attribute is determined by that attribute".

Definition: Given a relation schema R with attributes, a functional dependency X → Y (read: X functionally determines Y) holds if, in every valid instance of R, whenever two tuples agree on X, they also agree on Y. Formally: ∀ t₁, t₂ ∈ r: t₁[X] = t₂[X] ⟹ t₁[Y] = t₂[Y].

FDs are semantic constraints about the real world, not just observations about current data. The FD student_id → name holds because a student ID uniquely identifies a student — it's a design decision, not a coincidence.

Armstrong's Axioms are a sound and complete inference system for FDs:

1. Reflexivity: If Y ⊆ X, then X → Y. (trivial FD)
2. Augmentation: If X → Y, then XZ → YZ for any Z.
3. Transitivity: If X → Y and Y → Z, then X → Z.

From these three axioms you can derive additional rules:

• Union: X → Y and X → Z ⟹ X → YZ
• Decomposition: X → YZ ⟹ X → Y and X → Z
• Pseudotransitivity: X → Y and WY → Z ⟹ WX → Z

Closure of an attribute set: Given a set of FDs F and an attribute set X, the closure X⁺ is the largest set of attributes determined by X under F. Algorithm:

closure = X
repeat:
  for each FD A → B in F:
    if A ⊆ closure: closure = closure ∪ B
until no change

The closure is used to find candidate keys: X is a superkey iff X⁺ = all attributes.

Canonical cover (minimal cover) of a set F: an equivalent set F_c with no redundant FDs and no redundant attributes on any left-hand side. Computing it is a standard algorithm in database courses and matters for practical schema decomposition.

An important special case: a trivial FD X → Y is one where Y ⊆ X. Trivial FDs are always true and never useful for normalization.

# Computing attribute closure in Python
def closure(attrs: set, fds: list[tuple[set, set]]) -> set:
    """
    attrs: starting attribute set (e.g. {'A', 'B'})
    fds:   list of (lhs_set, rhs_set) tuples
    returns: closure attrs+
    """
    result = set(attrs)
    changed = True
    while changed:
        changed = False
        for lhs, rhs in fds:
            if lhs <= result and not rhs <= result:
                result |= rhs
                changed = True
    return result

# Example: R(A,B,C,D,E), F = {AB->C, C->D, D->E}
fds = [({'A','B'}, {'C'}),
       ({'C'},     {'D'}),
       ({'D'},     {'E'})]
print(closure({'A','B'}, fds))  # {'A','B','C','D','E'}
# So AB is a superkey (closure = all attributes)

The concepts of keys are central to both ER modeling and normalization, but they are often conflated. Let's be precise.

Superkey: A set of attributes K such that K → all attributes. No two tuples in any valid instance can agree on K. By definition, any superset of a superkey is also a superkey.

Candidate key: A minimal superkey — a superkey from which no attribute can be removed while still uniquely identifying tuples. Minimality is the key (pun intended) distinction. If K = {A, B} is a candidate key, then neither {A} nor {B} alone determines all attributes.

Primary key: One candidate key chosen by the designer as the official row identifier. SQL enforces this with PRIMARY KEY — it implies both UNIQUE and NOT NULL. The choice of primary key is often pragmatic (prefer single-attribute, non-nullable, stable values).

Foreign key: An attribute (or set) in table R that references the primary key of table S. This enforces referential integrity: you cannot have a row in R that refers to a non-existent row in S.

Algorithmically, finding all candidate keys is NP-hard in general, but for schemas with few attributes (as in textbook exercises) you use closure computations: enumerate attribute subsets in increasing size, compute their closures, and report those that are superkeys but whose proper subsets are not.

Example: R(A, B, C, D) with FDs: A → B, B → C, C → D, D → A.

• Closure of {A}: {A, B, C, D} — superkey
• Closure of {B}: {B, C, D, A} — superkey
• {C} and {D} similarly are superkeys
• {A} is minimal (single attribute, superkey) → candidate key
• Similarly {B}, {C}, {D} are all candidate keys
• Primary key: choose whichever is most stable in practice.

-- Illustrating superkey vs candidate key in SQL
CREATE TABLE Person (
  ssn     CHAR(11)      NOT NULL,  -- candidate key
  email   VARCHAR(200)  NOT NULL,  -- candidate key
  name    VARCHAR(100)  NOT NULL,
  -- Choose SSN as primary key:
  PRIMARY KEY (ssn),
  -- Enforce the other candidate key as UNIQUE:
  UNIQUE (email)
);

-- (ssn, email) is also a superkey but NOT a candidate key
-- because removing either attribute still gives a superkey.

Normal forms are a hierarchy of increasingly strict structural constraints. Each higher normal form eliminates a specific type of redundancy.

First Normal Form (1NF): A relation is in 1NF if every attribute value is atomic — it cannot be further decomposed into smaller, meaningful parts. No multi-valued attributes, no repeating groups, no nested tables.

Violation examples:

• phone_numbers storing "555-1234, 555-5678" (list in one cell)
• address storing "123 Main St, Cambridge, MA 02139" when you need to query by city

Fix: Give each value its own row (normalize the repeated group into a separate table) or its own column (split the composite value).

1NF is often described as the baseline — without it, you don't even have a proper relation. In practice, modern databases can enforce 1NF through schema design; the SQL standard itself technically allows some violations (array types, JSON columns) which are useful but require care.

Second Normal Form (2NF): A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key.

A non-prime attribute is one that is not part of any candidate key. A partial dependency is when a non-prime attribute depends on a proper subset of some candidate key — this is what 2NF forbids.

2NF is only relevant when a candidate key has more than one attribute. If all candidate keys are single attributes, 2NF is automatically satisfied.

Classic example: OrderItem(order_id, product_id, product_name, quantity, unit_price) with primary key (order_id, product_id).

• product_name depends only on product_id (partial dependency!) — not on the full key.
• quantity depends on both order_id and product_id (full dependency) — OK.

Fix: Decompose into OrderItem(order_id, product_id, quantity, unit_price) and Product(product_id, product_name). The partial dependency is eliminated.

-- 2NF violation
CREATE TABLE OrderItem_BAD (
  order_id    INT,
  product_id  INT,
  product_name VARCHAR(200),  -- depends only on product_id
  quantity    INT,
  unit_price  NUMERIC(10,2),  -- debatable: price may be order-specific
  PRIMARY KEY (order_id, product_id)
);

-- 2NF-compliant decomposition
CREATE TABLE Product (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL
);

CREATE TABLE OrderItem (
  order_id   INT,
  product_id INT REFERENCES Product(product_id),
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Even after eliminating partial dependencies (achieving 2NF), a relation can still suffer from redundancy due to transitive dependencies.

Transitive dependency: Non-prime attribute Z is transitively dependent on key K if K → X and X → Z for some non-key attribute set X (where X does not determine K, i.e., X is not itself a superkey).

Third Normal Form (3NF): A relation R is in 3NF if, for every non-trivial FD X → A in R, either:

1. X is a superkey of R, or
2. A is a prime attribute (member of some candidate key).

Intuitively: no non-key attribute should depend on another non-key attribute (transitively via the key).

Classic example: Employee(emp_id, dept_id, dept_name) with FDs: emp_id → dept_id, dept_id → dept_name.

• dept_name is determined by dept_id, which is not a superkey.
• Redundancy: every employee in the same department stores dept_name — update one row, forget the rest, contradiction.

Fix: Decompose into Employee(emp_id, dept_id) and Department(dept_id, dept_name).

Synthesis algorithm for 3NF: Given a set of FDs F:

1. Compute the canonical cover F_c.
2. For each FD X → Y in F_c, create a relation schema XY.
3. If no schema contains a candidate key of the original relation, add a schema consisting of any candidate key.
4. Eliminate redundant schemas (those whose attributes are a subset of another schema's attributes).

The synthesis algorithm is important because it guarantees the decomposition is both lossless (the original relation can be reconstructed by joining the pieces) and dependency-preserving (every FD in F can be checked in one of the resulting schemas without joining).

-- 3NF violation: transitive dependency
CREATE TABLE Employee_BAD (
  emp_id    INT PRIMARY KEY,
  dept_id   INT,
  dept_name VARCHAR(100)   -- transitively depends on emp_id via dept_id
);
-- emp_id -> dept_id -> dept_name
-- Redundancy: every engineer stores 'Engineering' string

-- 3NF-compliant decomposition
CREATE TABLE Department (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE Employee (
  emp_id  INT PRIMARY KEY,
  dept_id INT REFERENCES Department(dept_id)
);

3NF is provably achievable with both lossless-join and dependency-preservation guarantees. BCNF is strictly stronger but sometimes forces you to give up dependency preservation.

BCNF: A relation R is in BCNF if, for every non-trivial functional dependency X → Y in R, X is a superkey of R.

Compare to 3NF: 3NF has the "escape clause" that A may be prime. BCNF removes that escape clause. Every determinant of a non-trivial FD must be a superkey — no exceptions.

The gap between 3NF and BCNF: Most relations that are in 3NF are also in BCNF. The exceptions arise only when a relation has multiple overlapping candidate keys.

Classic BCNF violation (the "Courses" example): Enrollment(student, course, teacher) with:

• FDs: (student, course) → teacher, teacher → course
• Candidate keys: (student, course) and (student, teacher) (both are candidate keys)
• The FD teacher → course violates BCNF because teacher is not a superkey.
• But course (the RHS) IS a prime attribute — so 3NF is satisfied.

BCNF decomposition algorithm (decompose until each schema is in BCNF):

result = {R}
while some schema R_i in result is not in BCNF:
  find FD X → Y in R_i that violates BCNF
  decompose R_i into:
    R_i1 = X ∪ Y   (the violating FD lives here)
    R_i2 = R_i - Y  (the "remainder")
  replace R_i with R_i1 and R_i2

The BCNF vs 3NF tradeoff: BCNF decomposition is always lossless but may NOT preserve all FDs. In the Enrollment example above, decomposing into TeacherCourse(teacher, course) and StudentTeacher(student, teacher) eliminates the BCNF violation, but you can no longer check the FD (student, course) → teacher without joining.

In practice: aim for BCNF. When a BCNF decomposition loses a critical dependency, consider whether 3NF is sufficient, or whether the lost FD can be enforced via a trigger or application logic.

-- BCNF violation example
-- R(student, course, teacher)
-- FDs: (student,course)->teacher, teacher->course
-- teacher is not a superkey, yet teacher->course is non-trivial
-- => BCNF violation

-- BCNF decomposition:
CREATE TABLE TeacherCourse (
  teacher  VARCHAR(100) PRIMARY KEY,  -- teacher -> course
  course   VARCHAR(50)  NOT NULL
);

CREATE TABLE StudentTeacher (
  student  INT,
  teacher  VARCHAR(100) REFERENCES TeacherCourse(teacher),
  PRIMARY KEY (student, teacher)
);
-- Note: can no longer directly enforce (student,course)->teacher
-- without a JOIN. This is the dependency-preservation sacrifice.

Decomposition is only useful if it preserves the information you started with. Two formal properties guarantee this.

Lossless-join decomposition: Decomposing R into R₁ and R₂ is lossless if and only if:

R = R₁ ⋈ R₂ for every valid instance of R.

Equivalently (for two-way decomposition), the decomposition is lossless iff: (R₁ ∩ R₂) → R₁ or (R₁ ∩ R₂) → R₂ holds under the FD set F.

In plain English: the set of attributes common to both pieces must be a superkey of at least one piece. This ensures that when you join R₁ and R₂ back together, you get exactly R — no spurious tuples are created.

Lossy example: R(A, B, C) with A → B. Decompose into {A, C} and {B, C}. The intersection is {C}. Does C → {A,C}? No. Does C → {B,C}? No. → This decomposition is LOSSY. Joining back creates phantom combinations of A and B values that never appeared in R.

Dependency-preserving decomposition: Given F over R and decomposition into R₁, ..., Rₖ, define Fᵢ as the projection of F onto Rᵢ (all FDs in F⁺ that involve only attributes of Rᵢ). The decomposition is dependency-preserving if:

(F₁ ∪ F₂ ∪ ... ∪ Fₖ)⁺ = F⁺

In plain English: every FD can be checked by looking at one of the pieces (no join required for enforcement). This is critical for practical enforcement: if checking an FD requires joining two tables, the database engine can't easily enforce it as a constraint.

Key theorem: Every relation can be losslessly decomposed into BCNF. Every relation can be decomposed into 3NF with both lossless-join and dependency-preservation. BCNF does not always allow dependency-preserving decompositions — this is the fundamental 3NF vs BCNF tradeoff.

# Checking if a 2-way decomposition is lossless
def is_lossless(r1_attrs: set, r2_attrs: set,
               fds: list[tuple[set, set]]) -> bool:
    """
    Check losslessness via the intersection superkey condition.
    Decomposition R -> R1, R2 is lossless iff:
    (R1 ∩ R2)+ ⊇ R1  OR  (R1 ∩ R2)+ ⊇ R2
    """
    common = r1_attrs & r2_attrs
    c = closure(common, fds)   # using the closure() from earlier
    return r1_attrs <= c or r2_attrs <= c

# Example: R(A,B,C), FD: A->B
# Decompose into {A,B} and {A,C}
fds = [({'A'}, {'B'})]
print(is_lossless({'A','B'}, {'A','C'}, fds))
# intersection = {A}, closure({A}) = {A,B}
# {A,B} <= {A,B} -> True (lossless!)

# Compare: decompose into {B,C} and {A,C}
print(is_lossless({'B','C'}, {'A','C'}, fds))
# intersection = {C}, closure({C}) = {C}
# neither {B,C} <= {C} nor {A,C} <= {C} -> False (LOSSY!)

BCNF eliminates all redundancy due to functional dependencies, but there is a subtler kind of redundancy that FDs cannot capture: multivalued dependencies (MVDs).

A multivalued dependency X ↠ Y (read: X multidetermines Y) holds in R if the set of Y-values associated with any X-value is independent of the remaining attributes R - X - Y.

Formally: in every valid instance, whenever tuples t₁ and t₂ agree on X, there must exist a tuple t₃ that agrees with t₁ on X and Y, and with t₂ on R - X - Y.

Classic example: CourseInfo(course, teacher, textbook) where a course can have multiple teachers and multiple textbooks, but the choice of teacher is independent of the choice of textbook.

• MVDs: course ↠ teacher and course ↠ textbook
• Even in BCNF (the only FD is trivial: {course, teacher, textbook} → everything), the table is redundant: every (teacher, textbook) pair must be crossed with every other, creating a Cartesian explosion.

Fourth Normal Form (4NF): R is in 4NF if for every non-trivial MVD X ↠ Y in R, X is a superkey.

Every FD X → Y implies the MVD X ↠ Y (a special case where the "set" always has one element), so 4NF is strictly stronger than BCNF.

Fix: Decompose CourseInfo into CourseTeacher(course, teacher) and CourseTextbook(course, textbook). The MVD redundancy is eliminated; each table is in 4NF.

4NF is less commonly discussed in industry than BCNF, but it matters when modeling entities with multiple independent multi-valued attributes — a pattern that appears in tag systems, product configurations, and permission matrices.

-- 4NF violation: independent multivalued attributes forced together
CREATE TABLE CourseInfo_BAD (
  course   VARCHAR(10),
  teacher  VARCHAR(100),
  textbook VARCHAR(200),
  -- No FD violations (BCNF satisfied) but
  -- course ↠ teacher and course ↠ textbook cause
  -- Cartesian-product redundancy
  PRIMARY KEY (course, teacher, textbook)
);

-- 4NF decomposition (project on each MVD)
CREATE TABLE CourseTeacher (
  course  VARCHAR(10),
  teacher VARCHAR(100),
  PRIMARY KEY (course, teacher)
);

CREATE TABLE CourseTextbook (
  course   VARCHAR(10),
  textbook VARCHAR(200),
  PRIMARY KEY (course, textbook)
);

Normalization gives you correct data at the cost of more joins at query time. Real high-throughput systems — especially read-heavy analytics workloads — sometimes deliberately violate normal forms to gain performance. This is called denormalization, and it is a principled engineering tradeoff, not a mistake.

When denormalization is justified:

1. Read-heavy workloads: If a table is read millions of times per second and almost never written, the cost of joining is paid continuously while the cost of update anomalies is nearly zero.
2. Query-critical paths: A single hot query that joins 8 tables can be sped up dramatically by pre-computing the join into a materialized, redundant table.
3. Analytical queries (OLAP): Data warehouses (Snowflake, BigQuery, Redshift) routinely use star schemas and snowflake schemas which are explicitly denormalized — facts tables store redundant dimensional data to avoid joins at query time.

Common denormalization techniques:

• Pre-computing aggregates: Store order_count on the Customer table rather than COUNT(*)-ing every time.
• Duplicating frequently-read columns: Storing user_email in a Notification row to avoid joining with User on every notification display.
• Materialized views: The database maintains a denormalized view and automatically (or periodically) refreshes it.
• Event sourcing / append-only logs: The canonical normalized record is an immutable event log; derived denormalized views are projections of it.

The cost: You must now maintain consistency programmatically — through triggers, application logic, or an ETL pipeline. These mechanisms can fail, drift, or be forgotten. Denormalization creates a technical debt that must be actively managed.

The rule: Normalize first, always. Denormalize only after profiling proves a specific bottleneck, with a clear maintenance plan for the introduced redundancy.

-- Normalized: must JOIN to get customer email for notification
SELECT n.message, u.email
FROM Notification n
JOIN User u ON n.user_id = u.user_id
WHERE n.notification_id = 42;

-- Denormalized: email stored redundantly (fast, but fragile)
CREATE TABLE Notification_Denorm (
  notification_id BIGINT PRIMARY KEY,
  user_id         INT NOT NULL,
  user_email      VARCHAR(200) NOT NULL,  -- REDUNDANT
  message         TEXT NOT NULL,
  created_at      TIMESTAMPTZ DEFAULT now()
);
-- Now: if a user changes email, must update all Notification rows
-- or accept stale email data

-- Better: materialized view (database manages consistency)
CREATE MATERIALIZED VIEW NotificationView AS
  SELECT n.notification_id, n.message, u.email
  FROM Notification n JOIN User u ON n.user_id = u.user_id;
CREATE UNIQUE INDEX ON NotificationView(notification_id);
-- Refresh periodically or on trigger

Normalization theory is most powerful when embedded in a design process, not applied as an afterthought. Here is a battle-tested workflow that combines ER modeling, normalization, and pragmatic judgment.

Step 1: Domain analysis. Talk to stakeholders. Understand what entities exist, what facts need to be recorded, what queries will be run, and what the update patterns are.

Step 2: Conceptual design (ER diagram). Model entities, attributes, and relationships. Identify cardinalities. Identify strong vs weak entities. Do NOT think about SQL yet.

Step 3: Logical design (translate ER → relational). Apply the mechanical translation rules. Assign primary keys. Add foreign key constraints.

Step 4: Identify functional dependencies. For each relation, enumerate all the FDs that hold based on domain semantics. This is the most intellectually demanding step — it requires deep understanding of the domain.

Step 5: Normalize. Check each relation against the normal form hierarchy. Decompose violating relations. Verify losslessness after each decomposition. Aim for BCNF; accept 3NF when BCNF loses a critical dependency.

Step 6: Physical design. Add indexes. Decide on data types and storage parameters. Identify hot queries and consider materialized views or strategic denormalization.

Step 7: Iterate. Schema design is not a one-time exercise. As the application evolves, new FDs are discovered, query patterns change, and the schema must evolve through careful migrations.

Common design patterns:

• Junction table (bridge table): for M:N relationships, a table with two foreign keys as composite PK.
• Audit table: append-only shadow table logging every change to a main table.
• Polymorphic association: one foreign key pointing to multiple tables (requires care — often a code smell; use a supertype/subtype decomposition instead).
• Surrogate key: an artificial integer PK (SERIAL/UUID) added when no natural key exists or when natural keys are mutable.

-- Full mini-schema demonstrating the workflow
-- Domain: university course enrollment

CREATE TABLE Department (
  dept_code  CHAR(4) PRIMARY KEY,  -- natural key: 'EECS', 'MATH'
  dept_name  VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Professor (
  prof_id    SERIAL PRIMARY KEY,   -- surrogate key
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(200) NOT NULL UNIQUE,
  dept_code  CHAR(4) NOT NULL REFERENCES Department(dept_code)
);

CREATE TABLE Course (
  course_code VARCHAR(10) PRIMARY KEY,  -- natural key: '6.033'
  title       VARCHAR(200) NOT NULL,
  credits     SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 12),
  dept_code   CHAR(4) NOT NULL REFERENCES Department(dept_code)
);

CREATE TABLE CourseOffering (
  offering_id SERIAL PRIMARY KEY,
  course_code VARCHAR(10) REFERENCES Course(course_code),
  semester    CHAR(6) NOT NULL,   -- e.g. '2024FA'
  prof_id     INT NOT NULL REFERENCES Professor(prof_id),
  UNIQUE (course_code, semester)  -- one offering per course per sem
);

CREATE TABLE Student (
  student_id  SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(200) NOT NULL UNIQUE,
  year        SMALLINT CHECK (year BETWEEN 1 AND 4)
);

CREATE TABLE Enrollment (
  student_id  INT REFERENCES Student(student_id),
  offering_id INT REFERENCES CourseOffering(offering_id),
  grade       CHAR(2),
  PRIMARY KEY (student_id, offering_id)
);