Berisi DDL untuk membangun database perjalanan kereta di P.T. KAI sebagai studi kasus.

tugas-mandat-pertemuan-4.sql

CREATE DATABASE kereta_api_indonesia;

use kereta_api_indonesia;

CREATE TABLE country (
    id_country INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

INSERT INTO country (name) VALUES ('Indonesia');
INSERT INTO country (name) VALUES ('Malaysia');
INSERT INTO country (name) VALUES ('Singapore');
INSERT INTO country (name) VALUES ('Thailand');
INSERT INTO country (name) VALUES ('Vietnam');

CREATE TABLE province (
    id_province INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES country (id_country) ON DELETE SET NULL
);

INSERT INTO province (name, country_id) VALUES ('DKI Jakarta', 1);
INSERT INTO province (name, country_id) VALUES ('Jawa Barat', 1);
INSERT INTO province (name, country_id) VALUES ('Jawa Tengah', 1);
INSERT INTO province (name, country_id) VALUES ('DI Yogyakarta', 1);
INSERT INTO province (name, country_id) VALUES ('Jawa Timur', 1);

CREATE TABLE city (
    id_city INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    province_id INT,
    FOREIGN KEY (province_id) REFERENCES province (id_province) ON DELETE SET NULL
);

INSERT INTO city (name, province_id) VALUES ('Jakarta Selatan', 1);
INSERT INTO city (name, province_id) VALUES ('Jakarta Pusat', 1);
INSERT INTO city (name, province_id) VALUES ('Bekasi', 2);
INSERT INTO city (name, province_id) VALUES ('Purwakarta', 2);
INSERT INTO city (name, province_id) VALUES ('Bandung', 2);

CREATE TABLE station (
    id_station INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES city (id_city) ON DELETE SET NULL
);

INSERT INTO station (name, city_id) VALUES ('Gambir', 2);
INSERT INTO station (name, city_id) VALUES ('Manggarai', 1);
INSERT INTO station (name, city_id) VALUES ('Jatinegara', 3);
INSERT INTO station (name, city_id) VALUES ('Purwakarta', 4);
INSERT INTO station (name, city_id) VALUES ('Bandung', 5);

CREATE TABLE locomotive_type (
    id_locomotive_type INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL
);

INSERT INTO locomotive_type (name) VALUES ('LOC-A1');
INSERT INTO locomotive_type (name) VALUES ('LOC-A2');
INSERT INTO locomotive_type (name) VALUES ('LOC-A3');
INSERT INTO locomotive_type (name) VALUES ('LOC-B1');
INSERT INTO locomotive_type (name) VALUES ('LOC-C1');

CREATE TABLE employee_rank (
    id_employee_rank INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL
);

INSERT INTO employee_rank (name) VALUES ('Gol. I');
INSERT INTO employee_rank (name) VALUES ('Gol. II');
INSERT INTO employee_rank (name) VALUES ('Gol. III');
INSERT INTO employee_rank (name) VALUES ('Gol. IV');
INSERT INTO employee_rank (name) VALUES ('Gol. V');

CREATE TABLE payment_method (
    id_payment_method INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL
);

INSERT INTO payment_method (name) VALUES ('transfer');
INSERT INTO payment_method (name) VALUES ('go_show');
INSERT INTO payment_method (name) VALUES ('Kredit BCA');
INSERT INTO payment_method (name) VALUES ('Indomaret');
INSERT INTO payment_method (name) VALUES ('Kredit Mandiri');

CREATE TABLE wagon_facility (
    id_wagon_facility INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL
);

INSERT INTO wagon_facility (name) VALUES ('AC');
INSERT INTO wagon_facility (name) VALUES ('TV LED');
INSERT INTO wagon_facility (name) VALUES ('Free Snack');
INSERT INTO wagon_facility (name) VALUES ('Free Lunch');
INSERT INTO wagon_facility (name) VALUES ('Blanket');

CREATE TABLE service_type (
    id_service_type INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL
);

INSERT INTO service_type (name) VALUES ('Ekonomi A');
INSERT INTO service_type (name) VALUES ('Ekonomi B');
INSERT INTO service_type (name) VALUES ('Bisnis');
INSERT INTO service_type (name) VALUES ('Eksekutif A');
INSERT INTO service_type (name) VALUES ('Eksekutif B');

CREATE TABLE employee (
    id_employee INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    nip VARCHAR(30) NOT NULL,
    address TEXT,
    employee_rank_id INT,
    UNIQUE(nip),
    FOREIGN KEY (employee_rank_id) REFERENCES employee_rank (id_employee_rank) ON DELETE SET NULL
);

INSERT INTO employee (name, nip, address, employee_rank_id) VALUES ('Yono Mulyadi', '75118888', '', 3);
INSERT INTO employee (name, nip, address, employee_rank_id) VALUES ('Syifa Nurjannah', '75119988', '', 3);
INSERT INTO employee (name, nip, address, employee_rank_id) VALUES ('Karto Muwarid', '70111233', '', 3);
INSERT INTO employee (name, nip, address, employee_rank_id) VALUES ('Michael Atambua', '70012245', '', 3);
INSERT INTO employee (name, nip, address, employee_rank_id) VALUES ('Jackson Perangin Angin', '70013354', '', 3);

CREATE TABLE machinist (
    id_machinist INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    UNIQUE(employee_id),
    FOREIGN KEY (employee_id) REFERENCES employee (id_employee) ON DELETE SET NULL
);

INSERT INTO machinist (employee_id) VALUES (1);
INSERT INTO machinist (employee_id) VALUES (2);
INSERT INTO machinist (employee_id) VALUES (3);

CREATE TABLE customer_service (
    id_customer_service INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    UNIQUE(employee_id),
    FOREIGN KEY (employee_id) REFERENCES employee (id_employee) ON DELETE SET NULL
);

INSERT INTO customer_service (employee_id) VALUES (4);
INSERT INTO customer_service (employee_id) VALUES (5);


CREATE TABLE locomotive (
    id_locomotive INT PRIMARY KEY AUTO_INCREMENT,
    locomotive_number VARCHAR(30),
    vendor VARCHAR(50) NOT NULL,
    manufacture_year INT,
    note TEXT,
    locomotive_type_id INT,
    UNIQUE(locomotive_number),
    FOREIGN KEY (locomotive_type_id) REFERENCES locomotive_type (id_locomotive_type) ON DELETE SET NULL
);

INSERT INTO locomotive (locomotive_number, vendor, manufacture_year, note, locomotive_type_id) VALUES ("LCM-12345", 'PT. INKA', 2010, '', 1);
INSERT INTO locomotive (locomotive_number, vendor, manufacture_year, note, locomotive_type_id) VALUES ("LCM-12355", 'PT. INKA', 2010, '', 1);
INSERT INTO locomotive (locomotive_number, vendor, manufacture_year, note, locomotive_type_id) VALUES ("LCM-12365", 'PT. INKA', 2011, '', 2);
INSERT INTO locomotive (locomotive_number, vendor, manufacture_year, note, locomotive_type_id) VALUES ("LCM-12375", 'PT. INKA', 2011, '', 2);
INSERT INTO locomotive (locomotive_number, vendor, manufacture_year, note, locomotive_type_id) VALUES ("LCM-12385", 'PT. INKA', 2012, '', 3);


CREATE TABLE train (
    id_train INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    train_number VARCHAR(20) NOT NULL,
    UNIQUE(train_number)
);

INSERT INTO train (name, train_number) VALUES ("KA Argo Parahyangan", 'KA-123');
INSERT INTO train (name, train_number) VALUES ("KA Argo Wilis", 'KA-124');
INSERT INTO train (name, train_number) VALUES ("KA Argo Roro", 'KA-125');
INSERT INTO train (name, train_number) VALUES ("KA Argo Manis", 'KA-126');
INSERT INTO train (name, train_number) VALUES ("KA Argo Nagara", 'KA-127');

CREATE TABLE route (
    id_route INT PRIMARY KEY AUTO_INCREMENT,
    destination_order INT NOT NULL,
    departure_time TIME NOT NULL,
    arrival_time TIME NOT NULL,
    train_id INT,
    station_id INT,
    FOREIGN KEY (train_id) REFERENCES train (id_train) ON DELETE SET NULL,
    FOREIGN KEY (station_id) REFERENCES station (id_station) ON DELETE SET NULL
);

INSERT INTO route (destination_order, departure_time, arrival_time, train_id, station_id) VALUES (1, '07:00', '08:00', 1, 1);
INSERT INTO route (destination_order, departure_time, arrival_time, train_id, station_id) VALUES (1, '08:00', '08:45', 1, 2);
INSERT INTO route (destination_order, departure_time, arrival_time, train_id, station_id) VALUES (1, '08:45', '09:15', 1, 3);
INSERT INTO route (destination_order, departure_time, arrival_time, train_id, station_id) VALUES (1, '09:15', '10:00', 1, 4);
INSERT INTO route (destination_order, departure_time, arrival_time, train_id, station_id) VALUES (1, '10:00', '10:30', 1, 5);

CREATE TABLE basic_price (
    id_basic_price INT PRIMARY KEY AUTO_INCREMENT,
    price DOUBLE NOT NULL,
    train_id INT,
    service_type_id INT,
    FOREIGN KEY (train_id) REFERENCES train (id_train) ON DELETE SET NULL,
    FOREIGN KEY (service_type_id) REFERENCES service_type (id_service_type) ON DELETE SET NULL
);

INSERT INTO basic_price(price, train_id, service_type_id) VALUES (70000, 1, 5);
INSERT INTO basic_price(price, train_id, service_type_id) VALUES (50000, 1, 3);
INSERT INTO basic_price(price, train_id, service_type_id) VALUES (20000, 1, 1);

CREATE TABLE trip (
    id_trip INT PRIMARY KEY AUTO_INCREMENT,
    train_number VARCHAR(20) NOT NULL,
    departure_date DATE NOT NULL,
    departure_time TIME NOT NULL,
    arrival_time TIME NOT NULL,
    created_at TIMESTAMP,
    machinist_id INT,
    locomotive_id INT,
    FOREIGN KEY (machinist_id) REFERENCES machinist (id_machinist) ON DELETE SET NULL,
    FOREIGN KEY (locomotive_id) REFERENCES locomotive (id_locomotive) ON DELETE SET NULL
);

INSERT INTO trip(train_number, departure_date, departure_time, arrival_time, machinist_id, locomotive_id)
    VALUES ('KA-123', '2018-07-06', '07:00', '10:00', 1, 1);
INSERT INTO trip(train_number, departure_date, departure_time, arrival_time, machinist_id, locomotive_id)
    VALUES ('KA-123', '2018-07-07', '07:00', '10:00', 1, 1);
INSERT INTO trip(train_number, departure_date, departure_time, arrival_time, machinist_id, locomotive_id)
    VALUES ('KA-123', '2018-07-08', '07:00', '10:00', 1, 1);
INSERT INTO trip(train_number, departure_date, departure_time, arrival_time, machinist_id, locomotive_id)
    VALUES ('KA-123', '2018-07-09', '07:00', '10:00', 1, 1);
INSERT INTO trip(train_number, departure_date, departure_time, arrival_time, machinist_id, locomotive_id)
    VALUES ('KA-123', '2018-07-10', '07:00', '10:00', 1, 1);


CREATE TABLE wagon (
    id_wagon INT PRIMARY KEY AUTO_INCREMENT,
    wagon_number VARCHAR(20) NOT NULL,
    train_id INT,
    service_type_id INT,
    UNIQUE(wagon_number),
    FOREIGN KEY (train_id) REFERENCES train (id_train) ON DELETE SET NULL,
    FOREIGN KEY (service_type_id) REFERENCES service_type (id_service_type) ON DELETE SET NULL
);

INSERT INTO wagon (wagon_number, train_id, service_type_id)
    VALUES ('WG-12345', 1, 4);
INSERT INTO wagon (wagon_number, train_id, service_type_id)
    VALUES ('WG-12344', 1, 4);
INSERT INTO wagon (wagon_number, train_id, service_type_id)
    VALUES ('WG-12343', 1, 4);
INSERT INTO wagon (wagon_number, train_id, service_type_id)
    VALUES ('WG-12342', 1, 4);
INSERT INTO wagon (wagon_number, train_id, service_type_id)
    VALUES ('WG-12341', 1, 4);

CREATE TABLE wagon_facility_usage (
    id_wagon_facility_usage INT PRIMARY KEY AUTO_INCREMENT,
    wagon_id INT,
    wagon_facility_id INT,
    FOREIGN KEY (wagon_id) REFERENCES wagon (id_wagon) ON DELETE SET NULL,
    FOREIGN KEY (wagon_facility_id) REFERENCES wagon_facility (id_wagon_facility) ON DELETE SET NULL
);

INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (1, 1);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (1, 2);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (1, 3);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (2, 1);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (2, 2);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (2, 3);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (3, 1);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (3, 2);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (3, 3);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (4, 1);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (4, 2);
INSERT INTO wagon_facility_usage (wagon_id, wagon_facility_id) VALUES (5, 3);


CREATE TABLE wagon_seat (
    id_wagon_seat INT PRIMARY KEY AUTO_INCREMENT,
    wagon_number VARCHAR(20) NOT NULL,
    wagon_id INT,
    UNIQUE(wagon_number),
    FOREIGN KEY (wagon_id) REFERENCES wagon (id_wagon) ON DELETE SET NULL
);

INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('12A', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('12B', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('12C', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('12D', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('13A', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('13B', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('13C', 1);
INSERT INTO wagon_seat (wagon_number, wagon_id) VALUES ('13D', 1);

CREATE TABLE customer (
    id_customer INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL,
    phone_number VARCHAR(25) NOT NULL,
    gender ENUM('male', 'female', 'undefined'),
    identity_card_number VARCHAR(30) NOT NULL,
    date_of_birth DATE NOT NULL,
    place_of_birth VARCHAR(30),
    address TEXT,
    created_at TIMESTAMP,
    UNIQUE(email),
    UNIQUE(phone_number)
);

INSERT INTO customer (first_name, last_name, email, phone_number, gender, identity_card_number, date_of_birth, place_of_birth, address)
    VALUES ('Customer', 'Satu', 'customer.satu@gmail.com', '7591234', 'male', '12345678910', '1990-09-09', 'Bandung', '');
INSERT INTO customer (first_name, last_name, email, phone_number, gender, identity_card_number, date_of_birth, place_of_birth, address)
    VALUES ('Customer', 'Dua', 'customer.dua@gmail.com', '7591235', 'male', '12345678911', '1990-09-19', 'Bandung', '');
INSERT INTO customer (first_name, last_name, email, phone_number, gender, identity_card_number, date_of_birth, place_of_birth, address)
    VALUES ('Customer', 'Tiga', 'customer.tiga@gmail.com', '7591236', 'male', '12345678912', '1990-09-29', 'Bandung', '');
INSERT INTO customer (first_name, last_name, email, phone_number, gender, identity_card_number, date_of_birth, place_of_birth, address)
    VALUES ('Customer', 'Empat', 'customer.empat@gmail.com', '7591237', 'male', '12345678913', '1990-07-09', 'Bandung', '');
INSERT INTO customer (first_name, last_name, email, phone_number, gender, identity_card_number, date_of_birth, place_of_birth, address)
    VALUES ('Customer', 'Lima', 'customer.lima@gmail.com', '7591238', 'male', '12345678914', '1990-08-09', 'Bandung', '');

CREATE TABLE booking (
    id_booking INT PRIMARY KEY AUTO_INCREMENT,
    booking_status ENUM('success', 'onprogress', 'fail') NOT NULL,
    created_at TIMESTAMP,
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customer (id_customer) ON DELETE SET NULL
);

CREATE TABLE passenger_type (
    id_passenger_type INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);

CREATE TABLE booking_detail (
    id_booking_detail INT PRIMARY KEY AUTO_INCREMENT,
    book_code VARCHAR(10) NOT NULL,
    price DOUBLE NOT NULL,
    created_at TIMESTAMP,
    booking_id INT,
    wagon_seat_id INT,
    passenger_type_id INT,
    FOREIGN KEY(booking_id) REFERENCES booking (id_booking) ON DELETE SET NULL,
    FOREIGN KEY(wagon_seat_id) REFERENCES wagon_seat (id_wagon_seat) ON DELETE SET NULL,
    FOREIGN KEY(passenger_type_id) REFERENCES passenger_type (id_passenger_type) ON DELETE SET NULL
);

CREATE TABLE payment (
    id_payment INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT,
    payment_status ENUM('success', 'fail') NOT NULL,
    total DOUBLE NOT NULL,
    cash DOUBLE NOT NULL,
    change_due DOUBLE NOT NULL,
    created_at TIMESTAMP,
    payment_datetime DATETIME NOT NULL,
    customer_service_id INT,
    payment_method_id INT,
    UNIQUE(booking_id),
    FOREIGN KEY(booking_id) REFERENCES booking (id_booking) ON DELETE SET NULL,
    FOREIGN KEY(customer_service_id) REFERENCES customer_service (id_customer_service) ON DELETE SET NULL,
    FOREIGN KEY(payment_method_id) REFERENCES payment_method (id_payment_method) ON DELETE SET NULL
);

CREATE TABLE ticket_refund (
    id_ticket_refund INT PRIMARY KEY AUTO_INCREMENT,
    note TEXT,
    created_at TIMESTAMP,
    refund_at DATETIME NOT NULL,
    ticket_price DOUBLE NOT NULL,
    refund_amount DOUBLE NOT NULL,
    booking_detail_id INT,
    FOREIGN KEY(booking_detail_id) REFERENCES booking_detail (id_booking_detail) ON DELETE SET NULL
);