Database of tourism information management system

The ATM machine made in the previous stage needs to be modified by the landlord, hehe!

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!"

print

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.