Question

Case Study 4

A relational database is to be designed for a medium sized Company dealing with industrial applications of computers. The Company delivers various products to its customers ranging from a single application program through to complete installation of hardware with customized software. The Company employs various experts, consultants and supporting staff. All personnel are employed on long‐ term basis, i.e. there is no short‐term or temporary staff. Although the Company is somehow structured for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter‐disciplinary way. For each project a project team is selected, grouping employees from different departments, and a Project Manager (also an employee of the Company) is appointed who is entirely and exclusively responsible for the control of the project, quite independently of the Company's hierarchy.

Description: The following is a brief statement of some facts and policies adopted by the Company.

• Each employee works in some department.

• An employee may possess several skills

• Every manager (including the MD) is an employee

• A department may participate in none/one/many projects.

• At least one department participates in a project.

• An employee may be engaged in none/one/many projects

• Project teams consist of at least one member.

Answer 1

Explanation:

As per the Case Study, the tables their attributes and the constraints are given below :

Following are the table in the database :

1. DEPARTMENT

2. EMPLOYEE

3. PROJECT

4. EMPLOYEE_SKILLS

5. WORKS_ON

We have following constraint type :

1. Primary key

2. Foreign key

3. Check condition

4. NOT NULL

5. UNIQUE

Constraints for the table are given below:

Table : DEPARTMENT

Column_name	Data Type	Contraint_type
DEPT_ID	INT	PRIMARY KEY
DEPT_NAME	VARCHAR(50)	NOT NULL
MGR_ID	INT	FOREIGN KEY (references from EMPLOYEE table)

Table : EMPLOYEE

Column_name	Data Type	Contraint_type
EMP_ID	INT	PRIMARY KEY
FNAME	VARCHAR(50)	NOT NULL
MINIT	VARCHAR(50)	NOT NULL
LNAME	VARCHAR(50)	NOT NULL
SALARY	INT	NOT NULL
DEPT_ID	INT	FOREIGN KEY (references from DEPARTMENT table)

Table : PROJECT

Column_name	Data Type	Contraint_type
PRJ_ID	INT	PRIMARY KEY
PRJ_TITLE	VARCHAR(50)	NOT NULL
PRJ_MGR_ID	INT	FOREIGN KEY (references from EMPLOYEE table)

Table : EMPLOYEE_SKILLS

Column_name	Data Type	Contraint_type
SKILL_NAME	VARCHAR(50)	NOT NULL
EMP_ID	INT	FOREIGN KEY (references from EMPLOYEE table)

Table : WORKS_ON

Column_name	Data Type	Contraint_type
EMP_ID	INT	FOREIGN KEY (references from EMPLOYEE table)
PRJ_ID	INT	FOREIGN KEY (references from PROJECT table)

As per the Case Study the ER Diagram and Relational Schema are given below :

ER Diagram :

Relational Schema :