A27227 No calculator allowed in this examination

School of Computer Science
Fourth Year Undergraduate/Postgraduate
21923
Fundamentals: Databases
Main Summer Examinations 2019
Time allowed: 1:30
[Answer all questions]
– 1 – Turn Over
No calculator
Note
Answer ALL questions. Each question will be marked out of 20. The paper will be marked
out of 60, which will be rescaled to a mark out of 100.
Question 1 SQL Queries
Consider the following schemas for a database used by a share price information system.
Table shares
ISIN char(12)
symbol varchar(6)
name varchar(25)
issuedate date
Table prices
symbol varchar(6)
date date
price integer
volume integer
Stock market shares for companies can be sold and purchased by investors. Each stock
is identied by a trading symbol on the market, but internally the shares of that stock
have a unique id (ISIN). Occasionally the shares of a stock are replaced by new types of
shares with dierent value, even though they continue to trade under the same trading
symbol. (This occurs when a company \splits" its stocks.) The table shares contains all
the shares with ISIN's that have ever been traded on the market. The date on which a
particular ISIN share is issued is recorded in the table.
Share prices that are downloaded from servers contain, for each stock, its last traded
price (the \closing price") on a particular day, and the volume of shares traded on that
day. (The prices are expressed in pence. So they are just integers.) Assume that the
table prices contains all the prices for shares traded on the stock exchange during the year
2018.
Assume that every stock traded on the market has a corresponding share listed in the
shares table that is valid for that day (i.e., issued at some time prior to that date).
Dene the following queries in SQL. (You can list the stocks just by their trading
symbols.)
(a) Find the total number of dierent stocks which were traded during the year 2018.
[4 marks]
(b) List all the stocks traded during 2018, along with their average daily closing prices.
[4 marks]
(c) List all the stocks that have changed their ISIN id's at least once during the year
2018. [6 marks]
(d) List all the stocks that were traded during the year along with their maximum closing
prices and the number of days during which they had that closing price. [6 marks]
{ 2 { Turn OverA27227
No calculator
Question 2 Relational algebra and functional dependencies
(a) Using the tables given in Question 1, write a relational algebra expression for the
following problem: Find the ISIN's of stocks that traded over 10,000 shares in any
single day in 2018. [5 marks]
(b) Using the tables given in Question 1, write a relational algebra expression for the
following problem: Find the ISIN's of stocks that traded over 10,000 shares everyday
in 2018. [5 marks]
(c) Dene precisely when a decomposition of a schema for a table is said to be lossless.
[4 marks]
(d) Consider a schema T (A;B; C;D) with the functional dependencies
A ! B; B;C ! D; D;E ! A
Use the closure algorithm to nd all the attributes that are functionally dependent
on A;C.
Use this fact to suggest a lossless decomposition of the schema T . [6 marks]
{ 3 { Turn OverA27227
No calculator
Question 3 ER modelling
A town library has the following description for its business processes:
The library holds a number of books, some with multiple copies. Borrowers
can register with the library as members and borrow up to 4 books at any given
time, for 4 weeks each. The borrowers can renew books by telephone, again
for a 4 week period. If a member needs a book that is currently issued, they
can place a reservation for it and they will be informed when it is returned.
A copy of the book is recalled from the earliest member that has borrowed a
copy. If a member reports a book lost, they are charged the price of the book.
You are asked to develop a database design that supports these processes. The database
needs to record the current activity only. No historical data is required.
(a) Develop an entity-relation diagram for the required database.
The best solution involves just three entities: member, book and copy.
Make sure to annotate weak entities, if any, and the relationships that need to
be recorded. Mark the key attributes of the entities/relationships, but not routine
attributes like names etc. [5 marks]
(b) Annotate the diagram with multiplicities. [5 marks]
(c) Decide the tables that should be stored in the database and write schemas for them.
You should suitably notate the primary keys and the possibly null elds.
Mention how you are handling the relationships in your schemas. [6 marks]
(d) Translate two of the schemas into CREATE TABLE statements of SQL.
If you have a weak entity among your tables, do this for that entity along with its
parent entity. Otherwise, if you have a relationship in your tables, do this for the
relationship along with one of the entities it relates. If neither, you can do it for any
two tables. [4 marks]
{ 4 { End of PaperA27227
This page intentionally left blank.
{ 5 {A27227

Do not complete the attendance slip, fill in the
front of the answer book or turn over the
question paper until you are told to do so



Important Reminders

• Coats/outwear should be placed in the designated area.

• Unauthorised materials (e.g. notes or Tippex) must be placed in the
designated area.

• Check that you do not have any unauthorised materials with you
(e.g. in your pockets, pencil case).

• Mobile phones and smart watches must be switched off and
placed in the designated area or under your desk. They must not
be left on your person or in your pockets.

• You are not permitted to use a mobile phone as a clock. If you have
difficulty seeing a clock, please alert an Invigilator.

• You are not permitted to have writing on your hand, arm or other
body part.

• Check that you do not have writing on your hand, arm or other body
part – if you do, you must inform an Invigilator immediately

• Alert an Invigilator immediately if you find any unauthorised item
upon you during the examination.

Any students found with non-permitted items upon their person
during the examination, or who fail to comply with Examination
rules may be subject to Student Conduct procedures.

A27227 Fundamentals: Databases