Understanding Unary & Ternary Relationships in a Database: General Hardware Co. Study, Slides of Database Management Systems (DBMS)

Download Understanding Unary & Ternary Relationships in a Database: General Hardware Co. Study and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

The Relational Database Model:

Additional Concepts

Objectives

• Describe how unary and ternary relationships

are implemented in a relational database.

• Explain the concept of referential integrity.

• Describe how the referential integrity restrict,

cascade, and set-to-null delete rules operate

in a relational database.

General Hardware Co. including

OFFICE

Sa l es pe rson Num erb

Pro duct Num erb Quan tity 1 3 7 1 9 4 4 0 4 7 3 1 3 7 2 4 0 1 3 1 7 0 1 3 7 2 6 7 2 2 6 8 8 1 8 6 1 6 3 8 6 1 7 4 1 8 6 1 9 4 4 0 2 5 2 1 8 6 2 1 7 6 5 1 9 6 1 8 6 2 4 0 1 3 3 0 7 2 0 4 2 1 7 6 5 8 0 9 2 0 4 2 6 7 2 2 7 3 4 3 6 1 1 6 3 8 6 3 7 2 3 6 1 2 1 7 6 5 3 1 1 3 6 1 2 6 7 2 2 2 7 3 (e) SALES rela tio n.

Of f ic e Num ber T eleph one Si ze ( s q. ft. ) 1 2 5 3 9 0 1-5 5 5-4 2 7 6 1 2 0 1 2 2 7 9 0 1-5 5 5-0 3 6 4 1 0 0 1 2 8 4 9 0 1-5 5 5-7 3 3 5 1 2 0 1 2 0 9 9 0 1-5 5 5-3 1 0 8 9 5 ( f )O F FICE r el ati o n.

Unary One-to-Many Relationships

• A salesperson reports to

exactly one sales manager,

but each salesperson who

does serve as a sales

manager typically has

several salespersons

reporting to him.

• There is a one-to-many

relationship within

salespersons.

Salesperson (also a sales manager) Salesperson

General Hardware Co. Salesperson

Reporting Hierarchy

One-to-Many Unary Relationship

Salesperson

Nu mber

Salesperson

N ame

C ommission

Percen tage

Y ea r

O f H ire

Sales M anager

Nu mber

1 3 7 Baker 1 0 1 9 9 5 1 8 6

1 4 2 S m ith 1 5 2 0 0 1 1 3 7

1 7 0 Taylo r 1 8 1 9 9 2 4 3 9

1 8 6 A dam s 1 5 2 0 0 1

1 9 8 W ang 2 0 1 9 9 0 2 6 7

2 0 4 Dickens 1 0 1 9 9 8 2 6 7

2 6 7 Perez 2 2 2 0 0 0 2 8 5

2 8 5 C ostello 1 0 1 9 9 6

3 2 3 McN amara 1 5 1 9 9 5 1 3 7

3 6 1 Carlyle 2 0 2 0 0 1 4 8 3

3 8 8 G oldberg 2 0 1 9 9 7 4 8 3

4 1 1 D avidso n 1 8 1 9 9 2 1 3 7

4 3 9 W arren 1 0 1 9 9 6 1 8 6

4 4 6 Albert 1 0 2 0 0 1 4 8 3

4 8 3 Jo nes 1 5 1 9 9 5 2 8 5

SA LE S PERS ON Relatio n

• Requires the

addition of one

column to the

relation

representing the

single entity

involved in the

unary

relationship.

General Hardware Company’s Product

Set

