ER diagram, EER Diagram, Relational Model
What are the characteristics of weak entities?
- do not have key attributes of their own - identified a being related to a specific entity from another entity type - always has a total participation constraint
What is the cases for mapping specialization/ generalization?
1. Any specialization 2. subclasses that are total and disjoint 3. Subclasses are disjoint, with a single special attribute 4. Subclasses are overlapping with multiple attributes
E-R to Relational Mapping: Binary (M:N)
1. Create a new relation and use PK of entity types as FK attributes in the new relation 2. Include any simple attributes of M:N relationship type PK = combination of PK from participating entity types **ONly relationship type that you must create its own table for
E-R to Relational Mapping: Multivalued Attributes
1. Create a new table for it PK = attribute from relation that mult-valued is in + attribute
What are the 4 possible constraints in an EER diagram?
1. Disjoint, total = 1 to 1 2. Disjoint, patial = 0 to 1 3. Overlapping, total = one to many 4. Overlapping, partial = 0 to many
E-R to Relational Mapping: Binary (1:1)
1. Foreign key approach 2. Merge two tables approach 3. Relationship relation (create a new table with both pks as FKs)
What is a super class?
An entity that may have more meaningful sub groupings of entities
What is the difference between cardinality and participation?
Cardinality = employees may be assinged to 1 department at a time Employees may have several projects to work on Participation = Employees must be assigned to a department (total) Employees may not be assigned to any projects (partial)
What is the difference between a composite attribute and a multi valued attribute?
Composite is a deconstructed attribute = first name + last name Multivalued = can have multiple values for one attribute more than 1 phone for each employee
What is the difference between Disjoint and overlapping constraints with an EER diagram?
Disjoint = an entity can be at most 1 of the subclass Overlapping = entity can be multiple subclasses
How do you represent entity sets, attributes, and relationships in an ER diagram?
Entity Sets = rectangles Attributes = Circles Relationships = diamonds
E-R to Relational Mapping: Regular entity
For each regular entity create a relation that has all the attributes
E-R to Relational Mapping: Weak entity
For each weak entity create a relation R and include all simple attributes of the entity type as attributes of R Include primary key of identifying relation as foreign key attributes of R PK = primary key of its identifying relation plus its own partial key
E-R to Relational Mapping: Binary (M:1) Relationship
Introduce a FK on the many side that references that PK on the 1 side
What are the steps to convert an ER to relational mapping?
Look at these in order: 1. Regular entity type 2. Weak entity type 3. Binary M:1 relationships 4. Binary 1:1 relationships 5. Binary M:N relationships 6. Multi valued attributes
What is a derived attribute?
denoted with a dashed circle. An attribute that is calculated with data