CS307 Spring 2026 Database Project

This repository contains the implementation and report materials for the CS307 Spring 2026 database project. The project starts from five raw CSV files and builds a normalized flight-booking database, data import pipelines, validation SQL, benchmark experiments, and a CRUD-oriented booking application.

本仓库为 CS307 Spring 2026 数据库项目实现，内容包括关系数据库设计、PostgreSQL/MySQL 数据导入、数据正确性检查、导入性能测试，以及一个支持订票业务流程的 Java Web/CLI 应用。

Project Scope

The project follows the four tasks in CS307-spring26-project1.pdf:

Task	Requirement	Repository implementation
Task 1	E-R diagram and conceptual design	Final report explains the entity model and normalization decisions.
Task 2	Relational schema design	Schema.sql defines the PostgreSQL schema with PK/FK, unique constraints, checks, and no circular FK dependency.
Task 3	CSV data import and validation	import/ imports data into PostgreSQL; CheckScript/ contains validation SQL; import_mysql/ provides a MySQL-compatible import version; benchmark/ compares ticket import strategies.
Task 4	CRUD operations through code	task4/ implements ticket generation, ticket search, booking, passenger contacts, order search, and order deletion through Java services, DAO classes, CLI, and web UI.

Main Features

• Normalized schema for region, airline, airport, users, passenger, flight, ticket, and ticket_order.
• Composite flight business key based on flight number, airline, route, times, and overnight-arrival flag.
• Ticket inventory separated from flight schedules by date and cabin class.
• PostgreSQL import pipeline for region, airline, airport, passenger, flight, and ticket data.
• MySQL import pipeline for cross-database compatibility experiments.
• Validation SQL scripts for row counts and project query checks.
• Benchmark code comparing row-by-row insert, JDBC batch insert, and PostgreSQL COPY.
• Java Task 4 application with layered web -> service -> dao -> model -> db organization.
• Web pages for login/registration, ticket search/booking, passenger contacts, and order management.
• CLI workflow for registration, login, ticket generation, ticket search, booking, passenger contact management, order lookup, order deletion, and logout.

Repository Layout

