Related Course Objective:

$1.$ Implement databases for data persistence.

Assignment Description and Instructions:

The Placid Memorial Hospital is located in the centre of a small island along the east cost of

Liberia. Since it is the only hospital on the island, they have a huge clientele.

The hospital manually stores its records in dockets and as result has to be importing huge

containers to facilitate storage.

Additionally, because these dockets are stored and stacked up at different locations on the

hospital grounds, finding patients' records can be a daunting task.

The hospital is now running out of space and has decided that it is about time to invest in an

electronic medical record system.

The system requirements are:

$1.$ The hospital needs to store patients$$ information on entry for registration purposes. Each

patient must have a unique ID$.$ Their name, dob, address, phone number needs to be

stored. Additionally, there is also a need to store the patients' family medical history.

$2.$ There are three main categories of doctors that exists at Placid Memorial Hospital. There

are Consultants, Residents and Interns. The specialization of the Consultant should be

recorded. The names, phone numbers and addresses of all doctors should also be

recorded. Doctors will need to be able to enter medical data about the patient. This will

include: the diagnosis, treatment that should be given, procedures and results of tests

which sometimes includes scans.

$3.$ Nurses are also placed in three main categories. The categories are Registered, Enrolled

and Registered Midwife. The Nurses' name, dob, address and phone number should be

recorded. The nurse should be able to also retrieve data on patient and also make daily

CDD Course Assessment Plan Template $2024...$Page $|2$

updates. These updates will include documenting patient's vital signs. The nurses are also

responsible for administering medication. Hence, this process has to be documented.

$4.$ An appropriate coding system should be used to facilitate accurate processing of the data

by computer systems. For example, you should have codes to represent diseases. These

codes can be created by the database developer or the developer can use existing coding

systems like The International Classification of Diseases $($ICD$).$

Part $1$ Entity$-$Relationship $($E$-$R$)$ Diagram and Tables

$($a$)$ Draw an E$-$R diagram for the above application. Make sure that you state any

assumptions that you made and any information that could not be captured in the

diagram. You are free to add any additional information that you feel may be useful for

this application. However, you must state why you choose to do so$.$

$($b$)$ Convert the E$-$R diagram into a set of unnormalized relations$/$tables$.$

$($c$)$ List the functional dependencies and the set of normalized tables.

$($d$)$ Normalize the tables. You should clearly indicate which keys are to be used as the

primary keys. The normalized tables should be in at least $3$NF$,$ and where possible

BCNF$.$ Explain which form each table is in and your reasons for choosing this form.

$($e$)$ Provide a data dictionary, describing the intended meanings of the various table and

attribute names used in the tables.

Part $2$ The Application and Database

$($a$)$ Any appropriate user interface $($front end$)$ may be used. At least $4$ stored procedures

must be used in the queries.

Create a database with the normalized tables $($from Part $1).$ The database should contain

at least $100,000$ patients with at least $50,000$ persons suffering from some form of

allergies and the said number for tests done in the system. You will need to use a

programming language of your choice e$.$g$.$ Python to populate your tables. The script

should output an sql file that will be executed for the population of the database. Make all

the necessary decisions to optimize the application's communication with the database.

Provide the script that was used to create the sql file that populated your database. Attach

the file to the document created in the first part of this assignment.

$($b$)$ Authenticate to use the system. Create three $(3)$ levels of access for the following users:

$(1)$ Doctors $(2)$ Nurses and $(3)$ Secretary. Secretaries should be able to insert registration

details for patient. Doctors and nurses should be able to insert medical information for a

patient.

$($c$)$ Provide buttons in the application to generate the following reports:

CDD Course Assessment Plan Template $2024...$Page $|3$

i$.$ Enter a diagnosis and date range to get the names of all patients with a certain

diagnosis between the specified date range.

ii$.$ Get all allergies of a specific patient.

iii. Get the medication to which most patients are allergic.

iv$.$ Retrieve all test results which may include images$/$scans of a specific patient.

v$.$ List nurses who administered medication to a specific patient at a specified date.

vi$.$ Find the interns who treated the most patients.