Source Code

SQL Queries Create BUSINESS table
create table BUSINESS (
business_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
date_time_stamp TIMESTAMP NOT NULL,
name varchar(40) NOT NULL,
street varchar(40) NOT NULL,
city varchar(20) NOT NULL,
state char(2) NOT NULL,
zip_code INT(5),
phone_number VARCHAR(15),
webpage VARCHAR(30),
subcategory_id SMALLINT UNSIGNED NOT NULL,
category_id SMALLINT UNSIGNED NOT NULL,
primary key(business_id),
foreign key(subcategory_id) references SUBCATEGORY(subcategory_id),
foreign key(category_id) references CATEGORY(category_id));

Create CATEGORY table
create table CATEGORY (
category_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
category_name varchar(20) NOT NULL,
primary key(category_id));

Create SUBCATEGORY table
create table SUBCATEGORY (
subcategory_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
subcategory_name varchar(20) NOT NULL,
category_id SMALLINT UNSIGNED NOT NULL,
primary key(subcategory_id),
foreign key(category_id) references CATEGORY(category_id));

Create COMMENT table
create table COMMENT (
business_id SMALLINT UNSIGNED NOT NULL,
date_time_stamp TIMESTAMP NOT NULL,
rating DECIMAL(2,1) NOT NULL,
comment_text LONGBLOB NOT NULL,
submitter_name varchar(30),
submitter_email varchar(30),
primary key(business_id,date_time_stamp),
foreign key(business_id) references BUSINESS(business_id));

Insert into BUSINESS
insert into BUSINESS
values( null, null, {business_name},{street_address},{city},{state},{zip_code},{phone_number},
{webpage},{subcategory_id}, {category_id} );

Insert into COMMENT
insert into COMMENT
values( {business_id}, null, {minimum_rating},{comment},{your_name},{your_email} );

Perform an Advanced Search Select
SELECT business_id,name,street,city,state,zip_code,phone_number,webpage,
AVG(rating) AS avg_rating
FROM BUSINESS, CATEGORY, SUBCATEGORY, COMMENT
WHERE BUSINESS.business_id=COMMENT.business_id
AND CATEGORY.category_id=BUSINESS.category_id
AND SUBCATEGORY.subcategory_id=BUSINESS.subcategory_id
AND (category_name='{business_type}' OR '{business_type}' IS NULL)
AND (subcategory_name='{subcategory}' OR '{subcategory}' IS NULL)
AND (city LIKE '%{city}%' OR '{city}' IS NULL)
AND (state='{state}' OR '{state}' IS NULL)
AND (street LIKE '%{street}%' OR '{street}' IS NULL)
AND (zip_code='%{zip_code}%' OR '{zip_code}' IS NULL)
AND (rating>='{minimum_rating}' OR '{minimum_rating}' IS NULL)
GROUP BY name
ORDER BY name;

Perform a Keyword Search Select
SELECT business_id,name,street,city,state,zip_code,phone_number,webpage,rating,
category_name,subcategory_name,AVG(rating) AS avg_rating
FROM BUSINESS,CATEGORY,SUBCATEGORY,COMMENT
WHERE BUSINESS.business_id=COMMENT.business_id
AND CATEGORY.category_id=BUSINESS.category_id
AND SUBCATEGORY.subcategory_id=BUSINESS.subcategory_id
AND category_name LIKE '%{search}%' OR subcategory_name LIKE '%{search}%'
OR city LIKE '%{search}%' OR street LIKE '%{search}%' OR
state='{search}' OR zip_code LIKE '%{search}%' OR rating>='{search}'
OR phone_number='{search}' OR webpage LIKE '%{search}%' OR '{search}' IS NULL
GROUP BY name
ORDER BY name;

Retrieve a Business Page
SELECT name,street,city,state,zip_code,phone_number,webpage,
category_name,subcategory_name,rating,comment_text,submitter_name,
submitter_email,AVG(rating) AS avg_rating
FROM BUSINESS,CATEGORY,SUBCATEGORY,COMMENT
WHERE BUSINESS.business_id=COMMENT.business_id
AND CATEGORY.category_id=BUSINESS.category_id
AND SUBCATEGORY.subcategory_id=BUSINESS.subcategory_id
AND BUSINESS.business_id='{business_id}'
ORDER BY rating
GROUP BY name;

Retrieve a Subcategory Page
SELECT business_id,category_name,subcategory_name,name
FROM CATEGORY,SUBCATEGORY,BUSINESS
WHERE BUSINESS.subcategory_id=SUBCATEGORY.subcategory_id
AND CATEGORY.category_id=SUBCATEGORY.category_id
AND BUSINESS.category_id=CATEGORY.category_id
AND category_name='{category}'
ORDER BY category_name, subcategory_name;

Retrieve a Subcategory Selection Box
SELECT subcategory_name
FROM CATEGORY,SUBCATEGORY
WHERE CATEGORY.category_id=SUBCATEGORY.category_id AND category_name='{category}'
ORDER BY category_name, subcategory_name;

Delete a Business (first generate the necessary table)
SELECT business_id,name,street
FROM BUSINESS
ORDER BY name;

DELETE FROM BUSINESS
WHERE business_id='{number_to_delete}';

Delete a Comment (first generate the necessary table)
SELECT BUSINESS.business_id,COMMENT.date_time_stamp,name,street,comment_text
FROM BUSINESS,COMMENT
WHERE BUSINESS.business_ID=COMMENT.business_id
ORDER BY name;

DELETE FROM COMMENT
WHERE business_id='{number_to_delete}' AND date_time_stamp='{date_time_stamp}';

Back to top


Java Code
Java Servlet Classes (all original code)

FSmain Class
FSconnection Class
FSrequest Class
FSqueryString Class

Static HTML Pages (all original code) and Embedded JavaScript Code (borrowed and adapted from The JavaScript Source)
To view the HTML, view the source code of the following pages.

5 Star Home Page
5 Star Advanced Searching Page
5 Star Adding a New Business Page
5 Star Administrator's Login Page

Note: The (non-static) HTML generated by the Java Servlet must be obtained by viewing the source code on any of the pages on the actual 5 Star site.

Back to top



< Back * Contents