Download Introduction to Database Systems - Database Project | CS 4400 and more Study Guides, Projects, Research Deductive Database Systems in PDF only on Docsity!
CS 4400 DATABASE PROJECT
Spring Semester 2009
PURPOSE OF PROJECT
Analyze, specify, design, implement and demonstrate an information system to support the operation of the Online Shopping System. The database and the application must be implemented using ORACLE available on ACME. Alternative implementations must be approved by the professor.
PROJECT PHASES
The three phases of the project cover the following tasks. Specific deliverables will be defined for each of the three phases.
PHASE DESCRIPTION DUE DATE
I Analysis & Specification 13-Feb (Friday)
II Design 25-Mar (Wednesday)
III Implementation & Testing 21-Apr (Tuesday)
Demonstration April 22-
Note : Each group can submit the project either in Prof. Markās class on the respective days, or to the T.A. at CoC commons during the TA office hour. As a backup, you may submit to Secretary Deborah in 3042 Klaus building before 4 p.m. on the due date. Her phone no. is 404-385-2892. Phase III requirements will be communicated later.
GROUPS:
Each group must have 3 or 4 members. We allow for 2 member groups; but 4 is an absolute limit. Members can be combined from multiple sections ā but your title sheet on each report must state the last name, first name of each member along with their section. As a group, you will decide whether to complete the lightweight or
heavyweight project options. The two options are identical for phases I and II, but differ in the deliverable for phase III. Note that the option of whether you wish to do heavy or light weight can wait until you get into phase III and as late as the final submission of Phase III.
Heavyweight Option:
Groups choosing this option will demo a working implementation of their project to the TA. The implementation must include a Java or web-based GUI (Graphical User Interface) that uses JDBC (Java Database Connectivity) or ODBC (Open Database Connectivity) for database access. The SQL statements you create in phase II will be embedded inside your GUI.
Lightweight Option:
Groups choosing the lightweight option will submit working SQL statements for each of the project tasks and demo the SQL statements to the TA. This option may be appealing to groups with little or no experience programming GUIs.
Oracle
We will provide you with access to the Oracle Database Management System on ACME. See the course webpage for further information on how to access Oracle from the ACME command line or from a Java program.
DELIVERABLES FOR EACH PHASE
Phase I:
- List of group members (mark clearly on the title page in alphabetical order by Last Name, if from different sections, mention section of each member),
- Information Flow Diagram,
- E-R Diagram,
- Task Decomposition (where appropriate)
- Additional note, if necessary, about any assumptions
Useful Link for Phase I: http://www-static.cc.gatech.edu/classes/AY2007/cs4400_spring/methodologyFall2002.ppt
Project Description
For the project, you will be developing a database application for online shopping called āOnline ShopMartā. The store primarily sells two types of items, namely books and CDs in its online store. There are two types of users:
The customers of the system are the ones who search for items and buy certain items. The manager of the system is responsible for adding items to the inventory, approving the orders, etc.
For using this system, the customer should be a registered member of the website. For the purpose of this project all operations are performed after the user has logged in to his account.
The information and credentials of the manager have been hard coded into this database. That is, information about the managers has been inserted in the database after the database is setup.
Task 1a: Create New User Profile
The customer creates a profile for the site which he can use later on when he wants to log in to the site or make purchases. The username of the customer should be unique in the system. The profile needs to have one shipping address associated with his profile. The items he buys will be shipped to that address.
The create form can be similar to one shown in figure 2.
The system should also have a password which should be no less than 6 alpha- numeric characters.
After logging in, the customer must have the options as shown in figure 1.
Fig 1: Operations to be supported after user logs into the system.
Task 1b: Update User Profile
The user should be able to change his personal information similar to the one shown below in Fig 1a.
Fig 2: Search
It is absolutely OK if the user doesnāt enter anything in the above fields. In that case, the search should return the entire catalog for that type of item being searched.
The Search results page for both type of items searched will be similar to the one shown in figure 3.
Fig 3: Search Results
The āview Infoā button will display the information regarding the book such as the ISBN number, authors, year in which it was published, etc and an optional URL for the website. The āAdd to Cartā button will add the item to the shopping cart.
The quantity in the inventory is only updated after the final checkout and not when the item is added to the shopping cart.
Task 3: Shopping Cart
The user must be able to see and manage the shopping cart. The shopping cart only lasts as long as the customer has logged in to the system. That is, after the customer logs out, the items in the shopping cart are no longer present when he logs in the next time.
Fig 4: Shopping Cart
Before proceeding to the check out, it is imperative to see if the quantity entered for that item exists in the inventory. If there isnāt sufficient quantity, the customer should be shown a message about the maximum amount available and they can order only the maximum amount available. Only if all the items have sufficient quantity in the inventory should the customer be allowed to proceed for checkout.
Payment Information:
The customer can store his/her payment information like credit card details so that he just selects one of the stored cards for buying the items. He can also enter some other detail not stored on the system for checking out the items. In that case, an option must be made available to the user if he/she wants to save that payment information.
The customer makes the payment and is shown the confirmation receipt which is similar to above screen but with all values filled and no options for input.
After this transaction has been completed (i.e. purchase has been made), inventory is updated for the items present in the purchase. If the quantity in the inventory for the item(s) is below the minimum quantity required, then order(s) are generated for that item(s).
Task 4: Return Items:
The customer has the option of returning an item from a purchase. Only one item can be returned at a time. The customer can only return an item if it belongs to a particular purchase.
For example, the screen might look like this:
The check should be made that the purchase id belongs to the customer who has logged in.
For this project, we will assume that the product is delivered by the customer to the company after the form is submitted. Hence necessary updates have to be made to the inventory after the return is complete.
Task 5: Manager Tasks
The managers of Online ShopMart are responsible to efficient managing of items in the store. They are responsible for adding new items, deleting items, approving orders, etc.
The managers have been hard coded into the system. That means that you can enter a set of three managers and assume they are valid as managers.
After logging in the manger will see the screen similar to one shown below.
The manager also needs to login to the system to perform the operations. However once he logs in he will see the options as follows:
Figure 7: Inserting a new book
The item to be modified will be searched by the manager through a similar interface as shown in figure 2.
Task 5b: Adding a new CD
Similarly there is an interface to enter CD information as shown in figure below.
After adding a new book or a CD, an order is placed which is later approved by a manager. (It can be the same manager who inserted it or it can be some other manager).
Task 5c: Place Orders
The orders generated as a result of items being sold are viewed by the manager. The manager then places orders for those items. When placing orders, he specifies the quantity for that item (but the quantity must ensure that the stock of that item in the inventory will be greater than minimum quantity required in stock).
The order information can be similar to one shown in figure 8. There can be more attributes (which can give more information about the item) in the table so that the manger can easily deduce which item is to be ordered
Fig 8: Place Order Screen
