CSE 3241
First Exam
ER
EER
ER Relational
Intro DBMS
Slides 1
Slides 2
Slides 1
Slides 2
Slides 1
Slides 2
Slides 1
Slides 2
E- R relational mapping
Regular Entity type - map to a relation
one key of the entity type chosen as a primary key for the relation
Weak Entity Type
Include the attributes of the owner relation
primary key is combination of owner key attributes and partial key of weak entity type
Binary M:1 Relationship map to a foreign key in relation
M-side referring to a relation at 1 side
Binary 1:1 relationship
map to a foreign key from one relation referring to the other relation
Binary M:N Relationship
maps to a relation whose primary keys include the keys of both participating relations
Multi Valued Attributes
map to a relation R that includes the key of the owner relation
Each N-ary Relationship
maps to a relation that includes the keys of all participating relations
primary key is the combination of the primary keys of the participating entity
also foreign keys
attributes of the relationship type maps to attributes of the relation
EER Model constructs to Relations
Mapping Specialization/Generalization
8a - Any specialization
Create multiple relations for all super/subclasses
8b - subclasses that are total and disjoint
create multiple relations for all subclasses
8c - subclasses are disjoint, with a single special attribute
a single relation with a type attribute
8d - subclasses are overlapping with multiple attributes
create a single relation with multiple type attributes
Mapping of Categories (union types)
9 - Mapping of Union Types (categories
Defining Superclasses have different keys
Specify a new key attribute
Surrogate Key
Summary
Map conceptual schema design in the ER model to a relational database schema
algorithm for ER-to-relational mapping
Illustrated by examples from the COMPANY Database
Include additional steps in the algorithm for mapping constructs from EER model into relational model
Relational Database Constraints
Relational Integrity
Domain
Entity
Key
Referential Integrity
Semantic Integrity
Constraint Violations
Insert
Delete
Modify
Domain
Entity
Key
Referential
Domain
Entity
Key
Referential Integrity Constraint
Referential Integrity
Can a Foreign Key have a Null Value?
Deleting Target of a Foreign Key Reference
Restricted
Cascades
Nullifies
Updating Target of a Foreign Key Reference
Restricted
Cascades
Nullfiies
Inserting into Relation with the Foreign Key
Restricted
Cascades
Nullifies
Relation Integrity Constraints