Royal Anchor Shipping Database System

OVERVIEW

This project was developed as part of a database design assignment, where I created a fully functional SQL-based database system for a fictional shipping company, Royal Anchor Shipping (RAC). The goal was to design, implement, and test a relational model capable of managing vessels, routes, customers, containers, crew, and bookings.

An image of a smartphone on top of an eletronic surface

YEAR

2025

ROLE

Database Designer
Data Engineer

SERVICES

Relational Database Design
SQL Implementation

CREATE TABLE container_type ( container_type_id VARCHAR2(5) PRIMARY KEY, cont_name VARCHAR2(30) NOT NULL, cont_type VARCHAR2(20) NOT NULL, length FLOAT NOT NULL, width FLOAT NOT NULL, height FLOAT NOT NULL, capacity NUMBER(6) NOT NULL, TEU NUMBER(1) NOT NULL, tare NUMBER(5), max_payload NUMBER(6) NOT NULL ), quantity NUMBER(4);
INSERT ALL INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT03', 'Standard 20ft Dry', 'Dry', 6.06, 2.44, 2.59, 33, 1, 2300, 28200, 500) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT04', 'Standard 40ft Dry', 'Dry', 12.19, 2.44, 2.59, 67, 2, 3800, 26480, 400) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT05', 'High Cube 40ft Dry', 'Dry', 12.19, 2.44, 2.89, 76, 2, 4000, 26500, 300) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT06', 'Reefer 20ft', 'Reefer', 6.06, 2.44, 2.59, 28, 1, 3050, 27000, 150) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT07', 'Reefer 40ft', 'Reefer', 12.19, 2.44, 2.59, 58, 2, 4500, 25500, 120) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT08', 'Flat Rack 20ft', 'Flat Rack', 6.06, 2.44, 2.13, 30, 1, 2500, 31000, 100) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT09', 'Flat Rack 40ft', 'Flat Rack', 12.19, 2.44, 2.13, 62, 2, 5200, 40000, 90) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT10', 'Open Top 20ft', 'Open Top', 6.06, 2.44, 2.59, 32, 1, 2300, 28000, 80) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT11', 'Open Top 40ft', 'Open Top', 12.19, 2.44, 2.59, 66, 2, 3900, 26500, 70) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT12', 'Tank 20ft', 'Tank', 6.06, 2.44, 2.59, 25, 1, 3200, 26000, 60) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT13', 'Ventilated 20ft', 'Ventilated', 6.06, 2.44, 2.59, 33, 1, 2300, 28000, 50) SELECT * FROM
CREATE TABLE container_type ( container_type_id VARCHAR2(5) PRIMARY KEY, cont_name VARCHAR2(30) NOT NULL, cont_type VARCHAR2(20) NOT NULL, length FLOAT NOT NULL, width FLOAT NOT NULL, height FLOAT NOT NULL, capacity NUMBER(6) NOT NULL, TEU NUMBER(1) NOT NULL, tare NUMBER(5), max_payload NUMBER(6) NOT NULL ), quantity NUMBER(4);
INSERT ALL INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT03', 'Standard 20ft Dry', 'Dry', 6.06, 2.44, 2.59, 33, 1, 2300, 28200, 500) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT04', 'Standard 40ft Dry', 'Dry', 12.19, 2.44, 2.59, 67, 2, 3800, 26480, 400) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT05', 'High Cube 40ft Dry', 'Dry', 12.19, 2.44, 2.89, 76, 2, 4000, 26500, 300) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT06', 'Reefer 20ft', 'Reefer', 6.06, 2.44, 2.59, 28, 1, 3050, 27000, 150) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT07', 'Reefer 40ft', 'Reefer', 12.19, 2.44, 2.59, 58, 2, 4500, 25500, 120) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT08', 'Flat Rack 20ft', 'Flat Rack', 6.06, 2.44, 2.13, 30, 1, 2500, 31000, 100) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT09', 'Flat Rack 40ft', 'Flat Rack', 12.19, 2.44, 2.13, 62, 2, 5200, 40000, 90) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT10', 'Open Top 20ft', 'Open Top', 6.06, 2.44, 2.59, 32, 1, 2300, 28000, 80) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT11', 'Open Top 40ft', 'Open Top', 12.19, 2.44, 2.59, 66, 2, 3900, 26500, 70) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT12', 'Tank 20ft', 'Tank', 6.06, 2.44, 2.59, 25, 1, 3200, 26000, 60) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT13', 'Ventilated 20ft', 'Ventilated', 6.06, 2.44, 2.59, 33, 1, 2300, 28000, 50) SELECT * FROM
CREATE TABLE container_type ( container_type_id VARCHAR2(5) PRIMARY KEY, cont_name VARCHAR2(30) NOT NULL, cont_type VARCHAR2(20) NOT NULL, length FLOAT NOT NULL, width FLOAT NOT NULL, height FLOAT NOT NULL, capacity NUMBER(6) NOT NULL, TEU NUMBER(1) NOT NULL, tare NUMBER(5), max_payload NUMBER(6) NOT NULL ), quantity NUMBER(4);
INSERT ALL INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT03', 'Standard 20ft Dry', 'Dry', 6.06, 2.44, 2.59, 33, 1, 2300, 28200, 500) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT04', 'Standard 40ft Dry', 'Dry', 12.19, 2.44, 2.59, 67, 2, 3800, 26480, 400) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT05', 'High Cube 40ft Dry', 'Dry', 12.19, 2.44, 2.89, 76, 2, 4000, 26500, 300) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT06', 'Reefer 20ft', 'Reefer', 6.06, 2.44, 2.59, 28, 1, 3050, 27000, 150) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT07', 'Reefer 40ft', 'Reefer', 12.19, 2.44, 2.59, 58, 2, 4500, 25500, 120) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT08', 'Flat Rack 20ft', 'Flat Rack', 6.06, 2.44, 2.13, 30, 1, 2500, 31000, 100) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT09', 'Flat Rack 40ft', 'Flat Rack', 12.19, 2.44, 2.13, 62, 2, 5200, 40000, 90) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT10', 'Open Top 20ft', 'Open Top', 6.06, 2.44, 2.59, 32, 1, 2300, 28000, 80) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT11', 'Open Top 40ft', 'Open Top', 12.19, 2.44, 2.59, 66, 2, 3900, 26500, 70) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT12', 'Tank 20ft', 'Tank', 6.06, 2.44, 2.59, 25, 1, 3200, 26000, 60) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT13', 'Ventilated 20ft', 'Ventilated', 6.06, 2.44, 2.59, 33, 1, 2300, 28000, 50) SELECT * FROM
CREATE TABLE container_type ( container_type_id VARCHAR2(5) PRIMARY KEY, cont_name VARCHAR2(30) NOT NULL, cont_type VARCHAR2(20) NOT NULL, length FLOAT NOT NULL, width FLOAT NOT NULL, height FLOAT NOT NULL, capacity NUMBER(6) NOT NULL, TEU NUMBER(1) NOT NULL, tare NUMBER(5), max_payload NUMBER(6) NOT NULL ), quantity NUMBER(4);
INSERT ALL INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT03', 'Standard 20ft Dry', 'Dry', 6.06, 2.44, 2.59, 33, 1, 2300, 28200, 500) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT04', 'Standard 40ft Dry', 'Dry', 12.19, 2.44, 2.59, 67, 2, 3800, 26480, 400) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT05', 'High Cube 40ft Dry', 'Dry', 12.19, 2.44, 2.89, 76, 2, 4000, 26500, 300) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT06', 'Reefer 20ft', 'Reefer', 6.06, 2.44, 2.59, 28, 1, 3050, 27000, 150) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT07', 'Reefer 40ft', 'Reefer', 12.19, 2.44, 2.59, 58, 2, 4500, 25500, 120) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT08', 'Flat Rack 20ft', 'Flat Rack', 6.06, 2.44, 2.13, 30, 1, 2500, 31000, 100) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT09', 'Flat Rack 40ft', 'Flat Rack', 12.19, 2.44, 2.13, 62, 2, 5200, 40000, 90) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT10', 'Open Top 20ft', 'Open Top', 6.06, 2.44, 2.59, 32, 1, 2300, 28000, 80) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT11', 'Open Top 40ft', 'Open Top', 12.19, 2.44, 2.59, 66, 2, 3900, 26500, 70) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT12', 'Tank 20ft', 'Tank', 6.06, 2.44, 2.59, 25, 1, 3200, 26000, 60) INTO container_type (container_type_id, cont_name, cont_type, length, width, height, capacity, TEU, tare, max_payload, quantity) VALUES ('CT13', 'Ventilated 20ft', 'Ventilated', 6.06, 2.44, 2.59, 33, 1, 2300, 28000, 50) SELECT * FROM

