Cara membuat database penerbangan di mysql
1.
Buka msql enter
2.
create database penerbangan; enter
3.
use penerbangan; enter(untuk masuk ke database penerbangan)
4.
untuk membuat tabel pesawat
create table pesawat
(kode_pesawat
varchar(5) primary key,
nama_pesawat
varchar(15),
kapasitas
varchar(7));
5.
untuk melihat tabel kita desc pesawat;
mysql> desc pesawat;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| kode_pesawat | varchar(5) | NO | PRI | NULL | |
| nama_pesawat | varchar(15) | YES | | NULL | |
| kapasitas | varchar(7) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4.selanjutnya kita akan mengisi tabel
insert into pesawat values
('p01','garuda01','500'),
('p02','garuda02','200'),
('p03','garuda03','140');
7.Untuk melihat isi tabel
select * from pesawat;
mysql> select * from pesawat;
+--------------+--------------+-----------+
| kode_pesawat | nama_pesawat | kapasitas |
+--------------+--------------+-----------+
| p01 | garuda01 | 500 |
| p02 | garuda02 | 200 |
| p03 | garuda03 | 140 |
+--------------+--------------+-----------+
3 rows in set (0.00 sec)
8. membuat tabel jadwal
create table jadwal
(kode_jadwal
varchar(5) primary key,
tanggal
Date,
waktu
Time,
kode_pesawat
varchar(5));
9. melihat tabel jadwal desc jadwal;
mysql> desc jadwal;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| kode_jadwal | varchar(5) | NO | PRI | NULL | |
| tanggal | date | YES | | NULL | |
| waktu | time | YES | | NULL | |
| kode_pesawat | varchar(5) | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
10.
selanjutnya kita akan mengisi tabel jadwal
insert into jadwal values
('Jp','2010-06-15','09:00:00','P01'),
('JKp','2010-07-17','10:00:00','P02'),
('Jkj','2010-08-18','11:00:00','P03');
11.select * from jadwal;(melihat isi tabel)
mysql> select * from jadwal;
+-------------+------------+----------+--------------+
| kode_jadwal | tanggal | waktu | kode_pesawat |
+-------------+------------+----------+--------------+
| JJ | 2010-06-15 | 09:00:00 | P01 |
| JK | 2010-07-17 | 10:00:00 | P02 |
| JL | 2010-08-18 | 11:00:00 | P03 |
+-------------+------------+----------+--------------+
3 rows in set (0.02 sec)
12.membuat tabel rute
create table rute
(Kode_Rute
varchar(5) primary key,
Tujuan
varchar(25),
Kedatangan
varchar(25));
13.desc rute;(u/melihat tabel rute)
mysql> desc rute;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Kode_Rute | varchar(5) | NO | PRI | NULL | |
| Tujuan | varchar(25) | YES | | NULL | |
| Kedatangan | varchar(25) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
14.mengisi tabel rute
insert into rute values
('R01','Samarinda-Bandung','Bandung-Samarinda'),
('R02','Samarinda–Jakarta','Jakarta-Samarinda'),
('R03','Samarinda-Batam','Batam-Samarinda');
15. select * from rute; (untuk melihat isi tabel)
mysql> select * from rute;
+-----------+-------------------+-------------------+
| Kode_Rute | Tujuan | Kedatangan |
+-----------+-------------------+-------------------+
| R01 | Samarinda-Bandung | Bandung-Samarinda |
| R02 | Samarinda-Jakarta | Jakarta-Samarinda |
| R03 | Samarinda-Batam | Batam-Samarinda |
+-----------+-------------------+-------------------+
3 rows in set (0.00 sec)
16.membuat tabel rate
create table rate
(Kode_Rate
varchar(5) primary key,
Harga
varchar(10),
Kode_Rute
varchar(5),
Kode_jadwal
varchar(5));
18.mysql> desc rate;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Kode_Rate | varchar(5) | NO | PRI | NULL | |
| Harga | varchar(10) | YES | | NULL | |
| Kode_Rute | varchar(5) | YES | | NULL | |
| Kode_jadwal | varchar(5) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
19.mengisi tabel rate
insert into rate values
('T1','Rp.1.000.000','R01','JJ'),
('T2','Rp.850.000','R02','JK'),
('T3','Rp.800.000','R03','JL');
mysql> select * from rate;
+-----------+------------+-----------+-------------+
| Kode_Rate | Harga | Kode_Rute | Kode_jadwal |
+-----------+------------+-----------+-------------+
| T1 | Rp.1.000.0 | R01 | JJ |
| T2 | Rp.850.000 | R02 | JK |
| T3 | Rp.800.000 | R03 | JL |
+-----------+------------+-----------+-------------+
3 rows in set (0.00 sec)
21.membuat tabel penerbangan
create table penerbangan
(Kode_Penerbangan
varchar(5) primary key,
Kode_Rute
varchar(5),
Kode_jadwal
varchar(5),
Kode_Pilot
varchar(5),
Kode_pewasat
varchar(5));
mysql> desc penerbangan;
+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| Kode_Penerbangan | varchar(5) | NO | PRI | NULL | |
| Kode_Rute | varchar(5) | YES | | NULL | |
| Kode_jadwal | varchar(5) | YES | | NULL | |
| Kode_Pilot | varchar(5) | YES | | NULL | |
| Kode_pewasat | varchar(5) | YES | | NULL | |
+------------------+------------+------+-----+---------+-------+
5 rows in set (0.08 sec)
22.mengisi tabel penerbangan
insert into penerbangan values
('K1','R01','JJ','B1','P01'),
('K2','R02','JK','B2','P02'),
('K3','R03','JL','B3','P03');
mysql> select * from penerbangan;
+------------------+-----------+-------------+------------+--------------+
| Kode_Penerbangan | Kode_Rute | Kode_jadwal | Kode_Pilot | Kode_pesawat |
+------------------+-----------+-------------+------------+--------------+
| K1 | R01 | JJ | B1 | P01 |
| K2 | R02 | JK | B2 | P02 |
| K3 | R03 | JL | B3 | P03 |
+------------------+-----------+-------------+------------+--------------+
3 rows in set (0.00 sec)
23.membuat tabel pilot
create table pilot
(Kode_Pilot
varchar(5) primary key,
Nama
varchar(15),
Alamat
varchar(15));
mysql> desc pilot;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Kode_Pilot | varchar(5) | NO | PRI | NULL | |
| Nama | varchar(15) | YES | | NULL | |
| Alamat | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
24.mengisi tabel pilot
insert into pilot values
('B1','ADI','JAKARTA'),
('B2','ADE','Samarinda'),
('B3','FENDI','BATAM');
mysql> select * from pilot;
+------------+-------+-----------+
| Kode_Pilot | Nama | Alamat |
+------------+-------+-----------+
| B1 | ADI | JAKARTA |
| B2 | ADE | Samarinda |
| B3 | FENDI | BATAM |
+------------+-------+-----------+
3 rows in set (0.00 sec)
25.membuat tabel penumpang
create table penumpang
(Nama
varchar(10),
Alamat
varchar(10),
Kode_Rute varchar(5),
Kode_Penerbangan
varchar(5));
mysql> desc penumpang;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Nama | varchar(10) | YES | | NULL | |
| Alamat | varchar(10) | YES | | NULL | |
| Kode_Rute | varchar(5) | YES | | NULL | |
| Kode_Penerbangan | varchar(5) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
4 rows in set (0.06 sec)
26. mengisi tabel penumpang
insert into penumpang values
('Cici','Bandung','K1'),
('Yati','Batam','K2'),
('Elvi','samarinda','R03','K3');
mysql> select * from penumpang;
+------+-----------+------------------+
| Nama | Alamat | Kode_Penerbangan |
+------+-----------+------------------+
| Cici | Bandung | K1 |
| Yati | Batam | K2 |
| Elvi | samarinda | K3 |
+------+-----------+------------------+
3 rows in set (0.00 sec)
6.manggabungkan
a)penerbangan dan penumpang
select * from penerbangan natural join penumpang;
mysql> select * from penerbangan natural join penumpang;
+------------------+-----------+-------------+------------+--------------+------+-----------+
| Kode_Penerbangan | Kode_Rute | Kode_jadwal | Kode_Pilot | Kode_pesawat | Nama | Alamat |
+------------------+-----------+-------------+------------+--------------+------+-----------+
| K1 | R01 | JJ | B1 | P01 | Cici | Bandung |
| K2 | R02 | JK | B2 | P02 | Yati | Batam |
| K3 | R03 | JL | B3 | P03 | Elvi | samarinda |
+------------------+-----------+-------------+------------+--------------+------+-----------+
3 rows in set (0.01 sec)
b)select * from penerbangan natural join pilot;
mysql> select * from penerbangan natural join pilot;
+------------+------------------+-----------+-------------+--------------+-------+-----------+
| Kode_Pilot | Kode_Penerbangan | Kode_Rute | Kode_jadwal | Kode_pesawat | Nama | Alamat |
+------------+------------------+-----------+-------------+--------------+-------+-----------+
| B1 | K1 | R01 | JJ | P01 | ADI | JAKARTA |
| B2 | K2 | R02 | JK | P02 | ADE | Samarinda |
| B3 | K3 | R03 | JL | P03 | FENDI | BATAM |
+------------+------------------+-----------+-------------+--------------+-------+-----------+
3 rows in set (0.02 sec)
c)select * from jadwal natural join pesawat;
mysql> select * from jadwal natural join pesawat;
+--------------+-------------+------------+----------+--------------+-----------+
| kode_pesawat | kode_jadwal | tanggal | waktu | nama_pesawat | kapasitas |
+--------------+-------------+------------+----------+--------------+-----------+
| P01 | JJ | 2010-06-15 | 09:00:00 | garuda01 | 500 |
| P02 | JK | 2010-07-17 | 10:00:00 | garuda02 | 200 |
| P03 | JL | 2010-08-18 | 11:00:00 | garuda03 | 140 |
+--------------+-------------+------------+----------+--------------+-----------+
3 rows in set (0.00 sec)