Assignment Topic:

The topic of the assignment is transformation of Entity Relationship Diagram into Relational Schema.

Uploading instructions

Please view the assignment submission process document provided to you by the Virtual University to upload the assignment.

Rules for Marking

It should be clear that your assignment will not get any credit if:

The assignment is submitted after due date

The assignment is copied

Objective

• This assignment has been designed so that you understand the concept of transformation of Entity Relationship Diagram into Relational Schema.

Assignment

Assumptions:

• A person can have many addresses and an address can belong to many people
• A physician is responsible for many patients, but a patient can only have one responsible physician
• A physician makes many visits, but a visit can only be made by one physician
• An out-patient may be visited many times, but a particular visit is only to one patient at a time
• A resident may be assigned a bed. A bed does not have to be assigned to a resident (may be empty)
• A nurse may be assigned to many care centers, and a care center may have many nurses assigned to it.
• A nurse may be in charge of many care centers, but only one nurse can be in charge of a particular care center.
• A facility is located at a certain address. No two facilities can be located at the same address.
• A person may be an employee, volunteer, patient, and physician
• An employee either be a staff, technician, or nurse, but not any two or three at the same time.
• A patient is either an "Out Patient" or resident, but not both at the same time.
• A facility can either be a laboratory or care center, but not both at the same time.
• A technician/volunteer may have many skills, a particular skill can be had by many technicians/volunteers.

A technician may be assigned to many laboratories, a particular laboratory may have many technicians assigned to it.

Given the above scenario transform the given ER-Diagram into a relational schema and, show relationships between the tables and identify the constraints existing between entities.