Entity Relationship model
see attachment if instructions are dificult to read in this format.
Directions: Develop an E-R (or EER depending upon need) model for the below problem. 30 points of the grade will be based on evaluating your assumptions as well as your clarity in communicating the ER diagram and its assumption. Poorly presented assignments will receive a 0 grade. Any assignment that has any academic dishonesty in it will receive a 0 grade. Remember, we are not talking out this problem like we did in class, so you should have a lot of assumptions (think of it as listing out what you know of the problem). You may work in groups on this project. All groups must submit the project group request form no later than October 4, 2016. If you decide
to complete homework in a group, please submit only one copy of the assignment with everyone?s name on it.
Remember though, this work will be on the project and on the midterm exam and you will not have your classmates helping you on that. Groups should be no larger than 4 people. 1. Consider the following set of requirements for a university database that is used to keep track of students’ transcripts. – You are modeling a hospital system. A university is made up of campuses. Each campus offers specific medical specialties. – The hospital keeps track of each patients’ name, patient number, social security number, current address and PHONE permanent address and phone, birthdate, primary hospital, primary doctor, list of doctors who support the patient’s care, have allergies, medications they have been prescribed, procedures done to them by an ordering physician on a particular date, which are classified as ambulatory and non-ambulatory, blood work with related tests. Each point of service has a cost associated with it and a patient charge (doctor visit, procedure, blood work and tests). Patients should be able to understand every point of service they have had an the charge associated with it as well as the date of the point of service. Points of service include doctor visit + cost of the point of service. Do not worry about anything like taxes. – The hospital has facilities, which can be classified into public spaces, medical offices, therapy rooms, procedure rooms, patient rooms, surgical SUITES. Each of these have a max occupancy, supplies needed, security level, infectious disease status and departments that can authorize access to that room. -The hospital has employees. We record the name, address, phone number, employee number, ssn, bdate, and sex. Employees can work from multiple locations. Employees are classified into support staff, medical support staff, facilities, administration, nurses and doctors. Employees work for a department. All employees can have dependents. Support staff get further classified into clerical, admissions, finance or information technology. Doctors and nurses have specialties and procedures they can perform. Doctors have additional information about office location, and appointment costs and patient charge . -Each specialty is classified into a department. Departments can exist over multiple locations. Each department is described by a name, department code, office number, office phone, and hospital. Both name and code have unique values for each department. Departments have employees working for it led by a department director. Every department also offers medical procedures. – A medical procedure has a name, description, procedure number, number of hours it requires, doctors and nurses who can perform that procedure, notes, and cost associate with it offering department. There is also a patient charge. The value of number is unique for each procedure. – Blood work and tests have a name, unique code, description, location drawn, receive date, ordering physician, and notes. Blood work and tests also have a cost associated with it and a patient charge. – The hospital has a finance department. Finance keeps track of the costs associate with the business of the hospital and the revenue GENERATED. Users should be able to see a list of costs per point of service and also the income generated by a point of service or a doctor. It also tracks patient bills and if the patients bill has been paid.
– For simplicity sake, lets ignore the implications of health insurance.