地鐵管理系統
南科大2024年春季CS307 - Principles of Database System馬育新主講
Shenzhen-Metro
├── Project1 # part 1 of project (database design and data import)
│ ├── ShenzhenMetroDatabaseDesign
│ │ ├── src
│ │ │ ├── main
│ │ │ │ ├── java # import script in java
│ │ │ │ ├── resources # data in json
│ │ │ │ └── sql # ddl
│ └── ShenzhenMetroDatabaseDesignPython
│ ├── import_script.py # import script in python
│ └── resources # data in json
├── Project2 # part 2 of project (building an api)
│ ├── DataImport # updated data import
│ │ ├── src
│ │ │ ├── main
│ │ │ │ ├── java # updated import script in java
│ │ │ │ ├── resources # updated data
│ │ │ │ └── sql # updated ddl
│ └── ShenzhenMetro # spring boot project
│ ├── src
│ │ ├── main
│ │ │ ├── java # backend logic
│ │ │ │ └── com/sustech/cs307/project2/shenzhenmetro
│ │ │ │ ├── ShenzhenMetroApplication.java # main application driver
│ │ │ │ ├── controller # api route mapping
│ │ │ │ ├── dto # dto between client and server
│ │ │ │ ├── object # orm between database tables and application code
│ │ │ │ ├── repository # interfaces for data access
│ │ │ │ └── service # service layer containing business logic
│ │ │ └── resources # frontend logic
│ │ │ ├── application.properties # configuration file for spring boot application
│ │ │ ├── static
│ │ │ │ ├── assets
│ │ │ │ │ ├── css
│ │ │ │ │ ├── img
│ │ │ │ │ ├── js
│ │ │ │ │ └── vendor
│ │ │ │ │ ├── aos
│ │ │ │ │ ├── bootstrap
│ │ │ │ │ ├── bootstrap-icons
│ │ │ │ │ ├── glightbox
│ │ │ │ │ └── swiper
│ │ │ │ └── index.html # main html
│ │ │ └── templates
│ │ │ ├── buses
│ │ │ │ ├── create_bus.html
│ │ │ │ ├── index.html
│ │ │ │ └── update_bus.html
│ │ │ ├── landmarks
│ │ │ │ ├── create_landmark.html
│ │ │ │ ├── index.html
│ │ │ │ └── update_landmark.html
│ │ │ ├── lineDetails
│ │ │ │ ├── create_line_detail.html
│ │ │ │ ├── index.html
│ │ │ │ ├── navigate_routes.html
│ │ │ │ └── search_line_detail.html
│ │ │ ├── lines
│ │ │ │ ├── create_line.html
│ │ │ │ ├── index.html
│ │ │ │ └── update_line.html
│ │ │ ├── ongoingRides
│ │ │ │ └── index.html
│ │ │ ├── rides
│ │ │ │ ├── create_ride.html
│ │ │ │ ├── filter_ride.html
│ │ │ │ ├── index.html
│ │ │ │ └── update_ride.html
│ │ │ ├── stations
│ │ │ │ ├── create_station.html
│ │ │ │ ├── index.html
│ │ │ │ └── update_station.html
│ │ │ └── users
│ │ │ ├── card.html
│ │ │ └── passenger.html
└── README.md
專案的第一部分主要是根據所提供的資料背景，設計一個滿足關係資料庫原則的資料庫模式。設計階段完成後，我們會編寫腳本來匯入這些大型資料集。為了確保導入資料的準確性，我們需要執行一些查詢語句，並在防禦日檢查查詢結果。除此之外，我們也對數據進行了一些實驗，以獲得一些精彩的見解，如下所示。
[閱讀詳細要求]
我們相信沒有資料庫設計是完美的。事實上，我們在上面的ER圖中提出的設計是有缺陷的。在查看了資料集和每個資料集的背景之後，我們希望您能夠嘗試自己發現設計缺陷！ :)
註：對於圖形解釋，
| ID | 作業系統 | 晶片 | 記憶 | 固態硬碟 | 工具 |
|---|---|---|---|---|---|
| 1 | macOS 索諾瑪 14.4.1 | 蘋果M3 Pro | 18GB | 1TB | IDEA 2024.1（CE）、PyCharm 2023.3.4（CE）、Datagrip 2024.1 |
| 2 | Windows 11 家用版 23H2 | 第 12 代英特爾(R) 酷睿(TM) i9-12900H | 32GB | 1TB | IDEA 2024.1（CE）、Datagrip 2024.1 |
| 3 | Ubuntu 22.04.4（虛擬機器） | 蘋果M1 Pro | 16 GB | 512GB | IDEA 2024.1（CE）、Datagrip 2024.1 |
Experiment 1: Different Import Methods方法1（原始腳本）：此方法利用java.sql函式庫。首先，我們建立了與 PostgreSQL 伺服器的連線。然後我們從 JSON 檔案中讀取所有資料。接下來，我們迭代每個資料並為每個插入語句建立PreparedStatement 。最後，我們呼叫executeUpdate()方法來單獨執行每個語句。
方法2（最佳化腳本）：此方法同樣利用了java.sql函式庫，並且採用與方法1相同的資料讀取executeBatch() 。因此，我們迭代整個數據，使用PreparedStatement建立每個插入語句，並將每個PreparedStatement新增至一個批次中以進行批次執行。
方法三（運行.sql檔）：我們使用Java程式產生SQL插入語句，並採用與上述相同的資料讀取演算法將其寫入.sql檔。然後我們在 DataGrip 中執行該檔案。
由於我們在所有三種方法中使用相同的資料讀取演算法，因此我們將使用平均運行時間進行後續測試。我們最初收集了三種不同的運行時間——504 毫秒、546 毫秒和 552 毫秒——並計算出平均運行時間為 534 毫秒。
| 測試環境 | 方法 | 平均讀取時間（毫秒） | 寫入時間（毫秒） | 總時間（毫秒） | 吞吐量（語句/秒） |
|---|---|---|---|---|---|
| 1 | 1 | 第534章 | 206396 | 206930 | 8636.91 |
| 1 | 2 | 第534章 | 2114 | 2648 | 97632.92 |
| 1 | 3 | 第534章 | 13581 | 14115 | 15197.41 |
表 2 說明了不同方法的不同效能指標，其中方法 2 顯示了最高的吞吐量，而方法 1 的總時間最慢。這使得方法 2 成為即將進行的實驗中的標準測試方法。
Experiment 2: Data Import with Different Data Volumes管理和匯入不同數量的資料是確保資料庫系統的效能、可擴展性和可靠性的關鍵方面。
在導入過程之前，我們注意到「乘車」資料的數量明顯大於其他資料。基於這個想法，我們決定僅在ride.json上測試不同卷的資料導入。由於資料權重不一致，因此為其他表導入較少的資料量可能會因表之間的連接性而導致嚴重問題。
最初，我們首先導入除rides_by_id_num和rides_by_card_num表之外的所有其他表的完整資料（100%體積），以確保我們設計的一致性。為了有效管理這一點，我們對ride資料採用了分階段導入策略，從 20% 的資料子集開始，相當於 20,000 筆記錄。這個初始階段使我們能夠評估對系統效能的影響，並對導入過程進行必要的調整，而不會影響資料庫的穩定性。經過成功的驗證和效能調整後，我們繼續導入 50% 的數據，最後導入剩餘部分以完成 100% 的數據導入。請注意，我們對所有導入都使用方法 2，因為它是最快的。
| 測試環境 | 方法 | 體積 | 讀取時間（毫秒） | 寫入時間（毫秒） | 總時間（毫秒） | 語句計數 | 吞吐量（語句/秒） |
|---|---|---|---|---|---|---|---|
| 1 | 2 | 20% | 507 | 808 | 1315 | 80165 | 99214.11 |
| 1 | 2 | 50% | 第521章 | 1338 | 1859年 | 130849 | 97794.47 |
| 1 | 2 | 100% | 第534章 | 2114 | 2648 | 203502 | 96263.95 |
從表3可以看出，隨著資料量的增加（從20%到50%再到100%），讀取和寫入時間都會增加，導致操作的總時間更長。然而，這些數字並沒有給出任何有洞察力的意義，因為我們的進口量不同。如果我們看吞吐量的數量，吞吐量（語句/秒）隨著資料量的增加而逐漸下降，這表示隨著工作負載的增加，系統處理語句的效率降低。
Experiment 3: Data Import on Different Operating Systems在測試不同作業系統導入資料的過程中，我們採用了最快的導入方法，即方法二，導入量為100%。
請注意，透過虛擬機器在Linux上執行Java導入腳本時，必須考慮效能和資源分配上的不公平劣勢，因為虛擬化會引入開銷並影響系統的效率。
根據上述分析，很明顯，環境 1 (macOS) 顯示最佳效能，總時間最短為 2648 毫秒，吞吐量最高為 97,632.92 條語句/秒。相較之下，環境 2 (Windows) 最慢，總時間為 4117 毫秒，吞吐量最低為 60,669.02 條語句/秒。值得注意的是，Linux Ubuntu 雖然作為虛擬機器運行，但效能仍然優於裸機 Windows。
Experiment 4: Data Import with Various Programming Languages在這個實驗中，我們對 Java 程式碼使用了上面提到的方法 2，因為它是最快的。同時，對於Python，我們編寫了一個導入方法，利用Psycopg2與PostgreSQL資料庫伺服器進行通訊。
| 測試環境 | 程式設計語言 | 讀取時間（毫秒） | 寫入時間（毫秒） | 總時間（毫秒） | 吞吐量（語句/秒） |
|---|---|---|---|---|---|
| 1 | 爪哇 | 第534章 | 2114 | 2648 | 96263.95 |
| 1 | Python | 390 | 7237 | 7627 | 28119.66 |
表4的數據顯示，Java在速度和吞吐量方面均優於Python，顯示其讀寫操作的效率更高。
Experiment 5: Data Import on Different Databases我們為 PostgreSQL 和 MySQL 開發了三種不同的導入方法，但我們只對方法 2 進行了實驗，因為它是開發人員導入資料的選擇。 PostgreSQL 和 MySQL 都有相似的資料庫實作設計（DDL 面向）和相似的導入程式碼設計。
| 測試環境 | 方法 | 資料庫 | 讀取時間（毫秒） | 寫入時間（毫秒） | 總時間（毫秒） | 吞吐量（語句/秒） |
|---|---|---|---|---|---|---|
| 1 | 2 | PostgreSQL | 第534章 | 2114 | 2648 | 96263.95 |
| 1 | 2 | MySQL | 第534章 | 42315 | 42849 | 4809.22 |
儘管兩者都是基於 SQL 的資料庫，但 PostgreSQL 在寫入時間方面快了大約 16 倍。這可能是由於資料庫引擎架構和驅動程式實現的差異所造成的（ postgresql-42.2.5.jar ，MySQL 為mysql-connector-j-8.3.0.jar ）。
該專案的第 2 部分重點是透過建立公開一組應用程式介面 (API) 的後端程式庫來提供存取資料庫系統的基本功能。請注意，還需要匯入一個額外的資料集，因此需要更新的資料庫實作（可以在./Project2/DataImport中找到）。
[閱讀詳細要求]
./Project2/DataImport/src/main/sql/ddl.sql中提供的 DDL 設定 MySQL 資料庫./Project2/DataImport/src/main/java/ImportScript.java中提供的腳本導入數據./Project2/ShenzhenMetro ）（建議使用 IntelliJ IDEA IDE）
