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!

CompanyName	stockprice	startyear	productname	Productcategory
Apple	350	2001	iphone	Phone
Apple	350	2001	ipad	Tablet
Microsoft	350	2002	Ipad	Tablet

Weak Entity Sets

How do we translate Weak Entity Sets?

University(name)
Team(sport, affil_uni_name, 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)

**Recommendation: don’t use this option. Problems crop up down the road.

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:

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