旅游信息管理系统 数据库

问题描述：

实习报告： 做各关于旅游的.企业工资.或者是图书信息管理的数据库系统
] 麻烦各位帮忙找找 ！~
跪谢

问题解答：

前阶段做的ATM机的,楼主要不修改下吧,呵呵!
有两个小步骤没做完,楼主自己看一下!
use master
if exists(select * from sysdatabases where name='bankDB')
drop database bankDB
GO
---建库bankDB
create database bankDB
on
(
name='bankDB',
filename='D:\bank\bankDB.mdf',
filegrowth=15%
)
use bankDB
GO
---创建用户信息表(userInfo)

create table userInfo
(
customerID INT IDENTITY(1,1),
customerName VARCHAR(10) NOT NULL,
PID NUMERIC(18,0) NOT NULL,
telephone VARCHAR(15) NOT NULL,
address VARCHAR(30)
)
GO

---创建信息表的约束
ALTER TABLE userInfo ADD
CONSTRAINT PK_custonerID PRIMARY KEY(customerID),
CONSTRAINT CK_PID CHECK(len(PID)=18 or len(PID)=15),
CONSTRAINT UQ_PID UNIQUE(PID),
CONSTRAINT CK_telephone CHECK(telephone LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR telephone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR telephone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

---创建银行卡信息表(cardInfo)
CREATE TABLE cardInfo
(
cardID VARCHAR(20) NOT NULL,
curType VARCHAR(6) NOT NULL,
savingType VARCHAR(8),
openDate DATETIME NOT NULL,
openMoney MONEY NOT NULL,
balance MONEY NOT NULL,
pass VARCHAR(6) NOT NULL,
isReportLoss BIT NOT NULL,
customerID INT NOT NULL
)
GO

---创建银行卡信息表的约束

ALTER TABLE cardInfo ADD
CONSTRAINT PK_cardID PRIMARY KEY(cardID),
CONSTRAINT CK_cardID CHECK (cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_curType DEFAULT 'RMB' FOR curType,
CONSTRAINT CK_savingType CHECK(savingType IN ('活期' , '定活两便' , '定期')),
CONSTRAINT DF_openDate DEFAULT GETDATE() FOR openDate,
CONSTRAINT CK_openMoney CHECK (openMoney>=1),
CONSTRAINT CK_balance CHECK (balance>=1),
CONSTRAINT CK_pass CHECK (LEN(pass)=6),
CONSTRAINT DF_pass DEFAULT '888888' FOR pass,
CONSTRAINT DF_isReportLoss DEFAULT '0' FOR isReportLoss,
CONSTRAINT FK_customerID FOREIGN KEY (customerID) REFERENCES userInfo(customerID)
GO

---创建交易信息表(transInfo)

CREATE TABLE transInfo
(
transDate DATETIME NOT NULL,
cardID VARCHAR(20) NOT NULL,
transType VARCHAR(4) NOT NULL,
transMoney MONEY NOT NULL,
remark NTEXT
)
GO

---创建交易信息表的约束
ALTER TABLE transInfo ADD
CONSTRAINT DF_transDate DEFAULT GETDATE() FOR transDate ,
CONSTRAINT FK_cardID FOREIGN KEY (cardID) REFERENCES cardInfo(cardID),
CONSTRAINT CK_transType CHECK (transType='存入' or transType='支取'),
CONSTRAINT CK_transMoney CHECK (transMoney >0)

----测试插入数据
insert into userInfo values('张三','123456789012340','010-67898978','北京海淀')
insert into userInfo values('李四','320504198607221250','0512-65331652','江苏苏州')
insert into userInfo values('王五','320504198607223333','0512-65323442','江苏南京')
insert into userInfo values('赵六','320504198607131313','0512-65331643','江苏苏州')
insert into userInfo values('方六','320504194407131543','0512-65331243','江苏盐城')

insert into cardInfo values('1010 3576 1212 1134','RMB','活期','2006-10-01',1.0000,1.0000,'888888','','1')
insert into cardInfo values('1010 3576 1212 5555','RMB','活期','2006-10-01',1000.0000,1000.0000,'888888','','2')
insert into cardInfo values('1010 3576 2323 3343','RMB','活期','2006-12-01',15345.0000,1000.0000,'888888','','3')
insert into cardInfo values('1010 3576 1456 3454','RMB','活期','2006-11-28',2343.0000,1000.0000,'888888','','4')
insert into cardInfo values('1010 3576 1356 3554','RMB','活期','2006-12-05',22143.0000,10020.0000,'888888','','8')

----插入违背约束语句
insert into userInfo values('沈非','123456789','0512-67998978','江苏苏州') ---违背身份证长度的CHECK约束
insert into userInfo values('沈大','320504198607131313','0512-67998979','江苏苏州') ---违背身份证的唯一约束

insert into cardInfo values('1010 3576 1456 3424','RMB','活期','2006-11-28',0.0000,0,'888888','','5') ---违背开户金额不能<1的CHECK约束

insert into transInfo values('','1010 3576 1456 3424','支取',1000,'') ---违背主外键约束,交易卡号必须存在于cardInfo中

---查看所有表
select * from userInfo
select * from cardInfo
select * from transInfo

---创建存储过程:根据卡号修改密码
CREATE PROC proc_changePass @incardID VARCHAR(20),@inoldPass VARCHAR(6),@newPass1 VARCHAR(6),@newpass2 VARCHAR(6)
AS
DECLARE @oldPass VARCHAR(6)
IF EXISTS(SELECT * FROM cardInfo WHERE cardID=@incardID)
BEGIN
SELECT @oldPass=pass FROM cardInfo WHERE cardID=@incardID
IF(@oldpass=@inoldPass)
BEGIN
IF(@newpass1=@newpass2)
BEGIN
UPDATE cardInfo SET pass=@newPass1 WHERE cardID=@incardID
PRINT '密码修改成功！'
END
ELSE
PRINT '请确认您的新密码！'
END
ELSE
PRINT '您输入的原密码错误，请重新输入！'
END
ELSE
PRINT '您输入的卡号不存在，请重新输入！'

---测试修改密码的存储过程
EXEC proc_changePass '1010 3576 1212 1134','888888','123456','123456'

---创建挂失银行卡的存储过程
CREATE PROC proc_lossCard @inCustomerName VARCHAR(10),@inPID NUMERIC(18,0),@inTelephone VARCHAR(15),@inAddress VARCHAR(30),@inCardPass VARCHAR(6)
AS
DECLARE @customerName VARCHAR(10),@PID NUMERIC(18,0),@telephone VARCHAR(15),@address VARCHAR(30),@cardPass VARCHAR(6)
SELECT @customerName=customerName,@PID=PID,@telephone=telephone,@address=address FROM userInfo WHERE PID=@inPID
SELECT @cardPass=pass FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE PID=@inPID)
IF((@customerName=@inCustomerName)AND(@PID=@inPID)AND(@inTelephone=@telephone)AND(@inAddress=@address)AND(@inCardPass=@cardPass))
BEGIN
UPDATE cardInfo SET isReportLoss=1 WHERE customerID=(SELECT customerID FROM userInfo WHERE PID=@inPID)
END
ELSE
PRINT '您的信息有误，请核对后重新输入！'

----测试挂失的存储过程
EXEC proc_lossCard '王五','320504198607223333','0512-65323442','江苏南京','888888'

---统计银行流通金额

DECLARE @inMoney MONEY,@outMoney MONEY
SELECT @inMoney=sum(transMoney) from transInfo where transType='存入'
SELECT @outMoney=sum(transMoney) from transInfo where transType='支取'
PRINT '银行流通总额为:'+ convert(varchar(20),@inMoney-@outMoney)+' RMB'
PRINT '盈利结算为:'+convert(varchar(20),@outMoney*0.008-@inMoney*0.003)+' RMB'
GO

---查询月交易冠军的银行卡信息
SELECT * FROM transInfo WHERE
print CONVERT(VARCHAR(20),@money)
SELECT * FROM cardInfo WHERE

---查询半年未交易的卡信息
SELECT * FROM cardInfo WHERE cardID NOT IN (SELECT cardID FROM transInfo WHERE DATEDIFF(mm,transDate,getdate())>7)//////////

---查询本周开户的帐号
SELECT * FROM cardInfo where DATEDIFF(WEEK,openDate,getdate())<1

---查询挂失帐号的客户信息
SELECT * FROM userInfo where customerID IN(SELECT customerID FROM cardInfo where isReportLoss=1)

---催款提醒业务
SELECT customerName AS 客户姓名,telephone AS 联系电话,balance AS 余额 FROM
userInfo INNER join cardInfo ON userInfo.customerID=cardInfo.customerID WHERE balance<200 AND datepart(day,getdate())>=28

---给transInfo表的cardID字段添加非聚集索引
CREATE NONCLUSTERED INDEX IX_transInfo_cardID ON transInfo(cardID) WITH FILLFACTOR=70
---测试索引
SELECT * FROM transInfo (INDEX=IX_transInfo_cardID) WHERE cardID='1010 3576 1212 1134'

---创建视图
CREATE VIEW view_userInfo
AS
SELECT customerID AS 客户编号,customerName AS 客户姓名,PID AS 身份证号,telephone AS 电话号码,address AS 联系地址 FROM userInfo

CREATE VIEW view_cardInfo
AS
SELECT cardId AS 卡号,curType AS 货币种类,savingType AS 存款类型,openDate AS 开户日期,openMoney AS 开户金额,balance AS 帐户余额,pass AS 密码,isReportLoss AS 是否挂失,customerID AS 顾客编号 FROM cardInfo

CREATE VIEW view_transInfo
AS
SELECT transDate AS 交易日期,cardID AS 卡号,transType AS 交易类型,transMoney AS 交易金额,remark AS 备注 FROM transInfo

---查看视图
SELECT * FROM view_userInfo
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo

---创建触发器
CREATE TRIGGER trig_trans
ON transInfo
FOR INSERT
DECLARE @transType VARCHAR(4)

---创建触发器trig_trans
drop trigger trig_trans
CREATE TRIGGER trig_trans ON transInfo
FOR INSERT
AS

DECLARE @transType VARCHAR(4),@transMoney MONEY,@cardID VARCHAR(20),@balance MONEY,@customerName VARCHAR(10)
SELECT @transType=transType,@transMoney=transMoney,@cardID=cardID FROM inserted
SELECT @balance=balance FROM cardInfo WHERE cardID=@cardID
SELECT @customerName=customerName FROM userInfo WHERE customerID=(SELECT customerID FROM cardInfo WHERE cardID=@cardID)
BEGIN TRAN
IF (@transType='支取')
BEGIN
IF (@balance-@transMoney<1) ---如果取款后余额不足1元
BEGIN
PRINT '正在交易，请稍等....'
ROLLBACK TRAN ---取消交易，回滚
PRINT '余额不足，交易失败！'
END

ELSE

BEGIN
PRINT '正在交易，请稍等...'
COMMIT TRAN ---交易成功
UPDATE cardInfo SET balance=@balance-@transMoney WHERE cardID=@cardID
PRINT '交易成功,'+CONVERT(VARCHAR(10),@customerName)+' 您进行的是取款操作,当前的余额为:'+CONVERT(VARCHAR(20),@balance-@transMoney)+' RMB'
END
END

ELSE
BEGIN
PRINT '正在交易，请稍等...'
COMMIT TRAN
UPDATE cardInfo SET balance=@balance+@transMoney WHERE cardID=@cardID
PRINT '交易成功,'+CONVERT(VARCHAR(10),@customerName)+' 您进行的是存款操作,当前的余额为:'+CONVERT(VARCHAR(20),@balance+@transMoney)+' RMB'
END

INSERT INTO transInfo VALUES('','1010 3576 1212 5555','支取',999,'aa')

----创建存入与支取的存储过程
drop proc proc_transInfo
CREATE PROC proc_transInfo @customerName VARCHAR(10),@transMoney MONEY,@transType VARCHAR(4),@pass VARCHAR(6)=''
AS
DECLARE @pwd VARCHAR(6)
DECLARE @cardID VARCHAR(20)
SELECT @pwd=pass ,@cardID=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName=@customerName)---由userInfo姓名字段查询得用户的卡号和密码
PRINT '您的卡号为： '+CONVERT(VARCHAR(20),@cardID) ---将用户的卡号输出
IF(@transType='存入')
BEGIN
PRINT '正在交易，请稍等...'
INSERT INTO transInfo VALUES('',@cardID,@transType,@transMoney,'')
PRINT '交易成功,您成功从卡上存入了'+CONVERT(VARCHAR(20),@transMoney)
END

