schema_design教程.pdfVIP

Motivation • We have designed ER diagram, and translated it into a relational db schema R = set of R1, R2, ... • Now what? • We can do the following implement R in SQL start using it • However, R may not be well-designed, thus causing us a lot of problems 1 Potential Problems • Redundancy • Update anomalies • Deletion anomalies 2 How do We Obtain a Good Design? • Start with the original db schema R • Transform it until we get a good design R* • Some desirable properties for R* must preserve the information of R must have minimal amount of redundancy must be “dependency preserving” (we’ll come to this later) must also give good query performance 3 How do We Obtain a Good Design? 4 Normal Forms • DB gurus have developed many “normal forms” • Most important ones Boyce-Codd, 3rd, and 4th normal forms • If R* is in one of these forms, then R* is guaranteed to achieve certain good properties e.g., if R* is in Boyce-Codd NF, it is guaranteed to not have certain types of redundancy • DB gurus have also developed algorithms to transform R into R* that is in some of these normal forms 5 Normal Forms (cont.) • DB gurus have also discussed trade-offs among normal forms • Thus, all we have to do is learn these forms transform R into R* in one of these forms carefully evaluate the trade-offs • To understand these norm