Milestone 1 DBMS Project

2 IT-650: Principles of Database Design Southern New Hampshire University Friday 1 st , 2023 Dr. Kimberly Queenan Group Members Mohcin Bounadar Meghana Marupudi Dhanush Yeddula

2 CONTENTS Document Revision History ................................................................................................................................. 2 Contents ........................................................................................................................................................... 3 1. Purpose ....................................................................................................................................................... 4 2. Data Base Initial Study ................................................................................................................................. 4 2.1 G ROUP M EMBERS ....................................................................................................................... 4 2.2 G ROUP ’ S C LASS P ROJECT .......................................................................................................... 4 2.3 A NALYZE B USINESS R EASON ...................................................................................................... 4 2.4 D EFINE P ROBLEMS AND C ONSTRAINTS ....................................................................................... 4 2.5 D EFINE O BJECTIVES ................................................................................................................... 5 2.6 D EFINE S COPE AND B OUNDARY .................................................................................................. 5 3. Database Design .......................................................................................................................................... 6 3.1 C ONCEPTUAL D ATABASE D ESIGN ............................................................................................... 6 3.2 L OGICAL D ATABASE D ESIGN ...................................................................................................... 7 3.3 P HYSICAL D ATABASE D ESIGN ..................................................................................................... 8 3.4 T ABLES AND V ALUES .................................................................................................................. 9 3.5 N ORMALIZATION ...................................................................................................................... 13 4. DBMS Software ........................................................................................................................................ 13 5. MS Access – Bug Tracking Database ........................................................................................................... 14 5.1 MS A CCESS R ELATIONSHIP D IAGRAM ...................................................................................... 14 5.2 MS A CCESS C OMPONENTS ........................................................................................................ 14 5.3 MS A CCESS T ABLES WITH LOADED DATA ................................................................................. 17 6. Definitions / Abbreviations ......................................................................................................................... 20 7. References ................................................................................................................................................ 20

2 2. Purpose of the Project The project aims to streamline patient information management, automate administrative tasks, and enhance communication channels within the hospital setting. By consolidating patient records, scheduling, billing, inventory, and reporting into one comprehensive system, the HMS seeks to reduce the potential for human error, expedite service delivery, and enhance the quality of patient care. Moreover, the system is intended to support healthcare staff by providing timely and accurate data, thereby allowing for more effective management of hospital resources. In essence, the HMS is tailored to address the unique challenges of hospital management, such as maintaining patient confidentiality, managing complex billing structures, and adapting to changing healthcare regulations. 3. Expected Outcomes and Significance The implementation of the HMS is expected to lead to significant improvements in operational efficiency, cost reduction, and patient satisfaction. By providing a more streamlined and intuitive interface for hospital operations, the system will enhance the productivity of healthcare providers and allow for more focus on patient-centric care. Furthermore, the project aligns with the digitalization goals of the healthcare industry and sets a precedent for future technological advancements in hospital management. The significance of this project transcends the operational benefits and includes promoting a culture of innovation and continuous improvement in healthcare services. Ultimately, the success of this HMS project will be measured by its adaptability to the evolving healthcare landscape and its positive impact on the health outcomes of the community it serves. 4. Business Reasons for Creating a Hospital Management System Database

2 1. Improved Patient Care: A centralized database can store and manage patient records, medical histories, treatments, and medications, providing healthcare providers with a comprehensive view of each patient's medical journey. This can lead to improved diagnoses, better treatment plans, and reduced medical errors. 2. Enhanced Operational Efficiency: The database can automate administrative tasks, such as scheduling appointments, managing patient billing, and tracking inventory. This can free up healthcare staff to focus on providing direct patient care and improve overall operational efficiency. 3. Data-Driven Decision Making: The database can generate valuable insights into patient trends, resource utilization, and financial performance. This information can help hospital management make informed decisions about resource allocation, staffing, and strategic planning. 4. Improved Patient Experience: The database can facilitate better communication between patients and healthcare providers, enabling patients to access their medical records, schedule appointments, and communicate with their doctors electronically. This can enhance patient engagement and satisfaction. 5. Problems and Constraints Identified 1. Data Security and Privacy: Ensure that patient data is protected from unauthorized access and breaches. 2. Data Integration: Integrate data from various sources, such as electronic health records, billing systems, and pharmacy systems. 3. Data Quality: Maintain the accuracy and completeness of patient data.

