经典图书管理系统后台数据库

ER diagram:

use master
go

if exists (select name from sysdatabases where name = 'library')
drop database library
go

create database library
	on
(
	name = 'library_data',
	filename = './library_data.mdf',
	size = 50mb,
	maxsize = 100mb,
	filegrowth = 10%
)
	log on
(
	name = 'library_log',
	filename = './library_log.ldf',
	size = 10mb,
	maxsize = 20mb,
	filegrowth = 2mb
)
go

use library
go


--创建读者信息表
if exists (select name from sysobjects where name = '读者表' and type = 'U')
drop table 读者表
go

CREATE TABLE 读者表 (
       读者编号             CHAR(10) NOT NULL default '' primary key,
	  读者类型             CHAR(10) NULL,
       读者姓名		   varchar(10) default '',
	  读者性别             char(1) default 'M',     /*'M'代表男,'F'代表女*/
       出生日期             datetime default getdate(),
       有效证件             char(18) default '',
	  证件号码             varchar(18) default '',
       登记日期             datetime default getdate(),
       离开日期             datetime default '4000/01/01',
       借阅证号             CHAR(10) default '',
       备注                 CHAR(18) default '' null,
       操作员               CHAR(5) default '' NULL
)
go

--创建归还信息表,当操作员录入读者归还图书或光碟时写入信息
if exists (select name from sysobjects where name = '归还表' and type = 'U')
drop table 归还表
go

CREATE TABLE 归还表 (
	  归还序号             int identity(1,1) primary key,
       读者编号             CHAR(10) default '' NOT NULL,
       图书编号             CHAR(10) default '' NOT NULL,
       操作员               CHAR(5) default '' NULL,
       归还日期             datetime default getdate() null
)
go


--创建角色表
if exists (select name from sysobjects where name = '角色表' and type = 'U')
drop table 角色表
go

CREATE TABLE 角色表 (
       角色编号             CHAR(2) default '' primary key NOT NULL,
       角色名称             varchar(10) NOT NULL,
       备注                 varchar(20) NOT NULL
)
go

--创建权限表
if exists (select name from sysobjects where name = '权限表' and type = 'U')
drop table 权限表
go

CREATE TABLE 权限表 (
       权限编号             CHAR(2) default '' primary key NOT NULL,
       权限名称             varchar(10) NOT NULL,
       备注                 varchar(20) NOT NULL
)
go


--创建'角色权限表',表明每个角色所拥有的不同权限
if exists (select name from sysobjects where name = '角色权限表' and type = 'U')
drop table 角色权限表
go

CREATE TABLE 角色权限表 (
	  角色权限序号         int identity(1,1) primary key not null,
       角色编号             CHAR(2) NOT NULL,
       权限编号             CHAR(2) NOT NULL
)
go


--创建教职工表, 存储教职工信息
if exists (select name from sysobjects where name = '教职工表' and type = 'U')
drop table 教职工表
go

CREATE TABLE 教职工表 (
       工号                 CHAR(5) default '' primary key NOT NULL,
       姓名                 varchar(10) default '' NULL,
       性别                 CHAR(1) default 'M' NULL,
       出生日期             datetime default getdate(),
       有效证件             CHAR(18) default '',
       证件号码             VARCHAR(18) default '',
       联系方式             VARCHAR(20) default '',
       院系                 VARCHAR(10) default '',
       备注                 VARCHAR(20) default ''
)
go


--创建借阅信息表
if exists (select name from sysobjects where name = '借阅表' and type = 'U')
drop table 借阅表
go 

CREATE TABLE 借阅表 (
	  借阅序号             int identity(1,1) primary key,
       读者编号             CHAR(10) default '',
       图书编号             CHAR(5) default '',
       操作员               CHAR(5) default '',
       借阅日期             datetime default getdate()
)
go


--创建借阅证信息表
if exists (select name from sysobjects where name = '借阅证表' and type = 'U')
drop table 借阅证表
go

