Milestone 2 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 Table of Contents Introduction: …………………………………………………………………….1 Purpose of the project ……………………………. …………………………….. 2 Expected outcomes and significance… ………..………………………..……… 3 Business reasons for our HMSDB ……………………...………………………..4 Problems and Constraints ……………………………………………………….5 Objectives of the HMSDB … ……………………………………………………..6 Scope and Boundaries ……………………….………………………………...7 Exclusions …………………………………………………………………………..8 Project deliverables ………………………………………………………………..9 Conceptual Database Design ………………………………….………………….10 Logical Database Design ……...……………………………….…………………11 Database Design ……………………………………………….……………………12 Entity Relational Diagram ………………………………….……………………… 13 Conceptual Database Design ………………………………….……………………. 14 Physical Database Design …………………….……………….……………………. 15 Tables and Values ……… …………………….……………….……………………. 16 Normalization ………………………...……….……………….……………………. 17 DBMS Software to be used ………………….……………….……………………. 18

2 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 our Hospital Management System Database 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,

2 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. 4. User Adoption: Train staff to use the database effectively and encourage its adoption across all departments. 6. Hospital Management System Database Project Objectives  Enhance patient care: The database should enable healthcare providers to access patient data seamlessly, leading to improved diagnosis, treatment planning, and medication management.  Optimize hospital operations: The database should streamline administrative tasks, improve resource allocation, and automate processes to enhance operational efficiency.  Improve financial management: The database should provide accurate and up-to-date financial information to support informed financial decisions, cost analysis, and revenue maximization.

2 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 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.

2 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.  Data access controls: Define user roles and access permissions to restrict unauthorized access to sensitive data.  Data backup and recovery: Establish regular data backup and recovery procedures to protect against data loss and ensure system availability. Design Approach (Top-Down or Bottom-Up): Top-Down Design: In a top-down design approach, you would start with a high-level view of the system, defining the main entities, relationships, and overarching business rules. From there, you would progressively decompose the design into more detailed components. This approach is often used when there is a need to ensure that the database aligns with the overall strategic goals of the hospital and supports its key business processes. It provides a clear, structured, and holistic view of the system.

2 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.  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.

2  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.  Patient and Assigned_Test: (1; M)- Each patient can be assigned zero or many tests, and Each test can be assigned to many patients. 11.2Logical Database Design

2 15. Physical Database Design Tables, Columns, Data types, Keys of the Hospital Management System Database. Patient Table: Fields Data Type Relationships Patient_ID Varchar (5) Primary Key Doctor_ID Varchar (20) Not Null Dept_ID Varchar (20) Not Null CPresc_Num int Not null Appointment_Num int Not null age int Not Null weight int Not Null Gender Varchar (10) Not null address Varchar (50) Not Null Phone_Num int Not Null disease Varchar(20) Not Null 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_ID 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 Doctor Table: Fields Data Type Relationships Doctor_ID Varchar (5) Primary Key Doctor_name Varchar (15) Not Null Dept_ID Varchar (15) Not Null Department Table: Fields Data Type Relationships Deprt_ID Varchar (5) Primary Key Doctor_name Varchar (15) Not Null Doctor_ID Varchar (5) Not Null Hospital_ID Varchar (15) Not Null Hospital Table: Fields Data Type Relationships Hospital_ID Varchar (5) Primary Key Doctor_ID Varchar (15) Not Null Doctor_name Varchar (15) Not Null Location_ID Varchar (15) Not Null Location Table: Fields Data Type Relationships Location_ID Varchar (5) Primary Key Hospital_ID Varchar(15) Not Null Symptoms Table: Fields Data Type Relationships Symptom_type Varchar (5) Not Null Symptom_Scale Int Not Null Patient_ID Varchar (5) Not Null Allergies Table: Fields Data Type Relationships Allergy_type Varchar(5) Not Null Allergy_name Varchar(15) Not Null Patient_ID Varchar(5) Not Null

