Database Design and Modeling

Introduction

This assignment is a group assignment, which is to be done in a group of two to four students. The aim is to provide experience in the steps involved with the design of a simple database. The marks obtained for this exercise count as 20% of the aggregate continuous assessment marks for the semester. The proportion of marks for each task is shown below.

Your group is strongly encouraged to commence this assignment by the end of the seventh or eighth week of semester, but you should progress thoughtfully through the steps. Hasty decisions made early in the design process may result in much more work later.

Feel free to discuss concepts and ideas with peers and other groups, but remember your submission must be the work of your own group. Be careful not to allow anyone or any group to copy your work.

The assignment must be submitted as soft copy in the form of a Microsoft Word document through DSO. You do not have to submit an actual database!

Specification

Barclay Books is a bookstore chain owned by Joseph Barclay, who sells used books and remainders in his stores. To support his growing mail order and Internet business, he requires a database to ensure that his data is current, accurate, and easily accessible.

• There are several branches of Barclay Books in a number of locations. Identification and address data must be kept on these branches to locate and distribute stock.
• Barclay Books deal with a number of publishers. The publisher name and contact details for each publisher that Barclay Books deals with must be kept.
• Barclay Books keeps track of the authors of all books that they have held or are currently holding. This information is linked to the information on books that they have authored. The system must also record whether a particular author is the first, second, etc author of a book.
• The information on books includes the title, publisher, genre of book and whether or not it is available in paperback.
• Multiple copies of a particular book may be held. The system must be able to record and locate individual copies and the price of each copy. With used and remaindered books the price will vary between copies, dependent on condition. Each copy must also be tagged as “Excellent,” “Good” or “Fair” condition.

Operations

Barclay Books needs the system to be able to perform the following:

• regular entry of new books, new authors, new publishers, new copies;
• monthly reports on the copies held and value for each branch;
• monthly reports on the books held by publisher;
• the ability to make ad hoc queries, for example,
  • ‘who published a book titled “Jazz”?’
  • ‘who wrote “Jazz”?’
  • ‘for which books was Isaac Asimov first author?’
  • ‘what copies, including price and condition, does Barclay Books have of “London Under”?’

Tasks

You are required to perform the following tasks in this assignment.

• Construct an enhanced entity-relationship (EE-R) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and limits in participation. Ensure that you give adequate justification for the assumptions that you make in designing your entity-relationship model and include any important integrity constraints (business rules).
• In approximately half a page, describe the circumstances that indicate when supertype/subtype entities should be used in database modelling. Use examples, from your own EE-R diagram if appropriate, to illustrate your answer.
• Map your EE-R model to a relational model and produce a set of 3NF relations.
• Show by providing SQL statements, that all of the ‘dot points’ in the Operations section can be produced from an implementation of your set of normalised relations. Sometimes you may need to use more than one SQL statement, and you should explain how one SQL statement provides information needed by the subsequent SQL statement. NOTE: You don’t have to actually create the database, this is a thought exercise to show that the queries are possible.

You must add the Assignment Attachment Sheet and the Group Assignment Mark Allocation Sheet to your submission.

You may ask questions in the assignments section in DSO about the assignment and some hints may be given.