ER Model Concepts Entities and Attributes – Entities are specific

January 14, 2023

28 Less than a minute

44 Slides1.49 MB

Save Document

ER Model Concepts Entities and Attributes – Entities are specific objects or things in the miniworld that are represented in the database. – Attributes are properties used to describe an entity. Simple – Each entity has a single atomic value for the attribute. Composite – The attribute may be composed of several components. Composition may form a hierarchy where some components are themselves composite. Multi-valued – An entity may have multiple values for that attribute. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-1

Weak Entity Types An entity that does not have a key attribute A weak entity must participate in an identifying relationship type with an owner or identifying entity type Entities are identified by the combination of: – A partial key of the weak entity type – The particular entity they are related to in the identifying entity type Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-2

Relationships A relationship relates two or more distinct entities with a specific meaning. Relationships of the same type are grouped or typed into a relationship type. The degree of a relationship type is the number of participating entity types. More than one relationship type can exist with the same participating entity types. We can also have a recursive relationship type. – Both participations are same entity type in different roles. A relationship type can have attributes. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-4

Constraints on Relationships Constraints on Relationship Types – ( Also known as ratio constraints ) – Maximum Cardinality One-to-one (1:1) One-to-many (1:N) or Many-to-one (N:1) Many-to-many – Minimum Cardinality (also called participation constraint or existence dependency constraints) zero (optional participation, not existence-dependent) one or more (mandatory, existence-dependent) Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-5

Relationships of Higher Degree Relationship types of degree 2 are called binary Relationship types of degree 3 are called ternary and of degree n are called n-ary In general, an n-ary relationship is not equivalent to n binary relationships Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-6

ER-to-Relational Mapping Step 1: Mapping of Regular Entity Types. – Regular (strong) entity type E create a relation R that includes all the simple attributes of E. – Choose one of the key attributes of E as the primary key for R. If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R. Step 2: Mapping of Weak Entity Types – Weak entity type W with owner entity type E create a relation R and include all simple attributes of W as attributes of R. – Include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s). – The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-7

ER-to-Relational Mapping Step 3: Mapping of Binary 1:1 Relation Types Binary 1:1 relationship type R, relations S and T are the entity types participating in R. There are two possible approaches: (1) Foreign Key approach: Choose one of the relations-S, and include a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. (2) Merged relation option: An alternate mapping of a 1:1 relationship type is possible by merging the two entity types and the relationship into a single relation. This may be appropriate when both participations are total. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-8

ER-to-Relational Mapping Step 4: Mapping of Binary 1:N Relationship Types. – Regular binary 1:N relationship type R, identify the relation S that represent the participating entity type at the N-side of the relationship. – Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R. – Include any simple attributes of the 1:N relation type as attributes of S. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-9

ER-to-Relational Mapping Step 5: Mapping of Binary M:N Relationship Types. – Regular binary M:N relationship type R, create a new relation S to represent R. – Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S. – Also include any simple attributes of the M:N relationship type (or simple components of composite attributes) as attributes of S. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-10

ER-to-Relational Mapping Step 6: Mapping of Multivalued attributes. – Multivalued attribute A create a new relation R. This relation R will include an attribute corresponding to A, plus the primary key attribute K-as a foreign key in R-of the relation that represents the entity type of relationship type that has A as an attribute. – The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-11

ER-to-Relational Mapping Step 7: Mapping of N-ary Relationship Types. – n-ary relationship type R create a new relationship S to represent R. – Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. – Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-14

Summary of Mapping constructs ER Model Relational Model Entity type “Entity” relation 1:1 or 1:N relationship type Foreign key (or “relationship” relation) M:N relationship type “Relationship” relation and two foreign keys n-ary relationship type “Relationship” relation and n foreign keys Simple attribute Attribute Composite attribute Set of simple component attributes Multivalued attribute Relation and foreign key Value set Domain Key attribute Primary (or secondary) key Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-17

PROBLEM with ER notation THE ENTITY RELATIONSHIP MODEL IN ITS ORIGINAL FORM DID NOT SUPPORT THE SPECIALIZATION/ GENERALIZATION ABSTRACTIONS Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-18

Subclasses and Superclasses (1) An entity type may have additional meaningful subgroupings of its entities These are called superclass/subclass relationships. These are also called IS-A relationships (SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, ). Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-19

Attribute Inheritance in Superclass / Subclass Relationships An entity that is member of a subclass inherits all attributes of the entity as a member of the superclass It also inherits all relationships Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-20

Specialization Is the process of defining a set of subclasses of a superclass The set of subclasses is based upon some distinguishing characteristics of the entities in the superclass Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-21

Generalization The reverse of the specialization process Several classes with common features are generalized into a superclass; original classes become its subclasses Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-23

