Logical Database Design

1. Logical Database Design Translating Unary and Ternary Relationships

2. Translating Unary One-to-Many Relationships • Create a relation for the entity type • Within the same relation, include a foreign key attribute that references the primary key values; this foreign key must have the same domain as the primary key

3. Example of Unary One-to-Many Relationship MANAGES EMPLOYEE Employee(EmployeeID, Name, JobTitle, ManagerID)

4. Sample Data for Unary One-to-Many Relationship

5. Translating Unary Many-to-Many Relationships • Create a relation for the entity type • Create a relation to represent the many-many relationship. The primary key of the relationship relation consists of two attributes, which both take their values from the primary key of the entity relation. Include nonkey attributes of the relationship.

6. Example of Unary Many-to-Many Relationship COMPONENT ITEM Quantity ITEM(Item_No, Description, Unit_Cost) COMPONENT(Item_No, Component_No, Quantity)

7. Sample Data for Unary Many-to-Many Relationship

8. CourseNo PRE-REQUISITE COURSE CourseName Units COURSE(CourseNo, Name, Units) PREREQUISITE(CourseNo, PrereqNo)

11. Translating Many-Many-ManyTernary Relationships • Create a relation for each of the three entity types • Create a relation to represent the relationship. The default primary key of this relation consists of the three primary key attributes from the participating entities.

12. Example of Ternary Many-Many-Many Relationship (empprojternary.mdb) SKILL EMPLOYEE PROJECT ASSIGNMENT EMPLOYEE(EmployeeID,Name) SKILL(SkillCode, Description) PROJECT(ProjectID,Description,BillingRate) ASSIGNMENT(EmployeeID,SkillCode,ProjectID)

13. Access Implementation of Ternary: (empprojternary.mdb)

14. Ternary versus Binary Relationships A ternary relationship is not the same as three binary relationships. For example, if the project-employee-skill relationship were to be modeled as three binary relationships, as shown on the next slide, we would lose information about which skill a particular employee uses on a particular project.

15. Three Binaries: (empprojbinary.mdb) ATTAINS ASSIGNED_TO PROJECT EMPLOYEE SKILL PROJECT_SKILLS EMPLOYEE(EmployeeID,Name) SKILL(SkillCode, Description) PROJECT(ProjectID,Description,BillingRate) ASSIGNED_TO(EmployeeID,ProjectID) ATTAINS(EmployeeID,SkillCode) PROJECT_SKILL(ProjectID,SkillCode)

16. Access Implementation of Three Binaries: (empprojbinary.mdb)

17. Sample Data for Three Binaries: (empprojbinary.mdb)

18. Relationship Data for Three Binaries: (empprojbinary.mdb)

19. EmployeeProjects Query: (empprojbinary.mdb) • The EmployeeProjects query provides information about • Which projects a particular employee is working on • Which employees are working on a particular project We see that a particular employee can work on many projects (for example, employee1 works on project1 and project2) and that a particular project can have many employees working on it (for example, project1 has employee1 and employee3 working on it).

20. EmployeeSkills Query: (empprojbinary.mdb) • The EmployeeSkills query provides information about • Which skills a particular employee has attained • Which employees have attained a particular skill We see that a particular employee can attain many skills (for example, employee1 knows Java, C++, and Visual Basic), and that a particular skill can be attained by many employees (for example, C++ is known by employee1 and employee2).

21. Project_Skills Query: (empprojbinary.mdb) • The Project_Skills query provides information about • Which skills are used on a particular project • Which projects use a particular skill We see that a particular project can require many skills (for example, project1 requires Java and C++), and that a particular skill can be required by many projects (for example, C++ is required on Project1 and Project3).

22. Information we cannot extract from the binary design: (empprojbinary.mdb) We cannot find out which particular skills a particular employee uses on a particular project. If we execute the following query, we may think we are finding the answer …… But ……………..

23. We get output that indicates that an employee uses all of his/her skills on any project he/she is involved with. For example, employee3 has three skills (Java, Visual Basic, and Access); employee3 works on project1 and project3; the output indicates that employee3 uses all three skills on both of those projects. If you were to add a fourth skill to employee3, that fourth skill would show up for any project employee3 is involved with. With the binary design, there is no way to indicate that employee3 uses Java on project1 and uses Access and Visual Basic on project3.

24. Sample Data for the Ternary Design: (empprojternary.mdb)

25. Relationship Data for the Ternary Design: (empprojternary.mdb) In the ternary design, we capture information about which skills a particular employee uses on a particular project. For example, in the relationship relation, Assignment_Ternary, we can enter the information that employee3 uses Java on project1 and uses Access and Visual Basic on project3:

26. We can then query the database to find out which skills a particular employee uses on a particular project. For example, we can find out which skills employee3 uses on project3:

27. We can also query the database to find out in which project(s) a particular employee uses a particular skill:

28. And we can query the database to find out which employee(s) use a particular skill on a particular project:

29. Therefore, the ternary design provides us with the correct information about which skill(s) a particular employee uses on a particular project.

30. We do lose some information in the ternary design since we only record employee skills in relation to a particular project. For example, employee2 is shown as having only one skill – C++ . It is possible that employee2 knows Visual Basic as well, but this fact has not been recorded because employee2 is not currently using Visual Basic on any project.