图书管理系统

使用PowerDesigner建摸，图书管理系统。
下面是SQL脚本代码

USE [master]
GO
/****** Object:  Database [LibDBTest]    Script Date: 2016/6/27 20:23:17 ******/
CREATE DATABASE [LibDBTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LibDBTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LibDBTest.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'LibDBTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LibDBTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [LibDBTest] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [LibDBTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [LibDBTest] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [LibDBTest] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [LibDBTest] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [LibDBTest] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [LibDBTest] SET ARITHABORT OFF 
GO
ALTER DATABASE [LibDBTest] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [LibDBTest] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [LibDBTest] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [LibDBTest] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [LibDBTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [LibDBTest] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [LibDBTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [LibDBTest] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [LibDBTest] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [LibDBTest] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [LibDBTest] SET  DISABLE_BROKER 
GO
ALTER DATABASE [LibDBTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [LibDBTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [LibDBTest] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [LibDBTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [LibDBTest] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [LibDBTest] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [LibDBTest] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [LibDBTest] SET RECOVERY FULL 
GO
ALTER DATABASE [LibDBTest] SET  MULTI_USER 
GO
ALTER DATABASE [LibDBTest] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [LibDBTest] SET DB_CHAINING OFF 
GO
ALTER DATABASE [LibDBTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [LibDBTest] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'LibDBTest', N'ON'
GO
USE [LibDBTest]
GO
/****** Object:  StoredProcedure [dbo].[overdueMoney]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[overdueMoney] @WReturnId varchar(100)  --创建一个存储过程
as
begin
    declare @Id varchar(100)
    declare @d int
    declare WorkRId cursor for   --声明游标WorKRId
        select WorkReturnId,Datediff(day,convert(int,BorrowDate),RReturnDate)-30
            from BorrowBook,ReturnBook
            where BorrowBook.WorkBorrowId=ReturnBook.WorkReturnId 
    open WorKRId 
    fetch next from WorkId into @Id,@d
        while  @@fetch_status=0
        begin
            if @d>0   --计算借书日期和实际还书日期之间天数和30的差值，大于零说明超期，差值即为超期天数
                update ReturnBook
                    set Moneny=0.3*@d
                    where WorkReturnId=@Id
        fetch next from WorkId into @Id,@d
        end
        close WorkRId 
        deallocate WorkRId
return 
end
GO
/****** Object:  Table [dbo].[AdminInfo]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AdminInfo](
    [AdminId] [varchar](20) NOT NULL,
    [Adm_AdminId] [varchar](20) NULL,
    [PassWord] [varchar](30) NOT NULL,
    [Name] [varchar](20) NOT NULL,
    [Sex] [varchar](10) NULL,
    [PhoneNum] [varchar](30) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [StarDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [Note] [varchar](100) NULL,
 CONSTRAINT [PK_ADMININFO] PRIMARY KEY CLUSTERED 
(
    [AdminId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BookCollectInfo]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BookCollectInfo](
    [BookCollectId] [varchar](30) NOT NULL,
    [BookCollectName] [varchar](50) NOT NULL,
    [BookCollectFloor] [varchar](30) NOT NULL,
    [BookCollectNote] [varchar](100) NULL,
 CONSTRAINT [PK_BOOKCOLLECTINFO] PRIMARY KEY NONCLUSTERED 
(
    [BookCollectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BookInfo]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BookInfo](
    [BookId] [varchar](50) NOT NULL,
    [BookCollectId] [varchar](30) NOT NULL,
    [IsLibaryId] [varchar](20) NOT NULL,
    [BookTypeId] [varchar](20) NOT NULL,
    [ISBN] [varchar](30) NOT NULL,
    [BookName] [varchar](50) NOT NULL,
    [BookAuthor] [varchar](30) NOT NULL,
    [BookPress] [varchar](50) NOT NULL,
    [PublicationDate] [datetime] NOT NULL,
    [Profiles] [varchar](200) NOT NULL,
    [BookNum] [varchar](20) NOT NULL,
    [BookInfoNote] [varchar](100) NULL,
 CONSTRAINT [PK_BOOKINFO] PRIMARY KEY NONCLUSTERED 
(
    [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BookType]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BookType](
    [BookTypeId] [varchar](20) NOT NULL,
    [BookTypeName] [varchar](40) NOT NULL,
    [BookTypeNote] [varchar](100) NULL,
 CONSTRAINT [PK_BOOKTYPE] PRIMARY KEY NONCLUSTERED 
(
    [BookTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BorrowBook]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BorrowBook](
    [WorkBorrowId] [varchar](100) NOT NULL,
    [BookId] [varchar](50) NOT NULL,
    [ReaderId] [varchar](20) NULL,
    [BorrowDate] [datetime] NOT NULL,
    [SReturnDate] [datetime] NOT NULL,
    [BookStateId] [varchar](20) NULL,
    [ReaderBookNote] [varchar](100) NULL,
 CONSTRAINT [PK_BORROWBOOK] PRIMARY KEY CLUSTERED 
(
    [WorkBorrowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BorrowBookState]    Script Date: 2016/6/27 20:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BorrowBookState](
    [BookStateId] [varchar](20) NOT NULL,
    [BookStateName] [varchar](50) NOT NULL,
    [BookStateNote] [varchar](100) NULL,
 CONSTRAINT [PK_BORROWBOOKSTATE] PRIMARY KEY CLUSTERED 
(
    [BookStateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [