Problem 1 Create your database and Images The Problem: This term the topic of yo
ID: 3823384 • Letter: P
Question
Problem 1 Create your database and Images
The Problem: This term the topic of your web site is rummage sales. Your client wants to put the rummage sale online! So your first task is to make a database with a table for the customer information, a table of the clothing items on sale, and a table of categories including clothing clothing, food, sports, accessories, toys, electronics and household.
Note: For the values make sure there are no special characters. You will learn how later to deal with special cases.
Create your database and store it in the App_Data folder. This is important! Your instructor won't be able to view your pages if your data is not stored with the project.
Create the categories table. Enter sample data. You need to have at least 8 categories above (you may add more and make minor changes to the titles of the categories)
Create the products table. You need to have in each category at least 10 products. (you may add more. Each product is required to have a product name, product ID, model number, description, inventory number in stock, cost, price, weight. You cannot just fill in $1.00 for each product. Have different values. Each product needs a unique ID and assigned a category number that matches one in the categories table.
Create a customer table. You need to have at least 20 customers. Each customer must have a first name, last name, street address, city, state, zip code, phone number, birthdate, email address, login name, password, credit card name, credit card type, credit card number, expiration date, and 3 digit code. Each customer needs a unique customer ID.
Create an orders table that lists a unique order ID along with the product ID of the product purchased, the number of items and the customer ID, the price of the item and number of items, the date and time of the purchase, the tax rate used and the total amount of the purchase. Insert data for 10 orders.
Create a log table.We have provided you with a list of the field names and the data types for this table below. In the log table, you will collect the customer ID, their IP address and the session ID. You also need to collect the date and time that they accessed the site and the date and time they left the site. Collect the URL of the page they were trying to access on their first visit so we can know what page is popular. Include fields for the browser and version, and referring web site. Include a field to store their cookie. Insert data for 10 visits. Use your previous homework on cookies, sessions and http server variables to help identify content that is appropriate for each field. Later you will use this information in another homework assignment.
Images
For now, don't store the image in the database. If you do, you have to write more code to retrieve the images and the database will be much larger.
You will need pictures for your categories and products.
Please select some that are in public domain and store them in your Images folder. You may also store them in a subdirectory within the Images folder. Use only GIF, PNG or JPG formats. Make sure the file names have no spaces or special characters.
Problem 2 Use SQL to Create Stored Procedures
You know that many queries you will need to complete in your web application can be stored with the database.
Step 1 Review Creating Stored Procedures
Recall that you can create SQL statements and retain them in the database as a stored procedure. Here are examples. It's up to you to create them!
This stored procedure one just retrieves the record.
This stored procedure deletes a record so it needs an input parameter to identify which record(s) to delete.
Step 1 Create Stored Procedures to insert and update data
Create these three stored procedures as practice. When you are done, click the update button to store the statement in the database.
What is new here is you return the identity value using Scope_Identity.
TIP: If you have a local server or use parallel processing, retrieving the scope_identity can be a problem. You can use the output clause if your scope_identify is constantly showing you 0 or 1. Basically this means putting the output variable into a table or table variable. Then do not use ExecuteNonQuery which returns an integer, but use the executeScalar method returns an object. For now, just use Scope_Identity!
Create the stored procedure to retrieve the logid. Notice here the [ ] are used for the Log so it's not confusing the application with another log.
Create the stored procedure to update the log table.
Step 1 Create the Stored Procedures
Now it's your turn!!!
Create stored procedures below. Show the stored procedure and the data returned in your Word document. There are quite a few but you will use these later when you build the web site.
Return a list of all categories, sorted by category ID
Return a list of all products, sorted by product ID
Return a list of all orders, sorted by order ID
Return a list of all customers sorted by customer ID
Return a list of all log file activities sorted by log ID
Return a list of all products in a specific category, given a category ID as an input parameter
Return a List of all customers sorted by Last name, only returning the last name, first name and customer ID values
Return all the fields for a specific customers given a customer ID as an input parameter
Return all the orders for a specific customers given a customer ID as an input parameter
Return all the order information for a specific order, given the order ID as an input parameter
Return a list of all orders sorted by total amount of the purchase.
Return a list of all products that have been ordered, and show the total profit (Price - cost), sorted by the profit in descending order
Return a list of all products sorted based on the profit
Return a list of web sites that had links to your site, that the customers clicked on to access your site, sorted by the number of times they were referred. (You will get this information from the server variables later in the course)
Return a total number of visitors on the web site, based on the log table
Problem 3 Create Stored Procedures to Maintain Your Database
You know that many queries you will need to complete in your web application can be stored with the database.
Create the stored procedures below. Show the stored procedure and the data returned in your Word document. There are quite a few but you will use these later when you build the web site.
Insert a new category into the categories table (make sure to pass the values as input parameters)
Insert a new product into the products table (make sure to pass the values as input parameters)
Insert a new customer into the customers table (make sure to pass the values as input parameters)
Insert a new log file into the log table (make sure to pass the values as input parameters)
Insert a new order into the orders table (make sure to pass the values as input parameters)
Delete an order in the orders table, given the orderID.
Delete a product given a productID
Delete a customer given a customerID
Delete a log entry given a customerID
Delete a category given a category ID
Explanation / Answer
due to time contraint i am not able to answer all the questions
Create the categories table. Enter sample data. You need to have at least 8 categories above (you may add more and make minor changes to the titles of the categories)
create table categories(
categoryID number,
categoryName varchar2(100),
categoryDesc varchar2(100)
);
insert into categories values(1,'MShirts','Mens Shirts');
insert into categories values(2,'WShirts','womens Shirts');
insert into categories values(3,'KShirts','Kids Shirts');
insert into categories values(4,'MTShirts','Mens TShirts');
insert into categories values(5,'WTShirts','Womens Shirts');
insert into categories values(6,'KTShirts','Kids Shirts');
insert into categories values(7,'KPants','Kids Pants');
insert into categories values(8,'MPants','Mens Pants');
Create the products table. You need to have in each category at least 10 products. (you may add more. Each product is required to have a product name, product ID, model number, description, inventory number in stock, cost, price, weight. You cannot just fill in $1.00 for each product. Have different values. Each product needs a unique ID and assigned a category number that matches one in the categories table.
create table product(
productId number,
productName varchar2(100),
modelNumber varchar2(100),
description varchar2(100),
stock number,
cost number,
price number,
weight number,
categoryId number,
primary key(productId),
foreign key (categoryId) references categories(categoryID)
);
insert into product values(1,'mens shirts','M001','contains mens shirts',10,1200,1200,200,1);
insert into product values(2,'kids shirts','M002','contains mens shirts',8,1600,1800,200,2);
insert into product values(3,'womens shirts','M003','contains mens shirts',9,1300,1300,200,3);
insert into product values(4,'kids Tshirts','M004','contains mens shirts',13,1400,1400,200,4);
insert into product values(5,'mens tshirts','M005','contains mens shirts',15,1500,1500,200,5);
insert into product values(6,'womens tshirts','M006','contains mens shirts',13,1206,1200,200,1);
insert into product values(7,'kids pants','M007','contains mens shirts',18,1270,1270,200,2);
insert into product values(8,'mens pants','M008','contains mens shirts',17,1280,1280,200,3);
insert into product values(9,'mens tshirts','M009','contains mens shirts',14,1290,1290,200,4);
insert into product values(10,'mens trousers','M010','contains mens shirts',13,1000,1000,200,5);
Create a customer table. You need to have at least 20 customers. Each customer must have a first name, last name, street address, city, state, zip code, phone number, birthdate, email address, login name, password, credit card name, credit card type, credit card number, expiration date, and 3 digit code. Each customer needs a unique customer ID.
create table customer(
firstname varchar2(100),
lastname varchar2(100),
streetAddress varchar2(100),
city varchar2(100),
state varchar2(100),
zipCode varchar2(100),
phoneNumber number,
birthdate date,
emailaddress varchar2(100),
loginname varchar2(100),
password varchar2(100),
creditcardname varchar2(100),
cardtype varchar2(100),
epirydate date,
code number(3),
custId number,
primary key(custId)
);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,1);
insert into customer values('de','sa','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,2);
insert into customer values('ABdC','dsf','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,3);
insert into customer values('sdfdcxcx','xcxz','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,4);
insert into customer values('cvxv','DfEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,5);
insert into customer values('fggt','ml','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,6);
insert into customer values('bdsjdj','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,7);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,8);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,9);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,10);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,11);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,12);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,13);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,14);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,15);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,16);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,17);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,18);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,19);
insert into customer values('ABC','DEF','stree2','NY', 'US','222',123456,'21-jun-1978','hsha@hjsa.com','avcdes','123321','abcdef',12345789012,'21-jun-2017',123,20);
Create an orders table that lists a unique order ID along with the product ID of the product purchased, the number of items and the customer ID, the price of the item and number of items, the date and time of the purchase, the tax rate used and the total amount of the purchase. Insert data for 10 orders.
create table orders(
orderId number,
productId number,
productPurchased varchar2(100),
numberOfItems number,
custId number,
itemprice number,
purchasedate date,
taxrate number,
totalamount number,
primary key(orderID),
foreign key (productId) refrences Products(productId),
foreign key (custId) refrences customer(custId),
)
insert into order values(1,1,'jeans',2,1,1200,'21-Apr-2017',12.5,7800);
insert into order values(2,9,'polo tshitrs',2,2,1200,'21-Apr-2017',12.5,6800);
insert into order values(3,4,'shoes',2,5,1200,'21-Apr-2017',12.5,7800);
insert into order values(4,6,'slippers',2,5,1200,'21-Apr-2017',12.5,7800);
insert into order values(5,2,'shirts',2,7,1200,'21-Apr-2017',12.5,7800);
insert into order values(6,3,'UCB',2,8,1200,'21-Apr-2017',12.5,7800);
insert into order values(7,7,'jack and jones',2,9,1200,'21-Apr-2017',12.5,7800);
insert into order values(8,9,'puma shoes',2,10,1200,'21-Apr-2017',12.5,7800);
insert into order values(9,4,'puma night suit',2,11,1200,'21-Apr-2017',12.5,7800);
insert into order values(10,6,'socks',2,14,1200,'21-Apr-2017',12.5,7800);
insert into order values(11,4,'jeans',2,19,1200,'21-Apr-2017',12.5,7800);
Create a log table.We have provided you with a list of the field names and the data types for this table below. In the log table, you will collect the customer ID, their IP address and the session ID. You also need to collect the date and time that they accessed the site and the date and time they left the site. Collect the URL of the page they were trying to access on their first visit so we can know what page is popular. Include fields for the browser and version, and referring web site. Include a field to store their cookie. Insert data for 10 visits. Use your previous homework on cookies, sessions and http server variables to help identify content that is appropriate for each field. Later you will use this information in another homework assignment.
create table logs(
custId number,
ipaddress varchar2(100),
sessionId varchar2(100),
Login timestamp,
logout timestamp,
browser varchar2(100),
version varchar2(100),
cookie varchar2(100)
);
insert into logs values(1,'192.168.32.43','sdc12121',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'chrome', '2.2','adsdsdfere32');
insert into logs values(20,'192.168.32.43','krk4',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'safari', '2.2','adsdsdfere32');
insert into logs values(19,'192.168.22.13','ktkr;4',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'IE', '2.2','adsdsdfere32');
insert into logs values(18,'192.168.52.43','kgk4',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'mozilla', '2.2','adsdsdfere32');
insert into logs values(17,'192.168.62.43','grl;p',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'firefox', '2.2.0.01','adsdsdfere32');
insert into logs values(3,'192.168.42.43','l,g;rl;lo',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'chrome', '2.2.1.1','adsdsdfere32');
insert into logs values(6,'192.168.22.43',',b,f,gf',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'mozilla firefox', '2.2.32.2.1','adsdsdfere32');
insert into logs values(8,'192.168.30.43','69506itok',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'demo1', '2.2.32.32','adsdsdfere32');
insert into logs values(9,'192.168.32.30','u4ujfij',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'demo2', '2.1.43.2.2','adsdsdfere32');
insert into logs values(10,'192.168.32.3','ut4uio',21-APr-2017 14:09:90,21-Apr-2017 15:09:90,'demo3', '2.2.32','adsdsdfere32');
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.