Question

1. Cutting Edge Incorporated is a company engaged in the development of computer-aided design (CAD) software packages. The management of Cutting Edge wants to develop a project tracking system to accumulate and report data on current projects, employees and departments. They have given you the following attributes they store on an excel spreadsheet. A project can have several employees assigned to it. An employee belongs to only one department. Make any necessary assumptions and normalize to 3NF.

(ProjectNumber, ProjectName, StartDate, ProjectStatus, (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, JobTitle, ProjectHours))

Answer 1

1). ANSWER :

GIVENTHAT :

1NF

(ProjectNumber, ProjectName, StartDate, ProjectStatus, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, JobTitle, ProjectHours)

2NF

Functional Dependencies:

ProjectNumber -> ProjectName, StartDate, ProjectStatus, ProjectHours

EmployeeNumber -> EmployeeName, JobTitle

DepartmentNumber -> DepartmentName

Tables in 2NF

Project(ProjectNumber , ProjectName, StartDate, ProjectStatus, ProjectHours)

Employee(EmployeeNumber , EmployeeName, JobTitle)

Department(DepartmentNumber , DepartmentName)

3NF

A project can have several employees assigned to it.

EmployeeNumber -> ProjectNumber

An employee belongs to only one department

EmployeeNumber -> DepartmentNumber

Tables in 3NF

Project(ProjectNumber , ProjectName, StartDate, ProjectStatus, ProjectHours)

Employee(EmployeeNumber , EmployeeName, JobTitle,DepartmentNumber,ProjectNumber)

Department(DepartmentNumber , DepartmentName)

underlined are primary keys and italicised are foreign keys.