Database Report: Normalization, ERD, and Database Design

This report delves into the core concepts of database design, specifically focusing on normalization, Entity-Relationship Diagrams (ERDs), and implementation. The report begins by outlining the process of normalization, detailing the steps involved in achieving First, Second, and Third Normal Forms. It then progresses to the creation and understanding of an ERD, providing a visual representation of the database structure and relationships between entities. Finally, the report touches on the practical aspects of database implementation, showcasing the transition from conceptual design to a functional database. References from Elmasri, Navathe (2011) and Carlo Zaniolo (1982) are included, providing a foundation for the presented information. This report serves as a valuable resource for students studying database systems, offering insights into essential aspects of database design and management.

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