Purpose Of Project
Project Phases
The three phases of the project cover the following:
| Phase | Description | Begins | Ends |
| Phase I | Analysis & Conceptual Design | 1/21 | 2/10 |
| Phase II | Logical Design | 2/17 | 3/2 |
| Phase III | Implementation, Testing & Demonstration | 3/7 | 4/27 |
Groups
Groups of 3-4 students are required. If you have difficulty forming a group, please post to the newsgroup and we will combine people without groups into groups. Each group should have at least one CS/CompE student and no more than 2 ISyE students; however, no group can consist of all CS/CompE students or all ISyE students.
Reports
A typed report is handed in for grading at the end of each phase. You must follow the report requirements, which are posted at the links below.
The Phase I Report must contain an analysis of the intended information system and the purpose of this phase of the project. It must describe the problems encountered in this phase and justify the solutions. It must contain all documentation produced in this phase.
The Phase II Report must contain the goals of this phase of the project. It must contain the Phase I Report and must describe any revisions made to the specification described in the Phase I Report. It must further describe the problems encountered in Phase II and justify the solutions. It must contain all documentation produced in Phase II.
The Phase III Report will be a comprehensive
report for the entire project. It must contain the Phase I and Phase
II Reports and must describe any revisions made to the specification described
in the Phase II Report. It must describe the problems encountered in this
Phase III and justify the solutions. It must contain all documentation
produced in Phase III, including the schema definition and a source program
listing. Add a description of what additional features you have and what
may be some of the limitations of your system.
Grading
The project carries 30% of the class grade.
Different phases of the project carry different weights, with emphasis on the final report and implementation.
All members in a group get the same grade for phases I and II. Group work is to be emphasized..
For Phase III, every member is asked to evaluate other members in the team and turn in a confidential, hard-copy report to us. If there is evidence of lack of participation by certain members, their Phase III grade will reflect it.
The Allegro Music Store - General
Functionality
*** Important!!! The following
specifications are intended as a guide; they are NOT the complete, nitty-gritty
specifications. These are intended to get you started in the right
direction in designing your system. You as the designer must analyze
and decide what other details or features should be specified for your
system. Thus, individual group implimentations will differ in terms
of design and implementation styles.
The Allegro music store serves a wide variety of musical interests. As a small store, it must maintain close control of its inventory. The store keeps an inventory of music in stock. The store also keeps track of customer orders, which are placed either online or through an employee of the store.
Your task is to design the database and application programs that will
help manage the inventory and the day to day processing. Note that
certain functions like orders with vendors, automatically-generated orders,
receipt of shipments, etc. are left out in order to reduce the size of
the project.
There are four types of processes that are relevant:
_________________
[query] This process allows store employees to query the database with regard to music in stock and music on order.
[order] This process generates orders by customers for music. Orders are placed online (don't worry about online details) or called in by phone. A store employee inputs orders.
[sell] This process modifies the database appropriately, regarding the item(s) being sold and the employee making the sale. It is typically the operation done at the cash register.
[billing] An invoice is generated for every order placed by a customer. A receipt is printed for every in-store sale transaction.
[admin] This process modifies the database information about employees,
customers, vendors, etc. It may have other management report features
which are left out for this project.
_________________
The Allegro music store sells music in 3 different forms: cassette, compact disk, and sheet music. A given music title has several attributes associated with it: examples are title, artist(s), producer, musical subject, physical type of music (cassette, compact disc, or sheet music), year of recording, etc. An employee can query the music in stock by searching on any one or more attributes of the music.
Music vendors can distribute several music titles; however, each music title can only be distributed by one vendor. Vendor information (name, address, phone, contact person, etc.) is also maintained in the database.
Employees are in charge of several tasks: selling music to customers, ordering music from vendors, and/or generating sales reports. Employee information is also maintained: ID, name, address, pay level, job title, etc.
The store can place orders to vendors when stock runs low or when a customer requests a special order. A particular order can only go to one vendor: this means that all items being ordered from a particular vendor can exist on the same order. However, if items are distributed by differing vendors, an order form for each vendor must be filled out. Example attributes of orders are the item(s) to be ordered, date of order, vendor to whom the order is going, and customer information if applicable. *Note: In Phase I you should consider vendor orders for restocking purposes as a part of the system. In Phases II and III, they will be left out.
Customer information is maintained when special orders are placed for that customer. Customer information can also be maintained optionally when selling music: for example, customers can fill out a card to be put onto a mailing list. *Note: Advertising and promotion features are out of the scope of this project.
Sales information must be maintained whenever music items are sold: amount of purchase, each item purchased, customer information (if applicable), the employee making the sale, etc.
Statistics
Sample statistics relevant to the applications include the following. They would impact the real operational environment of the database system. Here, it is offered as general information only.
CUSTOMERS:
30,000 customers in the database
Queries: sales reports are done once weekly by music title
MUSIC:
50,000 different music titles
between 1 and 20 copies of any music title is in stock at any given time
VENDORS:
200 different vendors supply music
CUSTOMER ORDERS/SALES:
300 customer orders per day are submitted online (each order has on average 5 titles)
10 clerk orders per day are taken over the phone (each order has on average 3 titles)
500 orders are queried per day to check on the status of the order
500 sale transactions per day in the store with an average of 3 titles
per sale
ORDERS TO VENDORS:
Every week about xx orders are placed with vendors, each with
about 25 items (each item with an average quantity of 20)
SHIPMENTS RECEIVED FROM VENDORS:
Every day yy shipments are received from vendors. Each
shipment corresponds to an order.
NOTE: Values of xx and yy above can be estimated but are left out.
Assumptions
The following assumptions will be made regarding the day-to-day activities
of the Allegro Music store. More assumptions may be added at a later
date.
Assume that the database is implemented for transactions on or after
Jan. 1, 2000.
Music
Music titles can come in 3 different forms: cassette tape, compact disk,
or sheet music. At any one time the store may have all three forms
in stock for a particular music title. Therefore, the database must
keep track of which forms of music are in stock for each music title.
Vendors
A vendor may publish several music titles; however, a particular music
title can be published by one and only one vendor.
Orders to Vendors
When placing a regular order, all music by the same vendor may exist on the same order. Music from different vendors must be placed on different orders: one vendor per order. You can create a unique order ID by using the date as part of the order number. Date should be in the format YYMMDD.
Orders may be placed one of two ways. The customer can use an
online order form to place an order, or the customer may ask a store employee
to place the order for them. *Note: you
should not be concerned with how the online form is implemented.
Your only concern is to make sure the order information gets recorded in
the database, regardless of the method of input.
Customers
Customer information is optionally stored in the database. For
orders placed online, recording customer information is mandatory.
For in-store customers, it is optional. If a customer places a special
order, then the customer`s name and telephone number must be recorded.
Employees
Employees of the store will be the users of this database system, not
the customers themselves. Information on every employee is in the
database for management purposes.
Sales
Each sale is made by one employee to one customer. The sale can
include multiple music titles, and should also include quantity & price
of each item and a grand total. Upon making the sale, the system
should decrement the music in stock appropriately.
Applications For the Allegro
Music Store
You are to develop a menu driven application system for the Allegro Music Store database using Pro*C. The following are examples of some of the menus to be developed. All applications described below MUST be implemented. However, you may choose to add more functions.
** Note: You may add more menu screens as necessary; the screens
below are intended only as a guide.
Allegro Music - Main Menu
----------------------
1. Music Applications
2. Ordering Applications
3. Sales Applications
4. Administrative Applications -- Access restricted to Managers ONLY
5. Exit
----------------------
Select Item:
For item 1 (Music Applications) in the Main Menu, another menu would appear as shown below, which allows a sales clerk to add a music title to the inventory, change information about any title (based on ISBN) and query the music title information.
Music Applications - Menu
--------------------------
1. Add A Music Title
2. Change Music Information
3. Query Music Information
4. Return to Main Menu
--------------------------
Select Item:
Each of these items would require further information about the music title to be entered from the user. Adding music requires all parameters of the title to be entered into the database. Changing music information stored in the database requires the changed field(s) to be entered and the database must be updated appropriately. To query music information, the system must prompt the user to enter the desired search criteria and then return the results in a tabular format.
For item 2 (Ordering Applications) in the Main Menu, another menu would appear as shown below, which allows a sales clerk to query the order data (based on order number, customer, or vendor), place a new order, or change an existing order.
Order Applications - Menu
--------------------------
1. Query Order Information
2. Place New Order
3. Change Existing Customer Order
4. Return to Main Menu
--------------------------
Select Item:
Each of these items would require further information about the order
from the user. To query an order, the user must enter search criteria,
and results of the query must be returned in a tabular format. To
place an order, the order information must be prompted for and stored into
the database; if the order is for a customer, then customer information
must be stored also. To change order information, the user must first
pull up the order in question, then change the appropriate field(s).
The changes are then stored in the database.
For item 3 (Sales Applications) in the Main Menu, another menu would appear as shown below. The first option allows a clerk to record the information in the database that a given music title(s) has been sold. Appropriate customer information is also stored. The second option produces six sales reports. Note that only managers are allowed to produce sales reports.
Sales Applications - Menu
-------------------------
1. Sell A Music Title(s)
2. Sales Reports -- Access restricted to Managers ONLY
3. Return to Main Menu
-------------------------
Select Item:
The first choice is continuously used at the cash registers. No customer information is required for a sale. A sale should allow for multiple music titles to be sold during one transaction. A receipt should also be generated.
For item 2, another menu would be produced which would allow the manager to produce five sales reports. Note that the system must have some method of recognizing that the user is a valid manager (use some kind of login method).
Sales Reports - Menu
-------------------------
1. Total Sales by Title
2. Total Sales by Customer
3. Total Sales sorted by Vendor
4. Total Sales sorted by Subject
5. Total Sales - Top n Titles
4. Return to Main Menu
-------------------------
Select Item:
All sales reports will prompt the manager to enter starting and ending dates for the report. Depending on which report is chosen, additional information may be needed: vendor, subject, type of music, etc. All reports should list sales in decreasing order: e.g., the highest sale first.
The first report should list total sales per day for a particular title during the time period specified by starting/ending dates.
The second report should list all titles ordered for and sold to a particular customer. *Note that this is only applicable for those customers who have ordered books, since their information is already in the database. This does not apply to walk-in customers who are not listed in the database.
The third report shows all titles sold per vendor, sorted by vendor name. It should list a subtotal for each vendor, and a sales total at the end of the report.
The fourth report shows all titles sold per subject, sorted by subject name. It should list a subtotal for each subject, and a sales total at the end of the report.
The fifth report shows the top n sales of music titles in decreasing
order. For instance, if n is 20, then the top 20 best-selling
music titles will be listed with their total sales in decreasing order.
For item 4 (Administrative Applications) in the Main Menu, another menu
would appear as shown below. This menu allows the manager to add/change
customer information, publisher information, or employee information.
Again, access to employee information is restricted to managers only.
Administrative Applications - Menu
--------------------------
1. Customer Information
2. Publisher Information
3. Employee Information -- Access restricted to Managers ONLY
4. Return to Main Menu
--------------------------
Select Item:
Each of these items would require further information. For each choice, the system should ask whether the user wants to add new information or change existing information. To add information, the system should ask for input and store it appropriately. To change information, the system should first display the current information in the database for a particular record, then prompt the user to change the appropriate information, and finally, store the changes.
To access Employee information, managers must provide some sort of clearance
(login id and password, for instance).