1 cs 430 database theory winter 2005 lecture 15: how to convert an er model to relations

1

CS 430Database Theory

Winter 2005

Lecture 15: How to Convert an ER Model to Relations

2

Issue

We do Conceptual Modeling using ER Models

Our databases are relational Look a little like ER models but not exactly

How to we convert an ER model to relations for an RDBMS

Note: Typically the resulting relations are 3NF

3

ER Model

ER Model has: Entities, Relationships, Attributes

Relationships may have attributes May have multi-valued and composite attributes

To convert to Relation Tables: Need Keys for Entities Relationships need cardinalities, e.g. 1:1, 1:n, m:n

Example ER Model: Figure 7.1 from Text Book

4

Steps

Convert Independent (Strong, Regular) Entities

Convert Dependent (Weak) Entities Convert Binary Relations

1:1 1:n m:n

Convert N-Ary Relationships (N>2) Convert Multi-Valued Attributes

5

Convert Independent Entities

Create a new Relation for the Entity Make all simple (non-multi-valued, non-

composite) attributes of the Entity into attributes of the Relation For single-valued composite attributes, use the

underlying simple attributes Choose some Key attribute(s) to be the

Primary Key

6

Result So Far

SSN Fname Minit LName Sex Address BDate Salary

DName DNumber

PName PNumber PLocation

EMPLOYEE

DEPARTMENT

PROJECT

7

Convert Dependent Entities

Create a new Relation for the Entity Make all simple attributes into attributes of

the relation (same as for Independent Entities)

Add the primary key attribute(s) of the owning entity as a foreign key for the weak entity Convert entities in order: If one weak entity owns

another, convert the owner first Add ON UPDATE and ON DELETE

The usual approach is CASCADE

8

Result So Far


DName DNumber


EMPLOYEE

DEPARTMENT

PROJECT

ESSN Dependent_Name Sex BDate Relationship

DEPENDENT

9

Map 1:1 Relations

Foreign Key approach: Include the primary key of one relation as a

foreign key in the other Include attributes of the relationship with the

foreign key Merged Relationship

Merge the two relations Cross Reference

Add a new table with the keys from both entities (see m:n for example of this)

10

Result So Far



EMPLOYEE

DEPARTMENT

PROJECT


DEPENDENT

DName DNumber MgrSSN MgrStartDate

11

Map 1:n Relations

Let R be the “1 side”, S the “n side” Include R’s key as a foreign key in S Include any attributes of the relation together with

the foreign key in S Or use the Cross Reference table approach

(m:n approach)

12

Result So FarEMPLOYEE

DEPARTMENT

PROJECT


DEPENDENT


PName PNumber PLocation DNum

SSN Fname Minit LName Sex Address BDate Salary SuperSSN DNo

13

Map m:n Relations

Create a new Relation to represent the Relationship Include the keys of the related entities as foreign

keys in the relation Make the key of the relation the combined keys of

the participating entities Include any simple attributes of the relationship as

attributes of the relation Include CASCADE for UPDATE and DELETE

(typical)

14

Result So FarEMPLOYEE

DEPARTMENT

PROJECT


DEPENDENT




ESSN PNo Hours

WORKS_ON

15

Map N-Ary Relationships

Treat the same as mapping m:n relationships Create a new relation Make the keys of all the parents, foreign keys of

the relation Make the key the combined foreign keys Copy any simple attributes of the relationship as

attributes of the relation

16

Map Multi-Valued Attributes

Create a new Relation to represent the Attribute Include the key of the parent Relation as a foreign

key for the new relation Include the Multi-Valued Attribute as an attribute of

the relation Make the primary key the combination of the parent

key and the attribute CASCADE on UPDATE and DELETE Note: If you have a composite multi-valued attribute,

check to make sure you don’t have a weak entity Impacts the set of attributes that make up the primary key

17

Final Result




DNumber DLocation

ESSN PNo Hours


EMPLOYEE

DEPT_LOCATIONS

DEPARTMENT

DEPENDENT

WORKS_ON

PROJECT

1 cs 430 database theory winter 2005 lecture 15: how to convert an er model to relations

Documents