The Relational Data Model Tables Schemas Conversion from

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

A Relation is a Table Attributes (column headers) Tuples (rows) name Corvette Maxima manf G. M. Nissan cars 2

Schemas u. Relation schema = relation name and attribute list. w Optionally: types of attributes. w Example: cars(name, manf) or cars(name: string, manf: string) u. Database = collection of relations. u. Database schema = set of all relation schemas in the database. 3

Why Relations? u. Very simple model. u. Often matches how we think about data. u. Abstract model that underlies SQL, the most important database language today. 4

From E/R Diagrams to Relations u. Entity set -> relation. w Attributes -> attributes. u. Relationships -> relations whose attributes are only: w The keys of the connected entity sets. w Attributes of the relationship itself. 5

Entity Set -> Relation model manf cars Relation: cars(model, manf) 6

Relationship -> Relation name husband name addr drivers 1 Likes manf cars 2 Mustangdies Favorite wife Married Likes(driver, car) Favorite(driver, car) Mustangdies(name 1, name Married(husband, wife) 7

Combining Relations u 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. ” u Example: drivers(name, addr) and Favorite(driver, car) combine to make driver 1(name, addr, favcar). 8

Risk with Many-Many Relationships u. Combining drivers with Likes would be a mistake. It leads to redundancy, as: name Sally addr car 123 Maple Mustang Maxima Redundancy 9

Handling Weak Entity Sets u. 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. u. A supporting relationship is redundant and yields no relation (unless it has attributes). 10

Example name bill. To Logins name At Hosts location Hosts(host. Name, location) Logins(login. Name, host. Name, bill. To) At(login. Name, host. Name 2) At becomes part of Logins Must be the same 11

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

Example model cars manf isa RLR Sports Car 13

Object-Oriented name Mustang cars name Corvette manf Nissan manf G. M. Sports RLR None Good for queries like “find the RLR of Sports Car made by G. M. . ” 14

E/R Style name Mustang Corvette manf Nissan G. M. Cars name RLR Corvette none Sports Cars Good for queries like “find all cars (including Sports Cars) made by G. M. . ” 15

Using Nulls name Mustang Corvette manf Nissan G. M. cars RLR NULL none Saves space unless there are lots of attributes that are usually NULL. 16