Database Design And Normalization: Entity-relationship Diagram, SQL Queries, And ERD

Department (Department_ID (PK), Department_Name)

Project (P_Num (PK), Title, Start_Date, *ManagerEmp_ID(FK))

Manger (ManagerEmp_ID(PK,FK), ManagerEmp_Name)

Customer (Cust_Number (PK), First_Name, Last_Name, Address,Email)

Customer_Project(*Cust_Number (PK,FK), DateTime (PK), *P_Num(FK), Payment)

Employee (Employee_ID(PK), First_Name, Last_Name, DOB, address, *P_Num (FK), *Department_ID (FK))

• A department has one or many employees and each employee belong to one and only one department.
• A Project has one or many employees and each Employee work on one project.
• An employee work as a Manger and a manger belong to only one employee.
• A manager has one or many projects and each project related to one and only one manger.
• A customer belongs to one and only one project at a time but a project related to one to many Customers.

Department_ID-> {Department_Name}

P_Num-> {Title, Start_Date, MangerEmp_ID}

ManagerEmp_ID-> {MangerEmp_Name}

Cust_Number-> {First_Name, Last_Name, Address, Email}

Cust_Number, DateTime-> {P_Num, Payment}

Employee_ID-> {First_Name, Last_Name, DOB, Address, P_Num, Department_ID}

Employee_ID-> {MangerEmp_ID} 

SELECT *

FROM Project

WHERE Start_Date between #01/01/2017# and #01/01/2018#;

SELECT [Customer.First_Name] & ” ” & [Customer.Last_Name] AS Customer_Name, Customer.Email, Customer_Project.Payment AS Paid

FROM Customer, Customer_Project

WHERE (((Customer_Project.Payment)>5000) And ((Customer.Cust_Number)=Customer_Project.Cust_Number));

Hint : You need to  join Employee table and Project tables using a suitable query.

SELECT Project.P_Num, Project.Title, Project.ManagerEmp_ID, Employee.Employee_ID, [Employee.First_Name] & ” ” & [Employee.Last_Name] AS Name

FROM Project, Employee

WHERE (((Project.P_Num)=[Employee].[P_Num]));

References: –

C. Frye, Microsoft Access 2010 plain & simple. Sebastopol, Calif.: O’Reilly Media, 2010.

T. O’Leary and L. O’Leary, Microsoft Access 2010. New York, NY: McGraw-Hill, 2011.

J. Cronan, Microsoft Office Access 2010. New York: McGraw-Hill, 2010.

L. Friedrichsen, Microsoft Access 2010 illustrated. Australia: Course Technology/Cengage Learning, 2011.