.
|-- CS307-spring26-project1.pdf      # Original project requirement PDF
|-- database_project.pdf             # Final project report
|-- Schema.sql                       # PostgreSQL DDL
|-- CheckScript/                     # Import checks and Task 3 validation SQL
|-- import/                          # PostgreSQL CSV import Java project
|-- import_mysql/                    # MySQL DDL and import Java project
|-- benchmark/                       # Ticket import benchmark programs
|-- task4/                           # CRUD booking system: backend, CLI, and web UI
|-- report_tex_sources/              # LaTeX report sources and report figures
`-- retro-pixel-template/            # Visual style reference used by the web UI

Database Design Summary

The PostgreSQL schema is defined in Schema.sql.

Core design choices:

• region, airline, and airport store stable reference data.
• flight stores route and schedule information.
• ticket stores per-flight, per-date, per-class price and seat availability.
• users represents application accounts.
• passenger stores travelers/contacts owned by users.
• ticket_order records bookings with order_time DEFAULT NOW().
• flight uses a composite business key:

unique (
  flight_number,
  airline_id,
  departure_airport_id,
  arrival_airport_id,
  departure_time,
  arrival_time,
  arrival_next_day
)

The foreign-key dependency order is acyclic:

region -> airline / airport -> flight -> ticket -> ticket_order
users -> passenger -> ticket_order

Prerequisites

• Java 17+
• Maven 3.8+
• PostgreSQL 16+ for the main implementation
• MySQL 8.x only if running the cross-database import experiment
• DataGrip or another SQL client for executing DDL and validation queries

Setup: PostgreSQL Schema

1. Create a PostgreSQL database, for example project1.
2. Create or configure a database user, for example db_project.
3. Execute the DDL:

psql -h 127.0.0.1 -U db_project -d project1 -f Schema.sql

You can also run Schema.sql directly in DataGrip.

Data Import: PostgreSQL

The PostgreSQL import code is under import/.

Before running, edit import/src/DBConnection.java to match your local PostgreSQL instance.

cd import
mvn package -q
java -cp target/importer-1.0-jar-with-dependencies.jar Main

The pipeline runs the import steps in foreign-key order:

1. ImportRegion
2. ImportAirline
3. ImportAirport
4. ImportPassenger
5. ImportFlightTicket
6. Optional ImportTicketOrder if the class exists

The CSV files are expected in import/data/.

Data Import: MySQL Experiment

The MySQL-compatible implementation is under import_mysql/.

1. Execute import_mysql/import_mysql/import_mysql.sql in MySQL.
2. Edit import_mysql/src/DBConnection.java for your MySQL database.
3. Build and run:

cd import_mysql
mvn package -q
java -cp target/importer-1.0-jar-with-dependencies.jar Main

This version mirrors the PostgreSQL pipeline while using MySQL-compatible conflict handling and lookup logic.

Validation SQL

Validation scripts are stored in CheckScript/.

• check_import1.sql and check_import2.sql check import row counts and duplicate constraints.
• task3_validation1.sql and task3_validation2.sql contain SQL queries used for Task 3 data accuracy checking.

Run them in DataGrip or psql after the import pipeline finishes.

Benchmark

The benchmark/ project compares three ticket import strategies:

• Benchmark1RowByRow.java: row-by-row JDBC insert
• Benchmark2Batch.java: JDBC batch insert
• Benchmark3Copy.java: PostgreSQL COPY
• BenchmarkRunner.java: benchmark entry point

Build with:

cd benchmark
mvn package -q

Then run the benchmark entry point from the generated jar or from an IDE after configuring the database connection in the benchmark source files.

Task 4 Booking Application

The application in task4/ provides both a web UI and a CLI entry point. It uses Java 17, Spark Java, Gson, PostgreSQL JDBC, and a simple layered architecture.

Key modules:

task4/src/web/BookingWebServer.java  # HTTP routes and static page serving
task4/src/service/                   # Business validation and workflow logic
task4/src/dao/                       # SQL queries and transactions
task4/src/model/                     # Request/response/domain models
task4/src/db/ConnectionPool.java     # PostgreSQL connection helper
task4/src/cli/Main.java              # Prompt-based CLI workflow
task4/web/                           # home, booking, and order pages

Run the Web Application

On Linux/macOS/Git Bash:

cd task4
DB_URL='jdbc:postgresql://127.0.0.1:5432/project1' \
DB_USER='db_project' \
DB_PASSWORD='postgres' \
PORT=1234 \
./start_booking_system.sh

Open:

http://127.0.0.1:1234

Important routes:

Route	Purpose
/	Login and registration page
/booking	Ticket search, passenger contacts, and booking page
/orders	Order management page
GET /api/health	Health check
GET /api/tickets/search	Search tickets
POST /api/tickets/generate	Generate ticket inventory for a date range
POST /api/book	Book a ticket
GET /api/users/:userId/passengers	List passenger contacts
POST /api/users/:userId/passengers	Add passenger contact
POST /api/users/:userId/passengers/:passengerId/delete	Delete passenger contact
GET /api/users/:userId/orders	List user orders
POST /api/users/:userId/orders/:orderId/delete	Delete an order and restore one seat

Run the CLI

The Task 4 jar manifest points to cli.Main, so the packaged jar starts the CLI by default:

cd task4
mvn package -q
java -cp target/ticket-system-1.0-jar-with-dependencies.jar cli.Main

CLI functions include:

• Register
• Login
• Generate tickets by date range
• Search tickets
• Book ticket
• Manage passenger contacts
• Search ticket orders, including an Order Time column
• Delete ticket order
• Logout

Report

The final report is database_project.pdf.

The report covers:

• Dataset analysis and E-R design
• PostgreSQL schema and normalization
• Import scripts, dirty-data handling, and validation SQL
• Windows/Linux portability and MySQL compatibility
• Different data volume experiments
• Import efficiency benchmark
• Task 4 CRUD booking system and UI screenshots

LaTeX sources and figures are stored in report_tex_sources/.

中文说明

本项目以航班订票数据为背景，从 region.csv、airline.csv、airport.csv、passenger.csv 和 tickets.csv 五个原始 CSV 文件出发，完成数据库课程项目要求的四个部分：

1. 设计 E-R 图并说明实体关系。
2. 设计满足 3NF、主外键约束和无循环外键依赖的 PostgreSQL 表结构。
3. 编写 Java 导入程序，将 CSV 数据清洗并导入数据库，同时提供 SQL 检查脚本和性能实验。
4. 编写 Java 程序访问数据库，实现机票生成、查询、订票、订单查询、订单删除、联系人管理等 CRUD 相关功能。

主要运行顺序建议如下：

1. 在 PostgreSQL 中执行 Schema.sql。
2. 配置 import/src/DBConnection.java。
3. 运行 import/ 中的 Main 导入 CSV 数据。
4. 运行 CheckScript/ 中的 SQL 检查导入结果。
5. 配置并启动 task4/，通过 Web 或 CLI 测试订票系统。

Notes

• Database connection defaults in source files are environment-specific. Update them before running on another machine.
• Do not commit real production passwords. Use local course/demo credentials or environment variables when possible.
• database_project.pdf is the submitted-style report; CS307-spring26-project1.pdf is the original requirement document.