the entity relationship data model n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
The Entity-Relationship Data Model PowerPoint Presentation
Download Presentation
The Entity-Relationship Data Model

Loading in 2 Seconds...

play fullscreen
1 / 40

The Entity-Relationship Data Model - PowerPoint PPT Presentation


  • 101 Views
  • Uploaded on

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)?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Entity-Relationship Data Model' - leoma


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the entity relationship data model

The Entity-Relationship Data Model

Chapter 2

(Database Design)

steps in designing a database
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
steps in picture
Steps in picture
  • E/R diagram
  • Relational model
  • ODL, ORDL

Ideas/

Problems

Database

Structure

Database

Design

DBMS

Analysis Design Implementation

entity relationship model
Entity-Relationship Model
  • Traditional & popular
  • Graphical representation
  • Three types of elements
    • Entity set (rectangle)
    • Attributes (oval)
    • Relationship (diamond)
entity set
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: ?
attributes
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

relationship
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.
e r diagram
E/R Diagram
  • A graph representing entity sets, attributes, and relationships.
    • Entity set (rectangle)
    • Attributes (oval)
    • Relationship (diamond)
slide9

name

address

year

title

length

film type

Stars

Stars_in

Movies

name

Owns

Studios

address

a reading of the previous diagram
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)
instance of an e r diagram
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?
example
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

Stars_in

Owns

a little of math before continuing
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
slide14

many-one

one-one

one-many

many-many

multiplicity of binary e r relationship
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
slide16

name

address

year

title

many-many

many-one

length

film type

Stars

Stars_in

Movies

name

Owns

Studios

address

multiway relationships
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)

roles in relationship
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

Sequel

Sequel_of

Movies

Original

another example
Another example

Stars

Movies

Contracts

Producing studio

Studio

of star

Studios

attributes on relationships
Attributes on Relationships
  • How to record the salary of a star in a contract?

Stars

Movies

Contracts

Studios

?

?

?

salary

attributes on relationships1
Attributes on Relationships
  • How to record the salary of a star in a contract? Ans: attribute of Contracts!

Stars

Movies

Contracts

Studios

salary

slide22

name

address

year

title

length

film type

Stars

Movies

Contracts

name

*

Studios

*

salary

address

The complete diagram of the Contracts relationship.

Can we live without *?

slide23

name

address

year

title

length

film type

Stars

Movies

Contracts

name

Studios

Salaries

address

salary

eliminating attributes on relationship
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
converting multiway relationship to binary relationship
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
slide26

Stars

Movies

Contracts

Stars

Movies

Star_of

Producing studio

Studio

of star

Movie_of

Producing

studio

Studios

Contracts

Studios

Studio

of star

Before

After

subclasses in the e r model
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
slide28

year

title

length

film type

Stars

Movies

weapon

Voices

isa

isa

Cartoons

Murder

Mysteries

At home: read example 2.10 and 2.11.

design principles
Design Principles
  • Faithfulness
  • Avoiding redundancy
  • Simple
  • Select the right relationships
  • Select the right kind of element
faithfulness
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?
no redundancy
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
simplicity
Simplicity
  • Do only whatever is necessary!
  • Example:

Movies

Owns

Studios

is better than

Movies

Represents

Holdings

Owns

Studios

right relationships
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
slide34

name

address

year

Stars

Stars_in

title

Movies

Contracts

name

Owns

Studios

length

film type

address

Assumption: a star can plays in a movie if there is a contract

involving the star and the movie  Stars_in is redundant

slide35

name

address

year

title

length

film type

Stars

Stars_in

Movies

Works_for

name

Owns

Studios

address

Question: Would it make sense to have a relationship

Works_for?Depending …

right kind of element
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
slide37

name

address

year

Stars

title

Stars_in

Movies

name

Owns

Studios

length

film type

address

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)

when to use attribute for entity set e
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.