COMP23111 Fundamentals of Databases.pdf

COMP23111


Two hours


SECTION A AND SECTION B ARE COMPULSORY



UNIVERSITY OF MANCHESTER
SCHOOL OF COMPUTER SCIENCE




Fundamentals of Databases




Date: Wednesday 27th January 2016
Time: 09:45 - 11:45




The Paper is in THREE Sections

You must answer Section A, worth 10 marks
You must answer Section B, worth 10 marks
You must answer ONE of the two questions in Section C, worth 20 marks.

Use a SEPARATE answerbook for each QUESTION.

This is a CLOSED book examination

The use of electronic calculators is NOT permitted




[PTO]

, COMP23111
Section A

This section contains one question.
You MUST answer it using a SEPARATE answerbook.
1. This question has three parts, from 1.a to 1.c, and they may have subparts in turn.

a) Briefly explain in what sense the data definition language (DDL) and the data ma-
nipulation language (DML) in a DBMS have analogous roles by stating what is
common and what is different between them. (2 marks)

b) Assume the following (partially-specified) relational schema with three relations R,
S, and T :

R(a:int, b: int, c: str)
S(d:int, e: int, a: int)
T(d:int, g:str, h:int)

and the following database state:

R = {(5,0,’x’), (2,1,’y’), (12,1,’x’)}
S = {(101,43,2), (87,34,2), (98,24,5)}
T = {(101,’Tim’,4),(101,’Jim’,3),(87,’Jan’,18)}

Assume that the primary keys of R, S, and T are, respectively, {a}, {d} and {d, g}.
Further assume that T.d references S.d and that S.a references R.a.

i) Explain what constraint would be violated by executing the following SQL
statement: UPDATE R SET b = ’No’; on the above database state.
(1 mark)
ii) Explain what constraint would be violated by executing the following SQL
statement: DELETE FROM R WHERE a = 5; on the above database state.
(2 marks)
iii) The fact that the primary key of T includes a foreign key (viz., the primary
key of S) suggests that, in terms of entity-relationship modelling notions, T.g
is what kind of attribute? (2 marks)

c) Assume the relational schema and database state given earlier in this question, then
answer the following:

i) State in English what the following SQL statement retrieves:
SELECT s.d AS s_key, count(t.g) AS count
FROM S AS s, T AS t
WHERE s.d = t.d
GROUP BY s.d
(1 mark)
ii) Write out the result set (i.e., the set of tuples) returned by evaluating the query
just given on the database state given earlier in this question. (2 marks)


Page 2 of 9