Question

Design an ER Diagram for the given problem. Link the tables that are related and implement your design using MySQL. Insert at least a minimum of 5 records. Ensure that the data to be added are related to other tables.

Post here the screenshot of the following:

• Entity Relationship Diagram with attributes, PK, and FK.
• Use the "describe" command to view the structure of the tables.
• Use the "select" command to view the contents of the tables.

Company ABC has the following business rules. A department employs many employees, but each employee is employed by only one department. A division operates many departments An employee may be assigned many projects, and a project may have many employees assigned to it. Each department is identified by a department code, department name and phone number are kept in the system for every department. For each division, the division name and division type (Sales, Marketing, or Research ) are kept in the system. For the employee, name, contact number, address, and degree must be kept in the system. For the project, project title, duration(no. of days), and status( completed, canceled, or in progress) are stored in the system.

Answer 1

ER diagram is represented as:

The primary keys in the tables are:

Key                  Table

dep_code       department

ename            employee

div_name        division

title                  project

Table Employee:
Describing the structure of employee table:

Statement:
DESCRIBE employee;

Contents of employee table:

Statement:
Select * from employee;

Table Department:
Describing the structure of department table:

Statement:
DESCRIBE department;

Contents of department table:

Statement:
Select * from department;

Table Division:
Describing the structure of division table:

Statement:
DESCRIBE division;

Contents of Division table:

Statement:
Select * from division;

Table Project:
Describing the structure of Project table:

Statement:
DESCRIBE project;

Contents of Project table:

Statement:
Select * from project;