1 relational model and translating er into relational
TRANSCRIPT
![Page 1: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/1.jpg)
1
Relational Model and Translating ER into Relational
![Page 2: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/2.jpg)
2
Lecture Outline
• Relational model
• Translating from ER to relational model
![Page 3: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/3.jpg)
3
Motivations & comparison of ER with relational model ...
![Page 4: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/4.jpg)
4
Database Modeling & Implementation
Database Model(E/R, ODL)
Database Model(E/R, ODL)
IdeasIdeas
Physicalstorage
Physicalstorage
Diagrams (E/R) Tables: column names: attributes rows: tuples
Complexfile organizationand index structures.
Relational Schema
Relational Schema
![Page 5: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/5.jpg)
5
ER Model vs. Relational Model
• Both are used to model data
• ER model has many concepts– entities, relations, attributes, etc.– well-suited for capturing the app. requirements– not well-suited for computer implementation– (does not even have operations on its structures)
• Relational model– has just a single concept: relation– world is represented with a collection of tables– well-suited for efficient manipulations on computers
![Page 6: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/6.jpg)
6
The basics of the relational model ...
![Page 7: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/7.jpg)
7
An Example of a Relation
Name Price Category Manufacturer
gizmo $19.99 gadgets GizmoWorks
Power gizmo $29.99 gadgets GizmoWorks
SingleTouch $149.99 photography Canon
MultiTouch $203.99 household Hitachi
tuples
Attribute namesTable name
Products:
![Page 8: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/8.jpg)
8
Domains• Each attribute has a type• Must be atomic type (why? see later)• Called domain • Examples:
– Integer– String– Real– …
![Page 9: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/9.jpg)
9
Schemas vs. instances (very important, make sure you know
the difference)
![Page 10: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/10.jpg)
10
SchemasThe Schema of a Relation:
– Relation name plus attribute names– E.g. Product(Name, Price, Category, Manufacturer)– In practice we add the domain for each attribute
The Schema of a Database– A set of relation schemas– E.g. Product(Name, Price, Category, Manufacturer),
Vendor(Name, Address, Phone), . . . . . . .
![Page 11: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/11.jpg)
11
Instances• Relational schema = R(A1,…,Ak):
Instance = relation with k attributes (of “type” R)– values of corresponding domains
• Database schema = R1(…), R2(…), …, Rn(…)Instance = n relations, of types R1, R2, ..., Rn
![Page 12: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/12.jpg)
12
Example
Name Price Category Manufacturer
gizmo $19.99 gadgets GizmoWorks
Power gizmo $29.99 gadgets GizmoWorks
SingleTouch $149.99 photography Canon
MultiTouch $203.99 household Hitachi
Relational schema:Product(Name, Price, Category, Manufacturer)Instance:
![Page 13: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/13.jpg)
13
Annother Example
SSN Name Category 123-45-6789 Charles undergrad 234-56-7890 Dan grad … …
SSN CID 123-45-6789 CSE444 123-45-6789 CSE444 234-56-7890 CSE142 …
Students: Takes:
CID Name Quarter CSE444 Databases fall CSE541 Operating systems winter
Courses:
Three relational schemas here + three relational instances
One database schema + one database instance
![Page 14: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/14.jpg)
14
Updates
The database maintains a current database state (that is, a database instance).
Updates to the data (that is, the database instance)
1) add a tuple 2) delete a tuple 3) modify an attribute in a tuple
Updates to the data happen very frequently.
Updates to the schema: relatively rare. Rather painful. Why?
![Page 15: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/15.jpg)
15
Schemas and Instances
• Analogy with programming languages:– Schema = type– Instance = value
• Important distinction:– Database Schema = stable over long periods of time– Database Instance = changes constantly, as data is
inserted/updated/deleted
![Page 16: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/16.jpg)
16
How should we talk about relations (that is, represent them)?
Will skip this in the classYou can read for fun
![Page 17: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/17.jpg)
17
Two Mathematical Definitions of Relations
Relation as Cartesian product• Tuple = element of string x int x string x string• E.g. t = (gizmo, 19, gadgets, GizmoWorks)• Relation = subset of string x int x string x
string• Order in the tuple is important !
– (gizmo, 19, gadgets, GizmoWorks)
– (gizmo, 19 , GizmoWorks, gadgets)
• No attributes
![Page 18: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/18.jpg)
18
Relation as a set of functions• Fix the set of attributes
– A={name , price, category, manufacturer}
• A tuple = function t:A Domains• Relation = set of tuples• E.g.
• Order in a tuple is not important• Attribute names are important
{name gizmo, price 19, category gadgets, manufacturer gizmoWorks}
{name gizmo, price 19, category gadgets, manufacturer gizmoWorks}
![Page 19: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/19.jpg)
19
Two Definitions of Relations
• We will switch back and forth between these two:– Positional tuples, without attribute names– Relational schemas with attribute names
![Page 20: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/20.jpg)
20
Now the fun part: translating from ER to relational model
![Page 21: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/21.jpg)
21
Translating ER Diagram to Rel. Design
• Basic cases– entity set E = relation with attributes of E– relationship R = relation with attributes being keys of
related entity sets + attributes of R
• Special cases– combining two relations – translating weak entity sets– translating is-a relationships and subclasses
![Page 22: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/22.jpg)
22
address name ssn
Person
buys
makes
employs
CompanyProduct
name category
Stock price
name
price
An Example
![Page 23: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/23.jpg)
23
Basic cases ...
![Page 24: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/24.jpg)
24
Entity Sets to Relations
Product
name category
price
Product:
Name Category Price
gizmo gadgets $19.99
![Page 25: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/25.jpg)
25
Relationships to Relations
makes CompanyProduct
name category
Stock price
name
Relation Makes (watch out for attribute name conflicts) Product-name Product-Category Company-name Starting-year
gizmo gadgets gizmoWorks 1963
Start Year
price
![Page 26: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/26.jpg)
26
Relationship to Relation: Another Example
Drinkers BeersLikes
Likes(drinker, beer)Favorite
Favorite(drinker, beer)
Married
husband
wife
Married(husband, wife)
name addr name manf
Buddies
1 2
Buddies(name1, name2)
![Page 27: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/27.jpg)
27
Special cases:1) many-one relations2) weak entity sets3) is-a cases
![Page 28: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/28.jpg)
28
Combining Two Relations
makes CompanyProduct
name category
Stock price
name
No need for Makes. Just modify Product:
name category price StartYear companyName
gizmo gadgets 19.99 1963 gizmoWorks
Start Year
price
![Page 29: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/29.jpg)
29
Combining Relations
• It is OK to combine the relation for an entity-set E with the relation R for a many-one relationship from E to another entity set.
• Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favoriteBeer).
![Page 30: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/30.jpg)
30
Risk with Many-Many Relationships
• Combining Drinkers with Likes would be a mistake. It leads to redundancy, as:
name addr beerSally 123 Maple BudSally 123 Maple Miller
Redundancy
![Page 31: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/31.jpg)
31
Handling Weak Entity Sets
UniversityTeam affiliation
numbersport name
Relation Team:
Sport Number Affiliated University
mud wrestling 15 Montezuma State U.
- need all the attributes that contribute to the key of Team - don’t need a separate relation for Affiliation. (why ?)
![Page 32: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/32.jpg)
32
Handling Weak Entity Sets
• Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes.
• A supporting (double-diamond) relationship is redundant and yields no relation.
![Page 33: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/33.jpg)
33
Another Example
Logins HostsAt
name name
Hosts(hostName)Logins(loginName, hostName, time)At(loginName, hostName, hostName2)
Must be the same
time
At becomes part ofLogins
![Page 34: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/34.jpg)
34
Translating Subclass Entities
Product
Educational Product
SoftwareProduct
Educ-softwareProduct
ageGrouptopic
Platformsrequired memory
Educational-method
isa
isa
isa
isa
![Page 35: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/35.jpg)
35
Option #1: the OO Approach
4 tables: each object can only belong to a single table Product(name, price, category, manufacturer)
EducationalProduct( name, price, category, manufacturer, ageGroup, topic)
SoftwareProduct( name, price, category, manufacturer, platforms, requiredMemory)
EducationalSoftwareProduct( name, price, category, manufacturer, ageGroup, topic, platforms, requiredMemory)All names are distinct
![Page 36: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/36.jpg)
36
Option #2: the E/R Approach
Product(name, price, category, manufacturer)
EducationalProduct( name, ageGroup, topic)
SoftwareProduct( name, platforms, requiredMemory)
No need for a relation EducationalSoftwareProduct
Unless, it has a specialized attribute: EducationalSoftwareProduct(name, educational-method)
Same name may appear in several relations
![Page 37: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/37.jpg)
37
Option #3: The Null Value Approach
Have one table:
Product ( name, price, manufacturer, age-group, topic, platforms, required-memory, educational-method)
Some values in the table will be NULL, meaning that the attribute not make sense for the specific product.
Too many meanings for NULL
![Page 38: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/38.jpg)
38
Translating Subclass Entities: The RulesThree approaches:
1. Object-oriented : each entity belongs to exactly one class; create a relation for each class, with all its attributes.
2. E/R style : create one relation for each subclass, with only the key attribute(s) and attributes attached to that E.S.; entity represented in all relations to whose subclass/E.S. it belongs.
3. Use nulls : create one relation; entities have null in attributes that don’t belong to them.
![Page 39: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/39.jpg)
39
Example
Beers
Ales
isa
name manf
color
![Page 40: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/40.jpg)
40
Object-Oriented
name manfBud Anheuser-Busch
Beers
name manf colorSummerbrew Pete’s dark
Ales
![Page 41: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/41.jpg)
41
E/R Style
name manfBud Anheuser-BuschSummerbrew Pete’s
Beers
name colorSummerbrew dark
Ales
![Page 42: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/42.jpg)
42
Using Nulls
name manf colorBud Anheuser-Busch NULLSummerbrew Pete’s dark
Beers
![Page 43: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/43.jpg)
43
Comparisons• O-O approach good for queries like “find the color of
ales made by Pete’s.”– Just look in Ales relation.
• E/R approach good for queries like “find all beers (including ales) made by Pete’s.”– Just look in Beers relation.
• Using nulls saves space unless there are lots of attributes that are usually null.
![Page 44: 1 Relational Model and Translating ER into Relational](https://reader031.vdocuments.us/reader031/viewer/2022020718/56649d355503460f94a0c715/html5/thumbnails/44.jpg)
44
Translation Review
• Basic cases– entity to table, relation to table– selecting attributes based on keys
• Special cases– many-one relation can be merged– merging many-many is dangerous– translating weak entity sets– translating isa hierarchy
• 3 choices, with trade-offs