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.

 

1. The university is divided into several schools: business, arts and sciences, education, and applied sciences. Each school is administered by a dean who is also a professor. Each professor can be the dean of only one school, and a professor is not required to be the dean of any school. Data of interest regarding the school includes: school code and school name. Data of interest regarding the professor includes: ID number, 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. The smallest number of departments operated by a school is one, and the largest number of departments is indeterminate. Each department belongs to only a single school. Data of interest regarding departments includes: department code and department name.

 

3. Each department offers courses. For example, the management department offers courses such as Introduction to Management and Production Management. Each course is identified by a unique course number, a course title, and a course description. Data of interest regarding courses includes: course number, course title, course description, and credit hours.

 

4. Each semester, a number of courses (but not all courses) are offered. Any given course might be offered once or 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 entities:

· 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 and a care center name.

· Patient – a person who is either admitted to the hospital or is registered as an outpatient. Each patient has an identifier, medical record number, and name.

· 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 and name.

· Bed – a hospital bed that may abe assigned to a patient who is admitted to the hospital. Each bed has a bed number, room number, and care center ID.

· Item – any medical or surgical item that may be used in treating a patient. Each item has an item number, description, and unit cost.

· Employee – any person employed as part of the hospital staff. Each employee has an employee number and name. (Physicians are not considered hospital employees)

· Diagnosis – a patient’s medical condition diagnosed by a physician. Each diagnosis has a diagnosis ID/code and a diagnosis name. The diagnosis ID 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 as a treatment ID/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, order date, and order time.

 

Based upon the above descriptions, create entity relationship diagrams for the following business rules.

 

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 diagnose any number of conditions affecting a patient, and a diagnosis may apply to many patients. The hospital records the following information: date and time of diagnosis, diagnosis code, and description for each patient diagnosis. A specific patient’s diagnosis is made by only one physician.

5. A patient may consume any number of items. A given item may be consumed by one or more patients, or may not be consumed. 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.