Server-Side Web Programming with Active Server Pages
CSC123 "Shop" Database

During the course of this class you will be using the database below for SQL exercises, and in the latter half of the class, as the basis of the ASP shopping application you will build.  If you have questions or comments please send them to 

ERD, Data Dictionary, Site Map

HINT: If you have trouble with connecting your site to the database try this DSN instead:
    Provider=SQLOLEDB;Data Source=;Initial Catalog=Shop;User ID=class;Password=class;

Revised ERD 


Data Dictionary

This is used to describe the function of each field. For types and sizes refer to diagram above.

FieldName Tables Note
Address Customer,Sale, Sic.
CatalogID Catalog,Product, PK of Catalog. Catalog number that matches the class #
Category Product, User defined (optional) Product Type
City Customer,Sale, Sic.
CreditCardExp Customer,Sale, When does credit card expire (mm/yyyy)
CreditCardNum Customer,Sale, Credit Card `Number` (actually a string)
CreditCardType Customer,Sale, Use a dropdown (Visa, MC, AMEX, DISC)
CustomerID Customer,Sale, PK of customer table. Stored in the cookie
Email Customer, Sic.Used to login to Shop. Must contain an @ and a .
Extension Customer, Phone Extension (if any) not required.
Fax Customer, Fax number is optional
FreightCharge Sale, Total Shipping and Handling Charged
InStock Product, Quantity of the item in stock +Number mean you have some, - numbers indicate the item is on back order or unavailable
LineTotal SaleDetail, Quantity * Price + Sales Tax
Name Catalog, Of your site or catalog
NameFirst Customer,Sale, Sic
NameLast Customer,Sale, Sic.
Pending Sale, 0=Order has been shipped, -1 = this is the shopping cart. Each customer should only have 1 pending order.
Phone Customer, Sic.
Price Product, In US Dollars
ProductDescription Product, Sic
ProductID Product,SaleDetail, PK of product table
ProductName Product, Sic.
Quantity SaleDetail, Amount of item customer ordered
SaleDate Sale, Date of Sale (today)
SaleDetailID SaleDetail, PK of SaleDetail Table, uniquely identifies a line item
SaleID Sale,SaleDetail, PK of Sale Table = Order # = Shopping Cart #
SalePrice SaleDetail, (Unused except for extra credit) Used to show discounted price
SalesTax SaleDetail, Total for this line
SalesTaxRate Sale, Rate of sales tax (Default: 8%)
ShipDate Sale, Date order is shipped, some number of days after SaleDate
State Customer,Sale, Sic
UnitPrice SaleDetail, Cost/Each = Product.Price
Voided Catalog,Customer,Product,Sale,SaleDetail, (Unused) (Normally for deleted records)
ZIP Customer,Sale, Sic


Shop Site Map/Page Inventory

These are the required pages

Page Name Minimum Navigation Lab Assigned
Checkout.Asp From main navigation menu 13
Customer.Asp From main navigation menu, Login.asp 10
Default.Asp From main navigation menu 8
Engine.Asp From Product, Orders, Checkout 12
Error.Asp (Various) (optional)
Help.Asp From main navigation menu 13
Login.Asp From main navigation menu, From inside ValidateUser() function, Customer, Default 11
Orders.Asp From main navigation menu 12
Product.Asp Search, (optionally other places) 8, 12
Search.Asp From main navigation menu, (optionally other places) 9
includes/header.[asp|htm] N/A Used in all pages 8
includes/footer.[asp|htm] N/A Used in all pages 8
includes/{your code library goes here}.[asp|htm] N/A Used in all pages (all)


This map show the major call paths between pages. 



Valtara Digital Design   
Copyright 1999, 2001, Valtara Digital Design, Blitzkrieg Software