Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE SCHEMA MOVIE;
- CREATE TABLE MOVIE.MOVIE (
- KODE_DVD VARCHAR(10) PRIMARY KEY NOT NULL,
- JUDUL VARCHAR(60),
- HARGA_SEWA DOUBLE,
- DENDA DOUBLE,
- TAHUN_RILIS SMALLINT
- );
- CREATE TABLE MOVIE.GENRE(
- ID_GENRE SMALLINT PRIMARY KEY NOT NULL,
- GENRE VARCHAR(45)
- );
- CREATE TABLE MOVIE.GENRE_MOVIE(
- ID_GENRE SMALLINT NOT NULL,
- KODE_DVD VARCHAR(10) NOT NULL,
- PRIMARY KEY (ID_GENRE, KODE_DVD),
- FOREIGN KEY (ID_GENRE) REFERENCES MOVIE.GENRE (ID_GENRE),
- FOREIGN KEY (KODE_DVD) REFERENCES MOVIE.MOVIE (KODE_DVD)
- );
- CREATE SCHEMA ALAMAT;
- CREATE TABLE ALAMAT.KECAMATAN(
- ID_KECAMATAN SMALLINT PRIMARY KEY NOT NULL,
- KECAMATAN VARCHAR(45)
- );
- CREATE TABLE ALAMAT.KELURAHAN (
- ID_KELURAHAN SMALLINT PRIMARY KEY NOT NULL,
- ID_KECAMATAN SMALLINT NOT NULL,
- KELURAHAN VARCHAR(45),
- FOREIGN KEY (ID_KECAMATAN) REFERENCES ALAMAT.KECAMATAN (ID_KECAMATAN)
- );
- CREATE SCHEMA PELANGGAN;
- CREATE TABLE PELANGGAN.PELANGGAN(
- KODE_PELANGGAN VARCHAR(10) PRIMARY KEY NOT NULL,
- ID_KELURAHAN SMALLINT NOT NULL,
- NAMA VARCHAR(45),
- ALAMAT VARCHAR(60),
- JENIS_KELAMIN CHAR (1) CHECK (JENIS_KELAMIN IN ('P','L')),
- FOREIGN KEY (ID_KELURAHAN) REFERENCES ALAMAT.KELURAHAN (ID_KELURAHAN)
- );
- CREATE TABLE PELANGGAN.KONTAK(
- NO_HP VARCHAR(25),
- KODE_PELANGGAN VARCHAR(10) NOT NULL,
- FOREIGN KEY (KODE_PELANGGAN) REFERENCES PELANGGAN.PELANGGAN (KODE_PELANGGAN)
- );
- CREATE SCHEMA TRANSAKSI;
- CREATE TABLE TRANSAKSI.TRANSAKSI(
- KODE_DVD VARCHAR(10) NOT NULL,
- KODE_PELANGGAN VARCHAR(10) NOT NULL,
- TANGGAL_SEWA DATE NOT NULL,
- TANGGAL_WAJIB_KEMBALI DATE,
- TANGGAL_REALISASI_KEMBALI DATE,
- PRIMARY KEY (KODE_DVD, KODE_PELANGGAN, TANGGAL_SEWA),
- FOREIGN KEY (KODE_DVD) REFERENCES MOVIE.MOVIE (KODE_DVD),
- FOREIGN KEY (KODE_PELANGGAN) REFERENCES PELANGGAN.PELANGGAN (KODE_PELANGGAN)
- );
- INSERT INTO MOVIE.MOVIE (KODE_DVD, JUDUL, HARGA_SEWA, DENDA, TAHUN_RILIS)
- VALUES ('01', 'Avengers: Endgame', 10000.0, 2000.0, 2019),
- ('02','Captain Marvel', 10000.0, 2000, 2019),
- ('03','Fast and Furious Present: Hobbs & Shaw', 10000.0, 2000, 2019),
- ('04','Avengers: Endgame', 10000.0, 2000, 2019),
- ('05','Joker', 10000.0, 2000, 2019);
- INSERT INTO MOVIE.GENRE (ID_GENRE, GENRE)
- VALUES (1, 'Action' ),
- (2, 'Adventure'),
- (3, 'Superhero'),
- (4, 'Fantasy'),
- (5, 'Comedy'),
- (6, 'Mystery' ),
- (7, 'Buddy Cop' ),
- (8, 'Drama' ),
- (9, 'Thriller' );
- INSERT INTO MOVIE.GENRE_MOVIE (ID_GENRE, KODE_DVD)
- VALUES (1, '01'),
- (2, '01'),
- (3, '01'),
- (4, '01'),
- (1, '02'),
- (2, '02'),
- (3, '02'),
- (4, '02'),
- (1, '03'),
- (2, '03'),
- (5, '03'),
- (6, '03'),
- (7, '03'),
- (1, '04'),
- (2, '04'),
- (3, '04'),
- (4, '04'),
- (8, '05'),
- (9, '05');
- INSERT INTO ALAMAT.KECAMATAN (ID_KECAMATAN, KECAMATAN)
- VALUES (111, 'Lowokwaru');
- INSERT INTO ALAMAT.KELURAHAN (ID_KELURAHAN, ID_KECAMATAN, KELURAHAN )
- VALUES (200, 111, 'Ketawanggede'),
- (201, 111, 'Merjosari');
- INSERT INTO PELANGGAN.PELANGGAN(KODE_PELANGGAN, ID_KELURAHAN, NAMA, ALAMAT, JENIS_KELAMIN)
- VALUES ('C01', 200, 'Eko', 'Dinoyo no 01', 'L'),
- ('C02', 201, 'Emil', 'Merjosari Indah 55', 'L');
- INSERT INTO PELANGGAN.KONTAK(NO_HP, KODE_PELANGGAN)
- VALUES ('08127658290','C01'),
- ('08882126745','C01'),
- ('08751234566','C02'),
- ('0857721222','C02');
- INSERT INTO TRANSAKSI.TRANSAKSI (KODE_DVD, KODE_PELANGGAN, TANGGAL_SEWA, TANGGAL_WAJIB_KEMBALI, TANGGAL_REALISASI_KEMBALI)
- VALUES ('01', 'C01', '2019-11-1', '2019-11-2', '2019-11-2'),
- ('02', 'C01', '2019-11-12', '2019-11-13', '2019-11-15'),
- ('03', 'C02', '2019-11-12', '2019-11-12', '2019-11-12'),
- ('04', 'C02', '2019-11-12', '2019-11-12', '2019-11-12');
- SELECT * FROM MOVIE.MOVIE;
- SELECT * FROM MOVIE.GENRE;
- SELECT * FROM MOVIE.GENRE_MOVIE;
- SELECT * FROM ALAMAT.KECAMATAN;
- SELECT * FROM ALAMAT.KELURAHAN;
- SELECT * FROM PELANGGAN.KONTAK;
- SELECT * FROM PELANGGAN.PELANGGAN;
- SELECT * FROM TRANSAKSI.TRANSAKSI;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement