程序代写代做代考 database ER Microsoft PowerPoint - 7- DatabaseDesign_ER_UML_V2

Microsoft PowerPoint – 7- DatabaseDesign_ER_UML_V2

Database Design:
ER and UML Diagrams

Abdu Alawini
University of Illinois at Urbana-Champaign

CS411: Database Systems

October 1, 2018

Annoucements

•HW 1 is due today
•HW 2 will be posted on Wednesday
•Project Track 1 – Stage 1 is due on Wednesday
•The class is full and registration is close
•no students from the waitlist will be allowed to

•Conceptual design: (ER & UML Models are used
for this.)
• What are the entities and relationships we need?

•Logical design:
• Transform ER design to Relational Schema

•Schema Refinement: (Normalization)
• Check relational schema for redundancies and related

anomalies.

•Physical Database Design and Tuning:
• Consider typical workloads; (sometimes) modify the

database design; select file types and indexes.

Overview of Database Design

We’ll do
this later

•Relational model has:
•tables (relations) with attributes, keys, foreign keys,

domain definitions for attributes

•Entity-Relationship model has:
•Entities and entity sets with attributes, keys, and

domain definitions for attributes

•Relationships among entities and relationship
sets with uniqueness or cardinality constraints

Entity-Relationship Model is a different
model than the Relational Model

Entity Relationship Model
Unified Modeling Language

• Proposed by Peter Chen in 1976
• Gives us a language to specify

• What information the database must hold
• How the bits of information relate to one another

• UML is a standard language for designing software systems
• also used for DB design

• created by the Object Management Group (OMG)
• UML 1.0 specification draft was proposed to the OMG in early 1997.

ER Model

UML Model

Agenda

• basics of ER and UML models
• constraints
• weak entity sets

Entity-Relationship Diagram (original
syntax)

Employee

ssn
name

Department

Project

number

name

Start date

End date

budget

assigned

manager

sponsor

home
code name

title

Entity set

Relationship set

Attribute name

Legend:

• Entity: Real-world object distinguishable from other objects.
An entity is described using a set of attributes.

• Entity Set: A collection of similar entities. E.g., all employees.
(often referred to as just entity, which blurs the distinction between type and
collection)

Definitions

• Relationship: Association among 2 or more entities. E.g., Kristin’s home
department is Research & Development.

• Relationship Set: Collection of similar relationships. E.g., Home (often
referred to as just relationship).

Definitions

Relationships

• Formal definition:
• if A, B are sets, then a relation R is a subset of A x B

• A={1,2,3}, B={a,b,c,d},
R = {(1,a), (1,c), (3,b)}

Same story w/ entity sets

sponsor is a subset of Project x Department:

sponsor DepartmentProject

• one-one:

• many-one:

• many-many:

Multiplicity of E/R Relationships

1
2
3

a
b
c
d

1
2
3

a
b
c
d

1
2
3

a
b
c
d

 Multiplicity can be shown with arrows

 Arrow = at most 1

 Another interpretation: “determines”

One on LHS/RHS
connected to at most

one on RHS/LHS

One on LHS
connected to at most

one on RHS

No constraints

Q: Example scenarios for each case?

• one-one:

• many-one:

• many-many:

Department – head ??

Actor – play ??

Employee – company ??

Q: Example scenarios for each case?

• one-one:

• many-one:

• many-many:

Department – head ?? Each dep has 1 head, each head has 1 dep: one-one

Actor – play ?? Each actor has many plays, each play has many actors: many-many

Employee – company ?? Each employee has one company, each company has many
employees

UML version of the same E-R Diagram

assigned

manager

sponsor

home

Employee
ssn

name
title

Department
code
name

Project
number
name

start-date
end-date
budget

Employee (ssn, name, title, home-dept)

Project-team(ssn, number)

Department (id, name, manager)

Project (number, name, start-date, end-date, budget, sponsor)

Equivalent Relational Schema

assigned

manager

sponsor

home

Employee
ssn

name
title

Department
code
name

Project
number
name

start-date
end-date
budget

Cardinality Constraints on Relationship sets:
How many entities can participate?

assigned

manager

sponsor

home

Employee
ssn

name
title

Department
code
name

Project
number
name

start-date
end-date
budget

Cardinality Constraints on Relationship sets:
How many entities can participate?

assigned

manager

sponsor

home

Employee
ssn

name
title

Department
code
name

Project
number
name

start-date
end-date
budget

