Translating to the Relational Model

ER Diagrams vs Relational Model

Entity Relationship Models

  • entity, attribute, entity set
  • relation: binary, multiway, converting from multiway
  • relationship roles, attributes on relationships
  • subclasses (is-a)
  • Weak Entity Sets

Constraints

  • on relations
  • many-one, one-one, many-many (limitations of arrows)
  • keys, single-valued, ref integrity, domain & general constraints

Relational Model

Relations are database tables

  • Attributes have domains
  • Relations have keys
  • Relations have instances

CREATE TABLE Products (
name        CHAR(20),
category    CHAR(20),
      price       REAL
);

Translate from ER to Relational Model

Basic cases

  • entity set E = relation with attributes of E
  • relationship R = relation with attributes being keys of related entity sets + attributes of R

Product(name, category, price)

What about the relationship?

How should we translate this?

Products(name, category, price)
Makes(products_name, products_category, companies_name, start_year)
Companies(name, stock_price)

Combining Relationships

It is OK to combine two relations in some circumstances:

  1. Many-to-one relationship
  2. Let $M$ be the many-side EntitySet (i.e., Products), Let $R$ be a relationship (i.e., Makes). Let $O$ be the one-side entity set (i.e., Company)
  3. Combine $R$ into $M$. Keys from $O$ become foreign keys.

Products(name, category, price, start_year, company_name_fk)
Companies(name, stock_price)

What if Makes is many to many, can we combine?

Products(name, category, price)
Makes(products_name, products_category, companies_name, start_year)
<– Called a join table
Companies(name, stock_price)

Why? Redundancy!

CompanyNamestockpricestartyear productname Productcategory
Apple3502001iphonePhone
Apple3502001ipadTablet
Microsoft3502002IpadTablet

Weak Entity Sets

How do we translate Weak Entity Sets?

University(name)
Team(sport, affiliatedUniversity, attendance)

Why combine Affiliation with Team?

Why? – Because its many to one…. If referential integrity is noted to be many-to-many then affiliation must be separate.

Translating Subclass Entities

Three Options:

Option #1 – OO Approach

4 tables: each object can only belong to a single table

Product(name, price,  category, manufacturer)
EducationalProduct( name, price, category, manufacturer, ageGroup, topic)
SoftwareProduct( name, price, category, manufacturer, platforms, requiredMemory)
EducationalSoftwareProduct( name, price, category, manufacturer, ageGroup, topic, platforms, requiredMemory)

All names are distinct

Option #2 – E/R Approach

Product(name, price,  category, manufacturer)
EducationalProduct( name, ageGroup, topic)
SoftwareProduct( name, platforms, requiredMemory)
EducationalSoftwareProduct(name, educational-method)

Same name may appear in several relations. Why is this necessary?

Option #3 – Null value approach

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

                Missing value?
                Undefined value?

Overview of 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.

Example:

Null values

NameManfColor
ZuneMicrosoftNull
iPodV1AppleWhite
MusicPlayers

OO Method

Name Manf
ZuneMicrosoft
MusicPlayers
Name Manf Color
iPodV1AppleWhite
IPods

E/R Method

Name Manf
iPodApple
ZuneMicrosoft
MusicPlayers
Name Color
iPodV1White
IPods

Consider tradeoffs for the different queries:

Query 1: Find the colors of IPods made by some company, say, Apple.

                OO Method – only have to load and search IPods

Query 2: Find all music players made by Apple.

                E/R Method, maybe Apple makes lots of different music players – only have to search Music Players table