use master
go
if exists (select * from dbo.sysdatabases where name = 'library')
drop database library
GO
create database library
go
use library
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[readertype]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[readertype]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[readers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[readers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[booktype]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[booktype]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[books]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[borrowinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[borrowinfo]
GO
CREATE TABLE [dbo].[readertype] (
[typeno] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[typename] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[booknumber] [numeric](4, 0) NULL ,
[bookdays] [numeric](4, 0) NULL ,
[userfullife] [numeric](5, 0) NULL ,
[memo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[readers] (
[readerno] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[readername] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[readersex] [char] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[readertype] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[readerdep] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[address] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[readertel] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[email] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[checkdate] [datetime] NULL ,
[readermemo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[booktype] (
[booktypeno] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[typename] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[keyword] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[memo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[books] (
[bookid] [char] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bookname] [char] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[booktype] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bookauthor] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bookpub] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[bookpubdate] [datetime] NULL ,
[bookpages] [numeric](5, 0) NULL ,
[bookkeyword] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bookindate] [datetime] NULL ,
[putup] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bookmemo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[borrowinfo] (
[borrowno] [char] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[readerid] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[readername] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bookid] [char] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bookname] [char] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[borrowdate] [datetime] NULL ,
[returndate] [datetime] NULL ,
[memo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
