library.sql

四川大学图书管理系统！sql+c++实现

--THIS SECTION CREATE THE DATABASE
USE "master";

GO

CREATE DATABASE "Library";

GO

USE "Library";

GO

--管理类型表
CREATE TABLE [dbo].[管理员类型](
	[类型] [int] NOT NULL Primary Key,
	[增加删除管理员] [smallint] NOT NULL,
	[增加删除读者] [smallint] NOT NULL,
	[增加在库图书] [smallint] NOT NULL,
	[删除在库图书] [smallint] NOT NULL,
	[增加借阅记录] [smallint] NOT NULL,
	[删除借阅记录] [smallint] NOT NULL,
	[查询图书信息] [smallint] NOT NULL,
	[查看所有人信息] [smallint] NOT NULL,
	[查询所有读者信息] [smallint] NOT NULL,
	[查询修改本人信息] [smallint] NOT NULL,
	[自定义查询] [smallint] NOT NULL
)
GO

--读者类型表
CREATE TABLE [dbo].[读者类型](
	[类型] [int] NOT NULL Primary Key,
	[最大借阅量] [smallint] NOT NULL,
	[最大续借次数] [smallint] NOT NULL,
	[最大借阅期限] [smallint] NOT NULL
)
GO

--管理员表
CREATE TABLE [dbo].[管理员](
	[工作编号] [int] NOT NULL Primary Key,
	[密码] [char] (20) NULL,
	[管理员类型] [int] NOT NULL,
	[姓名] [char] (20) NULL,
	[性别] [char] (2) NULL,
	[出生年月] [datetime] NULL,
	[所在单位] [char] (50) NULL,
	[联系地址] [char] (80) NULL,
	[邮政编码] [char] (6) NULL,
	[电话1] [char] (15) NULL,
	[电话2] [char] (15) NULL,
	[备注] [text] NULL,
	FOREIGN KEY ([管理员类型]) REFERENCES [dbo].[管理员类型]([类型])
)
GO

--读者表
CREATE TABLE [dbo].[读者](
	[读者证号] [int] NOT NULL Primary Key,
	[密码] [char] (20) NULL,
	[读者类型] [int] NOT NULL,
	[已借数量] [smallint] NULL,
	[姓名] [char] (20) NULL,
	[性别] [char] (2) NULL,
	[出生年月] [datetime] NULL,
	[所在单位] [char] (50) NULL,
	[联系地址] [char] (80) NULL,
	[邮政编码] [char] (6) NULL,
	[电话1] [char] (15) NULL,
	[电话2] [char] (15) NULL,
	[备注] [text] NULL,
	FOREIGN KEY ([读者类型]) REFERENCES [dbo].[读者类型]([类型])
)
GO

--在库图书表
CREATE TABLE [dbo].[在库图书](
	[图书编号] [char] (30) NOT NULL Primary Key,
	[在库数量] [smallint] NOT NULL,
	[剩余数量] [smallint] NOT NULL,
	[入库时间] [datetime] NOT NULL,
	[被借阅次数] [int] NOT NULL,
	[操作员] [int] NOT NULL,
	[书名] [char] (50) NOT NULL,
	[作者] [char] (40) NOT NULL,
	[出版社] [char] (40) NOT NULL,
	[ISBN] [char] (20) NOT NULL,
	[分类] [char] (30) NOT NULL,
	FOREIGN KEY ([操作员]) REFERENCES [dbo].[管理员]([工作编号])
)
GO

--借阅表
CREATE TABLE [dbo].[借阅信息](
	[读者证号] [int] NOT NULL,
	[图书编号] [char] (30) NOT NULL,
	[借书日期] [datetime] NOT NULL,
	[应还日期] [datetime] NOT NULL,
	[剩余续借次数] [smallint] NOT NULL,
	[是否超期] [smallint] NOT NULL,
	[操作员] [int] NOT NULL,
	Primary Key ([读者证号],[图书编号]),
	FOREIGN KEY ([操作员]) REFERENCES [dbo].[管理员]([工作编号])
)
GO

--新书表
CREATE TABLE [dbo].[新书](
	[图书编号] [char] (30) NOT NULL Primary Key,
	[数量] [int] NOT NULL,
	[到书时间] [datetime] NOT NULL,
	[批次] [char] (20) NOT NULL,
	[书名] [char] (50) NOT NULL,
	[作者] [char] (40) NOT NULL,
	[出版社] [char] (40) NOT NULL,
	[ISBN] [char] (20) NOT NULL,
	[分类] [char] (30) NOT NULL,
	[操作员] [int] NOT NULL,
	FOREIGN KEY ([操作员]) REFERENCES [dbo].[管理员]([工作编号])
)
GO

--旧书表
CREATE TABLE [dbo].[旧书](
	[图书编号] [char] (30) NOT NULL Primary Key,
	[数量] [int] NOT NULL,
	[删除时间] [datetime] NOT NULL,
	[书名] [char] (50) NOT NULL,
	[作者] [char] (40) NOT NULL,
	[出版社] [char] (40) NOT NULL,
	[ISBN] [char] (20) NOT NULL,
	[分类] [char] (30) NOT NULL,
	[操作员] [int] NOT NULL,
	FOREIGN KEY ([操作员]) REFERENCES [dbo].[管理员]([工作编号])
)
GO

CREATE UNIQUE INDEX [按书名索引] ON [dbo].[在库图书]([书名]);
CREATE UNIQUE INDEX [按读者索引] ON [dbo].[借阅信息]([读者证号]);