CREATE TABLE 借阅证表 (
       借阅证号             CHAR(10) default '' primary key NOT NULL,
       借阅证密码           varchar(10) default '000000' check (len(借阅证密码) >= 6),
       办理日期             datetime default getdate(),
       有效期至             datetime default '4000/01/01',
       备注                 varchar(20) default '',
       操作员               CHAR(5) default ''
)
go

--创建可借书数量表, 不同类型的读者可以借不同数量的书或光碟
if exists (select name from sysobjects where name = '可借书数量表' and type = 'U')
drop table 可借书数量表
go

CREATE TABLE 可借书数量表 (
       读者类型             CHAR(10) default '' NOT NULL,
       可借数量             int default 0 NOT NULL,
       备注                 CHAR(18) NOT NULL
)
go


ALTER TABLE 可借书数量表
       ADD PRIMARY KEY (读者类型 ASC, 可借数量 ASC)
go


--创建其它信息表, 规定一些条件
if exists (select name from sysobjects where name = '其它信息表' and type = 'U')
drop table 其它信息表
go

CREATE TABLE 其它信息表 (
       过期日罚金额         float 	default 0 
)
go


--创建随书光盘表
if exists (select name from sysobjects where name = '随书光盘表' and type = 'U')
drop table 随书光盘表
go

CREATE TABLE 随书光盘表 (
       光盘编号             CHAR(5) default '' primary key NOT NULL,
       书架名称             varchar(10) default '' NULL,
       图书编号             CHAR(10) default '' NOT NULL,
       备注                 varchar(20) default '' NULL
)
go


--创建图书信息表
if exists (select name from sysobjects where name = '图书表' and type = 'U')
drop table 图书表
go

CREATE TABLE 图书表 (
       图书编号             CHAR(10) default '' primary key NOT NULL,
       条形码               CHAR(18) default '' NULL,
       书名                 varchar(10) default '' NULL,
       类型                 varchar(10) default '' NULL,
       作者                 varchar(10) default '' NULL,
       译者                 varchar(10) default '' NULL,
       ISBN                 CHAR(18) default '' NULL,
       出版社               varchar(10) default '' NULL,
       价格                 float default 0 NULL,
       页码                 int default 0 NULL,
       书架名称             varchar(10) default '' NULL,
       入库日期             datetime default getdate(),
       注销日期             datetime default '4000/01/01',
       备注                 varchar(20) default '' NULL
)
go


--创建图书馆员工表
if exists (select name from sysobjects where name = '图书馆员工表' and type = 'U')
drop table 图书馆员工表
go

CREATE TABLE 图书馆员工表 (
       员工编号             CHAR(5) default '' primary key NOT NULL,
       姓名                 varchar(10) default '' NULL,
       性别                 char(1) default 'M' NULL,
       职务                 CHAR(18) default '' NULL,
       出生日期             datetime default getdate(),
       有效证件             varchar(10) default '' NULL,
       证件号码             varchar(20) default '' NULL,
       联系方式             varchar(10) default '' NULL,
       备注                 varchar(20) default '' null
)
go


--创建违章罚款信息表
if exists (select name from sysobjects where name = '违章罚款表' and type = 'U')
drop table 违章罚款表
go

CREATE TABLE 违章罚款表 (
       违章罚款序号         int IDENTITY(1,1) primary key not null,
       图书编号             CHAR(5) default '',
       读者编号             CHAR(10) default '',
       违章罚款日期         datetime default getdate(),
       应罚金额             float default 0,
	  交费日期             datetime default getdate(),
       实收金额             float default 0,
	  操作员               CHAR(5) default '' NOT NULL,
       备注                 CHAR(18) default '' NULL
)
go


--创建续借信息表
if exists (select name from sysobjects where name = '续借表' and type = 'U')
drop table 续借表
go

CREATE TABLE 续借表 (
	  续借序号             int identity(1,1) primary key not null,
       读者编号             CHAR(10) default '',
       图书编号             CHAR(10) default '',
       操作员               CHAR(5) default '',
       续借日期             datetime default getdate()
)
go


--创建学生信息表
if exists (select name from sysobjects where name = '学生表' and type = 'U')
drop table 学生表
go

