建库语句.txt

SQL Sever2000,THEN COURSE DESIGN OF DATABASE SYSTEM PANORAMA

//数据库
CREATE DATABASE BookSale
   ON
      (NAME='BookSale_DATA',
       FILENAME='f:\BookSale.MDF',
       SIZE=5MB,
       MAXSIZE=20MB,
       FILEGROWTH=10%)
   LOG ON
      (NAME='BookSale_LOG',
       FILENAME='f:\BookSale.LDF',
       SIZE=2MB,
       MAXSIZE=5MB,
       FILEGROWTH=1MB)
   GO

//管理员信息表
CREATE TABLE Admin (
	AdminID varchar (15) PRIMARY KEY,
	Pwd varchar (15)  NOT NULL ,
	AdminName nvarchar (20)  NOT NULL ,
)
//用户信息
CREATE TABLE Client (
	ClientID varchar (15)  PRIMARY KEY ,
	Pwd varchar (15)   NOT NULL ,
	ClientName nvarchar (20)  NOT NULL ,
	ClientIDN char (18)  NOT NULL ,
	ClientADR nvarchar (40)  NULL ,
	ZipCode char (6)  NULL 
)
//书籍信息
CREATE TABLE Books (
	BookID char (15) PRIMARY KEY ,
	BookName varchar (20)  NOT NULL ,
	UnitPrice real NOT NULL ,
	BookType varchar (50)  NOT NULL ,
	BookIntro nvarchar (500)  NOT NULL ,
	BookImage varchar (50)  NULL ,
	BookSale int NULL 
)
//购物车信息
CREATE TABLE Cart (
	CartID char (20) PRIMARY KEY ,
	ClientID varchar (15) NOT NULL ,
	BookID char (15)  NOT NULL ,
	UnitPrice real NOT NULL ,
	Quantity int NOT NULL ,
	SubPrice int NOT NULL 
)
//订单详细信息表
CREATE TABLE OrderDetails (
	DetailID char (20) primary key,
	OrderID varchar (30)  NOT NULL ,
	BookID char (15)  NOT NULL ,
	UnitPrice real NOT NULL ,
	Quantity int NOT NULL 
)
//订单信息
CREATE TABLE Orders (
	OrderID varchar (30) PRIMARY KEY ,
	ClientID varchar (15) NOT NULL ,
	TotalPrice int NOT NULL ,
	OrderDate varchar (20)  NOT NULL ,
	OrderMess nvarchar (200) NOT NULL ,
	Paid varchar (3)  NOT NULL check  (paid='Y' or  paid='N'),
	Finished varchar (3)  NOT NULL ,
)