Entity-Relationship Model. & Converting to Tables. CS 4750 Database Systems

(1)

Entity-Relationship Model

& Converting to Tables

CS 4750 – Database Systems

1

(2)

Prof. Basit’s Office Hours

2

(3)

ER Tools

Many free applications that can be used for ER diagrams!

 yEd: http://www.yworks.com/en/products_yed_about.html

 Dia http://dia-installer.de/

draw.io: https://www.draw.io/

omniGraffle (Mac) Free trial (or buy it)

http://www.omnigroup.com/products/omnigraffle/

Visio

Know of others? Please email me and let me know so I can share with the class!

…

3

(4)

ER Diagram : Heroes & Villains

4

(5)

From Last time:

Relationship Sets with Attributes

 An attribute can be attached to the relationship set

 Remember: dotted line when connecting an attribute to the relationship set

5

Relevant only with the

relationship (“advisor meeting”)

(6)

Relationship Sets with Attributes

 An employee manages a department, and each department is managed by some employee.

 If we want to store the “Start Date” from which the employee

started managing the department, associate “Start Date” with the relationship Manages

6

(7)

Let’s Try: Attributes and Relationships

7



 Each student can take many courses.

 Each course can be taken by many students.

 A grade exists only when the student takes a course

 Technically: a course could have no students, students can take no courses

cid

sec_id

id

semester name

total_credit year

grade

(8)

Alternative Notation for Cardinality Limits

8

 You can place number ranges on the line

 Allows you to be very specific

 Cardinality limits can also express participation constraints

 However, do NOT use this format for homework/exams/etc!

 Why? Nothing wrong with it, but in a DB you cannot specify

specific number, like “an instructor can advise up to 10 students”

(9)

Participation and Cardinality

 Mixing cardinality and participation

 In this example, every section must have a course (due to total participation), and every course can have multiple sections

9

(10)

For this class…

 :

 Do not use:

10

(11)

Self-referential Relationships

A binary relationship that is turn in on itself 11

(12)

Self-referential Relationship (Roles)

 Entity sets of a relationship need not be distinct

Each occurrence of an entity set can play a “role” in the relationship

 The labels (e.g. “course_id” and “prereq_id”) are called roles

 Sometimes called a “self-referential relationship”

12

(13)

Self-referential Relationship (Roles)

 Each edge to the entity set represents a different role that the entity set plays in the relationship

 A movie can have many sequels, but for each sequel there is only one original movie

13

original sequel

(14)

Let’s Try: Self-Referential

 Given the following E-R diagram. Come up with an example that can be represented by the diagram.

 An example: Microsoft buys a printer from HP

14

vendor buys product

(15)

Multi-Way Relationships

A relationship involving more than two entity sets 15

(16)

Binary vs. Unary Relationships

E-R model makes it convenient to define relationships involving more than two entity sets.

In practice, ternary (3-way) or higher-degree relationships are rare and usually add complexity to the design.

If A, B, and C are sets, a relationship R is a subset of A X B X C

B R C

A

(17)

Multi-Way Relationships

product buys company

pid

name name

person

id

description address

address name

person.id product.pid company.name

11 p111 Humpty Inc.

11 p222 Humpty Inc.

22 p111 Humpty Inc.

22 p111 Dumpty Shop

buys

Each (person, product) pair can connect to many companies.

Each (person, company) pair can connect to many products.

Each (company, product) pair can connect to many persons.

Note: instances do not exist in E-R. These tables are only to help visualize the database being designed.

(18)

Let’s try: Multi-Way Relationships

 What if we want to ensure that each (person, product) pair comes from (or connects to) a single company?

 Complete the diagram. (hint: don’t forget the cardinality)

pid

name name

person

id

description address

address name

(19)

Let’s try: Multi-Way Relationships

 Based on the E-R diagram, identify if any row in the given table is not allowed

19

11 p111 Humpty Inc.

11 p222 Humpty Inc.

22 p111 Humpty Inc.

22 p111 Dumpty Shop

pid

name name

person

id

description address

address name

buys

11 p111 Humpty Inc.

11 p222 Humpty Inc.

22 p111 Humpty Inc.

22 p111 Dumpty Shop

(20)

Let’s try: Multi-Way Relationships

 Interpret the E-R diagram. What does this mean?

Each (person, product) pair connects to at most one company.

