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


  • 306 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

Transforming ER & EER diagrams into Relations(Chapter 9)


Overview

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

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?


Map composite 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

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.


Example

Name

SSN

Street

City

Address

Skills

State

Zip

Example

EMPLOYEE

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

EMPLOYEE_SKILL(SSN, Skill)


Map binary one many relationship

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)


Map binary many many relationships

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)


Map binary one one relationship

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)


Map unary one many relationship

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)


Map unary many many relationship

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)


Map weak entities

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

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.


Map ternary n ary 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

Example Result

SUPPLIER(SName)

PROJECT(ProjName)

SUPPLY(SName, ProjName, PartID, Quantity)

PART(PartID)


Map supertype subtype relationships

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

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.


Example1

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

Map Categories

  • Problem

    • Multiple supertypes may have different primary keys.

  • Solution

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


Example2

CName

Name

SSN

Address

Total_Balance

BName

Has_Acct

Example

PERSON

COMPANY

U

N

ACCOUNT

HOLDER

M

BANK


Example results1

Example Results

COMPANY(CName, Address, OwnerID)

PERSON(SSN, Name, OwnerID)

ACCOUNTHOLDER(OwnerID)

HASACCOUNT(OwnerID, BName, Total_Balance)

BANK(BName)


  • Login