Download ER Model vs. Relational Model: Translating ER Diagrams to Relational Design and more Study notes Deductive Database Systems in PDF only on Docsity!
1
CS 411
Database Systems
03: Relational Model
address name^ ssn^2 Person buys makes employs Company Product name category stockprice name price
ER Model Review
Entity Set Relationship Attribute Many-one Many-many Key 3
Subclasses
Beers Budweiser Kirin Sapporo Heineken name manf Summerbrew Bass Piranha Ales color Beers Ales isa name manf color 4
Weak Entity Set
Student University name student-ID name affiliation )
Database design process
Ideas E/R model Relational model Tables: Column names: attributes rows: tuples Diagrams: Entities Attributes Relationships ER model is well-suited to capture applications’ requirements
Today’s lecture
- Define relational model
- Show how to convert an ER diagram into
relations
7
Motivations & comparison of
ER with relational model ...
8
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 defining manipulations on data 9
The basics of the relational model ...
10
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)
19
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 20 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} 21
Two Definitions of Relations
- We will switch back and forth between these two:
- Positional tuples, without attribute names
- Relational schemas with attribute names 22
Now the fun part: translating from ER to
relational model
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
Basic cases ...
25
Entity Sets to Relations
Product name category price Product : Name Category Price gizmo gadgets $19.99 26
Relationships to Relations
makes Company Product 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 27
Special cases:
1 ) many one relations
2 ) weak entity sets
3 ) isa cases
28
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
Drinker 1 (name, addr, favoriteBeer).
Risk with Many Many Relationships
- Combining Customers with Likes would be a
mistake. It leads to redundancy, as:
name addr beer Sally 123 Maple Bud Sally 123 Maple Miller Redundancy Customers Beers name addr name manf Likes
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 38
Translating Subclass Entities: The Rules
Three approaches: !" Object-oriented : each entity belongs to exactly one class; create a relation for each class, with all its attributes. #" 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. $" Use nulls : create one relation; entities have null in attributes that don’t belong to them. 39
Example
Beers Ales isa name (^) manf color 40
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 may cause redundancy
- translating weak entity sets
- translating isa hierarchy
- 3 choices, with trade-offs
