Project 10.8
Online Auctions Database
Implement an Online Auction application in Java using JDBC.
The database for the application is given below:
drop table members cascade constraints;
create table members (
fname varchar2(20) not null,
lname varchar2(20) not null,
street varchar2(50) not null,
city varchar2(30) not null,
state varchar2(20) not null,
zip number(5) not null,
phone varchar2(12),
email varchar2(40),
userid varchar2(10),
password varchar2(20),
creditcardtype varchar2(10)
check(creditcardtype in ('amex','discover','mc','visa')),
creditcardnumber char(16),
primary key (userid)
);
drop table items cascade constraints;
create table items (
ino number(5),
category varchar2(20) not null,
title varchar2(128) not null,
description varchar2(2000),
qty number(3),
closeDate date,
sellerId varchar2(10) not null,
startPrice number(7,2) not null,
bidIncrement number(7,2) not null,
lastBidReceived date,
primary key (ino),
foreign key (sellerId) references members
);
drop table bids cascade constraints;
create table bids (
ino number(5),
buyerId varchar2(10),
price number(7,2),
qtyBid number(3),
bidTime date,
primary key (ino,buyerId,price,qtyBid,bidTime),
foreign key (ino) references items,
foreign key (buyerId) references members
);
drop table ratings cascade constraints;
create table ratings (
ino number(5),
buyerId varchar2(10),
bComment varchar2(100),
sComment varchar2(100),
bScale number(1) check (bScale between 1 and 5),
sScale number(1) check (sScale between 1 and 5),
primary key (ino,buyerId),
foreign key (ino) references items,
foreign key (buyerId) references members
);
This project will be developed in stages. In the first stage, which is
due on February 22nd, the following menu system should be implemented.
In addition, each student should send me 15 insert statements for
the items table. This way, I can merge them all and create a reasonable
size items database. Each student should cover at least 3 categories
(i.e. 5 items per category).
[raj@toto project]$ java OnlineAuction
**********************************************************************
*** ***
*** Welcome to the Online Auction ***
*** ***
**********************************************************************
1. Member Login
2. New Member Registration
q. Quit
Type in your option: 2
Welcome to the Online Auction
New Member Registration
Enter first name: Raj
Enter last name: Sunderraman
Enter street address: 123 Main Street
Enter city: Atlanta
Enter state: GA
Enter zip: 30303
Enter phone: 555-1212
Enter email address: raj@cs.gsu.edu
Enter userID: raj
Enter password: raj
Enter type of Credit Card(amex/visa/discover/mc): amex
Enter Credit Card Number: 12121212121212
You have registered successfully.
Name: Raj Sunderraman
Address: 123 Main Street
City: Atlanta GA 30303
Phone: 555-1212
Email: raj@cs.gsu.edu
UserID: raj
Password: raj
CreditCard Type: amex
CreditCard Number: 12121212121212
Press Enter to go back to Menu
**********************************************************************
*** ***
*** Welcome to the Online Auction ***
*** ***
**********************************************************************
1. Member Login
2. New Member Registration
q. Quit
Type in your option: 1
Enter userID: raj
Enter password: raj
**********************************************************************
*** ***
*** Welcome to Online Auction ***
*** Member Menu ***
*** ***
**********************************************************************
1. Browse by Category
2. Search by Title/Description
3. View All of My Bids
4. View All of My Items on Sale
5. Place an Item on Sale
6. Place a Bid
7. Place a Rating
8. View Ratings
9. View/Edit Personal Information
q. Logout
Type in your option: 8
You have successfully logged out!
Press enter to go back to Menu
The remaining options are described below:
1. Browse by Category
This option should first list all categories alphabetically;
Then allow user to choose one category;
Then display item details (2 items at a time on a screen)
Allow user to enter ino to bid on (subsequent interaction should
be similar to Place a Bid)
to press ENTER to return to main menu
to press n ENTER to continue browsing
2. Search by Title/Description
This option accepts a string of the form t-abcd for title search and
d-abcd for description search and should retrieve all items that
contain abcd as a substring in the title or description
(Note: abcd is a sample string!)
Then display item details (2 items at a time on a screen)
Allow user to enter ino to bid on (subsequent interaction should
be similar to Place a Bid)
to press ENTER to return to main menu
to press n ENTER to continue browsing
3. View all of my Bids
This option should display (in a neat format) all of the member's bids
sorted by nearest deadline for auction ending.
4. View all of my items on sale
This option should display (in a neat format) all of the member's
sale items sorted by nearest deadline for auction ending.
5. Place an item for sale
This option should prompt user for all relevant information
required to place item on sale and then place it for sale
(if information is satisfactory).
An (optional) e-mail should be sent to the member.
6. Place a Bid
This option should prompt user for an item number
Then request relevant information such as bid price and quantity
Once satisfactory information is collected, the bid should be placed
and an (optional) e-mail should be sent to the member.
7. Place a rating
8. View ratings
9. View/Edit Personal Information
This option should display all personal information (in a neat format)
and allow member to change e-mail, credit card information or address.
Please design an appropriate user interface (should allow changing
of none or one or two or all three pieces of information).