程序代写代做代考 database ER Microsoft PowerPoint - 10- DatabaseDesign_ER_FDs

Microsoft PowerPoint – 10- DatabaseDesign_ER_FDs

Database Design:
ER and UML Diagrams

Abdu Alawini
University of Illinois at Urbana-Champaign

CS411: Database Systems

October 3, 2018

Announcements

•HW 2 will be posted today
•Project Track 1 – Stage 1 is due today

•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 cover
this today

And
introduce this

Agenda

• Finish with weak entity sets
• Design principles
• Translating basic ER diagrams to a relational

schema

• Functional dependences

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

Course University

Num PK
Title

Name PKPK

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

 Finish with weak entity sets
• Design principles
• Translating basic ER diagrams to a relational

schema

• Functional dependences

Design Principles

•Be Faithful to Reality
•Avoid Redundancy
•Pick the Right Kind of Element

Also:
•Simplicity
•Choose the Right Relationships
(See Textbook for examples…)

Design Principles:
Principle 1: Be Faithful To Reality

PurchaseProduct Person

President PersonCountry

Teaches CourseInstructor

No: A Person may purchase multiple Products

No: A Country can have at most one President

Yes if multiple instructors, No if not.

Avoiding Redundancy

• Redundancy occurs when we say the same thing in two different
ways.

• Redundancy wastes space (as we will see) and (more importantly)
encourages inconsistency.
• The two instances of the same fact may become inconsistent if we

change one & forget to change the other.

Example: Good

Drinks ManfsManfBy

name

This design gives the address of each
manufacturer exactly once.

name addr

Example: Bad

Drinks ManfsManfBy

name

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

Update issues, Wasteful, …

name

manf

addr

Example: Bad

Drinks

name

This design repeats the manufacturer’s address
once for each beer (wasteful, update anomalies);

Also loses the address if there are temporarily no
drinks for a manufacturer.

manf manfAddr

Principle 2: Entity Sets Versus Attributes

• An entity set should satisfy at least one of the following
conditions:

• It is more than the name of something;
• i.e., it has at least one non-key attribute.

• It is the “many” in a many-one or many-many relationship.

Examples will illustrate why, but also think why each of these
rules actually make sense.

Example: Good

Drinks ManfsManfBy

name

 Manfs deserves to be an entity set because of the nonkey
attribute addr.

 Drinks deserves to be an entity set because it is the “many”
of the many-one relationship ManfBy. Can you see why?

name addr

 An E.S. is more than the name of something;
 i.e., it has at least one non-key attribute.

 An E.S is the “many” in a many-one or many-many relationship.

Example: Good

Drinks

name

If we had no manufacturer address information…

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

manf

 An E.S. is more than the name of something;
 i.e., it has at least one non-key attribute.

 An E.S is the “many” in a many-one or many-many relationship.

Example: Bad

Drinks ManfsManfBy

name

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.

name

 An E.S. is more than the name of something;
 i.e., it has at least one non-key attribute.

 An E.S is the “many” in a many-one or many-many relationship.

Construct an E-R diagram for a car-insurance company
whose customers own one or more cars each. Each car
has associated with it zero to any number of recorded
accidents.

ERD Exercise

ERD Exercise Student Solution 1

ERD Exercise Student Solution 2

ERD Exercise Student Solution 3

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

Agenda

 Finish with weak entity sets
 Design principles
• Translating basic ER diagrams to a relational

schema

• Functional dependences

Converting ER to Relational Schema

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

0..* Assignment 0..*

0..* Manager 1..1

1. Translate each entity set into a table,
with keys.

• Entity set:
•can be represented as

a table in the
relational model

•has a key … which
becomes a key for the
table

CREATE TABLE Employee
(SSN CHAR(11) NOT NULL,
E-Name CHAR(20),
Office INTEGER,
PRIMARY KEY (SSN))

Employee

SSN
E-Name

Office

Multi-valued Attribute

