L.03 Flashcards
EER and Relational Modelling
What is the purpose of EER modeling?
Extends ER modeling to represent the subgroupings that are called subclasses or subtypes.
What is the difference between subclasses and superclasses?
Subclass: A more specific category of an entity (e.g., ENGINEER is a subclass of EMPLOYEE)
Superclass: A general category that includes multiple subclasses
What is an IS-A relationship in EER?
A relationship where a subclass is a specific type of a superclass
Example: SECRETARY IS-A EMPLOYEE
What is attribute inheritance in EER?
A subclass inherits all attributes and relationships from its superclass
What are the two basic constraints in EER inheritance?
1) Completeness Constraint:
-Total: Every entity in the superclass must be a member of at least one subclass.
-Partial: An entity may or may not belong to a subclass.
2) Disjointness Constraint:
-Disjoint: An entity can belong to only one subclass.
-Overlapping: An entity can belong to multiple subclasses.
What are the four combinations of completeness and disjointness constraints?
Disjoint / Total
Disjoint / Partial
Overlapping / Total
Overlapping / Partial
What is a specialization hierarchy?
A tree structure where each subclass can have its own subclasses.
A subtype may itself have further subtypes specified on it.
Example: EMPLOYEE → ENGINEER → SOFTWARE_ENGINEER
What is a union type (category) in EER?
A subtype that inherits attributes from multiple supertypes
Example: A VEHICLE_OWNER can be a PERSON, COMPANY, or BANK
What is the relational model?
A formal model based on relations (tables)
Forms the foundation of SQL databases
What are the basic components of the relational model?
Relations (Tables)
Attributes (Columns)
Tuples (Rows)
What is a primary key in a relation?
A unique identifier for each tuple (row) in a relation
Must be unique and cannot be NULL
What is a candidate key?
A set of attributes that can uniquely identify a row
One candidate key is chosen as the primary key.
What is a compound (composite) key?
A primary key consisting of multiple attributes
Example: HOTEL_ROOM(hotel_name, room_number, guest_name)
What is an artificial primary key?
A generated unique identifier when no natural key exists
Example: ORDER(order_id, customer, item, amount)
What are the rules for selecting primary keys from candidate keys?
(1) We usually prefer short keys, e.g., ones that consist only of a single attribute.
(2) We prefer keys that are also often used to identify real-world entities over ones that just happen to be unique.
What are NULL values in a relational model?
Used when a value is unknown, unavailable, or not applicable.
What are the three types of constraints in RM?
1) Inherent Constraints – Based on relational model rules (e.g., no duplicate rows).
2) Schema-based Constraints – Defined within the schema (e.g., primary key, foreign key).
3) Application-based Constraints – Enforced by the application (e.g., salary cannot decrease).
What are key constraints in relational databases?
Primary keys must be unique and not NULL
Each relation can only have one primary key
What is referential integrity? Give example
A constraint involving two relations. Used to specify a relationship among tuples in two relations:
AKA referencing relation and the referenced relation.
Ensures that foreign keys reference valid primary keys
Example: An EMPLOYEE’s dept_id must exist in the DEPARTMENT table
How is referential integrity represented? Give example
Foreign keys reference primary keys of other relations
How are entities mapped from an EER model to relations?
Entities become relations
Attributes become columns
One of the keys becomes the primary key
How are relationships mapped from EER to relations?
1:N Relationships – Foreign key in the N-side relation.
1:1 Relationships – Foreign key in either relation (but not both).
M:N Relationships – Introduce a new relation with foreign keys for both entities.
How is generalization/specialization mapped to relations?
Single Table Inheritance – One table for all entities with a type attribute.
Multiple Table Inheritance – Separate tables for each subclass.
Joined Tables – Superclass and subclass tables linked via foreign keys.