2  Implement robust security measures to protect patient data and ensure compliance with data privacy regulations.  Develop user-friendly interfaces for healthcare providers to access, update, and analyze patient data.  Provide training and support to healthcare providers to ensure effective adoption of the new database system. 8. Exclusions:  Development of a comprehensive electronic health record system.  Integration with external systems beyond the hospital's network.  Implementation of advanced data analytics tools.  Development of a patient portal for direct access to medical records. 9. Project Deliverables  A fully functional database management system installed and configured within the hospital's IT infrastructure.  User manuals and training materials for healthcare providers.  Comprehensive documentation of the database system, including data structure, user roles, and security protocols. 10. Hospital Database Management System: Conceptual Design Business Rules

2 1. Each patient must have a unique identifier (patient ID). 2. Each patient must be associated with a single doctor. 3. A physician can be associated with multiple patients. 4. Each patient can have multiple medical records, each associated with a specific date and time. 5. Each medical record can have multiple diagnoses, each associated with an ICD-10 code. 6. Each medical record can have multiple medications, each associated with a medication name, dosage, and frequency. 1. Doctor: Stores physician information, including physician ID, name, specialty, contact information, and medical license number. 2. Current_prescription: Stores diagnosis information for each medical record, including ICD-10 code, description, and date of diagnosis. 3. Medication: Stores medication information for each medical record, including drug name, dosage, frequency, and start and end dates. ER Diagram Additional Considerations  Data integrity constraints: Implement data integrity constraints to ensure data consistency and prevent invalid entries.  Data security measures: Implement robust security measures to protect patient data and comply with data privacy regulations.

2 Centralized Design: A centralized database design consolidates all data into a single, central repository. This approach provides data consistency, security, and a single source of truth. It is often the preferred choice for hospital management systems to ensure that patient records and other critical data are secure, accessible, and consistent across the organization. Centralization simplifies data management and reporting. Decentralized Design: In a decentralized design, data is distributed across multiple locations or databases. This approach might be considered when there is a need for data partitioning, geographical distribution, or when different departments or facilities within the hospital require a degree of autonomy in managing their data. Decentralization can offer scalability and flexibility but may require more complex data synchronization and management processes. The choice between a centralized and decentralized design depends on factors such as the hospital's size, organizational structure, and data sharing requirements. In many hospital management systems, a centralized top-down design is favored to ensure data integrity, security, and comprehensive control over patient information. However, specific cases may require variations based on unique circumstances and priorities. 11. Database Design The database design includes conceptual, logical, and physical design of the system. 11.1Conceptual Database Design Entities of the Hospital Database Management system are: Doctor, Patient, Department, Hospital, Location, Symptom, Allergy, Medication, Current_Prescription, Pharmacist, Appointment, Assigned_Test, and Disease.

2  Patient and Doctor: (1:M) – A patient is assigned only one doctor, and each doctor can see many patients.  Doctor and Department: (1:M) – A doctor can work in only one department, and each department can have many doctors working in it.  Department and Hospital: (N:M) – A department can be in many hospitals, and a hospital can have many departments.  Hospital and Location: (N:M) – Each hospital has only one location, but each location Can have many hospitals.  Patient and Symptoms : (N:M) – A patient can have zero or many symptoms, and each Symptom can be associated to zero or many patients.  Patient and Allergies : (N:M) - A patient can have zero or many allergies, and each allergy Can be associated with many patients.  Patient and Disease: (N:M) – A patient can have zero or many diseases, while a disease Can be associated with many patients.  Patient and Current_prescription: (N; M) – A patient can have zero or many current prescriptions, A prescription can be prescribed to zero or many patients.  Doctor and Pharmacist: (1; M)- A doctor can send prescriptions to only one pharmacist, A pharmacist can receive prescriptions from many doctors.  Medication and Pharmacist: (N; M)- Medications can be sold by one or many pharmacists, while each pharmacist can sell many medications.  Patient and Appointment: (1; 1)- A patient can have only one appointment, and each Appointment is associated with only one patient.

2 Figure 2: ERD Diagram Primary and Foreign keys for Bug Tracking System. Entity Name Primary Key Foreign Key Patient Patient_ID Doctor_ID, Dept_ID, presct_Num, Appointment_Num Doctor Doctor_ID Patient_ID, Dept_ID Department Dept_ID Doctor_ID Hospital Hospital_ID Doctor_ID Location Location_ID None Symptoms None Patient_ID Allergies None Patient_ID Current_Prescription None Patient_IDa Medication Presct_Num Patient_ID, Pharmacist_ID, Doctor_ID Pharmacist Pharmacist_ID Presct_Num, Patient_ID Diseases None Patient_ID Appointment Appointment_Num Patient_ID, Doctor_ID Assigned_Test None Patient_ID

2 References: Carlos, C., & Steven, M. (Ed.13). DATABASE SYSTEMS: Design, Implementation and Management. Simplilearn, 2023. What is a Database? Everything You Need to Know. https://www.simplilearn.com/tutorials/dbms-tutorial/what-is-a-database