USE master
GO
IF EXISTS (
SELECT
name
FROM
sys.databases
WHERE
name
= N
'EShop'
)
DROP
DATABASE
EShop
GO
CREATE
DATABASE
EShop
GO
use EShop
go
create
table
Suppliers
(
SupplierID
int
identity(1,1)
primary
key
,
CompanyName nvarchar(40)
not
null
,
ContactName nvarchar(30),
ContactTitle nvarchar(30),
[Address] nvarchar(60),
City nvarchar(15),
Region nvarchar(15),
PostalCode nvarchar(15),
Country nvarchar(24),
Phone nvarchar(24),
Fax nvarchar(24),
HomePage ntext
)
go
create
table
Categories
(
CategoryID
int
identity(1,1)
primary
key
,
CategoryName nvarchar(15)
not
null
,
[Description] ntext,
Picture image
)
go
create
table
Products
(
ProductID
int
identity
primary
key
,
ProductName nvarchar(40)
not
null
,
SupplierID
int
foreign
key
references
Suppliers(SupplierID),
CategoryID
int
foreign
key
references
Categories(CategoryID),
QuantityPerUnit nvarchar(20),
UnitPrice money,
UnitsInStock
smallint
default
(0)
check
(UnitsInStock>=0),
UnitsOnOrder
smallint
default
(0)
check
(UnitsOnOrder>=0),
ReorderLevel
smallint
default
(0)
check
(ReorderLevel>=0),
Discontinued
bit
)
go
create
table
OrderDetails
(
OrderID
int
identity(1,1),
ProductID
int
,
UnitPrice money
not
null
,
Quantity
smallint
not
null
,
Discount
real
not
null
,
primary
key
(OrderID,ProductID)
)
go
create
table
Employees
(
EmployeeID
int
identity(1,1)
primary
key
,
lastName nvarchar(20)
not
null
,
FirstName nvarchar(10)
not
null
,
Title nvarchar(30),
TitleOfCourtesy nvarchar(25),
BirthDate datetime,
HireDate datetime,
[Address] nvarchar(50),
City nvarchar(15),
Region nvarchar(15),
PostalCode nvarchar(10),
Country nvarchar(15),
HomePhone nvarchar(24),
Extension nvarchar(4),
Photo image,
Notes ntext,
PhotoPath nvarchar(255)
)
go
create
table
Customers
(
CustomerID
nchar
(5)
primary
key
,
CompanyName nvarchar(40)
not
null
,
ContactName nvarchar(30),
ContactTitle nvarchar(30),
[Address] nvarchar(60),
City nvarchar(15),
Region nvarchar(15),
PostalCode nvarchar(15),
Country nvarchar(24),
Phone nvarchar(24),
Fax nvarchar(24)
)
go
create
table
CustomerDemoGraphics
(
CustomerTypeID
nchar
(10)
primary
key
,
CustomerDesc ntext
)
go
create
table
CustomerCustomerDemo
(
CustomerID
nchar
(5)
foreign
key
references
Customers(CustomerID),
CustomerTypeID
nchar
(10)
foreign
key
references
CustomerDemoGraphics(CustomerTypeID),
primary
key
(CustomerID,CustomerTypeID)
)
go
create
table
Orders
(
OrderID
int
identity
primary
key
,
CustomerID
nchar
(5)
foreign
key
references
Customers(CustomerID),
EmployeeID
int
foreign
key
references
Employees(EmployeeID),
OrderDate datetime,
RequiredDate datetime,
ShippedDate datetime,
Fright money,
ShipName nvarchar(15),
ShipAddress nvarchar(60),
ShipCity nvarchar(15),
ShipRegion nvarchar(15),
ShipPostalCode nvarchar(10),
ShipContry nvarchar(15)
)
create
table
Shippers
(
ShipperID
int
identity
primary
key
,
CompanyName nvarchar(40)
not
null
,
Phone nvarchar(24)
)
print
'ylb, tech 创建电子商务数据库完成'