database management systems chapter 3 the relational data model (i)
DESCRIPTION
Database Management Systems Chapter 3 The Relational Data Model (I). Instructor: Li Ma Department of Computer Science Texas Southern University, Houston. September, 2006. The Relational Data Model. Tables Schemas Conversion from E/R to Relations. Attributes (column headers). Tuples - PowerPoint PPT PresentationTRANSCRIPT
Database Management Systems
Chapter 3 The Relational Data Model (I)
Instructor: Li Ma
Department of Computer ScienceTexas Southern University, Houston
September, 2006
Jeffrey Ullman 3
A Relation is a Table
name manfWinterbrew Pete’sBud Lite Anheuser-
BuschBeers
Attributes(columnheaders)
Tuples(rows)
Jeffrey Ullman 4
Schemas Relation schema = relation name and
attribute list. Optionally: types of attributes. Example: Beers(name, manf) or
Beers(name: string, manf: string) Relation instance = current set of
rows. Database = collection of relations. Database schema = set of all relation
schemas in the database.
Jeffrey Ullman 5
A1 A2 A3 ... An
a1 a2 a3 an
b1 b2 a3 cn
a1 c3 b3 bn...
x1 v2 d3 wn
Relational Data ModelSet theoretic
Domain — set of valueslike a data type
Cartesian product (or product)D1 D2 ... Dnn-tuples (V1,V2,...,Vn)s.t., V1 D1, V2 D2,...,Vn Dn
Relation-subset of cartesian productof one or more domainsFINITE only; empty set allowed
Tuples = members of a relation inst.Arity = number of domainsComponents = values in a tupleDomains — corresp. with attributesCardinality = number of tuples
Relation as tableRows = tuplesColumns = componentsNames of columns = attributesSet of attribute names = schema
REL (A1,A2,...,An)
Arity
Cardinality
Attributes
Component
Tuple
Jeffrey Ullman 6
Name address tel #
5 3 7
Cardinality of domain
Domains
N A T
N1 A1 T1
N2 A2 T2
N3 A3 T3
N4 T4
N5 T5
T6
T7
Relation: ExampleDomain of
Relation
N A T
N1 A1 T1
N1 A1 T2
N1 A1 T3.
……
.
N1 A1 T7
N1 A2 T1
N1 A3 T1
N2 A1 T1
Arity 3
Cardinality <=5x3x7
of relation
Tuple µ
Domain
Component
Attribute
Jeffrey Ullman 7
Relation Instance
Name Address Telephone
Bob 123 Main St 555-1234
Bob 128 Main St 555-1235
Pat 123 Main St 555-1235
Harry 456 Main St 555-2221
Sally 456 Main St 555-2221
Sally 456 Main St 555-2223
Pat 12 State St 555-1235
Jeffrey Ullman 8
About Relational ModelOrder of tuples not important
Order of attributes not important (in theory)
Collection of relation schemas (intension)Relational database schema
Corresponding relation instances (extension)Relational database
intension vs. extensionschema vs. data
metadataincludes schema
Jeffrey Ullman 9
Why Relations?
Very simple model. Often a good match for the way we
think about our data. Abstract model that underlies SQL,
the most important language in DBMS’s today. But SQL uses “bags” while the abstract
relational model is set-oriented.
Jeffrey Ullman 10
From E/R Diagrams to Relations
Entity set -> relation. Attributes -> attributes.
Relationships -> relations whose attributes are only: The keys of the connected entity sets. Attributes of the relationship itself.
Jeffrey Ullman 11
Keys in Relations
An attribute or set of attributes K is a key for a relation R if we expect that in no instance of R will two different tuples agree on all the attributes of K.
Indicate a key by underlining the key attributes.
Example: If name is a key for Beers:Beers(name, manf)
Jeffrey Ullman 13
Relationship -> Relation
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)
Jeffrey Ullman 14
Combining Relations
OK to combine into one relation:1. The relation for an entity-set E 2. The relations for many-one
relationships of which E is the “many.” Example: Drinkers(name, addr) and
Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer).
Jeffrey Ullman 15
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
Jeffrey Ullman 16
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 relationship is redundant and yields no relation (unless it has attributes).
Jeffrey Ullman 17
Example
Logins HostsAt
name name
Hosts(hostName, location)Logins(loginName, hostName, billTo)At(loginName, hostName, hostName2)
Must be the same
billTo
At becomes part ofLogins
location
Jeffrey Ullman 18
Subclasses: Three Approaches
1. Object-oriented : One relation per subset of subclasses, with all relevant attributes.
2. E/R style : One relation for each subclass: Key attribute(s). Attributes of that subclass.
3. Use nulls : One relation; entities have NULL in attributes that don’t belong to them.
Jeffrey Ullman 20
Object-Oriented
name manfBud Anheuser-Busch
Beers
name manf colorSummerbrew Pete’s dark
Ales
Good for queries like “find thecolor of ales made by Pete’s.”
Jeffrey Ullman 21
E/R Stylename manfBud Anheuser-BuschSummerbrew Pete’s
Beers
name colorSummerbrew dark
Ales
Good for queries like“find all beers (includingales) made by Pete’s.”