1. In ER modelling, connection traps are to be avoided. Identify two types of connection trap and explain why they are undesirable.
Generate an E-R diagram for the situation in the following scenario. Clearly state any assumptions that you are making.
"A publishing company produces scientific books on various subjects. The books are written by authors who specialize in one particular subject. The company employs editors who, not necessarily being specialists in a particular area, each take sole responsibility for editing one or more publications. A publication covers essentially one of the specialist subjects and is normally written by a single author. When writing a particular book, each author works with an editor, but may submit another work for publication to be supervised by other editors. To improve their competitiveness, the company tries to employ a variety of authors, more than one author being a specialist in a particular subject."
3. In a book store, data on books and their authors is held in a small database with two tables, Book and Author. The schema of each table identifies the corresponding attributes where the primary key is underlined.
Book(ISBN, BookTitle, Price, PubDate, AuthorId)
Author(AuthorId, AuthorName, AuthorAddress)
a) Write an SQL statement to create the table for the Book schema only. Choose appropriate data types for the attributes of Book.
b) It was realized after the design of the database was completed, that an extra attribute was required for the Author table. Write an SQL statement to add the attribute PhoneNo to the schema of the Author table
c) Produce an SQL statement to define an index for the table Book in order to speed up access to the data.
4. A relational database includes two tables on publishers and the titles they publish. The schemas of the tables are as follows:
Publisher(pubId, pubCity, pubName)
Title(titleId, pubDate, title, type, pubId)
List the name of the publishers in alphabetical order.
List the name of all the publishers who are based in ‘Coventry'.
List the total number of books published by each publisher.
Use a subquery to list all the titles published by publisher whose name includes the text ‘Press' and are based in ‘Coventry'.
5. Define the term transaction and discuss the role of transactions in database systems.
6. How does the recovery manager ensure atomicity of transactions? How does it ensure durability?
7. Why does a DBMS interleave the actions of different transactions instead of executing transactions one after the other?
8. What is the difference between stable storage and disk?