Transcript E-Commerce 3

CHAPTER4
Designing Database
E-Commerce
Hassanin M. Al-Barhamtoshy
[email protected]
How This Chapter Is Structured
The main topics we’ll touch on in this chapter are:
1. Analyzing the requirement of the database and
the functionality it should support
2. Creating the database structures for the
application
3. Implementing the business tier objects required
to make the system run, and putting a basic but
functional error-handling strategy in place
4. Implementing a functional UI for the system
What Does a Product Catalog Look Like?
 We need the following file entities:
 Products
 Categories
 Customers
 Orders
Database Designing
The major tables in e-Commerce database,
as the following:
1- Products Table
Name
Category Name
Description
Vendor name
Vendor address
Vendor phone number
Price
Image file name
Database Designing
2- Category
Name
3- Customers
Last Name
First Name
Address
City
State
Zip Code
Phone Number
E-mail
Credit Card Number
Database Designing
4- Order
Order number
Date
Customer
Product
Quantity
Price
Subtotal
Shipping
Tax
Total
• After the key columns have been added, the list
looks like this:
Products
Product ID (primary key)
Name
Category ID (foreign key)
Category Name
Description
Price
Image file name
Vendor ID (foreign key)
Vendor
Vendor ID (primary key)
Name
Address
City
State
Zip Code
Phone Number
E-mail
Category
Category ID (primary key)
Name
Customers
Last Name
First Name
Address
City
State
Zip Code
Phone Number
E-mail (primary key)
Credit Card Number
Order
Order number (primary key)
Date
Order
Order number (primary key)
Date
Customer ID (foreign key)
Product ID (foreign key)
Quantity
Price
Subtotal
Shipping
Tax
Total
Entity Relation Database
Building a Product Catalog Application
This application’s user interface has just three
pages:
1. Default.aspx displays a list of products for a
category selected by the user.
2. Product.aspx displays details about a specific
product selected by the user.
3. Cart.aspx is displayed when the user chooses to
purchase a product.
The user interface for the Product Catalog application.
The Product List page.
The Product Detail page.
The Cart page
Designing the Product Database
It consists of three tables:
Categories
Products
FeaturedProducts
The Categories table
The Products table
The Featured Products table
Creating the database
sqlcmd -S localhost\SQLExpress -i CreateProducts.sql
The CreateProducts.sql script
USE master
GO
IF EXISTS(SELECT * FROM sysdatabases
WHERE name=’Products’)
DROP DATABASE Products
GO
CREATE DATABASE Products ➝3
ON (NAME= Product, FILENAME= ‘C:\APPS\Products.mdf’,
SIZE=10 )
GO
Creating the database
USE Products
GO
CREATE TABLE Categories ( catid VARCHAR(10) NOT NULL,
name VARCHAR(50) NOT NULL, [desc] VARCHAR(MAX) NOT NULL,
PRIMARY KEY(catid) )
GO
CREATE TABLE Products (productid VARCHAR(10) NOT NULL,
catid VARCHAR(10) NOT NULL, name VARCHAR(50) NOT NULL,
shorttext VARCHAR(MAX) NOT NULL, longtext VARCHAR(MAX) NOT NULL,
price MONEY NOT NULL, thumbnail VARCHAR(40) NOT NULL,
image VARCHAR(40) NOT NULL, PRIMARY KEY(productid),
FOREIGN KEY(catid) REFERENCES Categories(catid) )
GO
Creating the database
CREATE TABLE FeaturedProducts (
productid VARCHAR(10) NOT NULL,
featuretext VARCHAR(MAX) NOT NULL,
saleprice MONEY NOT NULL,
PRIMARY KEY(productid),
FOREIGN KEY(productid) REFERENCES Products(productid)
)
GO
Adding Logic to the Site
The following paragraphs describe the highlights of this script:
1. Sets the database context to master. This is usually the default
context, but it’s a good idea to set it just in case.
2. Deletes the existing Products database if it exists.
3. Creates a database named Products. The database file will be
created in the C:\Apps directory. You should change this
location if you want to place the database file in a different
folder.
4. Creates the Categories table.
5. Note that the column name desc is a SQL keyword, so it must
be enclosed in brackets.
6. Creates the Products table.
7. Creates the FeaturedProducts table.
Adding some test data
sqlcmd -S localhost\SQLExpress -i InsertProducts.sql
• Once again, you’ll need to change the server name if
you’re not running SQL Server Express on your own
computer.
• You’ll know the script works if you see a series of
messages like this one: (1 rows affected)
Adding some test data
Querying the database
The application must perform the following queries:
1. Retrieve all rows from the Categories table to fill the
drop-down list on the Default.aspx page so the user can
select a product.
2. Retrieve all rows from the FeaturedProducts table to
display at the top of the Default.aspx page. Note that
some data is also required from the Products table, so
this query requires a join.
3. Retrieve all products for a given category, including the
sale price indicated in the FeaturedProducts table.
4. Retrieve all data for a specified product to display on the
Product.aspx page. Note that this query must also
retrieve the sale price from the FeaturedProducts table.
Querying the database
• The query to retrieve all rows from the
Categories table uses this SQL statement:
SELECT catid, name,
[desc]
FROM Categories
ORDER BY name
Querying the database
• The query to retrieve the featured product rows
requires a join to retrieve data from the
FeaturedProducts table as well as the Products table:
SELECT FeaturedProducts.productid,
FeaturedProducts.featuretext,
FeaturedProducts.saleprice,
Products.name,
Products.price
FROM FeaturedProducts
INNER JOIN Products
ON FeaturedProducts.productid = Products.productid
Querying the database
•
The query to retrieve the products for a given category also
requires a join:
SELECT Products.productid,
Products.catid,
Products.name,
Products.shorttext,
Products.longtext,
Products.price,
Products.image,
Products.thumbnail,
FeaturedProducts.saleprice
FROM Products
LEFT OUTER JOIN FeaturedProducts
ON Products.productid = FeaturedProducts.productid
WHERE (Products.catid = @catid)
Querying the database
•
The last query used by the program retrieves the data for a
specific product:
SELECT Products.productid,
Products.catid,
Products.name,
Products.shorttext,
Products.longtext,
Products.price,
Products.image,
FeaturedProducts.saleprice,
FeaturedProducts.featuretext
FROM Products
LEFT OUTER JOIN FeaturedProducts
ON Products.productid = FeaturedProducts.productid
WHERE (Products.productid = @productid)”
Connecting to the database
The connection string used to access the Products database is
stored in the application’s web.config file, like this:
<connectionStrings>
<add name=”ConnectionString”
connectionString=”Data
Source=localhost\SQLExpress;
Initial Catalog=Products;Integrated Security=True”/>
</connectionStrings>
Querying the database
•
The last query used by the program retrieves the data for a
specific product:
SELECT Products.productid,
Products.catid,
Products.name,
Products.shorttext,
Products.longtext,
Products.price,
Products.image,
FeaturedProducts.saleprice,
FeaturedProducts.featuretext
FROM Products
LEFT OUTER JOIN FeaturedProducts
ON Products.productid = FeaturedProducts.productid
WHERE (Products.productid = @productid)”
Downloading the Code
• Once you download the code, just decompress it
with your favorite compression tool. Alternately,
you can go to the main Wrox code download
page at:
www.wrox.com/dynamic/books/download.aspx
to see the code available for this book and all
other Wrox books.
• You can also download the examples from the Wrox
Books Web site at:
http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0764588079.html
Quiz # 2
• Design an ERD (Entity Relation Database)
for e-Learning Application), includes the
following:
• E-Learning Table names.
• Tables Structure.
• Tables Relation.