Constraints on Specialization and Generalization (3) Hence, we have four types of specialization/generalization: – – – – Disjoint, total Disjoint, partial Overlapping, total Overlapping, partial Note: Generalization usually is total because the superclass is derived from the subclasses. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-24

Specialization / Generalization Hierarchies, Lattices and Shared Subclasses A subclass may itself have further subclasses specified on it Forms a hierarchy or a lattice Hierarchy has a constraint that every subclass has only one superclass (called single inheritance) In a lattice, a subclass can be subclass of more than one superclass (called multiple inheritance) In a lattice or hierarchy, a subclass inherits attributes not only of its direct superclass, but also of all its predecessor superclasses A subclass with more than one superclass is called a shared subclass Can have specialization hierarchies or lattices, or generalization hierarchies or lattices In specialization, start with an entity type and then define subclasses of the entity type by successive specialization (top down conceptual refinement process) In generalization, start with many entity types and generalize those that have common properties (bottom up conceptual synthesis process) Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-26

Categories (UNION TYPES) All of the superclass/subclass relationships we have seen thus far have a single superclass A shared subclass is subclass in more than one distinct superclass/subclass relationships, where each relationships has a single superclass (multiple inheritance) In some cases, need to model a single superclass/subclass relationship with more than one superclass Superclasses represent different entity types Such a subclass is called a category or UNION TYPE Note: The difference from shared subclass, which is subset of the intersection of its superclasses (shared subclass member must exist in all of its superclasses). Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-28

Mapping EER Model to Relations Step8: Options for Mapping Specialization or Generalization. Convert each specialization with m subclasses {S1, S2, .,Sm} and generalized superclass C, where the attributes of C are {k,a1, an} and k is the (primary) key, into relational schemas using one of the four following options: Option 8A: Multiple relations-Superclass and subclasses. Create a relation L for C with attributes Attrs(L) {k,a 1, an} and PK(L) k. Create a relation Li for each subclass Si, 1 i m, with the attributesAttrs(Li) {k} U {attributes of Si} and PK(Li) k. This option works for any specialization (total or partial, disjoint of over-lapping). Option 8B: Multiple relations-Subclass relations only Create a relation Li for each subclass Si, 1 i m, with the attributes Attr(Li) {attributes of Si} U {k,a1 ,an} and PK(Li) k. This option only works for a specialization whose subclasses are total (every entity in the superclass must belong to (at least) one of the subclasses). Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-30

Mapping EER Model Constructs to Relations (cont) Option 8C: Single relation with one type attribute. Create a single relation L with attributes Attrs(L) {k,a1, an} U {attributes of S1} U U {attributes of Sm} U {t} and PK(L) k. The attribute t is called a type (or discriminating) attribute that indicates the subclass to which each tuple belongs Option 8D: Single relation with multiple type attributes. Create a single relation schema L with attributes Attrs(L) {k,a1, an} U {attributes of S1} U U {attributes of Sm} U {t1, t2, ,tm} and PK(L) k. Each ti, 1 I m, is a Boolean type attribute indicating whether a tuple belongs to the subclass Si. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-31

FIGURE 7.4 Options for mapping specialization or generalization. (c) Mapping the EER schema in Figure 4.4 using option 8C. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-37

FIGURE 7.4 Options for mapping specialization or generalization. (d) Mapping Figure 4.5 using option 8D with Boolean type fields Mflag and Pflag. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-39

Mapping EER Model Constructs to Relations (cont) Mapping of Shared Subclasses (Multiple Inheritance) A shared subclass, such as STUDENT ASSISTANT, is a subclass of several classes, indicating multiple inheritance. These classes must all have the same key attribute; otherwise, the shared subclass would be modeled as a category. We can apply any of the options discussed in Step 8 to a shared subclass, subject to the restriction discussed in Step 8 of the mapping algorithm. Below both 8C and 8D are used for the shared class STUDENT ASSISTANT. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-40

Mapping EER Model Constructs to Relations (cont) Step 9: Mapping of Union Types (Categories). – For mapping a category whose defining superclass have different keys, it is customary to specify a new key attribute, called a surrogate key, when creating a relation to correspond to the category. – In the example below we can create a relation OWNER to correspond to the OWNER category and include any attributes of the category in this relation. The primary key of the OWNER relation is the surrogate key, which we called OwnerId. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Copyright 2004 Pearson Education, Inc. Chapter 3-43

January 14, 2023

28 Less than a minute

Related Articles

PRINCIPLES OF FLIGHT CHAPTER 4

ELEC 5200-001/6200-001 Computer Architecture and Design Spring 2016

Law of Torts and Consumer Protection Act Vicarious Liability

Vulnerability Assessment in Smart Grids Jinyuan Stella Sun UTK

Weld Joint Geometry and Welding Symbols Types of welds Groove welds

Suicide Prevention Kelli Donley Williams, MPH Suicide