CASE STUDY 5 (MySQL)

CREATE DATABASE DBNAME;
USE DBNAME;

CREATE TABLE Manufacturers (
    manufacturer_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    state VARCHAR(50),
    representative_id INT,
    bidding_reference VARCHAR(50)
);

CREATE TABLE Ships (
    ship_number INT PRIMARY KEY,
    class VARCHAR(50),
    capacity INT,
    date_of_purchase DATE,
    manufacturer_id INT,
    ranking VARCHAR(50),
    FOREIGN KEY (manufacturer_id) REFERENCES Manufacturers(manufacturer_id)
);

CREATE TABLE Captains (
    captain_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    license_grade VARCHAR(50),
    date_of_birth DATE
);

CREATE TABLE Items (
    item_number INT PRIMARY KEY,
    type VARCHAR(50),
    description VARCHAR(255),
    weight DECIMAL(10, 2)
);

CREATE TABLE Shipments (
    shipment_id INT PRIMARY KEY,
    shipment_date DATE,
    location VARCHAR(50),
    ship_number INT,
    captain_id INT,
    FOREIGN KEY (ship_number) REFERENCES Ships(ship_number),
    FOREIGN KEY (captain_id) REFERENCES Captains(captain_id)
);

INSERT INTO Manufacturers (manufacturer_id, name, city, state, representative_id, bidding_reference)
VALUES
    (201, 'ACME Shipping Co.', 'New York', 'NY', 101, 'BID-001'),
    (202, 'Pacific Maritime', 'Los Angeles', 'CA', 102, 'BID-002'),
    (203, 'Global Cargo Solutions', 'London', 'England', 103, 'BID-003'),
    (204, 'Mediterranean Shipping', 'Athens', 'Greece', 104, 'BID-004'),
    (205, 'Atlantic Freight Services', 'Miami', 'FL', 105, 'BID-005'),
    (206, 'North Star Shipping', 'Oslo', 'Norway', 106, 'BID-006'),
    (207, 'Asia Pacific Logistics', 'Singapore', 'Singapore', 107, 'BID-007'),
    (208, 'Baltic Shipping Group', 'St. Petersburg', 'Russia', 108, 'BID-008'),
    (209, 'Caribbean Shipping Lines', 'Kingston', 'Jamaica', 109, 'BID-009'),
    (210, 'South America Cargo', 'Buenos Aires', 'Argentina', 110, 'BID-010');

INSERT INTO Ships (ship_number, class, capacity, date_of_purchase, manufacturer_id, ranking)
VALUES
    (5501, 'Container Ship', 5000, '2020-05-15', 201, 'Rank 1'),
    (5502, 'Bulk Carrier', 8000, '2019-10-20', 202, 'Rank 2'),
    (5503, 'Ro-Ro Vessel', 3000, '2021-02-28', 203, 'Rank 3'),
    (5504, 'LNG Tanker', 7000, '2022-08-10', 205, 'Rank 2'),
    (5505, 'Chemical Tanker', 4000, '2023-01-05', 206, 'Rank 1'),
    (5506, 'Oil Tanker', 9000, '2020-07-19', 208, 'Rank 3'),
    (5507, 'Cruise Ship', 2000, '2021-11-30', 209, 'Rank 2'),
    (5508, 'Ferry', 1500, '2019-03-14', 210, 'Rank 1'),
    (5509, 'Tugboat', 500, '2022-06-25', 204, 'Rank 3'),
    (5510, 'Research Vessel', 1000, '2023-05-08', 207, 'Rank 2');

INSERT INTO Captains (captain_id, first_name, last_name, license_grade, date_of_birth)
VALUES
    (1, 'John', 'Smith', 'Grade 1', '1985-07-12'),
    (2, 'Emily', 'Johnson', 'Grade 2', '1990-02-25'),
    (3, 'Michael', 'Williams', 'Grade 3', '1988-09-18'),
    (4, 'Emma', 'Brown', 'Grade 1', '1992-05-03'),
    (5, 'Daniel', 'Jones', 'Grade 2', '1987-11-09'),
    (6, 'Olivia', 'Taylor', 'Grade 3', '1995-03-21'),
    (7, 'Matthew', 'Davis', 'Grade 1', '1989-02-12'),
    (8, 'Sophia', 'Miller', 'Grade 2', '1994-08-27'),
    (9, 'James', 'Wilson', 'Grade 3', '1991-11-02'),
    (10, 'Ava', 'Anderson', 'Grade 1', '1986-06-15');

INSERT INTO Items (item_number, type, description, weight)
VALUES
    (1, 'Electronics', 'Smartphone', 0.2),
    (2, 'Clothing', 'T-Shirt', 0.3),
    (3, 'Furniture', 'Chair', 5),
    (4, 'Books', 'Novel', 0.5),
    (5, 'Food', 'Canned Soup', 0.4),
    (6, 'Appliances', 'Microwave', 10),
    (7, 'Automotive', 'Car Battery', 15),
    (8, 'Sports', 'Tennis Racket', 0.8),
    (9, 'Tools', 'Hammer', 1.2),
    (10, 'Beauty', 'Perfume', 0.1);

INSERT INTO Shipments (shipment_id, shipment_date, location, ship_number, captain_id)
VALUES
    (3201, '2022-01-15', 'New York', 5501, 1),
    (3202, '2022-03-02', 'Florida', 5502, 2),
    (3203, '2022-05-10', 'Beijing', 5503, 3),
    (3204, '2022-07-20', 'Florida', 5504, 4),
    (3205, '2022-09-05', 'New York', 5505, 5),
    (3206, '2022-11-15', 'Florida', 5506, 6),
    (3207, '2023-01-20', 'Beijing', 5507, 7),
    (3208, '2023-03-28', 'Florida', 5508, 8),
    (3209, '2023-06-05', 'New York', 5509, 9),
    (3210, '2023-08-10', 'Florida', 5510, 10);