Transcript ppt - Distributed Information Systems Laboratory LSIR

Lecture 04
Entity/Relationship Modelling
1
Outline
• E/R model (Chapter 5)
• From E/R diagrams to relational schemas
(Chapter 5)
• Constraints in SQL (Chapter 4)
2
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
3
Some Terminology
Real world perception
Abstraction / Description
E/R Diagram
Classes of
similar objects
(and their
relationships)
students
Etudiant
(relational) Schema
Database
Type (of table)
Class
Instances
Student
-name
-firstname
-birthdate
-section
-year
4
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
5
Database Design Formalisms
1. 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) to
relational schemas
6
2. Entity / Relationship Diagrams
Objects
Classes
entities
entity sets
Attributes are like in Java.
Product
address
Relationships: like object references in Java except
buys
- first class citizens (not associated with classes)
- bidirectional
7
name
category
name
price
makes
Company
Product
stockprice
buys
employs
Person
address
name
ssn
8
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
name
category
price
Product
9
What is a Relation ?
• A mathematical definition:
– if A, B are sets, then a relation R is a subset of
AxB
1
a
• A={1,2,3}, B={a,b,c,d},
R = {(1,a), (1,c), (3,b)}
A=
b
2
c
3
B=
d
- makes is a subset of Product x Company:
makes
Product
Company
10
Multiplicity of E/R Relations
• one-one:
1
2
3
• many-one
1
2
3
• many-many
1
2
3
a
b
c
d
a
b
c
d
a
b
c
d
11
name
category
name
price
makes
Company
Product
stockprice
buys
What does
this say ?
employs
Person
address
name
ssn
12
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 ?)
13
Arrows in Multiway
Relationships
Q: what does the arrow mean ?
Invoice
VideoStore
Rental
Movie
Person
A: if I know the store, person, invoice, I know the
movie too
14
Arrows in Multiway
Relationships
Q: what do these arrows mean ?
Invoice
VideoStore
Rental
Movie
Person
A: store, person, invoice determines movie and
store, invoice, movie determines person
15
Arrows in Multiway
Relationships
Q: how do I say: “invoice determines store” ?
A: no good way; best approximation:
Invoice
VideoStore
Rental
Movie
Person
16
Roles in Relationships
What if we need an entity set twice in one relationship?
Product
Purchase
Store
buyer
salesperson
Person
17
Attributes on Relationships
date
Product
Purchase
Person
Store
18
Converting Multi-way
Relationships to Binary
date
ProductOf
Product
StoreOf
Store
BuyerOf
Person
Purchase
19
4. From E/R Diagrams
to Relational Schema
• Entity set  relation
• Relationship  relation
20
Entity Set to Relation
name
category
price
Product
Product(name, category, price)
name
category
price
gizmo
gadgets
$19.99
21
Relationships to Relations
price
name
category
Start Year
makes
name
Company
Product
Stock price
Makes(product-name, product-category, company-name, year)
Product-name Product-Category Company-name Starting-year
gizmo
gadgets
gizmoWorks
(watch out for attribute name conflicts)
1963
22
Relationships to Relations
price
name
category
Start Year
makes
name
Company
Product
Stock price
No need for Makes. Modify Product:
name
category price StartYear companyName
gizmo gadgets 19.99
1963
gizmoWorks
23
Multi-way Relationships to
Relations
name
Product
name
price
Purchase
address
Store
Purchase(prodName,stName,ssn)
Person
ssn
name
24
4. Design Principles
What’s wrong?
Product
Country
Moral: be faithful!
Purchase
President
Person
Person
25
Design Principles:
What’s Wrong?
date
Product
Purchase
Store
Moral: pick the right
kind of entities.
personAddr
personName
26
Design Principles:
What’s Wrong?
date
Dates
Product
Purchase
Store
Moral: don’t
complicate life more
than it already is.
Person
27
Design Principles
price
Product
date
Purchase
Store
Person
28
Information Loss
price
Product
Moral: avoid "navigation traps"
Sells
Buys
Person
date
Store
BuysAt
29
Modeling Subclasses
Some objects in a class may be special
• define a new class
• better: define a subclass
Products
Software
products
So --- we define subclasses in E/R
Educational
products
30
Subclasses
name
category
price
Product
isa
Software Product
platforms
isa
Educational Product
31
Age Group
Understanding Subclasses
• Think in terms of records:
– Product
field1
field2
– SoftwareProduct
– EducationalProduct
field1
field2
field3
field1
field2
field4
field5
32
Product
Subclasses to Relations
name
category
price
Software Product
platforms
Price
Category
Gizmo
99
gadget
Camera
49
photo
Toy
39
gadget
Sw.Product
Product
isa
Name
isa
Name
platforms
Gizmo
unix
Ed.Product
Educational Product
Age Group
Name
Age Group
Gizmo
todler
Toy
retired
33
Modeling Subclass Structure
Product
Platforms
ageGroup
topic
required memory
isa
isa
Educational
Product
Software
Product
isa
isa
Educ-software
Product
Educational-method
34
Modeling UnionTypes With
Subclasses
FurniturePiece
Person
Company
Say: each piece of furniture is owned either
by a person, or by a company
35
Modeling Union Types with
Subclasses
Say: each piece of furniture is owned either by a
person, or by a company
Solution 1. Acceptable, imperfect (What’s wrong ?)
Person
FurniturePiece
ownedByPerson
Company
ownedByPerson
36
Modeling Union Types with
Subclasses
Solution 2: better, more laborious
Owner
isa
isa
ownedBy
Company
Person
FurniturePiece
37
5. 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.
38
Keys in E/R Diagrams
name
Underline:
category
price
No formal way
to specify multiple
keys in E/R diagrams
Product
Person
address
name
ssn
39
Single Value Constraints
makes
v. s.
makes
40
Referential Integrity Constraints
Product
makes
Company
Each product made by at most one company.
Some products made by no company
Product
makes
Each product made by exactly one company.
Company
41
Other Constraints
<100
Product
makes
Company
What does this mean ?
42
Weak Entity Sets
Entity sets are weak when their key comes from other
classes to which they are related.
affiliation
Team
sport
number
Notice: we encountered this when converting
multiway relationships to binary relationships
University
name
43
Handling Weak Entity Sets
affiliation
Team
sport
number
University
name
Convert to a relational schema (in class)
Team(number,sport,university-name)
44
6. Constraints in SQL
• A constraint = a property that we’d like our
database to hold
• The system will enforce the constraint by
taking some actions:
– forbid an update
– or perform compensating updates
45
Constraints in SQL
Constraints in SQL:
• Keys, foreign keys
• Attribute-level constraints
• Tuple-level constraints
• Global constraints: assertions
simplest
Most
complex
The more complex the constraint, the harder it is to
check and to enforce
46
Keys
CREATE TABLE Product (
name CHAR(30) PRIMARY KEY,
category VARCHAR(20))
OR:
CREATE TABLE Product (
name CHAR(30),
category VARCHAR(20)
PRIMARY KEY (name))
47
Keys with Multiple Attributes
CREATE TABLE Product (
name CHAR(30),
category VARCHAR(20),
price INT,
PRIMARY KEY (name, category))
Name
Category
Price
Gizmo
Gadget
10
Camera
Photo
20
Gizmo
Photo
30
Gizmo
Gadget
40
48
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
49
Foreign Key Constraints
Referential
integrity
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
50
Product
Purchase
Name
Category
ProdName
Store
Gizmo
gadget
Gizmo
Wiz
Camera
Photo
Camera
Ritz
OneClick
Photo
Camera
Wiz
51
Foreign Key Constraints
OR
CREATE TABLE Purchase (
prodName CHAR(30),
category VARCHAR(20),
date DATETIME,
FOREIGN KEY (prodName, category)
REFERENCES Product(name, category)
(name, category) must be a
PRIMARY KEY
52
What happens during updates ?
Types of updates:
• In Purchase: insert/update
• In Product: delete/update
Product
Purchase
Name
Category
ProdName
Store
Gizmo
gadget
Gizmo
Wiz
Camera
Photo
Camera
Ritz
OneClick
Photo
Camera
Wiz
53
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
54
Constraints on Attributes and
Tuples
• Constraints on attributes:
NOT NULL
-- obvious meaning...
CHECK condition -- any condition !
• Constraints on tuples
CHECK condition
55
What
is the difference from
Foreign-Key ?
CREATE TABLE Purchase (
prodName CHAR(30)
CHECK (prodName IN
SELECT Product.name
FROM Product),
date DATETIME NOT NULL)
56
General Assertions
CREATE ASSERTION myAssert CHECK
NOT EXISTS(
SELECT Product.name
FROM Product, Purchase
WHERE Product.name = Purchase.prodName
GROUP BY Product.name
HAVING count(*) > 200)
57
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
58