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


  • 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

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

Weak Entity Sets

How do we translate Weak Entity Sets?

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.


Null values


OO Method

Name Manf
Name Manf Color

E/R Method

Name Manf
Name Color

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