CS 157B Midterm 2 Revision

CS 157B Midterm 2 Revision

Prof. Sin Min LeeProf. Sin Min Lee

Q # 1 (01)Q # 1 (01)1. Characterize the difference between the following pairs

of termsa. Entity and entity classb. Relationship and relationship typec. Attribute value and attributed. Strong entity class and weak entity class

2. Create an ER model for the following enterprise: Each building in an organization has a different building

name and address. The meeting rooms in each building have their own room numbers and seating capacities. Rooms may be reserved for meetings, and each meeting must start on the hour. The hour and length of use are recorded. Each reservation is made by a group in the company. Each group has a group number and a contact phone.

1. Characterize the difference between the following pairs of terms

a. Entity and entity classb. Relationship and relationship typec. Attribute value and attributed. Strong entity class and weak entity class

2. Create an ER model for the following enterprise: Each building in an organization has a different building

name and address. The meeting rooms in each building have their own room numbers and seating capacities. Rooms may be reserved for meetings, and each meeting must start on the hour. The hour and length of use are recorded. Each reservation is made by a group in the company. Each group has a group number and a contact phone.

Q#1 (01): SolutionsQ#1 (01): Solutions

a. Entity and entity class An entity is an object and an entity class is a

collection of objects. b. Relationship and relationship type A relationship is a specific instance of two related

entities. A relationship type represents the possibility that two entities are related.

c. Attribute value and attribute An attribute is a characteristic shared by entities

of a class. An attribute value is the particular value of the attribute for a specific entity.

d. Strong entity class and weak entity class A strong entity class is one with a key and a

weak entity class has no key of its own.

a. Entity and entity class An entity is an object and an entity class is a

collection of objects. b. Relationship and relationship type A relationship is a specific instance of two related

entities. A relationship type represents the possibility that two entities are related.

c. Attribute value and attribute An attribute is a characteristic shared by entities

of a class. An attribute value is the particular value of the attribute for a specific entity.

d. Strong entity class and weak entity class A strong entity class is one with a key and a

weak entity class has no key of its own.

Q # 1 (01): SolutionsQ # 1 (01): Solutions

G roup

1

U sesIsIn

1 MM

contact

H as

R oom

capacity typenum ber

B uild ing

nam e

M eeting

leng thhour

nam edate

num ber

phone

1

M

Q #1 (02)Q #1 (02)

1. Consider the following E-R diagram and attributes

Buyer: id, name, addressProperty: id, askingPrice, salePrice,

addressStaffMember: id, lastName,

firstName, phoneBranch: id, name, address, phone

a. Write sentences to describe the roles of staff members in the diagram.

b. Write sentences to describe the roles of property in the diagram.

2. Explain the following constraints.

a. Domain constraintb. Key constraintc. Cardinality constraintd. Participation constraint

1. Consider the following E-R diagram and attributes

Buyer: id, name, addressProperty: id, askingPrice, salePrice,

addressStaffMember: id, lastName,

firstName, phoneBranch: id, name, address, phone

a. Write sentences to describe the roles of staff members in the diagram.

b. Write sentences to describe the roles of property in the diagram.

2. Explain the following constraints.

a. Domain constraintb. Key constraintc. Cardinality constraintd. Participation constraint

Branch

M

StaffMember

M

WorksAt

PropretyBuyer

Sells

Buys

Manages

M

1

M

11

1

Q#1 (02) SolutionQ#1 (02) Solution

1.a. Write sentences to describe the roles of staff members in the diagram. A staff member may sell many properties. A staff member may work at a branch. A staff member may mange a branchb. Write sentences to describe the roles of property in the diagram. A property may be bought by one buyer A property may be sold by many staff members

2.a. Domain constraint : restrictions on the allowable values of attributes -

the type and the range of values possible for each attributeb. Key constraint : unique value in an entity class-most entity classes

have one or more attributes that form a key of the classc. Cardinality constraint : the number of relationships that may exist at a

time – (to-one and to-many)d. Participation constraint : whether at least one relationship must exist

for each entity of a class.

1.a. Write sentences to describe the roles of staff members in the diagram. A staff member may sell many properties. A staff member may work at a branch. A staff member may mange a branchb. Write sentences to describe the roles of property in the diagram. A property may be bought by one buyer A property may be sold by many staff members

2.a. Domain constraint : restrictions on the allowable values of attributes -

the type and the range of values possible for each attributeb. Key constraint : unique value in an entity class-most entity classes

