Assignment 3: Entity-Relationship (ER) Modeling and the Use of Super/Sub Structures
Outcomes addressed in this activity:
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.
Course Outcome:
IT525-2: Use entity-relation diagrams in the design of a database.
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.
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
Part 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:
• 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. 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.
Break composite keys such as Name and Address into the appropriate fields. Identify PK, FK and Hierarchy
