COMP5531- Files and Databases Warm-Up Project Solved

A Simple database for a Bookstore

In this project, you and your group are required to develop a miniature database application system, described below, and evaluate a number of queries and transactions against the database. For this, you should use the faculty MySQL DBMS through the group. Account assigned to your group a string of the form "xyc55311", for some letters x and y. The PODs during the lab sessions will help you resolve possible problems you may have, for instance, connecting or interacting with the DB server.

Project Description

The application is to develop a database system for a small Bookstore that maintain close control of its inventory and its day to day processing. The bookstore serves a wide variety of reader interests. The application must maintain information for the bookstore about books, customers, publishers and orders. The bookstore orders books from several publishers. A given book is only supplied by one publisher.

Information about publishers include publisher-number(id), company-name, branches, telephone-number, address, city, province, postal-code, email address, website. A publishing company can have one head office and multiple branches where each branch can have only one representative.

Jason: Information about branches include branch-name, representative and his email address, telephone-number, address, province and postal-code. Any branch for a given publishing company can supply any book published by that company.

Adnan: Information about orders include order-number(id), ISBN, order-date, quantity-ordered, publisher and branch. If the order is special for a specific customer, then the customer information must be recorded. An order may include several books and a book may appear in several orders.

Patrick: Information about books include ISBN, title, author, cost price, selling price, subject, quantity-on-hand, and year-to-date-qty-sold.

Information about customers include first-name, last-name or company name, telephone-number, address, city, province, postal-code, email address and amount-of-cumulative-purchases.

 These are the minimum requirements for your application. More details could be added through more research and investigations from your part.

 

Express the Bookstore information in the E/R model. Use arrows to indicate the constraints on the relationships. Underline the key attributes for the entity and relationship sets.
Convert the E/R diagram into at least 9 relations: Bookstore, Book, Customer, Publisher, Publisher-Branch, Inventory, Order, Reader-Interest, Author. Other relations might be needed to capture all the requirements.
Write SQL scripts to create the Bookstore database and populate the tables with appropriate data. Also write SQL scripts of the queries and transactions given below. Include at least 10 representative tuples in each table so that the result of each query includes at least one tuple. Note that the Graphical User-Interface (GUI) is not required in this project but encouraged.
Get details of all books in stock.
Get details of all back orders.
For a given customer, get details of all his/her back orders.
For a given customer, get details of all his/her purchases made during a specific period of time.
Give a report of sales during a specific period of time.
Find the title of book(s) that have the highest back-order.
Give details of books that are supplied by a given publisher.
For a given publisher, get details of the head office and all the branches for that publisher.
Get details of books that are in the inventory but there have never been a purchase for that specific book.
Get details of all books that are in the inventory for a given author.
Project Report: Structure and Contents
Each group should submit their project report through Moodle before the deadline, one report per group. The report should include the following parts:

DESIGN: The E/R diagram of the design of the database given in the project description (or a revised version, if deemed necessary).
The SQL statements formulated and used to create the database. Pick appropriate data types for the attributes and include them in your report.
The SQL statements formulated to express the required queries and transactions mentioned.
Populate each table in the database with at least 10 representative and appropriate tuples.
For each relation R created in your database, report the result of the following SQL statement:
SELECT COUNT(*) FROM R;

A Final Note: Your report should also include the originality FORM as the cover page that is signed by EVERY member of the group. The cover page should also include the name and ID of every member of the group members together with the "Group Account" assigned by Stan's email confirmation of your group registration.