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.

logid = INT customerid = INT authuser = NVARCHAR (200) authpassword = NVARCHAR (50) ipddress = NVARCHAR (50) sessionid = NVARCHAR (200) datevisitbegin = DATETIME datevisitended = DATETIME browser = NVARCHAR (50) browserversion = NVARCHAR (50) useragent = NVARCHAR (MAX) platformname = NVARCHAR (200) supportcookies = NVARCHAR (50) supportjavascript = NVARCHAR (50) inputtype = NVARCHAR (50) displayheight = NVARCHAR (50) displaywidth = NVARCHAR (50) displaybitsperpixel NVARCHAR (50) mobiledevice = NVARCHAR (50) mobilemanufacturer NVARCHAR (50) mobilemodel = NVARCHAR (50) referrer = NVARCHAR (MAX) pathinfo = NVARCHAR (MAX) firstpage = NVARCHAR (MAX) querystring = NVARCHAR (MAX) cookie = NVARCHAR (MAX) httpcookie = NVARCHAR (MAX) httpvariables= NVARCHAR (MAX) 

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.

CREATE PROCEDURE [dbo].DisplayCategories AS SELECT [CategoryID], [CategoryName], [Description], [Picture] FROM [Categories] Order By CategoryID RETURN 0 

This stored procedure deletes a record so it needs an input parameter to identify which record(s) to delete.

CREATE PROCEDURE [dbo].DeleteCategories @pCategoryID int AS DELETE FROM Categories WHERE CategoryID = @pCategoryID RETURN 0 

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.

CREATE PROCEDURE [dbo].[InsertLogSessionStart] @customerid INT = 0 , @authuser NVARCHAR(200) = NULL, @authpassword NVARCHAR(50) = NULL, @ipddress NVARCHAR(50), @sessionid NVARCHAR(200), @datevisitbegin DATETIME = NULL, @datevisitended DATETIME = NULL, @browser NVARCHAR(50) = NULL, @browserversion NVARCHAR(50) = NULL, @useragent NVARCHAR(MAX) = NULL, @platformname NVARCHAR(200) = NULL, @supportcookies NVARCHAR(50) = NULL, @supportjavascript NVARCHAR(50) = NULL, @inputtype NVARCHAR(50) = NULL, @displayheight NVARCHAR(50) = NULL, @displaywidth NVARCHAR(50) = NULL, @displaybitsperpixel NVARCHAR(50) = NULL, @mobiledevice NVARCHAR(50) = NULL, @mobilemanufacturer NVARCHAR(50) = NULL, @mobilemodel NVARCHAR(50) = NULL, @pathinfo NVARCHAR(MAX) = NULL, @firstpage NVARCHAR(MAX) = NULL, @querystring NVARCHAR(MAX) = NULL, @cookie NVARCHAR(MAX) = NULL, @httpcookie NVARCHAR(MAX) = NULL, @httpvariables NVARCHAR(MAX) = NULL, @referrer NVARCHAR(MAX) = NULL AS INSERT INTO Log ( customerid, authuser, authpassword, ipddress, sessionid, datevisitbegin, datevisitended, browser, browserversion, useragent, platformname, supportcookies, supportjavascript, inputtype, displayheight, displaywidth, displaybitsperpixel, mobiledevice, mobilemanufacturer, mobilemodel, pathinfo, firstpage, querystring, cookie, httpcookie, httpvariables, referrer) VALUES ( @customerid, @authuser, @authpassword, @ipddress, @sessionid, @datevisitbegin, @datevisitended, @browser, @browserversion, @useragent, @platformname, @supportcookies, @supportjavascript, @inputtype, @displayheight, @displaywidth, @displaybitsperpixel, @mobiledevice, @mobilemanufacturer, @mobilemodel, @pathinfo, @firstpage, @querystring, @cookie, @httpcookie, @httpvariables, @referrer) RETURN Scope_Identity() 

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 PROCEDURE [dbo].[GetLogID] @sessionid NVARCHAR(200) AS SELECT logid FROM [Log] WHERE sessionid = @sessionid RETURN 

Create the stored procedure to update the log table.

CREATE PROCEDURE [dbo].[UpdateLogSessionEnd] @logid int, @customerid int, @sessionid NVARCHAR(200), @datevisitended DATETIME AS UPDATE Log SET customerid = @customerid, datevisitended = @datevisitended WHERE @sessionid = sessionid or @logid = logid RETURN @@Identity 

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