Relational Model and Translating ER into Relational 1

Relational Model and Translating ER into Relational 1

Lecture Outline • Relational model • Translating from ER to relational model 2

Motivations & comparison of ER with relational model. . . 3

Database Modeling & Implementation Ideas Database Model (E/R, ODL) Diagrams (E/R) Relational Schema Tables: column names: attributes rows: tuples Physical storage Complex file organization and index structures. 4

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 Table name Products: Name Category Manufacturer $19. 99 gadgets Gizmo. Works Power gizmo $29. 99 gadgets Gizmo. Works Single. Touch $149. 99 photography Canon Multi. Touch $203. 99 household Hitachi gizmo tuples Attribute names Price 7

Domains • • Each attribute has a type Must be atomic type (why? see later) Called domain Examples: – Integer – String – Real –… 8

Schemas vs. instances (very important, make sure you know the difference) 9

Schemas The Schema of a Relation: – Relation name plus attribute names – E. g. Product(Name, Price, Category, Manufacturer) – In practice we add the domain for each attribute The Schema of a Database – A set of relation schemas – E. g. Product(Name, Price, Category, Manufacturer), Vendor(Name, Address, Phone), . . . . 10

Instances • Relational schema = R(A 1, …, Ak): Instance = relation with k attributes (of “type” R) – values of corresponding domains • Database schema = R 1(…), R 2(…), …, Rn(…) Instance = n relations, of types R 1, R 2, . . . , Rn 11

Example Relational schema: Product(Name, Price, Category, Manufacturer) Instance: Name Price Category Manufacturer gizmo $19. 99 gadgets Gizmo. Works Power gizmo $29. 99 gadgets Gizmo. Works Single. Touch $149. 99 photography Canon Multi. Touch $203. 99 household Hitachi 12

Annother Example Students: Takes: Courses: Three relational schemas here + three relational instances One database schema + one database instance 13

Updates The database maintains a current database state (that is, a database instance). Updates to the data (that is, the database instance) 1) add a tuple 2) delete a tuple 3) modify an attribute in a tuple Updates to the data happen very frequently. Updates to the schema: relatively rare. Rather painful. Why? 14

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 15

How should we talk about relations (that is, represent them)? Will skip this in the class You can read for fun 16

Two Mathematical Definitions of Relations Relation as Cartesian product • Tuple = element of string x int x string • E. g. t = (gizmo, 19, gadgets, Gizmo. Works) • Relation = subset of string x int x string • Order in the tuple is important ! – (gizmo, 19, gadgets, Gizmo. Works) – (gizmo, 19 , Gizmo. Works, gadgets) • No attributes 17

Relation as a set of functions • Fix the set of attributes – A={name , price, category, manufacturer} • A tuple = function t: A • Relation = set of tuples • E. g. {name price category manufacturer Domains gizmo, 19, gadgets, gizmo. Works} • Order in a tuple is not important • Attribute names are important 18

Two Definitions of Relations • We will switch back and forth between these two: – Positional tuples, without attribute names – Relational schemas with attribute names 19

Now the fun part: translating from ER to relational model 20

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 21

name An Example category name price makes Company Product Stock price buys employs Person address name ssn 22

Basic cases. . . 23

Entity Sets to Relations name category price Product: Name Category gizmo gadgets Price $19. 99 24

Relationships to Relations price name category Start Year makes name Company Product Stock price Relation Makes (watch out for attribute name conflicts) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmo. Works 1963 25

Relationship to Relation: Another Example name husband Drinkers 1 name addr Likes manf Beers 2 Buddies Favorite wife Married Likes(drinker, beer) Favorite(drinker, beer) Buddies(name 1, name 2) Married(husband, wife) 26

Special cases: 1) many-one relations 2) weak entity sets 3) is-a cases 27

Combining Two Relations price name category Start Year makes name Company Product No need for Makes. Just modify Product: name category price gizmo gadgets 19. 99 Stock price Start. Year company. Name 1963 gizmo. Works 28

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, favorite. Beer). 29

Risk with Many-Many Relationships • Combining Drinkers with Likes would be a mistake. It leads to redundancy, as: name Sally addr beer 123 Maple Bud Miller Redundancy 30

Handling Weak Entity Sets affiliation Team sport number University name Relation Team: Sport mud wrestling Number Affiliated University 15 Montezuma State U. - need all the attributes that contribute to the key of Team - don’t need a separate relation for Affiliation. (why ? ) 31

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. 32

Another Example name time Logins name At Hosts(host. Name) Logins(login. Name, host. Name, time) At(login. Name, host. Name 2) At becomes part of Logins Must be the same 33

Translating Subclass Entities Product Platforms required memory age. Group topic isa Educational Product Software Product isa Educ-software Product Educational-method 34

Option #1: the OO Approach 4 tables: each object can only belong to a single table Product(name, price, category, manufacturer) Educational. Product( name, price, category, manufacturer, age. Group, topic) Software. Product( name, price, category, manufacturer, platforms, required. Memory) Educational. Software. Product( name, price, category, manufacturer, age. Group, topic, platforms, required. Memory) All names are distinct 35

Option #2: the E/R Approach Product(name, price, category, manufacturer) Educational. Product( name, age. Group, topic) Software. Product( name, platforms, required. Memory) No need for a relation Educational. Software. Product Unless, it has a specialized attribute: Educational. Software. Product(name, educational-method) Same name may appear in several relations 36

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 37

Translating Subclass Entities: The Rules Three approaches: 1. Object-oriented : each entity belongs to exactly one class; create a relation for each class, with all its attributes. 2. 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. 3. Use nulls : create one relation; entities have null in attributes that don’t belong to them. 38

Example name Beers manf isa color Ales 39

Object-Oriented name manf Bud Anheuser-Busch Beers name Summerbrew manf color Pete’s dark Ales 40

E/R Style name manf Bud Anheuser-Busch Summerbrew Pete’s Beers name Summerbrew color dark Ales 41

Using Nulls name Bud Summerbrew manf Anheuser-Busch Pete’s Beers color NULL dark 42

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. 43

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 44