Each (person, company) pair connects to at most one product.

Each (company, product) pair connects to many persons.

pid

name name

person

id

description address

address name

(21)

11 p111 Humpty Inc.

11 p222 Humpty Inc.

22 p111 Humpty Inc.

22 p111 Dumpty Shop

11 p333 Awesome Shop

Let’s try: Multi-Way Relationships

Based on the E-R diagram, identify if any row in the given table is not allowed

Note: instances do not exist in E-R. These tables are only to help visualize the database being designed.

pid

name name

person

id

description address

address name

11 p111 Humpty Inc.

11 p222 Humpty Inc.

22 p111 Humpty Inc.

22 p111 Dumpty Shop

(22)

Converting Multi-Way to Binary

 E-R model does not require binary relationships

 It is useful to convert u-ary relationship to a collection of binary relationships

 To convert, replace a multi-way relationship with an entity set and binary relationships

(23)

Let’s try: Multi-Way Relationships

Suggest how we may convert the 4-way relationship into an E-R diagram with binary relationships

movies stars

title year

name

studios

address

address name

salary length

genre

contracts

producing studio studio

of star

(24)

Let’s try: Multi-Way Relationships

Suggest how we may convert the 4-way relationship into an E-R diagram with binary relationships (from previous page)

(25)

Weak Entity Sets

25

(26)

ER Diagram Components -- So far, we have seen…

26

is a (strong) Entity set

Attribute

Relationship

Weak entity

Subclass

strong versus weak

(27)

ER Diagram Components

-- Now we are going to look at…

27

Attribute

Relationship

Weak entity

Subclass

now

strong versus weak

(28)

Strong and Weak Entity Sets

 Strong entity set

Entities can be identified by the values of their attributes (a primary key)

This is what we have been discussing so far

 Weak entity set

Entities cannot be identified by the values of their attributes

There is no primary key that can be made from its own attributes

An entity can be identified by a combination of their attributes (“discriminator”) AND the relationship they have with another (strong) entity set, via an “identifying relationship”

28

(29)

Weak Entity Set

 Weak entity set

doesn’t have sufficient attributes to form a primary key

It is dependant on the strong entity set it is associated with

It still needs some form of identifier: the discriminator

29

By definition, there must be

total participation between the weak entity set and its identifying relationship

Total participation

Weak entity set Strong

entity set

(30)

