Question

2. 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).

a. Determine the functional dependencies that exist in each table given above.

Answer 1

1) Doctor(SSN , FirstName , LastName , Specialty,YearsOfExperience, PhoneNum)

Here , if we know the SSN number ,we can know the doctor first name, last name , speciality , years of experience .

Also, if we know the (Doctor's First Name + Last Name) we can know the doctor's speciality , years of experience.

Therefore ,

SSN --> FirstName, LastName, Specialty,YearsOfExperience
FirstName, LastName --> Specialty,YearsOfExperience

Why not PhoneNum ?

Let us consider a case,

SSN	FirstName	LastName	Speciality	YOE	PhoneNum
17235	John	Wick	Dentist	5	7894561230
17235	John	Wick	Dentist	5	3214569870

In this case , even if we know SSN,First Name,Last Name , Speciality and Years of Experience the phone num
is ambiguous.
Hence SSN--> phoneNum is WRONG.

But,if we know the phone number , we can determine the SSN,First Name ,Last Name of that person and by knowing SSN, we can determine Speciality and years of experience too.

Therefore ,

PhoneNum --> SSN,FirstName, LastName, Specialty,YearsOfExperience

2) Patient(SSN, FirstName, LastName, Address, DOB, PrimaryDoctor_SSN)

Similarly,

SSN --> FirstName, LastName, Address, DOB, PrimaryDoctor_SSN
FirstName, LastName --> Address, DOB, PrimaryDoctor_SSN

3) Medicine(TradeName, UnitPrice, GenericFlag)

TradeName --> UnitPrice, GenericFlag

4) Prescription(Prescription Id, Date, Doctor_SSN, Patient_SSN)

Prescription Id--> Date, Doctor_SSN, Patient_SSN

5) Prescription_Medicine(Prescription Id, TradeName, NumOfUnits)

Prescription Id , TradeName --> NumOfUnits

Because, Let us consider a case

Prescription Id	TradeName	NumOfUnits
1	Amoxicillin	5
1	Acetaminophen.	4

Therefore , it is not sufficient to just know the prescription Id .

By knowing the prescription id and TradeName , you can find the Num Of units .

Note :- In a database , you cannot have more than one entry in a single cell.

Ex :- Even though you have multiple phone numbers for a single person, you have to make separate entries for each phone number.