ELSE
BEGIN
IF(@pwd=@pass)
BEGIN
PRINT '正在交易，请稍等...'
INSERT INTO transInfo VALUES('',@cardID,@transType,@transMoney,'')
PRINT '支取成功,您成功从卡上支取了'+CONVERT(VARCHAR(20),@transMoney)
END
ELSE
BEGIN
PRINT '密码错误，请重新输入！'
END
END
GO

EXEC proc_transInfo '王五',100,'支取','888888' ----模拟存入与支取

----创建产生随机卡号的存储过程
CREATE PROC proc_RANDcardID @mycardID VARCHAR(20) OUTPUT
AS
DECLARE @rand NUMERIC(15,8)
DECLARE @randcardID VARCHAR(10)
SELECT @rand=RAND(CONVERT(INT,DATEPART(mm,GETDATE()) * 100000)+
CONVERT(INT,DATEPART(ss,GETDATE()) * 1000)+
CONVERT(INT,DATEPART(ms,GETDATE())))
SET @randcardID=SUBSTRING(CONVERT(VARCHAR(10),@rand),3,4)+' '+SUBSTRING(CONVERT(VARCHAR(10),@rand),7,4)
SET @mycardID='1010 3576'+' '+@randcardID
GO

----调用随机卡号生成存储过程，产生随机卡号
DECLARE @mycardID VARCHAR(20)
EXEC proc_RANDcardID @mycardID OUTPUT
PRINT '产生的随机卡号为:'+@mycardID

----创建开户的存储过程
drop proc proc_openAccount
CREATE PROC proc_openAccount @customerName VARCHAR(10),@PID NUMERIC(18,0),@telephone VARCHAR(15),@openMoney MONEY,@savingType VARCHAR(8),@address VARCHAR(30)
AS
WHILE(1=1)
BEGIN
DECLARE @mycardID VARCHAR(20)
EXEC proc_RANDcardID @mycardID OUTPUT
IF NOT EXISTS(SELECT cardID FROM cardInfo WHERE cardID=@mycardID)
BEGIN
INSERT INTO userInfo VALUES(@customerName,@PID,@telephone,@address)
DECLARE @customerID INT
SELECT @customerID=customerID FROM userInfo WHERE PID=@PID
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID) VALUES(@mycardID,@savingType,@openMoney,@openMoney,@customerID)
PRINT '开始开户，请稍等....'
PRINT '开户成功!您的卡号为：'+@mycardID
PRINT '开户日期:'+CONVERT(VARCHAR(20),GETDATE())
PRINT '开户金额为:'+CONVERT(VARCHAR(20),@openMoney)
BREAK
END
END
GO

---模拟开户，调用存储过程proc_openAccount
EXEC proc_openAccount '沈非','320504198607221447','13914048959',3999,'活期','苏州职业大学'