have one or more attributes that form a key of the classc. Cardinality constraint : the number of relationships that may exist at a

time – (to-one and to-many)d. Participation constraint : whether at least one relationship must exist

for each entity of a class.

Q #2 -01 Create a relational database schema for the following ER

model.

Q #2 -01 Create a relational database schema for the following ER

model.

Group

1

UsesIsIn

1 MM

contact

Has

Room

capacity typenumber

Building

name

Meeting

lengthhour

namedate

number

phone

1

M

address

Q#2 -01 : solutionQ#2 -01 : solution

Building (name, address) Room (number, capacity, type, buildingName) Meeting (name, date, hour, length, roomNo, byGroup) Group (number, contact, phone)

Building (name, address) Room (number, capacity, type, buildingName) Meeting (name, date, hour, length, roomNo, byGroup) Group (number, contact, phone)

Q # 2 (02)Q # 2 (02)

1. Create a relational database schema for the following EER model.

1. Create a relational database schema for the following EER model.

d

Vehicle

weight

vehicleId

capacity

numberWheels

Car

amenitiesnumberDoors

Truck

numberAxles

weightCapacity

Motorcycle

usageengineType

MOwnerHas

M

ssn

firstName

lastName

Q # 2 (02)Q # 2 (02)

2. Suppose R(A,B,C,D,E,F,G,H) is a relation. Suppose {A,B}{C,D,E,F,G,H}, C{B,E,F}, and GH.

a. Which sets of attributes are the keys of R?

b. Identify and eliminate any 2NF violations.

c. Identify and eliminate any 3NF violations.

2. Suppose R(A,B,C,D,E,F,G,H) is a relation. Suppose {A,B}{C,D,E,F,G,H}, C{B,E,F}, and GH.

a. Which sets of attributes are the keys of R?

b. Identify and eliminate any 2NF violations.

c. Identify and eliminate any 3NF violations.

Q # 2 (02): solutionQ # 2 (02): solution

1. One of the possible solutionsVehicle (vehicleId, weight, capacity, numberWheels)Car (vehicleId, numberDoors, amenities)Truck (vehicleId, weightCapacity, numberAxles)Motorcycle (vehicleId, engineType, usage)

Owner (ssn, firstName, lastName)

Has (ssn, vehicleId)

1. One of the possible solutionsVehicle (vehicleId, weight, capacity, numberWheels)Car (vehicleId, numberDoors, amenities)Truck (vehicleId, weightCapacity, numberAxles)Motorcycle (vehicleId, engineType, usage)

Owner (ssn, firstName, lastName)

Has (ssn, vehicleId)

Q # 2 (02): solutionQ # 2 (02): solution

2.a. Which sets of attributes are the keys of R?{A, B} and {A, C}b. Identify and eliminate any 2NF violations.C {E, F} is a 2NF violationThe revised schema isR9: (A, B, C, D, G, H), {A, C} is also a keyR10: (C, E, F)c. Identify and eliminate any 3NF violations.G H is a 3NF violationThe revised schema is R10,R11: (A, B, C, D, G), {A, C} is also a keyR12: (G, H)

2.a. Which sets of attributes are the keys of R?{A, B} and {A, C}b. Identify and eliminate any 2NF violations.C {E, F} is a 2NF violationThe revised schema isR9: (A, B, C, D, G, H), {A, C} is also a keyR10: (C, E, F)c. Identify and eliminate any 3NF violations.G H is a 3NF violationThe revised schema is R10,R11: (A, B, C, D, G), {A, C} is also a keyR12: (G, H)

Q# 3 (01)Q# 3 (01)1. Write SQL statements to create the following two tables (including key constraints and referential

constraints)2. Write a SQL select statement to find the names of Employees who work in ‘Research’ department.3. Write a SQL statement to insert a new department with Dnumber=6 and Dname=“sales”.

1. Write SQL statements to create the following two tables (including key constraints and referential constraints)

2. Write a SQL select statement to find the names of Employees who work in ‘Research’ department.3. Write a SQL statement to insert a new department with Dnumber=6 and Dname=“sales”.

Department DNumber DName MgrSSN

5 Research 333445555

4 Administrator 987654321

1 Headquarter 888665555

Employee SSN FName LName Sex Salary SuperSSN Dno

123456789 John Smith M 30000 333445555

5

333445555 Franklin Wong M 40000 888664444

5

999887777 Alicia Zelaya F 25000 987654321

4

987654321 Jennifer Wallace F 43000 888665555

4

666884444 Ramesh Narayn M 38000 333445555

