Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE halte CASCADE CONSTRAINT PURGE;
- DROP TABLE kepala_pengurus CASCADE CONSTRAINT PURGE;
- DROP TABLE petugas_kebersihan CASCADE CONSTRAINT PURGE;
- DROP TABLE kuli_bangunan CASCADE CONSTRAINT PURGE;
- DROP TABLE bus CASCADE CONSTRAINT PURGE;
- DROP TABLE penumpang CASCADE CONSTRAINT PURGE;
- DROP TABLE kartu_langganan CASCADE CONSTRAINT PURGE;
- DROP TABLE ktp CASCADE CONSTRAINT PURGE;
- DROP TABLE pengemudi CASCADE CONSTRAINT PURGE;
- DROP TABLE sim CASCADE CONSTRAINT PURGE;
- CREATE TABLE halte(
- id_halte VARCHAR2(3) PRIMARY KEY,
- nama_halte VARCHAR2(50),
- nomor_halte NUMBER(2),
- alamat_halte VARCHAR2(100)
- );
- CREATE TABLE kepala_pengurus(
- id_kepalaPengurus VARCHAR2(3) PRIMARY KEY,
- nama_kepala_pengurus VARCHAR2(50),
- jenis_kelamin_kepala_pengurus VARCHAR(1) CHECK(jenis_kelamin_kepala_pengurus = 'L' OR jenis_kelamin_kepala_pengurus = 'P'),
- tanggal_lahir_kepala_pengurus DATE NOT NULL
- );
- CREATE TABLE bus(
- id_bus VARCHAR2(3) PRIMARY KEY,
- nama_bus VARCHAR2(20),
- nomor_bus NUMBER(2),
- ukuran_bus NUMBER(3)
- );
- CREATE TABLE sim(
- id_sim VARCHAR2(3) PRIMARY KEY,
- nama_pemilik VARCHAR2(50),
- alamat_pemilik VARCHAR2(100),
- jenis_sim VARCHAR2(1) CHECK(jenis_sim = 'B' OR jenis_sim = 'A')
- );
- CREATE TABLE ktp(
- id_ktp VARCHAR2(3) PRIMARY KEY,
- nik NUMBER(16),
- nama VARCHAR2(50),
- alamat VARCHAR2(100)
- );
- CREATE TABLE kartu_langganan(
- id_kartu VARCHAR2(3) PRIMARY KEY,
- nama_kartu VARCHAR2(50),
- nomor_kartu NUMBER(5),
- jenis_kartu VARCHAR2(7) CHECK(jenis_kartu = 'Premium' OR jenis_kartu = 'Gold')
- );
- CREATE TABLE petugas_kebersihan(
- id_petugas VARCHAR2(3) PRIMARY KEY,
- id_halte VARCHAR2(3) REFERENCES halte(id_halte),
- nama_petugas VARCHAR(50),
- jenis_kelamin_petugas VARCHAR2(1) CHECK(jenis_kelamin_petugas = 'L' OR jenis_kelamin_petugas = 'P'),
- tanggal_lahir_petugas DATE NOT NULL
- );
- CREATE TABLE kuli_bangunan(
- id_kuli VARCHAR2(3) PRIMARY KEY,
- id_kepalaPengurus VARCHAR2(3) REFERENCES kepala_pengurus(id_kepalaPengurus),
- id_halte VARCHAR2(3) PRIMARY KEY REFERENCES halte(id_halte),
- nama_kuli VARCHAR2(50),
- jenis_kelamin_kuli VARCHAR2(1) CHECK(jenis_kelamin_kuli = 'L' OR jenis_kelamin_kuli = 'P'),
- tanggal_lahir_kuli DATE NOT NULL
- );
- CREATE TABLE penumpang(
- id_penumpang VARCHAR2(3) PRIMARY KEY,
- id_kartu VARCHAR2(3) REFERENCES kartu_langganan(id_kartu),
- id_bus VARCHAR2(3) REFERENCES bus(id_bus),
- id_ktp VARCHAR2(3) REFERENCES ktp(id_ktp),
- nama_penumpang VARCHAR2(50),
- jenis_kelamin_penumpang VARCHAR2(1) CHECK(jenis_kelamin_penumpang = 'L' OR jenis_kelamin_penumpang = 'P'),
- tanggal_lahir_penumpang DATE NOT NULL
- );
- CREATE TABLE pengemudi(
- id_pengemudi VARCHAR2(3) PRIMARY KEY,
- id_bus VARCHAR2(3) REFERENCES bus(id_bus),
- id_ktp VARCHAR2(3) REFERENCES ktp(id_ktp),
- nama_pengemudi VARCHAR2(50),
- umur_pengemudi NUMBER(2),
- tanggal_lahir_pengemudi DATE
- );
- -- Composite PRIMARY KEY
- CREATE TABLE membersihkan_2(
- id_petugas VARCHAR2(3) PRIMARY KEY REFERENCES petugas_kebersihan(id_petugas),
- id_bus VARCHAR2(3) PRIMARY KEY REFERENCES bus(id_bus)
- );
- CREATE TABLE ada(
- id_halte VARCHAR2(3) PRIMARY KEY REFERENCES halte(id_halte),
- id_bus VARCHAR2(3) PRIMARY KEY REFERENCES bus(id_bus)
- );
- CREATE TABLE kunjungi_1(
- id_penumpang VARCHAR2(3) PRIMARY KEY REFERENCES penumpang(id_penumpang),
- id_halte VARCHAR2(3) PRIMARY KEY REFERENCES halte(id_halte)
- );
- CREATE TABLE memiliki(
- id_halte VARCHAR2(3) REFERENCES halte(id_halte),
- id_kepalaPengurus VARCHAR2(3) REFERENCES kepala_pengurus(id_kepalaPengurus)
- );
- -- Soal no 5
- ALTER TABLE halte ADD(FASILITAS_HALTE VARCHAR2(20));
- ALTER TABLE sim ADD(MASA_AKTIF DATE);
- ALTER TABLE halte DROP COLUMN FASILITAS_HALTE;
- ALTER TABLE sim DROP COLUMN MASA_AKTIF;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement