tailieunhanh - Lecture Database design, application development and administration - Chapter 7: Normalization of relational tables

You learned about the notation used in entity relationship diagrams, important data modeling patterns, guidelines to avoid common modeling errors, and conversion of entity relationship diagrams (ERDs) into relational tables. You applied this knowledge to construct ERDs for small, narrative problems. This chapter extends your database design skills by presenting normalization techniques to remove redundancy in relational tables. | Chapter 7 Normalization of Relational Tables Welcome to Chapter 7 - Logical database design: converting and refining the ERD - Major part of logical database design: normalization - Normalization: refinement; identifying and resolving unwanted redundancy Objectives: - Identify modification anomalies - Define functional dependencies - Apply normalization rules to modest size problems: BCNF and simple synthesis procedure - Understand relationship independence problems - Appreciate the role and objective of normalization in the db development process Outline Modification anomalies Functional dependencies Major normal forms Relationship independence Practical concerns Modification anomalies: motivation for normalization; unwanted redundancy Functional dependencies: - Assertions or constraints about the data - Most important part of the process: recording FDs Normal forms: - Rules about allowable patterns of FDs - Apply on modest size problems - CASE tool for large problems Relationship independence: - More specialized redundancy problem - Not as common and important as BCNF Practical concerns: - Role of normalization in the development process: when to use; how important - Analyzing the objective Modification Anomalies Unexpected side effect Insert, modify, and delete more data than desired Caused by excessive redundancies Strive for one fact in one place Side effect: unintended consequence; sometimes good, sometimes bad Modification anomaly: - Cannot modify just the desired data - Must modify more than the desired data Cause: - Redundancy: facts stored multiple times - Remove unwanted redundancies to eliminate anomalies Big University Database Table Big University Database Table: - Table 7-1 except for omission of two columns (StdCity and OffTerm) - Typical beginner's mistake: use one table for the entire database Anomalies: - PK: combination of StdSSN and OfferNo - Insert: cannot insert a new student without enrolling in an offering (OfferNo part of PK) - Update: . | Chapter 7 Normalization of Relational Tables Welcome to Chapter 7 - Logical database design: converting and refining the ERD - Major part of logical database design: normalization - Normalization: refinement; identifying and resolving unwanted redundancy Objectives: - Identify modification anomalies - Define functional dependencies - Apply normalization rules to modest size problems: BCNF and simple synthesis procedure - Understand relationship independence problems - Appreciate the role and objective of normalization in the db development process Outline Modification anomalies Functional dependencies Major normal forms Relationship independence Practical concerns Modification anomalies: motivation for normalization; unwanted redundancy Functional dependencies: - Assertions or constraints about the data - Most important part of the process: recording FDs Normal forms: - Rules about allowable patterns of FDs - Apply on modest size problems - CASE tool for large problems Relationship .