In this project, you will be designing and implementing a database for an enterprise, and some basic functions required by the primary users of the database. The project will be developed in groups of two and will be divided into four parts:
The database system that you are to implement in this project is a Bookstore, called herein Bstore. The rest of this section provides a detailed description of the basic operations which the users of the database system expect to perform.
The main function of BStore is to sell books, stationery, electronic equipment (including computers, etc.), software, arts supplies, and miscellaneous items (like gifts, etc) to its customers. The bookstore keeps two types of book: textbooks and general books. Each book has been assigned a unique universal product code (UPC), and has a title, a set of authors and a publishing company. In addition, textbooks are assigned to one or more courses. Every item, except books, has a UPC, a name, and a manufacturer. Every item (including books) has a price, an indication whether it is taxable or not, and it is supplied by one or more suppliers. For each supplier the bookstore would like to record their name (which is unique), their address, their city, and their status, which can be "active" or "inactive".
The bookstore does not keep records for its customers, except for the Faculty and Staff of the University who have open accounts with BStore. A customer with a BStore account can charge a purchase to one of their accounts, as long as there is sufficient money in the account to cover the purchase. Each account bears an account id, the account holder's name, his/her department and the amount of funds available.
A customer may pay for their purchase either by cash, or one of the acceptable credit cards, or charge the purchase to their corresponding account (if they have one). BStore currently accepts SBI Card, Master Card, Visa and American Express cards. When the customer requests a credit card charge, the bookstore requests authorization from the credit card company. If authorization fails the customer is asked to pay cash. If the customer refuses, the purchase is cancelled. Similarly, when the customer asks for a purchase to be charged to their BStore account which either does not exist or has no sufficient funds, the customer is given the option to chose another form of payment (cash or credit card).
Customers can return merchandises to the bookstore within 15 days from the purchase date. When an item is returned, a refund is issued in the same payment form that was used for the original purchase. That is, if cash was used for the original purchase, the refund is in cash. Otherwise, the credit card or the BSrore account is credited.
The system should keep three transaction logs: one for the purchases, one for the returns and one for the supplies. A purchase log record consists of the date, the item purchased, the quantity, the amount paid, and the payment type. If the customer paid cash, a "cash" appears in the record. Otherwise the record has the credit card number, or the BStore account number. A return log record is similar to the purchase log record. A supply log record consists of the date, the item supplied, the price, the quantity, and the supplier.
In addition to item purchases and returns, the system should perform a variety of other transactions. The most frequent operations that we need to implement in the first release of the system include
· purchases of items
· item returns for refunding
· adding and removing suppliers,
· adding a new item, or more pieces of an existing item (this is used to process a shipment from a supplier) ,
· creating and deleting accounts and
· printing a variety of reports.
At the end of each day, the bookstore creates a report with all items sold during the day, grouped by item category, and showing the subtotal sales for each category and the grand total for the day. Every week, the bookstore would like to produce reports showing all the items that are low in stock (i.e. have less than 10 pieces on the shelves), or just showing the low stock items in a specific category.
Finally there are a number of simple queries the bookstore users should be able to ask. They should be able to
The system you design will be used by two types of user:
When a user starts the system, the program asks the user to identify their class and provide a password. Then the system starts up the appropriate menu for the current type of user. There are two passwords, one for each user type, that are set at the installation/initialization time. Our system won't deal with users and passwords.
Note:
The requirements stated herein, are not expected to be complete. As you start analyzing these requirements you may notice that certain details are missing. In this case, you may make any reasonable assumptions about them, but if there is any uncertainty about some requirements you should contact me at any time..
Part is due:
Tuesday October 26, in the lab.
Once the requirements are realized, write a short requirements specification document. In this document you have to identify the different operations the system has to perform and describe their behaviour. For each operation you should specify:
Follow this Link for a sample Specification
Once the requirements have been identified, perform a data analysis and draw an Entity-Relationship diagram for the enterprise. Make sure to:
For each entity and relationship set, if you cannot find a meaningful name for it or if there is additional information that is not shown on the ER diagram, provide a short paragraph with that information.
In this part of the project, you will start with the ER Diagram for the Bookstore and design the tables for the database. Start with the ER diagram that is developed in part I design the first version of the tables by following the mapping ER Diagram-to-Relatinal Schemas process we have discussed in the class.
For Part I, you should hand in a copy of the project title page and a single document containing:
An Example description
is gven for you:
Section(dept, cno, year, secno, iname)
You have to prepare the document using a reasonable word processor or hand written.
Each group should submit only one copy of the project with a title page that shows all the names of the group members.
Part II is due:
Tuesday November 30, in the lab.
In this part of the
project, you will implement a part of the database design for the
bookstore and define a user friendly graphical interface for the system
using Developer.
To implement the system you
need to
Usually, the tables are
created by using an interactive SQL command interpreter. You can write a
single SQL script and execute once at
the command interpreter to create and insert values to the tables .
The code for the
transactions is usually broken down into a set of PLSQL codes. The user interface is built on top of that. This
approach offers great modularity and flexibility. For instance, you can easily
add more functions to the system or completely change the interface in the
future. You will be taught PLSQL
very soon in the lab.
Tables for
implementation :
·
Obtained
in Part I of the project.
Operations for
implementation
For each table, you
should define the following operations:
After the tables have been
created and the basic operations have been defined, you should write code for
the following transactions.
Transactions performed by a
clerk:
Transactions performed by a
manager :
The behavior of these
operations is as following:
For
each item the report shows the item's upc, category, selling price, number
of pieces sold and the total value for the sold items. The records
should be grouped by the category and show the total sales value for each
category. At the end, the report should show the total sales value for the day.
The output may look like the following:
|
UPC |
Category |
Unit Price |
Units |
Total Value |
|
2244 |
Arts |
10.50 |
10 |
105.00 |
|
2555 |
Arts |
5.00 |
5 |
25.00 |
|
3455 |
arts |
20.00 |
1 |
20.00 |
|
|
Total |
|
16 |
150.00 |
|
1255 |
general |
20.00 |
5 |
100.00 |
|
5666 |
general |
10.00 |
10 |
100.00 |
|
|
Total |
|
15 |
200,00 |
|
1244 |
misc |
2.50 |
20 |
50.00 |
|
8844 |
misc |
5.00 |
50 |
250.00 |
|
|
Total |
|
70 |
300.00 |
|
|
|
|
|
---------- |
|
|
Total Daily |
Sales |
101 |
650.00 |
For each transaction,
make sure to check for the basic errors and produce appropriate messages for
the user.
User Interface
Create an easy to use
GUI that allows the users to execute all the operations and transactions
provided by the system , and display the rows of any table. Use the menu
facility provided by the graphics environment you use to define appropriate
menus and submenus. Make sure to design your interface in such a way that all
the error messages appear in separate pop-up boxes, or in a designated area of
the main window, so that they don't interfere with the other activities.
Do not worry about access
control. Your interface should have at least two menus: one for the clerks
and another for the managers. Nevertheless, your program should not
provide any access control. It should let any user access any one of the menus.
Submission and
Project Demonstration
Test your system with
enough data to make sure that works well. GUIs normally require a lot of
time and multiple tries to get them right. We expect you to produce a simple but
adequate interface. You should not spend a lot of time on the interface itself.
You should concentrate on the coding of the functionality of your project
rather than the GUI. The goal of the project is to give you some experience
with SQL and Developer tool., and not to have you spend hours trying to get
windows to line up in place.
For Part III, you should
hand in:
To evaluate your project
better, we'll ask each group to demonstrate their system. Demonstrations will
be scheduled for 10 minutes during the due day. I’ll pass a time
sheet around in the lab for the groups for their presentation.. The mark you will receive for this part of
the project will be based on the quality of your presentation, the
functionality of your program, the overall design, and the design of your
interface. Each member of the group should come to the presentation prepared
with a demonstration plan.
Good luck
Singh