1

1

Entity/RelationshipModelling

Lecture 4

2

Outline

• E/R model (Chapter 5)• From E/R diagrams to relational schemas

(Chapter 5)• Constraints in SQL (Chapter 4)

2

3

1. Database Design

• Modelling– Decide which part of reality is represented in a

database– Agree on structure of the database before

deciding on a particular implementation• Conceptual Modelling

– Oriented towards application and users– Independent of technology and implementation

4

Some Terminology

Classes of similarobjects (and theirrelationships)

students

Instances

Student-name-firstname-birthdate-section-year

Type (of table)

Database

Student

(relational) Schema

Real world perception Abstraction / Description

E/R Diagram

3

5

Conceptual Modelling

• Consider issues such as:– What entities to model– How entities are related– What constraints exist in the domain– How to achieve good designs

6

Database Design Formalisms1. Entity/Relationship model (E/R):

– More relational in nature.– Very widely used

2. Object Definition Language (ODL):– Closer in spirit to object-oriented models (e.g. Java)– Will not be covered

Both can be translated (semi-automatically) torelational schemas

4

7

2. Entity / Relationship Diagrams

Objects entitiesClasses entity sets

Attributes are like in Java.

Relationships: like object references in Java except

- first class citizens (not associated with classes)

- bidirectional

Product

address

buys

8address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

5

9

Keys in E/R Diagrams

• Every entity set must have a key– a key is an attribute that has a different value

for every entity

Product

name category

price

10

What is a Relation ?

• A mathematical 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)}

- makes is a subset of Product x Company:

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

6

11

Multiplicity of E/R Relations

• one-one:

• many-one

• many-many

123

abcd

123

abcd

123

abcd

12address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

What doesthis say ?

7

13

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

Purchase

Product

Person

Store

Can still model as a mathematical set (how ?)

14

Q: what does the arrow mean ?

A: if I know the store, person, invoice, I know themovie too

Rental

VideoStore

Person

Movie

Invoice

Arrows in Multiway Relationships

8

15

Q: what do these arrows mean ?

A: store, person, invoice determines movie andstore, invoice, movie determines person

Rental

VideoStore

Person

Movie

Invoice

Arrows in Multiway Relationships

16

Q: how do I say: “invoice determines store” ?A: no good way; best approximation:

Rental

VideoStore

Person

Movie

Invoice

Arrows in Multiway Relationships

9

17

Roles in Relationships

Purchase

What if we need an entity set twice in one relationship?

Product

Person

Store

salesperson buyer

18

Attributes on Relationships

Purchase

Product

Person

Store

date

10

19

Converting Multi-wayRelationships to Binary

Purchase

Person

Store

Product

StoreOf

ProductOf

BuyerOf

date

20

4. From E/R Diagramsto Relational Schema

• Entity set relation• Relationship relation

11

21

Entity Set to Relation

Product

name category

price

Product(name, category, price)

name category price

gizmo gadgets $19.99

22

Relationships to Relations

makes CompanyProduct

name category

Stock price

name

Makes(product-name, product-category, company-name, year) Product-name Product-Category Company-name Starting-year

gizmo gadgets gizmoWorks 1963

Start Yearprice

(watch out for attribute name conflicts)

12

23

Relationships to Relations

makes CompanyProduct

name category

Stock price

name

No need for Makes. Modify Product:

name category price StartYear companyName

gizmo gadgets 19.99 1963 gizmoWorks

Start Yearprice

24

Multi-way Relationships toRelations

Purchase

Product

Person

Storename price

ssn name

name address

Purchase(prodName,stName,ssn)

13

25

4. Design Principles

PurchaseProduct Person

What’s wrong?

President PersonCountry

Moral: be faithful!

26

Design Principles:What’s Wrong?

Purchase

Product

Store

date

personNamepersonAddr

Moral: pick the right kind of entities.

14

27

Design Principles:What’s Wrong?

Purchase

Product

Person

Store

dateDates

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

28

Design Principles

PurchaseProduct

Person

Store

price date

15

29

Information Loss

SellsProduct Store

price

date

Buys BuysAtPerson

Moral: avoid "navigation traps"

30

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

16

31

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

32

Understanding Subclasses

• Think in terms of records:– Product

– SoftwareProduct

– EducationalProduct

field1field2

field1field2

field1field2

field3

field4field5

17

33

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

gadget39Toy

photo49Camera

gadget99Gizmo

CategoryPriceName

unixGizmo

platformsName

retiredToy

todlerGizmo

Age GroupName

Product

Sw.Product

Ed.Product

34

Modeling Subclass Structure

Product

Educational Product

SoftwareProduct

Educ-softwareProduct

isa

isa

isa

isa

Platforms

required memory

Educational-method

ageGroup

topic

18

35

Modeling UnionTypes WithSubclasses

FurniturePiece

Person Company

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

36

Modeling Union Types withSubclasses

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

Solution 1. Acceptable, imperfect (What’s wrong ?)

FurniturePiecePerson Company

ownedByPerson ownedByPerson

19

37

Modeling Union Types withSubclasses

Solution 2: better, more laborious

isa

FurniturePiece

Person CompanyownedBy

Owner

isa

38

5. Constraints in E/R DiagramsFinding 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.

20

39

Keys in E/R Diagrams

address name ssn

Person

Product

name category

price

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

Underline:

40

Single Value Constraints

makes

makes

v. s.

21

41

Referential Integrity Constraints

CompanyProduct makes

CompanyProduct makes

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

Each product made by exactly one company.

42

Other Constraints

CompanyProduct makes<100

What does this mean ?

22

43

Weak Entity SetsEntity sets are weak when their key comes from otherclasses to which they are related.

UniversityTeam affiliation

numbersport name

Notice: we encountered this when convertingmultiway relationships to binary relationships

44

Handling Weak Entity Sets

UniversityTeam affiliation

numbersport name

Convert to a relational schema (in class)

Team(number,sport,university-name)

23

45

6. Constraints in SQL

• A constraint = a property that we’d like ourdatabase to hold

• The system will enforce the constraint bytaking some actions:– forbid an update– or perform compensating updates

46

Constraints in SQLConstraints in SQL:• Keys, foreign keys• Attribute-level constraints• Tuple-level constraints• Global constraints: assertions

The more complex the constraint, the harder it is tocheck and to enforce

simplest

Mostcomplex

24

47

Keys

OR:

CREATE TABLE Product (name CHAR(30) PRIMARY KEY,category VARCHAR(20))

CREATE TABLE Product (name CHAR(30),category VARCHAR(20)

PRIMARY KEY (name))

48

Keys with Multiple Attributes

CREATE TABLE Product (name CHAR(30),category VARCHAR(20),price INT,

PRIMARY KEY (name, category))

40GadgetGizmo

30

20

10

Price

PhotoGizmo

PhotoCamera

GadgetGizmo

CategoryName

25

49

Other Keys

CREATE TABLE Product ( productID CHAR(10),

name CHAR(30),category VARCHAR(20),price INT,

PRIMARY KEY (productID), UNIQUE (name, category))

There is at most one PRIMARY KEY;there can be many UNIQUE

50

Foreign Key Constraints

CREATE TABLE Purchase (prodName CHAR(30)

REFERENCES Product(name), date DATETIME)

prodName is a foreign key to Product(name)name must be a key in Product

Referentialintegrity

constraints

26

51

PhotoOneClick

PhotoCamera

gadgetGizmo

CategoryName

WizCamera

RitzCamera

WizGizmo

StoreProdName

Product Purchase

52

Foreign Key ConstraintsOR

(name, category) must be aPRIMARY KEY

CREATE TABLE Purchase (prodName CHAR(30),category VARCHAR(20),

date DATETIME, FOREIGN KEY (prodName, category) REFERENCES Product(name, category)

27

53PhotoOneClick

PhotoCamera

gadgetGizmo

CategoryName

WizCamera

RitzCamera

WizGizmo

StoreProdName

Product Purchase

What happens during updates ?

Types of updates:• In Purchase: insert/update• In Product: delete/update

54

What happens during updates ?• SQL has three policies for maintaining

referential integrity:• Reject violating modifications (default)• Cascade: after a delete/update do a

delete/update• Set-null set foreign-key field to NULL

28

55

Constraints on Attributes andTuples

• Constraints on attributes:NOT NULL -- obvious meaning...CHECK condition -- any condition !

• Constraints on tuplesCHECK condition

56

CREATE TABLE Purchase (prodName CHAR(30)

CHECK (prodName IN SELECT Product.name FROM Product), date DATETIME NOT NULL)

Whatis the difference from

Foreign-Key ?

29

57

General Assertions

CREATE ASSERTION myAssert CHECK NOT EXISTS(

SELECT Product.nameFROM Product, PurchaseWHERE Product.name = Purchase.prodNameGROUP BY Product.nameHAVING count(*) > 200)

58

Final Comments on Constraints

• Can give them names, and alter later• We need to understand exactly when they

are checked• We need to understand exactly what

actions are taken if they fail

30

59

Summary

• E/R Models to model the real world• Adhere to certain design recommendations