Database To Store Information About Books

CSG1207D Systems and Database Design

(Database Analysis & Design)

Learning Outcomes (Why are you doing this assignment?)

This assignment addresses the following learning outcomes from the Unit Outline:

1. Use Data Analysis to design a Database (Task 1);

3. Implement a Database design using a Database Management system (DBMS), and 

to construct complex queries upon it (Task 2);

General Assignment Information

Your first task is to design a database by analysing the requirements for the scenario detailed on the following pages. State any assumptions/notes you have made regarding your database design at the beginning of the database design document.  Do not make any assumptions/notes that change the structure of the scenario, as this may make Task 2 of the assignment difficult.  Only make assumptions/notes that influence your database design.  If you are unsure about an assumption/note you wish to make, ask your lecturer/tutor.

Once you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram and a physical ER diagram to depict your database.  Adhere to the distinctions between logical and physical ER diagrams covered in lectures.  Show the Table Creation Order in your physical ERD.

Lastly, create a Table Instance Chart (TIC) for each entity in your data model.  List your TICs in the appropriate table creation order that will need to be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, the TICs should contain all the information needed to implement your database. Download the ‘Sample Table Instance Charts for Assignment Task 1’ from Week 0 in Moodle.

Your complete database design should consist of a list of assumptions/notes, logical and physical ER diagrams and TICs. This should be in the form of a single word-processed document.  Include an ECC Assignment Coversheet on the first page of this document.  Be sure to include details of both team 

members if appropriate. You must also include the Marking Guide and the Work Allocation Plan (shown at the end of this document) as part of your submission.

You must use  www.gliffy.com  (or any other modelling tool you are familiar with) to draw your ERDs. Show Primary and Foreign Keys as such: CustomerID (PK) and CustID (FK). Do not underline as it is difficult to see and it is even harder when you have an attribute that is a PK as well as an FK. Instead of underlining use this format: OrderID (PK, FK), ProductID (PK, FK).

Scenario Details

You are required to design and create a database for Books-R-Us, a small general book store that sells books to the public. It would like to create a database to store information about books it sells and orders placed by customers. The following is a description of the entities of interest to the company and a list of attributes. 

Please note that the attributes listed below are the minimum requested by the users. As an analyst you may add any detail you think is necessary for the design to work well and to support the business requirements. These will be your Notes.

Customers

The firm stores the following details about their customers:

• First name and last name
• Gender
• Contact numbers
• Email Address

Orders

The firm stores the following details about orders:

• An order ID
• The date of the order
• The date the order was shipped
• Details about the customer who placed the order
• Details about the books sold, such as quantity, price sold, etc.
• Any discounts applied to the order

The shipping date must be validated against the order date; it may be the same as the order date or after, but not before the order date.

The company may apply one, or none, of the following discounts at the time of purchase:

• Staff discount (5%)
• Seniors discount (10%)
• Loyalty discount (15%)

Orders may be received in the following ways, and with the following data requirements:

Books

The firm stores the following details about books:

• The ISBN
• The title
• The edition of the book
• The publisher name
• The date published
• The cost price, the wholesale price, and the recommended price
• The category
• The sequel or prequel (if any) of a book
• The author order (in the case of books with multiple authors)
• The percentage royalty that must be paid to each author

The book categories used by the company are as follows:

• Fiction
• Non-fiction
• Romance
• History
• Business

The cost price must not exceed the wholesale price, which in turn must not exceed the recommended retail price.

Authors

The company stores the following details about the authors of the books they sell:

• First name and last name
• Contact number
• Email address

Publishers

The company stores the following details about the publishers of the books they sell:

• Company name
• Contact person and contact details
• Email address of the publisher

General Information and Guidelines 

The information above describes all of the entities and relationships required in the database design. Some details, such as the cardinality of some relationships, have been omitted. It is up to you to make and state any assumptions/notes you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer. 

It is strongly recommended that, where appropriate, you give each entity an auto-incrementing integer attribute as a primary key. Be sure to specify appropriate data types (and lengths, where applicable) for all columns in your Table Instance Charts. For example, while hourly pay rates are shown with a dollar sign in the table above, they should be stored in some kind of numeric form to allow mathematical operations to be performed on them.

Read the scenario details several times to ensure that your database design incorporates all the elements described. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer.