Database Scenario The COMPANY database keeps track of a company\'s employees, de
ID: 3811073 • Letter: D
Question
Database Scenario
The COMPANY database keeps track of a company's employees, departments, and projects
The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the department. A department may have several locations.
A department controls a number of projects. Each project has a unique name, a unique number and multiple locations.
We store each employee's name, social security number, address, salary, sex and birthday. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee. An employee must be either full-time or part-time.
We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent's name, sex, birthday and relationship to the employees.
Manages 1:1 Works_For 1:N
Controls 1:N Supervision 1: N
Workd_on M:N Dependents_Of 1:N
Business Rules
One employee must work for one and only one department.
One department must have 4 or more employees.
One employee may manage one and only one department.
One department must be managed by one and only employee.
One department may control one or more projects.
One project must be controlled by one and only one department.
One employee must work on one or many projects.
One project must have one or many employees to work on.
One employee may have one or more dependents.
One dependent must belong to one and only one employee.
One employee may supervise many other employees.
One employee may be supervised by another employee.
Part time employee must be an employee.
Full time employee must be an employee.
Assignment requirements:
1. Show functional dependency analysis for all the tables in the relational schema.
2. Indicate what normalization form each table is.
3. Normalized the tables in the relational schema.
PLEASE COMPLETE ALL THE REQUIREMENTS!!!!
Explanation / Answer
DEPARTMENT
DepartmentName, DepartmentNumber, {Locations}, Manager, ManagerStartDate
Not in 1NF as there is Locations attribute with multiple values
PROJECT
ProjectName, ProjectNumber, Location, ControllingDepartment
1NF as Controlling Department is not functionally dependent on ProjectNumber and ProjectName
EMPLOYEE
SSN,name, Sex, Address, Salary, BirthData, Department, Supervisor, {WorksOn(Project, Hours)}
Not in 1NF as WorksOn is multivalue attribute
DEPENDENT
EmployeeNumber, DependentName, Sex, BirthDate, Relationship
3 NF and DEPENDENT is weak entity which depends on EMPLOYEE
Relationships Mapping
MANAGES (1:1)
EMPLOYEE
DEPARTMENT
Attribute: StartDate
WORKS_FOR (1:N)
DEPARTMENT
EMPLOYEE
CONTROLS (1:N)
PROJECT
DEPARTMENT
SUPERVISION (1:N)
EMPLOYEE
EMPLOYEE
WORKS_ON (M:N)
EMPLOYEE
PROJECT
Attribute: Hours
DEPENDENTS_OF (1:N)
EMPLOYEE
DEPENDENT
(Weak Entity Type)
Normalized Tables
DEPARTMENT(DepartmentNumber, DepartmentName, Manager, ManagerStartDate)
PROJECT(ProjectNumber,ProjectName, DepartmentNo)
EMPLOYEE(SSN,Name, Sex, Address, Salary, BirthDate, DepartmentNumber, Supervisor)
DEPENDENT(EmployeeNumber, DependentName, Sex, BirthDate, Relationship)
MANAGES (EmployeeNumber,DepartmentNumber,StartDate)
WORKS_FOR (DepartmentNumber,EmployeeNumber)
CONTROLS (ProjectNumber,DepartmentNumber)
SUPERVISION (EmployeeNumber,ManagerNumber)
WORKS_ON (EmployeeNumber,PojectNumber, Hours)
DEPENDENTS_OF (EmployeeNumber,DependentName)
(Weak Entity Type)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.