ER Model
CSC 343 Winter 2021
MICHAEL LIUT (MICHAEL.LIUT@UTORONTO.CA) ILIR DEMA (ILIR.DEMA@UTORONTO.CA)
DEPARTMENT OF MATHEMATICAL AND COMPUTATIONAL SCIENCES UNIVERSITY OF TORONTO MISSISSAUGA
Overview of Database Design
Conceptual Designs
What are the entities and relationships in the enterprise?
What information about these entities and relationships should we store in
our database?
What are the integrity constraints and business rules that hold? i.e. We need to think about “Data Governance”
2
Purpose of Entity-Relationship (ER) Model
Allows us to create a visual representation of the database schema design.
◦ These are called entity-relationship diagrams.
◦ This visual also allows us to depict some constraints imposed in our schema.
Conversion of ER designs to relational database designs. ◦ This will come later!
3
Framework for ER Model
Design is a serious business! You are the architect of the database!
Business and management know they want/need a database, but they usually don’t have sufficient background or expertise to tell you what they want in it.
Sketching the key components is a great way to view the hierarchal structure, as well as an efficient way to develop a “good” working database.
4
Entity Sets
Entityàa “thing” or object.
Entity Setàa collection of similar entities.
◦ For those programmers out there; it’s similar to a class in an OO language. ◦ Each entity set has a key.
Attributeàproperty of an entity set.
◦ Attributes are simple values. e.g. int, char, or str, NOT struct, sets, etc… ◦ Each attribute has a domain.
5
ER Diagrams
Technicalities for drawing Diagrams (in this course): ◦ Entity Setàa rectangle.
◦ Attributeàan oval.
This is an attribute
◦ Requires a line to the oval, from the rectangle, representing its Entity Set.
NOTE: Notation varies! Some textbooks represent attributes within the (entity) rectangle.
6
Example
Entity Set Beers has two attributes: 1. name
2. manf (manufacturer).
Beers
Each Beers entity has values for these two attributes (entities). e.g. (Bud, Anheurser-Busch)
name
manf
7
Relationships
A relationship is a connection between two or more entity sets.
It is represented by a diamond on an E/R diagram.
◦ Lines connecting it to each of the entity sets involved is required.
8
Example
name addr license
Sells
name manf
Beers
Likes
Bars
Frequents
Drinkers
name addr
9
Relationship Set
The current “value” of an entity set is the set of entities that belong to it. e.g. the set of all bars in our database.
The “value” of a relationship is a relationship set, a set of tuples with one component for each related entity set.
Formally:
An n-ary relationship set R relates n entity sets E1, …, En; where each relationship in R involves E1, …, En.
◦ Same entity set could participate in different relationship sets, or different “roles” in same set.
10
Example
For the relationship Sells, we might have a relationship set like:
Bar
Beer
Joe’s Bar
Canadian
Joe’s Bar
Stella
Joe’s Bar
Miller
Tammy’s Bar
Canadian
Tammy’s Bar
Corona
11
Multi-way 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 relationship would.
12
Example
name addr license
Preferences
Bars
Drinkers
name addr
Beers
name manf
13
A Typical Relationship Set
Bar
Drinker
Beer
Joe’s Bar
Jenna
Canadian
Joe’s Bar
Abdi
Stella
Joe’s Bar
James
Miller
Tammy’s Bar
Jenna
Canadian
Tammy’s Bar
Abdi
Corona
Joe’s Bar
Abdi
Bud
Tammy’s Bar
James
Bud
Tammy’s Bar
James
Sleemans
14
Many-Many Relationship
Focus: binary relationships
e.g. 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.
15
Many-Many Illustrated
Note: each line is an instance of the binary relationship!
16
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.
17
Many-One Illustrated
(Partial) Function on entity set.
Bars Beers Drinkers
18
Example
Favourite, from Drinkers to Beers is many-to-one
A drinker has at most one favourite beer.
A beer can be the favourite of any number of drinkers (0 included).
Beers
Drinkers
19
One-One Relationship
Beers
Each entity of either entity set is related to (at most) one entity of the other set.
e.g. Relationship Best-Seller between entity-sets Manufacturers and Beers. ◦ A beer is the best seller for {0|1} manufacturers, and no manufacturer
can have more than one best-seller (assume no ties).
Manfs Beers Manfs
20
Multiplicity
Multiplicity is the cardinality (maximum number of relationships that occur) and the participation (in a relationship).
E.g., a many-one relationship has one entity set which is entering (at most) one, while a one-one relationship has both entity sets entering (at most) one.
21
Example: Many-One Relationship
M
Likes
M
Drinkers
Beers
1
M
Favourite
Notice that two relationships connect the same entity-sets but are different!
22
Example: Many-One Relationship
M
Likes
M
Drinkers
Beers
1
M
Favourite
Dashed line, indicating partial participation.
23
Example: One-One Relationship
Consider Best-Seller between Manfs and Beers.
◦ Some Beers are not the best-seller of any manufacturer. ◦ But a Beer manufacturer must have a best-seller.
Manf Best- Seller
Beers
24
In the E-R Diagram
Manfs Best- Beers 1 Seller 1
A manufacturer has exactly one best beer.
A beer is the best- seller for 0 or 1 manufacturer.
25
Participation Constraint
Does every student have to take a course?
◦ If so, this is a participation constraint: the participation of Students in
Enrolled is said to be total (vs. partial).
◦ Every SID value in Students table must appear in a row of the Enrolled table (with a non-null SID value!)
26
Example: Many-One Relationship
Participation of this entity is partial.
M
Drinkers
Likes M
Beers
Favourite M
1
Note: Two relationships connect the same entity sets, but are different.
27
Example: Many-One Relationship
Likes
M
Drinkers
1
M
Beers
Participation of this entity is total.
Favourite
M
28
Chen’s Notation
ØThis is what we will be using in this course! ØTake a look at the handout for reference.
29
Key Constraints
Many-Many: “An employee can work in many departments, and a department can have many employees.”
One-Many: “A department has at most one manager, and employees can manage many departments.”
M
M
WorksIn M
Employee
Department
Manages
1
30
Participation Constraints
Does every department have a manager?
◦ If yes, then then every department must appear in the manages relation:
partial participation vs. total participation.
Manages
Employee
Department
31
Attributes on Relationships
In certain instances, it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set.
◦ i.e., a type of connector/bridge between entity sets to satisfy an entity. Let’s see an example!
32
Example
Bars sell Beers; Beers have a price; Bars have a price.
Bars Sell Beers price
33
Example
Bars Sell Beers price
Explanation:
Price is a function of both the Bar and the Beer, not of one alone.
e.g., “The price of Miller beer at Joe’s bar.”
34
Equivalent Diagrams
i.e., without attributes on relationships
Create an entity set representing values of the attribute (entity). Make that entity set participate in the relationship.
Let’s see an example!
35
Example
Bars
Beers
Sells
1
Prices
price
Bars and Beers are required to form Prices and thus a “price” attribute/entity.
36
Roles
Sometimes an entity set appears more than once in a relationship.
Label the edges between the relationship and entity set with names called roles.
37
EXAMPLE
Relationship Set
Husband
Wife
John
Elizabeth
Warren
Alice
…
…
Married
Husband Wife
Drinkers
Notationally you denote the cardinality, not the name of the attribute
38
EXAMPLE
Relationship Set
Married
1
Drinkers
Husband
Wife
John
Elizabeth
Warren
Alice
…
…
39
EXAMPLE
Relationship Set
Buddies
Buddy1 Buddy2 Drinkers
Buddy1
Buddy2
Mike
Joe
Liz
Lisa
Jenny
Peter
Courtney
Moe
…
…
40
EXAMPLE 2
Relationship Set
Buddies
M
Drinkers
Buddy1
Buddy2
Mike
Ilir
Mike
Lisa
Ilir
Lisa
Andrew
Dan
…
…
41
Subclasses
Subclass (hierarchical) = special case = more properties e.g., Ales are a kind of beer.
◦ Not ever beer is an ale, but some are.
◦ Let us suppose that in addition to all the properties (attributes and relationships) of beers also have the attribute colour.
42
Subclasses in ER Diagrams
ISA triangles indicate the subclass relationship. ◦ Point to the superclass.
Reasons for using ISA:
◦ To add descriptive attributes specific to a subclass.
◦ To identify entities that participate in a relationship.
43
Example
Name Manf Colour
Beers
ISA
Ales
Note:
Assume subclasses form a tree.
44
ISA (“is a”) Hierarchies
As in C++, or other PLs, attributes are inherited.
If we declare A ISA B, every A entity is also considered to be a B entity.
sin
hours_worked
name
lot
Employees
• Overlap constraints: Can two sub-classes contain the same entity?
e.g. Can Mike be an Hourly_Emps as well as a Contract_Emps entity?
• Covering constraints: Does every Employees entity have to be an Hourly_Emps or a Contract_Emps entity?
hourly_wages
ISA
contractid
Hourly_Emps
Contract_Emps
45
ISA (“is a”) Hierarchies
Reasons for using ISA:
◦ To add descriptive attributes specific to a subclass.
◦ To identify entities that participate in a relationship.
hourly_wages
sin
hours_worked
name
lot
Employees
ISA
contractid
Hourly_Emps
Contract_Emps
46
Aggregation
Used when we have to model a relationship involving (entity sets and) a relationship set.
Aggregation allows us to treat a relationship set as an entity set for the purposes of participation in (other) relationships.
name
sin
lot
Employees
Monitors
until
started_on pbudget
since
dname
pid
did Sponsors
budget
Projects
Departments
47
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.
◦ This is identified by underlining the key attribute(s).
48
Example: Multi-Attribute Key
Courses
dept number hours room
Note:
hours and room could also serve as a key, but we must select only one primary key (compound in this case).
49
Keys
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.
Name Manf Colour
Beers
ISA
Ales
50
Weak Entity Sets
Occasionally, entities of an entity set need “help” to identify them uniquely. Let E represent an entity set; E is said to be weak if:
• E is uniquely identifiable by more than one many-one relationships from E
and
• E includes the key of the related entities from the connected entity sets
51
Example: Weak Entity Set
name is almost a key for football players, but there could potentially be two players with the same name.
number is certainly not a key, since players on two teams could have the same number.
But Teams name and number (i.e. {name, number}) with relation to the player by Plays-on is unique.
52
Example: Weak Entity Set
name
Players 1 Plays-on M Teams number name
Double diamond for identifying the many-one relationship. Double rectangle for the weak entity-set.
53
Weak Entity-Set Rules
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.
◦ Supporting relationships must have a double-diamond.
54
Weak Entity-Set Rules
The key for a weak entity set is its own underlined attributes and the keys from supporting entity sets.
e.g., number (Players) and name (Teams) is a key for Players.
55
Example
name
Players 1 Plays-on M Teams
number name
e.g., number (Players) and name (Teams) is the key for Players.
56
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.
57
1. Avoiding Redundancy
Redundancy is saying the same thing in two (or more) different ways. ◦ “the inclusion of extra components that are not strictly necessary to
functioning; repetition or superfluity of information.”
Redundancy wastes space and (more importantly) encourages inconsistency.
◦ Multiple representations of the same fact become inconsistent if we modify one and forget/do not modify its counterpart.
58
Example: Bad
Beers
1 ManfBy M
Manfs
name addr
name
manf
Note:
This design states the manufacturer of a beer twice: as an attribute and as a related entity set.
59
Example: Good
Beers 1 ManfBy M name
Manfs
name addr
Note:
This design gives the address of each manufacturer exactly one.
60
Example: Bad
Pause. Think why?
Beers
name manf
manfAddr
Note:
This design is repetitive! The manufacturer’s address will be repeated for each beer. Also, in the event there are temporarily no beers for a manufacturer the value is lost.
61
Entity Sets vs. Attributes
An entity set should satisfy (at least) one of the following:
◦ It is more than the name of something (i.e., it has at least one non-key attribute).
OR
◦ It is the “many” in a many-one or many-many relationship.
Depends on the application requirements:
◦ If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued).
◦ If the structure (city, street, etc…) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).
62
Example: Good
Beers 1 ManfBy M name
Manfs
name addr
Manfs deserves to be an entity set because of the non-key attribute addr.
Beers deserves to be an entity set because of the duplication issue with Manfs’ addr seen earlier.
63
Example: Bad
Beers 1 ManfBy M name
Manfs
name
Since the manufacturer is nothing but a name, and neither does beers, it can be one unified entity set.
64
Example: Good
There is no need to make the manufacturer an entity set, because we record nothing about manufacturers beside their name.
Beers
name
manf
65
2. Limit the use of Weak Entity Sets
Novice 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.
◦ Examples include: Social Insurance Numbers, automobile’s VINs, etc…
66
When do we need Weak Entity Sets?
The usual reason is that there is no ‘global authority’ capable of creating unique IDs.
e.g., it is unlikely that there could be an agreement to assign an unique player number across all football teams in the world.
67
From E/R Diagrams to Relations
Entity Setàrelation
◦ Attributesàattributes
Relationshipsàrelations whose attributes are only: ◦ The keys of the connected entity-sets.
◦ Attributes of the relationship itself.
68
Entity-Set à Relation Relation: Beers(name, manf)
Beers
name
manf
69
Relationship à Relation
M M
name manf
Beers
Likes
M
Drinkers
1
Buddies
Favourite
1
name
addr
Likes(drinker, beer) Favourite(drinker, beer) Buddies(buddy1, buddy2) Married(husband, wife)
1
Married
70
Handling Weak Entity Sets
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, non-key attributes.
A supporting relationship is redundant and yields no relation (unless it has attributes).
71
Example: Weak Entity SetàRelation
Logins
At Hosts name
Hosts(hostName, location) Logins(loginName, hostName, billTo) At(loginName, hostName)
location
name
billTo
72
Example: Weak Entity SetàRelation
Logins
At Hosts name
Hosts(hostName, location) Logins(loginName, hostName, billTo) At(loginName, hostName)
location
name
billTo
Note:
At becomes part of Logins.
73
Entity vs. Relationship
since name
lot
dbudget did
dname
ER diagram is OK if a manager gets a separate discretionary budget for each Departments.
What if a manager gets a discretionary budget that covers all managed Departments?
Redundancy
vdbudget stored for each dept managed by manager.
Misleading
vSuggest dbudget associated with department manager combination.
sin
budget
Employees
Departments
sin
name
ISA
lot
Manages2
since Manages2
dname
Employees
did
budget
Departments
Managers
dbudget
74
Summary
Conceptual design follows requirements analysis,
◦ Yields a high-level description of data to be stored
ER model popular for conceptual design
◦ Constructs are expressive, close to the way people think about their
applications.
Basic constructs: entities, relationships, and attributes (of entities and relationships).
Some additional constructs: weak entities, ISA hierarchies.
75
Summary
Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set.
◦ Some constraints cannot be expressed in the ER model.
◦ Notably, functional dependencies (FDs). We will talk about these
later in the course.
◦ Constraints play an important role in determining the best database design for an enterprise.
76
Summary
ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise.
Common choices include:
◦ Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies
◦ Ensuring good database design: resulting relational schema should be analyzed and refined further.
77
Flowchart Building
Online Tools:
◦ https://www.draw.ioàwe will be using this in this course
Applications:
◦ OmniGraffle Pro
◦ https://www.omnigroup.com/omnigraffle ◦ Microsoft PowerPoint or Word
◦ There are many more…
78
Questions?
Q
THANKS FOR LISTENING
I’LL BE ANSWERING QUESTIONS NOW
&
A
79
Citations, Images and Resources
Database Management Systems (3rd Ed.), Ramakrishnan & Gehrke
Some content is based off the slides of Dr. Fei Chiang – http://www.cas.mcmaster.ca/~fchiang/ https://en.oxforddictionaries.com/definition/redundancy
80