Advertisement
VelzA

error

Mar 11th, 2020
1,203
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE halte CASCADE CONSTRAINT PURGE;
  2. DROP TABLE kepala_pengurus CASCADE CONSTRAINT PURGE;
  3. DROP TABLE petugas_kebersihan CASCADE CONSTRAINT PURGE;
  4. DROP TABLE kuli_bangunan CASCADE CONSTRAINT PURGE;
  5. DROP TABLE bus CASCADE CONSTRAINT PURGE;
  6. DROP TABLE penumpang CASCADE CONSTRAINT PURGE;
  7. DROP TABLE kartu_langganan CASCADE CONSTRAINT PURGE;
  8. DROP TABLE ktp CASCADE CONSTRAINT PURGE;
  9. DROP TABLE pengemudi CASCADE CONSTRAINT PURGE;
  10. DROP TABLE sim CASCADE CONSTRAINT PURGE;
  11.  
  12. CREATE TABLE halte(
  13.     id_halte VARCHAR2(3) PRIMARY KEY,
  14.     nama_halte VARCHAR2(50),
  15.     nomor_halte NUMBER(2),
  16.     alamat_halte VARCHAR2(100)
  17. );
  18. CREATE TABLE kepala_pengurus(
  19.     id_kepalaPengurus VARCHAR2(3) PRIMARY KEY,
  20.     nama_kepala_pengurus VARCHAR2(50),
  21.     jenis_kelamin_kepala_pengurus VARCHAR(1) CHECK(jenis_kelamin_kepala_pengurus = 'L' OR jenis_kelamin_kepala_pengurus = 'P'),
  22.     tanggal_lahir_kepala_pengurus DATE NOT NULL
  23. );
  24. CREATE TABLE bus(
  25.     id_bus VARCHAR2(3) PRIMARY KEY,
  26.     nama_bus VARCHAR2(20),
  27.     nomor_bus NUMBER(2),
  28.     ukuran_bus NUMBER(3)
  29. );
  30. CREATE TABLE sim(
  31.     id_sim VARCHAR2(3) PRIMARY KEY,
  32.     nama_pemilik VARCHAR2(50),
  33.     alamat_pemilik VARCHAR2(100),
  34.     jenis_sim VARCHAR2(1) CHECK(jenis_sim = 'B' OR jenis_sim = 'A')
  35. );
  36. CREATE TABLE ktp(
  37.     id_ktp VARCHAR2(3) PRIMARY KEY,
  38.     nik NUMBER(16),
  39.     nama VARCHAR2(50),
  40.     alamat VARCHAR2(100)
  41. );
  42. CREATE TABLE kartu_langganan(
  43.     id_kartu VARCHAR2(3) PRIMARY KEY,
  44.     nama_kartu VARCHAR2(50),
  45.     nomor_kartu NUMBER(5),
  46.     jenis_kartu VARCHAR2(7) CHECK(jenis_kartu = 'Premium' OR jenis_kartu = 'Gold')
  47. );
  48. CREATE TABLE petugas_kebersihan(
  49.     id_petugas VARCHAR2(3) PRIMARY KEY,
  50.     id_halte VARCHAR2(3) REFERENCES halte(id_halte),
  51.     nama_petugas VARCHAR(50),
  52.     jenis_kelamin_petugas VARCHAR2(1) CHECK(jenis_kelamin_petugas = 'L' OR jenis_kelamin_petugas = 'P'),
  53.     tanggal_lahir_petugas DATE NOT NULL
  54. );
  55. CREATE TABLE kuli_bangunan(
  56.     id_kuli VARCHAR2(3) PRIMARY KEY,
  57.     id_kepalaPengurus VARCHAR2(3) REFERENCES kepala_pengurus(id_kepalaPengurus),
  58.     id_halte VARCHAR2(3) PRIMARY KEY REFERENCES halte(id_halte),
  59.     nama_kuli VARCHAR2(50),
  60.     jenis_kelamin_kuli VARCHAR2(1) CHECK(jenis_kelamin_kuli = 'L' OR jenis_kelamin_kuli = 'P'),
  61.     tanggal_lahir_kuli DATE NOT NULL
  62. );
  63. CREATE TABLE penumpang(
  64.     id_penumpang VARCHAR2(3) PRIMARY KEY,
  65.     id_kartu VARCHAR2(3) REFERENCES kartu_langganan(id_kartu),
  66.     id_bus VARCHAR2(3) REFERENCES bus(id_bus),
  67.     id_ktp VARCHAR2(3) REFERENCES ktp(id_ktp),
  68.     nama_penumpang VARCHAR2(50),
  69.     jenis_kelamin_penumpang VARCHAR2(1) CHECK(jenis_kelamin_penumpang = 'L' OR jenis_kelamin_penumpang = 'P'),
  70.     tanggal_lahir_penumpang DATE NOT NULL
  71. );
  72. CREATE TABLE pengemudi(
  73.     id_pengemudi VARCHAR2(3) PRIMARY KEY,
  74.     id_bus VARCHAR2(3) REFERENCES bus(id_bus),
  75.     id_ktp VARCHAR2(3) REFERENCES ktp(id_ktp),
  76.     nama_pengemudi VARCHAR2(50),
  77.     umur_pengemudi NUMBER(2),
  78.     tanggal_lahir_pengemudi DATE
  79. );
  80. -- Composite PRIMARY KEY
  81. CREATE TABLE membersihkan_2(
  82.     id_petugas VARCHAR2(3) PRIMARY KEY REFERENCES petugas_kebersihan(id_petugas),
  83.     id_bus VARCHAR2(3) PRIMARY KEY REFERENCES bus(id_bus)
  84. );
  85. CREATE TABLE ada(
  86.     id_halte VARCHAR2(3) PRIMARY KEY REFERENCES halte(id_halte),
  87.     id_bus VARCHAR2(3) PRIMARY KEY REFERENCES bus(id_bus)
  88. );
  89. CREATE TABLE kunjungi_1(
  90.     id_penumpang VARCHAR2(3) PRIMARY KEY REFERENCES penumpang(id_penumpang),
  91.     id_halte VARCHAR2(3) PRIMARY KEY REFERENCES halte(id_halte)
  92. );
  93. CREATE TABLE memiliki(
  94.     id_halte VARCHAR2(3) REFERENCES halte(id_halte),
  95.     id_kepalaPengurus VARCHAR2(3) REFERENCES kepala_pengurus(id_kepalaPengurus)
  96. );
  97.  
  98.  
  99.  
  100. -- Soal no 5
  101.  
  102. ALTER TABLE halte ADD(FASILITAS_HALTE VARCHAR2(20));
  103. ALTER TABLE sim ADD(MASA_AKTIF DATE);
  104.  
  105. ALTER TABLE halte DROP COLUMN FASILITAS_HALTE;
  106. ALTER TABLE sim DROP COLUMN MASA_AKTIF;
Advertisement
RAW Paste Data Copied
Advertisement