1. DDL.sql

CREATE TABLE KARYAWAN 
(
  IDKARYAWAN VARCHAR2(10) NOT NULL 
, NAMAKARYAWAN VARCHAR2(30) 
, EMAIL VARCHAR2(30) 
, NOTELP VARCHAR2(20) 
, CONSTRAINT KARYAWAN_PK PRIMARY KEY 
  (
    IDKARYAWAN 
  )
  ENABLE 
);

CREATE TABLE SUPERVISOR 
(
  NIP VARCHAR2(10) NOT NULL 
, NAMASUPERVISOR VARCHAR2(30) 
, THNMASUK DATE 
, CONSTRAINT SUPERVISOR_PK PRIMARY KEY 
  (
    NIP 
  )
  ENABLE 
);

CREATE TABLE CUSTOMER 
(
  IDCUSTOMER VARCHAR2(10) NOT NULL 
, NAMACUSTOMER VARCHAR2(30) 
, JENISKELAMIN VARCHAR2(10) 
, TGLLAHIR DATE 
, ALAMAT VARCHAR2(30) 
, EMAIL VARCHAR2(30) 
, NOTELP VARCHAR2(20) 
, CONSTRAINT CUSTOMER_PK PRIMARY KEY 
  (
    IDCUSTOMER 
  )
  ENABLE 
);

CREATE TABLE PAKETTRAVEL 
(
  KODEPAKET VARCHAR2(10) NOT NULL 
, IDKARYAWAN VARCHAR2(10) 
, NAMAPAKET VARCHAR2(30) 
, DESKRIPSI VARCHAR2(50) 
, HARGA NUMBER 
, CONSTRAINT PAKETTRAVEL_PK PRIMARY KEY 
  (
    KODEPAKET 
  )
  ENABLE 
);

ALTER TABLE PAKETTRAVEL
ADD CONSTRAINT PAKETTRAVEL_FK1 FOREIGN KEY
(
  IDKARYAWAN 
)
REFERENCES KARYAWAN
(
  IDKARYAWAN 
)
ENABLE;

CREATE TABLE PESAN 
(
  KODEPESANAN VARCHAR2(10) NOT NULL 
, IDCUSTOMER VARCHAR2(10) 
, KODEPAKET VARCHAR2(10) 
, NIP VARCHAR2(10) 
, TGLPESANAN DATE 
, TOTALHARGA NUMBER 
, STATUSBAYAR VARCHAR2(10) 
, CONSTRAINT PESAN_PK PRIMARY KEY 
  (
    KODEPESANAN 
  )
  ENABLE 
);

ALTER TABLE PESAN
ADD CONSTRAINT PESAN_FK1 FOREIGN KEY
(
  IDCUSTOMER 
)
REFERENCES CUSTOMER
(
  IDCUSTOMER 
)
ENABLE;

ALTER TABLE PESAN
ADD CONSTRAINT PESAN_FK2 FOREIGN KEY
(
  KODEPAKET 
)
REFERENCES PAKETTRAVEL
(
  KODEPAKET 
)
ENABLE;

ALTER TABLE PESAN
ADD CONSTRAINT PESAN_FK3 FOREIGN KEY
(
  NIP 
)
REFERENCES SUPERVISOR
(
  NIP 
)
ENABLE;

CREATE TABLE KEBERANGKATAN 
(
  IDBERANGKAT VARCHAR2(10) NOT NULL 
, TGLBERANGKAT DATE 
, NOKLOTER NUMBER 
, CONSTRAINT KEBERANGKATAN_PK PRIMARY KEY 
  (
    IDBERANGKAT 
  )
  ENABLE 
);

CREATE TABLE TRANSAKSI 
(
  NOTRANSAKSI VARCHAR2(10) NOT NULL 
, KODEPESANAN VARCHAR2(10) 
, IDBERANGKAT VARCHAR2(10) 
, TGLTRANSAKSI VARCHAR2(20) 
, TOTALBAYAR VARCHAR2(20) 
, CONSTRAINT TRANSAKSI_PK PRIMARY KEY 
  (
    NOTRANSAKSI 
  )
  ENABLE 
);

ALTER TABLE TRANSAKSI
ADD CONSTRAINT TRANSAKSI_FK1 FOREIGN KEY
(
  KODEPESANAN 
)
REFERENCES PESAN
(
  KODEPESANAN 
)
ENABLE;

ALTER TABLE TRANSAKSI
ADD CONSTRAINT TRANSAKSI_FK2 FOREIGN KEY
(
  IDBERANGKAT 
)
REFERENCES KEBERANGKATAN
(
  IDBERANGKAT 
)
ENABLE;

