簡(jiǎn)介面向數(shù)據(jù)庫(kù)課程設(shè)計(jì)學(xué)生這套航空訂票系統(tǒng)設(shè)計(jì)文檔覆蓋需求分析、系統(tǒng)結(jié)構(gòu)數(shù)據(jù)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、系統(tǒng)功能模塊設(shè)計(jì)到項(xiàng)目總結(jié)的完整流程可作為課程報(bào)告撰寫(xiě)與數(shù)據(jù)庫(kù)建模的參考范本。壓縮包共1個(gè)doc文檔體積約335KB報(bào)告包含E-R圖、關(guān)系模式、數(shù)據(jù)流程圖并詳細(xì)設(shè)計(jì)了旅客、航班、預(yù)訂、支付等核心實(shí)體及其關(guān)聯(lián)同時(shí)給出數(shù)據(jù)表字段描述、程序功能模塊和系統(tǒng)功能分析有助于理解數(shù)據(jù)庫(kù)概念建模、關(guān)系規(guī)范化與實(shí)際業(yè)務(wù)邏輯的映射。文檔還梳理了需求規(guī)定、功能模塊劃分和實(shí)現(xiàn)流程對(duì)完成課程設(shè)計(jì)中的文檔編寫(xiě)與數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)有直接幫助。目前已有97人學(xué)習(xí)瀏覽適合正在開(kāi)展航空訂票類(lèi)數(shù)據(jù)庫(kù)課程設(shè)計(jì)或希望系統(tǒng)掌握訂票系統(tǒng)架構(gòu)的學(xué)生參考使用。1. 最新航空訂票系統(tǒng)一份數(shù)據(jù)庫(kù)課程設(shè)計(jì)文檔背后的完整技術(shù)棧打開(kāi)這份《最新航空訂票系統(tǒng)數(shù)據(jù)庫(kù)課程設(shè)計(jì).doc》之前先想清楚一個(gè)事實(shí)絕大多數(shù)人手里拿到的不是能直接運(yùn)行的軟件而是一份要交給老師評(píng)分的文檔外加一個(gè)從零開(kāi)始搭起來(lái)的訂票系統(tǒng)。這個(gè)題目在數(shù)據(jù)庫(kù)課程設(shè)計(jì)里屬于經(jīng)典中的經(jīng)典覆蓋了從 ER 圖、關(guān)系模式、范式優(yōu)化到 SQL 增刪改查、視圖、存儲(chǔ)過(guò)程、觸發(fā)器、并發(fā)控制的全部考點(diǎn)。它同時(shí)也是入門(mén)級(jí)項(xiàng)目里最容易“把代碼寫(xiě)出來(lái)但答辯說(shuō)不上原理”的題目因?yàn)闃I(yè)務(wù)不復(fù)雜但數(shù)據(jù)庫(kù)設(shè)計(jì)的好壞一眼就能看出來(lái)。這篇筆記要解決的問(wèn)題是把這份課程設(shè)計(jì)文檔變成一套能在 MySQL 上復(fù)現(xiàn)、能自圓其說(shuō)、能應(yīng)對(duì)老師追問(wèn)的完整落地方案。適合正在做數(shù)據(jù)庫(kù)課設(shè)大作業(yè)的在校生也適合需要快速上手關(guān)系型數(shù)據(jù)庫(kù)設(shè)計(jì)流程的轉(zhuǎn)行開(kāi)發(fā)者。2. 把需求拆成關(guān)系模式航班、乘客與訂單的邊界劃分2.1 先定業(yè)務(wù)邊界再畫(huà) ER 圖訂票系統(tǒng)有哪些實(shí)體必須出現(xiàn)航空訂票系統(tǒng)如果只是“訂票”兩個(gè)字做出來(lái)的表結(jié)構(gòu)很容易走偏。常見(jiàn)做法是先把業(yè)務(wù)拆成三個(gè)閉環(huán)航班信息管理、乘客信息管理、訂單流轉(zhuǎn)下單、支付、退票、改簽。圍繞這三個(gè)閉環(huán)實(shí)體至少要包括航班、乘客、訂單、訂單明細(xì)、艙位價(jià)格。為什么訂單明細(xì)不能省——一次下單可能同時(shí)買(mǎi)兩張票而兩張票的艙位價(jià)格可能不同如果直接把票價(jià)字段塞進(jìn)訂單表一個(gè)月后想統(tǒng)計(jì)“經(jīng)濟(jì)艙總收入”就得解析字符串或者靠猜這就是典型的表設(shè)計(jì)沒(méi)做干凈。實(shí)體確定后屬性也要跟著業(yè)務(wù)走。航班的屬性里航班號(hào)、起降時(shí)間、起降機(jī)場(chǎng)、機(jī)型、總座位數(shù)這幾個(gè)是必須的剩余座位數(shù)建議別落表放到后面用視圖或?qū)崟r(shí)計(jì)算去實(shí)現(xiàn)否則每次訂票都要同步修改兩個(gè)字段出現(xiàn)不一致的概率很高。乘客屬性里身份證號(hào)是天然的候選鍵但要考慮一個(gè)現(xiàn)實(shí)課程設(shè)計(jì)里可能要用測(cè)試數(shù)據(jù)批量插入身份證號(hào)既做業(yè)務(wù)鍵又做主鍵會(huì)讓后續(xù)修改非常麻煩我一般會(huì)把它設(shè)成 UNIQUE 約束而不是 PRIMARY KEY。訂單屬性里最容易被忽略的是訂單狀態(tài)建議用 TINYINT 存數(shù)字0 代表已創(chuàng)建、1 代表已支付、2 代表已出票、3 代表已退票不要在數(shù)據(jù)庫(kù)里存中文狀態(tài)排序和統(tǒng)計(jì)都會(huì)難受。2.2 從 ER 圖到關(guān)系模式五張表的接口設(shè)計(jì)與外鍵方向把 ER 圖轉(zhuǎn)成關(guān)系模式時(shí)最容易翻車(chē)的點(diǎn)是外鍵的方向。航空訂票系統(tǒng)的關(guān)系其實(shí)很清楚一個(gè)乘客可以有多張訂單一張訂單可以包含多個(gè)乘客家庭出游一次買(mǎi)三張票所以需要三張核心表加一張中間表。航班和訂單之間是“一對(duì)多”外鍵加在訂單表乘客和訂單之間是“多對(duì)多”通過(guò)訂單明細(xì)表去橋接。這里有個(gè)經(jīng)驗(yàn)不要把乘客直接掛在訂單表上訂單表只存“誰(shuí)下的單、什么時(shí)候下的單、總價(jià)、狀態(tài)”具體每個(gè)人坐哪個(gè)航班、什么艙位、多少錢(qián)全部放明細(xì)表。字段類(lèi)型的選擇也是數(shù)據(jù)庫(kù)課程設(shè)計(jì)的評(píng)分點(diǎn)。航班編號(hào)用 CHAR(6) 而不是 INT因?yàn)楹桨嗵?hào)像 CA1831 這種是帶字母的編碼INT 存不了日期和時(shí)間用 DATETIME 而不是拆成兩個(gè) VARCHAR金額用 DECIMAL(10,2) 而不是 FLOAT原因應(yīng)該不用多講結(jié)算場(chǎng)景出現(xiàn)浮點(diǎn)誤差直接是事故。一張能拿到高分的表光看字段類(lèi)型就能看出設(shè)計(jì)者有沒(méi)有做過(guò)真實(shí)項(xiàng)目。關(guān)系模式定稿之后還要寫(xiě)清楚每個(gè)關(guān)系的函數(shù)依賴(lài)和規(guī)范化說(shuō)明。課程設(shè)計(jì)文檔里這一節(jié)占的篇幅不多但分值不小重點(diǎn)是解釋“為什么這張表已經(jīng)在第三范式”訂單明細(xì)表的主鍵是訂單編號(hào)加乘客編號(hào)的組合鍵所有非主屬性完全依賴(lài)于這個(gè)組合鍵不存在部分依賴(lài)這是第二范式同時(shí)艙位定價(jià)、折扣這些字段只依賴(lài)航班和艙位不會(huì)在明細(xì)表里出現(xiàn)冗余這是第三范式。老師的追問(wèn)一般就集中在范式這里能用一句話(huà)講清楚就可以過(guò)關(guān)。3. 建庫(kù)建表與約束MySQL 8.0 上跑通最小航班庫(kù)3.1 建庫(kù)腳本字符集、排序規(guī)則與存儲(chǔ)引擎的選型數(shù)據(jù)庫(kù)選型上課程設(shè)計(jì)最常見(jiàn)的組合是 MySQL 8.x 加 Navicat 或 MySQL Workbench?！白钚潞娇沼喥毕到y(tǒng)”這個(gè)標(biāo)題里的“最新”更多是指業(yè)務(wù)形態(tài)不是指什么冷門(mén)數(shù)據(jù)庫(kù)。建庫(kù)這一步字符集和排序規(guī)則直接決定后面會(huì)不會(huì)出現(xiàn)中文亂碼和索引失效的問(wèn)題。UTF-8 在 MySQL 里是個(gè)歷史遺留的大坑MySQL 8.0 里 utf8mb4 才是真正的四字節(jié) UTF-8utf8 只是 utf8mb3 的別名遇到生僻字或 Emoji 直接撐爆字段排序規(guī)則用 utf8mb4_unicode_ci 而不是 utf8mb4_general_ci前者對(duì)中文和多語(yǔ)言的排序更符合 Unicode 標(biāo)準(zhǔn)。以下是課程設(shè)計(jì)里可以直接復(fù)用的建庫(kù)腳本。DROP DATABASE IF EXISTS air_ticket_db; CREATE DATABASE air_ticket_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE air_ticket_db; CREATE TABLE flight ( flight_id CHAR(6) NOT NULL COMMENT 航班內(nèi)部編號(hào), flight_no VARCHAR(10) NOT NULL COMMENT 航班號(hào)如 CA1831, origin CHAR(3) NOT NULL COMMENT 出發(fā)機(jī)場(chǎng)三字碼, destination CHAR(3) NOT NULL COMMENT 到達(dá)機(jī)場(chǎng)三字碼, depart_time DATETIME NOT NULL COMMENT 計(jì)劃起飛時(shí)間, arrive_time DATETIME NOT NULL COMMENT 計(jì)劃到達(dá)時(shí)間, aircraft VARCHAR(20) NOT NULL DEFAULT B737 COMMENT 機(jī)型, total_seats SMALLINT NOT NULL COMMENT 總座位數(shù), PRIMARY KEY (flight_id), UNIQUE KEY uk_flight_no (flight_no, depart_time), KEY idx_route (origin, destination) ) ENGINE InnoDB;這段建表腳本里的每個(gè)參數(shù)都有具體用途。flight_no加depart_time做聯(lián)合唯一鍵是為了避免同一天同一航班號(hào)被插入兩次這在訂票業(yè)務(wù)里屬于完整性問(wèn)題idx_route是為“查某個(gè)航線(xiàn)的所有航班”這種高頻查詢(xún)準(zhǔn)備的不加索引的話(huà)數(shù)據(jù)庫(kù)全表掃描會(huì)隨著測(cè)試數(shù)據(jù)量增大而越來(lái)越慢COMMENT注釋雖然不影響運(yùn)行但課程設(shè)計(jì)評(píng)分時(shí)老師看表結(jié)構(gòu)就能理解字段含義比在文檔里單獨(dú)解釋省事得多。存儲(chǔ)引擎必須用 InnoDB不要用 MyISAM理由在 5.3 節(jié)講死鎖和事務(wù)時(shí)會(huì)直接體現(xiàn)出來(lái)。3.2 乘客表與訂單表的約束設(shè)計(jì)CHECK、外鍵與默認(rèn)值4. 核心業(yè)務(wù) SQL訂票、退票與改簽的增刪改查閉環(huán)4.1 訂票存儲(chǔ)過(guò)程事務(wù)邊界與行鎖加鎖順序Airline reservation system, database course design, Chen definition, physical design, database connection pool, transaction, deadlock, view, stored procedure, trigger. ## 4. 核心業(yè)務(wù) SQL訂票、退票與改簽的增刪改查閉環(huán)4.1 訂票存儲(chǔ)過(guò)程事務(wù)邊界與行鎖加鎖順序航司的核心業(yè)務(wù)循環(huán)就是盡力賣(mài)光每個(gè)航班的有效座位而數(shù)據(jù)庫(kù)面試官最?lèi)?ài)問(wèn)的問(wèn)題之一就是“訂票時(shí)怎樣避免最后一張票被兩個(gè)人同時(shí)搶到”。如果不假思索地在業(yè)務(wù)代碼里先 SELECT 余票數(shù)再 INSERT 訂單高并發(fā)下幾乎必掛因?yàn)檫@兩條 SQL 之間存在時(shí)間窗口兩個(gè)會(huì)話(huà)可能同時(shí)讀到余票為 1然后各自插入訂單最后航班冗余銷(xiāo)售。下面存儲(chǔ)過(guò)程用“先查后寫(xiě)且鎖定”的方式處理這個(gè)問(wèn)題。DELIMITER // DROP PROCEDURE IF EXISTS sp_book_ticket; CREATE PROCEDURE sp_book_ticket ( IN p_flight_id CHAR(6), IN p_passenger_id CHAR(18), IN p_order_id INT, IN p_cabin_class VARCHAR(10), OUT p_success TINYINT ) SQL SECURITY INVOKER BEGIN DECLARE v_remaining INT DEFAULT 0; START TRANSACTION; -- 鎖定航班行防止其他會(huì)話(huà)同時(shí)讀到相同余票 SELECT remaining_seats INTO v_remaining FROM flight WHERE flight_id p_flight_id FOR UPDATE; IF v_remaining 0 THEN SET p_success 0; ROLLBACK; ELSE INSERT INTO booking_detail (order_id, passenger_id, cabin_class, ticket_price) SELECT p_order_id, p_passenger_id, p_cabin_class, price FROM cabin_price WHERE flight_id p_flight_id AND cabin_class p_cabin_class; IF ROW_COUNT() 0 THEN SET p_success 0; ROLLBACK; ELSE UPDATE flight SET remaining_seats remaining_seats - 1 WHERE flight_id p_flight_id; SET p_success 1; COMMIT; END IF; END IF; END // DELIMITER ;這段存儲(chǔ)過(guò)程把并發(fā)安全性放在了數(shù)據(jù)庫(kù)層面而不是業(yè)務(wù)代碼層面。FOR UPDATE加的是行級(jí)排他鎖當(dāng)?shù)谝粋€(gè)會(huì)話(huà)執(zhí)行到這一句時(shí)MySQL 會(huì)對(duì)該航班的行加鎖直到事務(wù)提交或回滾第二個(gè)會(huì)話(huà)再來(lái)執(zhí)行同一個(gè)存儲(chǔ)過(guò)程會(huì)在同一行SELECT ... FOR UPDATE上等待直到第一個(gè)會(huì)話(huà)釋放鎖。這樣“先到先得”的邏輯就完全由數(shù)據(jù)庫(kù)保證了。存儲(chǔ)過(guò)程的參數(shù)按用途分成三個(gè)維度業(yè)務(wù)鍵、關(guān)聯(lián)鍵和輸出參數(shù)。p_flight_id是航班內(nèi)部編碼p_passenger_id用乘客身份證號(hào)作為關(guān)聯(lián)鍵而不是依賴(lài)自增主鍵這樣后續(xù)退票、改簽時(shí)業(yè)務(wù)語(yǔ)義清晰p_success用 OUT 類(lèi)型返回結(jié)果便于 JDBC 或 Python 調(diào)用端直接判斷成功與否。唯一要留意的是INSERT INTO ... SELECT中間如果查不到對(duì)應(yīng)艙位價(jià)格會(huì)靜默插入零行并返回成功所以代碼里必須判斷ROW_COUNT()。真正做課程設(shè)計(jì)時(shí)我建議把這個(gè)存儲(chǔ)過(guò)程當(dāng)成版本的“代碼庫(kù)模板”原因是事務(wù)要明確、異常要回滾、業(yè)務(wù)結(jié)果要有顯式的成功標(biāo)志這三點(diǎn)在架構(gòu)設(shè)計(jì)一致性上至關(guān)重要而存儲(chǔ)過(guò)程中過(guò)程式的IF/ELSE邏輯恰好能幫助初學(xué)者理解數(shù)據(jù)庫(kù)內(nèi)部流程控制——這是單獨(dú)用 Java 寫(xiě) DAO 很難看清的攻堅(jiān)難點(diǎn)。4.2 退票與改簽 SQL觸發(fā)器的一致性和時(shí)點(diǎn)問(wèn)題4.3 視圖與離線(xiàn)報(bào)表把查詢(xún)語(yǔ)義變成可復(fù)用的對(duì)象5. 并發(fā)與排查死鎖、連接池和課程設(shè)計(jì)最常翻車(chē)的 5 個(gè)坑5.1 現(xiàn)象余票變負(fù)數(shù)原因丟失更新5.2 現(xiàn)象MySQL 8 連接失敗原因驅(qū)動(dòng)版本與時(shí)區(qū)參數(shù)5.3 現(xiàn)象訂單刪除失敗原因外鍵約束與刪除順序5.4 現(xiàn)象插入中文亂碼原因連接參數(shù)沒(méi)有指定字符集5.5 現(xiàn)象死鎖原因多事務(wù)加鎖順序不一致6. 收尾技巧數(shù)據(jù)校驗(yàn)?zāi)_本與課程設(shè)計(jì)答辯自檢清單本文還有配套的精品資源點(diǎn)擊獲取