Question

Create a Relational Schema for the following scenario. Include all primary and foreign keys and list any assumptions you make.  A research facility has many chemists who work on one or more projects.  Attributes of chemists include a unique employee id, a name, rank and email.  Each project has one chemist who is given the title of project leader. A chemist may be the leader of several projects.  Attributes of a project include a unique project number, start date and description.  Each project is assigned to one or more laboratories but each laboratory can house at most one project at any time.  Attributes of a laboratory include a building name, room number, capacity and type. The combination of building name and room number is unique.  Chemists can borrow equipment from the stockroom. Each time a piece of equipment is borrowed the check-out date is recorded and when it is returned a return date is recorded. The return date is set to 00-00-0000 until the piece of equipment has been returned. Historical data on the equipment is maintained and a chemist can check out the same piece of equipment on multiple dates.  Attributes of equipment include a unique serial number, description and cost.

Answer 1

Answer:

See explaination

Explanation:

Some of the Important Observation used in Entity Relational Diagram.

1. A Chemist can be involved one project or many projects

2. A project can hire just one or many chemists.

3. A chemist can lead over one or many projects

4. A project can have only one project leader chemist.

5. A project can be done in 1 or many laboratories.

6. A laboratory can host 0 or 1 projects.

7. A laboratory can have 1 or many equipments.

8. An Equipment can be in 1 or 0 laboratories.

9. A chemist can have 0 or many types of equipments.

10. An Equipment can have 0 or 1 chemist at a time

Notations:

PK=Primary Key:

A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number

FK=Foreign Key:

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them

Composite key:

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it is taken individually it does not guarantee uniqueness