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:

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

1 answer

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

![ER diagram for Question 1](https://i.imgur.com/nRbNylP.png)

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 |
Similar Questions
    1. answers icon 1 answer
  1. Which type of database does not use a rigid schema?Group of answer choices Cloud Relational NoSQL On-premises
    1. answers icon 1 answer
    1. answers icon 1 answer
  2. DefinitionGender schema helps the children make sense of their world. A schema for a boy would be “boys play with trucks and
    1. answers icon 1 answer
more similar questions