0..*

1..1

0..*

1..1

0..1

assigned

manager

sponsor

home

Employee
ssn

name
title

Department
code
name

Project
number
name

start-date
end-date
budget

0..*

1..1

0..*

1..1

0..1

An employee can have 0 or 1 home departments

assigned

manager

sponsor

home

Employee
ssn

name
title

Department
code
name

Project
number
name

start-date
end-date
budget

0..*

1..1

0..*

1..1

0..1

A department
can be home to

0 to any number
of employees

Some Alternative Constraints (in UML)

home
title
name
ssn
Employee

name
code

Departments

1..1 0..*

home
title
name
ssn
Employee

name
code

Departments

0..* 0..1

home
title
name
ssn
Employee

name
code

Departments
0..* 1..*

Which one is right?
We must discover the semantics of the

application!

Relationship sets can have attributes

title namecode

start-date
name

home DepartmentEmployee

ssn

descriptive attribute
of the relationship set

home

Employee
ssn

name
title

Department
code
name

0..* 0..1

start-date

E-R
notation

UML
notation

home

Employee
ssn

name
title

Department
code
name

0..* 0..1

? ?
?

Try all three locations for the attributes:
which one makes sense?

start-date

Relationship sets can have role names
(in addition to the name of the relationship set)

manager

Employee
ssn

name
title

Department
code
name

1..1 0..1

managesmanaged-by

role name role name
relationship set name

Example: reading role names

manager

Employee
ssn

name
title

Department
code
name

1..1 0..1

managesmanaged-by

role name role name
relationship set name

“An employee manages 0 or 1 departments”

Same entity sets can participate in
different “roles” for the same

relationship set

subordinate

Reports_to

title

name

Employee

supervisor

ssn

Reports-to

Employee
ssn

name
title

0..*

0..1

subordinate

supervisor

E-R
notation

UML
notation

This explains why there are so many different ways to
design a schema.

Duality: entity value
and attribute relationship

Project
P-number
P-name

Due-Date

Employee
ssn

E-name
Office

Assignment

Manager

Should Office be an attribute of Employee? Or a
separate entity set? Most attributes can be “promoted”
to an entity set and some entities can be “demoted” to

an attribute value.

Entity vs. Value of an Attribute

Project
P-number
P-name

Due-Date

Employee
ssn

E-name

Assignment

Manager

What are some reasons to model Office as an entity set?

Office
O-number

Assigned
Office

• Office needs to participate in other relationship sets such as a
relationship set connecting to telephones jacks or network drops
(located in the office)

• There are other attributes of Office

• An employee can have more than one office

Entity vs. Value of an Attribute

Project
P-number
P-name

Due-Date

Employee
ssn

E-name

Assignment

Manager

Office
O-number

Sq-ft
Floor

Assigned
Office

NetworkDrop
Outlet-Number

Has-drop

Multiway Relationships
How do we model a purchase relationship between buyers,
products and stores?

Product

Person

StorePurchase

 Can still model as a mathematical set (how ?)
• Yes: As a subset of product x store x person

Q: what does the arrow mean ?

VideoStore

Person

Movie

Invoice

Rental

Arrows in Multiway Relationships

“At most one”. That is, a specific combination of videostore,
invoice and person can correspond to at most one movie.

What if I had an arrow into Person?Q:

Q: how do I say: “invoice determines store” ?

A: no good way; best approximation:

Q: Why is this bad ?

A: We aren’t clarifying that the store is a function
of the invoice only

Rental

VideoStore

Person

Movie

Invoice

Arrows in Multiway Relationships

Some ER Modeling Tools Require
2-way Relationships

Do we need multi-way relationships or
do 2-way (binary) relationships suffice?

How would you convert this into binary?

Purchase

Product

Person

Store

date

Converting Multiway
Relationships to Binary

Purchase

Person

Store

Product

StoreOf

ProductOf

BuyerOf

date

Q: Why the arrows?

Relationships: Summary

• Modeled as a mathematical set
• Binary and multi-way relationships
• Converting a multi-way one into many binary ones
• Constraints on the degree of the relationship

• many-one, one-one, many-many
• limitations of arrows

• Attributes of relationships
• not necessary, but useful

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses in ER Diagrams

Subclasses in UML

Subclasses
• “Isa” triangles indicate the subclass relationship.

• Point to the superclass.

• Subclasses form a tree.
• I.e., no “multiple inheritance”.

