Question

You will develop an E-Commerce database used to maintain customers, products and sales information. You are required to 1) gather and analyze requirements 2) design logical structure of the database 3) create stored procedures to develop the tables and insert the data 4) write SQL statements for data extraction and reporting.

Throughout the course of this semester you have analyzed the requirements for an eCommerce database, designed and developed your database. As a class we have gone through the process of writing the DDL scripts to create our database and the DML scripts to load the data. Now that we have a functional database loaded with data, we can start working with our database and performing business functions.

Maintaining the data and reporting are two tasks that are performed daily on all databases. Data is constantly being inserted, updated and deleted. Managers need reports and users execute queries to look up information. We will first take a look at some queries to get an understanding of how our database can be used. Then, you will write your own queries for reporting.

Query 1: upSell
Using data mining techniques and fuzzy logic, we are able to identify products that customers are most likely to purchase based on their purchase history or viewing habits. The upSells table stores the customer ID along with several products and a percentage ranking of how likely they are to purchase those products. The upSells information will be displayed on the Home page and Shopping Cart page to try to increase sales. For example, we can use the following query to select the products to be displayed for upSells.

SELECT Top 3 u.prodID, u.percentage, p.prodName, p.rPrice, p.sPrice FROM upSells u, product p
WHERE u.prodID = p.prodID and u.custID = 215
ORDER BY u.percentage desc;

We want to select the product information and percentage value from our upSells and product tables. An inner join is used to connect these two tables. We filter the results based on the custID so that the customer only sees their upSells recommendations. Finally, the results are ordered by percentage in descending order so that the highest percentage results are displayed first. When applying this query to our actual eCommerce site, we would use a variable in the place of our custID value (i.e. u.custID =@custID). The value for this variable would be passed to our query from the programming code calling our query. Using (and introducing) the “Top #” function in our SELECT statement, we will only display the top three results from the query. For customer 215, our query would produce the following results.

prodID Perc prodName 3132340.82Toshiba Canvio Connect 1TB Portable External Hard Drive63.9663.96 424462 0.81 Jensen JENSEN CD-750 Portable AM FM Stereo CD Play.. 87.06 87.06 rPrice sPrice 508339 0.76 Baekyard Gril etle Charcoal Gr 44.00 44.00

We could also run a similar query for crossSells. The crossSells table would recommend products on the product page based on all customer purchasing habits of that product. The prodID would be used to filter the results based on which product was viewed.

Query 2: Billing and Shipping Address
When the user adds items to their shopping cart and decides to checkout, we need to display their billing and payment options that we have stored in the database. To do this, we should select information from the customer, shippingAddress and paymentInfo tables. If we join these three tables together then we will end up with both the shippingAddress and paymentInfo information stored in a single record and also have duplicate output if multiple shipping addresses or payment methods exist. To solve this problem we will introduce the UNION command. A UNION will combine the results of two or more queries into a single output. For a union to work properly, there must be an even number of attributes in both queries that we are trying to combine. Below is the query to output shipping and billing information following by a detailed description of the query.

SELECT 'Billing Address:', p.billAddress, Null, p.city, p.state, p.zip, ccType, ccNum, ccExpire
FROM customer c, paymentInfo p
WHERE c.custID = p.custID and c.custID = 243

UNION

SELECT 'Shipping Address', s.address1, s.address2, s.city, s.state, s.zip, Null, Null, Null FROM customer c, shippingAddress s
WHERE c.custID = s.custID and c.custID = 243;

The first query will output the Billing Address records stored in the database. We use the ‘Billing Address:’ string at the beginning to output this text on each record to identify it as billing information. The second query will output shipping information and use the string ‘Shipping Address:’ string at the beginning to identify these records as shipping address records in the output. Notice the use of Null in the attributes list of the SELECT statement. The shippingAddress table has an address2 attribute but the paymentInfo table does not. Therefore, the word Null is entered into the SELECT statement to output Null values in order to make the attribute count equal in both queries. The shippingAddress table does not contain credit card information, so Null values where entered into the second query to make the number of attributes match the number of attributes from the first query. Both queries should filter the data based on the custID = @custID (i.e. 243 in our example). Once the two queries have been written with the desired output, we use the word UNION between the two queries. The UNION command will combine the results of both queries into one output. Notice that both queries return the same number of attributes with matching data types. Try copying this UNION in your vLab to see it in action.

Query 3: Fraud Detection
Online companies are always concerned about fraudulent transactions. Most online fraud can be detected by unusually large transactions. While there are other measures of detecting fraud, we will keep it simple and look for any orders that are greater than $1,000. Using our Sum( ) function and filtering based on the aggregate functions, we can use the following query to detect transactions that should be flagged as possible fraud.

