Judgments
COMP23111 Fundamentals of Databases.pdf
- Course
- Computer Science
- Institution
- Computer Science
COMP23111 Fundamentals of D
[Show more]
Seller
Follow
Content preview
COMP23111Two 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
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through EFT, credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying this summary from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller Studyabroad. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy this summary for R132,22. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
76669 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy summaries for 14 years now