Database Systems: Lecture 8 & 9 - Functional Dependencies & Normalization for Relational DBs includes about Functional dependencies (FDs), Normalization (1NF and dependency problems, 2NF – solves partial dependency, 3NF – solves indirect dependency, BCNF – well-normalized relations), Notes and suggestions. | Top-Down Database Design Mini-world Requirements Conceptual schema E1 E2 R Relation schemas ? Functional Dependencies & Normalization for Relational DBs Outline Introduction Functional dependencies (FDs) Definition of FD Direct, indirect, partial dependencies Inference Rules for FDs Equivalence of Sets of FDs Minimal Sets of FDs Normalization 1NF and dependency problems 2NF – solves partial dependency 3NF – solves indirect dependency BCNF – well-normalized relations Notes and suggestions Summary Reading suggestion: [1]: Chapters 10, 11; [2]: Chapters 13, 14 Introduction Each relation schema consists of a number of attributes and the relational database schema consists of a number of relation schemas Attributes are grouped to form a relation schema Need some formal measure of why one grouping of attributes into a relation schema may be better than another Introduction “Goodness” measures: Redundant information in tuples Update anomalies: modification, deletion, insertion . | Top-Down Database Design Mini-world Requirements Conceptual schema E1 E2 R Relation schemas ? Functional Dependencies & Normalization for Relational DBs Outline Introduction Functional dependencies (FDs) Definition of FD Direct, indirect, partial dependencies Inference Rules for FDs Equivalence of Sets of FDs Minimal Sets of FDs Normalization 1NF and dependency problems 2NF – solves partial dependency 3NF – solves indirect dependency BCNF – well-normalized relations Notes and suggestions Summary Reading suggestion: [1]: Chapters 10, 11; [2]: Chapters 13, 14 Introduction Each relation schema consists of a number of attributes and the relational database schema consists of a number of relation schemas Attributes are grouped to form a relation schema Need some formal measure of why one grouping of attributes into a relation schema may be better than another Introduction “Goodness” measures: Redundant information in tuples Update anomalies: modification, deletion, insertion Reducing the NULL values in tuples Disallowing the possibility of generating spurious tuples Introduction Redundant information in tuples: the attribute values pertaining to a particular department (DNUMBER, DNAME, DMGRSSN) are repeated for every employee who works for that department Introduction Update anomalies: modification, deletion, insertion Modification As the manager of a dept. changes we have to update many values according to employees working for that dept. Easy to make the DB inconsistent Introduction Deletion: if Borg James E. leaves, we delete his tuple and lose the existing of dept. 1, the name of dept. 1, and who is the manager of dept. 1 Introduction Insertion: How can we create a department before any employees are assigned to it ?? Introduction Reducing the NULL values in tuples Employees not assigned to any dept.: waste the storage space Other difficulties: aggregation operations (., COUNT, SUM) and joins Introduction Disallowing the .
