booksboeeowreadsystem.sql

一个功能强大的图书管理系统,是个人版,适合学习swing的初学者阅读.

use master 
go

/****** Object:  Database booksboeeowreadsystem    Script Date: 2007-9-2 10:37:51 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'booksboeeowreadsystem')
	DROP DATABASE [booksboeeowreadsystem]
GO

CREATE DATABASE [booksboeeowreadsystem]  ON (NAME = N'booksboeeowreadsystem_Data', FILENAME = N'E:\毕业项目\booksboeeowreadsystem_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'booksboeeowreadsystem_Log', FILENAME = N'E:\毕业项目\booksboeeowreadsystem_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'booksboeeowreadsystem', N'autoclose', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'bulkcopy', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'trunc. log', N'true'
GO

exec sp_dboption N'booksboeeowreadsystem', N'torn page detection', N'true'
GO

exec sp_dboption N'booksboeeowreadsystem', N'read only', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'dbo use', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'single', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'autoshrink', N'true'
GO

exec sp_dboption N'booksboeeowreadsystem', N'ANSI null default', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'recursive triggers', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'ANSI nulls', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'concat null yields null', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'cursor close on commit', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'default to local cursor', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'quoted identifier', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'ANSI warnings', N'false'
GO

exec sp_dboption N'booksboeeowreadsystem', N'auto create statistics', N'true'
GO

exec sp_dboption N'booksboeeowreadsystem', N'auto update statistics', N'true'
GO

use [booksboeeowreadsystem]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksInfo_BooksType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksInfo] DROP CONSTRAINT FK_booksInfo_BooksType
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksBoorowInfo_quanxian]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksBoorowInfo] DROP CONSTRAINT FK_booksBoorowInfo_quanxian
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksBoorowInfo_quanxian1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksBoorowInfo] DROP CONSTRAINT FK_booksBoorowInfo_quanxian1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksInfo_quanxian]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksInfo] DROP CONSTRAINT FK_booksInfo_quanxian
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksInfo_quanxian1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksInfo] DROP CONSTRAINT FK_booksInfo_quanxian1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_readerInfo_quanxian]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[readerInfo] DROP CONSTRAINT FK_readerInfo_quanxian
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_readerInfo_quanxian1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[readerInfo] DROP CONSTRAINT FK_readerInfo_quanxian1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_readerInfo_readerType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[readerInfo] DROP CONSTRAINT FK_readerInfo_readerType
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksBoorowInfo_booksInfo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksBoorowInfo] DROP CONSTRAINT FK_booksBoorowInfo_booksInfo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_booksBoorowInfo_readerInfo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[booksBoorowInfo] DROP CONSTRAINT FK_booksBoorowInfo_readerInfo
GO

/****** Object:  Table [dbo].[booksBoorowInfo]    Script Date: 2007-9-2 10:37:53 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[booksBoorowInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[booksBoorowInfo]
GO

/****** Object:  Table [dbo].[booksInfo]    Script Date: 2007-9-2 10:37:53 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[booksInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[booksInfo]
GO

/****** Object:  Table [dbo].[readerInfo]    Script Date: 2007-9-2 10:37:53 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[readerInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[readerInfo]
GO

/****** Object:  Table [dbo].[BooksType]    Script Date: 2007-9-2 10:37:53 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BooksType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BooksType]
GO

/****** Object:  Table [dbo].[quanxian]    Script Date: 2007-9-2 10:37:53 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[quanxian]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[quanxian]
GO

/****** Object:  Table [dbo].[readerType]    Script Date: 2007-9-2 10:37:53 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[readerType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[readerType]
GO

/****** Object:  User dbo    Script Date: 2007-9-2 10:37:51 ******/
/****** Object:  Table [dbo].[BooksType]    Script Date: 2007-9-2 10:37:53 ******/
CREATE TABLE [dbo].[BooksType] (
	[typeID] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[typeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[quanxian]    Script Date: 2007-9-2 10:37:54 ******/
CREATE TABLE [dbo].[quanxian] (
	[Name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[password] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[quanxian] [int] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[readerType]    Script Date: 2007-9-2 10:37:54 ******/
CREATE TABLE [dbo].[readerType] (
	[typeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[foregift] [money] NOT NULL ,
	[term] [int] NOT NULL ,
	[quantity] [int] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[booksInfo]    Script Date: 2007-9-2 10:37:54 ******/
CREATE TABLE [dbo].[booksInfo] (
	[Id] [int] IDENTITY (1, 1) NOT NULL ,
	[booksName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[booksNum] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[bookTypeId] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[bookAuthor] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[bookConcern] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[borrowMoneyNum] [int] NOT NULL ,
	[purchaseMoney] [money] NOT NULL ,
	[bookLanguage] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[booksLairage] [datetime] NOT NULL ,
	[lairagehandlers] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[booksLogout] [datetime] NULL ,
	[logouthandlers] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[readerInfo]    Script Date: 2007-9-2 10:37:54 ******/
CREATE TABLE [dbo].[readerInfo] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[readerID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[readerName] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[readerSex] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[readerTypeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[beginTime] [datetime] NOT NULL ,
	[exitTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[beginHandlers] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[state] [bit] NOT NULL ,
	[certificateName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[certificateNum] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[readerAddress] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[readerTelephone] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[endTime] [datetime] NULL ,
	[endHandlers] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[mangleQuantity] [int] NULL ,
	[borrowBooksNum] [int] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[booksBoorowInfo]    Script Date: 2007-9-2 10:37:54 ******/
CREATE TABLE [dbo].[booksBoorowInfo] (