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
