Database Design and Normalization Assignment for ECM38IS Module

This assignment solution addresses a database design problem for Vision Technologies, an engineering firm. The solution begins with an Entity-Relationship Diagram (ERD) based on provided business rules, encompassing employees, departments, vendors, projects, and skills. It details the relationships between entities, such as employees working in departments and projects, and vendors supplying departments. The solution then proceeds to normalize the relations up to the Third Normal Form (3NF), creating a relational structure. The normalized relations include Employee, Marriage_partner, Skill, EmployeeSkills, Department, Vendor, DepartmentVendor, City, Project, ProjectSkills, and ProjectEmployees. Each relation's attributes and primary/foreign keys are clearly defined, ensuring data integrity and efficiency for the database system.

Question 1 ERD
Figure 1: ERD
Business rules
The ERD shown on the figure 1 above was shown was modelled on the basis of the following business
rules;
 An employee can have a marriage partner who is also an employee in the organization. Its not
compulsory for an employee to have a marriage partner. The only marriage partner records that
are recorded in the database are those which the marriage is between employees working in
the organization. Employees with marriage partners not working for the organization are not
recorded.
 An employee works for only one department. A department can have one or many employees
working in the same department.
 A department has one or more vendors supplying items to the department. A vendor can supply
items to one or more departments.

 A project is done for one and only once city. A city can have one or more projects.
 A project requires one or more skills.
 An employee works in one or more projects but can only work in multiple projects in condition
that the projects are in different cities.
 An employee has one or more skills. An employee works on a project if he or she has one or
more skills that are required to undertake the project.
Question 2:
Performing normalization up to 3NF for all the relations in the ERD above can help achieve the relations
required for a relational structure. The relations in 3NF as derived from the ERD are;
 Employee (empNum, name, dob, jobTitle, department)
Primary key (empNum)
Foreign key (department) references department (departmentName)
 Marriage_partner (empNUm, partner)
Primary key (empNum, partner)
Foreign key (empNum) references employee (empNum)
Foreign key (partner) references employee (empNum)
 Skill (skillID, description)
Primary key (skillID)
 EmployeeSkills (empNum, skillID)
Primary key (empNum, skillID)
Foreign key (empNum) references employee (empNum)
Foreign key (skillID) references skill (skillID)
 Department (departmentName, phoneNumber)
Primary key (departmentName)
 Vendor (vendorID, name, address)
Primary key (vendorID)
 DeparmentVendor (departmentName, vendorID, lastMeetingDate)
Primary key (departmentName, vendorID)
Foreign key (departmentName) references department (departmentName)
Foreign key (vendorID) references vendor (vendorID)
 City (name, state, population)
Primary key (name)
 Project (projectID, cost, clientName, city)
Primary key (projectID)
Foreign key (city) references city (name)
 ProjectSkills (projectID, skillID)
Primary key (projectID, skillID)
Foreign key (projectID) references project (projectID)
Foreign key (skillID) references skill (skillID)
 ProjectEmployees (projectID, empNum)
Primary key (projectID, empNum)
Foreign key (projectID) references project (projectID)

Foreign key (empNum) references employee (empNum)
Question 3