Er Diagrams
Exercise 1
1.
Draw an E-R diagram for each of the following situations (if you believe that you need to make additional assumptions, clearly state them for each situation):
a. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include Physician_ID (the identifier) and Specialty.
Patients are admitted to the hospital by physicians. Attributes of PATIENT include Patient__ID (the identifier) and Patient_Name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the details of treatment
(Treatment_Detail).
Components of Treatment_Detail include Date, Time, and Results.
2. Table below shows a Grade Report that is mailed to stuat the end of each semester. Prepare an ER diagram reflecting the data contained in Grade Report. Assume that each course is taught by one instructor.
Millennium College Grade Report Fall Semester 199X
Name:
Campus Address:
Major:
Emily Williams
208 Brooks Hall
Information
Systems
ID: 268300458
Course
ID
Title
Instructor
Name
Instructo rLocation Grade
IS 350
IS 465
Database Mgt
System
Analysis
Codd
Parsons
B104
B317
AB
3. Prepare an E-R diagram for a real estate firm that lists property for sale. The following describes this organization:
•
The firm has a number of sales offices in several states. Attributes of sales office include Office_Number (identifier) and Location.
•
Each sales office is assigned one or more employees.
Attributes of employee include Employee_ID (Identifier) and Employee_Name. An employee must be assigned to only one sales office.
•
For each sales office, there is always one employee assigned to manage that
1.
Draw an E-R diagram for each of the following situations (if you believe that you need to make additional assumptions, clearly state them for each situation):
a. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include Physician_ID (the identifier) and Specialty.
Patients are admitted to the hospital by physicians. Attributes of PATIENT include Patient__ID (the identifier) and Patient_Name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the details of treatment
(Treatment_Detail).
Components of Treatment_Detail include Date, Time, and Results.
2. Table below shows a Grade Report that is mailed to stuat the end of each semester. Prepare an ER diagram reflecting the data contained in Grade Report. Assume that each course is taught by one instructor.
Millennium College Grade Report Fall Semester 199X
Name:
Campus Address:
Major:
Emily Williams
208 Brooks Hall
Information
Systems
ID: 268300458
Course
ID
Title
Instructor
Name
Instructo rLocation Grade
IS 350
IS 465
Database Mgt
System
Analysis
Codd
Parsons
B104
B317
AB
3. Prepare an E-R diagram for a real estate firm that lists property for sale. The following describes this organization:
•
The firm has a number of sales offices in several states. Attributes of sales office include Office_Number (identifier) and Location.
•
Each sales office is assigned one or more employees.
Attributes of employee include Employee_ID (Identifier) and Employee_Name. An employee must be assigned to only one sales office.
•
For each sales office, there is always one employee assigned to manage that