COMP9315 14s2

The University of New South Wales COMP9315 DBMS Implementation Final Exam 14s2

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:

1. select * from R where c = 'red'

2. select distinct(b) from R

3. select max(id) from R where a % 2 <> 0

4. select * from S where x ≥ 112111

5. select * from S where y > 13345

6. select * from S join R on (S.y=R.a)

7. select * from R left outer join S on (R.a=S.y)

Instructions:

• Type your answer to this question into the file called q5.txt
• Submit via:   submit q5