Database Design Database Design Process

Preparing to load PDF file. please wait...

0 of 0
100%
Database Design Database Design Process

Transcript Of Database Design Database Design Process

Introduction to Data Management CSE 344
Lecture 14: E/R Diagrams

Magda Balazinska - CSE 344, Fall 2012

1

Today: E/R Diagrams

Motivating scenario

•  Customer asks you to help them setup a DBMS

•  They want to store information about

–  Companies and various branches inside companies
•  Each company has a name, an address, and a CEO

•  Each company also has a list of key employees

•  Each branch has a name and a market share in $$$

–  Products manufactured by these companies
•  Each product has a name and a description

•  Products are manufactured by different branches

Magda Balazinska - CSE 344, Fall 2012

2

Database Design
•  Why do we need it?
–  Need a way to model real world entities in terms of relations –  Not easy to go from real-world entities to a database schema
•  Consider issues such as:
–  What entities to model –  How entities are related –  What constraints exist in the domain –  How to achieve good designs
•  Several formalisms exists
–  We discuss E/R diagrams

Magda Balazinska - CSE 344, Fall 2012

3

Database Design Process

Conceptual Model:

name Patient

patien_of

Doctor

zip

name

dno

Relational Model: Tables + constraints And also functional dep.
Normalization: Eliminates anomalies
Conceptual Schema
Physical storage details Physical Schema

Entity / Relationship Diagrams

This is an entity set

Objects Classes

entities entity sets

Product

Attributes are like in ODL

address

(ODL = Object Definition Language)

Relationships: like in ODL except

buys

- first class citizens (not associated with classes) - not necessarily binary

Magda Balazinska - CSE 344, Fall 2012

5

name category

price Product

makes

buys address

Person name

name Company
stockprice employs

ssn

6

1

Keys in E/R Diagrams
•  Every entity set must have a key
name category price
Product

Magda Balazinska - CSE 344, Fall 2012

7

What is a Relation ?

•  A mathematical definition:

–  if A, B are sets, then a relation R is a subset of A × B

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

1

a

R = {(1,a), (1,c), (3,b)}

A= 2 b

3

c

•  makes is a subset of Product × CompanyB:= d

Product

makes

Company

Magda Balazinska - CSE 344, Fall 2012

8

Multiplicity of E/R Relations

•  one-one:

1

a

2

b

3

c

•  many-one d

1

a

2

b

3

c

d

•  many-many

1

a

2

b

3

c

d

Magda Balazinska - CSE 344, Fall 2012

9

name category

price Product

makes

buys

What does this say ?

Person

name Company
stockprice employs

address

name

ssn

10

Multi-way Relationships
How do we model a purchase relationship between buyers, products and stores?
Product

Purchase

Store

Person

Can still model as a mathematical set (how ?)

11

Arrows in Multiway Relationships

Q: What does the arrow mean ?

Product

Purchase

Store

Person

A: A given person buys a given product from at most one store

Magda Balazinska - CSE 344, Fall 2012

12

2

Arrows in Multiway Relationships

Q: What does the arrow mean ? Product

Purchase

Store

Person

A: A given person buys a given product from at most one store AND every store sells to every person at most one product

Magda Balazinska - CSE 344, Fall 2012

13

Arrows in Multiway Relationships

Q: How do we say that every person shops at at most one store ?

Product Purchase

Store

Person

A: Cannot. This is the best approximation. (Why only approximation ?)

Magda Balazinska - CSE 344, Fall 2012

14

Converting Multi-way Relationships to Binary

date ProductOf

Product

Purchase

StoreOf

Store

BuyerOf Arrows go in which direction?

Person
15

Converting Multi-way Relationships to Binary

date ProductOf

Product

Purchase

StoreOf

Store

BuyerOf Make sure you understand why!

Person
16

3. Design Principles
What’s wrong?

Product

Purchase

Person

Country

President

Person

Moral: be faithful to the specifications of the app!

Magda Balazinska - CSE 344, Fall 2012

17

Product

Design Principles: What’s Wrong?
date

Purchase

Store

personAddr

Moral: pick the right kind of entities.
personName

Magda Balazinska - CSE 344, Fall 2012

18

3

Design Principles: What’s Wrong?

Product

Dates

date

Purchase

Moral: don’t complicate life more than it already is.

Person

Store
19

From E/R Diagrams to Relational Schema
•  Entity set à relation •  Relationship à relation

Magda Balazinska - CSE 344, Fall 2012

20

Entity Set to Relation

prod-ID category price

Product

Product(prod-ID, category, price)

prod-ID

category

price

Gizmo55

Camera

99.99

Pokemn19 Toy

29.99

21

Create Table (SQL)
CREATE TABLE Product ( prod-ID CHAR(30) PRIMARY KEY, category VARCHAR(20), price double)

Magda Balazinska - CSE 344, Fall 2012