CREATE TABLE 学生表 (
       学号                 CHAR(9) default '' primary key NOT NULL,
       姓名                 varchar(10) default '' NULL,
       性别                 CHAR(1) default 'M' NULL,
       出生日期             datetime default getdate(),
       有效证件             CHAR(18) default '',
       证件号码             varchar(18) default '',
       联系方式             VARCHAR(10) default '',
       年级                 CHAR(4) default '2000',
       院系                 VARCHAR(10) default '' NULL,
       专业                 VARCHAR(10) default '' NULL,
       班级                 VARCHAR(10) default '' NULL,
       备注                 VARCHAR(20) default '' NULL
)
go


--创建用户信息表
if exists (select name from sysobjects where name = '用户表' and type = 'U')
drop table 用户表
go

CREATE TABLE 用户表 (
       用户名               CHAR(18) default '' primary key NOT NULL,
       角色编号             CHAR(2) default '',
       用户密码             varchar(10) default '000000' check (len(用户密码) >= 6),
       用户类型             varchar(10) default '' NULL,
       员工编号             CHAR(5) default '' NULL,
       备注                 CHAR(18) default '' NULL
)
go


--创建预约表,读者预约图书时使用
CREATE TABLE 预约表 (
	  预约序号             int identity(1,1) primary key not null,
       读者编号             CHAR(10) default '' NULL,
       图书编号             CHAR(10) default '' NULL,
       操作员               CHAR(5) default '' NULL,
       预约日期             datetime default getdate() NULL
)
go

