bookshop_oracle10g.sql

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

/*==============================================================*/
/* DBMS name:      ORACLE Version 10g                           */
/* Created on:     2008-9-23 8:15:11                            */
/*==============================================================*/


alter table book
   drop constraint FK_BOOK_RELATIONS_ADMIN;

alter table bulletion
   drop constraint FK_BULLETIO_RELATIONS_ADMIN;

alter table orders
   drop constraint FK_ORDERS_RELATIONS_ORDERUSE;

alter table orders
   drop constraint FK_ORDERS_RELATIONS_BOOK;

alter table orderuser
   drop constraint FK_ORDERUSE_RELATIONS_USERS;

drop table admin cascade constraints;

drop table book cascade constraints;

drop table bulletion cascade constraints;

drop table orders cascade constraints;

drop table orderuser cascade constraints;

drop table users cascade constraints;

drop table visitor cascade constraints;

/*==============================================================*/
/* Table: admin                                               */
/*==============================================================*/
create table admin  (
   adminid            INTEGER                         not null,
   adminname          VARCHAR2(30)                    not null,
   adminpassword      VARCHAR2(12)                    not null,
   phone              VARCHAR2(15)                    not null,
   score              INTEGER,
   constraint PK_ADMIN primary key (adminid)
);

/*==============================================================*/
/* Table: book                                                */
/*==============================================================*/
create table book  (
   bookid             INTEGER                         not null,
   isbn               VARCHAR2(24)                    not null,
   bookname           VARCHAR2(50)                    not null,
   bookauthor         VARCHAR2(30)                    not null,
   publisher          VARCHAR2(50)                    not null,
   introduction       VARCHAR2(150)                   not null,
   price              FLOAT                           not null,
   picture            VARCHAR2(50),
   isnew              VARCHAR2(2)                     not null,   
   adminid            INTEGER,
   constraint PK_BOOK primary key (bookid)
);

/*==============================================================*/
/* Table: bulletion                                           */
/*==============================================================*/
create table bulletion  (
   bid                INTEGER                         not null,
   adminid            INTEGER,
   content            VARCHAR2(300),
   constraint PK_BULLETION primary key (bid)
);

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

/*==============================================================*/
/* Table: orderuser                                           */
/*==============================================================*/
create table orderuser  (
   orderuserid        INTEGER                         not null,
   userid             INTEGER,
   orderdate          DATE                            not null,
   name               VARCHAR2(20)                    not null,
   totalprice         VARCHAR2(11)                    not null,
   adress             VARCHAR2(100)                   not null,
   code               VARCHAR2(10)                    not null,
   phone              VARCHAR2(11)                    not null,   
   constraint PK_ORDERUSER primary key (orderuserid)
);

/*==============================================================*/
/* Table: users                                               */
/*==============================================================*/
create table users  (
   userid             INTEGER                         not null,
   password           VARCHAR2(12)                    not null,
   username           VARCHAR2(30)                    not null,
   constraint PK_USERS primary key (userid)
);

/*==============================================================*/
/* Table: visitor                                             */
/*==============================================================*/
create table visitor  (
   visitorid          INTEGER                         not null,
   title              VARCHAR2(50)                    not null,
   detail             VARCHAR2(300)                   not null,
   visitorname        VARCHAR2(30)                    not null,
   visitordate        DATE                            not null,
   constraint PK_VISITOR primary key (visitorid)
);

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

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

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

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

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