4347 Project Description(1)

docx

School

University of Texas, Dallas *

*We aren’t endorsed by this school

Course

DATABASE

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

3

Uploaded by CoachRainFerret17

Report
CS 4347 Database Design Term Project Project Description: MedConnect Health System Database MedConnect Health System seeks to develop a comprehensive relational database to streamline its healthcare services. The database should encompass critical modules such as Patient, Healthcare Professional, Medical Records, Appointments, Treatments, Billing, and Pharmacy. A Person can be a Patient or a healthcare professional. A person can be both a Patient and a healthcare professional. Details of a person such as Person ID, Name (First, Middle, Last), Address (Street, City, State, ZIP), Gender, Date of Birth, and Phone number (one person can have more than one phone number) are recorded. The Person ID should have the format “P-XXX” where X is a number from 0 to 9. Each patient's personal details including Patient ID, Name (First, Middle, Last), Address, Gender, Date of Birth, multiple Email address, and multiple Contact numbers need to be stored. Patient IDs should follow the format “PT-XXXXX”, where “X” is a number from 0 to 9(Hint: you can use regexp_like() function). Patients may have multiple associated medical records and may be assigned to various healthcare professionals for different needs. Healthcare Professional information should include details for employees such as Employee ID, Name, Address, Specialization, Department, Contact Information, Years of Experience (minimum of 0 and a maximum based on the professional's age), and Working Hours. Employee IDs should have the format “EMP-XXX”. Healthcare Professionals must be at least 18 years old. Healthcare professionals can be Doctors, Nurses, Technicians, or Administrative staff. Each professional may be associated with multiple patients and treatments. A doctor could also be an Administrative staff. The database should maintain a comprehensive medical record for each patient, including past diagnoses, treatment history, allergies (constraints on the values based on a predefined list of common allergens), current medications, and lab results. Each record must be uniquely identified and linked to the corresponding patient and authorized healthcare professionals. Appointments between patients and healthcare professionals must be tracked, including Appointment ID, Patient ID, Professional ID, Date, Time, and Purpose. Details of treatments prescribed by healthcare professionals to patients should be recorded, including Treatment ID, Description, Start Date, End Date, and Outcome. Each treatment is linked to one or more prescription records. All billing information related to patient treatments, hospital stays, and procedures should be stored, including Billing ID, Patient ID, Date, Itemized Costs, Total Cost, and Payment Method.
Billing records must be linked to respective Patient IDs and should accommodate multiple payment methods and installment plans. Payment Methods may be either Cash, Credit, or Insurance. Insurance information should contain the insurance company, insurance ID, amount, insurance ID, etc. Project Questions: 1. How crucial is the ability to model superclass/subclass relationships in a medical database environment, particularly for entities like Healthcare Professionals and Patients? 2. Identify 5 additional business rules that the MedConnect database should accommodate. How would these rules impact your Extended Entity-Relationship (EER) model? 3. Argue the case for using a Relational DBMS such as Oracle for the MedConnect project. (Detailed explanation with design justifications to be provided in the final report). Project Exercises: Phase I. Conceptual Design: Draw an EER diagram that accurately reflects the project requirements. Clearly state any assumptions you make. You may use any drawing tool, but ensure that the symbols are consistent with the textbook standards. Phase II. Logical Database Design: Given that a relational DBMS will be used, perform the following: a. Convert your Conceptual model (EER) to a logical data model suitable for a relational DBMS like Oracle. Address many-to-many relationships and multi-valued attributes to fit the relational model constraints. b. Document your design in a Database Schema format, detailing how you derived your schema from the EER. c. Present the database schema derived from your EER design, indicating primary and foreign keys for each table. Phase III. Implementation: Use appropriate naming conventions for all database entities. a. Ensure all tables are normalized to the third normal form. b. Create a dependency diagram for each table to illustrate the normalization process. c. Write SQL statements to create the database schema, tables, and other structures, defining primary keys, foreign keys, data types, and constraints, ensuring referential integrity. d. Create the following views using the CREATE VIEW statement: 1. VIP Patient View: This view lists the First Name, Last Name, and Enrollment Date of patients who have visited more than 3 times in the last month. 2. Preferred Healthcare Professional View: This view shows details of the healthcare professionals who have the highest patient interaction over the past two months. 3. Critical Treatment View: This view provides details of treatments that have been administered more than 5 times in the last month, indicating a possible outbreak or common condition.
4. Potential VIP Patient View: This view lists the names, phone numbers, and IDs of regular patients who are close to qualifying as VIPs based on their visit frequency over the past two months. 5. Top Staff View: This view returns details of the staff member who has processed the most appointments or treatments in the past month. e. Show the SQL statement of the following Queries. Feel free to use any of the views that you created in part (d.): 1. List all healthcare professionals by their specialization. 2. Find the names of healthcare professionals who are also registered patients. 3. Calculate the average number of visits by the top five VIP patients. 4. Identify the healthcare professional with the highest patient satisfaction ratings. 5. Determine the medication with the highest number of prescriptions in the past month. 6. Summarize the total number of treatments administered by each department. 7. Obtain details of healthcare professionals who have worked every day for the past week. 8. Count the number of patients seen by the most popular healthcare professional. 9. Retrieve all medical records created after the most recent healthcare professional was hired. 10. List all staff who have been registered as VIP patients within a month of their employment. 11. Identify the department with the highest number of unique patient visits. 12. Find the names of patients who have maintained VIP status for over 5 years. 13. Retrieve all appointment details made by potential VIP patients in the last year. Phase IV. Final Report Documentation: a. Problem Description: Replicate the provided medical database project description. b. Project Questions: Address and answer the three project questions listed above. c. EER Diagram with Assumptions: Include the EER diagram from Phase I with all accompanying assumptions. d. Relational Schema after Normalization: Present all tables in 3NF with primary and foreign keys indicated. e. Dependency Diagram: Provide a dependency diagram for each table from Phase III-b. f. SQL Statements: Document all SQL statements used for database creation, table structures, view creations, and queries from Phase III-c, d, and e.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

CSCI N207 Quiz Five Answers.docx
CSCI N207 Quiz Three Answers.docx
CSCI N207 Quiz One Answers.docx
CSCI N207 Quiz Four Answers.docx
CSCI N207 Quiz Two Answers.docx
README.md.docx
Midterm Exam-103-code1-solution.pdf
Midterm Exam-103-code2-solution.pdf
5-1 Module Five Assignment_ Impact of Cybercrime-2.pdf
Lab04.pdf
hw1.docx
Chicka chicka boom boom lesson plan 5:23.docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS