1 / 14

Relation Normalization (Chapter 14)

Relation Normalization (Chapter 14). Modification Anomalies. What are modification anomalies? Errors or inconsistencies that may result when a user attempts to update a relation. Types of anomalies. Insertion anomalies.

lynnea
Download Presentation

Relation Normalization (Chapter 14)

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. Relation Normalization(Chapter 14)

  2. Modification Anomalies • What are modification anomalies? • Errors or inconsistencies that may result when a user attempts to update a relation. • Types of anomalies. • Insertion anomalies. • An independent piece of information cannot be recorded into a relation unless an irrelevant information must be inserted together at the same time • Update anomalies. • The update of a piece of information must occur at multiple locations, not required by the referential integrity rule. • Deletion anomalies • The deletion of a piece of information unintentionally removes other information.

  3. Normal Forms • Normal forms are classes of relations and the techniques for preventing anomalies. • Normal forms are classified by the type of modification anomalies that have been removed. • Types of normal forms: • First Normal Form (1NF). • Second Normal Form (2NF). • Third Normal Form (3NF). • Boyce-Codd Normal Form (BCNF). • Fourth Normal Form (4NF). • Fifth Normal Form (5NF). • Domain/Key Normal Form (DK/NF).

  4. First Normal Form (1NF) • A relation R is in 1NF if and only if all attribute domains contain atomic values only. • Any table meets the definition of a relation is said to be in first Normal form, i.e. a relation in relational schema is always in 1NF.

  5. Second Normal Form (2NF) • A relation is in 2NF if and only if it is in 1NF without partial dependencies. • Partial dependency • A dependency in which one or more non-key attributes are functionally dependent on part (but not all) of the key. • Two extreme cases • The primary key consists of only one attribute. • No non-key attributes exist in the relation.

  6. Third Normal Form • A relation R is in 3NF if: whenever A1 A2 … AnB is a nontrivial dependency, either {A1, …,An} is a superkey, or B is a member of some key. • A relation is in 3NF if: • it is in 2NF without transitive dependencies. • Transitive dependency • A functional dependency between two (or more) non-key attributes.

  7. Example SALES(Cust_ID, Name, Salesperson, Region) FDs: Cust_ID  Cust_ID Name Salesperson Region Salesperson Region

  8. Example SALES1(Cust_ID, Name, Salesperson) SPERSON(Selesperson, Region)

  9. Another Example SHIPMENT(Snum, Origin, Destination Distance) FDs: Snum  Snum Origin Destination Distance Origin Destination  Distance SHIPMENT(Snum, Origin, Destination) DISTANCE(OriginDestination Distance)

  10. Relation Normalization Question FDs: SID CID  Grade SID  Name Major CID  Text Major  Dept 1. What can be the primary key for above relation? 2. Decompose the above relation into 2NF then 3NF relations.

  11. Boyce-Codd Normal Form • A relation R is in BCNF if and only if: whenever nontrivial dependency A1 A2 … AnB1 B2 … Bm holds for R, it must be the case that {A1, A2, …, An} is a superkey for R. • BCNF is one of the most important normal forms. • Relations in BCNF have no anomaly in regards to functional dependencies.

  12. 1. Each students may major in several subjects. 2. For each major, a given student has only one adviser. 3. Each major has several advisors. 4. Each advisor advises only one major. 5. Each advisor advises several students in one major. StuId Major  Fname; Fname  Major

  13. Decomposition into BCNF 1. Set D = {R}; 2. While there is a relation Q in D that is not in BCNF do begin choose a relation Q in D that is not in BCNF; find a FD XY in Q that violates BCNF; Expand right side to include X+; replace Q in D by two relations (Q-X+)  X and X+; end;

  14. Remove transitive dependencies Remove non-atomic attributes Remove partial dependencies Remove remaining anomalies resulting from functional dependencies Tables 1NF 2NF 3NF BCNF

More Related