COMP9315 Sample Exam
The University of New South Wales
COMP9315 DBMS Implementation
Sample Exam
DBMS Implementation
[Instructions] [Notes] [PostgreSQL] [C]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8]
Question 6 (8 marks)
Consider a relation defined as follows:
create type Colour as enum
(‘white’,’red’,’orange’,’yellow’,’green’,’blue’,’indigo’,’violet’);
create table R (
a integer not null check (a between 0 and 15),
b Colour not null,
c integer not null check (c between 1 and 100)
);
a. If the table contains 1000 tuples, and assuming uniform distribution of attribute values, indicate how many tuples are likely to appear in the result of each of the following queries:
i. select * from R where a = 5;
ii. select * from R where b = ‘green’;
iii. select * from R where c > 80;
iv. select * from R where a is null;
b. Consider that we wish to design a multi-attribute hashing index for the table, using the following properties:
◦ the file for the relation has b = 128 pages
◦ query distribution:
▪ Q1: select * from R where a = k, PQ1 = 0.3
▪ Q2: select * from R where b = j, PQ2 = 0.2
▪ Q3: select * from R where a = k and b = j, PQ3 = 0.3
▪ Q4: select * from R where b = j and c = m, PQ4 = 0.2
◦ where k, j and m are constants of the appropriate type
c. Determine the following:
i. the number of hash bits d required (you can assume that the file is not growing)
ii. the maximum number of hash bits that is useful to allocate to each attribute
iii. an allocation of hash bits (i.e. values for da, db, dc) giving minimum average query cost
iv. the weighted average cost of queries under this bit-allocation
Show all working.
Instructions:
• Type your answer to this question into the file called q6.txt
• Submit via: give cs9315 sample_q6 q6.txt
or via: Webcms3 > exams > Sample Exam > Submit Q6 > Make Submission
End of Question