DBMS Final Exam Chpt 5

Natural Key is _______ to end users and forms part of their _____-___-_____ business vocabulary

familiar, day, to, day

Redundancy is seldom a _______ thing in database environment

good

Specialization Hierarchy can have _____ _____ of supertype/subtype relationships

many, levels

Total Completeness-Every supertype occurrence must be a ________ of at least one subtype

member

Design Case 2: Maintaining History of Time Variant Data-Normally, existing attribute values are replaced with ______ ______ without regard to previous value

new value

Overlapping Subtypes-Contain ______-______ ________ of supertype entity set

non-unique, subset

Partial Completeness-Some supertype occurrences are _____ _________ of any subtype

not, members

Generally, data modeler uses natural identifier as _______ ______ of entity being modeled

primary, key

Some designs use redundant relationships to _______the design

simplify

Specialization is a ______-down process

top

Disjoint Subtypes-Contain a _______ _______ of the supertype entity set

unique, subset

Implementing 1:1 Relationships-In 1:1 relationship, there are two options:

• Place a foreign key in both entities (not recommended) •Place a foreign key in one of the entities

Specialization Hierarchy provides a means to do what? (3)

• Support attribute inheritance • Define the subtype discriminator • Define disjoint/overlapping constraints and complete/partial constraints

If you use surrogate key, use what?

"Unique index" and "not null" constraints

Entity Supertype

-Generic entity type related to one or more entity subtypes -Contains common characteristics

Criteria to determine the usage of Entity Supertype and Subtype

-There must be different, identifiable kinds of the entity in the user's environment -The different kinds of instances should each have one or more attributes that are unique to that kind of instance

When are composite primary keys useful? (2)

1. As identifiers of composite (aka bridge) entities 2. As identifiers of weak entities

Surrogate primary keys are help when...(3)

1. No natural key 2. Selected candidate key has embedded semantic contents 3. Selected candidate key is too long or cumbersome

Composite Key-When used as identifiers of weak entities normally used to represent what (2)

1. Real-world object that is existent-dependent on another real-world object 2. Real-world object that is represented in data model as two separate entities in strong identifying relationship

Inheritance-At implementation level, supertype and its subtype(s) maintain a __:__relationship

1:1

Inheritance-Lower-level subtypes inherit what?

All attributes and relationships from all upper-level supertypes

Inheritance-Entity subtypes inherit what?

All relationships in which supertype entity participates

Total Completeness is symbolized by what?

By a circle over a double line

Partial Completeness is symbolized by what?

By a circle over a single line

Entity Subtype

Contains unique characteristics of each entity subtype

Subtype Discriminator-Attribute in supertype entity

Determines to which entity subtype each supertype occurrence is related

Composite primary keys automatically provide what benefit?

Ensuring that there cannot be duplicate values

Subtype Discriminator-Default comparison condition for subtype discriminator attribute is what?

Equality comparison

What is most common design trap?

Fan trap

Design Case 1: Implementing 1:1 Relationships

Foreign keys work with primary keys are used to properly implement relationships in relational model

Generalization is based on what?

Grouping common characteristics and relationships of the subtypes

Specialization Hierarchy subtype has only one what?

Has only one supertype to which it is directly related

Specialization Hierarchy relationships described in terms of what?

IS-A relationship

Inheritance enables entity subtype to do what?

Inherit attributes and relationships of supertype

Entity Clustering-Considered "virtual" or "abstract" because why?

It is not actually an entity in final ERD

Composite Key-Dependent entity exists only when?

It is related to parent entity

Generalization identifies what?

More generic entity supertype from lower-level entity subtypes

Entity Clustering-Temporary entity used to represent what?

Multiple entities and relationships

Entity Clustering-"Virtual" entity type is used to represent what?

Multiple entities and relationships in ERD

A real-world identifier used to uniquely identify real world objects

Natural Key

Disjoint Subtypes are also called what?

Non-overlapping subtypes

Overlapping Subtypes-Implementation requires the use of what?

One discriminator attribute for each subtype

Identifies more specific entity subtypes from higher-level entity supertype

Specialization

If you use surrogate key, ensure what?

That candidate key of entity in question performs properly

Main concern of redundancy?

That redundant relationships remain consistent across model

Specialization Hierarchy depicts what?

The arrangement of higher-level entity supertypes and lower-level entity subtypes

Disjoint Subtypes-Implementation is based on what?

The value of the subtype discriminator attribute in the supertype

Inheritance-All entity subtypes inherit their primary key attribute from what?

Their supertype

Specialization is based on what?

Unique characteristics and relationships of the subtypes

When does fan trap occur?

When one entity is in two 1:M relationships to other entities

When do design traps occur?

When relationship is improperly or incompletely identified

When does redundancy occur?

When there are multiple relationship paths between related entities

Specialization Hierarchy subtype only exists when?

When within context of a supertype

Completeness Constraint specifies what?

Whether entity supertype occurrence must be a member of at least one subtype

Generalization is a ______-up process

bottom

Entity Clustering-Eliminate _________

consequences