Data Model

T3: Data Model

Analysis of Data Needs for Executing a Department filed Report

The data for the business process is organized in three tables:

• Hospital info: Hospital name and location (primary key), Head departments, admissions per day, discharges per day.
• Employee information: Employee ID# (primary key), Employee name, Department(s) Name(s), Email, phone number.
• Department info: Department name (primary key),  Hospital name and Location, Contact information, Head of Department name and employee ID
• Report info: Report ID/case number (primary key), Department requesting report, Employee requesting report. 

Relational Model for Prospective Patients Outpatient visits and Inpatient stays:

The tables indicate the specific relationships among data between a hospital and its reports from different departments.

Hospital information is important for tracking and managing departments, employees, and patient movement. The primary key is the Hospital Name and location. The table identifies head Departments, admissions per day, and discharges per day. 

Department information is important for organizing important roles of the hospital, also for separating certain needs and specialities. The primary key is the Department Name. The table identifies hospital name and location, and contact information.
Reporting information is important for knowing all the information for the report request and knowing who it's coming from and what it is for. The primary key is Report ID/Case number. The table includes the department requesting the report, and the employee requesting the report.
Employee information  is important to know, therefore, you know when they've become an employee, what department(s) they work for, and how to get a hold of them. The primary key is the Employee ID#. The table includes employee name, department(s) name(s), email, and phone number. 

EXCEL TABLES:

HOSPITAL INFORMATION

Hospital Name & Location	Head Departments	Admissions Per Day	Discharges Per Day
UPMC Presbyterian & Oakland	Cardio, Orthopedic	1,000	922
UPMC Altoona & W. Central PA	Oncology, Respiratory	150	125

DEPARTMENT INFORMATION

Department name	Hospital Name and Location	Contact Information	Head of Department Name	Employee ID#
Cardio	UPMC Presbyterian & Oakland	412-555-1234	Trisha Moore	866702
Orthopedic	UPMC Presbyterian & Oakland	412-555-1233	Kathy Smith	866701
Oncology	UPMC Altoona & W. Central PA	412-555-5677	Miranda Jackson	866722
Respiratory	UPMC Altoona & W. Central PA	412-555-5678	Lisa Franklin	866734

REPORT INFORMATION

Report ID/Case #	Department Requesting Report	Employee Requesting Report
74-1852963	Cardio	Trisha Moore
74-2589631	Orthopedic	Kathy Smith
74-3697845	Oncology	Miranda Jackson
74-4569871	Respiratory	Lisa Franklin

EMPLOYEE INFORMATION

Employee ID#	Employee Name	Department(s)	email	Phone Number
866702	Trisha Moore	Cardio, ER	Trisha.Moore@upmc.com	412-555-5555
866701	Kathy Smith	Orthopedic, Pediatric	Kathy.Smith@upmc.com	412-555-6789
866722	Miranda Jackson	Oncology, ICU	Miranda.Jackson@upmc.com	412-555-1111
866734	Lisa Franklin	Respiratory, NICU	Lisa.Franklin@upmc.com	412-555-2212

ACCESS RELATIONSHIP MAP

Nature of relationship analysis

Each hospital has many  patient reports but patients reports are only at one hospital at a time.

This is a one -to -many relationship.

Each Department employs many employees and Employees can be involved with multiple departments

This is a many-to-many relationship.

Kroenke, David. MIS Essentials. Pearson Education Inc., 2015.