“Database System Lab Assignment”
Muhammad Bilal
Syed Rameel Obaid
Submitted By
Muhammad Arshad Zaman
Subject Database System Lab
Semester 3rd Semester-Section A
RC # R-BSCS-2258409
R-BSCS-2258405
R-BSCS-2258401
Dated 27/4/2026
Topic Smart Hospital Management System
Submitted To Ms Habiba Saeed
NATIONAL UNIVERSITY OF MODERN LANGUAGES, RAWALPINDI.
Department Of Computer Science
Database System Lab Assignment
“Hospital Management System”
1. SYSTEM OVERVIEW
A Hospital Management System manages doctors, patients, appointments, prescriptions,
medicines, payments, and medical records. It streamlines hospital operations by maintaining
organized data relationships between all departments and entities.
1.1. SYSTEM OBJECTIVES
Maintain organized records of all doctors, patients, and departments
Efficiently manage appointment scheduling between patients and doctors
Digitally store and track prescriptions and medicines
Manage payment records linked to appointments
Keep permanent medical history for every patient
Eliminate data duplication through proper normalization
1.2. SYSTEM REQUIREMENTS
Store and manage records of Doctors, Patients, and Departments
Allow patients to book appointments with doctors
Generate and store prescriptions after every appointment
Maintain complete medical history of every patient
Track payment records linked to appointments
Manage medicine stock and manufacturer details
Database must be 3NF compliant — no redundancy or data duplication
1
2. WHAT ARE ENTITIES & ATTRIBUTES?
Entity:
An Entity is a real-world object or concept about which data is stored in a database. Think of it as
a table in a database that represents something meaningful.
Example: In a hospital, a Doctor is an entity because we need to store information about every
doctor separately.
Attribute:
An Attribute is a property or characteristic that describes an entity. Think of it as a column in a
table that holds specific information about that entity.
Example: A Doctor entity has attributes like Full_Name, Specialization, Phone, and Email. These
describe what information we store about each doctor.
2.1. Types of Attributes:
Type Description Example
Simple Attribute Cannot be divided further Phone, Age
Composite Attribute Can be divided into smaller parts Full_Name → First + Last
Key Attribute Uniquely identifies each record Doctor_ID (PK)
Derived Attribute Calculated from another attribute Age (from DOB)
Multi-valued Can hold multiple values Multiple phone numbers
Primary Key (PK):
A Primary Key is a unique identifier for each record in a table. No two records can have the same
PK value and it can never be NULL.
Foreign Key (FK):
A Foreign Key is an attribute in one table that refers to the Primary Key of another table. It
creates a link between two entities.
2
3. ENTITIES & ATTRIBUTES
3.1. Department
Attribute Type Key
Dept_ID INT PK
Dept_Name VARCHAR(100) —
Description TEXT —
Head_Doctor_ID INT FK
3.2. Doctor
Attribute Type Key
Doctor_ID INT PK
Full_Name VARCHAR(100) —
Specialization VARCHAR(100) —
Phone VARCHAR(15) —
Email VARCHAR(100) —
License_No VARCHAR(50) —
Dept_ID INT FK
3.3. Patient
Attribute Type Key
Patient_ID INT PK
Full_Name VARCHAR(100) —
DOB DATE —
Gender VARCHAR(10) —
Phone VARCHAR(15) —
Email VARCHAR(100) —
Address TEXT —
Blood_Group VARCHAR(5) —
3.4. Appointment
Attribute Type Key
Appt_ID INT PK
Appt_Date DATE —
Appt_Time TIME —
Status VARCHAR(20) —
Reason TEXT —
Patient_ID INT FK
Doctor_ID INT FK
3
3.5. Prescription
Attribute Type Key
Pres_ID INT PK
Diagnosis TEXT —
Date_Issued DATE —
Notes TEXT —
Appt_ID INT FK
3.6. PrescriptionDetail
Attribute Type Key
PD_ID INT PK
Dosage VARCHAR(50) —
Frequency VARCHAR(50) —
Duration_Days INT —
Pres_ID INT FK
Med_ID INT FK
3.7. Medicine
Attribute Type Key
Med_ID INT PK
Med_Name VARCHAR(100) —
Type VARCHAR(50) —
Unit_Price DECIMAL(10,2) —
Stock_Qty INT —
Mfr_ID INT FK
3.8. Manufacturer
Attribute Type Key
Mfr_ID INT PK
Mfr_Name VARCHAR(100) —
3.9. Payment
Attribute Type Key
Payment_ID INT PK
Amount DECIMAL(10,2) —
Method VARCHAR(30) —
Payment_Date DATE —
Status VARCHAR(20) —
Patient_ID INT FK
Appt_ID INT FK
4
3.10. MedicalRecord
Attribute Type Key
Record_ID INT PK
Record_Date DATE —
Diagnosis TEXT —
Treatment TEXT —
Notes TEXT —
Patient_ID INT FK
Doctor_ID INT FK
4. RELATIONSHIPS, CARDINALITY & MODALITY
# Relationship Entities Cardinality Modality
1 Assigned Department — Doctor 1:N Dept: Mandatory · Doctor: Mandatory
2 Handles Doctor — Appointment 1:N Doctor: Mandatory · Appt: Mandatory
3 Books Patient — Appointment 1:N Patient: Mandatory · Appt: Mandatory
4 Generates Appointment — Prescription 1:1 Appt: Mandatory · Pres: Mandatory
5 Contains Prescription — PrescriptionDetail 1:N Both: Mandatory
6 Included In Medicine — PrescriptionDetail M:N Medicine: Optional · Detail: Mandatory
7 Produces Manufacturer — Medicine 1:N Both: Mandatory
8 Pays Appointment — Payment 1:1 Appt: Mandatory · Payment: Mandatory
9 Writes Doctor — MedicalRecord 1:N Doctor: Mandatory · Record: Mandatory
10 Belongs Patient — MedicalRecord 1:N Patient: Mandatory · Record: Mandatory
5
5. ER DIAGRAM
6
6. NORMALIZATION
What is Normalization?
Normalization is the process of organizing a database to reduce data redundancy and improve
data integrity.
6.1. First Normal Form
Rule: Every column must have atomic (single) values. No repeating groups.
Applied:
Each attribute in every entity holds only ONE value
For example, a Patient does not store multiple phone numbers in one field — each patient
has one phone attribute
PrescriptionDetail was created separately to avoid storing multiple medicines in one
Prescription row
6.2. Second Normal Form
Rule: Must be in 1NF + No partial dependency (every non-key attribute must depend on the
WHOLE primary key).
Applied:
PrescriptionDetail has composite dependency on both Pres_ID and Med_ID
Dosage, Frequency, Duration_Days depend on BOTH prescription AND medicine together
— so they correctly live in PrescriptionDetail
No attribute in any table depends on only part of a composite key
6.3. Third Normal Form
Rule: Must be in 2NF + No transitive dependency (non-key attributes must NOT depend on other
non-key attributes).
Applied in each table:
Table Transitive Dependency Removed
Doctor Dept_Name NOT stored in Doctor — linked via Dept_ID FK
Medicine Mfr_Name NOT stored in Medicine — linked via Mfr_ID FK
Appointment Doctor/Patient details NOT repeated — linked via FK
Payment Patient details NOT repeated — linked via Patient_ID FK
MedicalRecord Doctor/Patient details NOT repeated — linked via FK
PrescriptionDetail Medicine details NOT stored — linked via Med_ID FK
7
6.4. Unormalized Data:
is raw, unorganized data where multiple values are stored in a single cell, there are no primary
keys defined, and the same information is repeated across multiple rows — with no proper
structure or organization.
Unorganized Table:
Bl Appt Am
App Patient Patient Doctor Special Dept_ Diagn Medici Dos Manuf Pay_M
oo _Dat oun
t_ID _Name _Phone _Name ization Name osis nes age acturer ethod
d e t
Parace 500
Dr. GSK,
Ali 0302111 Cardiol Cardio 2024- Hypert tamol, mg, 200
1 A+ Ahmed AstraZ Cash
Hassan 1111 ogist logy 01-10 ension Atorva 20m 0
Raza eneca
statin g
Parace 500
Dr.
Fatima 030222 Neurolo Neurol 2024- Migrai tamol, mg, GSK, 250
2 B+ Sara Card
Malik 22222 gist ogy 01-11 ne Ibuprof 400 Abbott 0
Khan
en mg
Ligam
Hamza 030233 Dr. Bilal Orthop Ortho 2024- Ibuprof 400 300
3 O+ ent Abbott Online
Sheikh 33333 Mirza edic pedics 01-12 en mg 0
Tear
8
6.5. First Normal Form
Definition: Every cell must hold ONE atomic value. No lists, no repeating groups. Every row must
be unique with a Primary Key.
Changes Made:
Split multiple medicines into separate rows
Each cell now holds only one value
Primary Key defined
Table after 1NF:
Bl Am
App Patient Doctor Special Dept_ Appt Diagn Med_ Dos Manuf Pay_M
Phone oo oun
t_ID _Name _Name ization Name _Date osis Name age acturer ethod
d t
Dr.
Ali 030211 Cardiol Cardio 2024- Hypert Parac 500 200
1 A+ Ahmed GSK Cash
Hassan 11111 ogist logy 01-10 ension etamol mg 0
Raza
Dr.
Fatima 030222 Neurolo Neurol 2024- Migrai Parac 500 250
2 B+ Sara GSK Card
Malik 22222 gist ogy 01-11 ne etamol mg 0
Khan
Ligam
Hamza 030233 Dr. Bilal Orthope Ortho 2024- Ibupro 400 300
3 O+ ent Abbott Online
Sheikh 33333 Mirza dic pedics 01-12 fen mg 0
Tear
1NF Achieved: Every cell has one atomic value. No multiple values in one cell.
6.6. SECOND NORMAL FORM
Definition: Must be in 1NF first. Every non-key column must depend on the WHOLE Primary Key
— no partial dependencies.
All tables use single-column PKs — therefore partial dependency is impossible and all tables
automatically satisfy 2NF.
Changes Made: Separated into individual tables — each entity gets its own table.
Department Table:
dept_id (PK) dept_name description
1 Cardiology Heart and vascular
2 Neurology Brain and nervous system
3 Orthopedics Bones and joints
9
Doctor Table:
doctor_id dept_id
full_name specialization phone email license_no
(PK) (FK)
Dr. Ahmed
1 Cardiologist 03001111111 ahmed@[Link] LIC001 1
Raza
Dr. Sara
2 Neurologist 03002222222 sara@[Link] LIC002 2
Khan
Dr. Bilal
3 Orthopedic 03003333333 bilal@[Link] LIC003 3
Mirza
Patient Table:
patient_id
full_name dob gender phone email address blood_group
(PK)
1990-
1 Ali Hassan Male 03021111111 ali@[Link] Lahore A+
03-15
Fatima 1985-
2 Female 03022222222 fatima@[Link] Karachi B+
Malik 07-22
Hamza 2000-
3 Male 03023333333 hamza@[Link] Islamabad O+
Sheikh 11-01
Appointment Table:
appt_id (PK) patient_id (FK) doctor_id (FK) appt_date appt_time status reason
1 1 1 2024-01-10 09:00 Completed Chest pain
2 2 2 2024-01-11 10:30 Completed Headaches
3 3 3 2024-01-12 11:00 Completed Knee pain
Medicine Table (before 3NF fix):
med_id (PK) med_name type manufacturer unit_price stock_qty
1 Paracetamol Tablet GSK 15.00 500
2 Amoxicillin Capsule Pfizer 50.00 300
3 Metformin Tablet Novartis 30.00 400
4 Atorvastatin Tablet AstraZeneca 80.00 200
7 Ibuprofen Tablet Abbott 20.00 450
10
Prescription Table (before 3NF fix):
pres_id appt_id doctor_id patient_id
diagnosis date_issued notes
(PK) (FK) (FK) (FK)
Reduce salt
1 1 1 1 Hypertension 2024-01-10
intake
Avoid screen
2 2 2 2 Migraine 2024-01-11
exposure
Ligament
3 3 3 3 2024-01-12 Rest for 4 weeks
Tear
PrescriptionDetail Table:
pd_id (PK) pres_id (FK) med_id (FK) dosage frequency duration_days
1 1 1 500mg Twice a day 7
2 1 4 20mg Once a day 30
3 2 1 500mg Three times a day 5
4 2 7 400mg Twice a day 3
5 3 7 400mg Three times a day 7
Payment Table:
payment_id (PK) patient_id (FK) appt_id (FK) amount method payment_date status
1 1 1 2000.00 Cash 2024-01-10 Paid
2 2 2 2500.00 Card 2024-01-11 Paid
3 3 3 3000.00 Online 2024-01-12 Paid
MedicalRecord Table:
record_id patient_id doctor_id
record_date diagnosis treatment notes
(PK) (FK) (FK)
BP monitoring
1 1 1 2024-01-10 Hypertension Medication
weekly
Medication + Screen time
2 2 2 2024-01-11 Migraine
Rest limit
Ligament No heavy
3 3 3 2024-01-12 Physiotherapy
Tear lifting
2NF Achieved: All tables have single-column PKs — partial dependency impossible.
11
6.7. THIRD NORMAL FORM
Definition: Must be in 2NF first. No transitive dependencies — non-key columns must NOT
depend on other non-key columns.
Violation 1 : Prescription Table
Transitive Dependencies Found:
pres_id → appt_id → doctor_id
pres_id → appt_id → patient_id
doctor_id and patient_id depend on appt_id, not directly on pres_id — this is a 3NF violation.
Prescription Table (After 3NF Fix):
pres_id (PK) appt_id (FK) diagnosis date_issued notes
1 1 Hypertension 2024-01-10 Reduce salt intake
2 2 Migraine 2024-01-11 Avoid screen exposure
3 3 Ligament Tear 2024-01-12 Rest for 4 weeks
Fix Applied: doctor_id and patient_id removed — retrieved via JOIN through appt_id.
Violation 2: Medicine Table
Transitive Dependency Found:
med_id → med_name → manufacturer
manufacturer depends on med_name, not directly on med_id — 3NF violation.
Fix: Separate Manufacturer Table Created:
mfr_id (PK) mfr_name
1 GSK
2 Pfizer
3 Novartis
4 AstraZeneca
5 Abbott
12
Medicine Table (After 3NF Fix):
med_id (PK) med_name type mfr_id (FK) unit_price stock_qty
1 Paracetamol Tablet 1 15.00 500
2 Amoxicillin Capsule 2 50.00 300
3 Metformin Tablet 3 30.00 400
4 Atorvastatin Tablet 4 80.00 200
7 Ibuprofen Tablet 5 20.00 450
Verification: manufacturer name removed , stored once in Manufacturer table, linked via mfr_id
FK.
7. Design Justification
No data duplication : Doctor info stored once, referenced everywhere via FK
Scalable : New medicines, patients, doctors can be added without restructuring
3NF compliant : No partial or transitive dependencies anywhere
Proper modality : Optional/mandatory correctly reflects real hospital rules
M:N resolved : Medicine ↔ Prescription M:N is broken into PrescriptionDetail (bridge table)
8. CONCLUSION
The Hospital Management System database was successfully designed to manage all core
hospital operations in a structured and efficient manner. With 10 well-defined entities, 11
meaningful relationships, and correct application of cardinality and modality, the system accurately
reflects real-world hospital workflow.
The database is fully normalized to Third Normal Form (3NF), ensuring zero redundancy, strong
data integrity, and a scalable structure ready for real-world implementation.
13