Philandrio Inc. is one of the largest drug manufacturing companies in the world. It has many offices
worldwide. Each office has a manpower of approximately 14,000. The managers themselves are
employees in the company. You are building a database for the company’s Human Resources
Department (HRD). You want to track the following attributes for each employee, and at the same
time, eliminate duplicate entries and minimize data storage wherever possible.
FirstName
MiddleName
LastName
EmployeeIdentityNumber
Address
Date_ of_ Hire
Department
Salary
ManagerIdentityNumber
Which of the following is the appropriate table detail sufficient to track the above attributes,
correctly and efficiently?
A.
Only one table, Employee
B.
Three tables, Employee, Manager, and a table to link both these entities
C.
Two tables, Employee and Manager
D.
Either two or three tables
Explanation:
Only one table is sufficient to keep track of all the above attributes of each employee, because all
these attributes refer to the Employee
entity. However, there can be many employees working under one manager, which requires a oneto-many relationship between Manager
and Employee.
To retrieve the name of the manager of a particular employee, a self join should be performed.
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by
table aliases that qualify column names in
the join condition.
Example:
SELECT A.EmployeeIdentityNumber, A.LastName, B.LastName
FROM Employees A, Employees B
WHERE A.ManagerIdentityNumber=B.EmployeeIdentityNumber;