Wrench Model A (#11) Deluxe Wrench Set (#43) Wrench Model B (#14) Supreme Tool Wrench Model C (#17) Set (#53) Master Wrench Set (#44) Wrench Model D (#19)

Hammer Model A (#22) Grand Tool Hammer Model B (#24) Deluxe Hammer Set (#48) Set (#56)

Hammer Model C (#28)

Drill Model A (#31 )

Drill Model B (#35 )

Figure 6.5 General Hardware Co. product bill of materials.

• Tools and sets of tools are sold.
• Many-to-many nature of products.

Modified Product Relation

Pro duct Num ber

Pro duct Name

Uni t Pri ce 1 1 Wr en c hMo d el A 1 2.5 0 1 4 Wr en c hMo d el B 1 3.7 5 1 7 Wr en c hMo d el C 1 1.6 2 1 9 Wr en c hMo d el D 1 5.8 0 2 2 Ha mmer Mo d el A 1 7.5 0 2 4 Ha mmer Mo d el B 1 8.0 0 2 8 Ha mmer Mo d el C 1 9.9 5 3 1 D ri ll Mo d e lA 3 1.2 5 3 5 D ri ll Mo d e lB 3 8.5 0 4 3 De l u xe Wr en c hSet 2 3.9 5 4 4 Mast er Wre nch Se t 3 5.0 0 4 8 De l u xe Ha mmer Set 5 1.0 0 5 3 Su pre me T o ol Set 1 0 0.0 0 5 6 G ra n dTo o l Set 1 0 9.9 5 PR OD UC Tre l ati o n

• Product Numbers have

been reduced to 2 digits

for simplicity.

• Every individual unit

item and every set of

tools has its own row in

the relation because

every item and set is

available for sale.

Ternary Relationships

• Involves three different

entity types.

General Hardware Co.: Ternary

Relationship

Sa les person Num ber

Sa les person Name

Com missio n Percen ta g e

Yea r Of H ire 1 3 7 B ake r 1 0 1 9 9 5 1 8 6 Ad ams 1 5 2 0 0 1 2 0 4 Dicken s 1 0 1 9 9 8 3 6 1 C arlyle 2 0 2 0 0 1 (a) SALESPERSO N relation.

Cus tomer Num ber

Cus tomer Name HQ City 0 1 2 1 Ma in St. Hard wa re New York 0 8 3 9 Ja n e’ s Stor es C h ica g o 0 9 3 3 AB C Ho me Stores L os A n g eles 1 0 4 7 Ac me Hardware Store L os A n g eles 1 5 2 5 F re d’ s T o ol Stor es Atlan ta 1 7 0 0 XYZ Stores Was hin g to n 1 8 2 6 C ity Hardware New York 2 1 9 8 Western Hardwar e New York 2 2 6 7 C e ntra l Stores New York (b) CU STO ME R r elatio n

Pro uctd Num erb

Pro uctd Name

Uni t Pri ce 1 6 3 8 Wr e 6 n c h1 .9 2 5 1 9 4 4 Ha m 0 mer 1 .5 7 0 2 1 7 6 D 5 i llr 3 .9 2 9 2 4 0 1 Saw 3 2 .2 6 5 2 6 7 2 Pl i 2 e rs 1 .5 1 0 (c) P OR UD T r eC l ati o n.

Salesperson Number

Customer Number

Product Number Date Quantity 137 0839 24013 2/21/2002 25 361 1700 16386 2/27/2002 70 137 2267 19440 3/1/2002 40 204 1047 19440 3/1/2002 15 186 0839 26722 3/12/2002 35 137 1700 16386 3/17/2002 65 361 0121 21765 3/21/2002 40 204 2267 19440 4/03/2002 30 204 0839 19440 4/17/2002 20 (d) SALES relation.

Ternary Relationship

• The primary key of the additional relation

(SALES) will be (at least) the combination of

the primary keys of the entities involved in the

relationship.

Ternary Relationship

Salesp e rso n1 3 7 C us tome r0 8 3 9

Salesp e rso n2 0 4 C us tome r1 8 2 6 (a ) Salesp e rso n s a n dcu stomers.

C us tome r0 8 3 9 Pr o d uc t 1 9 4 4 0

C us tome r1 8 2 6 Pr o d uc t 2 4 0 1 3 (b )C us to mers a n dpro d u cts.

Salesp e rso n1 3 7 Pr o d uc t 1 9 4 4 0

Salesp e rso n2 0 4 Pr o d uc t 2 4 0 1 3 (c ) Salesp e rso n s a n dpro d u ct s.

Did salesperson

137 sell product

19440 to customer

Referential Integrity

• Revolves around the circumstance of trying to

refer to data in one relation in the database,

based on values in another relation.

Referential Integrity - Record Deletion

Sa les person Num ber

Sa les person Name

Com missio n Percen ta g e

Yea r Of H ire 1 3 7 B ake r 1 0 1 9 9 5 1 8 6 Ad ams 1 5 2 0 0 1 2 0 4 Dicken s 1 0 1 9 9 8 3 6 1 C arlyle 2 0 2 0 0 1 (a) SALESPERSO N relation.

Customer

Number

Customer Name

Salesperson Number HQ City 0121 Main St. Hardware 137 New York 0839 JaneÕsStores 186 Chicago 0933 ABC Home Stores 137 Los Angeles 1047 Acme Hardware Store 137 Los Angeles 1525 Fred’s Tool Stores 361 Atlanta 1700 XYZ Stores 361 Washington 1826 City Hardware 137 New York 2198 Western Hardware 204 New York 2267 Central Stores 186 New York (b) CUSTOMER relation

♦ A problem

arises, e.g.,

because a

deleted

record, a

salesperson

record, is on

the “one side”

of a one-to-

many

relationship.