What’s A Matter University (good old Whatsamatter U) is developing a database to manage academic issues. You are asked to assist in the design of the database. Sketch the Entity-Relationship diagram for each of the following business needs. Your answer must adhere to the following guidelines:
a) You need to provide a separate E-R diagram for each of the questions, not a single diagram that addresses all five questions;
b) Although each question is to be answered with a separate E-R diagram, the business rules do remain consistent throughout the question set so, an entity you identified in one question might well be used as part of an answer to a second question;
c) Your model should follow the “crows foot” notation, as described in Chapter 4 (pp 253-255) of the text.
d) Grading points:
a. All necessary entities identified and appropriately named.
b. All attributes described for each entity included in the diagram.
c. Primary key clearly identified for each entity.
d. Both maximum and minimum cardinality clearly identified
e. Any many-to-many relationships resolved.
f. No unnecessary entities or attributes.
1. The university is divided into several schools such as business, arts and sciences, education, applied sciences, etc. Each school is administered by a dean who is also a professor. Each professor can be the dean of only one school, but a professor is not required to be the dean of any school. Data of interest regarding the school includes: school code (which uniquely identifies each school) and school name. Data of interest regarding the professor includes: ID number (which uniquely identifies each professor), specialty, rank, name, and email address.
2. Each school is comprised of several departments. For example, the school of business has an accounting department, a management department, a marketing department, etc. A school has to have at least one department, and can consist of any number of departments. Each department belongs to only a single school. Data of interest regarding departments includes: department code (which uniquely identifies each department) and department name.
3. Each department offers courses; a department may offer as few as one course or any number of courses. A given course is offered by only one department. For example, the management department offers courses such as Introduction to Management and Production Management, etc. Data of interest regarding courses includes: course number (which uniquely identifies each course), course title, course description, and credit hours.
4. Each semester, a number of courses (but not all courses) are offered. Any given course might or might not be offered and, if offered, could be offered a number of times in the semester. Each course offering is uniquely identified by a CRN (course requirement number). Other information regarding the course offering includes: cycle (i.e. Monday-Wednesday-Friday, Tuesday-Thursday, etc), starting time (i.e. 8:00 am), duration (i.e. 50 minutes) and semester (i.e. Fall 2014).
5. Each department has at least one professor assigned to it. A professor is assigned to one and only one department.
Lakeside Hospital is comprised of the following:
· Care Center – a treatment center within the hospital, such as maternity, emergency care, and multiple sclerosis center. Each care center has a care center ID that uniquely identifies the center and a care center name.
· Patient – a person who is either admitted to the hospital or is registered as an outpatient. Each patient has patient id that uniquely identifies the patient, name, date of birth, and gender.
· Physician – a member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID that uniquely identifies the physician and name.
· Bed – a hospital bed that may be assigned to a patient who is admitted to the hospital. Each bed has a bed number and room number. The combination of bed number and room number uniquely identifies each bed.
· Item – any medical or surgical item that may be used in treating a patient. Each item has an item number that uniquely identifies the item, description, and unit cost.
· Employee – any person employed as part of the hospital staff. Each employee has an employee number that uniquely identifies the employee and name. (Physicians are not considered hospital employees)
· Diagnosis – a patient’s medical condition diagnosed by a physician. Each diagnosis has a diagnosis code and a diagnosis name. The diagnosis code is derived from the ICD-9-CM manual (i.e. code 00.50 is used for Staph Food Poisoning)
· Treatment – any test or procedure ordered by and/or performed by a physician for a patient. Each treatment has a treatment code and treatment name, again using standard codes from the ICD-9-CM
· Order – any order issued by a physician for treatment and/or services such as diagnostic tests, therapeutic procedures, drugs, or devices. Each order has an order ID that uniquely identifies the type of treatment or service to be performed, order date, and order time.
Based upon the above descriptions, create entity relationship diagrams for the following business rules. The same guidelines detailed in the previous set of questions apply.
1. Each hospital employee is assigned to work in one or more care centers. Each care center has at least one employee and may have any number of employees.
2. A given patient may or may not be assigned to a bed (some patients are outpatients). Occupancy rates are seldom 100%, so a bed may or may not be assigned to a patient but, if assigned, it would be assigned to only one patient.
3. A patient may be admitted to the hospital by exactly one physician. A physician may admit any number of patients or may not admit any patients.
4. Physicians examine patients and, based on the examination, make a diagnosis. Based on the diagnosis a treatment is ordered and administered to a patient. A physician may diagnose many patients or no patients at all. A patient could receive only one diagnosis or many diagnoses. For each diagnosis, a patient would receive one and only one treatment. A specific patient’s diagnosis is made by only one physician.
5. A patient may consume any number of items, including no items as all. A given item may be consumed by one or more patients, or may not be consumed at all. For each item consumed by a patient, the hospital records the following: date, time, quantity, and total cost (which can be computed by multiplying quantity times unit cost) for each patient.
Page 1 of 4
xARMYSTRONGx
er-diagram.docx
0
