Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CMPUT 291 – SAMPLE Final Exam

Question 1

Using CREATE TABLE statements map the following ER diagram into relational tables.

Question 2

(a) Is E → B entailed by functional dependencies { A → B, ED → A, E → C }? Justify your answer.

(a) Consider a relation R (X, Y, Z) where X, Y and Z are sets of attributes. Is the decomposition of R into R1 (X,Y) and R2 (X, Z) lossless? If yes, prove it. If not, argue why (you can use a counter-example).

Question 3

a) A Hash table, regardless of the hashing function used, is always more efficient than a B+-tree for equality searches.

( ) TRUE     ( ) FALSE

b) A relation that is not in BCNF is subject to update and deletion anomalies

( ) TRUE     ( ) FALSE

c) The minimum unit that can be read from disk is a tuple.

( ) TRUE     ( ) FALSE

d) No ER diagram can be mapped directly into BCNF relations, i.e., it is always necessary to decompose the relations obtained from the mapping process.

( ) TRUE     ( ) FALSE

e) The transfer time of a page is proportional to the disk’s rotational delay.

( ) TRUE     ( ) FALSE

Question 4

(a) Consider a static hash table with H buckets, a good hashing function, and a relation R whose tuples have K unique search key values. Assume H is used to index R on K. Discuss the effects of having H >> K in terms of both equality search and storage efficiency.

(b) Disregarding storage space, discuss two conditions under which one should probably consider denormalizing a relation after it is brought into BCNF form.

Question 5

Consider the following relations, where attributes with the same name indicate a foreign key-primary key relationship.

Write the following queries in SQL:

(a) Find the names and phone numbers of all customers who have a loan in a branch that has at least $500,000 in assets.

(b) Find the names and phone numbers of all customers who have both a chequing account and a loan using EXISTS and also NOT using neither EXISTS nor IN.

(c) Find the name and the average balance of each customer who lives in Edmonton and has at least three chequing accounts regardless of their branches.

Question 6

Consider a hard disk with the following specification:

● P plates each with a double surface.

● Each plate has two dedicated R/W heads (for both plate’s surfaces).

● Average seek time: S msec/track.

● Average rotational delay: R msec.

● Transfer time: T msec/page.

● Finally, there are: 28 tracks/surface, 25 pages/track and each page fits 29 bytes.

Consider further two relations R and S. Relation R has 213 tuples, each tuple requiring 25 bytes, and relation S has 215 tuples, each tuple requiring 28 bytes.

(a) Assume that all tuples of S are tightly packed into pages but not sorted. (i) What is the maximum number of I/Os necessary to find a particular tuple in S given its primary key? (ii) What if the tuples are sorted on S’s primary key?

(b) Considering that the disk’s R/W head is at an arbitrary position, what is the average time required to read a random tuple from R?

Question 7

Each of the following curves reflect the typical cost of retrieving tuples for a relation when a range query is posed, depending on whether the relation uses a tree clustered index, a tree unclustered index or a linear scan on an unclustered file. Indicate in the graph which curve corresponds to which case.

Question 8

Consider a collection named facultyArea on a MongoDB database containing the following documents:

Write a MongoDB query that finds the total number of faculty members in each rank across all departments. Your query should look like: “[ { _id: <level>, numberOf: <NUMBER }, … ]