Pet Shop Database design of Pets store __ Database

Database specific SQL query file contents are as follows:

Create Database Petstore;

Use Petstore

CREATE TABLE Client (

Username varchar NOT NULL,

passwd varchar NOT NULL,

Constraint Pk_client primary KEY (username)

);

CREATE TABLE Account (

UserID varchar NOT NULL,

Email varchar NOT NULL,

FullName varchar () NOT NULL,

Country varchar is not NULL,

City varchar is not NULL,

Address varchar is not NULL,

Zip varchar NOT NULL,

Phone varchar NOT NULL,

Constraint Pk_account primary KEY (UserID),

Constraint Fk_account foreign key (userid)

References Client (username)

)

CREATE TABLE category (

CatID char (TEN) NOT NULL,

CatName varchar () NULL,

DESCN varchar (255) NULL,

Constraint Pk_category primary key (CATID)

)

CREATE TABLE Product (

ProductID Char (TEN) NOT NULL,

CatID char (TEN) NOT NULL,

Name varchar () NULL,

DESCN varchar (255) NULL,

Pic varchar (255) NULL,

ListPrice decimal (10,2) NULL,

UnitCost decimal (10,2) NULL,

Qty int NOT NULL,

Constraint Pk_product primary KEY (ProductID),

Constraint fk_product_1 foreign key (CATID)

References category (CATID)

)

CREATE TABLE Orders (

OrderID int NOT NULL auto_increment,

UserID varchar NOT NULL,

OrderDate date NOT NULL,

Ordertime time is not NULL,

Shiptoname varchar () NOT NULL,

ShipCountry varchar is not NULL,

ShipCity varchar () NOT NULL,

ShipAddress varchar () NOT NULL,

Shipzip varchar is not NULL,

Shipphone varchar () NOT NULL,

Billtoname varchar () NOT NULL,

Billcountry varchar is not NULL,

billcity varchar () NOT NULL,

billaddress varchar () NOT NULL,

Billzip varchar is not NULL,

Billphone varchar () NOT NULL,

Totalprice Numeric (10,2) NOT NULL,

CreditCard varchar () NOT NULL,

Cardtype varchar () NOT NULL,

Constraint Pk_orders PRIMARY KEY (OrderID),

Constraint fk_orders_1 foreign key (userid)

References account (userid)

)

CREATE TABLE LineItem (

OrderID int NOT NULL,

Itemid char (TEN) NOT NULL,

Quantity int NOT NULL,

UnitPrice Numeric (10,2) NOT NULL,

Constraint Pk_lineitem primary KEY (Orderid,itemid),

Constraint fk_lineitem_1 foreign KEY (OrderID)

References orders (OrderID),

Constraint fk_lineitem_2 foreign key (itemid)

References Product (ProductID)

)

Business logic:

1, User registration: Enter user name, password, mailbox, full name, country, city, address, postal code, telephone registration after the success can be classified by Product browsing site

2, Product Management: For administrators to use, administrators can increase product classification, as well as for each category to add products, including product name, product description, market price, current prices, quantity

3, the user orders the pet: When the user value a pet, may join the user's shopping cart (realizes with the session) when the user shopping cart pet Choice completes, may carry on the reservation, the schedule relates to the table for orders,lineitem,product. The Orders table will be required to fill out the bill to the department, the goods sent to the place, as well as the scheduled date, the total price, the bank card number and type.