CPSC 304

Partial Solution for Project Part I

1. Transaction Specification

1.1  Purchase

User: Clerk

Input: UPC, quantity, payment type

Optional input (depending on payment type): cash amount, or credit card type and number, or account type and number.

Output: "OK" or "Cancelled"

Basic Case:

The system will remove quantity many items from the item table check if the item is taxable, and calculate the total value. If the amount is to be charged on a credit card, will ask for authorization. If the payment is to be charged to a UBStore account, check for sufficient funds. If all go well it adds a log record in the purchases log table and returns "OK".

Exceptions:

• If item is not in the database, transaction is aborted and "Cancelled" is returned.
• If there are not enough pieces in stock transaction is aborted and "Cancelled" is returned.
• If not enough cash is tendered, transaction is aborted and "Cancelled" is returned.
• If credit card is not authorized, the customer is asked to pay cash.
• If UBStore account for the item category does not exist, or does not have sufficient funds, the customer is given the option to chose another form of payment (cash or credit card).

1.2  Return

User: Clerk

Input: UPC, quantity, payment type

Optional input (depending on payment type): credit card type and number, or account type and number.

Output: "OK" or "Cancelled"

Basic Case:

The system will add quantity many items to the item table, check if the item is taxable, and calculate the total value to be refunded. If the payment was through a UBStore account, it credits the account. If all go well ,it adds a log record in the purchases log table and returns "OK". (Crediting a credit card is done manually by the clerk.)

Exceptions:

• If item is not in the database, transaction is aborted and "Cancelled" is returned.
• If UBStore account for the item category does not exist, a cash refund is given..

1.3  Add Item

User: Manager

Input: date, UPC, quantity, supplier name.

Optional input :

• If item is new: type sell price, taxable status, supplier price.
• If item is a book: title, authors, publisher.
• If it is a text book: courses in which it is used
• If it is not a book: name, manufacturer

Output: "OK" or "Cancelled"

Basic Case:

The system will check if the item already exist in the database. It will update the item table, the supplies table if the item is new, the book and nonbook tables according to the item type, and will insert a record in the shipment log.

Exceptions:

• Supplier name is not in the database. System will ask to add the supplier  info first, and then will continue. If supplier addition is not successful, transaction is aborted and "Cancelled" is returned.
• If the item category does not exist, the user will be asked to correct it. If still not correct, transaction is aborted and "Cancelled" is returned.
• If item is a text and no course is assigned, or course assigned does not exist, the user will be asked to correct it. If still not correct, transaction is aborted and "Cancelled" is returned.

1.4  Add Supplier

....

1.5  Remove Supplier

...

1.6  Add Account

....

1.7  Remove Account

...

1.8  Day Sales Report

...

1.9 Low Stock Report

...

1.10  Item Query

...

1.11  Suppliers for Item Query

...

1.12  Top 5 Items from Supplier Query

...

1.13  Courses for Text Query

...

1.14  Texts for Course Query

...

1.15  Books from Publisher Query

...

2. ER Diagram

The following is the ER diagram for the UBStore enterprise. Please note the following:

• A "d" inside the ttiangle of an ISA hierarchy indicated that it is disjoint.
• We could have expressed the different types of a nonbook item with another ISA hierarchy containing one subset for each item category.  Since all the nonbook items have the same attributes and participate in the same relationships, there is no need to split them. Instead we define an attribute "category" that allows us to distinguish their type.
• We could have used another subclass for book, say "General". In this case the ISA relations would be total and disjoint.