Project 3

Outcomes addressed in this activity:

Course Outcomes:

IT525-2: Use data modeling concepts.

IT525-3: Use entity-relation diagrams in the design of a database.

Project Instructions:

Question 1. Definitions

Define each of the following terms (1 pt. each). In addition, provide an example of each term (2 pts. each).

  1. Attribute Inheritance
  2. Supertype
  3. Subtype
  4. Generalization
  5. Composite Key
  6. Total Completeness
  7. Partial Completeness
  8. Surrogate Key
  9. Disjoint Subtype
  10. Overlapping Subtype

Question 2. Analyze a Supertype/Subtype ERD (a subset of a Publisher database ERD)

  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:
  4. Can an employee also be an author?
  5. Can a person be neither an employee nor an author?
  6. 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. See the "IT525.Unit3.Add and Rename FK in Visio" document in Doc Sharing to help you with this question (and with Question 3). You may start with the "IT_525_Question 2 Figure.vsd" diagram, available in Doc Sharing.

Question 3. Create an ERD

A 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. Hint: See the Unit3.Visio Tutorial in Doc Sharing.

Entities

Person

PersonID

Name

Address

HomePhone

CellPhone

Gender

Race

ReferringPhysician

PersonID

NationalPractitionerID

Specialty

Patient

PersonID

ReferringPhysician (FK referencing physician's PersonID)

Employee

EmployeeID

DateHired

DateLas Promotion

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:MVisitProcedure

Technician to VisitProcedure

One Technician performs many VisitProcedures

One VisitProcedure is performed by one Technician

Technician 1:MVisitProcedures

Review the grading rubric below before beginning this activity.

100 point project grading rubric

Project Requirements/criterion / Points Possible / Points earned by student
1. Document demonstrates that the student was able to provide the proper definitions with examples. / 0-30
2. Document demonstrates that the student understands the supertype/subtype concepts . / 0-20
3. Student creates ERD with supertype/subtype constructs and required attributes. / 0-50
Total (Sum of all points)
Points deducted for spelling, grammar, and/or APA errors.
Adjusted total points