---创建帐户间转帐的存储过程
drop proc proc_trans
CREATE PROC proc_trans @outcardID VARCHAR(20),@incardID VARCHAR(20),@transMoney MONEY
AS
DECLARE @outbalance MONEY,@inbalance MONEY,@isReportLoss1 BIT,@isReportLoss2 BIT
SELECT @outbalance=balance FROM cardInfo WHERE cardID=@outcardID
SELECT @inbalance=balance FROM cardInfo WHERE cardID=@incardID
SELECT @isReportLoss1=isReportLoss FROM cardInfo WHERE cardID=@outcardID
SELECT @isReportLoss2=isReportLoss FROM cardInfo WHERE cardID=@incardID
IF(@isReportLoss1 =0 AND @isReportLoss2=0)
BEGIN
IF(@outbalance-@transMoney<1)
BEGIN
PRINT '正在交易中，请稍等....'
PRINT '交易失败，余额不足，无法转帐!'
PRINT '您的余额为:'+CONVERT(VARCHAR(20),@outbalance)+' RMB'
END
ELSE
BEGIN
PRINT '正在交易中，请稍等....'
INSERT INTO transInfo(cardID,transType,transMoney) VALUES(@outcardID,'支取',@transMoney)
INSERT INTO transInfo(cardID,transType,transMoney) VALUES(@incardID,'存入',@transMoney)
UPDATE cardInfo SET balance=@outbalance-@transMoney WHERE cardID=@outcardID
UPDATE cardInfo SET balance=@inbalance+@transMoney WHERE cardID=@incardID
PRINT '转帐成功!'
PRINT
END
END
ELSE
PRINT '有一方卡已经挂失，请主动投案自首！交易失败'

----模拟转帐
EXEC proc_trans '1010 3576 1356 3554','1010 3576 1212 1134',1000

---添加登陆帐户
EXEC SP_ADDLOGIN 'philip','65331652'
---创建数据库用户
EXEC SP_GRANTDBACCESS 'philip'
---赋予三张表的增删改查权限
GRANT SELECT,INSERT,DELETE,UPDATE ON userInfo TO philip
GRANT SELECT,INSERT,DELETE,UPDATE ON cardInfo TO philip
GRANT SELECT,INSERT,DELETE,UPDATE ON transInfo TO philip

---赋予存储过程的执行权限
GRANT EXEC ON proc_changePass TO philip
GRANT EXEC ON proc_lossCard TO philip
GRANT EXEC ON proc_transInfo TO philip
GRANT EXEC ON proc_RANDcardID TO philip
GRANT EXEC ON proc_openAccount TO philip
GRANT EXEC ON proc_trans TO philip