Purpose

My solution for the following task, received during the database course at my university in 2023:

Design and write an application that performs the task:
"A company has a network of water stations on rivers and lakes, along which there are kayaking routes. Each station has a parking lot, a campsite, and some also offer indoor accommodation (cottages) and kayak rentals. Stations also provide transport for kayaks and people. Design a database with information about the stations and their equipment, as well as the trail network (approximate location of the stations, transport and sailing times). The customer should be able to book equipment and stay. In addition to individual customers, you can also reserve equipment and places for larger groups. Additionally, the database records the customer's departure on the trail to check whether they reach the planned destination on time."

Its focus was on designing the database and creating appropriate triggers for it.

Highlights

• Fully manually-written with PHP, pure CSS, PostgreSQL.
• A recursive SQL query for finding a path between two nodes in a graph (

  uwr-db-project/server/html/new_trip.php

  Lines 74 to 81 in f056a02

  	$query =
  	"WITH RECURSIVE Path(station1_id, station2_id) AS (
  	SELECT station1_id, station2_id, distance, kayaking_time, ARRAY[station1_id, station2_id] AS path FROM connection
  	UNION
  	SELECT c.station1_id, p.station2_id, c.distance + p.distance, c.kayaking_time + p.kayaking_time, array_cat(ARRAY[c.station1_id], p.path)
  	FROM connection c
  	JOIN Path p ON c.station2_id = p.station1_id
  	) SELECT * FROM Path WHERE path = ARRAY[$path]";

  )

Database diagram

Installation

Create the database:

createdb -U postgres db_project --owner=USERNAME

Import schema.sql:

psql -d db_project < schema.sql

Tables item, station, connection and equipment need to be filled manually.
Setting users as staff (higher privileges) also is done manually by setting is_staff to true and appropriately setting their at_station.

The following should be enough for the app to work:

cd server/html
php -S localhost:3000

Tested with:

• PHP 8.3.7 (cli) (built: May 7 2024 16:35:26) (NTS gcc x86_64)
• psql (PostgreSQL) 16.1

Screenshots presenting a few functionalities