M1_2_Relational_Database_Specifications

Brian Blake

George Mason University

Professor Ioulia Rytikova

AIT-524-002

January 22, 2023

Company Description

The radiant healthcare center is a healthcare organization that provides various healthcare services. The hospital has seven branches in the country, which operate as different entities, and the hospital wants to open an eighth branch. The new branch will provide more healthcare services than all the other branches. Providing services like oncology, burn unit, coronary care unit, general services, GYN, ecology, intensive care unit, and maternity. This assignment will focus on developing a finance database for the finance department, which will involve information like the patients' bills, insurance information, their financial information, and the healthcare center's finances.  

           Radiant Healthcare Center requires a finance database to help the hospital manage its finances, allowing ion and operations, enabling the healthcare center to make informed financial decisions. The finance database is essential for the effective billing and collection of patient's bills by storing patient's financial and insurance information making it easy to generate invoices, track payments and generate receipts (Mahmudova, 2019). The information in a finance database is used for financial tracking and reporting by storing information like the hospital's expenses, revenue, and income statements. This makes it easy for the hospital to analyze historical financial data for budgeting and forecasting.     

Business Requirements

           The financial database will record the patient's billing and insurance information which will be connected to the medical procedure and medicine database containing information on the services the patient has received (Lubrano et al., 2021). The database will show the patient and the finance department how much the insurance will pay and how much the patient is supposed to pay out of pocket. The billing information will be generated once the medical procedure is done and the patient has been prescribed medication. The billing information will deduct insurance giving the patient the total bill they will pay. Once the patient pays the bill, it reflects on the hospital management system and generates a receipt.

The database entities will include patients, medical procedures, medications, appointments, and billing information. The relationship between the patient and medical procedure is that a patient may have one or more medical procedures, which will be recorded in one bill. The patient may be prescribed several medications in one account. A patient may have several appointments; however, the billing information is unique to a meeting and the patient. Hence one bill is associated with one charge. The constraints include that each patient must have a unique identification number, and each appointment should also have a unique identification number. The billing information must follow insurance regulations and data security compliance to ensure the patient's financial data security.

Description of Each Entity

Patient Entity

           This entity's name will be patients, and its purpose will be to store information about the patients receiving medical care from Radiant Healthcare Center. The attributes include the patient ID, name, phone number, address, emergency contact, and insurance information. The patient entity will have a one-to-many relationship with the appointment entity where a patient can have several appointments scheduled, but an appointment is unique to a patient. The patient entity will have a one-to-many relationship with the medical procedures entity, where a patient can have many medical procedures in their lifetime but the medical system is specific to a patient. The patient entity has a one-to-many relationship with the medication entity, where a patient can be prescribed several medications during many visits to the healthcare center. Still, the medicines are unique to the patient. The patient entity has a one-to-many relationship with billing information; a patient can have several billing information for different visits; however, the data is unique to the patient.  

Appointment Entity

           The name will be Appointments, which will store scheduled appointment information. The attributes include appointment ID, patient ID, date and time, and reason for the appointment. The appointment entity has a one-to-many relationship with the medical procedures entity. One appointment can include many medical procedures, but the medical designs are unique to an appointment. The appointment entity has a one-to-many relationship with the medication entity; one appointment can have several medications, but medications given in a visit are related to one appointment. The appointment entity has a one-to-one relationship with billing information. Billing information is unique to an appointment; the amount a patient pays is impressive to an appointment.

Medical Procedure Entity

           The name will be Medical Procedures, and the purpose is to record information on the patient's medical procedures performed on the patient. The attributes include procedure ID, patient ID, appointment ID, procedure name, date, and cost. The medical procedure entity has many-to-many relationships with the medical entity, where many procedures can result in a patient being prescribed several medications. The medical system has a many-to-one relationship with the billing information entity, where several medical functions are billed in one billing information for one appointment.  

Medication Entity 

           The name will be medication, and the purpose is to record the medications the patient will be prescribed. The attributes are medication ID, patient ID, appointment ID, medications name, dosage, and pricing. The medication entity has a many-to-one relationship with the billing information. One billing information for one appointment is related to many medications, whereas billing information can have several prescribed medications.

Billing Information Entity

           The name will be billing information, and its purpose is to store the patient's billing information on the medical procedures and medications the patient has received. The attributes will include billing ID, patient ID, appointment ID, insurance information, amount to be paid, and date. The billing information entity has a one-to-many relationship with the patient entity, where several billing information can belong to one patient. The billing information has a one-to-one relationship where billing information is unique to one appointment. The billing information entity has a one-to-many relationship with the medical procedures entity, where several medical procedures can be billed in one billing information. The billing information entity has a one-to-many relationship with the medication entity; one billing information can have several medications billed.

The Database Users

           The database users will be patients who need to access their financial data and finance officers who require it to access the financial data of patients and the hospital. The patients and finance officers are naïve/parametric users because they either retrieve or input data into the database and do not know its functioning. The patient uses the hospital website or application to enter and retrieve information from the database. In contrast, finance officers use the database management system software to recover or enter information into the finance database. Patients will use a graphical user interface to access data from the database, where they can choose what data they want to retrieve and input. The financial officers will use both form user interface and graphical user interface, using form UI to enter data into the database. In contrast, the graphical UI will use to retrieve data from the database.  

Database Management System Architecture

           The DBMS architecture that the project will use is the n-Tier architecture for Web Applications which involves having intermediate layers between the client and server sides (Elmasri & Navathe, 2017). The application server is located between the client and server sides, authenticating client-side requests before sending them to the server side. The application server processes client requests and sends them to the server as database queries, and the server side returns data to the application server, which is then sent back to the client (Elmasri & Navathe, 2017). This architecture will be appropriate for this project because it gives an extra layer of security which ensures that client-side requests come from genuine clients, ensuring the safety of the patient's sensitive financial data. The other architectures are inappropriate because an outage in one tier disrupts the system (Elmasri & Navathe, 2017). Since hospital services are essential, they may disrupt access to critical services. In the n-tier architecture, when one tier experiences an outage, the other tiers are unaffected, enabling service continuation. 

References

Elmasri, R., & Navathe, S. B. (2017). Fundamentals of Database Systems 7 the Edition.

Lubrano, F., Stirano, F., Varavallo, G., Bertone, F., & Terzo, O. (2021). HAMS: an integrated hospital management system to improve information exchange. In  Complex, Intelligent and Software Intensive Systems: Proceedings of the 14th International Conference on Complex, Intelligent and Software Intensive Systems (CISIS-2020) (pp. 334-343). Springer International Publishing.

Mahmudova, N. (2019). The Importance of Using Database Management Systems in Hospitals.