The IBuySpy Store application uses SQL Server as the database for its records. In total, there are seven tables that contain the store's information. Following is a description of each table.
Categories This table contains the names of each category. It has only two fields: CategoryID and CategoryName. CategoryID is a primary key (and also an identity column), and CategoryName contains the name of the category, such as Communications and Deception.
Customers This table contains information about customers. It has four fields: CustomerID, FullName, EmailAddress, and Password. CustomerID is a primary key (and also an identity column); and FullName, EmailAddress, and Password contain the user's information.
OrderDetails This table contains information about order items for orders that have been placed. It has four fields: OrderID, ProductID, Quantity, and UnitCost. OrderID is an integer key that uniquely identifies the order number; ProductID is an integer key that uniquely identifies the product number; Quantity is an integer that contains the number of this particular product that was ordered; and UnitCost is a money field that contains the price of the item at the time that the order was placed.
Orders This table contains information about a specific order. It has four fields: OrderID, CustomerID, OrderDate, and ShipDate. OrderID is an integer key (and also an identity column) that uniquely identifies the order number; the CustomerID contains the ID of the customer who placed the order; the OrderData field contains the date that the order was place (and is automatically assigned when a row is created by using getdate() for the default value); and the ShipDate field contains the date that the order was shipped (and is automatically assigned when a row is created by using getdate() for the default value).
Products This table contains information about a specific product. It has seven fields: ProductID, CategoryID, ModelNumber, ModelName, ProductImage, UnitCost, and Description. ProductID is an integer key that uniquely identifies the product (and is an identity key); CategoryID is an integer field that identifies the category; ModelNumber is a text string that contains the product model number; ModelName is a text string that contains the product name; ProductImage contains the image file name that is displayed; UnitCost contains the cost for a single product; and Description is a text description of the product.
Reviews This table contains user/customer reviews for each product. It has six fields: ReviewID, ProductID, CustomerName, CustomerEmail, Rating, and Comments. ReviewID is an integer key that uniquely identifies the review (and also an identity column); ProductID is an integer that associates the review with a product; CustomerName contains the name of the customer; CustomerEmail contains the email of the customer; Rating is an integer that contains the rating value that the customer assigned the product; and Comments contains the text that the customer entered when reviewing the product.
ShoppingCart This table contains the shopping cart contents for customers. It has five fields: RecordID, CartID, Quantity, ProductID, and DateCreated. RecordID is an integer key that uniquely identifies the shopping cart record (and is an identity column); CartID contains the name of the cart; Quantity contains the number of a particular product that's in the shopping cart; ProductID identifies the product that's in the cart for this record; and DateCreated contains the date that this shopping cart record was created (and is automatically assigned when a row is created by using getdate() for the default value).
The relationships between the tables can be seen on Figure 8.3.
