QUESTION
Consider the following relations:
Doctor(SSN, FirstName, LastName, Specialty,YearsOfExperience, PhoneNum)
Patient(SSN, FirstName, LastName, Address, DOB, PrimaryDoctor_SSN)
Medicine(TradeName, UnitPrice, GenericFlag)
Prescription(Prescription Id, Date, Doctor_SSN, Patient_SSN)
Prescription_Medicine(Prescription Id, TradeName, NumOfUnits)
Note: The Medicine relation has attributes, trade name, unit price, and whether or not the medicine is generic (True or False).
- Determine the functional dependencies that exist in each table given above.
Example answer for - Doctor table:
Doctor(SSN, FirstName, LastName, Specialty,YearsOfExperience, PhoneNum)
Write Functional dependency exists in this table:
SSN à FirstName, LastName, Specialty,YearsOfExperience, PhoneNum, Patient_SSN
- After determining the functional dependencies, convert these tables to an equivalent collection of tables that are in third normal form (3NF). If they are already in 3NF justify your answer.
Example: Answer for table Doctor
Doctor(SSN, FirstName, LastName, Specialty,YearsOfExperience, PhoneNum, *Patient_SSN)
1NF: No repeating groups. So it is 1NF.
2NF : check Partial dependency on the primary key, so the relation is in 2NF.
3NF: Check non primary key dependent on another non-primary key attribute
No So it is in 3NF
Repeat this for other tables.
- Write SQL queries to answer following questions
- List the trade names of all medicine with unit price less than $50.
Answer-
SELECT*
FROM medicine
WHERE unit price <$50;
- List the first and last name of patients whose primary doctor named ʻJohn Smithʼ.
Answer-
SELECT FirstName, LastName
FROM Patient
Where FirstName = ‘John’ AND LastName = ‘Smith’ ;
- List the first and last name of the doctor who is a primary doctor of patient “Sam Rapke”
- List TradeName and NumOfUnits of all medicines written in prescriptions with NumOfUnits is greater than the average NumOfUnits
- List the SSN of patients who have ʻAspirinʼ and ʻVitaminʼ trade names in one prescription.
- Write the SQL command you would use to add a new record to a table “Prescription”
Consider the following relations:
Doctor(SSN, FirstName, LastName, Specialty,YearsOfExperience, PhoneNum)
Patient(SSN, FirstName, LastName, Address, DOB, PrimaryDoctor_SSN)
Medicine(TradeName, UnitPrice, GenericFlag)
Prescription(Prescription Id, Date, Doctor_SSN, Patient_SSN)
Prescription_Medicine(Prescription Id, TradeName, NumOfUnits)
Note: The Medicine relation has attributes, trade name, unit price, and whether or not the medicine is generic (True or False).
- Determine the functional dependencies that exist in each table given above.
Example answer for - Doctor table:
Doctor(SSN, FirstName, LastName, Specialty,YearsOfExperience, PhoneNum)
Write Functional dependency exists in this table:
SSN à FirstName, LastName, Specialty,YearsOfExperience, PhoneNum, Patient_SSN
- After determining the functional dependencies, convert these tables to an equivalent collection of tables that are in third normal form (3NF). If they are already in 3NF justify your answer.
Example: Answer for table Doctor
Doctor(SSN, FirstName, LastName, Specialty,YearsOfExperience, PhoneNum, *Patient_SSN)
1NF: No repeating groups. So it is 1NF.
2NF : check Partial dependency on the primary key, so the relation is in 2NF.
3NF: Check non primary key dependent on another non-primary key attribute
No So it is in 3NF
Repeat this for other tables.
- Write SQL queries to answer following questions
- List the trade names of all medicine with unit price less than $50.
- List the first and last name of patients whose primary doctor named ʻJohn Smithʼ.
- List the first and last name of the doctor who is a primary doctor of patient “Sam Rapke”
- List TradeName and NumOfUnits of all medicines written in prescriptions with NumOfUnits is greater than the average NumOfUnits
- List the SSN of patients who have ʻAspirinʼ and ʻVitaminʼ trade names in one prescription.
- Write the SQL command you would use to add a new record to a table “Prescription”
Public Answer
