entity relationship model l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Entity-Relationship Model PowerPoint Presentation
Download Presentation
Entity-Relationship Model

Loading in 2 Seconds...

play fullscreen
1 / 68

Entity-Relationship Model - PowerPoint PPT Presentation


  • 213 Views
  • Uploaded on

Entity-Relationship Model. E-R Diagrams Class hierarchies Weak entity sets. Purpose of E/R Model. The E/R model allows us to sketch the design of a database informally. Designs are pictures called entity-relationship diagrams .

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 'Entity-Relationship Model' - inoke


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
entity relationship model

Entity-Relationship Model

E-R Diagrams

Class hierarchies

Weak entity sets

purpose of e r model
Purpose of E/R Model
  • The E/R model allows us to sketch the design of a database informally.
  • Designs are pictures called entity-relationship diagrams.
  • convert E/R diagrams to real implementations like relational databases exist.
entity sets
Entity Sets
  • Entity = “thing” or object.
  • Entity set = collection of similar entities.
  • Attribute = property of an entity set.
    • Generally, all entities in a set have the same properties.
    • Attributes are simple values, e.g. integers or character strings.
attributes types

Phone#

name

customer

Bar

Attributes Types
  • Single-valued attribute
  • Multi-valued attribute
attributes types cont

Date of birth

customer

Attributes Types,cont…
  • Derived attribute: the value of this attribute is derived from the value of other related attribute

Example: Date of birth and Age

Age

e r diagrams
E/R Diagrams
  • In an entity-relationship diagram, each entity set is represented by a rectangle.
  • Each attribute of an entity set is represented by an oval or ellipse
example

name

manf

Beers

Example
  • Entity set Beers has two attributes, name and manf (manufacturer).
  • Each Beer entity has values for these two attributes, e.g. (Bud, Anheuser-Busch)
relationships
Relationships
  • A relationship connects two or more entity sets.
  • It is represented by a diamond, with lines to each of the entity sets involved.
  • Most relationship are Binary.
example9

name

addr

name

manf

Bars

Beers

Sells

Bars sell some

beers.

license

Drinkers like

some beers.

Frequents

Likes

Drinkers frequent

some bars.

Drinkers

name

addr

Example
relationship set
Relationship Set
  • entity set: is the set of entities that belong to it.
    • Example: the set of all bars in our database.
  • Relationship set is lists of related entities, one from each of the related entity sets.
example11
Example
  • For the relationship Sells, we might have a relationship set like:

Bar Beer

Joe’s Bar Bud

Joe’s Bar Miller

Sue’s Bar Bud

Sue’s Bar Pete’s Ale

Sue’s Bar Bud Lite

multiway relationships
Multiway Relationships
  • Sometimes, we need a relationship that connects more than two entity sets.
  • Suppose that drinkers will only drink certain beers at certain bars.
    • Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction.
    • But a 3-way (ternary) relationship would.
example13
Example

name

addr

name

manf

Bars

Beers

license

Preferences

Drinkers

name

addr

disadvantages of ternary relationship
Disadvantages of ternary Relationship
  • Increase complexity of system
  • Some times it is not possible to translate constraints
a typical relationship set
A Typical Relationship Set

Bar Drinker Beer

Joe’s Bar Ann Miller

Sue’s Bar Ann Bud

Sue’s Bar Ann Pete’s Ale

Joe’s Bar Bob Bud

Joe’s Bar Bob Miller

Joe’s Bar Cal Miller

Sue’s Bar Cal Bud Lite

types of relationship
Types of Relationship?
  • One-to-One relationship
  • One-to-Many relationship
  • Many-to-One relationship
  • Many-to-Many relationship
one one relationships
One-One Relationships
  • Each entity of either entity set is related to at most one entity of the other set.
  • Example: Relationship Best-sellerbetween entity sets Manfs (manufacturer) and Beers.
    • A beer cannot be made by more than one manufacturer, and no manufacturer can have more than one best-seller.
many one relationships
Many-One Relationships
  • Some binary relationships are many -one from one entity set to another.
  • Each entity of the first set is connected to at most one entity of the second set.
  • But an entity of the second set can be connected to zero, one, or many entities of the first set.
example19
Example
  • Favorite, from Drinkers to Beers is many-one.
  • A drinker has at most one favorite beer.
  • But a beer can be the favorite of any number of drinkers, including zero.
many many relationships
Many-Many Relationships
  • Sells between Bars and Beers.
  • In a many-many relationship, an entity of either set can be connected to many entities of the other set.
    • E.g., a bar sells many beers; a beer is sold by many bars.
representing multiplicity
Representing “Multiplicity”
  • Show a many-one relationship by an arrow entering the “one” side.
  • Show a one-one relationship by arrows entering both entity sets.
