Question

Lab 4 – Logical Design

Introduction:
This lab is designed to practice database design concepts.

Submission:
For the scenario below, draw a logical design draft on the paper, and then draw the design in the computer using PG Modeler software. After that, export the model to PNG photo. Submit to Brightspace a word file that has a photo of the draft design (on the paper) and the PNG photo that is produced from the PG modeler.

Database scenario:
Assume that you were appointed by a multi-national company to design a database for them. The database is required to store data about the employees of the company. For the employee, its required to have the employee ID, name, specialization and age. While, for the department, its required to have the department ID, Name, and manager. Regarding the branch, its required to have branch ID, Name, Telephone and location. However, for the project, its required to have the project ID, name, location and manager. At the same time, its required for the customer to have the customer ID, Name, and contact Info. In addition, for the supplier its required to have the supplier ID, name and contact Info. The department can have many employees but the employee can be assigned to one department only. Each branch can have many departments but the department can be assigned to one branch only. The department can have many projects and the project can be assigned to many departments. Each employee can have many customers but the customer can be assigned to one employee only. The supplier can supply many projects and the project can be supplied by many suppliers.

Answer 1

DATABASE DESIGN:

Branch Branch_ID: bigint (NN) Name: text (NN) Telephone: bigint Location: text Branch_ID FK_Branch_DEPT Department Name: text Manager: bigint Department_ID: bigint (NN) Department_ID FK_Department_Branch FK_Department_EMP FK_Department_Proj Supplier Supplier_ID: bigint (NN) Name: text Contact_Info: text Supplier_ID FK_Supplier_Proj Employee Name: text (NN) Specialization: text Age: integer unsigned Employee_ID: bigint (NN) Employee_ID FK_Employee_Cust FK_Employee_Dept Customer Customer_ID: bigint (NN) Name: text Contact_info: text Customer_ID FK_Customer_EMP Project Project_ID: bigint (NN) Name: text Location: text Manager: text Project_ID FK_Project_Dept FK_Project_Supp