Design the Relational Schema for the E-R diagram that you have drawn for Question 1. The relations must be at least in 3NF. Perform the following on the relations:

Question

a) Enter about 5 sets of meaningful data in each of the relations.

b) Identify the domain of various attributes.

c) Identify the Primary key of every relation. d) Identify the Foreign keys and referential integrity constraints in the relations

Answer 1

The E-R diagram for Question 1 is as follows:


The relational schema for the above E-R diagram is as follows:

**Employee** (employee_id, first_name, last_name, dob, gender, hire_date, salary_per_month, department_id)

**Department** (department_id, department_name, manager_id)

**Project** (project_id, project_name, start_date, end_date, department_id)

**Assignment** (assignment_id, employee_id, project_id, start_date, end_date)

a) Sample data for each relation:

**Employee**

| employee_id | first_name | last_name | dob | gender | hire_date | salary_per_month | department_id |
|-------------|------------|-----------|------------|--------|------------|------------------|---------------|
| 1 | John | Doe | 1980-01-01 | M | 2005-01-01 | 5000 | 1 |
| 2 | Jane | Smith | 1985-06-15 | F | 2008-03-05 | 5500 | 2 |
| 3 | Robert | Johnson | 1990-12-23 | M | 2010-07-12 | 6000 | 2 |

**Department**

| department_id | department_name | manager_id |
|---------------|------------------|------------|
| 1 | Sales | 1 |
| 2 | Marketing | 2 |
| 3 | R&D | 3 |

**Project**

| project_id | project_name | start_date | end_date | department_id |
|------------|--------------|------------|-------------|---------------|
| 1 | ABC | 2010-06-01 | 2010-12-31 | 1 |
| 2 | XYZ | 2011-01-01 | 2011-12-31 | 2 |
| 3 | PQR | 2012-01-01 | 2012-06-30 | 3 |

**Assignment**

| assignment_id | employee_id | project_id | start_date | end_date |
|----------------|-------------|------------|------------|-------------|
| 1 | 1 | 1 | 2010-06-01 | 2010-12-31 |
| 2 | 2 | 2 | 2011-01-01 | 2011-12-31 |
| 3 | 3 | 3 | 2012-01-01 | 2012-06-30 |

b) Domain of various attributes:

| Attribute | Domain |
|-------------------|-----------------------------------------------------|
| employee_id | Integer, Unique |
| first_name | String, 50 |
| last_name | String, 50 |
| dob | Date |
| gender | Char, F or M |
| hire_date | Date |
| salary_per_month | Decimal, (6,2), Non-negative |
| department_id | Integer, Foreign key referencing department_id |
| department_name | String, 50 |
| manager_id | Integer, Foreign key referencing employee_id |
| project_id | Integer, Unique |
| project_name | String, 50 |
| start_date | Date |
| end_date | Date |
| assignment_id | Integer, Unique |

c) Primary Key of each relation:

| Relation | Primary Key |
|-------------|--------------|
| Employee | employee_id |
| Department | department_id |
| Project | project_id |
| Assignment | assignment_id |

d) Foreign Key and referential integrity constraints in the relations:

| Relation | Foreign Key | Referenced table | Referenced Key |
|-------------|----------------------------------|------------------|------------------|
| Employee | department_id | Department | department_id |
| Department | manager_id | Employee | employee_id |
| Project | department_id | Department | department_id |
| Assignment | employee_id (References Employee) | Employee | employee_id |
| Assignment | project_id (References Project) | Project | project_id |