A shipping company, built from
22 tables.
Design a database
for the high seas.
This was an assignment for the Databases and Data Management module of my MSc, and one I got a First for. The brief was to design and build a database for a fictional shipping company called Royal Anchor Shipping. I worked on it over a few months, alongside my other assignments.
The brief listed ten tables as a starting point. Offices, vessels, containers, bookings, crew and routes. But I wanted it to work the way a real shipping company would, so before designing anything I spent a lot of time researching how the industry actually runs. Routes, ports, customs, tariffs, even what crew members earn.
By the end, those ten tables had become 22.
Thinking in tables
before writing SQL.
I started with a simple list of everything the company would need to run. Then I mapped the relationships in Lucidchart one area at a time. Crew, vessels, routes, containers, then bookings and orders. Once each area made sense, I linked them all into one ER diagram.
One decision I'm proud of was splitting the crew into separate tables. Contact details, salary, employment, certification and job role each have their own table, so the main crew table stays clean instead of holding everything at once.
Every relationship came with an assumption. A vessel can have many schedules, but each schedule belongs to one vessel. A booking can have many orders, but every order belongs to one booking. Writing those out is what turned the diagram into rules the database could enforce.
Building it
for real.
I built it in Oracle SQL. Every table has primary and foreign keys, plus constraints like NOT NULL to keep bad data out.
To test it properly I needed realistic data, so I generated sample data with ChatGPT and loaded it in with SQL and CSV files. That part took far longer than I expected. The data had to respect every relationship in the design, so I kept going back to fix data types, character lengths and foreign keys until everything lined up.
CREATE TABLE vessel (
vessel_id VARCHAR2(4) PRIMARY KEY,
vessel_type_id VARCHAR2(4),
vessel_name VARCHAR2(50) NOT NULL,
IMO NUMBER(10) NOT NULL,
MMSI NUMBER(10) NOT NULL,
AIS_type_code NUMBER(2),
AIS_flag NUMBER(3) NOT NULL,
callsign NUMBER(5) NOT NULL,
gross_tonnage NUMBER(6,2) NOT NULL,
date_built DATE NOT NULL,
CONSTRAINT fk_vessel_type
FOREIGN KEY (vessel_type_id)
REFERENCES vessel_type(vessel_type_id)
);Asking it
real questions.
The final test was six queries that proved the database could answer the questions a shipping company would actually ask. Here are three of them.
SELECT
book.booking_id,
book.booking_date,
cust.first_name AS customer_name,
reci.first_name AS recipient_name,
ord.order_id,
ord.item_description,
ord.weight,
ord.volume,
ord.order_status
FROM bookings book
JOIN customers cust ON book.cust_id = cust.cust_id
JOIN recipients reci ON book.recipient_id = reci.recipient_id
JOIN orders ord ON book.booking_id = ord.booking_id
ORDER BY book.booking_date, book.booking_id, ord.order_id;SELECT
ord.order_id,
ord.item_description,
ord.weight,
book.booking_id,
vses.vessel_id,
po.port_name AS destination
FROM orders ord
JOIN bookings book ON ord.booking_id = book.booking_id
JOIN vessel_schedules vses ON book.ves_schedule_id = vses.ves_schedule_id
JOIN ports po ON vses.port_id = po.port_id
WHERE ord.weight > 1000;SELECT
ve.vessel_name,
vs.port_id,
po.port_name,
TO_CHAR(vs.arrival_date, 'DD-Mon-YYYY') AS eta,
TO_CHAR(vs.departure_date, 'DD-Mon-YYYY') AS etd
FROM vessel ve
JOIN vessel_schedules vs ON ve.vessel_id = vs.vessel_id
JOIN ports po ON vs.port_id = po.port_id
WHERE ve.vessel_name = 'RAC ULCV Spirit'
ORDER BY vs.port_order;Four things
about thinking in tables.
- 01.
Research is useful, but the design is the foundation. Looking back, I'd spend less time researching and more time on the ER diagram.
- 02.
More isn't always better. At one point my vessel table had almost 20 attributes. Cutting it down to what actually mattered made the whole design stronger.
- 03.
Relationships are the hard part. I redrew the ER diagram several times as I built the tables, because some things only made sense once real data hit them.
- 04.
Sample data is part of the design. Getting it to respect every relationship taught me more about my own database than the diagram did.