Transcript Chapter 2

The Entity-Relationship Data Model
Chapter 2
(Database Design)
1
Steps in designing a database
• Analysis:
– What information needs to be stored?
– What are the relationships between different
components of the stored information?
– What is the suitable database structure (or schema)?
• Design the database structure (using a database
design language or notation suitable for expressing
design)
• Implementation in DBMS once committed to the
design
2
Steps in picture
• E/R diagram
• Relational model
• ODL, ORDL
Ideas/
Problems
Analysis
Database
Structure
Design
Database
Design
DBMS
Implementation
3
Entity-Relationship Model
• Traditional & popular
• Graphical representation
• Three types of elements
– Entity set (rectangle)
– Attributes (oval)
– Relationship (diamond)
4
Entity Set
•
•
•
•
Collection of similar objects
Similar to a class in the sense of OOP
Entity vs. entity set
Example: Database about movies, their stars, the
studio that producing them, and other aspects of
movies.
– A movie is an entity
– Collection of movies is an entity set
– Other entities in this db: ?
5
Attributes
• Entity set has associated attributes
• Each attribute represents a property of entities
belonging to the entity set
• Example:
– The entity set Movies has the associated attribute Title
 each movie has a title
• Assumption: attributes are atomic values
 address with two components (e.g. number, street)
