Entity/Relationship Modelling Lecture 4 1 Outline E/R model - - PDF document
Entity/Relationship Modelling Lecture 4 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
1
2
3
– Decide which part of reality is represented in a database – Agree on structure of the database before deciding on a particular implementation
– Oriented towards application and users – Independent of technology and implementation
4
Classes of similar objects (and their relationships) students
Instances
Student
Type (of table)
Database
Student
(relational) Schema
Real world perception Abstraction / Description
E/R Diagram
5
– What entities to model – How entities are related – What constraints exist in the domain – How to achieve good design
6
– More relational in nature. – Very widely used
– Closer in spirit to object-oriented models (e.g. Java) – Will not be covered
Both can be translated (semi-automatically) to relational schemas
7
Objects entities Classes entity sets Attributes are like in Java. Relationships: like object references in Java except
Product address buys
8
address name ssn Person buys makes employs Company Product name category stockprice name price
9
– a key is an attribute that has a different value for every entity
Product name category price
10
– if A and B are sets, then a relation R is a subset of A x B
R = {(1,a), (1,c), (3,b)}
1 2 3 a b c d A= B=
makes Company Product
11
1 2 3 a b c d 1 2 3 a b c d 1 2 3 a b c d 12
address name ssn Person buys makes employs Company Product name category stockprice name price What does this say ?
13
How do we model a purchase relationship between buyers, products and stores? Purchase Product Person Store
14
Q: what does the arrow mean ? A: if I know the store, person, invoice, I know the movie too
Rental VideoStore Person Movie Invoice
15
Q: what do these arrows mean ? A: store, person, invoice determines movie and store, invoice, movie determines person
Rental VideoStore Person Movie Invoice
16
Rental VideoStore Person Movie Invoice
17
Purchase What if we need an entity set twice in one relationship? Product Person Store salesperson buyer
18
Purchase Product Person Store date
19
Purchase Person Store Product StoreOf ProductOf BuyerOf date
20
21
Product name category price Product(name, category, price) name category price gizmo gadgets $19.99
22
makes Company Product 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 Year price (watch out for attribute name conflicts)
23
makes Company Product name category Stock price name No need for StartYear in Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks Start Year price
24
Purchase Product Person Store name price ssn name name address Purchase(prodName,stName,ssn)
25
Purchase Product Person
President Person Country Moral: be faithful!
26
Purchase Product Store date personName personAddr Moral: pick the right kind of entities.
27
Purchase Product Person Store date Dates Moral: don’t complicate life more than it already is.
28
Purchase Product Person Store price date
29
Sells Product Store price date Buys BuysAt Person Moral: avoid "navigation traps"
30
Some objects in a class may be special
Products Software products Educational products So --- we define subclasses in E/R
31
Product name category price isa isa Educational Product Software Product Age Group platforms
32
– Product – SoftwareProduct – EducationalProduct
field1 field2 field1 field2 field1 field2
field3 field4 field5
33
Product name category price isa isa Educational Product Software Product Age Group platforms
gadget 39 Toy photo 49 Camera gadget 99 Gizmo Category Price Name unix Gizmo platforms Name retired Toy todler Gizmo Age Group Name
Product Sw.Product Ed.Product
34
Product Educational Product Software Product Educ-software Product isa isa isa isa Platforms required memory Educational-method ageGroup topic
35
FurniturePiece Person Company
Say: each piece of furniture is owned either by a person, or by a company
36
Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable, imperfect (Whats wrong ?)
FurniturePiece Person Company
37
isa FurniturePiece Person Company
Owner isa
38
Finding constraints is part of the modelling 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.
39
address name ssn Person Product name category price No formal way to specify multiple keys in E/R diagrams Underline:
40
makes makes
41
Company Product makes Company Product makes Each product made by at most one company. Some products made by no company Each product made by exactly one company.
42
Company Product makes <100 What does this mean?
43
Entity sets are weak if their key comes from other classes to which they are related. Cannot be identified by its own attributes alone. University Team affiliation number sport name
44
University Team affiliation number sport name Convert to a relational schema (in class) Team(number,sport,university-name)
45
– forbid an update – or perform compensating updates
46
Constraints in SQL:
The more complex the constraint, the harder it is to check and to enforce
simplest Most complex
47
CREATE TABLE Product ( name CHAR(30) PRIMARY KEY, category VARCHAR(20)) CREATE TABLE Product ( name CHAR(30), category VARCHAR(20), PRIMARY KEY (name))
48
CREATE TABLE Product ( name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (name, category))
40 Gadget Gizmo 30 20 10 Price Photo Gizmo Photo Camera Gadget Gizmo Category Name
49
There is at most one PRIMARY KEY; there can be many UNIQUE
50
prodName is a foreign key to Product(name) name must be a key in Product Referential integrity constraints
51
Photo OneClick Photo Camera gadget Gizmo Category Name Wiz Camera Ritz Camera Wiz Gizmo Store ProdName
Product Purchase
Purchase Product
52
(name, category) must be a PRIMARY KEY
53
Photo OneClick Photo Camera gadget Gizmo Category Name Wiz Camera Ritz Camera Wiz Gizmo Store ProdName
Product Purchase
54
55
NOT NULL
CHECK condition
CHECK condition
56
What is the difference from Foreign-Key ?
57
58
59