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
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 |
To design the relational schema, I would need the E-R diagram from Question 1. Please provide me with the E-R diagram, and I will assist you in designing the relational schema according to your requirements.
To design the relational schema for the E-R diagram, follow these steps:
Step 1: Identify Entities
Identify the entities in the E-R diagram and convert them into separate tables in the relational schema. Each table will represent one entity.
Step 2: Identify Attributes
For each entity, identify the attributes and represent them as columns in the corresponding table.
Step 3: Define Primary Keys
Identify the primary keys for each table. The primary key uniquely identifies each record in a table. It can be a single attribute or a combination of attributes.
Step 4: Define Relationships
Identify the relationships between entities and represent them using foreign keys. Foreign keys create the relationship between tables by referencing the primary key of another table.
Step 5: Normalize Tables
Normalize the tables to ensure they are in at least the third normal form (3NF). This involves eliminating redundancy and ensuring that each attribute is directly dependent on the primary key.
Once you have completed the above steps, you can perform the following actions on the schema:
a) Entering Meaningful Data
Enter about five sets of meaningful data in each table. This data should be representative of the real-world entities and their relationships.
b) Identifying Attribute Domains
Identify the domain of each attribute. The domain refers to the set of possible values that an attribute can take. For example, if an attribute represents age, the domain can be integers from 0 to 120.
c) Identifying Primary Keys
Identify the primary key for each table. The primary key uniquely identifies each record in the table. It should be one or more attributes that can uniquely identify each record.
d) Identifying Foreign Keys and Referential Integrity Constraints
Identify the foreign keys in each table. Foreign keys create a relationship between tables by referencing the primary key of another table. Ensure that referential integrity constraints are applied to maintain data consistency.
By following these steps, you will be able to design the relational schema for the E-R diagram and perform the required actions on the relations.