22

N-N Relationships to Relations

prod-ID cust-ID date
date

name

Orders

Shipment

Shipping-Co

address

Represent that in relations!

Magda Balazinska - CSE 344, Fall 2012

23

N-N Relationships to Relations

prod-ID cust-ID date
date

name

Orders

Shipment

Shipping-Co

Orders(prod-ID,cust-ID, date) Shipment(prod-ID,cust-ID, name, date) Shipping-Co(name, address) prod-ID cust-ID
Gizmo55 Joe12
Gizmo55 Joe12

address

name UPS FEDEX

date
4/10/2011 24 4/9/2011

4

Create Table (SQL)
CREATE TABLE Shipment( name CHAR(30) REFERENCES Shipping-Co, prod-ID CHAR(30), cust-ID VARCHAR(20), date DATETIME,
PRIMARY KEY (name, prod-ID, cust-ID), FOREIGN KEY (prod-ID, cust-ID)
REFERENCES Orders )

N-1 Relationships to Relations

prod-ID cust-ID date
date

name

Orders

Shipment

Shipping-Co

address

Represent this in relations!

Magda Balazinska - CSE 344, Fall 2012

26

N-1 Relationships to Relations

prod-ID cust-ID date
date

name

Orders

Shipment

Shipping-Co

Orders(prod-ID,cust-ID, date1, name, date2) Shipping-Co(name, address)

address

Remember: no separate relations for many-one relationship

Multi-way Relationships to

Product

Relations
name

address

prod-ID price Purchase

Store

Person

Represent this in relations!

ssn

name

Magda Balazinska - CSE 344, Fall 2012

28

Modeling Subclasses

Some objects in a class may be special •  define a new class •  better: define a subclass
Products

Software products

Educational products

So --- we define subclasses in E/R

Magda Balazinska - CSE 344, Fall 2012

29

Subclasses
name

category

price

Product

isa

isa

Software Product

Educational Product

platforms

Magda Balazinska - CSE 344, Fall 2012

Age Group

5

Understanding Subclasses

•  Think in terms of records:
–  Product

field1 field2

–  SoftwareProduct –  EducationalProduct

field1
field2
field3

field1

field2
field4 field5

Magda Balazinska - CSE 344, Fall 2012

31

Subclasses to Relations
name category
price
Product

isa

isa

Product
Name Gizmo Camera
Toy

Price 99 49 39

Category gadget photo gadget

Sw.Product

Name Gizmo

platforms unix

Software Product platforms

Educational Product Age Group

Ed.Product

Name

Age Group

Other ways to convert are possible
Magda Balazinska - CSE 344, Fall 2012

Gizmo Toy

toddler retired
32

Modeling UnionTypes With Subclasses

FurniturePiece

Person

Company

Say: each piece of furniture is owned either by a person or by a company

Magda Balazinska - CSE 344, Fall 2012

33

Modeling Union Types with Subclasses

Say: each piece of furniture is owned either by a person or by a company
Solution 1. Acceptable but imperfect (What’s wrong ?)

Person

FurniturePiece

Company

ownedByPerson

ownedByComp.

Magda Balazinska - CSE 344, Fall 2012

34

Modeling Union Types with Subclasses
Solution 2: better, more laborious
Owner

isa Person

ownedBy

isa Company

FurniturePiece

Magda Balazinska - CSE 344, Fall 2012

35

Weak Entity Sets
Entity sets are weak when their key comes from other classes to which they are related.

Team

affiliation

sport

number

University name

Team(sport, number, universityName) University(name)

Magda Balazinska - CSE 344, Fall 2012

36

6

What Are the Keys of R ?
A

B

R

H

S

T

C

W

U

V

V FL K

E

Q

D

G

Z

Constraints in E/R Diagrams
Finding constraints is part of the modeling process. Commonly used constraints:

Keys: social security number uniquely identifies a person.

Single-value constraints: a person can have only one father.

Referential integrity constraints: if you work for a company, it must exist in the database.

Other constraints: peoples’ ages are between 0 and 150.

Magda Balazinska - CSE 344, Fall 2012

38

Keys in E/R Diagrams

Underline:

name price

category

No formal way to specify multiple keys in E/R diagrams

Product Person

address

name

ssn

Single Value Constraints

makes v. s. makes

Magda Balazinska - CSE 344, Fall 2012

40

Referential Integrity Constraints

Product

makes

Company

Each product made by at most one company. Some products made by no company

Product

makes

Company

Each product made by exactly one company.

Magda Balazinska - CSE 344, Fall 2012

41

Other Constraints

Product

<100 makes

Company

Q: What does this mean ? A: A Company entity cannot be connected by relationship to more than 99 Product entities

Magda Balazinska - CSE 344, Fall 2012

42

7
Magda BalazinskaPersonCompanyProductConstraints