Comp 353- Databases
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, 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.
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.
Information about orders include order-number, 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.
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, telephonenumber, address, city, province, postal-code, email address and amount-of-cumulativepurchases.
These are the minimum requirements for your application. More details could be added
through more research and investigations from your part.
1. 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
2. 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.
3. 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.
i. Get details of all books in stock ordered by year-to-date-qty-sold in
ii. Get details of all back orders for a given publisher.
iii. For a given customer, get details of all his/her special orders.
iv. For a given customer, get details of all his/her purchases made during a
specific period of time from a given branch.
v. Give a report of sales during a specific period of time for a given branch.
vi. Find the title and name of publisher of book(s) that have the highest backorder.
vii. Give details of books that are supplied by a given publisher ordered by
their sale price in increasing order.
viii. For all publishers who have at least three branches, get details of the head
office and all the branches for those publishers.
ix. Get details of books that are in the inventory for at least one year but there
have never been a purchase for that specific book.
x. 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:
(1) DESIGN: The E/R diagram of the design of the database given in the
project description (or a revised version, if deemed necessary).
(2) The SQL statements formulated and used to create the database. Pick
appropriate data types for the attributes and include them in your
(3) The SQL statements formulated to express the required queries and
(4) Populate each table in the database with at least 10 representative and
(5) For each relation R created in your database, report the result of the
following SQL statement:
SELECT COUNT(*) FROM R;
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx