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