example many to one
Example : many-to-one

Likes

Drinkers

Beers

Favorite

example one to one
Example: one-to-one
  • Consider Best-seller between Manfs and Beers.

Best-

seller

Manfs

Beers

attributes on relationships
Attributes on Relationships
  • Sometimes it is useful to attach an attribute to a relationship especially in many-to-many relationship .

Sells

Bars

Beers

price

equivalent diagrams without attributes on relationships
Equivalent Diagrams Without Attributes on Relationships
  • Create an entity set representing values of the attribute.
  • Make that entity set participate in the relationship.
example26
Example

Sells

Bars

Beers

Prices

price

attributes on relationships27
Attributes on Relationships

Attributes on Relationships depends on the type of relationship.

In one-to-one and one-to-many relationship the attributes can be associated with one of the participating entity sets rather than with the relationship set, while in many-to-many the attribute must be associated with relationship set.

attributes on relationships one to many
Attributes on Relationships: one-to-many

Example: one -to-many (one customer may have several accounts but each account is held by only one customer).

In this case , access time [when the customer last access that account] be associated with account entity

customer account

Ahmad

Amjad

Khaled

deposit

account

customer

28

Access time

attributes on relationships one to one
Attributes on Relationships: one -to-one

The relationship attribute can be associated with either one of the participating entity set.

In this case , access time [when the customer last access that account] be associated with customer or account entity.

attributes on relationships many to many
Attributes on Relationships many -to-many

The relationship attribute must be associated with the relationship set.

In this case , access time [when the customer last access that account] must be associated with depositor relationship set, because if it was an attribute of account , we can’t determine which customer last access the joint account.

deposit

account

customer

30

Access time

many to many
Many-to-many
  • if access time attribute was an attribute of account we can’t determine which customer last access the joint account, so the attribute must be on the relationship set.

customer account

Ahmad

Amjad

Khaled

  • if access time attribute was an attribute of customer we can’t determine for which account is the last access time, so the attribute must be on the relationship set..

customer account

Ahmad – 24/4/2007

Amjad – 11/5/2006

Khaled – 25/7/2007

e r diagram
E-R diagram
  • E-R diagram can show the number of times each entity participate in relationship by associate the minimum and maximum times that shown in form L….H .
  • L = minimum H= maximum
  • The customer can have 0 or more loan and each loan must have exactly one associated customer.
  • 0 …* 1…1

borrower

loan

customer

slide33
Keys
  • A key is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key.
    • It is allowed for two entities to agree on some, but not all, of the key attributes.
  • We must designate a key for every entity set.
keys in e r diagrams
Keys in E/R Diagrams
  • Underline the key attribute(s).
  • In an Isa hierarchy, only the root entity set has a key, and it must serve as the key for all entities in the hierarchy.
example name is key for beers
Example: name is Key for Beers

Beers

name

manf

isa

Ales

color

example a multi attribute key or super key
Example: a Multi-attribute Keyor super-key

dept

number

hours

room

Courses

  • Note that hours and roomcould also serve as a
  • key, but we must select only one key(primary key)
  • -Candidate key : all possible key
  • - We want always minimal super-key
weak entity sets
Weak Entity Sets
  • is an entity that cannot be uniquely identified by its own attributes alone
  • therefore, it must use a foreign key in conjunction with its attributes to create a Primary key.
  • The foreign key is a primary key of one relation but it is an attribute in other relation
example38
Example
  • name is almost a key for football players, but there might be two with the same name.
  • number is certainly not a key, since players on two teams could have the same number.
  • But number, together with the Team-name related to the player by Plays-on should be unique.
weak entity set rules
Weak Entity-Set Rules
  • The key for a weak entity set is its own underlined attributes and the keys for the supporting entity sets.
    • E.g., player-number and team-name is a key for Players in the previous example.
in e r diagrams
In E/R Diagrams

name

number

name

Plays-

on

Players

Teams

  • Double diamond for supportingmany-one relationship
  • between weak entity and dependent entity.
  • Double rectangle for the weak entity set.
don t overuse weak entity sets
Don’t Overuse Weak Entity Sets
  • Beginning database designers often doubt that anything could be a key by itself.
    • They make all entity sets weak, supported by all other entity sets to which they are linked.
  • In reality, we usually create unique ID’s for entity sets.
when do we need weak entity sets
When Do We Need Weak Entity Sets?
  • When there is no capability of creating unique ID’s.
extended e r features
Extended E-R Features
  • 1- specialization
  • 2-Aggregation
extended e r features 1 specialization
Extended E-R Features1- specialization

Specialization: is a process of defining subgroup within an entity set

Top-down level

Beers

name

manf

High Level [super class]

isa

Ales

XYZ

color

Low Level [subclass]