Didn’t see this case when discussing ER diagrams
One or more values of same attribute for an entity

Employee

SSN Office
E-Name

Office Office

2. Create a table for the multi-valued
attribute.

How many offices can one employee have?

Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)

vs.

Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name)
Office-Assignment(SSN, Office)

Most relational DBMSs do not allow multi-valued
attributes.

Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)

12 Smith O-105
15 Wei O-110

Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name)

12 Smith
15 Wei

Office-Assignment(SSN, Office)
12 O-105
12 O-106
15 O-110

Sample Data

3. Translate each many-to-many
relationship set into a table

3535

What are the attributes and what is the key for Assignment?

Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

0..* Assignment 0..*

0..* Manager 1..1

Answer: Assignment(P-Number, SSN)

P-Number is a foreign key for Project
SSN is a foreign key for Employee

Project(P-Number, P-Due-Date)

Employee(SSN, E-Name, Office)

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

0..* Assignment 0..*

0..* Manager 1..1

3. Translate each many-to-many
relationship set into a table

What should we do with each one-to-many
relationship set?

Manager (?)

Project(P-number, P-name, Due-Date)
Employee(SSN, E-Name, Office)

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

0..* Assignment 0..*

0..* Manager 1..1

4. Create a foreign key for a 1-to-many
relationship set.

Project(P-number, P-name, Due-Date, MgrSSN)
Employee(SSN, E-Name, Office)

MgrSSN is a foreign key (referencing the Employee relation)

value of Manager must match an SSN

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

0..* Assignment 0..*

0..* Manager 1..1

4. Create a foreign key for a 1-to-many
relationship set.

Project(P-number, P-name, Due-Date, MgrSSN)
Employee(SSN, E-Name, Office)

vs.
Project(P-number, P-name, Due-Date)

Employee(SSN, E-Name, Office)
Manager(P-number, SSN)

What are the tradeoffs between these two?

Note:
P-number
is the key

for Manager

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

0..* Assignment 0..*

0..* Manager 1..1

What do we do when a many-to-many
relationship set has an attribute?

Assignment(P-number, SSN)
Project(P-number, P-name, Due-Date)

Employee(SSN, E-Name, Office)

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

role
start-date
end-date

0..* Assignment 0..*

Assignment(P-number, SSN, role, start-date, end-date)
Project(P-number, P-name, Due-Date)

Employee(SSN, E-Name, Office)

Project
P-number
P-name

Due-Date

Employee
SSN

E-Name
Office

role
start-date
end-date

What do we do when a many-to-many
relationship set has an attribute?

0..* Assignment 0..*

What do we do when a 1-to-many
relationship set has an attribute?

Project(P-number, P-name, Due-Date, MgrSSN)
Employee(SSN, E-Name, Office)

Employee
SSN

E-Name
Office

start-date
end-date

Project
P-number
P-name

Due-Date

0..* Manager 1..1

Project(P-number, P-name, Due-Date, MgrSSN,
start-date, end-date)

Employee(SSN, E-Name, Office )

Employee
SSN

E-Name
Office

start-date
end-date

Project
P-number
P-name

Due-Date

What do we do when a 1-to-many
relationship set has an attribute?

0..* Manager 1..1

Weak Entity Sets

Policy
dep-name

cost

Insures

supporting
entity set

supporting
relationship

set

weak
Entity set

Employee
SSN

name
office

• Weak entity sets and supporting relationship sets are
translated into a single table. Must include key of
supporting entity set, as a foreign key.

• When the owner entity is deleted, all owned weak
entities must also be deleted.

Translating Weak Entity Sets

CREATE TABLE Insurance_Policy (
dep-name CHAR(20),
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (dep-name, ssn),

FOREIGN KEY (ssn) REFERENCES Employee,
ON DELETE CASCADE)

Agenda

 Finish with weak entity sets
 Design principles
 Translating basic ER diagrams to a relational

schema

• Functional dependences

Related Posts