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.

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.

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.