Document Preview:

CREATE DATABASE PREMIERE; USE PREMIERE; CREATE TABLE Rep (RepNum CHAR(2) PRIMARY KEY, LastName CHAR(15), FirstName CHAR(15), Street CHAR(15), City CHAR(15), State CHAR(2), Zip CHAR(5), Commission DECIMAL(7,2), Rate DECIMAL(3,2)); CREATE TABLE Customer (CustomerNum CHAR(3) PRIMARY KEY, CustomerName CHAR(35) NOT NULL, Street CHAR(15), City CHAR(15), State CHAR(2), Zip CHAR(5), Balance DECIMAL(8,2), CreditLimit DECIMAL(8,2), RepNum CHAR(2), foreign key (RepNum) references Rep(RepNum) ); CREATE TABLE Orders (OrderNum CHAR(5) PRIMARY KEY, OrderDate DATE, CustomerNum CHAR(3), FOREIGN KEY (CustomerNum) REFERENCES Customer (CustomerNum) ); CREATE TABLE Part (PartNum CHAR(4) PRIMARY KEY, Description CHAR(15), OnHand DECIMAL(4,0), Class CHAR(2), Warehouse CHAR(1), Price DECIMAL(6,2) ); CREATE TABLE OrderLine (OrderNum CHAR(5), PartNum CHAR(4), NumOrdered DECIMAL(3,0), QuotedPrice DECIMAL(6,2), PRIMARY KEY (OrderNum, PartNum), foreign key (PartNum) references Part(PartNum), foreign key (OrderNum) references Orders (OrderNum)); INSERT INTO Rep VALUES ('20','Kaiser','Valerie','624 Randall','Grove','FL','33321',20542.50,0.05); INSERT INTO Rep VALUES ('35','Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07); INSERT INTO Rep VALUES ('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05); INSERT INTO Customer VALUES ('148','Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,'20'); INSERT INTO Customer VALUES ('282','Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,'35'); INSERT INTO Customer VALUES ('356','Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,'65'); INSERT INTO Customer VALUES ('408','The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,'35'); INSERT INTO Customer VALUES ('462','Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,'65'); INSERT...Modify the following tutorial to build a Web Application Using a MySQL Database:

http://netbeans.org/kb/docs/web/mysql-webapp.html

In the above example, the welcome page (index.jsp) presents the user with a simple HTML form. When a browser requests index.jsp, the JSTL code within the page initiates a query on its database and retrieves data from the database table, and inserts it into to the page before it is sent to the browser. When the user submits his or her selection in the welcome page's HTML form, the submit initiates a request for the response page (response.jsp).

For our assignment, please use our "Premiere" database. Design the webpages to allow the user to select a rep from the rep list in index.jsp, then response.jsp will present the user the information of the customers associated with the selected rep.

Please attach:

1. zip file for the netbean project.

2. A report in Word document that contains detailed screen shots of your web application. (If the project has errors and cannot run, just tell in the report).

 Credits cannot be awarded without above proofs.  No make up changes can be made when evidences are missing. 

CREATE DATABASE PREMIERE;

 

USE PREMIERE;

 

CREATE TABLE Rep

(RepNum CHAR(2) PRIMARY KEY,

LastName CHAR(15),

FirstName CHAR(15),

Street CHAR(15),

City CHAR(15),

State CHAR(2),

Zip CHAR(5),

Commission DECIMAL(7,2),

Rate DECIMAL(3,2));

 

CREATE TABLE Customer

(CustomerNum CHAR(3) PRIMARY KEY,

CustomerName CHAR(35) NOT NULL,

Street CHAR(15),

City CHAR(15),

State CHAR(2),

Zip CHAR(5),

Balance DECIMAL(8,2),

CreditLimit DECIMAL(8,2),

RepNum CHAR(2),

foreign key (RepNum) references Rep(RepNum)

);

 

CREATE TABLE Orders

(OrderNum CHAR(5) PRIMARY KEY,

OrderDate DATE,

CustomerNum CHAR(3),

FOREIGN KEY (CustomerNum) REFERENCES Customer (CustomerNum) 

);

 

CREATE TABLE Part

(PartNum CHAR(4) PRIMARY KEY,

Description CHAR(15),

OnHand DECIMAL(4,0),

Class CHAR(2),

Warehouse CHAR(1),

Price DECIMAL(6,2) );

 

CREATE TABLE OrderLine

(OrderNum CHAR(5),

PartNum CHAR(4),

NumOrdered DECIMAL(3,0),

QuotedPrice DECIMAL(6,2),

PRIMARY KEY (OrderNum, PartNum),

foreign key (PartNum) references Part(PartNum),

foreign key (OrderNum) references Orders (OrderNum));

 

INSERT INTO Rep

VALUES

('20','Kaiser','Valerie','624 Randall','Grove','FL','33321',20542.50,0.05);

INSERT INTO Rep

VALUES

('35','Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07);

INSERT INTO Rep

VALUES

('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);

INSERT INTO Customer

VALUES

('148','Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,'20');

INSERT INTO Customer

VALUES

('282','Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,'35');

INSERT INTO Customer

VALUES

('356','Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,'65');

INSERT INTO Customer

VALUES

('408','The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,'35');

INSERT INTO Customer

VALUES

('462','Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,'65');

INSERT INTO Customer

VALUES

('524','Kline''s','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,'20');

INSERT INTO Customer

VALUES

('608','Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',2106.00,10000.00,'65');

INSERT INTO Customer

VALUES

('687','Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',2851.00,5000.00,'35');

INSERT INTO Customer

VALUES

('725','Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',248.00,7500.00,'35');

INSERT INTO Customer

VALUES

('842','All Season','28 Lakeview','Grove','FL','33321',8221.00,7500.00,'20');

INSERT INTO Orders

VALUES

('21608','2010-10-20','148');

INSERT INTO Orders

VALUES

('21610','2010-10-20','356');

INSERT INTO Orders

VALUES

('21613','2010-10-21','408');

INSERT INTO Orders

VALUES

('21614','2010-10-21','282');

INSERT INTO Orders

VALUES

('21617','2010-10-23','608');

INSERT INTO Orders

VALUES

('21619','2010-10-23','148');

INSERT INTO Orders

VALUES

('21623','2010-10-23','608');

INSERT INTO Part

VALUES

('AT94','Iron',50,'HW','3',24.95);

INSERT INTO Part

VALUES

('BV06','Home Gym',45,'SG','2',794.95);

INSERT INTO Part

VALUES

('CD52','Microwave Oven',32,'AP','1',165.00);

INSERT INTO Part

VALUES

('DL71','Cordless Drill',21,'HW','3',129.95);

INSERT INTO Part

VALUES

('DR93','Gas Range',8,'AP','2',495.00);

INSERT INTO Part

VALUES

('DW11','Washer',12,'AP','3',399.99);

INSERT INTO Part

VALUES

('FD21','Stand Mixer',22,'HW','3',159.95);

INSERT INTO Part

VALUES

('KL62','Dryer',12,'AP','1',349.95);

INSERT INTO Part

VALUES

('KT03','Dishwasher',8,'AP','3',595.00);

INSERT INTO Part

VALUES

('KV29','Treadmill',9,'SG','2',1390.00);

INSERT INTO OrderLine

VALUES

('21608','AT94',11,21.95);

INSERT INTO OrderLine

VALUES

('21610','DR93',1,495.00);

INSERT INTO OrderLine

VALUES

('21610','DW11',1,399.99);

INSERT INTO OrderLine

VALUES

('21613','KL62',4,329.95);

INSERT INTO OrderLine

VALUES

('21614','KT03',2,595.00);

INSERT INTO OrderLine

VALUES

('21617','BV06',2,794.95);

INSERT INTO OrderLine

VALUES

('21617','CD52',4,150.00);

INSERT INTO OrderLine

VALUES

('21619','DR93',1,495.00);

INSERT INTO OrderLine

VALUES

('21623','KV29',2,1290.00);

 

 

create view TopLevelCust as 

    select CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit 

    from Customer 

    where CreditLimit >= 10000; 

 

create view PartOrder as 

    select Part.PartNum, Description, Price, Orders.OrderNum, OrderDate, NumOrdered, QuotedPrice 

    from Part, Orders, OrderLine 

    where Orders.OrderNum = OrderLine.OrderNum 

     and OrderLine.PartNum = Part.PartNum ;