中北大学成人教育学院2009届本科毕业设计说明书
图4.3 BOOKINFO
图4.4 USERS
- 23 -
中北大学成人教育学院2009届本科毕业设计说明书
图4.5 MANGER
图4.6 LENDINFO
4.3.5 物理设计
对数据库建立索引，索引语句在SQL语句中。
Create unique index book_id ON bookinfo(bookid) Create unique index user_id ON user(userid) Create unique index lend_id ON lendinfo(lendid) 4.3.6 SQL语句的实现
创建数据库名为library CREATE DATABASE LIBRARY
创建员工基本信息 CREATE TABLE BookInfo
- 24 -
中北大学成人教育学院2009届本科毕业设计说明书
( bookid int(4) NOT NULL UNIQUE , bookname varchar(100) , pubname varchar(100) , bookauthor varchar(50) , series varchar(50) , ISBN varchar(50) , SearchNO varchar(50) , PubDate smalldatetime(4) , Price float(8) , Barcode varchar(50) );
CREATE TABLE LendInfo (
LendID int(4) NOT NULL UNIQUE , BookID int(4) NOT NULL UNIQUE ,
UserID varchar(50) NOT NULL UNIQUE , LendDate smalldatetime(4) , ReturnDate smalldatetime(4) , IsBack int(4) );
CREATE TABLE manage (
manageid int(4) NOT NULL UNIQUE, Pass char(10) ) ;
CREATE TABLE users (
userid int(4) NOT NULL UNIQUE , username char(10) , pass char(10) , email char(50) , phone char(10) ,
address varchar(50) , BookNum int(4) );
4.3.7 创建视图
创建关于书名的视图，因为查询时需要绑定。 Create view book_name As
Select bookid,pubname,bookauthor,bookname,searchNO FROM BOOKINFO
- 25 -
中北大学成人教育学院2009届本科毕业设计说明书
WHERE bookname=’ASP程序设计’；
创建关于用户名的视图，因为查询时需要绑定。 Create view user_name As
Select userid,username,phone,address,email FROM users
WHERE username=’1’；
4.3.8 创建存储过程
创建建表的存储过程 USE LIBRARY GO
CREATE PROCEDURE createtables AS
CREATE TABLE BookInfo
( bookid int(4) NOT NULL UNIQUE , bookname varchar(100) , pubname varchar(100) , bookauthor varchar(50) , series varchar(50) , ISBN varchar(50) , SearchNO varchar(50) , PubDate smalldatetime(4) , Price float(8) , Barcode varchar(50) );
CREATE TABLE LendInfo (
LendID int(4) NOT NULL UNIQUE , BookID int(4) NOT NULL UNIQUE ,
UserID varchar(50) NOT NULL UNIQUE , LendDate smalldatetime(4) , ReturnDate smalldatetime(4) , IsBack int(4) );
CREATE TABLE manage (
manageid int(4) NOT NULL UNIQUE, Pass char(10) ) ;
CREATE TABLE users (
- 26 -
中北大学成人教育学院2009届本科毕业设计说明书
userid int(4) NOT NULL UNIQUE , username char(10) , pass char(10) , email char(50) , phone char(10) ,
address varchar(50) , BookNum int(4) ); GO
4.3.9 创建触发器
创建关于书号和用户号的触发器，分别在BOOKINFO .USERS中修改bookid和userid时，在LENDINFO中也会有相应的修改。
CREATE TRIGGER BookidChange ON BOOKINFO AFTER UPDATE AS
IF UPDATE(bookid) BEGIN
DECLARE @book_id as int
DECLARE @old_book_id as int
SELECT @ book_id=bookid inserted SELECT @ old_book_id =bookid deleted Update LENDINFO
SET LENDINFO.bookid=@ book_id
WHERE LENDINFO. bookid =@ old_book_id END
CREATE TRIGGER UserIdChanges ON USERS AFTER UPDATE AS
IF UPDATE(userid) BEGIN
DECLARE @user_id as int
DECLARE @old_user_id as int
SELECT @ user_id =userid inserted SELECT @ old_user_id = userid deleted Update LENDINFO
SET LENDINFO. userid =@ user_id
WHERE LENDINFO. userid =@ old_user_id END
- 27 -