Database Modeling & Design Fourth Edition- P31: Database technology has evolved rapidly in the three decades since the rise and eventual dominance of relational database systems. While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the science and engineering fields, relational systems remain the dominant database technology for business enterprises. | Summary 137 Table Summary of Higher Normal Forms Table Name Normal Form Two-way Lossless decomp join Three-way Lossless decomp join Nontrivial MVDs skill_required BCNF yes yes 2 skill_in_ 4NF no yes 0 common skill_used 5NF no no 0 A many-to-many-to-many ternary relationship is BCNF if it can be replaced by two binary relationships 4NF if it can only be replaced by three binary relationships 5NF if it cannot be replaced in any way and thus is a true ternary relationship We observe the equivalence between certain ternary relationships and the higher normal form tables transformed from those relationships. Ternary relationships that have at least one one entity cannot be decomposed or broken down into binary relationships because that would destroy the one or more FDs required in the definition as shown previously. A ternary relationship with all many entities however has no FDs but in some cases may have MVDs and thus have a lossless decomposition into equivalent binary relationships. In summary the three common cases that illustrate the correspondence between a lossless decomposition in a many-to-many-to-many ternary relationship table and higher normal forms in the relational model are shown in Table . Summary In this chapter we defined the constraints imposed on tables most commonly the functional dependencies or FDs. Based on these constraints practical normal forms for database tables are defined 1NF 2NF 3NF and BCNF. All are based on the types of FDs present. In this chapter a practical algorithm for finding the minimum set of 3NF tables is given. 138 CHAPTER 6 Normalization The following statements summarize the functional equivalence between the ER model and normalized tables 1. Within an entity. The level of normalization is totally dependent upon the interrelationships among the key and nonkey attributes. It could be any form from unnormalized to BCNF or higher. 2. Binary or binary recursive one-to-one or one-to-many relationship. Within the
