CS2102 Tutorial #3: Entity Relationship Solution

CS2102: Database Systems
Tutorial #3: Entity Relationship Model Week 5
1 Discussions
The following questions are to be discussed during tutorial. All answers will be released with explanation.
The following are three di↵erent ER designs for the application:

ERD : Design A (ternary) ERD : Design B (binary)
1

2. (ER Translation) Consider the ER diagrams shown below.
ERD : ER 1
ERD : ER 2
Translate each ER diagram into a relational schema. In your translation • enforce as many constraints captured in the ER diagram as possible
• do not enforce additional constraints that are not captured in the ER diagram
ERD : ER 3
• assume that all the attributes have integer domain
2 Challenge
The answers to the following questions is given without explanation. Please discuss them on Canvas.
1. The Prescription-R-X chain of pharmacies has o↵ered to give you a free lifetime supply of medicine if you design its database. Given the rising cost of health care, you agree. Here’s the information that you gather:
1. Patients are identified by an SSN. Their names, addresses and ages must also be recorded.
2. Doctors are identified by an SSN. Their name, specialty and years of experience must also be recorded.
3. Each pharmaceutical company is identified by name and has a phone number.
4. For each drug, the trade name and formula must be recorded.
5. Each drug is sold by a given pharmaceutical company and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.
6. Each pharmacy has a name, address and phone number.
7. Every patient has a primary physician.
8. Every doctor is the primary physician of at least one patient.
9. Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies and the price could vary from one pharmacy to another.
Questions:
(a) Consider the ER diagram shown on the next page for Prescriptions-R-X. What are the constraints that are not captured by this design? Modify the ER design to capture as many of the constraints as possible.
(b) Translate your ER design in part (a) into a relational schema using SQL (assume reasonable data types for the domain constraints). Your solution should capture as many of the application’s constraints as possible. Identify any constraints that are not captured by your relational schema.
(c) How would your design in part (a) change if each drug must be sold at a fixed price by all pharmacies?
(e) Suppose that pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract but the contract supervisor can change over the lifetime of the contract. Supervisors are identified by an SSN and their start dates must be recorded. Modify your ER design in part (a) to capture this additional requirement.
(f) Translate your ER design in part (e) into a relational schema. Identify any constraints that are not captured by your relational schema.