IT525 ER Modeling

Unit Outcomes

• Define the construct of supertype and subtype.
• Identify overlap and disjoint subtypes.
• Identify completeness & disjoint constraints.
• Prepare an enhanced entity relationship (EER) model.

Purpose

This Assignment will allow you to demonstrate practical knowledge of supertype/subtype concepts and will have you create an ERD with supertype/subtype constructs and required attributes.

Assignment Instructions

Part 1: Definitions

Define each of the following terms. In addition, provide an example of each term.

• Attribute Inheritance
• Supertype
• Subtype
• Generalization
• Composite Key
• Total Completeness
• Partial Completeness
• Surrogate Key
• Disjoint Subtype
• Overlapping Subtype

Part 2: Analyze a Supertype/Subtype ERD (a Subset of a Publisher Database ERD)

Figure: see image.

1. List the attributes for Author. List the attributes for Employee.

2. For each entity in the diagram, state whether it is a supertype, a subtype, or neither.

3. Answer the questions below:

• Can an employee also be an author?
• Can a person be neither an employee nor an author?

4. Assume there is a "manages" relationship in which an Employee manages an Author. State the business rules for the relationship. Modify the ERD to reflect this relationship. Note that a single attribute may have only one FK constraint.

Part 3: Create an ERD

The Diagnostic Radiology Service provides radiology procedures for outpatients. Use the entity descriptions and Business Rules described below to develop an ERD. Please break composite keys such as Name and Address into the appropriate fields. Please identify PK, FK and Hierarchy Constraints and all relationships within the ERD.

Entities

Person

• PersonID
• Name
• Address
• HomePhone
• CellPhone
• Gender
• Race

ReferringPhysician

• PersonID
• NationalPractitionerID
• Specialty

Patient

• PersonID
• ReferringPhysician (FK referencing physician's PersonID)

Employee

• EmployeeID
• DateHired
• DateLastPromotion
• Degree
• HourlyRate

Technician

• EmployeeID
• Certification

Radiologist

• EmployeeID
• Specialty

Visit

• PatientID
• Date
• VisitReason

RadiologyProcedure

• RadiologyProcedureID
• Name
• Description

Person is a supertype.

Subtypes: ReferringPhysician, Patient

A Person is not employed by the organization. A Person must be at least one of the following:

Referring Physician or Patient. A Person can be both.

Employee is a supertype.

Subtypes: Technician, Radiologist

An Employee is a Technician, or a Radiologist, or an Administrative Staff.

Patient to ReferringPhysician

• One Referring Physician has many Patients.
• One Patient has one and only one Referring Physician.
• Referring Physician 1:M Patient

Patient to Visit

• One Patient has many Visits.
• One Visit relates to one and only one Patient.
• Patient 1:M Visit

Visit to RadiologyProcedure

• One Visit may contain many Radiology Procedures
• One Radiology Procedure may be performed for many Visits
• Visit M:N Radiology Procedure; Associative Entity: VisitProcedure

Radiologist to VisitProcedure

• One Radiologist reads results from many VisitProcedures
• One VisitProcedure result is read by one Radiologist
• Radiologist 1:M VisitProcedure

Technician to VisitProcedure

• One Technician performs many VisitProcedures
• One VisitProcedure is performed by one Technician
• Technician 1:M VisitProcedures