Assignment Submission
Write your solutions in a Word file, type your name and course number in that file, name it.
This the question down below:
QUESTION 1
- 1. To keep track of books it sells, a B Club book store uses the table structure shown below. Assuming that the sample data are representative, draw a dependency diagram in Visio that shows all functional dependencies including both partial and transitive dependencies. (Hint: Look at the sample values to determine the nature of the relationships.)Attribute NameSample ValueSample ValueSample ValueBOOK_CODE123454837519356BOOK_TITLEHistory of RomeNew LegendIntro to DatabasesGENRE_CODEH23M12F01GENRE_DESCRIPHistoryMysteryComputersAUTHOR_ID3399, 483933999375AUTHOR_NAMERobert Knut, John SmithRobert KnutDiego PriestlyBOOK_PRICE23.2512.6518.902. Using the initial dependency diagram drawn in question 1, remove all partial dependencies, draw the new dependency diagrams in Visio, and identify the normal forms for each table structure you created.3. Using the table structures you created in question 2, remove all transitive dependencies, and draw the new dependency diagrams in Visio. Also identify the normal forms for each table structure you created. If necessary, add or modify attributes to create appropriate determinants or to adhere to the naming conventions.4. Using the results of question 3, draw the fully labeled Crow’s Foot ERD in Visio. The diagram must include all entities, attributes, and relationships. Primary keys and foreign keys must be clearly identified on the diagram.
here is the 2nd portion below with the instructions:INSTRUCTIONS
For the database you designed in your previous assignments, write ten queries using operations of relational algebra. Queries must follow the requirements listed below.Queries 1 – 3 must retrieve at least two attributes and must be based on a single table. Each query must include at least two conditions. Provide an explanation of what the query is retrieving. Use complete, coherent sentences with no database terminology.
Queries 4 – 6 must retrieve at least three attributes in total and must be based on two tables. Each query must include at least two conditions. Provide an explanation of what the query is retrieving. Use complete, coherent sentences with no database terminology.
Queries 7 – 8 must retrieve at least three attributes in total and must be based on three tables. Each query must include at least two conditions. Provide an explanation of what the query is retrieving. Use complete, coherent sentences with no database terminology.
Queries 9 – 10 must be based on at least two tables, include at least two conditions, and one grouping function operator. Provide an explanation of what the query is retrieving. Use complete, coherent sentences with no database terminology.
Note: Please include your ERD/EERD along with your solutions.
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.
7
M2_2_Relational_Database_Design
Brian Blake
George Mason University
Professor Ioulia Rytikova
AIT-524-002
January 29, 2023
Description of the Company
Radiant Healthcare Center is a multi-specialty healthcare organization that provides its patients with a wide range of medical services. It has branches in different parts of the country, each operating independently. The center offers various services, including primary care, preventive medicine, diagnostic services, and specialized medical and surgical treatments. The team of medical professionals at Radiant Healthcare Center is highly trained and experienced in various specialties, and they are dedicated to providing high-quality, compassionate care to their patients. They strive to create a comfortable and welcoming environment for patients and families and to ensure that patients receive the best possible care. With the opening of the 8th branch, Radiant Healthcare Center will continue to expand its reach and improve access to quality healthcare for more people.
Reasons for Database
Radiant Healthcare Center, as a multi-specialty healthcare organization, requires a database to manage and organize the vast amount of patient information it receives daily. A database would enable the center to store, retrieve, and update patient information such as medical history, treatment plans, and test results efficiently and efficiently. Radiant Healthcare Center needs a database to improve patient care. With a database, the center will facilitate quick access to patient information, including medical history, allergies, and current medications. It will enable medical professionals to provide more accurate diagnoses and treatment plans and to avoid potential adverse reactions or interactions between drugs.
A database will facilitate the sharing of patient information among different branches of Radiant Healthcare Center (Lubrano et al., 2021). It ensures continuity of care for patients who visit other units or are referred to specialists within the organization. A database is also essential for record-keeping and compliance with regulations. It will allow the center to easily store and retrieve patient information required for billing and insurance claims and comply with laws such as HIPAA.
Business Rules
1. Each patient can have one or more medical records. Each medical history must belong to one patient.
2. Each medical record can have one or more diagnostic results. Each diagnostic result must belong to one medical history.
3. Each diagnostic result can have one or more treatments. Each treatment must belong to one diagnostic development.
4. Each patient can have one or more appointments. Each appointment must belong to one patient.
5. Each appointment can have one or more diagnostic procedures. Each diagnostic system must belong to one position.
6. Each patient can have one or more prescriptions. Each prescription must belong to one patient.
7. Each prescription can have one or more medications. Each medication must belong to one drug.
Explanation
Each patient can have one or more medical records. Each medical history must belong to one patient. This relationship is 1:M as every patient may have many medical records, and each one belongs to only one patient. It is a weak relationship as medical records are independent of patients. The connection is optional on the “many” side because business rules say that every patient “may” have a medical history (Cichy and Rass, 2019). It is mandatory on the “one” side because business rules say that every medical record “must” belong to a patient. According to business rules, the cardinality on the many sides is (0, N) because it is optional and (1, 1) for the one side because it is mandatory.
Each medical record can have one or more diagnostic results. Each diagnostic result must belong to one medical history. This relationship is 1:M, as every medical record may have many diagnostic results, and each diagnostic result belongs to only one medical record. It is a weak relationship as diagnostic results are independent of medical records. The connection is optional on the “many” side because business rules say that every medical history “may” have a diagnostic result. It is mandatory on the “one” side because business rules state that every diagnostic development “must” belong to a medical record. According to business rules, the cardinality on the many sides is (0, N) because it is optional and (1, 1) for the one side because it is mandatory.
Each diagnostic result can have one or more treatments. Each treatment must belong to one diagnostic development. This relationship is 1:M, as every diagnostic result may have many treatments, and each therapy belongs to only one diagnostic result. It is a weak relationship as treatments are independent of diagnostic results (Palanisamy and Thirunavukarasu, 2019). The connection is optional on the “many” side because business rules say that every diagnostic development “may” have a treatment. It is mandatory on the “one” side because business rules state that every treatment “must” belong to a diagnostic result. According to business rules, the cardinality on the many sides is (0, N) because it is optional and (1, 1) for the one side because it is mandatory.
Each patient can have one or more appointments. Each appointment must belong to one patient. This relationship is 1:M, as every patient may have many meetings, and each work belongs to only one patient. It is a weak relationship as appointments are independent of patients. The connection is optional on the “many” side because business rules say that every patient “may” have a license. It is mandatory on the “one” side because business rules state that every appointment “must” belong to a patient. According to business rules, the cardinality on the many sides is (0, N) because it is optional and (1, 1) for the one side because it is mandatory.
ERD/EERD Diagram
Relationships:
Patient has many Medical_Records (1:M)
Medical_Record has many Diagnostic_Results (1:M)
Diagnostic_Result has many Treatments (1:M)
The patient has many Appointments (1:M)
Appointment has many Diagnostic_Procedures (1:M)
The patient has many Prescriptions (1:M)
Prescription has many Medications (1:M)
References
Cichy, C., & Rass, S. (2019). An overview of data quality frameworks.
IEEE Access,
7, 24634-24648.
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.
Palanisamy, V., & Thirunavukarasu, R. (2019). Implications of big data analytics in developing healthcare frameworks–A review.
Journal of King Saud University-Computer and Information Sciences,
31(4), 415-425.
image1
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
