Lesson 3: Relational Design and Normalization

In the relational model, data are stored in tables (relations) made up of rows (tuples) and columns (attributes). Each table has a primary key column (or set of columns) whose value uniquely identifies each row. Other columns are fields like name or date. Tables can be related by keys: a foreign key in one table points to the primary key of another. For example, a Student(student_id, name, program) table might have student_id as primary key. An Enrollment(enrollment_id, student_id, course_id) table would have student_id as a foreign key linking to Student.student_id. The DBMS enforces these relationships to prevent, say, enrolling a student ID that doesn't exist.

A functional dependency describes a relationship between columns: "X → Y" means that for any two rows, if X is the same then Y must also be the same. For example, in a Student table, student_id → name, program (student ID determines the student's name and program). Understanding FDs helps in designing tables without redundancy. If part of a composite primary key determines a column, that's a partial dependency. If a non-key column depends on another non-key column, that's a transitive dependency. These situations can cause data anomalies (like updates and insertions failing to correctly propagate).