cannot be used as an attribute of an entity set
6
Relationship
• Connections among entity sets
• Represent a relationship between entity sets
– If E and F are two entity sets and R connect the two,
then R is a binary relation between E and F,
mathematically we write R  E x F.
• Example: Movies and Stars are two entity sets
and a connection Stars-In between the two  the
intention: m is related to s means that star s starts
in movie m.
7
E/R Diagram
• A graph representing entity sets, attributes,
and relationships.
– Entity set (rectangle)
– Attributes (oval)
– Relationship (diamond)
8
name
address
year
title
Stars_in
Movies
length
name
Owns
film type
Stars
Studios
address
9
A reading of the previous diagram
• Three entity sets:
– Movies
– Stars
– Studio
[title,year,length,film type]
[name,address]
[name,address]
• Two relationships
– (m:n) – Stars_in(Movies,Stars)
– (m:1) – Owns(Movies,Studios)
10
Instance of an E/R diagram
• Concrete data of a database, whose structure is
represented by a E/R diagram, creates a database
instance
• In an instance
– For each entity set: a particular finite set of entities,
each has values for each attribute
– For each relationship connecting the entities E1,
E2,…,En a set of tuples (e1,e2,…,en) each ei is an entity
in the entity set of Ei
– Where are the attributes?
11
Example
• A possible database instance of the previous E/R
diagram (Note: The info is inserted for illustrative purpose
only – it needs not be true in real life)
Movies
Stars
Studio
Basic instinct
1990
150
Drama
Sharon Stone
a1
Universal Studio
Total recall
1989
120
Mystery
Arnold Schwarzenegger
a2
Dream World
Basic instinct
Sharon Stone
Total recall
Sharon Stone
Total recall
Arnold Schwarzenegger
Stars_in
Basic instinct
Universal Studio
Total recall
Universal Studio
Owns
12
A little of math before continuing
• E, F are two sets
– R  E x F: R is a binary relation from E to F
• R is a set whose member is a pair (e,f) where e is a member of
E and f is a member of F
• R could be empty, could be equal the Cartesian product of E
and F
– R is many to one relation if for each e in E there is at
most one element (e,f) in R
– R is one to one: many to one from E to F and many to
one from F to E
– R is many to many: not many to one from E to F and
not many to one from F to E
13
many-one
one-one
one-many
many-many
14
Multiplicity of Binary E/R
Relationship
• R is a binary relation from E to F
– One to many
– One to one
– Many to many
• Representing using arrow in the connection
between entity set and relationship
– Arrow entering an entity set represents the
‘one’ in the above description
15
name
address
year
title
Stars_in
Movies
length
name
Owns
film type
Stars
Studios
address
16
Multiway Relationships
• Easy to handle in E/R diagram: connect all
related entity sets with the relationship
(Note: weakening the relationship (movie,studio))
Stars
Movies
Contracts
Studios
Contracts relationship: (studio,star,movie)
17
Roles in Relationship
• An entity set can appear more than one
times in a relationship
• E/R diagram: labeling the arc connecting
the entity set and the relationship
Movies
Sequel
Original
Sequel_of
18
Another example
Stars
Movies
Contracts
Producing studio
Studio
of star
Studios
19
Attributes on Relationships
• How to record the salary of a star in a
contract?
Stars
Movies
Contracts
Studios
?
salary
?
?
20
Attributes on Relationships
• How to record the salary of a star in a
contract? Ans: attribute of Contracts!
Stars
Movies
Contracts
Studios
salary
21
name
address
year
Stars
title
Movies
Contracts
*
salary *
length
film type
name
Studios
address
The complete diagram of the Contracts relationship.
Can we live without *?
22
name
address
year
Stars
title
Movies
length
Contracts
Salaries
film type
name
Studios
address
salary
23
Eliminating Attributes on
Relationship
• Introducing a new entity set whose
attributes are the attributes attached to the
relationship
• Connecting the new entity set to the
relationship, with the arrow pointed to the
new entity set
24
Converting Multiway Relationship
to Binary Relationship
• Useful when the language for defining the
database structure does not allow multiway
relationship.
• Simple:
– Introducing a new entity set representing the
relationship
– Introducing binary relation between the new
entity set and the old ones which are connecting
to the relationship
25
Stars
Movies
Contracts
Studio
of star
Producing studio
Before
Studios
After
Stars
Movies
Star_of
Movie_of
Contracts
Producing
studio
Studio
of star
Studios
26
Subclasses in the E/R Model
•
•
•
•
Subclass: common in real-life
Represented by the isa relationship
isa is one-one relationship
In E/R diagram:
– draw as a triangle
– no arrows into the entity sets
27
year
Stars
Voices
Cartoons
title
length
film type
Movies
weapon
isa
isa
Murder
Mysteries
At home: read example 2.10 and 2.11.
28
Design Principles
•
•
•
•
•
Faithfulness
Avoiding redundancy
Simple
Select the right relationships
Select the right kind of element
29
Faithfulness
• Entity sets and attributes should reflect reality.
• Relationships are created only if they make sense
given what we know about the
domain/application.
• Example:
– Stars_in relationship should be many-many
– Teaches relationship between Courses and Instructors?
What? How?
30
No Redundancy
•
•
•
•
Motto: Stored everything only one!
Space reason (less serious)
Consistency (serious)
Example: Add an attribute studioName to the
entity set Movies while having the relationship
Owns between Movies and Studios –
consequences:
– More space (obvious: studio name stored twice)
– Change in ownership of a movie  change in the Owns
relationship and the Movies entity set
31
Simplicity
• Do only whatever is necessary!
• Example:
Movies
Owns
Studios
is better than
Movies
Represents
Holdings
Owns
Studios
32
Right Relationships
• Should we represent every possible
relationships? NO: due to space &
redundancy requirements  get only the
necessarily one
• How? Consider the assumptions, identify
those that cannot be deduced from or
represented by others
33
name
address
year
title
Stars_in
Movies
length
Contracts
Owns
film type
Stars
name
Studios
address
Assumption: a star can plays in a movie if there is a contract
involving the star and the movie  Stars_in is redundant
34
name
address
year
title
Stars
Stars_in
Works_for
Movies
Owns
length
film type
name
Studios
address
Question: Would it make sense to have a relationship
Works_for? Depending …
35
Right Kind of Element
• Attribute vs. Entity set/Relationship
• Example: address
– in the examples so far: attribute
– better as an entity with attributes such as street,
number, zip code, state
36
Example: What happens if we replace Studios
by its two attributes?  redundancy, losing
information (if a studio does not own a
movie its address is lost)
name
address
year
title
Stars_in
Movies
length
film type
Stars
name
Owns
Studios
address
37
When to use attribute for entity set E?
• All relationships connecting to E must have
an arrow entering E.
• Attributes for E must collectively identify
an entity. If there are more than one
attributes then they must not depend on
each other.
• No relationship involves E more than one.
38
Converting from Entity sets to
Attributes
E
A
R
F
B
F
A
B
39
Converting from Entity sets to
Attributes
E
R
A
B
A
F
F
R
F
B
F
40