CSL 451 Introduction to Database Systems Quiz 7, 18-3-2015 Name: Roll Number: 1. Given the database schema R(A, B, C) and a relation r on the schema R, write an SQL query to test whether the functional dependency A → C holds on relation r. ( 2 points) select A from R group by A having count(distinct C)>1 If the query returns non-null output, then the dependency does not hold 2. Given the relation R(A, B, C, D) with all the attributes being atomic and the following set of functional dependencies that hold on R BC → D 1 BC → A 2 D→B3 A→C4 a. Derive all the candidate keys of R (2 points) Clearly not atomic attribute can be a candidate key BC → ABCD 5, augment 4 with B, and use 5 AB → ABCD 6, augment 3 with C, and use 5 CD → ABCD 7, augment 4 with D, and use 7 AD → ABCD b. Explain all normal forms among 1NF, 3NF, and BCNF that R satisfies. (2 points) 1NF – All attributes are atomic 3NF – 1 and 2 - BC is a superkey, 3 – A is part of a candidate key, 4 – C is part of a candidate key BCNF – NO, 3 – D is not a superkey or 4 – A is not a superkey. c. Explain if R can be decomposed into a dependency preserving BCNF. (3 points) D → B violates BNCF for ABCD. So decompose ABCD into BD and ACD A → C violates BCNF for ACD. So decompose ACD into AC, and AD So the final BCNF decomposition is – BD, AC, and AD. However the dependency BC → D and BC → A is not preserved in this BCNF decomposition. 3. Suppose a relation R has only atomic candidate keys. Prove that if R is in 3NF, then R will also be in BCNF. (3 points) Assume R is in 3NF, but not in BCNF, then there must be a dependency 𝛼 → 𝛽 that holds on R with 𝛼 not being a superkey. Since R is in 3NF, there must be an attribute A ∈ 𝛽 − 𝛼 such that A is part of a candidate key for R. However we know that R has only atomic candidate keys. Hence A is itself a candidate key. So if 𝛼 → 𝛽 and 𝛽 contains A – a candidate key of R, then 𝛼 should be a superkey. Thus R should be in BCNF. 4. Sort the following increasing order of speed of access - main memory, cache, optical disk, magnetic disk, flash memory, and magnetic tapes. (1 point) cache < main memory < flash memory < magnetic disk < optical disk < magnetic tapes 5. Define any two metrics to measure the performance of storage devices (apart from capacity) (2 points) Access time – time from when a read or write is request is issued to when data transfer begins Average seek time – average time for repositioning an arm to access a data from set of random requests Data transfer rate – rate at which data can be retrieved from or stored to the disk Mean time to failure – amount of time that, on average, the disk runs continuously without any failure Average latency time – average time spent waiting for the sector to be accessed to appear under the head
© Copyright 2024