CREATE TABLE crew_jobs ( job_id VARCHAR2(4) PRIMARY KEY, job_title VARCHAR2(24) NOT NULL, rankings VARCHAR2(30) NOT NULL, department VARCHAR2(14) NOT NULL, job_description VARCHAR2(30) NOT NULL, certified VARCHAR2(1) DEFAULT 'N')
INSERT ALL INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB10', 'Captain', 'Officer', 'Deck', 'Master of the vessel', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB11', 'Chief Mate', 'Officer', 'Deck', 'Head of deck operations', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB12', 'Second Mate', 'Junior Officer', 'Deck', 'Navigation duties', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB13', 'Third Mate', 'Junior Officer', 'Deck', 'Safety equipment manager', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB14', 'Boatswain', 'Deck Crew', 'Deck', 'Lead deckhand operations', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB15', 'Able Seaman', 'Deck Crew', 'Deck', 'Experienced deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB16', 'Ordinary Seaman', 'Deck Crew', 'Deck', 'Junior deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB17', 'Chief Engineer', 'Officer', 'Engine', 'Head of engine dept.', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB18', 'Second Engineer', 'Engineer', 'Engine', 'Assist Chief Engineer', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB19', 'Third Engineer', 'Engineer', 'Engine', 'Auxiliary engine ops', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB20', 'Fourth Engineer', 'Engineer', 'Engine', 'Engine room support', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB21', 'Motorman', 'Technician', 'Engine', 'Assist engine officers', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB22', 'Oiler', 'Technician', 'Engine', 'Lubricates engine parts', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB23', 'Wiper', 'Technician', 'Engine', 'Engine room cleaning', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB24', 'Electrician', 'Technician', 'Engine', 'Maintains ship electrics', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB25', 'Chief Cook', 'Catering Staff', 'Catering', 'Manages kitchen ops', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB26', 'Cook', 'Catering Staff', 'Catering', 'Prepares daily meals', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB27', 'Baker', 'Catering Staff', 'Catering', 'Bakes food items', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB28', 'Steward', 'Catering Staff', 'Catering', 'Cabin service duties', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB29', 'Messman', 'Catering Staff', 'Catering', 'Assists in mess hall', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB30', 'Medic', 'Medical Officer', 'Medical', 'Provides medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB31', 'Nurse', 'Medical Officer', 'Medical', 'Assists medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB32', 'Radio Officer', 'Communication Officer', 'Communication', 'Handles communications', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB33', 'IT Officer', 'Communication Officer', 'Communication', 'Manages IT systems', 'N') SELECT * FROM
CREATE TABLE crew_jobs ( job_id VARCHAR2(4) PRIMARY KEY, job_title VARCHAR2(24) NOT NULL, rankings VARCHAR2(30) NOT NULL, department VARCHAR2(14) NOT NULL, job_description VARCHAR2(30) NOT NULL, certified VARCHAR2(1) DEFAULT 'N')
INSERT ALL INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB10', 'Captain', 'Officer', 'Deck', 'Master of the vessel', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB11', 'Chief Mate', 'Officer', 'Deck', 'Head of deck operations', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB12', 'Second Mate', 'Junior Officer', 'Deck', 'Navigation duties', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB13', 'Third Mate', 'Junior Officer', 'Deck', 'Safety equipment manager', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB14', 'Boatswain', 'Deck Crew', 'Deck', 'Lead deckhand operations', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB15', 'Able Seaman', 'Deck Crew', 'Deck', 'Experienced deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB16', 'Ordinary Seaman', 'Deck Crew', 'Deck', 'Junior deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB17', 'Chief Engineer', 'Officer', 'Engine', 'Head of engine dept.', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB18', 'Second Engineer', 'Engineer', 'Engine', 'Assist Chief Engineer', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB19', 'Third Engineer', 'Engineer', 'Engine', 'Auxiliary engine ops', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB20', 'Fourth Engineer', 'Engineer', 'Engine', 'Engine room support', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB21', 'Motorman', 'Technician', 'Engine', 'Assist engine officers', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB22', 'Oiler', 'Technician', 'Engine', 'Lubricates engine parts', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB23', 'Wiper', 'Technician', 'Engine', 'Engine room cleaning', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB24', 'Electrician', 'Technician', 'Engine', 'Maintains ship electrics', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB25', 'Chief Cook', 'Catering Staff', 'Catering', 'Manages kitchen ops', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB26', 'Cook', 'Catering Staff', 'Catering', 'Prepares daily meals', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB27', 'Baker', 'Catering Staff', 'Catering', 'Bakes food items', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB28', 'Steward', 'Catering Staff', 'Catering', 'Cabin service duties', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB29', 'Messman', 'Catering Staff', 'Catering', 'Assists in mess hall', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB30', 'Medic', 'Medical Officer', 'Medical', 'Provides medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB31', 'Nurse', 'Medical Officer', 'Medical', 'Assists medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB32', 'Radio Officer', 'Communication Officer', 'Communication', 'Handles communications', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB33', 'IT Officer', 'Communication Officer', 'Communication', 'Manages IT systems', 'N') SELECT * FROM
CREATE TABLE crew_jobs ( job_id VARCHAR2(4) PRIMARY KEY, job_title VARCHAR2(24) NOT NULL, rankings VARCHAR2(30) NOT NULL, department VARCHAR2(14) NOT NULL, job_description VARCHAR2(30) NOT NULL, certified VARCHAR2(1) DEFAULT 'N')
INSERT ALL INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB10', 'Captain', 'Officer', 'Deck', 'Master of the vessel', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB11', 'Chief Mate', 'Officer', 'Deck', 'Head of deck operations', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB12', 'Second Mate', 'Junior Officer', 'Deck', 'Navigation duties', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB13', 'Third Mate', 'Junior Officer', 'Deck', 'Safety equipment manager', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB14', 'Boatswain', 'Deck Crew', 'Deck', 'Lead deckhand operations', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB15', 'Able Seaman', 'Deck Crew', 'Deck', 'Experienced deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB16', 'Ordinary Seaman', 'Deck Crew', 'Deck', 'Junior deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB17', 'Chief Engineer', 'Officer', 'Engine', 'Head of engine dept.', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB18', 'Second Engineer', 'Engineer', 'Engine', 'Assist Chief Engineer', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB19', 'Third Engineer', 'Engineer', 'Engine', 'Auxiliary engine ops', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB20', 'Fourth Engineer', 'Engineer', 'Engine', 'Engine room support', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB21', 'Motorman', 'Technician', 'Engine', 'Assist engine officers', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB22', 'Oiler', 'Technician', 'Engine', 'Lubricates engine parts', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB23', 'Wiper', 'Technician', 'Engine', 'Engine room cleaning', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB24', 'Electrician', 'Technician', 'Engine', 'Maintains ship electrics', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB25', 'Chief Cook', 'Catering Staff', 'Catering', 'Manages kitchen ops', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB26', 'Cook', 'Catering Staff', 'Catering', 'Prepares daily meals', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB27', 'Baker', 'Catering Staff', 'Catering', 'Bakes food items', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB28', 'Steward', 'Catering Staff', 'Catering', 'Cabin service duties', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB29', 'Messman', 'Catering Staff', 'Catering', 'Assists in mess hall', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB30', 'Medic', 'Medical Officer', 'Medical', 'Provides medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB31', 'Nurse', 'Medical Officer', 'Medical', 'Assists medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB32', 'Radio Officer', 'Communication Officer', 'Communication', 'Handles communications', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB33', 'IT Officer', 'Communication Officer', 'Communication', 'Manages IT systems', 'N') SELECT * FROM
CREATE TABLE crew_jobs ( job_id VARCHAR2(4) PRIMARY KEY, job_title VARCHAR2(24) NOT NULL, rankings VARCHAR2(30) NOT NULL, department VARCHAR2(14) NOT NULL, job_description VARCHAR2(30) NOT NULL, certified VARCHAR2(1) DEFAULT 'N')
INSERT ALL INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB10', 'Captain', 'Officer', 'Deck', 'Master of the vessel', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB11', 'Chief Mate', 'Officer', 'Deck', 'Head of deck operations', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB12', 'Second Mate', 'Junior Officer', 'Deck', 'Navigation duties', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB13', 'Third Mate', 'Junior Officer', 'Deck', 'Safety equipment manager', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB14', 'Boatswain', 'Deck Crew', 'Deck', 'Lead deckhand operations', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB15', 'Able Seaman', 'Deck Crew', 'Deck', 'Experienced deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB16', 'Ordinary Seaman', 'Deck Crew', 'Deck', 'Junior deckhand', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB17', 'Chief Engineer', 'Officer', 'Engine', 'Head of engine dept.', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB18', 'Second Engineer', 'Engineer', 'Engine', 'Assist Chief Engineer', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB19', 'Third Engineer', 'Engineer', 'Engine', 'Auxiliary engine ops', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB20', 'Fourth Engineer', 'Engineer', 'Engine', 'Engine room support', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB21', 'Motorman', 'Technician', 'Engine', 'Assist engine officers', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB22', 'Oiler', 'Technician', 'Engine', 'Lubricates engine parts', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB23', 'Wiper', 'Technician', 'Engine', 'Engine room cleaning', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB24', 'Electrician', 'Technician', 'Engine', 'Maintains ship electrics', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB25', 'Chief Cook', 'Catering Staff', 'Catering', 'Manages kitchen ops', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB26', 'Cook', 'Catering Staff', 'Catering', 'Prepares daily meals', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB27', 'Baker', 'Catering Staff', 'Catering', 'Bakes food items', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB28', 'Steward', 'Catering Staff', 'Catering', 'Cabin service duties', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB29', 'Messman', 'Catering Staff', 'Catering', 'Assists in mess hall', 'N') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB30', 'Medic', 'Medical Officer', 'Medical', 'Provides medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB31', 'Nurse', 'Medical Officer', 'Medical', 'Assists medical care', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB32', 'Radio Officer', 'Communication Officer', 'Communication', 'Handles communications', 'Y') INTO crew_jobs (job_id, job_title, rankings, department, job_description, certified) VALUES ('JB33', 'IT Officer', 'Communication Officer', 'Communication', 'Manages IT systems', 'N') SELECT * FROM

About the project

To develop a realistic and efficient database system that mirrors the operations of a global shipping company.

Capture complex relationships between vessels, routes, customers, and crew.
Ensure data accuracy and integrity across multiple entities.
Support business processes like cargo tracking, route scheduling, and customs clearance.
Minimise redundancy through normalisation and well-structured foreign key constraints.
Provide a foundation for future analytics and performance reporting.

Smooth Scroll
This will hide itself!