bookshop_sql2000.sql

有简单的网上书店需求及设计流程

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2008-9-23 8:13:01                            */
/*==============================================================*/


if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('book') and o.name = 'FK_BOOK_RELATIONS_ADMIN')
alter table book
   drop constraint FK_BOOK_RELATIONS_ADMIN
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('bulletion') and o.name = 'FK_BULLETIO_RELATIONS_ADMIN')
alter table bulletion
   drop constraint FK_BULLETIO_RELATIONS_ADMIN
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('orders') and o.name = 'FK_ORDERS_RELATIONS_ORDERUSE')
alter table orders
   drop constraint FK_ORDERS_RELATIONS_ORDERUSE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('orders') and o.name = 'FK_ORDERS_RELATIONS_BOOK')
alter table orders
   drop constraint FK_ORDERS_RELATIONS_BOOK
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('orderuser') and o.name = 'FK_ORDERUSE_RELATIONS_USERS')
alter table orderuser
   drop constraint FK_ORDERUSE_RELATIONS_USERS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('admin')
            and   type = 'U')
   drop table admin
go

if exists (select 1
            from  sysobjects
           where  id = object_id('book')
            and   type = 'U')
   drop table book
go

if exists (select 1
            from  sysobjects
           where  id = object_id('bulletion')
            and   type = 'U')
   drop table bulletion
go

if exists (select 1
            from  sysobjects
           where  id = object_id('orders')
            and   type = 'U')
   drop table orders
go

if exists (select 1
            from  sysobjects
           where  id = object_id('orderuser')
            and   type = 'U')
   drop table orderuser
go

if exists (select 1
            from  sysobjects
           where  id = object_id('users')
            and   type = 'U')
   drop table users
go

if exists (select 1
            from  sysobjects
           where  id = object_id('visitor')
            and   type = 'U')
   drop table visitor
go

/*==============================================================*/
/* Table: admin                                                 */
/*==============================================================*/
create table admin (
   adminid              int                  not null,
   adminname            varchar(30)          not null,
   adminpassword        varchar(12)          not null,
   phone                varchar(15)          not null,
   score                int                  null,
   constraint PK_ADMIN primary key nonclustered (adminid)
)
go

/*==============================================================*/
/* Table: book                                                  */
/*==============================================================*/
create table book (
   isbn                 varchar(24)          not null,
   bookname             varchar(50)          not null,
   bookauthor           varchar(30)          not null,
   publisher            varchar(50)          not null,
   introduction         varchar(150)         not null,
   price                float                not null,
   picture              varchar(50)          null,
   isnew                varchar(2)           not null,
   bookid               int                  not null,
   adminid              int                  null,
   constraint PK_BOOK primary key nonclustered (bookid)
)
go

/*==============================================================*/
/* Table: bulletion                                             */
/*==============================================================*/
create table bulletion (
   bid                  int                  not null,
   adminid              int                  null,
   content              varchar(300)         null,
   constraint PK_BULLETION primary key nonclustered (bid)
)
go

/*==============================================================*/
/* Table: orders                                                */
/*==============================================================*/
create table orders (
   orderid              int                  not null,
   bookid               int                  null,
   orderuserid          int                  null,
   bookacount           int                  not null,
   constraint PK_ORDERS primary key nonclustered (orderid)
)
go

/*==============================================================*/
/* Table: orderuser                                             */
/*==============================================================*/
create table orderuser (
   orderuserid        int                        not null,
   userid             int,
   orderdate          datetime                            not null,
   name               varchar(20)                    not null,
   totalprice         varchar(11)                    not null,
   adress             varchar(100)                   not null,
   code               varchar(10)                    not null,
   phone              varchar(11)                    not null,   
   constraint PK_ORDERUSER primary key (orderuserid)
)
go

/*==============================================================*/
/* Table: users                                                 */
/*==============================================================*/
create table users (
   userid               int                  not null,
   password             varchar(12)          not null,
   username             varchar(30)          not null,
   constraint PK_USERS primary key nonclustered (userid)
)
go

/*==============================================================*/
/* Table: visitor                                               */
/*==============================================================*/
create table visitor (
   visitorid            int                  not null,
   title                varchar(50)          not null,
   detail               varchar(300)         not null,
   visitorname          varchar(30)          not null,
   visitordate          datetime             not null,
   constraint PK_VISITOR primary key nonclustered (visitorid)
)
go

alter table book
   add constraint FK_BOOK_RELATIONS_ADMIN foreign key (adminid)
      references admin (adminid)
go

alter table bulletion
   add constraint FK_BULLETIO_RELATIONS_ADMIN foreign key (adminid)
      references admin (adminid)
go

alter table orders
   add constraint FK_ORDERS_RELATIONS_ORDERUSE foreign key (orderuserid)
      references orderuser (orderuserid)
go

alter table orders
   add constraint FK_ORDERS_RELATIONS_BOOK foreign key (bookid)
      references book (bookid)
go

alter table orderuser
   add constraint FK_ORDERUSE_RELATIONS_USERS foreign key (userid)
      references users (userid)
go