Database Design For Healthcare Organization

Approval of Project Topic

Yes

The database is created for supporting a healthcare organization to record the diseases and patient database. The database is supposed to be storing the data of the diseases, patients and which patient has diagnosed with which diseases. The spread sheets are very error prone and time consuming. Finding results from a spread sheet is a complex work that everyone does not understand. For this reason, the database is implemented. It will provide the doctors and other staff of the organization a simple interface to interact and easy communication medium. The database is developed also with the purpose of allowing the doctors to find the treatment related information very easily. As the RDBMS is based on establishing relationships among the entities, the developed database in Microsoft Access will be extremely useful for the organization.

The database will run the query of identification of the patient and diseases name based on the most frequent diseases from a certain date to year end. This query combines both the patient and diseases entities. These entities together provide the details of the diseases frequency information. This query is based on the referential integrity between patient and diseases. If the referential integrity is correctly formed, then finding the result would be possible otherwise the database cannot support the basic need of the query.

The ER diagramming has been followed for designing the database of the system. As the name suggests, it is based on entities and relationships and these two things are only focused on in this project. An ERD or entity relationship diagram can be referred to as the graphical visualization of information system in terms of data used and processed in the system. Through the ER Diagram, the entities like diseases and patient is created. The definition of the model states that a real world system does not support any many-to-many relationship. After designing the many-to-many relation among the diseases and patient, it was not possible to correctly represent the patient and diseases relation properly without data anomalies. Therefore, as per the instructions of the ER diagram development, a bridge table is created to support the relation and convert the many-to-many into one-to-many.

The first entity is the diseases itself. This entity has a primary key named as ‘ID’. This is an auto number which means every time a new row is created it will updated automatically. No two auto numbers are same in the same entity. This number is not implemented for give meaning to the entity, it is here for providing structural support to the entity. The entity has no such attribute that will have different value for each row, therefore this ID is used as primary key. The other filed is the name. This name will be used in the real world practice like in prescription or bills instead of ID. The diseases has symptoms, tests, primary treatments and all these information can be easily captured using the attributes of the entity. Each row of the entity just hold the information of each diseases.

Administrator the database

The second entity is the patient. The patient entity is developed for the purpose of storing the patient details and the details of their treatment. This entity has a primary key named as ‘ID’. This is an auto number which means every time a new row is created it will updated automatically. No two auto numbers are same in the same entity. This number is not implemented for give meaning to the entity, it is here for providing structural support to the entity. The entity has no such attribute that will have different value for each row, therefore this ID is used as primary key. The name of the patient will be used in the prescription but in the bills, the ID will also be present. This entity has a diseases foreign key to store the diseases that it had. This makes the whole entity prone to data anomaly. It is fine until one patient is diagnose with a different diseases second time. This time the same patient has to be created again to just store the diseases ID. This is the issue many-to-many relation can create in real world. However, a bridge table can easily handle this issue.

It is possible to state that the diseases name and ID also the patient name and ID can determine each other.

ID à name

name à ID , for both the entities as these two attributes are same in patient and diseases entities. However, this condition does not apply when two names are same but ID is different. Therefore, only the universal condition will be considered and that is ID determines the name. As ID is primary key, it cannot same for two or more rows.

ID à name.

Like the name attribute, the other attributes also have dependency on the primary key. It is shown in the below.

ID à name, primaryTreatment, possibleTests, mainMedicines, symptoms

ID à name, residentialAddress, contactNumber, emailAddress, emergencyContactNumber, contactPersonName, diseases

Now the issues is with the diseases attribute in the patient entity. If it is considered that all attributes are in one entity and there is no normalization then dependency would be like following.

diseasesID, patientID à diseases

As the above functional dependency suggests, the diseases is dependent on both the primary keys.

patientID à patientName, residentialAddress, contactNumber, emailAddress, emergencyContactNumber, contactPersonName, diseases, diseasesName, primaryTreatment, possibleTests, mainMedicines, symptoms

diseasesID à diseasesName, primaryTreatment, possibleTests, mainMedicines, symptoms, diseases

So the transitive dependency is existed through the diseasesID.

The diseases and patient entities have many-to-many relationship among them. It is because, the patient can be diagnosed with the different disease next time he/she comes for checkup. On the other side, more than one patient can be diagnosed with same diseases.

a) First Normal Form:

Suppose the table is like HealthCare (patientID, patientName, residentialAddress, contactNumber, emailAddress, emergencyContactNumber, contactPersonName, diseasesName, primaryTreatment, possibleTests, mainMedicines, symptoms)

Now the diseases related attributes have to be multivalued. The primary condition for being in first normal form is no multivalued data should be present in the database. Therefore the new table must have atomic valued attributes. This means, for each diseases, the patient details will be repeated.

The table must not have any partial dependency to be in the second normal form. The partial dependency is as shown below.

diseasesID à diseasesName, primaryTreatment, possibleTests, mainMedicines, symptoms, diseases

The dieasesID is not a primary key but other non-key attributes is dependent on it. Two tables are created diseases and patient. The tables are as following.

Patient (patientID, patientName, residentialAddress, contactNumber, emailAddress, emergencyContactNumber, contactPersonName, diseases)

Diseases (dieasesID, diseasesName, primaryTreatment, possibleTests, mainMedicines, symptoms)

The diseases act as the foreign key.

The entity must not have any transitive dependency. However, it is already shown the transitive dependency remains through dieasesID. If it is removed then following entities are created.

Patient (ID, name, residentialAddress, contactNumber, emailAddress, emergencyContactNumber, contactPersonName)

Diseases (ID, name, primaryTreatment, possibleTests, mainMedicines, symptoms)

PatientDiseases (patientID, diseasesID, startDate, endDate)

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Cengage Learning.

Greenstein, M. A., Grunin, G., Schwenger, M. N., & Swamikrishnan, P. (2018). U.S. Patent Application No. 15/837,211.

Setiaji, B., & Wibowo, F. W. (2016). Chatbot using a knowledge in database. In 7th International Conference on Intelligent Systems, Modelling and Simulation.