Conversion of Entity Relationship Diagrams into Relational Tables

1

• Conversion of Entity Relationship Diagrams into
  Relational Tables

2
Outline

• Introduction
• Relational data model
• Basic conversion rules
• Generalization hierarchy conversion
• 1-1 rule
• Unary relationships
• Identifying relationships

3
Introduction
Conceptual Data Modeling
Data requirements
ERD
Logical Database Design
Tables
Distributed Database Design
Physical Database Design
Internal Schema
4
Relational Data Model

• Relational database is a collection of tables
• Heading Table name and Column names
• Body Rows, occurrences of data
• Primary key An attribute (or combination of
  attributes) that uniquely identifies each row of
  a table cannot contain null values
• Foreign key Column(s) whose values must match
  the values in a candidate key of another table

5
Relational Data Model

• Relational Key
• Candidate Key
• Primary Key
• No two rows with the same primary key value
• No null values in a primary key
• Foreign Key
• Foreign keys must match candidate key of source
  table
• Foreign keys in some cases can be null
• Properties of Relations
• Each relation has a unique name
• Each cell of a relation contains exactly one
  atomic (single) value
• Each attribute has unique name
• The values of an attribute are all from the same
  domain
• The order of attributes has no significance
• Each tuple is distinct
• The order of tuples has no significance

6
Summary of Conversion Basic Rules

• Each entity type becomes a table.
• Each attribute becomes a column.
• Each 1-M relationship becomes a foreign key in
  the M table.
• Each M-N relationship becomes an associative
  table with a combined primary key.
• Each identifying relationship adds a column to a
  primary key.

7
Application of Basic Rules (I)
Course
Offering
8
Application of Basic Rules (II)
Student
Enrolls_In
Offering
9
Application of Basic Rules (III)

• Same conversion result as the previous slide
• Different application of rules

10
ERD With Two Weak Entity Types
Student
Enrolls_In
Offering
Attendance
11
Generalization Hierarchy Rule

• Generalization hierarchy
• Each subtype table contains specific attributes
  plus primary of its parent table.
• Foreign key constraints for subtype tables

12
Generalization Hierarchy Example
EMPLOYEE
SALARYEMP
HOURLYEMP
13
1-1 Relationships

• When any one cardinality is optional one
  Primary key on the mandatory side becomes a
  foreign key on the optional side
• When each cardinality is mandatory one Primary
  key on any mandatory side becomes a foreign key
  on the other side

14
1-1 Relationships
EMPLOYEE
OFFICE
15
Self-Referencing Relationships
a)
FACULTY
COURSE
b)
PREREQTO
16
Identification Dependency (Weak Entity)
BUILDING
ROOM
17
Summary

• Conversion rules convey data model differences
• Conversion rules are easy to apply
• Some CASE tools perform conversion