旅遊資料庫

⑴ 跪求用sql sever做的旅遊景點管理資料庫的設計及相關代碼。謝謝啦

可以依靠網路Hi提醒我
有時間可以解決你的問題
更進一步的要求也可以提醒我

ES:\\
交易提醒:預付定金是陷阱

⑵ 旅遊信息管理系統 資料庫

前階段做的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

⑶ 資料庫設計旅遊景點表應該有些什麼欄位

景點名,景點類型(人文,自然),門票價格,省,市,地址,臨近機場...暫時想到這么多

⑷ 旅遊論文寫作常用的資料庫有哪些

萬方、知網、維普、中國學位論文全文資料庫

⑸ 請問在哪兒可以下載到包含全國的旅遊景點的資料庫，MySql、Sql Server、sqlite都可以，謝謝！

這種東西可遇不可求，一般的不會拿來共享

⑹ 旅遊管理系統資料庫

For a description of your ，
告訴我你的問題和聯系方式,
可以與我們聯系進一步需求,
有可能幫你,
網路_Hi聯系我,
此回復針對所有來訪者和需求者有效,
ES:\\

⑺ 做簡單旅遊網站,資料庫需要哪些表，欄位 只需要將行程線路添加到資料庫 可以查看就行的

這個問題不能問別人的，關鍵是看你要保存哪些數據，要保存哪些數據要看你有哪些功能。所以當你增加功能時，有時要更改資料庫的設計。
因為你有用戶管理，肯定有一個用戶表,常見欄位為： ID，用戶名，密碼，許可權或級別，如果你要存放用戶最近最登錄時間，哪就要最後登錄時間欄位。
肯定要有線路表，因為不同的線路，節點多少不一樣，所以要設線路ID，線路名2個欄位。
再弄個線路站點表：ID,線路ID，站點名稱，序號。這樣根據線路ID可以把各站點按順序查詢出來。
你先搞清楚自己有哪些功能，要保存哪些數據全部列出來。

⑻ access資料庫 大作業 旅遊資料庫

編資料庫，不但要懂得ACCESS，還要懂行業業務流程，這樣才能編寫高質量的資料庫。可留下QQ嗎？我聯系你。

⑼ 旅遊管理系統資料庫有哪些表

要看具體的系統有哪些功能,不能籠統的告訴你有哪些表.因為即使相同的內容,表的名字也可以不一樣。