02/05/2020
COMP9315 14s2
COMP9315 14s2 Final Exam
The University of New South Wales
COMP9315 DBMS Implementation Final Exam 14s2
[Instructions] [Notes] [PostgreSQL] [C] [Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8]
DBMS Implementation
Question 5 (7 marks)
Consider the following relational schema:
create type colour enum {‘red’,’green’,’blue’};
create table R (
a integer primary key,
b char(2) check (b in (‘AA’,’BB’)),
c colour
);
create table S (
x integer primary key,
y integer not null references R(a),
z colour
);
and some statistics on the tables
Attr
#Values
MinVal
MaxVal
Distribution
R.a
1000
12345
13345
primary key
R.b
2
‘AA’
‘BB’
‘AA’:50%, ‘BB’:50%
R.c
3
‘red’
‘blue’
‘red’:50%, ‘green’:30%, ‘blue’:20%
S.x
2000
111111
113111
primary key
S.y
500
12345
13345
uniform
S.z
3
‘red’
‘blue’
red’:33%, ‘green’:34%, ‘blue’:33%
Note that #Values refers to the number of distinct values for an attribute. If not specified in the above table, you may assume that values for an attribute are distributed uniformly across the underlying domain.
Using the above information, determine the likely number of results for each of the following queries:
a. select * from R where c = ‘red’
b. select distinct(b) from R
c. select max(id) from R where a % 2 <> 0 d. select * from S where x ¡Ý 112111
e. select * from S where y > 13345
f. select * from S join R on (S.y=R.a)
g. select * from R left outer join S on (R.a=S.y)
Instructions:
https://www.cse.unsw.edu.au/~cs9315/19T2/past-exams/14s2/Q05.html 1/2
02/05/2020 COMP9315 14s2 Final Exam
Type your answer to this question into the file called q5.txt Submit via: submit q5
End of Question
Powered by TCPDF (www.tcpdf.org)
https://www.cse.unsw.edu.au/~cs9315/19T2/past-exams/14s2/Q05.html 2/2