EntityRelationshipModel

EntityRelationshipModel - Transcript

Entity Relationship Model
E R Diagrams Weak Entity Sets Converting E R Diagrams to Relations
1

Purpose of E R Model
x The E R model allows us to sketch database schema designs x Designs are pictures called entity relationship diagrams x Later convert E R designs to relational DB designs
2

Includes some constraints but not operations

Framework for E R
x Design is a serious business x The boss knows they want a database but they don t know what they want in it x Sketching the key components is an efficient way to develop a working database
3

Entity Sets
x Entity thing or object x Entity set collection of similar entities

x Attribute property of the entities of an entity set

Similar to a class in object oriented languages

Attributes are simple values e g integers or character strings not structs sets etc
4

E R Diagrams
x In an entity relationship diagram
Entity set rectangle Attribute oval with a line to the rectangle representing its entity set

5

Example

name

manf

Beers

x Entity set Beers has two attributes name and manf manufacturer x Each Beers entity has values for these two attributes e g Bud Anheuser Busch
6

Relationships
x A relationship connects two or more entity sets x It is represented by a diamond with lines to each of the entity sets involved

7

Example Relationships
name Bars license
Note license beer full none

addr Sells

name

manf Bars sell some beers Drinkers like some beers Drinkers frequent some bars 8

Beers

Frequents

Likes

name

Drinkers

addr

Relationship Set
x The current value of an entity set is the set of entities that belong to it x The value of a relationship is a relationship set a set of tuples with one component for each related entity set
9

Example the set of all bars in our database

Example Relationship Set
x For the relationship Sells we might have a relationship set like
Bar Joe s Bar Joe s Bar Sue s Bar Sue s Bar Sue s Bar Beer Bud Miller Bud Pete s Ale Bud Lite 10

Multiway Relationships
x Sometimes we need a relationship that connects more than two entity sets x 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 relationship would
11

Example 3 Way Relationship
name license Bars Preferences addr name Beers manf

Drinkers name addr 12

A Typical Relationship Set
Bar Joe s Bar Sue s Bar Sue s Bar Joe s Bar Joe s Bar Joe s Bar Sue s Bar Drinker Ann Ann Ann Bob Bob Cal Cal Beer Miller Bud Pete s Ale Bud Miller Miller Bud Lite

13

Many Many Relationships
x Focus binary relationships such as Sells between Bars and Beers x 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
14

In Pictures

many many 15

Many One Relationships
x Some binary relationships are many one from one entity set to another x Each entity of the first set is connected to at most one entity of the second set x But an entity of the second set can be connected to zero one or many entities of the first set
16

In Pictures

many one 17

Example Many One Relationship
x Favorite from Drinkers to Beers is many one x A drinker has at most one favorite beer x But a beer can be the favorite of any number of drinkers including zero

18

One One Relationships
x In a one one relationship each entity of either entity set is related to at most one entity of the other set x Example Relationship Best seller between 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 assume no ties
19

In Pictures

one one 20

Representing Multiplicity
x Show a many one relationship by an arrow entering the one side

x Show a one one relationship by arrows entering both entity sets x Rounded arrow exactly one i e each entity of the first set is related to exactly one entity of the target set
21

Remember Like a functional dependency

Example Many One Relationship

Drinkers

Likes

Beers

Favorite

Notice two relationships connect the same entity sets but are different 22

Example One One Relationship
x Consider Best seller between Manfs and Beers x Some beers are not the best seller of any manufacturer so a rounded arrow to Manfs would be inappropriate x But a beer manufacturer has to have a best seller
23

In the E R Diagram
Best seller

Manfs

Beers A manufacturer has exactly one best seller

A beer is the best seller for 0 or 1 manufacturer

24

Attributes on Relationships
x Sometimes it is useful to attach an attribute to a relationship x Think of this attribute as a property of tuples in the relationship set

25

Example Attribute on Relationship
Bars Sells Beers

price Price is a function of both the bar and the beer not of one alone 26

Equivalent Diagrams Without Attributes on Relationships
x Create an entity set representing values of the attribute x Make that entity set participate in the relationship

27

Example Removing an Attribute from a Relationship
Bars Sells Beers
Note convention arrow from multiway relationship all other entity sets together determine a unique one of these

Prices price

28

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

29

Example Roles

Relationship Set Wife Ann Sue

Married husband Drinkers wife

Husband Bob Joe

30

Example Roles

Relationship Set

Buddies 1 Drinkers 2

Buddy1 Buddy2 Bob Ann Joe Sue Ann Bob Joe Moe

31

Subclasses
x Subclass special case fewer entities more properties x Example Ales are a kind of beer
Not every beer is an ale but some are Let us suppose that in addition to all the properties attributes and relationships of beers ales also have the attribute color
32

Subclasses in E R Diagrams
x Assume subclasses form a tree x Isa triangles indicate the subclass relationship
Point to the superclass I e no multiple inheritance

33

Example Subclasses
name Beers isa color Ales manf

34

E R Vs Object Oriented Subclasses
x In OO objects are in one class only x In contrast E R entities have representatives in all subclasses to which they belong
Subclasses inherit from superclasses