create trigger tD_读者表 on 读者表 for DELETE as
/* ERwin Builtin Sun Nov 13 17:55:37 2005 */
/* DELETE trigger on 读者表 */
begin
  declare  @errno   int,
           @errmsg  varchar(255)
    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    /* 读者表 R/20 预约表 ON PARENT DELETE NO ACTION */
    /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="预约表"
    P2C_VERB_PHRASE="R/20", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_20", FK_COLUMNS="读者编号""借阅证号" */
    if exists (
      select * from deleted,预约表
      where
        /*  %JoinFKPK(预约表,deleted," = "," and") */
        预约表.读者编号 = deleted.读者编号 and
        预约表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE 读者表 because 预约表 exists.'
      goto error
    end

    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    /* 读者表 R/17 续借表 ON PARENT DELETE NO ACTION */
    /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="续借表"
    P2C_VERB_PHRASE="R/17", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_17", FK_COLUMNS="读者编号""借阅证号" */
    if exists (
      select * from deleted,续借表
      where
        /*  %JoinFKPK(续借表,deleted," = "," and") */
        续借表.读者编号 = deleted.读者编号 and
        续借表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE 读者表 because 续借表 exists.'
      goto error
    end

    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    /* 读者表 R/14 归还表 ON PARENT DELETE NO ACTION */
    /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="归还表"
    P2C_VERB_PHRASE="R/14", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_14", FK_COLUMNS="读者编号""借阅证号" */
    if exists (
      select * from deleted,归还表
      where
        /*  %JoinFKPK(归还表,deleted," = "," and") */
        归还表.读者编号 = deleted.读者编号 and
        归还表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE 读者表 because 归还表 exists.'
      goto error
    end

    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    /* 读者表 R/11 借阅表 ON PARENT DELETE NO ACTION */
    /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="借阅表"
    P2C_VERB_PHRASE="R/11", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_11", FK_COLUMNS="读者编号""借阅证号" */
    if exists (
      select * from deleted,借阅表
      where
        /*  %JoinFKPK(借阅表,deleted," = "," and") */
        借阅表.读者编号 = deleted.读者编号 and
        借阅表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE 读者表 because 借阅表 exists.'
      goto error
    end

    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    /* 读者表 R/8 违章罚款表 ON PARENT DELETE NO ACTION */
    /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="违章罚款表"
    P2C_VERB_PHRASE="R/8", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_8", FK_COLUMNS="读者编号""借阅证号" */
    if exists (
      select * from deleted,违章罚款表
      where
        /*  %JoinFKPK(违章罚款表,deleted," = "," and") */
        违章罚款表.读者编号 = deleted.读者编号 and
        违章罚款表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE 读者表 because 违章罚款表 exists.'
      goto error
    end

    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    /* 借阅证表 R/5 读者表 ON CHILD DELETE NO ACTION */
    /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="借阅证表"
    CHILD_OWNER="", CHILD_TABLE="读者表"
    P2C_VERB_PHRASE="R/5", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_5", FK_COLUMNS="借阅证号" */
    if exists (select * from deleted,借阅证表
      where
        /* %JoinFKPK(deleted,借阅证表," = "," and") */
        deleted.借阅证号 = 借阅证表.借阅证号 and
        not exists (
          select * from 读者表
          where
            /* %JoinFKPK(读者表,借阅证表," = "," and") */
            读者表.借阅证号 = 借阅证表.借阅证号
        )
    )
    begin
      select @errno  = 30010,
             @errmsg = 'Cannot DELETE last 读者表 because 借阅证表 exists.'
      goto error
    end


    /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
    return
error:
    raiserror @errno @errmsg
    rollback transaction
end
go

create trigger tU_读者表 on 读者表 for UPDATE as
/* ERwin Builtin Sun Nov 13 17:55:37 2005 */
/* UPDATE trigger on 读者表 */
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @ins读者编号 CHAR(10), 
           @ins借阅证号 CHAR(10),
           @errno   int,
           @errmsg  varchar(255)

  select @numrows = @@rowcount
  /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
  /* 读者表 R/20 预约表 ON PARENT UPDATE NO ACTION */
  /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="预约表"
    P2C_VERB_PHRASE="R/20", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_20", FK_COLUMNS="读者编号""借阅证号" */
  if
    /* %ParentPK(" or",update) */
    update(读者编号) or
    update(借阅证号)
  begin
    if exists (
      select * from deleted,预约表
      where
        /*  %JoinFKPK(预约表,deleted," = "," and") */
        预约表.读者编号 = deleted.读者编号 and
        预约表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE 读者表 because 预约表 exists.'
      goto error
    end
  end

  /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
  /* 读者表 R/17 续借表 ON PARENT UPDATE NO ACTION */
  /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="续借表"
    P2C_VERB_PHRASE="R/17", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_17", FK_COLUMNS="读者编号""借阅证号" */
  if
    /* %ParentPK(" or",update) */
    update(读者编号) or
    update(借阅证号)
  begin
    if exists (
      select * from deleted,续借表
      where
        /*  %JoinFKPK(续借表,deleted," = "," and") */
        续借表.读者编号 = deleted.读者编号 and
        续借表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE 读者表 because 续借表 exists.'
      goto error
    end
  end

  /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
  /* 读者表 R/14 归还表 ON PARENT UPDATE NO ACTION */
  /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="归还表"
    P2C_VERB_PHRASE="R/14", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_14", FK_COLUMNS="读者编号""借阅证号" */
  if
    /* %ParentPK(" or",update) */
    update(读者编号) or
    update(借阅证号)
  begin
    if exists (
      select * from deleted,归还表
      where
        /*  %JoinFKPK(归还表,deleted," = "," and") */
        归还表.读者编号 = deleted.读者编号 and
        归还表.借阅证号 = deleted.借阅证号
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE 读者表 because 归还表 exists.'
      goto error
    end
  end

  /* ERwin Builtin Sun Nov 13 17:55:37 2005 */
  /* 读者表 R/11 借阅表 ON PARENT UPDATE NO ACTION */
  /* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="读者表"
    CHILD_OWNER="", CHILD_TABLE="借阅表"
    P2C_VERB_PHRASE="R/11", C2P_VERB_PHRASE="", 
    FK_CONSTRAINT="R_11", FK_COLUMNS="读者编号""借阅证号" */
  if
    /* %ParentPK(" or",update) */
    update(读者编号) or
    update(借阅证号)
  begin
    if exists (
      select * from deleted,借阅表