Database Modeling & Design Fourth Edition- P27: 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. | The Design of Normalized Tables A Simple Example 117 4. proj_no - proj_name proj_start_date proj_end_date 5. dept_no - dept_name mgr_id 6. mgr_id - dept_no Our objective is to design a relational database schema that is normalized to at least 3NF and if possible minimize the number of tables required. Our approach is to apply the definition of third normal form 3NF in Section to the FDs given above and create tables that satisfy the definition. If we try to put FDs 1 through 6 into a single table with the composite candidate key and primary key emp_id start_date we violate the 3NF definition because FDs 2 through 6 involve left sides of FDs that are not superkeys. Consequently we need to separate 1 from the rest of the FDs. If we then try to combine 2 through 6 we have many transitivities. Intuitively we know that 2 3 4 and 5 must be separated into different tables because of transitive dependencies. We then must decide whether 5 and 6 can be combined without loss of 3NF this can be done because mgr_id and dept_no are mutually dependent and both attributes are Figure ER diagram for employee database 118 CHAPTER 6 Normalization superkeys in a combined table. Thus we can define the following tables by appropriate projections from 1 through 6. emp_hist emp_id start_date - job_title end_date employee emp_id - emp_name phone_no proj_no dept_no phone phone_no - office_no project proj_no - proj_name proj_start_date proj_end_date department dept_no - dept_name mgr_id mgr_id - dept_no This solution which is BCNF as well as 3NF maintains all the original FDs. It is also a minimum set of normalized tables. In Section we will look at a formal method of determining a minimum set that we can apply to much more complex situations. Alternative designs may involve splitting tables into partitions for volatile frequently updated and passive rarely updated data consolidating tables to get better query performance or duplicating data in different tables to get better
