Logical Database Design

1. Logical Database Design Relational Model

2. Logical Database Design • Logical database design: process of transforming conceptual data model into a logical data model. • Relational data model is the most commonly used in contemporary applications.

3. Referential Integrity When referential integrity is enforced • value of foreign key field must exist in primary key of primary table, else the foreign key value must be null • can’t delete a record from the primary table if matching records exist in related table • can’t change primary key value in the primary key table if that record has related records

4. Transforming E-R Diagrams to Relational Model -- Entities • Each regular entity type in an ER diagram is transformed into a relation • For weak entities, create a new relation, but in addition to the simple attributes, must include primary key of owner relation as a foreign key attribute.

5. Transforming E-R Diagrams to Relational Model -- Attributes • Each simple attribute of the entity type becomes an attribute of the relation (the identifying attribute becomes the primary key) • Only simple component attributes of a composite attribute are included • When the entity type contains a multivalued attribute, two new relations are created

6. General Rule for Mapping Binary One-to_Many • Create a relation (table) for each entity. • Have the primary key of the table on the one side appear as a foreign key in the table on the many side.

7. ERD: Binary One-Many ISSUED CUSTOMER LOAN

8. Customer_ID Name Address Phone# Loan_ID Date Amount Interest_rate Term Type Customer_ID Logical Schema: Binary One-Many CUSTOMER LOAN

9. Access Implementation: Binary One-to-Many

10. ERD: Binary Many-to-Many ASSIGNED_TO EMPLOYEE PROJECT BillingRate

11. Employee_ID Name Address Birthdate Employee_ID Project_ID Billing_Rate Project_ID Project_Name Start_Date Logical Schema: Binary Many-to-Many EMPLOYEE ASSIGNED_TO PROJECT

12. Logical Schema; Text EMPLOYEE(Employee_ID, Name, Address, Birthdate) ASSIGNED_TO(Employee_ID, Project_ID, Billing_Rate) PROJECT(Project_ID, Project_Name, Start_Date)

13. Access Database

14. Access Database

15. Access Database

16. Access Implementation: Binary Many-to-Many

17. Logical Database Design Normalization

19. Problem with Unnormalized Design • No satisfactory primary key -- proj_num contains nulls, emp_num is duplicated • Too easy for data entry to be inconsistent • Data redundancy gives rise to the following anomalies: • Update anomalies • Insertion anomalies • Deletion anomalies

20. Normalization • Process of assigning attributes to entities • Reduces data redundancy • Helps eliminate data anomalies that result from redundancies

21. Normal Forms • Normalization works through a series of stages called normal forms (1NF, 2NF, 3NF are most frequently encountered in business designs) • From a structural point of view, 2NF is better than 1NF, and 3NF is better than 2NF • Generally, the higher the normal form, the more joins are required to produce a specified output and the more slowly the system responds to end-user demands. Therefore, sometimes we may have to denormalize some portions of a database design in order to meet performance requirements.

22. Functional Dependency • Normalization is based on the analysis of functional dependencies. A functional dependency is a constraint between two attributes, or two sets of attributes. • For any relation R, attribute B is functionally dependent on attribute A if, for every valid instance of A, the value of A uniquely determines the value of B. This is notated as: A B • Each normal form represents a state of a relation that results from applying simple rules regarding functional dependencies to that relation.

23. Examples of Functional Dependency ISBN Title, First_Author_name VIN Make, Model, Color SSN Name, Address, Birthdate Emp_ID, Course_Name Date_Completed

24. Candidate Keys A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. It must satisfy the following properties: • Unique identification. For every row, the value of the key must uniquely identify that row. This implies that each nonkey attribute is functionally dependent on that key. • Nonredundancy. No attribute in the key can be deleted without destroying the property of unique identification.

25. First Normal Form Remove repeating groups (multivalued attributes). A table with multivalued attributes or repeating groups is converted to a relation in first normal form by extending the data in each column to fill the cells that are empty.

28. Insertion Anomaly In order to complete a row definition, a new employee must be assigned to a project. If an employee is not yet assigned, a phantom project must be created in order to complete the employee data entry.

29. Deletion Anomaly If an employee quits, deletions must be made for every entry in which that emp_num appears. As such deletions are made, other vital data are lost. For example, in our sample data, Darlene M. Smithson is the only DSS Analyst. If she leaves the company, we have to delete her in two places, and we will lose the information that a DSS Analyst is billed at $46 per hour.

31. Update Anomaly Modifying data for an employee or a project requires updating many occurrences. For example, if Annelise Jones changes from Applications Designer to Database Designer, we would have to record this change in every project that Ms. Jones partakes in.

33. Second Normal Form A relation is in second normal form (2NF) if it is in first normal form and every nonkey attribute is fully functionally dependent on the primary key. Thus no nonkey attribute is functionally dependent on part (but not all) of the primary key. A relation that is in 1NF will be in 2NF if any one of the following conditions exist: • the primary key consists of only one attribute • no nonkey attributes exist in the relation • every nonkey attribute is functionally dependent on the full set of primary key attributes

34. Partial Functional Dependency A partial functional dependency is a functional dependency in which one or more nonkey attributes are functionally dependent on part (but not all) of the primary key.

35. Analyzing Functional Dependencies Full Dependency Partial Dependency Partial Dependency Proj_Num Proj_Name Emp_Num Emp_Name Job_Class Chg_Hour Hours Transitive Dependency

36. Proj_Num Proj_Name Emp_Num Emp_Name Job_Class Chg_Hour Proj_Num Emp_Num Hours Decomposing 1NF Relation to Convert to 2NF PROJECT RELATION Primary key consists of only one attribute EMPLOYEE RELATION Primary key consists of only one attribute ASSIGN RELATION Every nonkey attribute is functionally dependent on the full set of primary key attributes

37. Third Normal Form A relation is in third normal form (3NF) if it is in second normal form and no transitive dependencies exist.

38. Transitive Dependency A transitive dependency involves a functional dependency between two (or more) nonkey attributes. For example, in the Employee relation, CHG_HOUR is functionally dependent on JOB_CLASS. This causes the following problems • A new job class and its associated rate cannot be entered until an employee has been assigned to the job class • If we delete an employee who is the only member of a particular job class, we lose information about that job class and its associated rate

39. Decomposing 2NF Employee Relation to Convert to 3NF EMPLOYEE RELATION Transitive Dependency Emp_Num Emp_Name Job_Class Chg_Hour EMPLOYEE RELATION EMPLOYEE RELATION Emp_Num Emp_Name Job_Class Emp_Num Emp_Name Job_Code JOB RELATION JOB RELATION Job_Class Chg_Hour Job_Code Job_Class Chg_Hour

40. Putting it back together: