Database Modeling & Design Fourth Edition- P29 | Fourth and Fifth Normal Forms 127 Step 5. Definition of the Minimum Set of Normalized Tables The minimum set of normalized tables has now been computed. We define them below in terms of the table name the attributes in the table the FDs in the table and the candidate keys for that table R1 ABC AB- C with key AB R2 AEF A- EF with key A R3 EG E- G with key E R4 DGI G- DI with key G R5 DFJ F- DJ with key F R6 DKLMNP D- KLMNP L- D with keys D L R7 PQRT PQR- T with key PQR R8 PRS PR- S with key PR Note that this result is not only 3NF but also BCNF which is very frequently the case. This fact suggests a practical algorithm for a near minimum set of BCNF tables Use Bernstein s algorithm to attain a minimum set of 3NF tables then inspect each table for further decomposition or partial replication as shown in Section to BCNF. Fourth and Fifth Normal Forms Normal forms up to BCNF were defined solely on FDs and for most database practitioners either 3NF or BCNF is a sufficient level of normalization. However there are in fact two more normal forms that are needed to eliminate the rest of the currently known anomalies. In this section we will look at different types of constraints on tables multivalued dependencies and join dependencies. If these constraints do not exist in a table which is the most common situation then any table in BCNF is automatically in fourth normal form 4NF and fifth normal form 5NF as well. However when these constraints do exist there may be further update especially delete anomalies that need to be corrected. First we must define the concept of multivalued dependency. Multivalued Dependencies Definition. In a multivalued dependency MVD X- Y holds on table R with table scheme RS if whenever a valid instance of table R X Y Z contains a pair of rows that contain duplicate values of X 128 CHAPTER 6 Normalization then the instance also contains the pair of rows obtained by interchanging the Y values in the original pair. This includes