Weak Entity Set

 Payment – although each payment entity is distinct, payments for different loans may share the same payment number (multiple payment #1’s, 2’s, etc…)

 Discriminator – although no primary key, we need a means of distinguishing among those entities in the entity set that depend on one particular strong entity (e.g. payment-number)

 Discriminator – represented like a primary key, except with a dashed underline

 Note it is NOT correct to: put l-number in payment!

 Note the double rectangle for the weak entity set, and the double diamond for the identifying relationship

 We can now distinguish payment 1 for loan X from payment 1 for loan Y 30

(31)

Weak Entity Set – “Homework” Example

 Homework is an example of a weak entity set. It cannot exist

without course. There are many homework 1’s, homework 2’s, etc.

(Different courses may have the same homework numbers)

 Every homework must belong to a single class

 A course can have many homeworks

 Different courses may have the same homework number

 To identify a homework, we need c_number and hw_number

31

homework have course

c_number

title hw_number

total_scores due_date

(32)

Let’s try: Weak Entity Set – “Movie Studio”

Draw an ER diagram for the following scenario:

 A movie studio might have several film crews

 Film crews might be designated by a crew number (e.g. crew1, crew2, etc)

 Each film crew has multiple phone numbers

 Other movie studios might use the same designations for crews, so the attribute for crew number cannot be the key for film crew

 To name a crew uniquely, we need to give both the name of the movie studio to which it belongs and the crew number

32

(33)

Let’s try: Weak Entity Set – “Movie Studio”

 A movie studio might have several film crews

 Film crews might be designated by a crew number (e.g. crew1)

 Each film crew has multiple phone numbers

 Other movie studios might use the same designations for crews, so the attribute for crew number cannot be the key for film crew

 To name a crew uniquely, we need to give both the name of the movie studio “name” to which it belongs and the crew number “c_number”

33

crew unit_of studio

name

address c_number

phone

Total

participation Many-to-one

relationship Multivalued

attribute

(34)

Subclasses

Dealing with Hierarchy 34

(35)

ER Diagram Components

-- Now we are going to look at…

35

Attribute

Relationship

Weak entity

Subclass

now

(36)

ISA

 Entity sets = like classes in OO

 There are super-classes and subclasses.

 They’re called “IS-A” (ISA) relationships

 ** The triangle points to the specialization **

 Generalization – “PERSON”

 Specialization –

“Faculty”, “Student”, “Staff”

36

 The specialization (subclass) sets have everything the generalization (superclass) set has but more.

“A student IS-A person”, “A faculty member is a person”

(37)

37

 An entity set may contain entities that have special properties not associated with all members of the set

 Subclasses: special-case entity sets

 The specialization (subclass) sets have everything the generalization

(superclass) set has but more

“A student IS-A person”, “A faculty member is a person”

product

edu_product software

is a is a

price

name category

platforms age_group

The triangle points to the specialization

Specialization Generalization

Similar to classes in OO (super-classes and

sub-classes)

Specialization

ISA (“Is-a”)

(38)

Subclassing

Subclasses are mutually exclusive

product

is a is a

price

name category

platforms age_group

price name

Let’s try: Subclassing (Movies)

 Consider the following (partial) description of a movie scenario.

 Each movie has a title and year; title and year together uniquely

identify the movie. Length and genre are maintained for each movie.

 Among the special kinds of movies we might store in our database are cartoons and murder mysteries.

 A cartoon has, in addition to the attributes and relationships of Movies, an additional relationship called Voices that gives us a set of stars who speak, but do not appear in the movie (voice actor). Movies that are not cartoons do not have such stars.

 Murder-mysteries have an additional attribute weapon.

 Draw an E-R diagram to show the connections among the three entity sets: Movies, Cartoons, and Murder-Mysteries.

(40)

Let’s try: Subclassing (Movies)

Movies

Cartoons Murder-

Mysteries

is a is a

year

title length

weapon year

title

genre

year title

genre genre

length Voices length

to: Voice Actor

(This is an incomplete ER

Diagram)

(41)

Decisions to make…

 Binary vs. n-ary Relationship sets [additional info on this later]

 Specialization/Generalization for modularity [additional info on this later]

 Aggregation for modularity

41

(42)

42

(43)

Entity vs. Attribute

 Entities can model situations that attribute cannot model naturally

 Entities can participate in relationships

 Entities can have attributes

 Attributes cannot do any of these

43

product

price

name category

(44)

Decisions to make…

 Entity set vs. Attributes

If has more data → entity set

If is the data → attribute

 Entity set vs. Relationship set

Verb argument:

Entity set: nouns (students, faculty, loans, …)

Relationship: possession verbs (teaches, advises, battles, owns, loan payment, …)

44

person buys product

vendor

personID productID

amount

vendorID price

“price” as an entity

person product

price

buys

“price” as an attribute (adequate-is the amount)

(45)

Decisions to make…

 Binary vs n-ary relationship sets

 Specialization / generalization

45

person buys product

vendor

personID productID

amount

vendorID price

“price” as an entity

person product

price

buys

“price” as an attribute (adequate-is the amount)

(46)

Rules of Thumb

 Choose the right entities

 Keep it simple

 Don’t over complicate things

 Choose the right elements (entities vs. attributes)

 Choose the right relationships

 Follow the specification of the application

 Avoid NULL value

 Avoid redundancy

 Consider small number of tables

46

(47)

Converting E-R Diagrams to Tables

 Primary keys allow entity sets and relationship sets to be

expressed uniformly as tables which represent the contents of the database

 A database which conforms to an E-R diagram can be represented by a collection of tables

 For each entity set and relationship set there is a unique table which is assigned the name of the corresponding entity set or relationship set

 Each table has a number of columns (generally corresponding to attributes), which have unique names

47

(48)

48

(49)

Strong entity set

 Strong Entity Set (E) – attributes {a₁, a₂, …, a_n}, with a primary key :

 Direct mapping:

Entity name → table name

Attributes → columns

Primary key → same as in entity set

 e.g. student(ID, name, tot_credit)

49

student

ID name tot_credit

ID name tot_credit

nb3f Nada 15

abc5d Amelia 12

student

(50)

Strong entity set (with composite attribute)

 Create separate attributes for each component – don’t include the higher level attribute “Name”

 Name becomes: first_name, middle_name, and last_name

 e.g. person(pid, first_name, middle_name, last_name) (schema statement)

50

person

pid name middle

last first

(51)

Strong entity set

(with multivalued attribute)

 Attribute M (e.g. phone) on an entity set E (e.g.

instructor) is represented by a separate table called EM (e.g. Instructor_phone) that is, the concatenation of the two tables names, often separated by an underscore “_”

 EM has attributes: primary key of E (“ID”), and attribute corresponding to multivalued attribute M

 Primary key of this table: ALL attributes

(the primary key of entity set E and the attribute M)

 instructor(cid, name)

instructor_phone(cid, phone)

51

instructor

cid name

phone

(52)

Weak entity set

 Let A be a weak entity set, and B is the identifying strong entity set on which A depends

 Create table with primary key of B and all A’s attributes

 Primary key: combination of primary key of B (strong entity) and discriminator of A

 e.g. homework(c_number, hw_number, due_date, total_scores)

52

homework have course

c_number

title hw_number

total_scores due_date

Payment example: payment(l-number, payment-number, payment-amount, date)

Discriminator →

(53)

Relationship set

 Create a table for each side of the relationship, then figure out how to create a table for the relationship

(based on cardinality and participation!)

 For example:

product(pid, name, description) company(cname, address)

makes(cname, pid)

53

product makes company

pid

name cname

description

address

(54)

Relationship set: Many-to-Many

 Table: primary keys of both participating entity sets and any attributes on the relationship itself

 Primary key: primary keys of both participating entity sets

makes(pid, cname, quantity)

54

pid

name cname

description

address

quantity

Primary keys of both entities

(55)

Relationship set:

Many-to-One/ One-to-Many

 Table: primary keys of both participating entity sets and any attributes on the relationship itself

 Primary key: primary keys of the entity set on the “many” side

makes(pid, cname, quantity)

55

Primary key of the

“many” side

pid

name cname

description

address

quantity

(56)

Relationship set: M:1 Total Participation

 Given the total participation requires ALL entities to participate …

 Table: attributes of the entity set on the total participation side, PLUS the primary key of the “one” side. NO table for the relationship set needed!!

 Primary key: primary keys of the entity set on the “many” side (TP)

product(pid, name, description, cname, quantity) company(cname, address)

56

Primary key of the

“many” side

pid

name cname

description

address

quantity

(57)

Example:

 Scenario 1:

belongsIn(nID, deptID)

 For Scenario 2:

(many-to-one or one-to-many mapping between two strong entity sets)

 The primary key of the entity set on the “many” side (nurse) is the primary key of the relationship. So:

 belongsIn(nID, deptID)

57

 Scenario 2:

Total Participation (double line)

(58)

Example:

 Scenario 1:

 Due to the total participation, you can choose to represent this relationship (belongsIn) in a different way:

 Can choose to add the primary key of the one side (dept) to the nurse entity set since with total participation EVERY nurse must

‘belong in’ a department

 Therefore, you could choose to create the nurse table like:

 nurse(nID, fname, lname, …, deptID) (PK will still be nID)

58

 Scenario 2:

(59)

Example:

 Scenario 1:

 Due to the total participation, you can choose to represent this relationship (belongsIn) in a different way:

 Can choose to add the primary key of the one side (dept) to the nurse entity set since with total participation EVERY nurse must

‘belong in’ a department

 Therefore, you could choose to create the nurse table like:

 nurse(nID, fname, lname, …, deptID) (PK will still be nID)

59

 Scenario 2:

(60)

Relationship set: One-to_One

 Table: Either side can be used as the main table

(Which side? It doesn’t matter. Pick the one that makes the most sense!

Attributes of the entity set on the “chosen” side, PLUS the primary key of the “other” side

 Primary key: primary keys of the entity set you choose

company(cname, address, pid, quantity) product(pid, name, description)

60

Primary key of the

“chosen” side

pid

name cname

description

address

quantity

(61)

Summary: Relationship sets

 Many-to-many

Table: primary keys of the two participating entity sets and any attributes on the relationship itself (may have dups here)

Primary key: the primary keys added from the participating entity sets

 One-to-many / many-to-one

If total participation on the many side

Table: adding extra attribute (the primary key of the one side) to the many side

 Think: which side takes the primary key of the other? Side of many

Primary key: Original primary key of entity set of many side

61

(62)

Summary: Relationship sets

 One-to-one

Table: Either side can act as the “many” side, adding the primary key of the other

Which one? Doesn’t matter – which ever seems to make the most sense

Primary key: Original primary key of entity set you chose to add the primary key of the other

62

(63)

Summary: Relationship sets

 m:m BOTH of the primary keys become the primary key of the relationship

 1:m the primary key on the MANY side is the primary key of the relationship

 1:1 pick ONE of the two to be the primary key of the relationship

63

[ m = many; 1: one ]

(64)

Let’s try: E-R to Relations (1)

 Convert the following E-R diagram into relations

stars

movies name

title address

year

length genre stars-in

owns studios

name address

stars(name, address) studios(name, address)

movies(title, year, genre, length)

stars-in(title, year, starsName)

owns(title, year, studioName)

(65)

Let’s try: E-R to Relations (2)

flights(number, day, aircraft)

customers(custID, name, phone, address) bookings(number, day, custID, row, seat)

to_flight

to_cust

bookings

row seat flights

day

customers

address

number aircraft

custID name

phone

(66)

Look out for…

 If partial participation on the many side, watch for null values

 What if you specify limits? Those limits have to be managed programmatically – NOT in the DB. There isn’t a good way to manage that in the database.

66

(67)

Look out for…

 DO NOT do this for identifying relationship sets

Never create a table for the “double diamond” (“loan payment”)

67

Total participation

Weak entity set Strong

entity set

payment(l-number, payment-number, payment-amount, date) loan(l-number, amount)

“loan payment”

handled by payment

(68)

Pay attention to this situation

“teaches” relationship set

 “section” is a weak entity set

 “course” is a strong entity set which section relies on

 Remember: weak entity set primary key is: primary key of strong entity set + its

discriminator

 teaches(ID, course_id, sec_id, semester, year)

ID=instructor; course_id=course; rest:

the discriminator of section (which happens to be all three attributes) 68

(69)

Subclasses / Specialization (ISA’s)

69

product

is a is a

price

name category

platforms age_group

(70)

Subclasses / Specialization (ISA’s)

Method 1:

 Form a table for the higher level entity

 Form a table for each lower level entity set, including primary key of the higher level entity set and local attributes

 Drawback: getting info about lower levels requires accessing more tables

70

(71)

Subclasses / Specialization (ISA’s) Method 1 – Keep Everything

71

product

is a is a

price

name category

platforms age_group

product(name, price, category) edu_product(name, age_group) software(name, platforms)

(72)

Subclasses / Specialization (ISA’s)

Method 2:

 Form a table for each entity set with all local and inherited attributes.

 If specialization is total (all must be specialized), table for generalized entity is not required to store info

 Drawback: people that are more than one specialization will have repetition of some info (worker and customer)

72

(73)

Subclasses / Specialization (ISA’s)

Method 2 – Keep Specialization Entities

73

product

is a is a

price

name category

platforms age_group

edu_product(name, price, category, age_group) software(name, price, category, platforms)

(74)

Subclasses / Specialization (ISA’s)

 Tough decision – depends on how many attributes each one has

 Method 3?

 No table for specialization entity sets

 Drawback: NULL in attributes from specialization entity sets

 Although less duplication of data, need to handle NULL values

74

(75)

Subclasses / Specialization (ISA’s)

Method 3 – Keep Generalization Entity

75

product

is a is a

price

name category

platforms age_group

product(name, price, category, age_group, platforms)

(76)

Subclasses / Specialization (ISA’s)

 If balanced – create all four ---

 If more attributes in specialization ---

 If more attributes in generalization ---

 To sum up, the design decision depends on:

the number of attributes

DB administrator’s decision

 Goal: minimize duplication (there is no one “correct” way)

76

(77)

Let’s try: Subclasses

depts(name, dept_chair)

courses(deptName, number, room)

labCourses(deptName, number, computerAllocation) seminarCourses(deptName, number, research)

given_by depts

name dept_chair

courses

number room Is a

lab courses computer

allocation

Is a

seminar

courses research

(78)

Aggregation

 [Review this material on your own]

 Create a table with

primary key of aggregated relationship,

primary key of the associated entity set,

plus any descriptive attributes

78