--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,
	[最大續(xù)借次數(shù)] [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,
	[聯(lián)系地址] [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,
	[已借數(shù)量] [smallint] NULL,
	[姓名] [char] (20) NULL,
	[性別] [char] (2) NULL,
	[出生年月] [datetime] NULL,
	[所在單位] [char] (50) NULL,
	[聯(lián)系地址] [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,
	[在庫數(shù)量] [smallint] NOT NULL,
	[剩余數(shù)量] [smallint] NOT NULL,
	[入庫時間] [datetime] NOT NULL,
	[被借閱次數(shù)] [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,
	[應(yīng)還日期] [datetime] NOT NULL,
	[剩余續(xù)借次數(shù)] [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,
	[數(shù)量] [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,
	[數(shù)量] [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].[借閱信息]([讀者證號]);