Transcript CH14

Chapter 14
Semantic Modeling
Prof. Yin-Fu Huang
CSIE, NYUST
14.1 Introduction
 Database systems typically have only a very limited
understanding of what the data in the database means.
 Semantic modeling ideas can be useful as an aid in
the process of database design, even in the absence of
direct DBMS support for those ideas.
Advanced Database System
Yin-Fu Huang
14.2 The Overall Approach
 Four steps:
1. To identify a set of semantic concepts that seem to be
useful in talking informally about the real world.
e.g., entities, entity types, instances
properties, identity, relationships
2. To devise a set of corresponding symbolic objects that can
be used to represent the foregoing semantic concepts.
e.g., E-relations, P-relations
Advanced Database System
Yin-Fu Huang
14.2 The Overall Approach (Cont.)
3. To devise a set of formal, general integrity rules to go
along with those formal objects.
e.g., property integrity
4. To develop a set of formal operators for manipulating
those formal objects.
e.g., Property operator
 Semantic concepts (See Fig. 14.1 (next slide))
 The very same object in the real world might legitimately be
regarded as an entity by some people, as a property by others,
as a relationship by still others.
Advanced Database System
Yin-Fu Huang
Fig. 14.1 Semantic concepts
Advanced Database System
Yin-Fu Huang
14.3 The E/R Model
 The entity/relationship model introduced by Chen in 1976.
 E/R diagrams (See Fig. 14.2 (next slide))
 Entities: a thing which can be distinctly identified.
 regular or strong entities
 weak or dependent entities
 Properties
 simple or composite
 key
 single or multi-valued
 missing
 base or derived
Advanced Database System
Yin-Fu Huang
Fig. 14.2 E/R diagrams
Advanced Database System
Yin-Fu Huang
14.3 The E/R Model (Cont.)
 Relationships: an association among entities
 participants
 the degree of the relationship
 total or partial
 one-to-one, one-to-many, many-to-many
• The many-to-many case is the only one that demands
representation by means of a separate relvar.
 Entity Subtypes and Supertypes
 subtype vs. supertype
 inheritance
a. properties
b. relationships
Yin-Fu Huang
Advanced
System
 entity type hierarchy
(SeeDatabase
Fig. 14.3)
Fig. 14.3 Entity type hierarchy
Advanced Database System
Yin-Fu Huang
14.4 E/R Diagrams
 The popularity of the E/R model as an approach to database
design can probably be attributed more to the existence of the
E/R diagramming technique.
 Entities: a rectangle
 regular entity type  a rectangle
e.g., Department, Employee, Supplier, Part, Project
 weak entity type  the border of the rectangle is doubled.
e.g., Dependent
 Properties: an ellipse
 derived  dotted or dashed
 multi-valued  doubled
 composite  further ellipses
 key  underlinedAdvanced Database System
Yin-Fu Huang
14.4 E/R Diagrams (Cont.)
 Relationships: a diamond
 between a weak entity type and an entity type:
the diamond border is doubled
 one-to-one, many-to-one: the line is labeled “1” or “M”
 total  the line is doubled
e.g., many-to-one: Dept_Emp, Emp_Dep, Proj_Manager
many-to-many: Proj_Work, Supp_Part_Proj,
Supp_Part, Part_Structure
 Entity Subtypes and Supertypes
 “the isa relationship”  a solid line marked with an
arrowhead
Advanced Database System
Yin-Fu Huang
14.5 Database Design with the E/R Model (1/5)
 An E/R diagram  a relational database definition
 Regular entities: a base relvar
e.g., Var Dept Base Relation
{ Dept# …, … }
Primary Key {Dept#};
Advanced Database System
Yin-Fu Huang
14.5 Database Design with the E/R Model (2/5)
 Many-to-many relationships: a base relvar
e.g., Var SP Base Relation
{ S# ..., P#..., ... }
Primary Key {S#,P#}
Foreign Key {S#} References S
On Delete Restrict
On Update Cascade
Foreign Key {P#} References P
On Delete Restrict
On Update Cascade;
Two participants  two foreign keys
If that combination is unique, and if the designer has
no objection to composite primary keys  two foreign
keys as the primary key
Advanced Database System
Yin-Fu Huang
14.5 Database Design with the E/R Model (3/5)
 Many-to-one relationships:
 To introduce a foreign key in the relvar on the “many”
side of the relationship that references the relvar on the
“one” side.
e.g., Var Emp Base Relation
{ Emp# ..., Dept#..., ... }
Primary Key {Emp#}
Foreign Key {Dept#} References Dept
On Delete ...
On Update ...;
 One-to-one relationships: the same way as many-to-one
relationships
Advanced Database System
Yin-Fu Huang
14.5 Database Design with the E/R Model (4/5)
 Weak entities: a many-to-one relationship
 The Delete and Update rules for the relationship must be
as follows:
On Delete Cascade
On Update Cascade
e.g., Var Dependent Base Relation
{ Emp# ..., … }
Primary Key { Emp#, Dep_name }
Foreign Key {Emp#} References Emp
On Delete Cascade
On Update Cascade;
• If the database designer has no objection to composite
primary keys  the combination of the foreign key and the
weak entity “key”
Yin-Fu Huang
Advanced Database System
14.5 Database Design with the E/R Model (5/5)
 Properties: An attribute
 Entity Supertypes and Subtypes: a base relvar
e.g. Var Pgmr Base Relation
{ Emp# ..., Lang ..., ... }
Primary Key {Emp#}
Foreign Key {Emp#} References Emp
On Delete Cascade
On Update Cascade;
Advanced Database System
Yin-Fu Huang
14.6 A Brief Analysis
 The E/R model as a Foundation for the Relational Model?
 The relational model is a formal system; the E/R model
is not a formal model.
 The relational model was originally and explicitly based
on some rather E/R-like ideas.
 Is the E/R Model a Data Model?
 The E/R model is indeed a data model, but one that is
essentially just a thin layer on top of the relational model.
 The fundamental E/R data object  the n-ary relation
 The E/R operators  the operators of the relational
algebra
 The integrity  a given relvar represents a certain kind
of relationship
Advanced Database System
Yin-Fu Huang
14.6 A Brief Analysis (Cont.)
 Entities vs. Relationships
 The very same object can quite legitimately be regarded
as an entity by some users and a relationship by others.
 Example: a marriage
• Relationship  “Who was Elizabeth Taylor
married to in 1975?”
• Entity  “How many marriages have been
performed in this church since April?”
Advanced Database System
Yin-Fu Huang
The End.
Advanced Database System
Yin-Fu Huang