MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
CS406 ASSIGNMENT 2 SOLUTION SPRING 2025
Due Date: 23-June-2025
Total Marks: 20
DO NOT COPY PASTE THE SAME
FOR PAID SOLUTION CONTACT US
WHATSAPP 03162965677
Assignment Submission Instruction:
Microsoft Word (doc or .docx) will be uploaded on VULMS.
Question No. 1
Mark 10
A small hospital wants to manage patients, doctors, and appointments. To begin with, they want
a simple database that stores:
1. Patient details
2. Doctor details
3. Appointments
You are given a table called Patient records that is not normalized yet. Your task is to convert
this table into 1NF,2NF and finally 3NF by ensuring that all attributes contain
Only atomic values
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
No partial dependency
No transitive dependencies (non-key attributes must depend only on the primary key).
This normalization will generate at least 3 tables from this one table.
Pati Pati Doc Appoi
Patie Doctor Doct Re
ent entP tor ntme
ntAd Special orCo aso SOLUTION
Na hon Na ntDat
dress ization ntact n
me e me e
He Convert to First Normal Form (1NF)
Ali 0312 Dr. 0300 art
Laho Cardiol 2025- 1NF Table:
Kha 0001 Ah 1112 Ch
re ogist 04-10
n 111 med 222 eck
up
Sara 0333 Dr. 0322 He
Islam Neurol 2025-
Noo 0002 Fati 3334 ada
abad ogist 04-11
r 222 ma 444 che
BP
Bilal 0345 Dr. 0300
Kara Cardiol 2025- Ch
Ah 0003 Ah 1112
chi ogist 04-15 eck
mad 333 med 222
up
Meh 0313 Dr. 0322 Mi
Laho Neurol 2025-
wish 0004 Fati 3334 gra
re ogist 04-17
Ali 444 ma 444 ine
To
Zain 0322 Dr. 0300
Mult 2025- oth
ab 0005 Tah Dentist 9998
an 04-20 ach
Rauf 555 a 888
e
Patient Patient PatientA Doctor DoctorSpeci DoctorC Appointm Reaso
Name Phone ddress Name alization ontact entDate n
Ali 0312000 Lahore Dr. Cardiologist 0300111 2025-04-10 Heart
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
Khan 1111 Ahmed 2222 Check
up
Sara 0333000 Islamaba Dr. Neurologist 0322333 2025-04-11 Heada
Noor 2222 d Fatima 4444 che
Bilal 0345000 Karachi Dr. Cardiologist 0300111 2025-04-15 BP
Ahmad 3333 Ahmed 2222 Check
up
Mehwis 0313000 Lahore Dr. Neurologist 0322333 2025-04-17 Migra
h Ali 4444 Fatima 4444 ine
Zainab 0322000 Multan Dr. Dentist 0300999 2025-04-20 Tooth
Rauf 5555 Taha 8888 ache
Now, this table is in 1NF since each attribute contains only atomic values, and there are no
repeating groups.
Convert to Second Normal Form (2NF)
Tables in 2NF:
1. Patients Table:
PatientID PatientName PatientPhone PatientAddress
1 Ali Khan 03120001111 Lahore
2 Sara Noor 03330002222 Islamabad
3 Bilal Ahmad 03450003333 Karachi
4 Mehwish Ali 03130004444 Lahore
5 Zainab Rauf 03220005555 Multan
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
2. Doctors Table:
DoctorID DoctorName DoctorSpecializatio DoctorContact
n
1 Dr. Ahmed Cardiologist 03001112222
2 Dr. Fatima Neurologist 03223334444
3 Dr. Taha Dentist 03009998888
3. Appointments Table:
AppointmentI PatientID DoctorID AppointmentDat Reason
D e
1 1 1 2025-04-10 Heart Checkup
2 2 2 2025-04-11 Headache
3 3 1 2025-04-15 BP Checkup
4 4 2 2025-04-17 Migraine
5 5 3 2025-04-20 Toothache
Now, the Patients Table contains details specific to the patient, the Doctors Table stores the
doctor-specific information, and the Appointments Table tracks appointments. This removes
partial dependencies, making it 2NF.
Convert to Third Normal Form (3NF)
3NF Requirement: The table must be in 2NF, and there must be no transitive dependencies (i.e.,
non-key attributes must depend only on the primary key).
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
In the current structure, there are no transitive dependencies since all non-key attributes in each
table are directly dependent on their respective primary keys. For example, DoctorSpecialization
and DoctorContact depend only on DoctorID, and Reason depends on AppointmentID, which is
unique for each appointment.
The structure is already in 3NF
Question No. 2
Marks 10
Write an SQL statement to create a table name “Patient Appointment” that contain list of all
patients and their appointments:
1. PT_ID (Patient ID)
2. PT_Contact (Patient Phone Number)
3. Doc_Name (Doctor Name)
4. App_Date (Appointment date)
Instructions:
Write SQL Statement.
Ensure that you choose the appropriate primary key from the attributes and SQL data types for
each attribute
SOLUTION
CREATE TABLE PatientAppointment (
PT_ID INT PRIMARY KEY, -- Patient ID
PT_Contact VARCHAR(15), -- Patient Phone Number
Doc_Name VARCHAR(100), -- Doctor Name
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
App_Date DATE -- Appointment Date
);
REGARD - SARIM
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
MORE SOLUTIONS, HANDOUTS AND PAST PAPERS FREELY VISIT
VUAnswer.pk
WHATSAPP +923162965677
PLEASE NOTE:
Don't copy-paste the same answer.
Make sure you can make some changes to your solution file before
submitting copy paste solution will be marked zero.
If you found any mistake then correct yourself and inform me.
Before submitting an assignment must check your assignment requirement
file.
If you need some help or question about file and solutions feel free to ask.
FOR FREE ASSIGNMENTS SOLUTIONS VISIT
VUAnswer.pk
FOR PAID ASSIGNMENTS CORRECT SOLUTION
WhatsApp: +923162965677
