Traditional Culture Encyclopedia - Travel guide - Database of tourism information management system
Database of tourism information management system
There are still two small steps to be completed, so the landlord can see for himself!
Use master
If it exists (select * from sysdatabases, where name='bankDB')
Delete database bankDB
go to
-build bankDB.
Create database bankDB
exist
(
name='bankDB ',
filename='D:\bank\bankDB.mdf ',
File growth = 15%
)
Using bankDB
go to
-create a user information table (userInfo)
Create table user information
(
customerID INT IDENTITY( 1, 1),
CustomerName VARCHAR( 10) is not empty.
PID value (18,0) is not empty.
Phone VARCHAR( 15) is not empty.
Address VARCHAR(30)
)
go to
-Create constraints for information tables
Change table user information add
Constraint PK _ custonid primary key (customerID),
Constraint CK_PID check (len(PID)= 18 or len(PID)= 15),
Constraint UQ_PID unique (PID),
Constraint CK _ phone check (similar to "[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]] [0-9][0-9][0-9] "phone or similar to" [0-9] [0-9] [0-9] [0-9] "
-create a card information table (cardInfo)
Create table card information
(
CardID VARCHAR(20) is not empty.
CurType VARCHAR(6) is not empty.
savingType VARCHAR(8),
OpenDate date time is not empty.
OpenMoney MONEY is not empty.
The balance is not empty.
VARCHAR(6) passed is not empty.
IsReportLoss bit is not empty.
CustomerID INT is not empty.
)
go to
-Constraints on creating bank card information tables
Change table card information add
Constraint PK_cardID primary key (cardID),
Constraint CK_cardID check (cardidlike'10103576 [0-9] [0-9] [0-9] [0-9] [0-9] [0-9].
Constraint DF _ curType curType defaults to RMB,
Constraintck _ saving type check (save type in ('current',' fixed convenience',' general')),
Constraint DF_openDate default GETDATE() FOR openDate,
Constraint CK _ openmoney check (openmoney >; = 1),
Constraint CK _ balance check (balance & gt= 1),
Constraint CK _ passed the check (LEN(pass)=6),
Constraint DF_pass defaults to 888888',
Isreportboss constraint DF_isReportLoss has default value' 0'.
Constraint FK _ customer ID foreign key (customer ID) refers to user information (customer ID).
go to
-create a transaction information table (transInfo)
Create table transfer information
(
The transit date time is not empty.
CardID VARCHAR(20) is not empty.
TransType VARCHAR(4) is not empty.
TransMoney currency is not empty.
NTEXT commented
)
go to
-Constraints for creating transaction information tables
Change table transmission information addition
Constraint df _ transDatedefaultgetdate () of transdate
Constraint FK _ card ID foreign key (card ID) refers to card Info (card ID),
Constraintck _ trans type check (transType=' deposit' or trans type =' withdrawal'),
Constraint CK_transMoney check (transMoney & gt0)
-Test the inserted data.
Insert userInfo values ('Zhang San',' 1234567890 12340',' 0167898978',' Beijing Haidian').
Insert the userInfo values ('Li Si',' 320504198607221250',' 05 12-6533 1652',' Suzhou, Jiangsu').
Insert userInfo values ('Wu Wang',' 320504 19860722333',' 05 12-65323442',' Nanjing, Jiangsu').
Insert userInfo values ('Liu Zhao',' 3205041986071313',' Suzhou, Jiangsu').
Insert userInfo values ('Fang Liu',' 3205041944071543',' 05 12-6533 1243',' Yancheng, Jiangsu').
Insert into cardinfo value ('101035712134',' RMB',' current account',' 2006-10-06544'.
Insert into cardinfo value ('101035712125555',' RMB',' current account',' 2006-101.
Insert into cardinfo value ('10/0 3576 2323 3343',' RMB',' current account',' 2006- 12-0 1',15345.
Insert the cardinfo value ('1010 35761456 3454',' RMB',' current account',' 2006-1-28', 2006.50009.00000000000505
Insert into cardinfo values ('1010 35761356 3554',' RMB',' current',' 2006- 12-05', 22143'.
-Insert a statement that violates the constraint.
Inserting into userInfo values ('Shen Fei',' 123456789',' 05 12-67998978',' Suzhou, Jiangsu')-violates the check constraint of ID card length.
Inserted into the userInfo values ('Shenda',' 32050419860713',' 05 12-67998979',' Suzhou, Jiangsu')-the only one that violates the ID card.
Insert into cardinfo value ('1 010 35761456 3424',' RMB',' current',' 2006-1-28', 0.0000.
Inserted into the transportation information value (','1010 35761456 3424',' withdrawal', 1000' ')- the transaction card number must exist in cardInfo, which violates the constraints of primary key and foreign key.
-View all forms
select * from userInfo
select * from cardInfo
select * from transInfo
-Create a stored procedure: change the password according to the card number.
CREATE PROC PROC _ change pass @ incardID VARCHAR(20),@inoldPass VARCHAR(6),@newPass 1 VARCHAR(6),@newpass2 VARCHAR(6)
be like
DECLARE @oldPass VARCHAR(6)
If it exists (select * from card info where card id = @ incardid)
begin
SELECT @ old pass = pass FROM card info WHERE card id = @ incar did
IF(@oldpass=@inoldPass)
begin
IF(@newpass 1=@newpass2)
begin
Update cardinfo setpass = @ newpass1where cardID=@incardID.
Print "Password modified successfully!"
end
other
Print "Please confirm your new password!"
end
other
Print' The original password you entered is wrong, please re-enter!'
end
other
Print' The card number you entered does not exist, please re-enter!'
-Test the stored procedure used to change the password.
EXEC proc _ change pass ' 10 10 3576 12 1 134 ',' 888888 ',' 123456 ',' 123456 '
-Create a stored procedure for loss reporting of bank cards.
CREATE PROC _ loss card @ inCustomerName VARCHAR( 10),@inPID NUMERIC( 18,0),@ Intel phone VARCHAR( 15),@inAddress VARCHAR(30),@inCardPass VARCHAR(6)
be like
Declare @customerName VARCHAR( 10), @ PID Numeric (18,0), @telephone VARCHAR( 15), @address VARCHAR(30), @cardPass VARCHAR(6).
SELECT @ customer name = customer name, @PID=PID, @ phone = phone, @ address = address FROM userInfo where PID=@inPID.
SELECT @ card pass = pass FROM card info WHERE customerID =(SELECT customerID FROM userInfo WHERE PID = @ inPID)
IF((@ customerName = @ inCustomerName)AND(@ PID = @ inPID)AND(@ Intel phone = @ telephone)AND(@ in address = @ address)AND(@ inCardPass = @ card pass))
begin
Update cardinfo set is report loss =1,where customerID= (select customerID from userInfo, where PID=@inPID).
end
other
Print "Your information is incorrect, please check and re-enter!"
-Test the stored procedure of loss report.
EXEC proc_lossCard' Wu Wang',' 320504 19860722333',' 05 12-65323442',' Nanjing, Jiangsu',' 88888'
-Bank circulation statistics
Declare "internal funds" and "external funds"
Select @ inmoney = sum (transmoney) from transminfowhere transmype =' deposit'
Select @ outmoney = sum (transmoney) from transinfo where transtype =' withdrawal'
PRINT' Total bank circulation is:' +convert (varchar (20), @ inmoney-@ outmoney)+'RMB'
PRINT' Profit settlement:' +convert (varchar (20), @ outmoney * 0.008-@ inmoney * 0.003)+'RMB'
go to
-Inquire about the bank card information of the monthly trading champion.
SELECT * FROM transInfo where
Print conversion (VARCHAR(20), @money)
SELECT * FROM cardInfo where
-Inquire about the card information that has not been traded for half a year.
SELECT * FROM card info WHERE card id NOT IN(SELECT card id FROM trans info WHERE DATEDIFF(mm,transDate,getdate())& gt; 7)//////////
-Inquire about the accounts opened this week.
SELECT * FROM card info where DATEDIFF(WEEK,openDate,getdate())& lt; 1
-Inquire about the customer information of the loss reporting account.
SELECT * FROM userInfo where customerID IN(SELECT customerID FROM card info where is report loss = 1)
-dunning reminder service
Select customerName as the customer name, phone number as the contact number, and balance as the balance from.
UserInfo internal connection cardinfo on userinfo.customerid = cardinfo.customerid where balance <; 200 and datepart(day, getdate ()) > =28
-add a nonclustered index to the cardID field of the transInfo table.
Use FILLFACTOR=70 to create a nonclustered index IX_transInfo_cardID on transInfo(cardID).
-Test index
Select * from transinfo (index = IX _ transinfo _ card) where card ='1010 35761212134'
-Create a view
Create view view_userInfo
be like
Select customerID as customer number, customerName as customer name, PID as ID number, telephone as phone number and address as contact address from userInfo.
Create view view_cardInfo
be like
Select card number as cardId, currency as curType, deposit type as savingType, account opening date as account opening date, account opening amount as openMoney, balance as account balance, password as pass, whether to report loss or not, and customer number as the customer number in cardInfo.
Create view view_transInfo
be like
Select transaction date as transaction date, card number as card number, transaction type as transaction type, transaction amount as transaction amount, and remarks as from transaction information.
-Look at the scenery
SELECT * FROM view_userInfo
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
-Create a trigger
Create trigger trig_trans
About transInfo
Used to insert
Declare @transType VARCHAR(4)
-create trigger trig_trans.
Discard trigger trig_trans
Create trigger trig_trans on transInfo.
Used to insert
be like
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)
Start TRAN
IF(@ transType = ' retraction ')
begin
If (@ balance-@ transmoney <; 1)- If the balance after withdrawal is less than 1 yuan.
begin
Print "The transaction is in progress, please wait a moment ..."
Roll back trans- cancels the transaction and rolls back.
Print' Insufficient balance, transaction failed!'
end
other
begin
Print "The transaction is in progress, please wait a moment ..."
Commit ran- the transaction was successful.
Update cardinfo set balance = @ balance-@ transmoney where cardID=@cardID.
Print' Transaction successful,' +convert (varchar (10), @ customername)+' You are withdrawing money, and the current balance is' +convert (varchar (20), @ balance-@ transmoney)+' RMB'.
end
end
other
begin
Print "The transaction is in progress, please wait a moment ..."
Submit TRAN
Update cardinfo set balance = @ balance+@ transmoney where cardID=@cardID.
The' Print' transaction was successful.' +convert (varchar (10), @ customername)+' You made a deposit, and the current balance is' +convert (varchar (20), @ balance+@ transmoney)+' RMB'.
end
Insert the transportation information value (','101035712125555',' withdrawal', 999,' aa').
-Create a stored procedure for deposits and withdrawals.
drop proc proc_transInfo
CREATE PROC PROC _ trans info @ customerName VARCHAR( 10),@transMoney MONEY,@transType VARCHAR(4),@pass VARCHAR(6)= ' '
be like
DECLARE @pwd VARCHAR(6)
DECLARE @cardID VARCHAR(20)
Select @ pwd = pass, @ cardid = cardinfrom cardinfo where customerid = (select customerid from userinfo where customername = @ customername)-the card number and password of the user queried in the userinfo name field.
Print' Your card number is' +convert (varchar (20), @ CARDID)- output the user's card number.
IF(@transType=' deposit ')
begin
Print "The transaction is in progress, please wait a moment ..."
Insert into transInfo value (',@cardID, @transType, @transMoney,'')
The' print' transaction was successful, and you have successfully deposited' +convert (varchar (20), @ transmoney' from the card.
end
other
begin
IF(@pwd=@pass)
begin
Print "The transaction is in progress, please wait a moment ..."
Insert into transInfo value (',@cardID, @transType, @transMoney,'')
Print' Withdrawal successful, you have successfully returned the card' +convert (varchar (20), @ transmoney).
end
other
begin
Print "Wrong password, please re-enter!"
end
end
go to
EXEC proc_transInfo' Wu Wang', 100,' withdrawal',' 888888 '- analog deposit and withdrawal.
-Create a stored procedure for generating random card numbers.
Create procproc _ randcardid @ mycardidvarchar (20) output.
be like
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 = ' 10 10 3576 '+' '+@ randcardID
go to
-Call the random card number generation stored procedure to generate the random card number.
DECLARE @mycardID VARCHAR(20)
EXEC proc_RANDcardID @mycardID output
The random card number generated by PRINT' is' +@mycardID.
-Create a stored procedure for opening an account.
Delete procedure proc_openAccount
CREATE PROC _ openAccount @ customerName VARCHAR( 10)、@PID NUMERIC( 18,0)、@telephone VARCHAR( 15)、@openMoney MONEY、@savingType VARCHAR(8)、@address VARCHAR(30)
be like
WHILE( 1= 1)
begin
DECLARE @mycardID VARCHAR(20)
EXEC proc_RANDcardID @mycardID output
If it does not exist (select cardID from cardInfo, where cardID=@mycardID).
begin
Insert user information value (@ Customer Name, @PID, @ Phone, @ Address)
Declare @customerID INT
Select @ customerid = customerid from userinfo where PID=@PID.
Insert into cardInfo(cardID, savingType, openMoney, balance, customerID) value (@mycardID, @savingType, @openMoney, @customerID).
Print' Start opening an account, please wait a moment ...'
The "Print" account was opened successfully! Your card number is' +@mycardID.
PRINT' account opening date:' +CONVERT(VARCHAR(20), GETDATE ())
PRINT' account opening amount is:' +convert (varchar (20), @ openmoney).
break
end
end
go to
-Simulate account opening and call the stored procedure proc_openAccount.
EXEC proc_openAccount' Shen Fei',' 320504198607221447',' 139 14048959', 3999,' current',' Suzhou Vocational University'
-Create a stored procedure for inter-account transfer.
Delete Process Process _ Transaction
CREATE PROC PROC _ trans @ out cardid VARCHAR(20),@incardID VARCHAR(20),@ transMoney MONEY
be like
Declare @outbalance MONEY, @inbalance MONEY, @ isreportoss1bit, @ isreportoss2bit.
Select @ outbalance = balance from cardinfo where cardID=@outcardID.
SELECT @ in balance = balance FROM card info WHERE card id = @ incar did
Select @ isreports1= isreports from cardinfo where cardID=@outcardID.
Select @ isreportoss2 = isreportossfrom cardinfo where cardID=@incardID.
If (@ isreportoss1= 0 and @ isreportoss2 = 0)
begin
If (@ outbalance-@ transmoney <; 1)
begin
Print "Transaction in progress, please wait ..."
The' Print' transaction failed, and the balance is insufficient. Cannot transfer! '
PRINT' Your balance is:' +convert (varchar (20), @ outbalance)+'RMB'
end
other
begin
Print "Transaction in progress, please wait ..."
Insert transinfo (card, transaction type, transmoney) value (@ outcardid,' withdrawal', @transMoney).
Insert transinfo (card, transaction type, transaction amount) values (@ incremental,' deposit', @transMoney).
Update cardinfo set balance = @ outbalance-@ transmoney where cardid = @ outbardid.
Update cardinfo set balance = @ inbalance+@ transmoney, where cardid = @ incremental.
Print "Transmission successful!"
end
end
other
Print: One of the cards has been reported lost, please surrender! Transaction failed'
-Simulated transfer.
EXEC proc _ trans ' 10 10 3576 1356 3554 ',' 10 3576 12 12 1 134 ', 1000
-Add login account
EXEC SP_ADDLOGIN 'philip ',' 6533 1652 '
-Create a database user
EXEC SP _ GRANTDBACCESS ' philip '
-Grant three tables permission to add, delete and change queries.
Grant philip the right to select, insert, delete and update user information.
Grant philip the right to select, insert, delete and update cardInfo.
Grant philip the right to select, insert, delete and update transInfo.
-Grant the stored procedure execution permission.
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.
- Related articles
- How many chapters are there in the Tourism Law?
- 20 10 comparison between Yangzhou Shuntian Hotel and Shunxin Building
- I found a turtle while traveling in Wuhan. Please help me to see what species it is, what habits it has and how to raise it …
- Introduction of Ocean Ball Tourist Attractions Video of Ocean Ball Paradise
- An analysis of the consumption psychology of self-help tourists
- Why do some people like to travel and travel abroad?
- The concept of tourism business environment
- How much does it cost for two people to travel to Lijiang for a week?
- Linzhi area | Encounter a small village
- How to write a composition about autumn travel