17 Questions
In the Extended Entity Relationship Model, what is a common design trap that occurs when relationships are improperly identified?
Fan trap
Which of the following is a characteristic of a good Primary Key?
Uniquely identifies each record
What should be done to avoid a fan trap in a data model?
Represent relationships consistently with the real world
How does the Extended Entity Relationship Model handle time-variant data?
By creating a new entity with new value and date of change
What does a design trap called 'fan trap' indicate about the data model?
It has inconsistent representation of relationships
Why should a Primary Key avoid containing null values?
To maintain consistency across the database
What is a common use case for composite primary keys?
Identifying composite entities in M:N relationships
When is a surrogate key especially helpful?
When there is no natural key available
What does a weak entity rely on for identification?
A composite primary key from a related entity
How does a composite primary key prevent duplicates in M:N relationships?
By ensuring each combination of keys is unique
What characteristic makes a primary key 'good'?
Being unique and simple
In the context of a catering hall renting out rooms, what would be the most suitable primary key?
Room number
What is the process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes called?
Specialization
What does partial completeness in an entity supertype mean?
Not every supertype occurrence is a member of a subtype
Which constraint specifies that every supertype occurrence must be a member of at least one subtype?
Total Completeness Constraint
What are disjoint subtypes in entity relationships?
Subtypes that contain a unique subset of the supertype entity set
What is the bottom-up process used to identify a higher-level, more generic entity supertype from lower-level entity subtypes?
'Generalization' Process
Study Notes
Composite Primary Keys
- Useful in two cases: as identifiers of composite entities in M:N relationships, and as identifiers of weak entities with a strong identifying relationship with the parent entity.
- Automatically ensures that there cannot be duplicate values.
- Example: Composite PK of ENROLL ensures a student cannot register for the same class twice.
Weak Entities
- Normally used to represent real-world objects that exist dependent on another real-world object.
- Weak entity has a strong identifying relationship with the parent entity.
- Examples: EMPLOYEE and DEPENDENT, LINE and INVOICE.
Surrogate Keys
- Especially helpful when there is no natural key, the selected candidate key has embedded semantic contents, or the selected candidate key is too long or cumbersome.
- Ensure that the candidate key of the entity in question performs properly.
- Use "unique index" and "not null" constraints.
Foreign Keys in 1:1 Relationships
- Select the FK that causes the fewest nulls, or place the FK in the entity in which the relationship role is played.
- Both sides are mandatory.
Time-Variant Data
- Values change over time, and a history of data changes must be kept.
- Equivalent to having a multivalued attribute in an entity.
- Create a new entity in a 1:M relationship with the original entity, containing the new value and date of change.
Design Traps
- Occur when a relationship is improperly or incompletely identified.
- Fan trap: one entity is in two 1:M relationships to other entities, producing an association among other entities not expressed in the model.
Redundant Relationships
- Occur when there are multiple relationship paths between related entities.
- Main concern is that redundant relationships remain consistent across the model.
Generalization and Specialization
- Generalization: a bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes.
- Specialization: the opposite of generalization, creating subtypes from a supertype.
- Examples: VEHICLE (supertype) and CAR, TRUCK, and MOTORCYCLE (subtypes).
Completeness Constraints
- Specify whether each entity supertype occurrence must also be a member of at least one subtype.
- Partial completeness: not every supertype occurrence is a member of a subtype.
- Total completeness: every supertype occurrence must be a member of at least one subtype.
Disjoint Subtypes
- Also known as nonoverlapping subtypes.
- Contain a unique subset of the supertype entity set; each entity instance of the supertype can appear in only one of the subtypes.
Explore the concepts of composite primary keys in advanced data modeling, including their use as identifiers for composite entities and weak entities. Learn how they ensure unique values and enhance security compliance in database design.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
