Database Management Systems Chapter 3 The Relational Data Model (I)

1. Database Management SystemsChapter 3 The Relational Data Model (I) Instructor: Li Ma Department of Computer Science Texas Southern University, Houston September, 2006

2. The Relational Data Model Tables Schemas Conversion from E/R to Relations

3. Attributes (column headers) Tuples (rows) A Relation is a Table name manf Winterbrew Pete’s Bud Lite Anheuser-Busch Beers 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. Relational Data Model • Relation as table • Rows = tuples • Columns = components • Names of columns = attributes • Set of attribute names = schema • REL (A1,A2,...,An) Set theoretic Domain — set of values like a data type Cartesian product (or product) D1 D2 ...  Dn n-tuples (V1,V2,...,Vn) s.t., V1 D1, V2 D2,...,Vn Dn Relation-subset of cartesian product of one or more domains FINITE only; empty set allowed Tuples = members of a relation inst. Arity = number of domains Components = values in a tuple Domains — corresp. with attributes Cardinality = number of tuples A1 A2 A3 ... An a1 a2 a3 an b1 b2 a3 cn a1 c3 b3 bn . . . x1 v2 d3 wn Attributes C a r d i n a l i t y Tuple Component Arity Jeffrey Ullman

6. Relation: Example Domain 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 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 Attribute Component Tuple µ Domain 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 Model Order 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. extension schema vs. data metadata includes 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

12. Entity Set -> Relation Relation: Beers(name, manf) name manf Beers Jeffrey Ullman

13. Likes husband 2 1 Favorite Buddies Likes(drinker, beer) Favorite(drinker, beer) wife Buddies(name1, name2) Married Married(husband, wife) Relationship -> Relation name name addr manf Drinkers Beers Jeffrey Ullman

14. Combining Relations • OK to combine into one relation: • The relation for an entity-set E • 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. Redundancy 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 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. Must be the same At becomes part of Logins Example name name Logins At Hosts location billTo Hosts(hostName, location) Logins(loginName, hostName, billTo) At(loginName, hostName, hostName2) Jeffrey Ullman

18. Subclasses: Three Approaches • Object-oriented: One relation per subset of subclasses, with all relevant attributes. • E/R style: One relation for each subclass: • Key attribute(s). • Attributes of that subclass. • Use nulls: One relation; entities have NULL in attributes that don’t belong to them. Jeffrey Ullman

19. Example Beers name manf isa Ales color Jeffrey Ullman

20. Object-Oriented name manf Bud Anheuser-Busch Beers name manf color Summerbrew Pete’s dark Ales Good for queries like “find the color of ales made by Pete’s.” Jeffrey Ullman

21. E/R Style name manf Bud Anheuser-Busch Summerbrew Pete’s Beers name color Summerbrew dark Ales Good for queries like “find all beers (including ales) made by Pete’s.” Jeffrey Ullman

22. Using Nulls name manf color Bud Anheuser-Busch NULL Summerbrew Pete’s dark Beers Saves space unless there are lots of attributes that are usually NULL. Jeffrey Ullman