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)
List the attributes for Author. List the attributes for Employee.
For each entity in the diagram, state whether it is a supertype, a subtype, or neither.
Answer the questions below:
Can an employee also be an author?
Can a person be neither an employee nor an author?
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 Unit 3 Add and Rename FK in Visio document to help you with this question (and with Question 3). You may start with the Unit 3 Question 2 Figure diagram.
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. Hint: See the Unit 3 Visio Tutorial.
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:M VisitProcedure
Technician to VisitProcedure
One Technician performs many VisitProcedures
One VisitProcedure is performed by one Technician
Technician 1:M VisitProcedures
