1 / 22

Introduction to Normalization

Introduction to Normalization. CPSC 356 Database Ellen Walker Hiram College. Building a Schema. Start with a list of all attributes, considered as if you had a giant flat database (one relation) with all possible information in one place Divide the attributes into multiple relations

zyta
Download Presentation

Introduction to Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Introduction to Normalization CPSC 356 Database Ellen Walker Hiram College

  2. Building a Schema • Start with a list of all attributes, considered as if you had a giant flat database (one relation) with all possible information in one place • Divide the attributes into multiple relations • Intuitively • According to formal rules (normalization) • This is a formalized alternative to the algorithms we learned before

  3. What Makes A Good Schema? • Each relation should have clear semantics, i.e. can be easily described in a few words • Try to avoid redundancy (to minimize storage space, but also to avoid anomalies) • Avoid a design that encourages too many NULL values in a relation. NULL can be ambiguous: N/A vs. unknown vs. not-yet-entered, etc. • Don’t split related attributes so that the relationship between them is lost (e.g. make sure LastName and UserID are both in the same relation)

  4. Tracking Real Estate Staff • Consider a single relation for real estate • It contains branch name, branch number, staff name, staff number, staff salary, etc. • One entry for each staff member of each branch • Branch information is repeated for different staff (REDUNDANCY!) • Staff information is repeated if they work in multiple branches (REDUNDANCY!) • This is an example of what NOT to do

  5. Redundancy-caused Anomalies • Insertion Anomalies • A branch with no staff has many NULLs • Entering a new staff member has NULL branch info • But branch number and staff number are both part of primary key! (Why) • Deletion Anomalies • When the last staff member at a branch is deleted, the branch info is lost • Update Anomalies • If we make a change in branch info once, it must be changed in all copies (for all staff).

  6. Solving Redundancy Problems • Decompose the relation into multiple relations • Use Foreign Keys so the complete relation can be reconstructed through a join • Branch: has branch number & branch info • Staff: has staff number, staff info & branch number as foreign key • Foreign Keys are exactly the attributes that are in the primary key of the other relation • Insertion, deletion & update anomalies are gone! • Consider: add branch with no staff, remove last staff member, update branch info

  7. How to Decompose? • Decompositions are not (always) intuitively obvious • Codd discovered mathematical properties (called Normal Forms) that describe “goodness” of decomposition • First, Second, Third normal forms decrease redundancy without loss of information • BCNF, Fourth and Fifth normal forms potentially introduce information loss (we will see…) • To understand normal forms, start with functional dependencies

  8. Functional Dependencies • If A and B are attributes, and every value of A is associated with exactly one value of B (so knowing A predicts B), then B is functionally dependent on A (We write this as: A->B) • Functional dependency is based on the semantics (meaning) of the attributes. • A->B and B->A are two different constraints • Email -> first name is a valid dependency • First name -> Email is not a valid dependency

  9. Examples of Functional Dependencies • US Zip Code -> State • US Area Code -> State • Email -> Firstname, lastname • HotelNo, RoomNo -> Price • JobTitle, ServiceLength -> Salary

  10. What are the dependencies? • item place customer-name • ring Kay jewelers prince charming • ring walmart miss piggy • Place -> item? • Item -> place? • oil walmart tin man

  11. Finding Dependencies in Data • If a value of attribute A is associated with two or more values of B, then it is not true that A->B. • If a value of attribute A is associated with exactly one value of B, then it might be true that A->B. • Only when every possible value of attribute A is associated with exactly one value of B is it true that A->B.

  12. Characteristics of Functional Dependencies for Normalization • For any given values of the attributes on the left, there is exactly one possible attribute on the right • No future data will ever invalidate the dependency • Dependency is nontrivial -- no attributes from the left are repeated on the right

  13. Keys & Functional Dependency • Remember, a candidate key is a subset of attributes that is (guaranteed) unique for every tuple • Therefore, a valid candidate key determines all other attributes in the tuple • Therefore, there is a functional dependency from the candidate key to all other non-key attributes of the relation. • (Since the primary key is a candidate key, these arguments can also be made for primary keys)

  14. Manipulating Functional Dependencies • Given a set of dependencies, derive more dependencies using inference rules • The closure X+ of a set of dependencies is the set of all possible dependencies that can be derived from it.

  15. Armstrong’s Inference Rules for Manipulating Dependencies • if Y is a subset of X, then X -> Y Alternatively: X,Y -> X (Reflexive) • If X->Y then X,Z->Y,Z (Augmentation) • If X->Y and Y->Z then X->Z (Transitive)

  16. Additional Inference Rules • A->A (Self-determination) • If A->B,C then A->B and A->C (Decomposition) • If A->B and A->C then A->B,C (Union) • If A->B and C->D then A,C -> B,D (Composition)

  17. When are two sets of FDs equivalent? • When we can use inference rules to transform A to B , then A and B are equivalent • Problem: it might take a long time to find the right set of inference rules • What we need is a “standard form” of FD’s - then we can just compare

  18. Finding the Closure • F is a set of functional dependencies (e.g. the obvious ones from primary keys) We want to find X+, which is the set of all attributes that are dependent on X (based on F). X+ = X repeat for each dependency Y->Z in F do if Y is a subset of X+ then X+ = X+ union Z until no more can be added to X+

  19. Closure Example • F is the following set of dependencies: A->B,C C->D A,D -> F • What is A+ (all attributes that can be derived from A)? • Initialize A+ = A • Because A->B,C add B,C to A+ • Because C is in A+ and C->D, add D to A+ • Because A and D are in A+, add F to A+ • Therefore A+ is A,B,C,D,F

  20. Equivalence Test • Are the following sets of FDs equivalent? • AB->C, D->E, AE->G, GD->H, ID->J • ABD->C, ABE->G, GD->EH, IE->J • Compute closures for each, if any two are different, they are not equivalent • You will need to consider every left side…

  21. Finding a Key • Given a relation with attributes ABCDEFGHIJ and the following FDs, find a candidate key for the relation • AB->C, D->E, AE->G, GD->H, ID->J • A candidate key is a subset of attributes that has the entire set of attributes as its closure. • Let’s try ABD…

  22. What is Normalization? • Formal technique for analyzing relations based on primary key (or candidate keys) and functional dependencies • Series of tests (normal forms), each of which is harder to “pass” • Normal forms 1NF, 2NF, 3NF, BCNF depend on functional dependencies • Higher forms (4NF, 5NF) based on other dependencies • To avoid update anomalies without loss, normalize to 3NF.

More Related