FunctionalDependenciesandNormalizationfor.pptVIP

Functional Dependencies and Normalization for Relational Databases ? Dr. Ali Obaidi CS-450 Fall 2002 Informal Design Guidelines for Relational Databases Relational database design: The grouping of attributes to form good relation schemas Two levels of relation schemas: The logical user view level The storage base relation level Design is concerned mainly with base relations Criteria for good base relations: Discuss informal guidelines for good relational design Discuss formal concepts of functional dependencies and normal forms 1NF 2NF 3NF BCNF Semantics of the Relation Attributes Each tuple in a relation should represent one entity or relationship instance Only foreign keys should be used to refer to other entities Entity and relationship attributes should be kept apart as much as possible Design a schema that can be explained easily relation by relation. The semantics of attributes should be easy to interpret. Redundant Information in Tuples and Update Anomalies Mixing attributes of multiple entities may cause problems Information is stored redundantly wasting storage Problems with update anomalies: Insertion anomalies Deletion anomalies Modification anomalies EXAMPLE OF AN UPDATE ANOMALY Consider the relation: EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours) Update Anomaly Changing the name of project number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1 Insert Anomaly Cannot insert a project unless an employee is assigned to . Inversely- Cannot insert an employee unless he/she is assigned to a project. EXAMPLE OF AN UPDATE ANOMALY (2) Delete Anomaly When a project is deleted, it will result in deleting all the employees who work on that project. Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project. Design a schema that does not suffer from the insertion, deletion and update anomalies. I