Database Design for Patient, Doctor, Pharmacy System - 2NF and 3NF

This assignment presents a database design for a patient, doctor, and pharmacy system. It begins with an overview of user views, defining tables for patients, doctors, drugs, and pharmacies, including their respective attributes. The document details the relationships between these entities, such as the one-to-one relationship between a patient and their primary physician, and many-to-many relationships between doctors and drugs, as well as drugs and pharmacies. The assignment covers functional dependencies and normalization, explaining how the tables are normalized to at least 2NF and then normalized further to 3NF. It includes the ER diagram and relationship diagrams to visually represent the database structure. The solution provides table designs and datasheet views of the tables. The assignment also provides a bibliography of the resources used.

Running head: DATABASE DEIGN
Database Design
Name of the Student:
Name of the University:
Author Note

1
DATABASE DEIGN
Table of Contents
User Views.......................................................................................................................................2
Relationships....................................................................................................................................2
Functional Dependencies.................................................................................................................2
Normalization..................................................................................................................................3
ER Diagram.....................................................................................................................................4
Relationship Diagram......................................................................................................................4
Tables Designs.................................................................................................................................4
Datasheet view of the tables............................................................................................................6
Bibliography....................................................................................................................................8

2
DATABASE DEIGN
User Views
Patients (PatientID (pk), PatientName, address, age)
Doctors (DoctorID (pk), DoctorName, speciality, Experience)
PrimaryDoctor (PatientID, DoctorID)
Prescription (DoctorID, DrugID)
Drugs (DrugID (pk), TradeName, Formula)
Pharmacy (PaharmcyID (pk), PharmacyName, Address, PhoneNumber, DrugID, Price)
Relationships
Every patient has a primary physician/doctor. Every doctor has at least one patient.
Hence One to one relationship exists between Doctor and Patients
A doctor could prescribe one or more drugs for several patients, and a patient could obtain
prescriptions from several doctors.
Hence Many to Many relationship exists in between the Doctors and the Drugs.
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.
Hence Many to Many relationship exists in between the Drugs and Pharmacy.
Functional Dependencies
Attributes Dependencies
PatientID PatientName, address, age
DoctorID DoctorName, speciality, Experience

3
DATABASE DEIGN
DrugID TradeName, Formula
PharmacyID PharmacyName, Address, PhoneNumber
DrugID, PharmacyID Price
Normalization
The tables that are designed for the user views are already in 2NF. Hence the
implementation of the transitive dependencies on the table would further normalize the table into
3NF. Hence after the normalization of the database the tables would look as the following:
Patients (PatientID (pk), PatientName, address, age)
Prescription (PrescriptionID(pk), DrugID, PatientID, DoctorID)
Doctors (DoctorID (pk), DoctorName, Speciality, Experience)
Drugs (DrugID (pk), TradeName, Formula)
Pharmacy (PaharmcyID (pk), PharmacyName, Address, PhoneNumber)
Price (PaharmcyID, DrugID, Price)

4
DATABASE DEIGN
ER Diagram
Relationship Diagram
Tables Designs
Patients

5
DATABASE DEIGN
Doctors
Prescription
Drugs
Pharmacy

6
DATABASE DEIGN
Price
Datasheet view of the tables
Doctors
Drugs
Patients

7
DATABASE DEIGN
Pharmacy
Prescription
Price

8
DATABASE DEIGN
Bibliography
[1] Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
[2] Gouhar, A., 2017. Database Management System. International Journal of Engineering
Science, 11766.