Perancangan Basis Data
SQL (Structure Quert Language)
<>
Memulai Menjalankan
CMD untuk akses MySQL
1.
Buka cmd (cd c:\xampp\mysql\bin)
2.
Buka Direktori C:\xampp\mysql\bin
3.
Login mysql
–u root –p
Mengetahui versi MySQL
Select version();
Membuat Database
mysql>create database pesawat;
Memilih Database
mysql>USE pesawat;
Mengetahui Struktur
Tabel
DESC customer;
Melihat isi Tabel
SELECT * FROM customer;
A. Membuat Database
CREATE DATABASE pesawat;
B. Membuat Tabel
1. Tabel Customer
CREATE TABLE customer (
id_customer VARCHAR(3) PRIMARY KEY,
nama_customer VARCHAR(20) NOT NULL,
no_identitas CHAR(11) NOT NULL,
no_telp CHAR(11) NOT NULL);
2. Tabel Petugas
CREATE TABLE petugas (
id_petugas VARCHAR(3) PRIMARY KEY,
nama_petugas VARCHAR(20) NOT NULL);
3. Tabel Maskapai
CREATE TABLE maskapai (
id_maskapai VARCHAR(3) PRIMARY KEY,
nama_maskapai VARCHAR(30) NOT NULL,
jml_kursi VARCHAR(3) NOT NULL);
4. Tabel Keberangkatan
CREATE TABLE keberangkatan (
kd_keberangkatan VARCHAR(6) PRIMARY KEY,
tgl_berangkat DATE NOT NULL,
bandara_keberangkatan VARCHAR(30) NOT NULL,
bandara_tujuan VARCHAR(30) NOT NULL,
waktu_berangkat TIME NOT NULL,
waktu_sampai TIME NOT NULL);
5.
Tabel
Penerbangan
CREATE TABLE penerbangan (
kd_penerbangan VARCHAR(6) PRIMARY KEY,
kd_keberangkatan VARCHAR(6) NOT NULL,
id_maskapai VARCHAR(3) NOT NULL,
tgl_berangkat DATE NOT NULL,
bandara_keberangkatan VARCHAR(30) NOT NULL,
bandara_tujuan VARCHAR(30) NOT NULL,
waktu_berangkat TIME NOT NULL,
waktu_sampai TIME NOT NULL,
nama_maskapai VARCHAR(30) NOT NULL,
jml_kursi VARCHAR(3) NOT NULL);
6. Tabel Penjualan
CREATE TABLE penjualan (
kd_penjualan VARCHAR(6) PRIMARY KEY,
tgl_jual DATE NOT NULL,
kd_penerbangan VARCHAR(6) NOT NULL,
id_customer VARCHAR(3) NOT NULL,
id_petugas VARCHAR(3) NOT NULL,
harga VARCHAR(8) NOT NULL,
ppn VARCHAR(8) NOT NULL,
total_harga VARCHAR(8) NOT NULL);
7. Tabel Akun
CREATE TABLE akun (
nomor_akun VARCHAR(4) PRIMARY KEY,
nama_akun VARCHAR(20) NOT NULL);
8. Tabel Jurnal
CREATE TABLE jurnal (
id_jurnal VARCHAR(3) PRIMARY KEY,
tanggal DATE NOT NULL,
kd_penjualan VARCHAR(6) NOT NULL );
9. Tabel Jurnal_Penjualan
CREATE TABLE jurnal_penjualan (
kd_jurnal VARCHAR(3) PRIMARY KEY,
id_jurnal VARCHAR(3) NOT NULL,
nomor_akun VARCHAR(4) NOT NULL,
debit VARCHAR(6) NULL,
kredit VARCHAR(6) NULL);
C. Menambahkan Foreign Key
1. Tabel Penerbangan
ALTER TABLE penerbangan
ADD CONSTRAINT fk_penr_mask FOREIGN KEY (id_maskapai) REFERENCES maskapai
(id_maskapai) ;
ALTER TABLE penerbangan
ADD CONSTRAINT fk_penr_kbrn FOREIGN KEY (kd_keberangkatan) REFERENCES
keberangkatan (kd_keberangkatan) ;
2. Tabel Penjualan
ALTER TABLE penjualan
ADD CONSTRAINT fk_penj_penr FOREIGN KEY (kd_penerbangan) REFERENCES
penerbangan (kd_penerbangan) ;
ALTER TABLE penjualan
ADD CONSTRAINT fk_penj_cust FOREIGN KEY (id_customer) REFERENCES customer
(id_customer) ;
ALTER TABLE penjualan
ADD CONSTRAINT fk_penj_ptgs FOREIGN KEY (id_petugas) REFERENCES petugas
(id_petugas) ;
3.
Tabel
Jurnal
ALTER TABLE jurnal
ADD CONSTRAINT fk_jurn_penj FOREIGN KEY (kd_penjualan) REFERENCES
penjualan (kd_penjualan) ;
4.
Tabel jurnal_Penjualan
ALTER TABLE jurnal_penjualan
ADD CONSTRAINT fk_jurp_jurn FOREIGN KEY (id_jurnal) REFERENCES jurnal
(id_jurnal) ;
ALTER TABLE jurnal_penjualan
ADD CONSTRAINT fk_jurp_akun FOREIGN KEY (nomor_akun) REFERENCES akun
(nomor_akun) ;
D. Memasukkan Data
1. Tabel Customer
INSERT INTO customer VALUES (‘C01’, ‘Muchtarul Affandi’,
‘12345678901’, ‘98765432109’),
(‘C02’, ‘Eka Fauzi’, ‘12345678901’, ‘98765432109’),
(‘C03’, ‘Perianto’, ‘12345678901’, ‘98765432109’);
2. Tabel Petugas
INSERT INTO petugas VALUES (‘ATN’, ‘Anton setiabudi’),
(‘TLS’, ‘Tiara Lestari’),
(‘HTS’, ‘Hartono Sucipto’);
3. Tabel Maskapai
INSERT INTO maskapai VALUES (‘AAA’, ‘Air Asia Airlines’,
‘40’),
(‘ADA’, ‘Adam Air’, ‘40’),
(‘DAS’, ‘Dirgantara Air Service’, ‘60’);
4. Tabel Keberangkatan
INSERT INTO keberangkatan VALUES (‘JT330’, ‘2012-05-01’,
‘Bandara Soekarno - Hatta’, ‘Bandara Juanda’, ’06:00:00’, ’07:00:00’),
(‘JT331’, ‘2012-05-01’, ‘Bandara Soekarno - Hatta’, ‘Bandara
Adi Sumarmo’, ’08:00:00’, ’09:00:00’),
(‘JT332’, ‘2012-05-01’, ‘Bandara Halim Perdanakusuma’,
‘Bandara Ngurah Rai’, ’09:00:00’, ’11:00:00’);
5.
Tabel Penerbangan
INSERT INTO penerbangan VALUES (‘PE112’, ‘JT330’, ‘AAA’,
‘2012-05-01’, ‘Bandara Soekarno - Hatta’, ‘Bandara Juanda’, ’06:00:00’,
’07:00:00’, ‘Air Asia Airlines’, ‘40’),
(‘PE113‘, ‘JT331’, ‘ADA’, ‘2012-05-01’, ‘Bandara Soekarno -
Hatta’, ‘Bandara Adi Sumarmo’, ’08:00:00’, ’09:00:00’, ‘Adam Air’, ‘40’),
(‘PE114’, ‘JT332’, ‘DAS’, ‘2012-05-01’, ‘Bandara Halim
Perdanakusuma’, ‘Bandara Ngurah Rai’, ’09:00:00’, ’11:00:00’, ‘Dirgantara Air
Service’, ‘60’);
6. Tabel Penjualan
INSERT INTO penjualan VALUES ('PJ11', '2012-05-01', 'PE112',
'C01', 'ATN', '300000', '30000', '330000'),
('PJ12', '2012-05-01', 'PE113', 'C02', 'TLS', '400000',
'40000', '440000'),
('PJ13', '2012-05-02', 'PE114', 'C03', 'HTS', '500000',
'50000', '550000');
7. Tabel Akun
INSERT INTO akun VALUES ('1001', 'Kas di Bank BNI'),
('2001', 'Hutang Pajak'),
('4001', 'Penjualan Tiket');
8.
Tabel
Jurnal
INSERT INTO jurnal VALUES ('J01', '2012-05-01', 'PE112'),
('J02', '2012-05-01', 'PE113'),
('J03', '2012-05-01', 'PE114' );
9. Tabel Jurnal_Penjualan
INSERT INTO
jurnal_penjualan VALUES ('J11', 'J01', '1001', '330000', '0'),
('J12', 'J01',
'4001', '0', '300000'),
('J13', 'J01',
'2001', '0', '30000');
MENGUBAH DATA MENJADI
NOT NULL
ALTER TABLE `pegawai_honorer` ADD PRIMARY KEY(`id_pegawai`)
Komentar
Posting Komentar