Question One (30% = 30 marks)
Consider the following relational database schema:
• Author(aID, aName, age, nationality)
• Publisher(pID, pName, address)
• PublishesWith (aID, pID, since)
• Book(bID, title, genre, bFormat)
• AuthoredBy(aID, bID)
• Sells(pID, bID, copiesAvailable, copiesSold)
Clarifications:
• aID, pID and bID are the identification numbers (unique IDs) of authors, publishers, and
books, respectively.
• Underlined attributes indicate the primary keys of each relation.
• The bFormat attribute indicates the format of the book, e.g. hardback, softback, mass
market, digital, etc. Assume each book has a single format, (i.e. if released as hardback it
will not be released in paperback and so on).
• The genre attribute indicate the genre of the book, e.g.: literary fiction, horror, crime, young
adult, sci-fi, fantasy, etc.
• PublishesWith relation links authors with their publishers, the since attribute indicates the
date when the authors started working for the corresponding publisher. Authors publish with
publishers (i.e. they write books of multiple genres, which the publishers subsequently own
and sell). The same author could publish with multiple publishers, so do not make
assumptions otherwise.
• Authoredby relation links authors with the books they have written, as some books may
have more than one author.
• Sells relation indicate which books each publisher sells (i.e. publishes or releases);
copiesAvailable attribute indicates how many copies of a book are available for sale via the
corresponding publisher; and copiesSold attribute indicates the number of copies of a book
which has already been sold by the corresponding publisher.
Task
Create the above schemas in MySQL, using the CREATE TABLE statement. Make sure
that you define all possible keys, and that entity integrity and referential integrity are
guaranteed in a reasonable manner.
Key Points to Consider:
• Make sure you assign appropriate domains for each attribute.
• Make sure you assign appropriate primary keys for each table.
• Make sure you identify, and correctly assign, foreign keys for tables which require them.
Ensure you use the correct and complete syntax when doing so.
• Do not add any additional attributes to any of the above relations when creating your tables.
2
Question 2 (70% = 70 marks)
Task
For the above relational database schema provide MySQL queries for the following:
1. (10 marks) Find the names of publishers that sell “hardback” books.
2. (10 marks) Find the names of publishers that sell both “fantasy” books and “sci-fi” books\
Note that fantasy and sci-fi are both genres of books.
3. (10 marks) Find the names of publishers where one can find all “fantasy” books (i.e. copies
of all those books should be available in stock).
4. (10 marks) Find the ids of publishers which sell “literary fiction” books, and which do not
have any authors older than 30. Note that “literary fiction” is a genre of books.
5. (10 marks) For each publisher report the publisherID and the age of the oldest authors who
publish with it.
6. (10 marks) Find the names of authors who are older than at least one other author who
publishes with the publisher “Sci-Fi Masters”. Note that “Sci-Fi Masters” is a name, not the
ID, of a publisher.
7. (10 marks) Find the names of authors who publish with publishers which have sold at least
five genres of books.
Hint:
A good idea would be to populate sample data into your database using MySQL. This will help
you to verify that your MySQL queries are correct.
Key Points to Consider:
• There may be more than one way to answer each question. You will not lose marks as long
as your query is free of redundancies: you should not include redundant joins or operations,
or return additional attributes or data records (rows) that are not requested by the question
• You must submit one query per question, this also applies to the first question. Please do not
submit multiple solutions as you will lose marks for any that are incorrect, and there is no
extra marks for "additional solutions".
• Make sure you consider, where appropriate, records from both the main (publisher, author,
book) and linking (e.g. publisheswith) relations when writing your queries.