图书馆管理系统建模设计.doc

图书馆管理系统建模设计

由PDM对象自动生成旳数据库脚本文献代码如下：
if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('books') and o.name = 'FK_BOOKS_管理书籍_ADMIN')
alter table books
drop constraint FK_BOOKS_管理书籍_ADMIN
go

if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('borrow_information') and o.name = 'FK_BORROW_I_BORROW_IN_BOOKS')
alter table borrow_information
drop constraint FK_BORROW_I_BORROW_IN_BOOKS
go

if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('borrow_information') and o.name = 'FK_BORROW_I_BORROW_IN_READER')
alter table borrow_information
drop constraint FK_BORROW_I_BORROW_IN_READER
go

if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('login') and o.name = 'FK_LOGIN_LOGIN_ADMIN')
alter table login
drop constraint FK_LOGIN_LOGIN_ADMIN
go

if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('login') and o.name = 'FK_LOGIN_LOGIN2_READER')
alter table login
drop constraint FK_LOGIN_LOGIN2_READER
go

if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('reversation') and o.name = 'FK_REVERSAT_REVERSATI_BOOKS')
alter table reversation
drop constraint FK_REVERSAT_REVERSATI_BOOKS
go

if exists (select 1
from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('reversation') and o.name = 'FK_REVERSAT_REVERSATI_READER')
alter table reversation
drop constraint FK_REVERSAT_REVERSATI_READER
go

if exists (select 1
from sysindexes
where id = object_id('admin')
and name = '权限_FK'
and indid > 0
and indid < 255)
drop index admin.权限_FK
go

if exists (select 1
from sysobjects
where id = object_id('admin')
and type = 'U')
drop table admin
go

if exists (select 1
from sysindexes
where id = object_id('books')
and name = '管理书籍_FK'
and indid > 0
and indid < 255)
drop index books.管理书籍_FK
go

if exists (select 1
from sysindexes
where id = object_id('books')
and name = '图书类型_FK'
and indid > 0
and indid < 255)
drop index books.图书类型_FK
go

if exists (select 1
from sysobjects
where id = object_id('books')
and type = 'U')
drop table books
go

if exists (select 1
from sysobjects
where id = object_id('booktype')
and type = 'U')
drop table booktype
go

if exists (select 1
from sysindexes
where id = object_id('borrow_information')
and name = 'Association_2_FK'
and indid > 0
and indid < 255)
drop index borrow_information.Association_2_FK
go

if exists (select 1
from sysindexes
where id = object_id('borrow_information')
and name = 'Association_1_FK'
and indid > 0
and indid < 255)
drop index borrow_information.Association_1_FK
go

if exists (select 1
from sysobjects
where id = object_id('borrow_information')
and type = 'U')
drop table borrow_information
go
if exists (select 1
from sysindexes
where id = object_id('login')
and name = 'login2_FK'
and indid > 0
and indid < 255)
drop index login.login2_FK
go

if exists (select 1
from sysindexes
where id = object_id('login')
and name = 'login_FK'
and indid > 0
and indid < 255)
drop index login.login_FK
go

if exists (select 1
from sysobjects
where id = object_id('login')
and type = 'U')
drop table login
go

if exists (select 1
from sysindexes
where id = object_id('reader')
and name = '顾客权限_FK'
and indid > 0
and indid < 255)
drop index reader.顾客权限_FK
go

if exists (select 1
from sysobjects
where id = object_id('reader')
and type = 'U')
drop table reader
go

if exists (select 1
from sysindexes
where id = object_id('reversation')
and name = 'reversation2_FK'
and indid > 0
and indid < 255)
drop index reversation.reversation2_FK
go

if exists (select 1
from sysindexes
where id = object_id('reversation')
and name = 'reversation_FK'
and indid > 0
and indid < 255)
drop index reversation.reversation_FK
go

if exists (select 1
from sysobjects
where id = object_id('reversation')
and type = 'U')
drop table reversation
go
create table admin (
admin_id int not null,
admin_name char(20) null,
admin_password char(16) null,
logins int null,
last_login char(10) null,
"right" int null,
constraint PK_ADMIN primary key nonclustered (admin_id)
)
go
create index 权限_FK on admin (

)
go
create table booktype (
type_id int null
)
go
create table books (
book_id int not null,
admin_id int null,
title char(20) null,
type_id int null,
author char(40) null,
price money null,
"book concern" char(50) null,
addtime datetime null,
amount int null,
remain int null,
constraint PK_BOOKS primary key nonclustered (book_id),
constraint FK_BOOKS_管理书籍_ADMIN foreign key (admin_id)
references admin (admin_id),
constraint FK_BOOKS_BOOKTYPE_BOOKTYPE foreign key ()
references booktype
)
go
create index 图书类型_FK on books (

)
go
create index 管理书籍_FK on books (
admin_id ASC
)
go
create table reader (
reader_id int not null,
reader_name char(20) null,
sex char(2) null,
age tinyint null,
class char(15) null,
address text null,
memo text null,
maxborrowed int null,
reader_password char(16) null,
"right" int null,
constraint PK_READER primary key nonclustered (reader_id)
)
go
create table borrow_information (
book_id int not null,
reader_id int not null,
borrow_time datetime null,
end_time datetime null,
amount int null,
return_time datetime null,
constraint PK_BORROW_INFORMATION primary key (book_id, reader_id),
constraint FK_BORROW_I_BORROW_IN_BOOKS foreign key (book_id)
references books (book_id),
constraint FK_BORROW_I_BORROW_IN_READER foreign key (reader_id)
references reader (reader_id)
)
go
create index Association_1_FK on borrow_information (
book_id ASC
)
go
create index Association_2_FK on borrow_information (
reader_id ASC
)
go
create table login (
admin_id int not null,
reader_id int not null,
"right" int null,
constraint PK_LOGIN primary key (admin_id, reader_id),
constraint FK_LOGIN_LOGIN_ADMIN foreign key (admin_id)
references admin (admin_id),
constraint FK_LOGIN_LOGIN2_READER foreign key (reader_id)
references reader (reader_id)
)
go
create index login_FK on login (
admin_id ASC
)
go
create index login2_FK on login (
reader_id ASC
)
go
create index 顾客权限_FK on reader (

)
go
create table reversation (
book_id int not null,
reader_id int not null,
reservation_time datetime null,
reservationcancel datetime null,
constraint PK_REVERSATION primary key (book_id, reader_id),
constraint FK_REVERSAT_REVERSATI_BOOKS foreign key (book_id)
references books (book_id),
constraint FK_REVERSAT_REVERSATI_READER foreign key (reader_id)
references reader (reader_id)
)
go
create index reversation_FK on reversation (
book_id ASC
)
go
create index reversation2_FK on reversation (
reader_id ASC
)
go