Data Normalization: Converting Entities and Relationships in Databases, Slides of Database Management Systems (DBMS)

Download Data Normalization: Converting Entities and Relationships in Databases and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Logical Database Design

Objectives

• Describe the concept of logical database

design.

• Design relational databases by converting

entity-relationship diagrams into relational

tables.

• Describe the data normalization process.

Docsity.com^ 7-

Logical Database Design

• The process of deciding how to arrange the

attributes of the entities in the business

environment into database structures, such as

the tables of a relational database.

• The goal is to create well structured tables

that properly reflect the company’s business

environment.

Docsity.com^ 7-

Logical Design of Relational Database

Systems

• (1) The conversion of E-R diagrams into

relational tables.

• (2) The data normalization technique.
• (3) The use of the data normalization

technique to test the tables resulting from the

E-R diagram conversions.

Docsity.com^ 7-

Converting a Simple Entity

Salesp e rso n N umb er

Salesp e rso n N ame

C om m iss io n Perc e nta g e Y e ar o f Hire SA LE S PERS ON

• The table simply contains the attributes that were specified in

the entity box.

• Salesperson Number is underlined to indicate that it is the

unique identifier of the entity and the primary key of the

table.

7-

Converting Entities in Binary

Relationships: One-to-One

• There are three options for designing tables to

represent this data.

7-

One-to-One: Option

Salesperson Number

Salesperson Name

Commission Percentage

Year of Hire

Office Number SALESPERSON

• Separate tables for the

SALESPERSON and OFFICE

entities, with Office Number

as a foreign key in the

SALESPERSON table.

7-

O i cf fe

Nu m r bTel e p e eh oSi z ne

OFF CI E

One-to-One: Option

Sa les pe rs o n Nu m b er

Sa les pe rs o n Na me

C o mmissio n Percen ta g e

Ye ar of Hire SALESPER SO N

• Separate tables for the

SALESPERSON and OFFICE

entities, with Salesperson

Number as a foreign key in

the OFFICE table.

7-

O f fi ce

Nu m b er Tel e p h o ne

Sa les pe rs o n

Nu m b er Si ze

OFF IC E

Converting Entities in Binary

Relationships: One-to-Many

Salesperson Num b er

Salesperson Name

C ommission Percentage Year of Hire SALESPER SO N

C usto me r Nu m b er

C usto me r Na me HQ C ity

Sa les pe rs o n Nu m b er CUSTOME R

7-

Converting Entities in Binary

Relationships: Many-to-Many

• E-R diagram with the many-to-many binary

relationship and the equivalent diagram using an associative entity.

7-

Converting Entities in Binary

Relationships: Many-to-Many

Product Number

Product Name Unit Price PRODUCT

Salesperson Num b er

Salesperson Name

C ommission Percentage Year of Hire SALESPER SO N

• The primary key of SALE is

the combination of the

unique identifiers of the

two entities in the many-

to-many relationship.

Additional attributes are

the intersection data.

7-

Sa les pe rs o n Nu m rb e

P ro d u c t Nu m rb e Qu a n tit y SALE

P o d u c tr

Nu m rb e

P o d u c tr

Na me Un i tP i c er

PR DO CU T

Converting Entities in Unary

Relationships: One-to-One

Salesperson

Number

Salesperson

Name

Commission

Percentage Year of Hire

Backup

Number

SALESPERSON

• With only one entity type

involved and with a one-to-

one relationship, the

conversion requires only one

table.

7-

Converting Entities in Unary

Relationships: Many-to-Many

P o d u c tr Nu m rb e

P o d u c tr Na me Un i tP i c er PR OD CU T

• This relationship requires two tables in the

conversion.

• The PRODUCT table has no foreign keys.

7-

P ro d u c t Nu m rb e

Su b-As se m lyb Nu m rb e Qu a n tit y COMP ONENT

Converting Entities in Unary

Relationships: Many-to-Many

P o d u c tr Nu m rb e

P o d u c tr Na me Un i tP i c er PR OD CU T

• A second table is created since in the conversion of a many-to-

many relationship of any degree — unary, binary, or ternary

— the number of tables will be equal to the number of entity

types (one, two, or three, respectively) plus one more table

for the many-to-many relationship.

7-

P ro d u c t Nu m rb e

Su b-As se m lyb Nu m rb e Qu a n tit y COMP ONENT