• Why subclasses?
• Unnecessary to add redundant properties to the root entity set that don’t apply to

many of the entities

ER Vs. Object Oriented Subclasses

• In the object-oriented world, objects are in one class only.
• Subclasses inherit properties from superclasses.

• In contrast, E/R entities have components in all subclasses to which
they belong.
• Matters when we convert to relations.

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses in ER Diagrams

A, B, C, D, E

B, C, D A, C

Another Example

Movies

year length

title

isa isa

Murder-MysteriesAnimation Movies

weapon

Voices

Stars
 The Minions?

 Mad Max: Fury Road?

 Murder on the Orient Express?

Agenda

basics of ER and UML diagrams
• constraints
• weak entity sets

Constraints in ER diagram

•A constraint = an assertion about the database that
must be true at all times

•Part of the database schema = structure
(so it must be part of the ER diagram)

•Very important in database design

Modeling Constraints
Finding constraints is part of the modeling process.
Commonly used constraints:

 Keys: attributes that identify entities in an entity set
e.g., social security number uniquely identifies a person.

 Referential integrity constraints: relationship-based constraints
e.g., if you work for a company, it must exist in the database.

 Domain constraints: peoples’ ages are between 0 and 150.
 General constraints: all others (at most 50 students enroll in a class)

Keys in E/R Diagrams

name email netid

Student

Product

name category

price

No formal way
to specify multiple

keys in E/R diagrams

Underline:

This means “name”
and “category”

together uniquely
determine product

Referential Integrity Constraints

CompanyProduct makes

Recall: the arrow meant “at most one”.

Each Product must be related to (“made by”) at most one Company
in the database.

This says “exactly one”.

Each Product must be related to (“made by”) exactly one Company
in the database.

Wouldn’t it be weird if a product was not associated with any
company?

Arrow = at most 1

Semicircle = exactly 1

)

Referential Integrity Constraints

StudiosMovies Owns Runs Presidents

What do these two semi-circles mean?

Each movie is owned by precisely one studio, and
Each president runs exactly one studio

Each studio has up to one president

) (

Referential Integrity Constraints
UML: Aggregation

CompanyProduct makes

ER
Notation

UML
Notation

Referential Integrity Constraints
UML: Composition

CompanyProduct makes )

ER
Notation

UML
Notation

Agenda

basics of ER and UML Models
constraints
• weak entity sets

Weak Entity Sets

Entity sets are weak when some of their key attributes come
from other classes to which they are related.

We’ll see why this is important in a bit…

UniversityTeam affiliation

numbersport name

Sports and number don’t uniquely determine the team

Football team 1 may be present multiple times

But sport, univ and number do

)

Weak Entity Sets

Entity sets are weak when some of their key attributes come
from other classes to which they are related.

UniversityCourse OfferedAt

numberTitle name

CS411, along with UIUC determine the rest of the attributes

(“Database Systems”). Lots of CS411s otherwise!

)

Weak Entity Sets

• Occasionally, entities of an entity set need “help” to identify them
uniquely.

• Entity set E is 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 entity sets.

• Note: not an is-a relationship because E is not a “subclass” of F:
Univ and Team

Notations for weak entity set

UniversityTeam affiliation

numbersport name

UniversityCourse OfferedAt

numberTitle name

• “University” is a “supporting entity set” for “Team”.

• “Affiliation” is a “supporting relationship”.

)

Weak entity set in UML

Another scenario where weak e.s. arises

Purchase

Product

Person

Store

date

A Multi-way relationship …

Another scenario where weak e.s. arises

Person

Store

Product

StoreOf

ProductOf

BuyerOf

date

Purchase

… converted to binary relationships

Remember this is what we had …

… This is not quite accurate

Not sufficient to
identify purchase via
just dates

Another scenario where weak e.s. arises

Person

Store

Product

StoreOf

ProductOf

BuyerOf

date

Purchase

address name ssn

name
categoryprice

nameaddress

3 Supporting Entity Sets &
3 Supporting Relationships

)

Agenda

basics of ER and UML diagrams
constraints
weak entity sets

ER Review

• Basics of ER and UML
• entity, attribute, entity set
• relation: binary, multiway, converting from multiway
• relationship roles, attributes on relationships
• subclasses (is-a)

• Constraints
• on relations

• many-one, one-one, many-many
• limitations of arrows

• keys, single-valued, ref integrity, domain & general constraints

Related Posts