Database Technologies Assignment

This assignment focuses on database technologies concepts. It requires students to analyze functional dependencies in a set of tables, normalize the database schema to various normal forms (1NF, 2NF, 3NF), and create an Entity-Relationship Diagram (ERD) representing the relationships between entities. The assignment also includes examples of database implementations for specific tables like Customer and Project, drawing upon relevant references.

Student ID –
Date -
Assignment 2
Database Technologies
Module Tutor –
1 | P a g e

Report
1 D) Functional Dependency
Employee (EmployeeID, FirstName, LastName, Address, DOB, ContactNumber)
EmployeeID  FirstName, LastName, Address, DOB, ContactNumber
Customer (Cust_Number, FirstName, LastName, Address, PNum, ProjectTitle, Payment)
Cust_Number  FirstName, LastName, Address, PNum, Payment
PNum  ProjectTitle, Payment
Cust_Number, PNum  Payment
Project (PNum, ProjectTitle, StartDate, ManagerID, ManagerNumber, ManagerContactNumber)
PNum  ProjectTitle, StartDate, ManagerID
ManagerID  ManagerNumber, ManagerContactNumber
ProjectEmployee (EmployeeID, PNum)
EmployeeID  PNum
Department (DepartmentID, DepartmentName)
DepartmentID  DepartmentName
DepartmentEmployee (EmployeeID, DepartmentID)
EmployeeID  DepartmentID
ProjectSubmission (Cust_Number, SubmissionDate, PNum)
Cust_Number, SubmissionDate  PNum
2 | P a g e

1 E) Normalised Tables
First Normal Form
First normal form is obtained by the below steps-
- Eliminate repeated groups from the tables.
- Recognise primary key in new tables.
Second Normal Form
Second normal form is obtained by the below steps-
- All tables are in first normal form.
- All fields depend upon the primary key.
Third Normal Form
Third normal form is obtained by the below steps-
- All tables are in second normal form.
- Eliminate transitive dependencies.
Employee (EmployeeID, FirstName, LastName, Address, DOB, ContactNumber)
Customer (Cust_Number, FirstName, LastName, Address)
Project (PNum, ProjectTitle, StartDate)
Manager (ManagerID, ManagerNumber, ManagerContactNumber)
ProjectEmployee (EmployeeID, PNum)
Department (DepartmentID, DepartmentName)
DepartmentEmployee (EmployeeID, DepartmentID)
DepartmentProject (DepartmentID, PNum)
ProjectSubmission (Cust_Number, SubmissionDate, PNum)
3 | P a g e

2) Entity-Relationship Diagram
4 | P a g e

3) Database Implementation
Customer Table
Project Table
(Elmasri, Navathe 2011)
(Carlo Zaniolo 1982)
5 | P a g e

References
Elmasri, Navathe, Fundamental of Database Systems Sixth Edition, 2011
Carlo Zaniolo, A New Normal Form for the Design of Relational Database Schemata, September 1982
6 | P a g e