the entity-relationship data model
DESCRIPTION
The Entity-Relationship Data Model. Chapter 2 (Database Design). 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)? - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/1.jpg)
1
The Entity-Relationship Data Model
Chapter 2
(Database Design)
![Page 2: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/2.jpg)
2
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
![Page 3: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/3.jpg)
3
Steps in picture
Ideas/Problems
DatabaseStructure
DatabaseDesign
DBMS
Analysis Design Implementation
• E/R diagram • Relational model• ODL, ORDL
![Page 4: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/4.jpg)
4
Entity-Relationship Model
• Traditional & popular
• Graphical representation
• Three types of elements– Entity set (rectangle)– Attributes (oval)– Relationship (diamond)
![Page 5: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/5.jpg)
5
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: ?
![Page 6: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/6.jpg)
6
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
![Page 7: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/7.jpg)
7
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.
![Page 8: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/8.jpg)
8
E/R Diagram
• A graph representing entity sets, attributes, and relationships.– Entity set (rectangle)– Attributes (oval)– Relationship (diamond)
![Page 9: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/9.jpg)
9
Movies
Stars_in
Owns
title
year
length
film type
Studios
name
address
Stars
name address
![Page 10: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/10.jpg)
10
A reading of the previous diagram
• Three entity sets:– Movies [title,year,length,film type]– Stars [name,address]– Studio [name,address]
• Two relationships– (m:n) – Stars_in(Movies,Stars)– (m:1) – Owns(Movies,Studios)
![Page 11: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/11.jpg)
11
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?
![Page 12: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/12.jpg)
12
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)
Basic instinct 1990 150 Drama
Total recall 1989 120 Mystery
Sharon Stone a1
Arnold Schwarzenegger a2
Basic instinct Sharon Stone
Total recall Sharon Stone
Total recall Arnold Schwarzenegger
Movies Stars
Stars_in
Universal Studio
Dream World
Studio
Basic instinct Universal Studio
Total recall Universal Studio
Owns
![Page 13: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/13.jpg)
13
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
![Page 14: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/14.jpg)
14
many-one
one-many
one-one
many-many
![Page 15: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/15.jpg)
15
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
![Page 16: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/16.jpg)
16
Movies
Stars_in
Owns
title
year
length
film type
Studios
name
address
Stars
name address
many-many
many-one
![Page 17: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/17.jpg)
17
Multiway Relationships• Easy to handle in E/R diagram: connect all
related entity sets with the relationship (Note: weakening the relationship (movie,studio))
Movies Contracts
Studios
Stars
Contracts relationship: (studio,star,movie)
![Page 18: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/18.jpg)
18
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_of
Original
Sequel
![Page 19: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/19.jpg)
19
Another example
Movies Contracts
Studios
Stars
Producing studioStudioof star
![Page 20: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/20.jpg)
20
Attributes on Relationships
• How to record the salary of a star in a contract?
Movies Contracts
Studios
Stars
salary
? ??
![Page 21: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/21.jpg)
21
Attributes on Relationships
• How to record the salary of a star in a contract? Ans: attribute of Contracts!
Movies Contracts
Studios
Stars
salary
![Page 22: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/22.jpg)
22
Movies
title
year
length
film type
Studios
name
address
Stars
name address
Contracts
salary
The complete diagram of the Contracts relationship.Can we live without *?
**
![Page 23: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/23.jpg)
23
Movies
title
year
length
film type
Studios
name
address
Stars
name address
Contracts
salary
Salaries
![Page 24: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/24.jpg)
24
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
![Page 25: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/25.jpg)
25
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
![Page 26: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/26.jpg)
26
Movies Contracts
Studios
Stars
Producing studioStudioof star
Movies
Studios
Stars
Contracts
Movie_of
Star_of
Producingstudio
Studioof star
Before
After
![Page 27: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/27.jpg)
27
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
![Page 28: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/28.jpg)
28
Movies
titleyear length film type
CartoonsMurder
Mysteries
isa isaweaponVoices
Stars
At home: read example 2.10 and 2.11.
![Page 29: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/29.jpg)
29
Design Principles
• Faithfulness
• Avoiding redundancy
• Simple
• Select the right relationships
• Select the right kind of element
![Page 30: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/30.jpg)
30
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?
![Page 31: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/31.jpg)
31
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
![Page 32: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/32.jpg)
32
Simplicity
• Do only whatever is necessary!
• Example:
Movies Owns Studios
Movies Owns StudiosHoldingsRepresents
is better than
![Page 33: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/33.jpg)
33
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
![Page 34: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/34.jpg)
34
Movies
title
year
length
film type
Studios
name
address
Stars
name address
Contracts
Stars_in
Owns
Assumption: a star can plays in a movie if there is a contract involving the star and the movie Stars_in is redundant
![Page 35: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/35.jpg)
35
Movies
title
year
length
film type
Studios
name
address
Stars
name address
Works_for
Stars_in
Owns
Question: Would it make sense to have a relationship Works_for? Depending …
![Page 36: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/36.jpg)
36
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
![Page 37: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/37.jpg)
37
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)
Movies
Stars_in
Owns
titleyear
length
film type
Studios
name
address
Stars
name address
![Page 38: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/38.jpg)
38
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.
![Page 39: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/39.jpg)
39
Converting from Entity sets to Attributes
E R F
A B
F
A B
![Page 40: The Entity-Relationship Data Model](https://reader035.vdocuments.us/reader035/viewer/2022070411/568146ea550346895db42400/html5/thumbnails/40.jpg)
40
Converting from Entity sets to Attributes
E R F
A B F
R FA
BF