Unit 2: Design a Relational Database System

Running Head: UNIT 2: DESIGN A RATIONAL DATABASE SYSTEM

Unit 2: Design a Rational Database System

Dale Henderson

Kaplan University

Professor Michael Collins

IT520: Database Management Systems

August 12, 2013

Term / Definition / Example
Attribute Inheritance / Let’s the entity subtype to inherit the attributes and relationships of the supertype (Coronel, Morris, & and Rob, 2013). / Pilots, mechanics, and accountants all inherit the employee number, last name, first name, middle initial, and hire date from the EMPLOYEE entity, but the pilots have unique attributes, the same is true for the mechanics and accountants. All entity subtypes inherit their primary key attribute from their supertype (Coronel, Morris, & and Rob, 2013).
Supertype / An entity set that contains the commonly shared characteristics of its entities. If the entity set can include characteristics that are not common to all entities within the set, the supertype becomes the parent to one or more subtypes in generalization hierarchy (Coronel, Morris, & and Rob, 2013). / The EMPLOYEE is the supertype of PILOT (Coronel, Morris, & and Rob, 2013)
Subtype / An entity that contains unique characteristics attributes within a more broadly defined entity known as a supertype. In generalization hierarchy, a subtype in any entity below a parent entity (Coronel, Morris, & and Rob, 2013). / A PILOT could be a subtype of the supertype EMPLOYEE (Coronel, Morris, & and Rob, 2013).
Generalization / Is a specialization hierarchy, the grouping of common attributes into a supertype entity (Coronel, Morris, & and Rob, 2013). / You can identify multiple types of tools: hammer, screwdriver, and pliers. Using the generalization approach. You can identify “screwdrivers” entity supertype to hold the common characteristics of multiple subtypes (Coronel, Morris, & and Rob, 2013).
Completeness Constraint / A constraint that specifies whether each entity supertype occurrence must also be a member of least one subtype. The completeness constraint can be partial or total (Coronel, Morris, & and Rob, 2013). / A single horizontal line under a circle represents partial completeness constraint and double horizontal line under the circle represents a total completeness in the ERD using MS Visio (Coronel, Morris, & and Rob, 2013).
Total Specialization Rule / Total completeness means that every supertype occurrence must be a member of at least one subtype (Coronel, Morris, & and Rob, 2013). / Must be member of least one subtype. In a ERD using MS Visio a double horizontal line under a circle is used (Coronel, Morris, & and Rob, 2013).
Partial Specialization Rule / Partial completeness means some supertype occurrences might not be members of any subtype (Coronel, Morris, & and Rob, 2013). / Not all occurrences will be members of any subtype. In ERD using MS Visio a single horizontal line under a circle is used (Coronel, Morris, & and Rob, 2013).
Disjointness Constraint / A constraint that addresses the question whether an instance of a supertype may simultaneously be a member of two or more subtypes (Coronel, Morris, & and Rob, 2013). / An employee can be a pilot, a mechanic, or an accountant (Coronel, Morris, & and Rob, 2013).
Disjoint Rule / In a specialization hierarchy, a unique and non-overlapping subtype entity set (Coronel, Morris, & and Rob, 2013). / An employee (supertype) who is a pilot (subtype) can appear only in the PILOT subtype, not in any of the other subtypes. In Visio this is represented by the letter “d” inside the category shape (Coronel, Morris, & and Rob, 2013).
Overlap Rule / In a specialization hierarchy, a condition in which each entity instance row of supertype can appear in more than one subtype (Coronel, Morris, & and Rob, 2013). / A person may be an employee, a student, or both. An employee may be a professor as well as an administrator. STUDENT and EMPLOYEE are overlapping subtypes of supertype PERSON, just as PROFESSOR and ADMISTRATOR are overlapping subtypes of supertype EMPLOYEE (Coronel, Morris, & and Rob, 2013).

1. Terms, Definitions, and Examples

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

1. Employee – Author

2. Employee Manages 0 to many Authors [M]

3. Many Author has one employee manager [1]

4. Author M:1 Employee

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

Ø Entity: PERSON is a Supertype

Ø Entity: EMPLOYER is a Subtype

Ø Entity: AUTHOR is a Super and Subtype

Ø Entity: AUTHOR_BOOK is a Subtype

Ø Entity: BOOK is a Subtype

C. Answer the questions below:

Ø Can an employee also be an author? Yes

Ø Can a person be neither an employee nor an author? No

D. Define the “manages” relationship between Employee and Author by stating the business rules and the connectivity.

1. Employee – Author

2. Employee Manages 0 too many Authors [M]

3. Many Author has one employee manager [1]

4. Author M:1 Employee

E. Update the ERD to show that an Employee manages 0 to many Authors.

3. Create an ERD

Coronel, Morris, Rob. (2013, P. 173). Question 1 (stated here):

Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate. Two-Bit Drilling Company keeps information on employees and their insurance dependents. Each employee has an employee number, name, date of hire and title. If an employee is an inspector, then the date of certification and certification renewal date should also be recorded in the system. For all employees, the Social Security number and dependent names should be kept. All dependents must be associated with one and only one employee. Some employees will not have dependents, while others will have many dependents.

Reference:

Coronel, C., Morris, S., & and Rob, P. (2013). Database Systems: Design, Implementation, and Management. (10th ed.). MA, Boston: Cengage Learning.