5

453453453 Joyce English F 25000 333445555

5

987987987 Ahmad Jabbar M 25000 987654321

4

888665555 James Borg M 55000 - 1

Q # 3 (01): SolutionsQ # 3 (01): Solutions

1.Create table Department (

Dnumber int primary key,Dname varchar(20),MgrSSN int references Employee );

Create table Employee (SSN int primary key,Fname varchar(20),Lname varchar(20),Sex char(1),Salary int,SuperSSN int references Employee,Dno int references Department );

2. Select Fname, Lname from Employee, Department where

Dname=‘Research’ and Dnumber = Dno;3.Insert into Department (Dnumber, Dname) values (6, “Sales”);

1.Create table Department (

Dnumber int primary key,Dname varchar(20),MgrSSN int references Employee );

Create table Employee (SSN int primary key,Fname varchar(20),Lname varchar(20),Sex char(1),Salary int,SuperSSN int references Employee,Dno int references Department );

2. Select Fname, Lname from Employee, Department where

Dname=‘Research’ and Dnumber = Dno;3.Insert into Department (Dnumber, Dname) values (6, “Sales”);

Q # 3 (02)Q # 3 (02)1. Write a SQL select statement to find the names of all employees whose salaries are greater

than 30000.2. Write a SQL select statement to find the names of departments for which female employees

work. 3. Write a SQL statement to delete all the employees who work at Dno 4.

1. Write a SQL select statement to find the names of all employees whose salaries are greater than 30000.

2. Write a SQL select statement to find the names of departments for which female employees work.

3. Write a SQL statement to delete all the employees who work at Dno 4.

Department DNumber DName MgrSSN

5 Research 333445555

4 Administrator 987654321

1 Headquarter 888665555

Employee SSN FName LName Sex Salary SuperSSN Dno

123456789 John Smith M 30000 333445555

5

333445555 Franklin Wong M 40000 888664444

5

999887777 Alicia Zelaya F 25000 987654321

4

987654321 Jennifer Wallace F 43000 888665555

4

666884444 Ramesh Narayn M 38000 333445555

5

453453453 Joyce English F 25000 333445555

5

987987987 Ahmad Jabbar M 25000 987654321

4

888665555 James Borg M 55000 - 1

QUIZ # 3 (02): SolutionQUIZ # 3 (02): Solution

1.Select Fname, Lname From Employee Where Salary > 30000;2.Select Dname From Employee, Department Where Sex=‘F’ and Dno=Dnumber;3.Delete * From Employee Where Dno=4;

1.Select Fname, Lname From Employee Where Salary > 30000;2.Select Dname From Employee, Department Where Sex=‘F’ and Dno=Dnumber;3.Delete * From Employee Where Dno=4;

Manipulating Information with the Relational Algebra

[Ch. 6.1]

Manipulating Information with the Relational Algebra

[Ch. 6.1]

Relation is a set of tuples and that each tuple in a relation has the same number and types of attributes. Relational algebra includes :Selection OperatorsProjection OperatorsSet OperatorsJoin and product Operations

Relation is a set of tuples and that each tuple in a relation has the same number and types of attributes. Relational algebra includes :Selection OperatorsProjection OperatorsSet OperatorsJoin and product Operations

Selection Operators ()Selection Operators ()

Reduce the number of tuples in a set by selecting those that satisfy some criteria.

Example : lastName = ‘Doe’ (Customer) [ Select from Customer where

lastName = ‘Doe’ ]

CustomerCustomer

Reduce the number of tuples in a set by selecting those that satisfy some criteria.

Example : lastName = ‘Doe’ (Customer) [ Select from Customer where

lastName = ‘Doe’ ]

CustomerCustomerAccoun

t IdLast

NameFirst Name

Street City State

Zip Code

Balance

101 Block Jane 345 Randolph Circle Apopka FL 30458- $0.00

102 Hamilton Cherry 3230 Dade St. Dade City FL 30555- $3.00

103 Harrison Katherine 103 Landis Hall Bratt FL 30457- $31.00

104 Breaux Carroll 76 Main St. Apopka FL 30458- $35.00

106 Morehouse

Anita 9501 Lafayette St. Houma LA 44099- $0.00

111 Doe Jane 123 Main St. Apopka FL 30458- $0.00

201 Greaves Joseph 14325 N. Bankside St. Godfrey IL 43580- $0.00

444 Doe Jane Cawthon Dorm, room 142

Tallahassee

FL 32306- $10.55

Projection Operators ()Projection Operators ()

Reduce the size of each tuple in a set by eliminating specific attributes.

Example : lastName, firstNAme (Customer) [ project customer onto (lastName,

firstName) ]

CustomerCustomer

Reduce the size of each tuple in a set by eliminating specific attributes.

Example : lastName, firstNAme (Customer) [ project customer onto (lastName,

firstName) ]

CustomerCustomer

Account Id

Last Name

First Name

Street City State

Zip Code

Balance

101 Block Jane 345 Randolph Circle Apopka FL 30458- $0.00

102 Hamilton Cherry 3230 Dade St. Dade City FL 30555- $3.00

103 Harrison Katherine 103 Landis Hall Bratt FL 30457- $31.00

104 Breaux Carroll 76 Main St. Apopka FL 30458- $35.00

106 Morehouse

Anita 9501 Lafayette St. Houma LA 44099- $0.00

111 Doe Jane 123 Main St. Apopka FL 30458- $0.00

201 Greaves Joseph 14325 N. Bankside St. Godfrey IL 43580- $0.00

444 Doe Jane Cawthon Dorm, room 142

Tallahassee

FL 32306- $10.55

Set Operators ( -)Set Operators ( -)

Manipulate two similar sets of tuples by combining or comparing.

Example : Rental PreviousRental Rental PreviousRental

Manipulate two similar sets of tuples by combining or comparing.

Example : Rental PreviousRental Rental PreviousRental

accountId

videoID dateRented

dateDue

cost

103 101 1/3/99 1/4/99 $1.59

101 113 2/22/99 2/25/99 $3.00

101 114 2/2/99 2/25/99 $3.00

103 123 12/1/98 12/31/98

$10.99

101 145 2/14/99 2/16/99 $1.99

101 90987 1/1/99 1/8/99 $2.99

101 99787 1/1/99 1/4/99 $3.49

accountId

videoId

dateRented

dateReturned

cost

101 101 12/9/98 12/10/98 $2.49

101 112 1/13/98 1/4/98 $1.99

101 113 1/15/99 1/15/99 $0.99

102 113 12/1/98 12/3/98 $2.49

111 101 12/4/98 12/6/98 $2.49

111 99787 1/1/99 1/4/99 $3.95

201 113 129/98 12/14/98 $3.99

201 77564 1/14/99 1/24/99 $3.35

Set Operators ( -) ...con’t

Set Operators ( -) ...con’t

• The union of two relations is a relation that contains the set of each tuple that is in at least one of the input relations.

Partial result of the Rental PreviousRentalaccountId videoId dateRented dateDue cost

101 90987 1/1/99 1/8/99 $2.99

101 99787 1/1/99 1/4/99 $3.49

103 101 1/3/99 1/4/99 $1.59

103 123 12/1/98 12/31/98 $10.99

111 101 12/4/98 12/6/98 $2.49

201 77564 1/14/99 1/24/99 $3.35

Set Operators ( -) ...con’t

Set Operators ( -) ...con’t

The intersection of two relations is the set of all tuples that occur in both input relations.

The intersection of the relations Rental PreviousRental in the previous example will return an empty set.

Another example would be the intersection between the video IDs of the two tables.

videoId (Rental) videoId (PrevioutsRental) = Videotapes that are currently rented as well as those that have been rented before.

The set of all videotapes that have been rented previously but are not currently rented is expressed as follows: videoId (PreviousRental) - videoId (Rental)

The intersection of two relations is the set of all tuples that occur in both input relations.

The intersection of the relations Rental PreviousRental in the previous example will return an empty set.

Another example would be the intersection between the video IDs of the two tables.

videoId (Rental) videoId (PrevioutsRental) = Videotapes that are currently rented as well as those that have been rented before.

The set of all videotapes that have been rented previously but are not currently rented is expressed as follows: videoId (PreviousRental) - videoId (Rental)

Join and Product Operations ()

Join and Product Operations ()

Increase the size of each tuple by adding attributes The Cartesian product produces a tuple of the new

realtion for each combination of one tuple from the left operand and one tuple from the right operand. Example : Employee TimeCard

Employee TimeCard

Increase the size of each tuple by adding attributes The Cartesian product produces a tuple of the new

realtion for each combination of one tuple from the left operand and one tuple from the right operand. Example : Employee TimeCard

Employee TimeCardssn lastNam

efirstNam

e

145-09-0967

Uno Jane

245-11-4554

Toulouse Jennifer

376-77-0099

Threat Ayisha

479-98-0098

Fortune Bruce

588-99-0093

Fivozinsky

Bruce

ssn date startTime

endTime

storeId

paid

145-09-0967

01/14/99

8:15 12:00 3 yes

245-11-4554

01/14/99

8:15 12:00 3 yes

376-77-0099

02/23/99

14:00 22:00 5 yes

145-09-0967

01/16/99

8:15 12:00 3 yes

376-77-0099

01/03/99

10:00 14:00 5 yes

376-77-0099

01/03/99

15:00 19:00 5 yes

Join and Product Operations () ...con’t

Join and Product Operations () ...con’t

The result of this operation has 30 tuples because there are 5 Employee and 6 TimeCard.

Partial result of Cartesian product Employee TimeCard

The result of this operation has 30 tuples because there are 5 Employee and 6 TimeCard.

Partial result of Cartesian product Employee TimeCard

Employee.ssn

lastName

firstName

TimeCard.ssn

Date startTime

endTime

storeId

paid

145-09-0967

Uno Jane 145-09-0967

01/14/99

8:15 12:00 3 no

245-11-4554

Toulouse Jie 245-11-4554

01/14/99

8:15 12:00 3 no

145-09-0967

Uno Jane 376-77-0099

02/23/99

14:00 22:00 5 no

245-11-4554

Toulouse Jie 145-09-967 01/14/99

8:15 12:00 3 no

Join and Product Operations () ...con’t

Join and Product Operations () ...con’t

A selection of those tuples where Employee.ssn equals TimeCard.ssn can be expressed by :

Employee.ssn = TimeCard.ssn (Employee TimeCard) This type of product is called a join. The join operation

puts together related objects from two relations. A Natural Join however is defined so that the shared

attribute appears only once in the output table. Ref. textbook Table 6.6 [natural join] vs Table 6.7 [join]

A selection of those tuples where Employee.ssn equals TimeCard.ssn can be expressed by :

Employee.ssn = TimeCard.ssn (Employee TimeCard) This type of product is called a join. The join operation

puts together related objects from two relations. A Natural Join however is defined so that the shared

attribute appears only once in the output table. Ref. textbook Table 6.6 [natural join] vs Table 6.7 [join]

R = ( A, B, C )F = { A B, B C }

F+ = { A A, B B, C C,AB AB, BC BC, AC AC, ABC ABC,AB A, AB B, BC B, BC C, AC A, AC C,ABC AB, ABC BC, ABC AC,ABC A, ABC B, ABC C,A B, … (1) ( given )B C, … (2) ( given )A C, … (3) ( transitivity on (1) and (2) )AC BC, … (4) ( augmentation on (1) )

AC B, … (5) ( decomposition on (4) )

A AB, … (6) ( augmentation on (1) )

AB AC, AB C, B BC,

A AC, AB BC, AB ABC, AC ABC, A BC, A ABC }

R = ( A, B, C )F = { A B, B C }

F+ = { A A, B B, C C,AB AB, BC BC, AC AC, ABC ABC,AB A, AB B, BC B, BC C, AC A, AC C,ABC AB, ABC BC, ABC AC,ABC A, ABC B, ABC C,A B, … (1) ( given )B C, … (2) ( given )A C, … (3) ( transitivity on (1) and (2) )AC BC, … (4) ( augmentation on (1) )

AC B, … (5) ( decomposition on (4) )

A AB, … (6) ( augmentation on (1) )

AB AC, AB C, B BC,

A AC, AB BC, AB ABC, AC ABC, A BC, A ABC }

Using reflexivity, wecan generate all trivial dependencies

Example:

F = { A B, B C }

A+ = ABC

B+ = BC

C+ = C

AB+ = ABC

R = ( A, B, C, G, H, I )F = { A B, A C, CG H, CG I, B H }

To compute AG+

closure = AGclosure = ABG ( A B )closure = ABCG ( A C )closure = ABCGH ( CG H )closure = ABCGHI ( CG I )

Is AG a candidate key?

AG R

A+ R ?

G+ R ?

In general, suppose X A violates BCNF, then one of the following holdsX is a subset of some key K: we

store ( X, A ) pairs redundantly.X is not a subset of any key:

there is a chain K X A ( transitive dependency )

In general, suppose X A violates BCNF, then one of the following holdsX is a subset of some key K: we

store ( X, A ) pairs redundantly.X is not a subset of any key:

there is a chain K X A ( transitive dependency )

Third Normal FormThird Normal Form

The definition of 3NF is similar to that of BCNF, with the only difference being the third condition.

Recall that a key for a relation is a minimal set of attributes that uniquely determines all other attributes. A must be part of a key (any key, if there are several). It is not enough for A to be part of a superkey, because this

condition is satisfied by every attribute.

The definition of 3NF is similar to that of BCNF, with the only difference being the third condition.

Recall that a key for a relation is a minimal set of attributes that uniquely determines all other attributes. A must be part of a key (any key, if there are several). It is not enough for A to be part of a superkey, because this

condition is satisfied by every attribute.

A relation R is in 3NF if, for all X A that holds over R A X ( i.e., X A is a trivial FD ), or X is a superkey, or A is part of some key for R

If R is in BCNF,obviously it is in3NF.

Suppose that a dependency X A causes a violation of 3NF. There are two cases:X is a proper subset of some key K.

Such a dependency is sometimes called a partial dependency. In this case, we store (X,A) pairs redundantly.

X is not a proper subset of any key. Such a dependency is sometimes called a transitive dependency, because it means we have a chain of dependencies K XA.

Suppose that a dependency X A causes a violation of 3NF. There are two cases:X is a proper subset of some key K.

Such a dependency is sometimes called a partial dependency. In this case, we store (X,A) pairs redundantly.

X is not a proper subset of any key. Such a dependency is sometimes called a transitive dependency, because it means we have a chain of dependencies K XA.

Key Attributes X Attributes A

Key Attributes AAttributes X

Key Attributes A Attributes X

Partial Dependencies

Transitive Dependencies

A not in a key

A not in a key

A in a key

Motivation of 3NFBy making an exception for certain dependencies

involving key attributes, we can ensure that every relation schema can be decomposed into a collection of 3NF relations using only decompositions.

Such a guarantee does not exist for BCNF relations.It weaken the BCNF requirements just enough to

make this guarantee possible.Unlike BCNF, some redundancy is possible

with 3NF.The problems associate with partial and transitive

dependencies persist if there is a nontrivial dependency XA and X is not a superkey, even if the relation is in 3NF because A is part of a key.

Motivation of 3NFBy making an exception for certain dependencies

involving key attributes, we can ensure that every relation schema can be decomposed into a collection of 3NF relations using only decompositions.

Such a guarantee does not exist for BCNF relations.It weaken the BCNF requirements just enough to

make this guarantee possible.Unlike BCNF, some redundancy is possible

with 3NF.The problems associate with partial and transitive

dependencies persist if there is a nontrivial dependency XA and X is not a superkey, even if the relation is in 3NF because A is part of a key.

Reserves

Assume: sid cardno (a sailor uses a unique credit card to pay for reservations).

Reserves is not in 3NF sid is not a key and cardno is not part of a key In fact, (sid, bid, day) is the only key. (sid, cardno) pairs are redundantly.

Reserves

Assume: sid cardno, and cardno sid (we know that credit cards also uniquely identify the owner).

Reserves is in 3NF (cardno, sid, bid) is also a key for Reserves. sid cardno does not violate 3NF.

DecompositionDecomposition

Decomposition is a tool that allows us to eliminate redundancy.

It is important to check that a decomposition does not introduce new problems.A decomposition allows us to recover

the original relation?Can we check integrity constraints

efficiently?

Decomposition is a tool that allows us to eliminate redundancy.

It is important to check that a decomposition does not introduce new problems.A decomposition allows us to recover

the original relation?Can we check integrity constraints

efficiently?

A set of relation schemas { R1, R2, …, Rn }, with n 2 is a

decomposition of R if R1 R2 … Rn = R

sidsidSupply statusstatus citycity part_idpart_id qtyqty

Supplier

SP

sidsid statusstatus citycity

sidsid part_idpart_id qtyqty

and

Problems with decomposition1. Some queries become more expensive.2. Given instances of the decomposed

relations, we may not be able to reconstruct the corresponding instance of the original relation – information loss.

3. Checking some dependencies may require joining the instances of the decomposed relations.

Lossless Join DecompositionLossless Join

Decomposition

The relation schemas { R1, R2, …, Rn } is a lossless-join decomposition of R if:

for all possible relations r on schema R,r = R1( r ) R2( r ) …

Rn( r )

The relation schemas { R1, R2, …, Rn } is a lossless-join decomposition of R if:

for all possible relations r on schema R,r = R1( r ) R2( r ) …

Rn( r )

Example: a lossless join decomposition

sidsid snamesname majormajor

IN sidsid snamesname

IM sidsid majormajorStudent

Student IN

IM‘Student’ can be recovered by joining the instances of IN and IM

Example: a non-lossless join decomposition

sidsid snamesname majormajor

IN

IMStudent

Student IN

IM

sidsid majormajor

majormajorsnamesname

Student = IN IM????

IN IM

IN IM

The instance of ‘Student’ cannot be recovered by joining the instances of IM and NM. Therefore, such a decomposition is not a lossless join decomposition.

Student

R - a relation schemaF - set of functional dependencies on RThe decomposition of R into relations with attribute

setsR1, R2 is a lossless-join decomposition iff

( R1 R2 ) R1 F +

OR( R1 R2 ) R2 F +

Theorem:

i.e., R1 R2 is a superkey for R1 or R2.

(the attributes common to R1 and R2 must contain a key for

either R1 or R2 ).

ExampleR = ( A, B, C )F = { A B }R = { A, B } + { A, C } is a lossless

join decompositionR = { A, B } + { B, C } is not a

lossless join decomposition

ExampleR = ( A, B, C )F = { A B }R = { A, B } + { A, C } is a lossless

join decompositionR = { A, B } + { B, C } is not a

lossless join decomposition

N = 5N = 5

R(AR(A11, A, A22, A, A33, A, A44, A, A55))M=3M=3

R1(AR1(A11, A, A33))

R2(AR2(A22, A, A44))

R3(AR3(A11, A, A22, A, A55))

# of FD# of FDP=5P=5

FD1, AFD1, A1 1 -> A-> A22

FD2, AFD2, A22, A, A3 3 -> A-> A44

FD3, AFD3, A5 5 ->A->A11

FD4, AFD4, A22, A, A44 -> A -> A11, A, A33

FD5, AFD5, A3 3 -> A-> A22, A, A44

N = 5N = 5

R(AR(A11, A, A22, A, A33, A, A44, A, A55))M=3M=3

R1(AR1(A11, A, A33))

R2(AR2(A22, A, A44))

R3(AR3(A11, A, A22, A, A55))

# of FD# of FDP=5P=5

FD1, AFD1, A1 1 -> A-> A22

FD2, AFD2, A22, A, A3 3 -> A-> A44

FD3, AFD3, A5 5 ->A->A11

FD4, AFD4, A22, A, A44 -> A -> A11, A, A33

FD5, AFD5, A3 3 -> A-> A22, A, A44

S-matrix 5x3-matrixS-matrix 5x3-matrix

AA11 A A22 A A33 A A44 A A55

R1R1 a1a1 b(1,2) b(1,2) a3 a3 b(1,4) b(1,4) b(1,5) b(1,5)

R2R2 b(2,1) a2b(2,1) a2 b(2,3) a4 b(2,3) a4 b(2,5) b(2,5)

R3R3 a1a1 a2 a2 b(3,3) b(3,4) b(3,3) b(3,4) a5 a5

S-matrix 5x3-matrixS-matrix 5x3-matrix

AA11 A A22 A A33 A A44 A A55

R1R1 a1a1 b(1,2) b(1,2) a3 a3 b(1,4) b(1,4) b(1,5) b(1,5)

R2R2 b(2,1) a2b(2,1) a2 b(2,3) a4 b(2,3) a4 b(2,5) b(2,5)

R3R3 a1a1 a2 a2 b(3,3) b(3,4) b(3,3) b(3,4) a5 a5

FD1. AFD1. A11->A->A22

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD2 AFD2 A22,A,A33 -> A -> A44

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD1. AFD1. A11->A->A22

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD2 AFD2 A22,A,A33 -> A -> A44

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD3 AFD3 A55 -> A -> A11

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD4 AFD4 A22,A,A44 -> A -> A11,A,A33

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD3 AFD3 A55 -> A -> A11

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD4 AFD4 A22,A,A44 -> A -> A11,A,A33

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD5 AFD5 A33 -> A -> A22, A, A44

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

// check if any single row has all “a” value// check if any single row has all “a” value

// if not it is not a good decomposition// if not it is not a good decomposition

FD5 AFD5 A33 -> A -> A22, A, A44

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

// check if any single row has all “a” value// check if any single row has all “a” value

// if not it is not a good decomposition// if not it is not a good decomposition

FD1, AFD1, A11 -> A -> A22

FD2, AFD2, A11, A, A33 -> A -> A44, A, A55

FD3, AFD3, A55 -> A -> A11, A, A33

FD4, AFD4, A22, A, A44 -> A -> A11, A, A33

