📄 petshop_schema.sql
字号:
/******************************************************************************
** File: PetShop_Schema.sql
** Name: Physical Database Schema SQL Script
** Desc: This script will create the .NET PetShop database schema
**
** Date: 11/9/2001
**
*******************************************************************************/
USE master;
-- create the database
IF NOT EXISTS (SELECT dbid FROM sysdatabases WHERE name = 'petshop')
CREATE DATABASE petshop;
GO
-------------------------------------------------------------------------------
--
-- Database Tables
--
-------------------------------------------------------------------------------
-- switch to the .NET PetShop database
USE petshop;
GO
-------------------------------------------------------------------------------
-- Supplier Table
-------------------------------------------------------------------------------
CREATE TABLE Supplier
(
suppid int NOT NULL,
[name] varchar(80) NOT NULL,
status char(2) NOT NULL,
addr1 varchar(80) NULL,
addr2 varchar(80) NULL,
city varchar(80) NULL,
state varchar(80) NULL,
zip char(5) NULL,
phone varchar(80) NULL
);
-- add the primary key constraints
ALTER TABLE Supplier ADD
CONSTRAINT PK_Supplier
PRIMARY KEY CLUSTERED (suppid);
-- grant access
GRANT ALL ON Supplier TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Orders Table
-------------------------------------------------------------------------------
CREATE TABLE Orders
(
orderid int IDENTITY NOT NULL,
userid varchar(80) NOT NULL,
orderdate datetime NOT NULL,
shipaddr1 varchar(80) NOT NULL,
shipaddr2 varchar(80) NULL,
shipcity varchar(80) NOT NULL,
shipstate varchar(80) NOT NULL,
shipzip varchar(20) NOT NULL,
shipcountry varchar(20) NOT NULL,
billaddr1 varchar(80) NOT NULL,
billaddr2 varchar(80) NULL,
billcity varchar(80) NOT NULL,
billstate varchar(80) NOT NULL,
billzip varchar(20) NOT NULL,
billcountry varchar(20) NOT NULL,
courier varchar(80) NOT NULL,
totalprice numeric(10, 2) NOT NULL,
billtofirstname varchar(80) NOT NULL,
billtolastname varchar(80) NOT NULL,
shiptofirstname varchar(80) NOT NULL,
shiptolastname varchar(80) NOT NULL,
creditcard varchar(80) NOT NULL,
exprdate char(7) NOT NULL,
cardtype varchar(80) NOT NULL,
locale varchar(20) NOT NULL
);
-- add the primary key constraints
ALTER TABLE Orders ADD
CONSTRAINT PK_Orders
PRIMARY KEY CLUSTERED (orderid);
-- grant access
GRANT ALL ON Orders TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- OrderStatus Table
-------------------------------------------------------------------------------
CREATE TABLE OrderStatus
(
orderid int NOT NULL,
linenum int NOT NULL,
[timestamp] datetime NOT NULL,
status char(2) NOT NULL
);
-- add the primary key constraints
ALTER TABLE OrderStatus ADD
CONSTRAINT PK_OrderStatus
PRIMARY KEY CLUSTERED (orderid);
-- add the foreign key constraints
ALTER TABLE OrderStatus ADD
CONSTRAINT FK_OrderStatus_Orders FOREIGN KEY (orderid)
REFERENCES Orders (orderid);
-- grant access
GRANT ALL ON OrderStatus TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Category Table
-------------------------------------------------------------------------------
CREATE TABLE Category
(
catid char(10) NOT NULL,
[name] varchar(80) NULL,
descn varchar(255) NULL
);
-- add the primary key
ALTER TABLE Category ADD
CONSTRAINT PK_Category
PRIMARY KEY CLUSTERED (catid);
-- grant access
GRANT ALL ON Category TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Product Table
-------------------------------------------------------------------------------
CREATE TABLE Product
(
productid char(10) NOT NULL,
category char(10) NOT NULL,
[name] varchar(80) NULL,
descn varchar(255) NULL
);
-- add the primary key constraints
ALTER TABLE Product ADD
CONSTRAINT PK_Product
PRIMARY KEY CLUSTERED (productid);
-- add the foreign key constraints
ALTER TABLE Product ADD
CONSTRAINT FK_Product_Category FOREIGN KEY (category)
REFERENCES category (catid);
-- grant access
GRANT ALL ON Product TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Signon Table
-------------------------------------------------------------------------------
CREATE TABLE Signon
(
username varchar(80) NOT NULL,
password varchar(25) NOT NULL,
);
-- add the primary key constraints
ALTER TABLE Signon ADD
CONSTRAINT PK_Signon
PRIMARY KEY CLUSTERED (username);
-- grant access
GRANT ALL ON Signon TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- BannerData Table
-------------------------------------------------------------------------------
CREATE TABLE BannerData
(
favcategory varchar(80) NOT NULL,
bannername varchar(80) NULL
);
-- add the primary key
ALTER TABLE BannerData ADD
CONSTRAINT PK_BannerData
PRIMARY KEY CLUSTERED (favcategory);
-- grant access
GRANT ALL ON BannerData TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Profile Table
-------------------------------------------------------------------------------
CREATE TABLE Profile
(
userid varchar(80) NOT NULL,
langpref varchar(80) NOT NULL,
favcategory varchar(80) NULL,
mylistopt int NULL,
banneropt int NULL
);
-- add the primary key constraints
ALTER TABLE Profile ADD
CONSTRAINT PK_Profile
PRIMARY KEY CLUSTERED (userid);
-- add the foreign key constraints
ALTER TABLE Profile ADD
CONSTRAINT FK_Profile_BannerData FOREIGN KEY (favcategory)
REFERENCES BannerData (favcategory);
-- grant access
GRANT ALL ON Profile TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Account Table
-------------------------------------------------------------------------------
CREATE TABLE Account
(
userid varchar(80) NOT NULL,
email varchar(80) NOT NULL,
firstname varchar(80) NOT NULL,
lastname varchar(80) NOT NULL,
status char(2) NULL,
addr1 varchar(80) NOT NULL,
addr2 varchar(40) NULL,
city varchar(80) NOT NULL,
state varchar(80) NOT NULL,
zip varchar(20) NOT NULL,
country varchar(20) NOT NULL,
phone varchar(80) NOT NULL
);
-- add the primary key
ALTER TABLE Account ADD
CONSTRAINT PK_Account
PRIMARY KEY CLUSTERED (userid);
-- add the foreign key constraints
ALTER TABLE Account ADD
CONSTRAINT FK_Account_Signon FOREIGN KEY (userid)
REFERENCES Signon (username),
CONSTRAINT FK_Account_Profile FOREIGN KEY (userid)
REFERENCES Profile (userid);
-- grant access
GRANT ALL ON Account TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- ItemTable Table
-------------------------------------------------------------------------------
CREATE TABLE Item
(
itemid char(10) NOT NULL,
productid char(10) NOT NULL,
listprice decimal(10, 2) NULL,
unitcost decimal(10, 2) NULL,
supplier int NULL,
status char(2) NULL,
attr1 varchar(80) NULL,
attr2 varchar(80) NULL,
attr3 varchar(80) NULL,
attr4 varchar(80) NULL,
attr5 varchar(80) NULL
);
-- add the primary key constraints
ALTER TABLE Item ADD
CONSTRAINT PK_Item
PRIMARY KEY CLUSTERED (itemid);
-- add the foreign key constraints
ALTER TABLE Item ADD
CONSTRAINT FK_Item_Product FOREIGN KEY (productid)
REFERENCES Product (productid),
CONSTRAINT FK_Item_Supplier FOREIGN KEY (supplier)
REFERENCES Supplier (suppid);
-- grant access
GRANT ALL ON Item TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- Inventory Table
-------------------------------------------------------------------------------
CREATE TABLE Inventory
(
itemid char(10) NOT NULL,
qty int NOT NULL
);
-- grant access
GRANT ALL ON Inventory TO PUBLIC;
GO
-------------------------------------------------------------------------------
-- LineItem Table
-------------------------------------------------------------------------------
CREATE TABLE LineItem
(
orderid int NOT NULL,
linenum int NOT NULL,
itemid char(10) NOT NULL,
quantity int NOT NULL,
unitprice numeric(10, 2) NOT NULL
);
-- add the primary key constraints
ALTER TABLE LineItem ADD
CONSTRAINT PK_LineItem
PRIMARY KEY CLUSTERED (orderid, linenum);
-- add the foreign key constraints
ALTER TABLE LineItem ADD
CONSTRAINT FK_LineItem_Orders FOREIGN KEY (orderid)
REFERENCES Orders (orderid);
-- grant access
GRANT ALL ON LineItem TO PUBLIC;
GO
-------------------------------------------------------------------------------
--
-- Indexes
--
-------------------------------------------------------------------------------
CREATE INDEX [IX_Item] ON [dbo].[Item]([productid], [itemid], [listprice], [attr1]) ON [PRIMARY];
GO
CREATE INDEX [IX_Product_1] ON [dbo].[Product]([name]) ON [PRIMARY];
GO
CREATE INDEX [IX_Product_2] ON [dbo].[Product]([category]) ON [PRIMARY];
GO
CREATE INDEX [IX_Product_3] ON [dbo].[Product]([category], [name]) ON [PRIMARY];
GO
CREATE INDEX [IX_Product_4] ON [dbo].[Product]([category], [productid], [name]) ON [PRIMARY];
GO
-------------------------------------------------------------------------------
--
-- Stored Procedures
--
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- upAccountAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountAdd
(
@userid varchar(80),
@password varchar(80),
@email varchar(80),
@firstname varchar(80),
@lastname varchar(80),
@addr1 varchar(80),
@addr2 varchar(40),
@city varchar(80),
@state varchar(80),
@zip varchar(20),
@country varchar(20),
@phone varchar(80),
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -