Advertisement
Guest User

Untitled

a guest
Nov 13th, 2019
125
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 3.88 KB | None | 0 0
  1. CREATE SCHEMA MOVIE;
  2.  
  3. CREATE TABLE MOVIE.MOVIE (
  4.     KODE_DVD VARCHAR(10) PRIMARY KEY NOT NULL,
  5.     JUDUL VARCHAR(60),
  6.     HARGA_SEWA DOUBLE,
  7.     DENDA DOUBLE,
  8.     TAHUN_RILIS SMALLINT
  9. );
  10.  
  11. CREATE TABLE MOVIE.GENRE(
  12.     ID_GENRE SMALLINT PRIMARY KEY NOT NULL,
  13.     GENRE VARCHAR(45)
  14. );
  15.  
  16. CREATE TABLE MOVIE.GENRE_MOVIE(
  17.     ID_GENRE SMALLINT NOT NULL,
  18.     KODE_DVD VARCHAR(10) NOT NULL,
  19.     PRIMARY KEY (ID_GENRE, KODE_DVD),
  20.     FOREIGN KEY (ID_GENRE) REFERENCES MOVIE.GENRE (ID_GENRE),
  21.     FOREIGN KEY (KODE_DVD) REFERENCES MOVIE.MOVIE (KODE_DVD)
  22. );
  23. CREATE SCHEMA ALAMAT;
  24.  
  25. CREATE TABLE ALAMAT.KECAMATAN(
  26.     ID_KECAMATAN SMALLINT PRIMARY KEY NOT NULL,
  27.     KECAMATAN VARCHAR(45)
  28. );
  29.  
  30. CREATE TABLE ALAMAT.KELURAHAN (
  31.     ID_KELURAHAN SMALLINT PRIMARY KEY NOT NULL,
  32.     ID_KECAMATAN SMALLINT NOT NULL,
  33.     KELURAHAN VARCHAR(45),
  34.     FOREIGN KEY (ID_KECAMATAN) REFERENCES ALAMAT.KECAMATAN (ID_KECAMATAN)
  35. );
  36.  
  37. CREATE SCHEMA PELANGGAN;
  38.  
  39. CREATE TABLE PELANGGAN.PELANGGAN(
  40.     KODE_PELANGGAN VARCHAR(10) PRIMARY KEY NOT NULL,
  41.     ID_KELURAHAN SMALLINT NOT NULL,
  42.     NAMA VARCHAR(45),
  43.     ALAMAT VARCHAR(60),
  44.     JENIS_KELAMIN CHAR (1) CHECK (JENIS_KELAMIN IN ('P','L')),
  45.     FOREIGN KEY (ID_KELURAHAN) REFERENCES ALAMAT.KELURAHAN (ID_KELURAHAN)
  46. );
  47.  
  48. CREATE TABLE PELANGGAN.KONTAK(
  49.     NO_HP VARCHAR(25),
  50.     KODE_PELANGGAN VARCHAR(10) NOT NULL,
  51.     FOREIGN KEY (KODE_PELANGGAN) REFERENCES PELANGGAN.PELANGGAN (KODE_PELANGGAN)
  52. );
  53.  
  54. CREATE SCHEMA TRANSAKSI;
  55.  
  56. CREATE TABLE TRANSAKSI.TRANSAKSI(
  57.     KODE_DVD VARCHAR(10) NOT NULL,
  58.     KODE_PELANGGAN VARCHAR(10) NOT NULL,
  59.     TANGGAL_SEWA DATE NOT NULL,
  60.     TANGGAL_WAJIB_KEMBALI DATE,
  61.     TANGGAL_REALISASI_KEMBALI DATE,
  62.     PRIMARY KEY (KODE_DVD, KODE_PELANGGAN, TANGGAL_SEWA),
  63.     FOREIGN KEY (KODE_DVD) REFERENCES MOVIE.MOVIE (KODE_DVD),
  64.     FOREIGN KEY (KODE_PELANGGAN) REFERENCES PELANGGAN.PELANGGAN (KODE_PELANGGAN)
  65. );
  66.  
  67.  
  68. INSERT INTO MOVIE.MOVIE (KODE_DVD, JUDUL, HARGA_SEWA, DENDA, TAHUN_RILIS)
  69. VALUES ('01', 'Avengers: Endgame', 10000.0, 2000.0, 2019),
  70.          ('02','Captain Marvel', 10000.0, 2000, 2019),
  71.          ('03','Fast and Furious Present: Hobbs & Shaw', 10000.0, 2000, 2019),
  72.          ('04','Avengers: Endgame', 10000.0, 2000, 2019),
  73.          ('05','Joker', 10000.0, 2000, 2019);
  74.  
  75. INSERT INTO MOVIE.GENRE (ID_GENRE, GENRE)
  76. VALUES (1, 'Action' ),
  77.         (2, 'Adventure'),
  78.         (3, 'Superhero'),
  79.         (4, 'Fantasy'),
  80.         (5, 'Comedy'),
  81.         (6, 'Mystery' ),
  82.         (7, 'Buddy Cop' ),
  83.         (8, 'Drama' ),
  84.         (9, 'Thriller' );
  85.  
  86. INSERT INTO MOVIE.GENRE_MOVIE (ID_GENRE, KODE_DVD)
  87. VALUES (1, '01'),
  88.         (2, '01'),
  89.         (3, '01'),
  90.         (4, '01'),
  91.         (1, '02'),
  92.         (2, '02'),
  93.         (3, '02'),
  94.         (4, '02'),
  95.         (1, '03'),
  96.         (2, '03'),
  97.         (5, '03'),
  98.         (6, '03'),
  99.         (7, '03'),
  100.         (1, '04'),
  101.         (2, '04'),
  102.         (3, '04'),
  103.         (4, '04'),
  104.         (8, '05'),
  105.         (9, '05');
  106.  
  107. INSERT INTO ALAMAT.KECAMATAN (ID_KECAMATAN, KECAMATAN)
  108. VALUES  (111, 'Lowokwaru');
  109.  
  110.  
  111. INSERT INTO ALAMAT.KELURAHAN (ID_KELURAHAN, ID_KECAMATAN, KELURAHAN )
  112. VALUES  (200, 111, 'Ketawanggede'),
  113.         (201, 111, 'Merjosari');
  114.        
  115. INSERT INTO PELANGGAN.PELANGGAN(KODE_PELANGGAN, ID_KELURAHAN, NAMA, ALAMAT, JENIS_KELAMIN)
  116. VALUES  ('C01', 200, 'Eko', 'Dinoyo no 01', 'L'),
  117.         ('C02', 201, 'Emil', 'Merjosari Indah 55', 'L');
  118.        
  119. INSERT INTO PELANGGAN.KONTAK(NO_HP, KODE_PELANGGAN)
  120. VALUES ('08127658290','C01'),
  121.         ('08882126745','C01'),
  122.         ('08751234566','C02'),
  123.         ('0857721222','C02');
  124.  
  125.  
  126. INSERT INTO TRANSAKSI.TRANSAKSI (KODE_DVD, KODE_PELANGGAN, TANGGAL_SEWA, TANGGAL_WAJIB_KEMBALI, TANGGAL_REALISASI_KEMBALI)
  127. VALUES ('01', 'C01', '2019-11-1', '2019-11-2', '2019-11-2'),
  128.         ('02', 'C01', '2019-11-12', '2019-11-13', '2019-11-15'),
  129.         ('03', 'C02', '2019-11-12', '2019-11-12', '2019-11-12'),
  130.         ('04', 'C02', '2019-11-12', '2019-11-12', '2019-11-12');
  131.  
  132. SELECT * FROM MOVIE.MOVIE;
  133. SELECT * FROM MOVIE.GENRE;
  134. SELECT * FROM MOVIE.GENRE_MOVIE;
  135. SELECT * FROM ALAMAT.KECAMATAN;
  136. SELECT * FROM ALAMAT.KELURAHAN;
  137. SELECT * FROM PELANGGAN.KONTAK;
  138. SELECT * FROM PELANGGAN.PELANGGAN;
  139. SELECT * FROM TRANSAKSI.TRANSAKSI;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement