cs 157b midterm 2 revision prof. sin min lee. q #1 (01) 1. characterize the difference between the...
Post on 22-Dec-2015
213 Views
Preview:
TRANSCRIPT
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
top related