【正文】 Lecture 9 in CETL to work on Access ? Project guidelines are provided – Students should start the design of their database Announcement This form dictates that all nonkey attributes of a table must be functionally dependent on a candidate key . there can be no interdependencies among nonkey attributes. For a table to be in 3NF, there are two requirements – The table should be second normal form – No attribute is transitively dependent on the primary key Example (Not in 3NF) Scheme ? {Title, PubID, PageCount, Price } 1. Key ? {Title, PubId} 2. {Title, PubId} ? {PageCount} 3. {PageCount} ? {Price} 4. Both Price and PageCount depend on a key hence 2NF 5. Transitively {Title, PubID} ? {Price} hence not in 3NF Third Normal Form (3NF) Example 2 (Not in 3NF) Scheme ? {Studio, StudioCity, CityTemp} 1. Primary Key ? {Studio} 2. {Studio} ? {StudioCity} 3. {StudioCity} ? {CityTemp} 4. {Studio} ? {CityTemp} 5. Both StudioCity and CityTemp depend on the entire key hence 2NF 6. CityTemp transitively depends on Studio hence violates 3NF Example 3 (Not in 3NF) Scheme ? {BuildingID, Contractor, Fee} 1. Primary Key ? {BuildingID} 2. {BuildingID} ? {Contractor} 3. {Contractor} ? {Fee} 4. {BuildingID} ? {Fee} 5. Fee transitively depends on the BuildingID 6. Both Contractor and Fee depend on the entire key hence 2NF Third Normal Form (3NF) BuildingID Contractor Fee 100 Randolph 1200 150 Ingersoll 1100 200 Randolph 1200 250 Pitkin 1100 300 Randolph 1200 1. Move all items involved in transitive dependencies to a new entity. 2. Identify a primary key for the new entity. 3. Place the primary key for the new entity as a foreign key on the original entity. Example 1 (Convert to 3NF) Old Scheme ? {Title, PubID, PageCount, Price } New Scheme ? {PubID, PageCount, Price} New Scheme ? {Title, PubID, PageCount} 3NF Deposition Example 2 (Convert to 3NF) Old Scheme ? {Studio, StudioCity, CityTemp} New Scheme ? {Studio, StudioCity} New Scheme ? {StudioCity, CityTemp} Example 3 (Convert to 3NF) Old Scheme ? {BuildingID, Contractor, Fee} New Scheme ? {BuildingID, Contractor} New Scheme ? {Contractor, Fee} 3NF Deposition BuildingID Contractor 100 Randolph 150 Ingersoll 200 Randolph 250 Pitkin 300 Randolph Contractor Fee Randolph 1200 Ingersoll 1100 Pitkin 1100 ? BCNF does not allow dependencies between attributes that belong to candidate keys. ? BCNF is a refinement of the third normal form in which it drops the restriction of a nonkey attribute from the 3rd normal form. ? Third normal form and BCNF are not same if the following conditions are true: – The table has two or more candidate keys – At least two of the candidate keys are posed of more than one attribute – The keys are not disjoint . The posite candidate keys share some attributes Example 1 Address (Not in BCNF) Scheme ? {City, Street, ZipCode } 1. Key1 ? {City, Street } 2. Key2 ? {ZipCode, Street} 3. No nonkey attribute hence 3NF 4. {City, Street} ? {ZipCode} 5. {ZipCode} ? {City} 6. Dependency between attributes belonging to a key BoyceCodd Normal Form (BCNF) Exam