SELECT o.OID, o.custID, o.orderDt, sum(oi.qty * oi.price) FROM orders o, orderItems oi
WHERE o.OID = oi.OID
GROUP BY o.OID, o.custID, o.orderDt

HAVING sum(oi.qty * oi.price) > 1000;

Answer 1

DATABASE SCHEMA for the given System ( USE Oracle SQL PLUS):

create table product
(
prodID Number PRIMARY KEY,
prodName varchar2(100) Not Null,
rPrice Number(5,3) Not Null,
sPrice Number(5,3) Not Null
);

create table upSells
(
custID Number Not Null,
prodID Number Not Null,
percentage Number(3,2)
);

create table shippingAddress
(
custID Number Not Null,
Address1 varchar2(100) Not Null,
Address2 varchar2(100) Not Null,
city varchar2(100) Not Null,
state varchar2(100) Not Null,
zip varchar2(100) Not Null
);

create table customer
(
custID Number Not Null,
custName varchar2(100) Not Null
);

create table paymentInfo
(
custID Number Not Null,
billAddress varchar2(100) Not Null,
Address2 varchar2(100) Not Null,
city varchar2(100) Not Null,
state varchar2(100) Not Null,
zip varchar2(100) Not Null,
ccType varchar2(100) Not Null,
ccNum varchar2(100) Not Null,
ccExpire varchar2(100) Not Null
);

INSERTING INTO above-created tables:

INSERT INTO product values (313234, 'Toshiba Canvio Connect', 63.96,63.96);
INSERT INTO product values (424462, 'JENSEN jensen CD-750', 87.06,87.06);
INSERT INTO product values (508339, 'Charcoal Grill', 44.00,44.00);

INSERT INTO shippingAddress values (243, 'Thane bk213', 'Thane bk213' , 'Mumbai','Maharashtra' , '421001');

INSERT INTO customer values (243, 'Mahesh');

INSERT INTO upSells values(212, 313234, 0.82);
INSERT INTO upSells values(212, 424462, 0.61);
INSERT INTO upSells values(215, 313234, 0.82);
INSERT INTO upSells values(215, 424462, 0.61);
INSERT INTO upSells values(215, 508339, 0.76);

PROCEDURE for listing upsells recommendation

CREATE OR REPLACE PROCEDURE Recommendations(

p_custID IN upSells.custID%TYPE

p_ prodID OUT upSells.prodID%TYPE,

p_percentage OUT upSells.percentage%TYPE,

p_prodName OUT product.prodName%TYPE,

p_rPrice OUT product.rPrice%TYPE,

p_sPrice OUT product.sPrice%TYPE

)

IS

X Number(10);

BEGIN

  

select count(*) into X from (SELECT u.prodID, u.percentage, p.prodName, p.rPrice, p.sPrice FROM upSells u, product p

WHERE u.prodID = p.prodID and u.custID = p_custID ORDER BY u.percentage desc) where ROWNUM <= 3 ;

  

If X > 0 THEN

select u.prodID, u.percentage, p.prodName, p.rPrice, p.sPrice into p_prodID, p_percentage, p_prodName, p_rPrice, p_sPrice

from (SELECT u.prodID, u.percentage, p.prodName, p.rPrice, p.sPrice FROM upSells u, product p

WHERE u.prodID = p.prodID and u.custID = p_custID ORDER BY u.percentage desc) where ROWNUM <= 3 ;

else

dbms_output.put_line('Customer with CustID' || p_custID || 'not exist');

endif;

END;

CALLING PROCEDURE:

CREATE OR REPLACE PROCEDURE getRecommendations(

p_custID IN upSells.custID%TYPE

)

IS

p_ prodID upSells.prodID%TYPE,

p_percentage upSells.percentage%TYPE,

p_prodName product.prodName%TYPE,

p_rPrice product.rPrice%TYPE,

p_sPrice product.sPrice%TYPE

BEGIN

  

Recommendations(p_custID, p_prodID, p_percentage, p_prodName, p_rPrice, p_sPrice);

END;

Query 2:

ELECT 'Billing Address:', p.billAddress, Null, p.city, p.state, p.zip, ccType, ccNum, ccExpire
FROM customer c, paymentInfo p
WHERE c.custID = p.custID and c.custID = 243

UNION

SELECT 'Shipping Address', s.address1, s.address2, s.city, s.state, s.zip, Null, Null, Null FROM customer c, shippingAddress s
WHERE c.custID = s.custID and c.custID = 243;

OUTPUT: