Transforming ER & EER diagrams into Relations
This presentation is the property of its rightful owner.
Sponsored Links
1 / 21

Transforming ER & EER diagrams into Relations (Chapter 9) PowerPoint PPT Presentation


  • 325 Views
  • Uploaded on
  • Presentation posted in: General

Transforming ER & EER diagrams into Relations (Chapter 9). Overview. A relatively straightforward process with a well-defined set of rules. Many CASE Tools can automatically perform many of the conversion steps. CASE tools often cannot model complex data relationships.

Download Presentation

Transforming ER & EER diagrams into Relations (Chapter 9)

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Transforming ER & EER diagrams into Relations(Chapter 9)


Overview

  • A relatively straightforward process with a well-defined set of rules.

  • Many CASE Tools can automatically perform many of the conversion steps.

    • CASE tools often cannot model complex data relationships.

    • There are sometimes legitimate alternatives where you will need to choose a particular solution.

    • You must be prepared to perform a quality check on the results obtained with a CASE Tool.


Map Simple Regular Entities

  • Each regular entity type in an ER diagram is transformed into a relation.

  • The name given to the relation is generally the same as the entity type.

  • Each simple attribute of the entity type become an attribute of the relation.

  • Choose one of the key(s) as primary key of the relation.

  • How about composite attributes?

  • How about multi-valued attributes?


Street

Name

City

ID

Address

State

Zip

Map Composite Attributes

  • When a regular entity type has a composite attribute, only the simple component attributes of the composite attribute are included in the new relation.

COSTOMER

CUSTOMER(ID, Name, Street, City, State, Zip)


Map Multi-valued Attributes

  • When a regular entity type contains a multivalued attribute, two new relations (rather than one) are created.

  • The first relation contains all of the attributes of the entity type except the multi-valued attribute.

  • The second relation contains two sets of attributes.

    • The primary key from the first relation, which becomes a foreign key of the second relation.

    • Multi-valued attribute itself.

    • The primary key of the second of relation is the combination of all attributes.

  • The name of the second relation should capture the meaning of the multi-valued attribute.


Name

SSN

Street

City

Address

Skills

State

Zip

Example

EMPLOYEE

EMPLOYEE(SSN, Name, State, City, State, Zip)

EMPLOYEE_SKILL(SSN, Skill)


SSN

DeptName

Name

Major_in

Map Binary One-Many Relationship

  • Create a relation for each of the two entity types participating in the relationship.

  • Include the primary key attribute (or attributes) of the entity on the one-side of the relationship as a foreign key in the relation that is on the many-side of the relationship ( a mnemonic you can use to remember this rule is this: The primary key migrates to many side).

N

1

STUDENT

DEPT

STUDENT(SSN, Name, DeptName)

DPET(DeptName)


Name

CID

SID

Text

taking

Map Binary Many-Many Relationships

  • Suppose that there is a binary relationship (M:N) between two entity types A and B. For such a relationship, create a new relation C:

    • Include as foreign key attributes in C the primary key for each of the two participating entity type.

    • These attributes become the primary key of C.

    • Any attributes that are associated with the relationship are included with the relation C.

Grade

STUDENT

COURSE

N

M

STUDENT(SID, NAME)

COURSE(CID, Text)

Taking(SID, CID, Grade)


Name

SSN

Manages

Map Binary One-One Relationship

  • Binary 1:1 relationship can be viewed as a special case of 1:m relationship. The process of mapping such a relationship to relations requires to steps:

    • first, two relations are created, one for each of the participating entity type.

    • Second, the primary key of one of the relations is included as foreign key in the other relation.

DeptName

1

1

DEPT

EMPLOYEE

EMPLOYEE(SSN, Name)

EMPLOYEE(SSN, Name, DeptName)

DEPT(DeptName, ManagerSSN)

DEPT(DeptName)


Name

SSN

