Logical Database Design Translating Unary and Ternary Relationships

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

Example of Unary One-to-Many Relationship EMPLOYEE MANAGES Employee(Employee. ID, Name, Job. Title, Manager. ID)

Sample Data for Unary One-to-Many Relationship

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.

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

Sample Data for Unary Many-to-Many Relationship

Course. No Course. Name COURSE PRE-REQUISITE Units COURSE(Course. No, Name, Units) PREREQUISITE(Course. No, Prereq. No)

Translating Many-Many. Ternary 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.

Example of Ternary Many-Many Relationship (empprojternary. mdb) SKILL EMPLOYEE PROJECT ASSIGNMENT EMPLOYEE(Employee. ID, Name) SKILL(Skill. Code, Description) PROJECT(Project. ID, Description, Billing. Rate) ASSIGNMENT(Employee. ID, Skill. Code, Project. ID)

Access Implementation of Ternary: (empprojternary. mdb)

Ternary versus Binary Relationships A ternary relationship is not the same as three binary relationships. For example, if the projectemployee-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.

Three Binaries: (empprojbinary. mdb) ATTAINS ASSIGNED_TO PROJECT EMPLOYEE PROJECT_SKILLS EMPLOYEE(Employee. ID, Name) SKILL(Skill. Code, Description) PROJECT(Project. ID, Description, Billing. Rate) ASSIGNED_TO(Employee. ID, Project. ID) ATTAINS(Employee. ID, Skill. Code) PROJECT_SKILL(Project. ID, Skill. Code) SKILL

Access Implementation of Three Binaries: (empprojbinary. mdb)

Sample Data for Three Binaries: (empprojbinary. mdb)

Relationship Data for Three Binaries: (empprojbinary. mdb)

Employee. Projects Query: (empprojbinary. mdb) The Employee. Projects query provides information about (1) Which projects a particular employee is working on (2) Which employees are working on a particular project We see that a particular employee can work on many projects (for example, employee 1 works on project 1 and project 2) and that a particular project can have many employees working on it (for example, project 1 has employee 1 and employee 3 working on it).

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

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

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 ……………. .

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

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

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 employee 3 uses Java on project 1 and uses Access and Visual Basic on project 3:

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 employee 3 uses on project 3:

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

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

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

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