图书馆管理系统建模设计.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_管理书籍_ADMINgoif 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_BOOKSgoif 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_READERgoif 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_ADMINgoif 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_READERgoif 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_BOOKSgoif 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_READERgoif exists (select 1 from sysindexes where id = object_id(admin) and name = 权限_FK and indid 0 and indid 0 and indid 0 and indid 0 and indid 0 and indid 0 and indid 0 and indid 0 and indid 0 and indid 0 and indid 255) drop index reversation.reversation_FKgoif exists (select 1 from sysobjects where id = object_id(reversation) and type = U) drop table reversationgocreate 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)gocreate index 权限_FK on admin ()gocreate table booktype ( type_id int null)gocreate 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)gocreate index 图书类型_FK on books ()gocreate 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)gocreate 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)gocreate index Association_1_FK on borrow_information (book_id ASC)gocreate index Association_2_FK on borrow_information (reader_id ASC)gocreate 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)gocreate index login_FK on login (admin_id ASC)gocreate index login2_FK on login (reader_id ASC)gocreate index 顾客权限_FK on reader ()gocreate 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)gocreate index reversation_FK on reversation (book_id ASC)gocreate index reversation2_FK on reversation (reader_id ASC)go