Download Relational Database Model vs. Entity-Relationship Model: A Comparison and more Study notes Deductive Database Systems in PDF only on Docsity!
1
CS
Database Systems
Fall 2004
03: Relational Model
2
Motivations & comparison of
ER with relational model ...
Database Modeling & Implementation
Database Model (E/R, ODL)
Ideas
Physical storage
Diagrams (E/R) Tables: column names: attributes rows: tuples
Complex file organization and index structures.
Relational Schema
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
5
The basics of the relational model ...
6
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 names
Table name
Products:
Domains
• Each attribute has a type
• Must be atomic type (why? see later)
• Called domain
• Examples:
Schemas vs. instances
(very important, make sure you know
the difference)
13
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
14
How should we talk about relations
(that is, represent them)?
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
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}
17
Two Definitions of Relations
• We will switch back and forth between these two:
- Positional tuples, without attribute names
- Relational schemas with attribute names
18
Now the fun part: translating from ER to
relational model
19
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
address name^ ssn 20
Person
buys
makes
employs
Company Product
name category
Stock price
name
price
An Example
25
Special cases:
1) many one relations
2) weak entity sets
3) isa cases
26
Combining Two Relations
makes Company Product
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
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).
Risk with Many Many Relationships
• Combining Drinkers with Likes would be a
mistake. It leads to redundancy, as:
name addr beer Sally 123 Maple Bud Sally 123 Maple Miller
Redundancy
29
Handling Weak Entity Sets
Team affiliation University
sport number 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 ?) 30
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.
Another Example
Logins At Hosts
name name
Hosts(hostName) Logins(loginName, hostName, time) At(loginName, hostName, hostName2)
Must be the same
time
At becomes part of Logins
Translating Subclass Entities
Product
Educational Product
Software Product
Educ-software Product
ageGroup topic
Platforms required memory
Educational-method
isa
isa
isa
isa
37
Example
Beers
Ales
isa
name (^) manf
color
38
Object Oriented
name manf Bud Anheuser-Busch Beers
name manf color Summerbrew Pete’s dark Ales
Beers
Ales
isa
name (^) manf
color
E/R Style
name manf Bud Anheuser-Busch Summerbrew Pete’s Beers
name color Summerbrew dark Ales
Beers
Ales
isa
name (^) manf
color
Using Nulls
name manf color Bud Anheuser-Busch NULL Summerbrew Pete’s dark Beers
Beers
Ales
isa
name (^) manf
color
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.
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