CREATE TABLE PEMANDU 
(
  IDPEMANDU VARCHAR2(10) NOT NULL 
, NAMAPEMANDU VARCHAR2(30) 
, CONSTRAINT PEMANDU_PK PRIMARY KEY 
  (
    IDPEMANDU 
  )
  ENABLE 
);

CREATE TABLE FASILITAS 
(
  IDFASILITAS VARCHAR2(10) NOT NULL 
, KAPASITASJAMAAH NUMBER 
, CONSTRAINT FASILITAS_PK PRIMARY KEY 
  (
    IDFASILITAS 
  )
  ENABLE 
);

CREATE TABLE DAPAT 
(
  NOTRANSAKSI VARCHAR2(10) NOT NULL 
, IDPEMANDU VARCHAR2(10) NOT NULL 
, IDBERANGKAT VARCHAR2(10) NOT NULL 
, IDFASILITAS VARCHAR2(10) NOT NULL 
, CONSTRAINT DAPAT_PK PRIMARY KEY 
  (
    NOTRANSAKSI 
  , IDPEMANDU 
  , IDBERANGKAT 
  , IDFASILITAS 
  )
  ENABLE 
);

ALTER TABLE DAPAT
ADD CONSTRAINT DAPAT_FK1 FOREIGN KEY
(
  NOTRANSAKSI 
)
REFERENCES TRANSAKSI
(
  NOTRANSAKSI 
)
ENABLE;

ALTER TABLE DAPAT
ADD CONSTRAINT DAPAT_FK2 FOREIGN KEY
(
  IDPEMANDU 
)
REFERENCES PEMANDU
(
  IDPEMANDU 
)
ENABLE;

ALTER TABLE DAPAT
ADD CONSTRAINT DAPAT_FK3 FOREIGN KEY
(
  IDBERANGKAT 
)
REFERENCES KEBERANGKATAN
(
  IDBERANGKAT 
)
ENABLE;

ALTER TABLE DAPAT
ADD CONSTRAINT DAPAT_FK4 FOREIGN KEY
(
  IDFASILITAS 
)
REFERENCES FASILITAS
(
  IDFASILITAS 
)
ENABLE;

CREATE TABLE KENDARAAN 
(
  IDFASILITAS VARCHAR2(10) NOT NULL 
, NOURUTKENDARAAN NUMBER 
, CONSTRAINT KENDARAAN_PK PRIMARY KEY 
  (
    IDFASILITAS 
  )
  ENABLE 
);

ALTER TABLE KENDARAAN
ADD CONSTRAINT KENDARAAN_FK1 FOREIGN KEY
(
  IDFASILITAS 
)
REFERENCES FASILITAS
(
  IDFASILITAS 
)
ENABLE;

CREATE TABLE HOTEL 
(
  IDFASILITAS VARCHAR2(10) NOT NULL 
, NAMAHOTEL VARCHAR2(30) 
, CONSTRAINT HOTEL_PK PRIMARY KEY 
  (
    IDFASILITAS 
  )
  ENABLE 
);

ALTER TABLE HOTEL
ADD CONSTRAINT HOTEL_FK1 FOREIGN KEY
(
  IDFASILITAS 
)
REFERENCES FASILITAS
(
  IDFASILITAS 
)
ENABLE;

CREATE TABLE FASILITASHOTEL 
(
  IDFASILITASHOTEL VARCHAR2(10) NOT NULL 
, NAMAFASILITAS VARCHAR2(30) 
, DESKRIPSI VARCHAR2(50) 
, CONSTRAINT FASILITASHOTEL_PK PRIMARY KEY 
  (
    IDFASILITASHOTEL 
  )
  ENABLE 
);

CREATE TABLE PUNYA 
(
  IDFASILITAS VARCHAR2(10) NOT NULL 
, IDFASILITASHOTEL VARCHAR2(10) NOT NULL 
, CONSTRAINT PUNYA_PK PRIMARY KEY 
  (
    IDFASILITAS 
  , IDFASILITASHOTEL 
  )
  ENABLE 
);

ALTER TABLE PUNYA
ADD CONSTRAINT PUNYA_FK1 FOREIGN KEY
(
  IDFASILITAS 
)
REFERENCES HOTEL
(
  IDFASILITAS 
)
ENABLE;

ALTER TABLE PUNYA
ADD CONSTRAINT PUNYA_FK2 FOREIGN KEY
(
  IDFASILITASHOTEL 
)
REFERENCES FASILITASHOTEL
(
  IDFASILITASHOTEL 
)
ENABLE;