Database Design Assignment

This assignment delves into the principles of database design. It outlines various user views for a healthcare system, including Patients, Doctors, Prescriptions, Drugs, Pharmacies, and Prices. The document explores relationships between these entities, functional dependencies, and normalization techniques to achieve 3NF. It presents ER diagrams, relationship diagrams, table designs, and datasheet views of the normalized database schema.

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.