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 one- to-many
relationship between Manager
and Employee.
To retrieve the name of the manager of a particularemployee, a self join should be performed. A self join is a
join of a table to itself. This table appears twicein 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;