Rule if entity e is represented in a subclass then e is represented in the superclass and recursively up the tree
35

Example Representatives of Entities
name Beers isa color Ales manf Pete s Ale

36

Keys
x 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 x We must designate a key for every entity set
37

It is allowed for two entities to agree on some but not all of the key attributes

Keys in E R Diagrams
x Underline the key attribute s x 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

38

Example name is Key for Beers
name Beers isa color Ales manf

39

Example a Multi attribute Key
dept number hours room

Courses Note that hours and room could also serve as a key but we must select only one key

40

Weak Entity Sets
x Occasionally entities of an entity set need help to identify them uniquely x Entity set E is said to be weak if in order to identify entities of E uniquely we need to follow one or more many one relationships from E and include the key of the related entities from the connected entity sets
41

Example Weak Entity Set
x name is almost a key for football players but there might be two with the same name x number is certainly not a key since players on two teams could have the same number x But number together with the team name related to the player by Plays on should be unique
42

In E R Diagrams
name number Players Plays on name Teams

Note must be rounded because each player needs a team to help with the key Double diamond for supporting many one relationship Double rectangle for the weak entity set 43

Weak Entity Set Rules
x A weak entity set has one or more many one relationships to other supporting entity sets

Not every many one relationship from a weak entity set need be supporting But supporting relationships must have a rounded arrow entity at the one end is guaranteed
44

Weak Entity Set Rules 2
x 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

45

Design Techniques
1 Avoid redundancy 2 Limit the use of weak entity sets 3 Don t use an entity set when an attribute will do

46

Avoiding Redundancy
x Redundancy saying the same thing in two or more different ways x Wastes space and more importantly encourages inconsistency
Two representations of the same fact become inconsistent if we change one and forget to change the other Recall anomalies due to FD s
47

Example Good
name Beers ManfBy name Manfs addr

This design gives the address of each manufacturer exactly once 48

Example Bad
name Beers manf This design states the manufacturer of a beer twice as an attribute and as a related entity 49 ManfBy name Manfs addr

Example Bad
name manf Beers manfAddr

This design repeats the manufacturer s address once for each beer and loses the address if there are temporarily no beers for a manufacturer 50

Entity Sets Versus Attributes
x An entity set should satisfy at least one of the following conditions




It is more than the name of something it has at least one nonkey attribute or It is the many in a many one or many many relationship

51

Example Good
name Beers ManfBy name Manfs addr

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 52

Example Good
name Beers manf

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

Example Bad
name Beers ManfBy name 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 54

Don t Overuse Weak Entity Sets
x Beginning database designers often doubt that anything could be a key by itself

x In reality we usually create unique ID s for entity sets
Examples include social security numbers automobile VIN s etc
55

They make all entity sets weak supported by all other entity sets to which they are linked

When Do We Need Weak Entity Sets
x The usual reason is that there is no global authority capable of creating unique ID s x Example it is unlikely that there could be an agreement to assign unique player numbers across all football teams in the world
56

From E R Diagrams to Relations
x Entity set relation
Attributes attributes

x Relationships relations whose attributes are only

The keys of the connected entity sets Attributes of the relationship itself

57

Entity Set Relation
name manf

Beers

Relation Beers name manf
58

Relationship Relation
name husband addr Likes name Beers manf Drinkers 1 2 Favorite wife Married Likes drinker beer Favorite drinker beer Buddies name1 name2 Married husband wife 59

Buddies

Combining Relations
x OK to combine into one relation
1 The relation for an entity set E 2 The relations for many one relationships of which E is the many

x Example Drinkers name addr and Favorite drinker beer combine to make Drinker1 name addr favBeer
60

Risk with Many Many Relationships
x Combining Drinkers with Likes would be a mistake It leads to redundancy as
name addr Sally 123 Maple Sally 123 Maple beer Bud Miller

Redundancy 61

Handling Weak Entity Sets
x Relation for a weak entity set must include attributes for its complete key including those belonging to other entity sets as well as its own nonkey attributes x A supporting relationship is redundant and yields no relation unless it has attributes
62

Example Weak Entity Set Relation
name billTo Logins At name Hosts location

Hosts hostName location Logins loginName hostName billTo At loginName hostName hostName2 At becomes part of Logins Must be the same 63

Subclasses Three Approaches
1 Object oriented One relation per subset of subclasses with all relevant attributes 2 Use nulls One relation entities have NULL in attributes that don t belong to them 3 E R style One relation for each subclass
Key attribute s Attributes of that subclass

64

Example Subclass Relations
name Beers isa color Ales manf

65

Object Oriented
name manf Bud Anheuser Busch Beers name manf Summerbrew Pete s Ales color dark

Good for queries like find the color of ales made by Pete s 66

E R Style
name manf Bud Anheuser Busch Summerbrew Pete s Beers name color Summerbrew dark Ales Good for queries like find all beers including ales made by Pete s 67

Using Nulls
name manf color Bud Anheuser Busch NULL Summerbrew Pete s dark Beers Saves space unless there are lots of attributes that are usually NULL 68