1 / 24

Chapter 6 The Relational Model and Normalization

Chapter 6 The Relational Model and Normalization. IS420/520 Western Oregon University. Relations. Relational DBMS products store data in the form of relations, a special type of 2D table A relation is a two-dimensional table that has the following characteristics

glenna-hunt
Télécharger la présentation

Chapter 6 The Relational Model and 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. Chapter 6The Relational Model and Normalization IS420/520 Western Oregon University

  2. Relations • Relational DBMS products store data in the form of relations, a special type of 2D table • A relation is a two-dimensional table that has the following characteristics • Rows contain data about an entity • Columns contain data about attributes of the entity • All entries in a column are of the same kind • Each column has a unique name • Cells of the table hold a single value • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical • Although not all tables are relations, especially when keys are added to the tables, the terms table and relation are normally used interchangeably • Table/row/column = file/record/field = relation/tuple/attribute

  3. Example: Relation

  4. Example: Tables Not Relations

  5. Types of Keys • A key is one or more columns of a relation that identifies a row • Composite key is a key that contains two or more attributes • A Surrogate Key is an artificial column added to a table to serve as the primary key • A foreign key is a column or set of columns that is the primary of another table • A relation has one unique primary key and may also have additional unique keys called candidate keys • Primary key is used to • Represent the table in relationships • Organize table storage • Generate indexes

  6. Functional Dependencies • A functional dependency occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s) • The attribute on the left side of the functional dependency is called the determinant • SID  GPA • SKU  Department, SKU_Description • (CustomerNumber, ItemNumber, Quantity)  Price • Mathematically, we say that “A” determines “B”, A  B • Physically, we might say that “A” is a unique identifier for “B”

  7. Functional Dependency (2) • Full Functional Dependency: “B” is functionally dependent on “A” but not on any subset of “A” • Transitive Dependency: If “B” and “C” are both dependent on “A”, but “C” is also dependent on “B”, we say that “C” is transitively dependent on “B”

  8. Normalization • Normalization eliminates modification anomalies • Deletion anomaly: deletion of a row loses information about two or more entities • Insertion anomaly: insertion of a fact in one entity cannot be done until a fact about another entity is added • Anomalies can be removed by splitting the relation into two or more relations; each with a different, single theme • Normalization works through classes of relations called normal forms

  9. Relationship of Normal Forms

  10. Normal Forms • Any table of data is in 1NF if it meets the definition of a relation • A relation is in 2NF if all its non-key attributes are dependent on all of the key (no partial dependencies, all are full dependencies) • If a relation has a single attribute key, it is automatically in 2NF • A relation is in 3NF if it is in 2NF and has no transitive dependencies • A relation is in BCNF if every determinant is a candidate key • A relation is in fourth normal form if it is in BCNF and has no multi-value dependencies

  11. Example: 2NF

  12. Example: NOT IN 3NF

  13. Example: 3NF

  14. Example: NOT IN BCNF

  15. Example: BCNF

  16. Another Example: NOT IN BCNF Specialized tool maker CUSTOMER ID + Tool Type  Tool Trainer ID CUSTOMER ID + Tool TRAINER ID is also an candidate key (unique) Tool TRAINER ID  Tool Type

  17. Example: NOT IN 4NF

  18. Example: 4NF

  19. Example: 4NF

  20. DK/NF • First published in 1981 by Fagin • DK/NF has no modification anomalies; so no higher normal form is needed • A relation is in DK/NF if every constraint on the relation is a logical consequence of the definition of keys and domains

  21. Example 1: DK/NF

  22. Example: DK/NF

  23. Normal Forms-Review • Any table of data is in 1NF if it meets the definition of a relation • A relation is in 2NF if all its non-key attributes are dependent on all of the key (no partial dependencies) • If a relation has a single attribute key, it is automatically in 2NF • A relation is in 3NF if it is in 2NF and has no transitive dependencies • A relation is in BCNF if every determinant is a candidate key • A relation is in fourth normal form if it is in BCNF and has no multi-value dependencies

  24. De-normalized Designs • When a normalized design is unnatural, awkward, or results in unacceptable performance, a de-normalized design is preferred • Example • Normalized relation • CUSTOMER (CustNumber, CustName, Zip) • CODES (Zip, City, State) • De-Normalized relations • CUSTOMER (CustNumber, CustName, City, State, Zip)

More Related