B01DBFN212 - Relational Database Design and Implementation Project

This document presents a comprehensive database design and implementation project, including an entity-relationship diagram illustrating the relationships between key entities such as candidates, checkups, doctors, admissions, rooms, and prescriptions. It details the relational tables with field names, data types, and field sizes for each entity. The project demonstrates data creation in MS Access, a form for inputting and updating candidate data, and examples of SQL queries for selecting, joining, and performing numeric functions. The document also includes a room allocation report generated in MS Access. This resource is ideal for students studying database fundamentals and looking for practical examples. Desklib provides access to a wealth of similar solved assignments and past papers to aid in your studies.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Entity Relationship Diagram:..........................................................................................................2
Relational Tables:............................................................................................................................3
Data Creation in MS Access:...........................................................................................................5
Form to input/update data for Candidate:........................................................................................6
Queries:............................................................................................................................................6
Report:.............................................................................................................................................7
Bibliography:...................................................................................................................................8

2DATABASE DESIGN AND IMPLEMENTATION
Entity Relationship Diagram:
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
The above entity relationship diagram shows that eight entities are required to support the
data requirements. The ERD shows that the RoomAdmission table is a bridge table that converts
the many-to-many relationship into one-to-one and many-to-one relationship. The ERD also
represent that the each admission has to be associated with the payment. The diagram clearly
shows the cardinality and optionality of the entities. It is not necessary that each doctor will

3DATABASE DESIGN AND IMPLEMENTATION
check a patient. However, each candidate has to go through checkup. As the real life process, the
database will only accept prescription for a check or admission.
Relational Tables:
Candidate Table:
Field Name Data Type Field Size
candidateID AutoNumber Long Integer
name Short Text 255
address Short Text 255
Checkup Table:
Field Name Data Type Field Size
checkupID AutoNumber Long Integer
candidateID Number Long Integer
outcome Short Text 255
doctorID Number Long Integer
Doctor Table:
Field Name Data Type Field Size
doctorID AutoNumber Long Integer
name Short Text 255
type Short Text 255

4DATABASE DESIGN AND IMPLEMENTATION
Admission Table:
Field Name Data Type Field Size
admissionID AutoNumber Long Integer
checkupID Number Long Integer
admissionDate Date/Time Short Date
dischargeDate Date/Time Short Date
paymentMade Currency Auto
RoomAdmission Table:
Field Name Data Type Field Size
roomID Number Long Integer
admissionID Number Long Integer
Room Table:
Field Name Data Type Field Size
roomID AutoNumber Long Integer
type Short Text 255
charge Currency Auto
Candidate Table:

5DATABASE DESIGN AND IMPLEMENTATION
Field Name Data Type Field Size
prescriptionID AutoNumber Long Integer
checkupID Number Long Integer
medicineName Long Text Auto
Data Creation in MS Access:
Figure 2: Database Created in MS Access
(Source: Created by Author)

6DATABASE DESIGN AND IMPLEMENTATION
Form to input/update data for Candidate:
Figure 3: Candidate Data Insert/Update Form in MS Access
(Source: Created by Author)
The form shows the candidate add, update and delete options. Each operation is shown
through buttons. The form also has navigation operations. At the bottom left side, the previous
record navigation is present. At the bottom right side, the next record navigation is situated.
Queries:
Select: SELECT Doctor.* FROM Doctor;
Select (Doctor)
doctorID name type
1 Sam regular
2 Jessica on call
Join: SELECT Candidate.name, Admission.admissionDate, Admission.dischargeDate,
Room.type FROM Room INNER JOIN (((Candidate INNER JOIN Checkup ON
Candidate.candidateID = Checkup.[candidateID]) INNER JOIN Admission ON

7DATABASE DESIGN AND IMPLEMENTATION
Checkup.checkupID = Admission.checkupID) INNER JOIN RoomAdmisssion ON
Admission.admissionID = RoomAdmisssion.admissionID) ON Room.roomID =
RoomAdmisssion.roomID;
Join (Room Allocation to Candidate)
name admissionDate dischargeDate type
Minarva 1/10/2019 1/30/2019 general
Sam 2/1/2019 2/5/2019 Private
Numeric Function: SELECT Candidate.name, Sum(Payment.amount) AS SumOfamount
FROM ((Candidate INNER JOIN Checkup ON Candidate.candidateID = Checkup.candidateID)
INNER JOIN Admission ON Checkup.checkupID = Admission.checkupID) INNER JOIN
Payment ON Admission.admissionID = Payment.admissionID GROUP BY Candidate.name;
Numeric Function
name SumOfamount
Minarva $4,200.00
Sam $3,900.00
Report:
Figure 4: Room Allocation Report in MS Access
(Source: Created by Author)

8DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Kralj, D. and Hren, N., 2018, January. Framework project of database development: Record
keeping of protective equipment. In 7. Međunarodni stručno-znanstveni skup" Zaštita na radu i
zaštita zdravlja"/7th International Professional and Scientific Conference" Occupational safety
and health".
Liu, C., Springer, D., Li, Q., Moody, B., Juan, R.A., Chorro, F.J., Castells, F., Roig, J.M., Silva,
I., Johnson, A.E. and Syed, Z., 2016. An open access database for the evaluation of heart sound
algorithms. Physiological Measurement, 37(12), p.2181.
Pratt, R., Smatt, C. and Wynn Jr, D., 2017. Tourism through Travel Club: A Database
Project. Information Systems Education Journal, 15(2), p.40.
Yang, Y., Liu, X. and Deng, R.H., 2018. Lightweight break-glass access control system for
healthcare Internet-of-Things. IEEE Transactions on Industrial Informatics, 14(8), pp.3610-
3617.