2 Adams n 02 Claire Belair Dr.Ariel Lamar Cardiology 55 F Digoxin 2145 03 Noah Wolf Dr.Monica Diaz Emergency 23 M None 0651 04 Kristine Park Dr. Yun Lee ORTHOPEDI C 18 F None 0125 05 Pierre Destin Dr.Candace Davis Pathology 32 M Benotol 0173 06 Kim Kardashians Dr. Pete Petersburg Plastic Surgery 36 F None 0789 07 Caitlyne Jenners Dr. Tracy Trans Psychiatric department 53 F Prozac+ Xanax 6941 08 Angelina Molie Dr. Donald McDonald Gynecology 31 F MagTeine 0987 09 Brad Shit Dr. Julia Fox Urology 42 M Kito28 9430 10 Jose Ortega Dr.Diana Vasquez Dental 25 M None 8462 Doctor Table: Doctor_ID Doctor_Name Depart_Name 0101 Dr. Smith Adams Neurology 0202 Dr. Ariel Lamar Cardiology 0303 Dr. Monica Diaz Emergency 0404 Dr. Yun Lee ORTHOPEDIC 0505 Dr. Candace Davis Pathology 0606 Dr. Pete Petersburg Plastic Surgery 0707 Dr. Tracy Trans Psychiatric department 0808 Dr. Donald McDonald Gynecology 0909 Dr. Julia Fox Urology 1010 Dr. Diana Vasquez Dental Department Table: Deprt_ID Doctor_Name Depart_Name Hospital_ID

2 14578 Dr. Smith Adams Neurology 3010 1579 Dr. Ariel Lamar Cardiology 3011 1680 Dr. Monica Diaz Emergency 4012 1682 Dr. Yun Lee ORTHOPEDIC 4013 1783 Dr. Candace Davis Pathology 5014 1784 Dr. Pete Petersburg Plastic Surgery 5015 1885 Dr. Tracy Trans Psychiatric department 6016 1886 Dr. Donald McDonald Gynecology 6017 1987 Dr. Julia Fox Urology 7018 1988 Dr. Diana Vasquez Dental 7019 Hospital Table: Hospital_ID Doctor_Name Depart_Name Location_ID 3010 Dr. Smith Adams Neurology 001 3011 Dr. Ariel Lamar Cardiology 002 4012 Dr. Monica Diaz Emergency 003 4013 Dr. Yun Lee ORTHOPEDIC 004 5014 Dr. Candace Davis Pathology 005 5015 Dr. Pete Petersburg Plastic Surgery 006 6016 Dr. Tracy Trans Psychiatric department 007 6017 Dr. Donald McDonald Gynecology 008 7018 Dr. Julia Fox Urology 009 7019 Dr. Diana Vasquez Dental 101 Location Table:

2 01 Food Allergy 02 Animal Allergy 03 Mold allergy 04 Pollen Allergy 05 Latex Allergy 06 Contact Dermatitis 07 Anaphylaxis 08 Food Allergy 09 None 10 Seasonal Allergy Current Prescription Table: Patient_ID Cpresc Pharmacist_ID 01 Neurontin 0001 02 Digoxin 0002 03 None 0003 04 None 0004 05 Benotol 0005 06 None 0006 07 Prozac+ Xanax 0007 08 MagTeine 0008 09 Kito28 0009 10 None 0010 Medication Table: Patient_ID Medication_Nam e Pharmacist_ID Doctor_ID

2 01 Vitaminox 0001 0101 02 Sodiuminol 0002 0202 03 None 0003 0303 04 None 0004 0404 05 Benotol 0005 0505 06 None 0006 0606 07 Prozac 0007 0707 08 None 0008 0808 09 Benzoyl 0009 0909 10 Fluorexin 0010 1010 Disease Table: Patient_ID Disease_Type Disease_Name 01 Learning disorder Dyslexia 02 Cardiovascular Coronary artery disease 03 Brain Injury Chronic traumatic encephalopathy 04 Wrists Injury Carpal Tunnel Syndrome 05 Blood Disorder Anemia 06 Face Reconstruction Maxillofacial 07 Personality disorder Gender Dysphoria 08 Female reproductive system Ovarian Cyst 09 Male reproductive System Erectile Dysfunction 10 Tooth decay Caries