Map Unary One-Many Relationship

  • The entity type in the unary relationship is mapped to a relation using the procedure described before. Then a foreign key attribute is added within the same relation that references the primary key values.

  • Note that the foreign key attribute name should reflect the role name on the one-side.

  • A recursive foreign key is a foreign key in a relation that references the primary key values of that same relation.

Supervisee

N

EMPLOYEE

Manages

1

Supervisor

EMPLOYEE(SSN, Name, SupervisorSSN)


Quantity

Name

Unit_cost

Map Unary Many-Many relationship

  • With this type of relationship:

    • Two relations are created: one to represent the entity type in the relationship and the other an associative relation to represent the M:N relationship itself.

    • The primary key of the associative relation consists of two parts: both take their values from the primary key of the other relation.

    • Any attribute of the relationship is included in the associative relation.

ID

Item

M

PART

Contains

N

Components

COMPONENT(ItemID, ComponentID, Quantity)

PART(ID, Name, Unit_Cost)


name

name

addr.

SSN

salary

sex

sex

birthdate

birthdate

DEPENDANT_

OF

Dependants

Map Weak Entities

  • For each weak entity type, create a new relation and include all of the simple attributes (or simple components of composite attributes) as attributes of this relation.

  • Then, include the primary key of the owner relation as a foreign key attribute in this relation.

  • The primary key of the new relation is the combination of this primary key of the owner and the partial key of the weak entity type.

N

1

Employees

relationship


Example Results

Employee( SSN, name, addr, salary sex, birthdate)

Dependants(name, birthdate, sex, relationship, empSSN)

  • The relation for the weak entity not only has the attributes of itself, but also has the key attributes of the other entity sets.

  • Do not construct a relation for a “double-diamond” relationship.


ProjName

Quantity

SName

ProjName

Quantity

SName

1

1

N

N

SUPPLIER

SUPPLY

PROJECT

SPJ

SS

SUPPLY

N

SP

PartNo

1

PART

PartNo

Map Ternary (n-ary) Relationship

  • It is recommended that you convert the ternary (n-ary) relationship to a number of binary relationships, and then transform the diagram into relationships.

SUPPLIER

PROJECT

PART


Example Result

SUPPLIER(SName)

PROJECT(ProjName)

SUPPLY(SName, ProjName, PartID, Quantity)

PART(PartID)


Map Supertype/Subtype Relationships

  • The relational data model does not yet directly support/subtype relationships.

  • There are various strategies that database designer can use to represent these relationships.

  • In this lecture, we introduce the most commonly employed strategy.


Mapping Strategy

  • Create a separate relation for the supertype and for each of its subtypes.

  • Assign to the relation created for the supertype the attributes that are common to all memebers of the supertype, including the primary key.

  • Assign to the relation for each subtype the primary key of the supertype, and this primary key is also a foreign key that references the primary key in relation representing the supertype.

  • Assign to the relation for each subtype the attributes that are unique to that subtype.


Address

Name

SSN

Date_hired

Billing_rate

Annual_salary

Stock_option

Contract_No.

Hourly_rate

Example

EMPLOYEE

d

HOURLY

EMPLOYEE

SALARED

EMPLOYEE

CONSULTANT

EMPLOYEE( SSN, Name, Address, Date_hired)

HOURLY_EMPLOYEE(SSN, Hourly_Rate)

SALARIED_EMPLOYEE(SSN, Annual_salary, Stock_Option)

CONSULTANT(SSN, Contract_No, Billing_rate)


Map Categories

  • Problem

    • Multiple supertypes may have different primary keys.

  • Solution

    • The concept of surrogate key, a specified new key attribute for the subtype.


CName

Name

SSN

Address

Total_Balance

BName

Has_Acct

Example

PERSON

COMPANY

U

N

ACCOUNT

HOLDER

M

BANK


Example Results

COMPANY(CName, Address, OwnerID)

PERSON(SSN, Name, OwnerID)

ACCOUNTHOLDER(OwnerID)

HASACCOUNT(OwnerID, BName, Total_Balance)

BANK(BName)


  • Login