Transcript Unary and Ternary Relationships - N

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(EmployeeID, Name, JobTitle, ManagerID)
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
CourseNo
CourseName
COURSE
PRE-REQUISITE
Units
COURSE(CourseNo, Name, Units)
PREREQUISITE(CourseNo, PrereqNo)
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.
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)
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(EmployeeID,Name)
SKILL(SkillCode, Description)
PROJECT(ProjectID,Description,BillingRate)
ASSIGNED_TO(EmployeeID,ProjectID)
ATTAINS(EmployeeID,SkillCode)
PROJECT_SKILL(ProjectID,SkillCode)
SKILL
Access Implementation of Three
Binaries: (empprojbinary.mdb)
Sample Data for Three Binaries:
(empprojbinary.mdb)
Relationship Data for Three Binaries:
(empprojbinary.mdb)
EmployeeProjects Query: (empprojbinary.mdb)
The EmployeeProjects
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,
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).
EmployeeSkills Query: (empprojbinary.mdb)
The EmployeeSkills 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,
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).
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, 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).
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, 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.
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
employee3 uses Java on project1 and uses Access and Visual Basic on
project3:
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:
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,
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.