BN204 Database Technologies Assignment: Knox Hospital Database Design

This assignment solution for the BN204 Database Technologies course focuses on data model development and implementation. The solution begins by outlining the entities, attributes, and relationships for a case study involving Knox Private Hospital, detailing the design of a relational database. An Entity Relationship Diagram (ERD) visually represents the database structure. The assignment proceeds to demonstrate the implementation of the database using MS Access, providing screenshots of table designs and data views for the Patient, Doctor, Prescription, Drug, and Pharmacy tables. The solution also includes the functional dependencies. The document concludes with a bibliography of relevant database design and implementation resources. This assignment provides a comprehensive guide to data modeling and database implementation within a practical context.

Running head: DATABASE TECHNOLOGIES
Database Technologies
Name of Student-
Name of University-
Author’s Note-

1DATABASE TECHNOLOGIES
Section B
1. Database Design
a. The entities that are related with the case study of Knox Private Hospital are:
i. Patients
ii. Doctors
iii. Prescriptions
iv. Drug
v. Pharmacy
b. Attributes of the entities
i. Patient (PatientID, PatientName, Address, Age)
ii. Doctors (DoctorID, DoctorName, Speciality, YoE, PatientID)
iii. Prescriptions (PID, PatientID, DoctorID)
iv. Drug (DrugID, DrugName, QuantityPerStrips, PID)
v. Pharmacy (PCode, Pname, PAddress, PNum, DrugID, Price)
c. Relationships
i. Every patients has one or more doctors – one to many relationship
ii. Every patients has more than one prescriptions – one to many relationship
iii. Every doctors has one prescriptions – one to one relationship

2DATABASE TECHNOLOGIES
iv. Every doctors has one or more patients – one to many relationship
v. Every prescriptions has many rugs – one to many relationship
vi. Every prescriptions has many one doctor – one to one relationship
vii. Every drugs are present in one or more pharmacy – one to many relationship
viii. Every drug is present on more than one prescription - one to many relationship
d. Functional dependencies
PatientID -> PatientName, Address, Age
DoctorID -> DoctorName, Speciality, YoE
PID -> PatientID, DoctorID
DrugID -> DrugName, QuantityPerStrips
PCode -> Pname, PAddress, PNum, DrugID, Price

3DATABASE TECHNOLOGIES
2. Entity Relationship Diagram
Figure 1: Entity Relationship Diagram
(Source: Created by author on Visio)

4DATABASE TECHNOLOGIES
3. MS Access
Patient Table
Figure 2: Data View Sheet
Figure 3: Design View

5DATABASE TECHNOLOGIES
Doctor Table
Figure 4: Data View Sheet
Figure 5: Design View

6DATABASE TECHNOLOGIES
Prescription Table
Figure 6: Data View Sheet
Figure 7: Design View

7DATABASE TECHNOLOGIES
Drug Table
Figure 8: Data View Sheet
Figure 9: Design View

8DATABASE TECHNOLOGIES
Pharmacy Table
Figure 10: Data View Sheet
Figure 11: Design View

9DATABASE TECHNOLOGIES
Bibliography
[1] Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
[2] Connolly, T.M. and Beg, C.E., 2015. Database systems: a practical approach to design,
implementation, and management.
[3] Karnitis, G. and Arnicans, G., 2015, June. Migration of relational database to document-
oriented database: structure denormalization and data transformation. In 2015 7th International
Conference on Computational Intelligence, Communication Systems and Networks (pp. 113-
118). IEEE.
[4] Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.