Taste

another example
Another example

Person

city

name

High Level [super class]

isa

salary

customer

employee

id

Low Level [subclass]

C-id

subclasses
Subclasses
  • Subclass = special case = more properties.
  • Example: Ales are a kind of beer.
    • Not every beer is an ales, but some are.
    • Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute color.
subclasses in e r diagrams
Subclasses in E/R Diagrams
  • Isa triangles indicate the subclass relationship.
    • Point to the superclass.
e r vs object oriented subclasses
E/R Vs. Object-Oriented Subclasses
  • Subclasses inherit properties from superclasses.
extended e r features 2 aggregation
Extended E-R Features2-Aggregation
  • This E-R diagram can’t express relation across relationship.

job

Works on

branch

employee

manages

manager

extended e r features solution aggregation
Extended E-R Features-solution[Aggregation]
  • Is an abstraction through which relationships are treated as higher-level entity.

work

job

Works on

branch

employee

manages

manager

design techniques
Design Techniques
  • Avoid redundancy.
  • Limit the use of weak entity sets.
  • Don’t use an entity set when an attribute will do.
avoiding redundancy
Avoiding Redundancy
  • Redundancy occurs when we say the same thing in two different ways.
  • Redundancy wastes space and encourages inconsistency.
    • The two instances of the same fact may become inconsistent if we change one and forget to change the other, related version.
example good
Example: Good

name

name

addr

ManfBy

Beers

Manfs

This design gives the address of each manufacturer exactly once.

example bad
Example: Bad

name

name

addr

ManfBy

Beers

Manfs

manf

This design states the manufacturer of a beer twice: as an attribute and as a related entity.

example bad55
Example: Bad

name

manf

manfAddr

Beers

This design repeats the manufacturer’s address once for each beer.

entity sets versus attributes
Entity Sets Versus Attributes
  • An entity set should satisfy at least one of the following conditions:
    • it has at least one nonkey attribute.

or

    • It is the “many” in a many-one or many-many relationship.
example good57
Example: Good

name

name

addr

ManfBy

Beers

Manfs

  • Manfs deserves to be an entity set because of the nonkey attribute addr.
  • Beers deserves to be an entity set because it is the “many” of the many-one relationship ManfBy.
example good58
Example: Good

name

manf

Beers

There is no need to make the manufacturer an entity set, because we record nothing about manufacturers besides their name.

example bad59
Example: Bad

name

name

ManfBy

Beers

Manfs

Since the manufacturer is nothing but a name, and is not at the “many” end of any relationship, it should not be an entity set.

roles
Roles
  • Sometimes an entity set appears more than once in a relationship.
  • Label the edges between the relationship and the entity set with names called roles.
example61

Relationship Set

Husband Wife

Bob Ann

Joe Sue

… …

Married

husband

wife

Drinkers

Example
example62
Example

Relationship Set

Buddy1 Buddy2

Bob Ann

Joe Sue

Ann Bob

Joe Moe

… …

Buddies

1

2

Drinkers

constraints
Constraints
  • Limits to which the contents of a database must be conformed
data languages
Data Languages
  • Data Definition Language [DDL] : specify database schema and consistency constraints such as balance>=100
  • like define in c++
  • Example: define account table
  • Create table account (account-number char(10), balance integer);
  • This will create table called account and update the data dictionary [data about metadata]
  • Data Manipulation Language [DML]: database query
  • used to Retrieve data
  • Insert a new record to DB
  • Delete from DB
  • Modify information in DB
data languages65
Data Languages
  • Query : is a statement, requesting the retrieval of information
  • Example: select from one table
  • Select customer. Customer-name from customer where customer. Customer-id=123;
  • Example: select from many tables
  • Select account. Balance from depositor, account where depositor. Customer-id=123 and depositor. Account-number=account.account-number;
database access from application programs
Database Access from Application Programs
  • Application programs such banking system usually written in a host language like c++ / java.
  • Need to execute DML and DDL from inside this applications by providing an interface that can use the DML and DDL
  • We need The Open Database Connectivity(ODBC) for C++ language
  • We need The |Java Database Connectivity(JDBC) for Java Language
transaction management
Transaction Management
  • Is a collection of operations that perform a single logical function in a DB application
  • Each transaction must be atomicity , durability and consistence
  • Example : Bank transfer (AB)
  • The transfer must happen entirely or not at all atomicity
  • The transfer must be durable : persistence despite the failure
  • The transfer must be consistence
database system structures
Database system structures
  • The storage manager
  • Query processor
  • The storage manager is a program that provides the interface between the low level data stored in the database and the application programs and queries submitted to the system.
  • It includes :
    • Authorization manager
    • Transaction manager
    • File manager
  • Query processor: includes DDL and DML languages