Database-Chap 6

Question 1

Extended relational data model

Answer 1

Semantic data model developed in response to increasing complexity of applications
DBMS based on the ERDM often described as an object/relational DBMS
Primarily geared to business applications

Question 2

Entity super type

Answer 2

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

Question 3

Entity sub type

Answer 3

Contains unique characteristics of each entity subtype

Question 4

Specialization hierarchy rules

Answer 4

-Depicts arrangement of higher-level entity super types(parent entities) and lower-level entity subtypes(child entities)
-Subtype can exist only within context of subtype
-Every subtype can have only one super type to which it is directly related

Question 5

Specialization hierarchy provides the means to

Answer 5

1)Support attribute inheritance
-Enables an entity subtype to inherit the attributes and relationships of the super type
2)Define a special super type attribute known as the subtype discriminator
3)Define disjoint? overlapping constraints and complete/partial constraints

Question 6

Property of inheritance

Answer 6

Support attribute inheritance
-Enables an entity subtype to inherit attributes and relationships of the super type
-Subtypes can have specialized attributes in addition to inherited attributes
Sub type discriminator
-A special attribute used to determine the subtype of an entity
-Helps distinguish between different subtypes of a super type

Question 7

Disjoint constraint

Answer 7

An entity can belong to only one subtype

Question 8

Overlapping constraint

Answer 8

An entity can belong to multiple sub types

Question 9

Partial constraint

Answer 9

Some instances of the super type might not belong belong to any subtype

Question 10

Complete constraint

Answer 10

Every instance of the super type must belong to at least one subtype

Question 11

Inheritance

Answer 11

-Enables entity sub type to inherit attributes and relationships of a super type
-All entity subtypes inherit their primary key attribute from their super type
-All implementation level, super type and its sub types depicted in the specialization hierarchy maintain a 1:1 relationship

Question 12

Specialization

Answer 12

Top-down process of identifying lower-level ,more specific entity sub types from higher -level entity super types
Based on grouping unique characteristics and relationships of the sub types

Question 13

Generalization

Answer 13

Bottom-up process of identifying higher-level ,more generic entity super types from lower-level entity sub types
Based on grouping common characteristics and relationships of the subtypes

Question 14

Composition

Answer 14

-Special case of aggregation-mandatory association
-When the parent entity instance is deleted ,all child entity instances are automatically deleted

Question 15

Aggregation

Answer 15

-A larger entity can be composed of smaller entities
-Optional association
-When you delete the parent entity the child entity is not deleted

Question 16

Aggregation construct

Answer 16

Is used when an entity is composed of(or is formed by) a collection of other entities are independent of each other ,the relationship can be classified as a ‘has-a’ relationship type

Question 17

A composition construct

Answer 17

Is used when two entities are associated in an aggregation association with a strong identifying relationship

Question 18

Entity clustering “place holder”

Answer 18

-A “virtual” entity type used to represent multiple entities and relationships in an ERD
-Considered “virtual” or “abstract” because it is not actually an entity in final ERD
-Temporary entity used to represent multiple entities and relationships
-Eliminate undesirable consequences caused by missing information
-Avoid display of attributes when entity clusters are used

Question 19

Natural keys

Answer 19

Or natural identifier is a real-world ,generally accepted identifier used to uniquely identify real-world objects
-Data modeler uses natural identifier as primary key of entity being modeled

Question 20

Primary key

Answer 20

Is an attribute or combination of attributes that
-Uniquely identifies entity instances in an entity set-main function
-Guarantee entity integrity
-Non-intelligent :The PK should not have embedded semantic meaning
-No change over time
-Preferably single-attribute
-Preferably numeric
-Security complaint etc. using a social security number

Question 21

When to use composite keys(case 1)

Answer 21

Useful as identifiers of composite entities, where each PK combination is allowed only once in a many to many relationship
-Automatically provides benefit of ensuring that there cannot be duplicate values

Question 22

When to use composite keys(case 2)

Answer 22

Useful as identifiers of weak entities ,where weak entity has strong identifying relationship with parent entity
-Normally used to represent:
-A real-world object that is existent-dependent on another real-world object
-A real-world object that is represented in the data model as two separate entities in a strong identifying relationship

Question 23

When to use surrogate PKs

Answer 23

-A surrogate PK is a replacement for unsuitable attributes
Especially helpful when there is:
-No natural key
-Selected candidate key has embedded semantic contents
-Selected candidate key is too long/cumbersome

Question 24

Primary key guidelines

Answer 24

You should understand the function of a PK
-PKs and FKs are used to implement relationships among entities

Question 25

Design case 1(implementing one: one relationships)

Answer 25

-Foreign keys work with primary keys to properly implement relationships in a relational model -In a one: many relationship you put the PK of the "one" side(parent entity) on the "many" side(dependent entity) as the foreign key 1:One side is mandatory and the other side is optional 2:Both sides are optional 3:Both sides are mandatory

Question 26

Design case 2(maintaining history of time-variant data)

Answer 26

Time-variant: Refers to data whose values change over time and for which you must keep a history of data changes

Question 27

Design case 3(Fan traps)

Answer 27

-Design trap occurs when relationship is improperly or incompletely identified -Most common design trap is known as fan trap -Fan trap occurs when having one entity in two 1:* relationships to other entities -Thus producing an association among other entities that is not expressed in the model

Question 28

Design case 4(Redundant relationships)

Answer 28

-Redundancy is seldom a good thing in database environment -Occurs when there are multiple relationship paths between related entities -Main concern is that redundant relationships remain consistent across model

Question 29

Case 1 action

Answer 29

Place the PK of the entity on the mandatory side in the entity on the optional side as a FK and make the FK mandatory

Question 30

Case 2 action

Answer 30

Select the FK that causes the fewest no. of nulls or place the FK in the entity in which the relationship role is played

Question 31

Partial(optional)

Answer 31

Disjointconstraint(OR) -Super type has optional subtypes -Subtype discriminator can be null -Subtype sets are unique Overlapping constraint(AND) -Sub type has optional sub types -Sub type discriminators can be null -Sub type sets are not unique

Question 32

Total(mandatory)

Answer 32

Disjoint(or) -Every super type instance is a member of (at least one) subtype -Subtype discriminator cannot be null -Subtype sets are unique Overlapping(and) -Every super type instance is a member of (at least one) sub type -Subtype discriminators cannot be null -Sub type set are not unique