CSC3170 Assignment
This is an individual assignment and should be
submitted by 5 pm, 24 April 2020 via Blackboard
1. Consider the following relation on car sales:
Car_Sale (Car#, Date_sold, Salesman#, Commision%, Discount_amt)
Assume that a car may be sold by multiple salesmen and hence {Car#, Salesman#} is the primary
key. Assuming the following additional dependencies:
Date_sold → Discount_amt
Salesman# → Commission%
Car# → Date_sold
(i) Based on the given primary key, determine whether this relation in 1NF, 2NF, or 3NF.
You should justify your answer.
(ii) Normalize the given relation to 2NF.
(iii) Normalize the relations in (ii) to 3NF.
2. Consider the following relations for an order processing application database at company Global-
UK.
Order (O#, Odate, Cust#, Total_amount)
Order-Item (O#, I#, Qty_ordered, Total_price, Discount%)
Assume that each item has a different discount. The Total_price refers to one item, Odate is the date
on which the order was placed, and the Total_amount is the amount of the order. Let us apply a
natural join on the relations Order-Item and Order and call the result RelationX.
(i) Write down the schema of RelationX.
(ii) Determine the primary key for RelationX.
(iii) What are the functional dependencies of RelationX. You should state clearly any
assumptions that you make. These assumptions should be reasonable assumptions.
(iv) Is RelationX in 2NF or 3NF? You should justify your answers.
3. Prove that functional dependency satisfies the formal definition of multivalued dependency.
4. Consider the relation concerning refrigerators
Ref (Model#, Year, Price, Manuf_Plant, Color)
and the following set of functional dependencies:
Model# → Manuf_Plant
Model#, Year → Price
Manuf_Plant → Color
(i) Evaluate each of the following as a candidate key for Ref, giving reasons why it can or
cannot be a candidate key: {Model#}, {Model#, Year}, {Model#, Color}.
(ii) Based on the result of (i) above, determine whether the relation Ref is in 3NF and in
BCNF. You should justify your answers.
(iii) Consider the decomposition of Ref into
R1 (Model#, Year, Price)
R2 (Model#, Manuf_Plant, Color)
Determine whether this is a lossless decomposition. You should justify your answers.
5. Consider a PARTS file with Part# as hash key, which includes records with the following Part#
values:
{2369, 3760, 4692, 4871, 5659, 1821, 1074, 7115, 1620, 2428, 3943, 4750, 6975, 4981, 9208}
The file uses 8 buckets, numbered 0 to 7. Each bucket is one disk block and holds two records.
Suppose we load these 15 records into the file in the given order using the hash function h(K) = K
mod 8. Determine the average number of block accesses for a random record retrieval on Part#.
6. Consider the records with key values in the previous question. Suppose now we load them into
an expandable hash file based on extendible hashing, now with hashing function h(K) = K mod
128.
(i) Determine the hash value of all the records.
(ii) Show the structure, clearly indicating the local and global depths, after (a) the insertion of
the 4th record, and (b) the insertion of the 6th record, assuming insertion is carried out in
the order given,