Entity-Relationship Model - PowerPoint PPT Presentation

Entity-Relationship Model

E-R Diagrams

Class hierarchies

Weak entity sets

• 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 = “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.

Phone#

name

customer

Bar

• Single-valued attribute
• Multi-valued attribute

Date of birth

customer

• Derived attribute: the value of this attribute is derived from the value of other related attribute

Example: Date of birth and Age

Age

• 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

name

manf

Beers

• Entity set Beers has two attributes, name and manf (manufacturer).
• Each Beer entity has values for these two attributes, e.g. (Bud, Anheuser-Busch)

• 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.

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

• 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.

• 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

• 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.

name

addr

name

manf

Bars

Beers

license

Preferences

Drinkers

name

addr

• Increase complexity of system
• Some times it is not possible to translate constraints

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

• One-to-One relationship
• One-to-Many relationship
• Many-to-One relationship
• Many-to-Many relationship

• 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.

• 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.

• 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.

• 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.

• Show a many-one relationship by an arrow entering the “one” side.
• Show a one-one relationship by arrows entering both entity sets.

Likes

Drinkers

Beers

Favorite

• Consider Best-seller between Manfs and Beers.

Best-

seller

Manfs

Beers

• Sometimes it is useful to attach an attribute to a relationship especially in many-to-many relationship .

Sells

Bars

Beers

price

• Create an entity set representing values of the attribute.
• Make that entity set participate in the relationship.

Sells

Bars

Beers

Prices

price

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.

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

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.

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

• 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 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

• 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.

• 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.

Beers

name

manf

isa

Ales

color

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

• 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

• 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.

• 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.

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.

• 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 there is no capability of creating unique ID’s.

• 1- specialization
• 2-Aggregation

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

Person

city

name

High Level [super class]

isa

salary

customer

employee

id

Low Level [subclass]

C-id

• 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.

• Isa triangles indicate the subclass relationship.
• Point to the superclass.

• Subclasses inherit properties from superclasses.

• This E-R diagram can’t express relation across relationship.

job

Works on

branch

employee

manages

manager

• Is an abstraction through which relationships are treated as higher-level entity.

work

job

Works on

branch

employee

manages

manager

• Avoid redundancy.
• Limit the use of weak entity sets.
• Don’t use an entity set when an attribute will do.

• 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.

name

name

addr

ManfBy

Beers

Manfs

This design gives the address of each manufacturer exactly once.

name

name

addr

ManfBy

Beers

Manfs

manf

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

name

manf

manfAddr

Beers

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

• 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.

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.

name

manf

Beers

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

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.

• 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.

Relationship Set

Husband Wife

Bob Ann

Joe Sue

… …

Married

husband

wife

Drinkers

Relationship Set

Buddy1 Buddy2

Bob Ann

Joe Sue

Ann Bob

Joe Moe

… …

Buddies

1

2

Drinkers

• Limits to which the contents of a database must be conformed

• 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

• 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;

• 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

• 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

• 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