FD5, AFD5, A33 -> A -> A22, A, A44

FD1. A1->A2FD1. A1->A2

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1) a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD1, AFD1, A11 -> A -> A22

FD2, AFD2, A11, A, A33 -> A -> A44, A, A55

FD3, AFD3, A55 -> A -> A11, A, A33

FD4, AFD4, A22, A, A44 -> A -> A11, A, A33

FD5, AFD5, A33 -> A -> A22, A, A44

FD1. A1->A2FD1. A1->A2

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1) a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD2 AFD2 A11, A, A33 -> A -> A44, A, A55

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD3 AFD3 A55 -> A -> A11, A, A33

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD2 AFD2 A11, A, A33 -> A -> A44, A, A55

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD3 AFD3 A55 -> A -> A11, A, A33

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD4 AFD4 A22, A, A44 -> A -> A11, A, A33

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD5 AFD5 A33 -> A -> A22, A, A44

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD4 AFD4 A22, A, A44 -> A -> A11, A, A33

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

FD5 AFD5 A33 -> A -> A22, A, A44

a1a1 a2a2 a3a3 b(1,4)b(1,4) b(1,5)b(1,5)

b(2,1)b(2,1)a2a2 b(2,3) b(2,3) a4a4 b(3,5)b(3,5)

a1a1 a2a2 b(3,3)b(3,3) b(3,4) b(3,4) a5a5

R ( A1 , A2 , A3 , A4 , A5 )

S-Matrix 5x3Because m = 3 and P = 5

A1 A2 A3 A4 A5

R1 a1 b(1,2) a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

# of FDP = 5

FD 1 A1 A2 FD 2 A1, A3 A4, A5 FD 3 A5 A1FD 4 A2, A4 A1, A3FD 5 A3 A2, A4

n = 5 R( A1 , A2 , A3 , A4 , A5 )m = 3 R1 ( A1 , A3 ) R2 ( A2 , A4 ) R3 ( A1 , A2 , A5 )

In Class Examples 02In Class Examples 02

FD1. A1 A2A1 A2 A3 A4

A5

R1 a1 a2 a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

FD1. A1 A2A1 A2 A3 A4

A5

R1 a1 a2 a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

FD2. A1 , A3 A4, A5A1 A2 A3 A4

A5

R1 a1 a2 a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

FD3. A5 A1A1 A2 A3 A4

A5

R1 a1 a2 a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

FD4. A2 , A4 A1 , A3A1 A2 A3 A4

A5

R1 a1 a2 a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

FD5. A3 A2 , A4A1 A2 A3 A4 A5

R1 a1 a2 a3 b(1,4) b(1,5)R2 b(2,1) a2 b(2,3) a4 b(2,5) R3 a1 a2 b(3,3) b(3,4) a5

NormalizationNormalizationConsider algorithms for converting

relations to BCNF or 3NF. If a relation schema is not in BCNF

it is possible to obtain a lossless-join decomposition into a collection of BCNF relation schemas.

Dependency-preserving is not guaranteed.3NF

There is always a dependency-preserving, lossless-join decomposition into a collection of 3NF relation schemas.

Consider algorithms for converting relations to BCNF or 3NF.

If a relation schema is not in BCNFit is possible to obtain a lossless-join

decomposition into a collection of BCNF relation schemas.

Dependency-preserving is not guaranteed.3NF

There is always a dependency-preserving, lossless-join decomposition into a collection of 3NF relation schemas.

BCNF DecompositionBCNF Decomposition

It is a lossless join decomposition. But not necessary dependency preserving

It is a lossless join decomposition. But not necessary dependency preserving

Suppose R is not in BCNF, A is an attribute, and X A is a FD that violates the BCNF condition.

1. Remove A from R2. Decompose R into XA and R-A3. Repeat this process until all the relations become

BCNF

3NF Synthesis Algorithm3NF Synthesis Algorithm

Note: result is lossless-join and dependency preservingNote: result is lossless-join and dependency preserving

Find a canonical cover Fc for F ;

result = ;for each in Fc do

if no schema in result contains then add schema to result;

if no schema in result contains a candidate key for Rthen begin

choose any candidate key for R; add schema to the result

end

Design GoalsDesign Goals

Goal for a relational database design is:BCNFlossless joinDependency preservation

If we cannot achieve this, we accept:3NFlossless joinDependency preservation

Goal for a relational database design is:BCNFlossless joinDependency preservation

If we cannot achieve this, we accept:3NFlossless joinDependency preservation