laporan basisdata 4

LAPORAN PRAKTIKUM BASIS DATA

PERTEMUAN KE 4

                                                                                                        

Nama         : Rizky Yuniawan

Nim   : 095610114

SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER

AKAKOM

YOGYAKARTA

2011

PERTEMUAN IV

TRANSAKSI

1.                  Pembahasan

Pada pelaksanaan praktikum basis data transaksi, kita harus manjalankan perintah innodb untuk mendukung transaksi, dan jika ingin memeriksa dukungan transaksi gunakan perintah ini.

mysql> show variables like 'have_innodb';

Dan jika saat diperiksa hasilnya seperti diatas, berarti dukungan telah aktif . Sebelum melakukan transaksi kita buat tabelnya dahulu

mysql> use akademik;

Database changed

mysql> create table coba_transaks(pelatihan varchar(10) NOT NULL, instruktur char(15) NOT NULL, primary key(pelatihan)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.16 sec)

Untuk memulai transaksi kita gunakan perintah :

mysql> start transaction;

Query OK, 0 rows affected (0.06 sec)

Dan coba kita masukan data ke tabel transaks

mysql> insert into coba_transaks values('MySQL','Totok'),('Oracle','Enny');

Query OK, 2 rows affected (0.34 sec)

Records: 2  Duplicates: 0  Warnings: 0

Melihat penambahan data menggunakan perintah :

mysql> select*from coba_transaks;

mysql> exit         àuntuk perintah keluar

prtk-2 + login ulang

mysql> use akademik;

Database changed

mysql> select*from coba_transaks;

Empty set

Hasilnya : tabel transaks tidak keluar karena saat akan keluar dari MySQL belum menjalankan perintah COMMIT. Perintah COMMIT digunakan untuk menyimpan data yang telah di ketik sebelumnya 0.00 sec

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

Memasukan data kedalam tabel coba_transaks :

mysql> insert into coba_transaks values('MySQL','Totok'),('Oracle','Enny');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select*from coba_transaks;

mysql> commit;

Query OK, 0 rows affected (0.09 sec)

mysql> exit

prtk-3 + login ulang

mysql> use akademik;

Database changed

mysql> select*from coba_transaks;

Selanjutnya kita akan menjalankan perintah autocommit bernilai 0, yang berarti saat kita meninsert data, data tidak tersimpan secara permanen atau hanya sementara dan saat kita exit data akan hilang.

mysql> select @@autocommit;

mysql> set @@autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into coba_transaks values('sybase','Hartono');

Query OK, 1 row affected (0.00 sec)

mysql> select*from coba_transaks;

mysql> exit

prtk-4 + login ulang

mysql> use akademik;

Database changed

mysql> select*from coba_transaks;

mysql> select @@autocommit;

mysql> set @@autocommit=1;

Query OK, 0 rows affected (0.00 sec)

Hasil autocommit bernilai 0

mysql> insert into coba_transaks values('sybase','Hartono');

Query OK, 1 row affected (0.06 sec)

mysql> select*from coba_transaks;

mysql> exit

prtk-5 + login ulang

Hal diatas terjadi karena nilai autocommit masih berupa 1atau juga dapat dibilang jika mode transaksi masih OFF atau mati.

mysql> use akademik;

Database changed

mysql> select*from coba_transaks;

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select*from coba_transaks;

mysql> insert into coba_transaks values ('sybase','Emmy');

ERROR 1062 (23000): Duplicate entry 'sybase' for key 'PRIMARY'

Karena ada kemiripan pada PRIMARY KEY-nya, maka value (‘sybase’,’Emmy’) tidak dapat diinputkan kedalam database. Hal ini terjadi karena mode transaksi autocommit masih bernilai = 1, atau masih dalam keadaaan mati. Karena itulah auto commit harus diaktivkan dengan mengubah nilainya menjadi = 0.

mysql> select @@autocommit;

mysql> set @@autocommit=0;

Query OK, 0 rows affected (0.02 sec)

Re-login

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select*from coba_transak;

mysql> insert into coba_transak values('sybase','Emmy');

Query OK, 1 row affected (0.08 sec)

mysql> select*from coba_transak;

mysql> insert into coba_transak values('sybase','Harry');

ERROR 1062 (23000): Duplicate entry 'sybase' for key 'PRIMARY'

Karena ada kemiripan pada PRIMARY KEY-nya, maka value (‘sybase’,’Harry’) tidak dapat diinputkan kedalam database.

mysql> rollback;

Query OK, 0 rows affected (0.08 sec)

Saat rollback diaktivkan, maka semua penambahan data akan dinyatakan tidak valid atau dibatalkan tergantung pada kondisi transaksinya. Perintah dibawah digunakan untuk mengecek jika perintah rollback berhasil dilaksanakan.

mysql> select*from coba_transaks;

2.      Latihan

Membuat tabel basisdata transak_jual dengan atribut kdbrg, nmbrg, jnsbrg, & harga.

mysql> create table transak_jual(kdbrg char(9)not null, nmbrg varchar(30), jnsbrg varchar(10), harga char(15), primary key(kdbrg))engine=InnoDB;

Query OK, 0 rows affected (0.38 sec)

mysql> insert into transak_jual values('a123456','kopi bubuk','minuman','1500'), ('b123456','roti sobek','makanan','5000'), ('e123456','sabun mandi','perlengkapan','1500'), ('a223456','susu kental manis sc','minuman','750'), ('b334567','kecap manis cap sendok 250ml','makanan','2500');

Query OK, 3 rows affected, 1 warning (0.20 sec)

Records: 5  Duplicates: 0  Warnings: 1

Menggunakan perintah :

è Commit

 mysql> create table transak_jual(kdbrg char(9)not null, nmbrg varchar(30), jnsbrg varchar(10), harga char(15), primary key(kdbrg))engine=InnoDB;

Query OK, 0 rows affected (0.38 sec)

 mysql> insert into transak_jual values('a123456','kopi bubuk','minuman','1500'), ('b123456','roti sobek','makanan','5000'), ('e123456','sabun mandi','perlengkapan','1500'), ('a223456','susu kental manis sc','minuman','750'), ('b334567','kecap manis cap sendok 250ml','makanan','2500');

               Query OK, 3 rows affected, 1 warning (0.20 sec)

               Records: 5  Duplicates: 0  Warnings: 1

mysql> select*from transak_jual;

mysql> commit;

Query OK, 0 rows affected (0.08 sec)

mysql> exit

setelah login ulang :

è Autocommit

mysql> use akademik;

Database changed

mysql> select*from transak_jual;

mysql> select @@autocommit;

mysql> set @@autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into transak_jual values('c123456','elpiji 3kg','bahan bakar','30000');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select*from transak_jual;

mysql> exit

setelah login ulang :

Jika autocommit = 1, maka :

mysql> select @@autocommit;

mysql> set @autocommit=1;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into transak_jual values('c23456','minyak tanah','bahan bakar','8000');

Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> select*from transak_jual;

mysql> exit

setelah login ulang :

è Rollback àimplisit

                àeksplisit

 mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into transak_jual values('a898999','air mineral','minuman','2000');

Query OK, 1 row affected (0.00 sec)

mysql> select*from transak_jual;

mysql> rollback;

Query OK, 0 rows affected (0.06 sec)

mysql> select*from transak_jual;

3.      Tugas

1.            Database bank

mysql> use bank;

Database changed

2.                     Table nasabah, saldo, & transaksi

mysql> create table nasabah(norek char(30) not null, nama char(30), alamat varchar(50),noid char(20), cabang varchar(20))engine=InnoDB;

Query OK, 0 rows affected (0.13 sec)

mysql> create table saldo(tgl char(6), sandi char(2), debet char(20), kredit char(20), pc char(2)not null , saldo char(30), ket varchar(9), primary key(pc) );

Query OK, 0 rows affected (0.11 sec)

mysql> create table transaksi(tgl char(6)not null, debet char(20),kredit char(20), saldo char(30)not null, primary key(saldo));

Query OK, 0 rows affected (0.39 sec)

3.                     Isi data table

mysql> insert into nasabah values('1001010123456121','Rizky Yuniawan','Jl Janti', '900010210001231009', 'Yogja');

Query OK, 1 row affected (0.08 sec)

mysql> insert into nasabah values('1001010123456789','Noormalisa','Jl Buntu', '900110330001231009', 'Solo');

Query OK, 1 row affected (0.34 sec)

mysql> insert into nasabah values('1001010123456666','Rifta Nor Hastuti','Jl Pelita timur', '900110330001234567', 'Bandung');

Query OK, 1 row affected (0.33 sec)

mysql> select*from nasabah;

+------------------+-------------------+-----------------+--------------------+---------+

| norek            | nama              | alamat          | noid               |cabang  |

+------------------+-------------------+-----------------+--------------------+---------+

| 1001010123456121 | Rizky Yuniawan    | Jl Janti        | 900010210001231009 |Yogja   |

| 1001010123456789 | Noormalisa        | Jl Buntu        | 900110330001231009 |Solo    |

| 1001010123456666 | Rifta Nor Hastuti | Jl Pelita Timur | 900110330001234567 |Bandung |

+------------------+-------------------+-----------------+--------------------+---------+

3 rows in set (0.00 sec)

mysql> insert into saldo values('241011','1','1,000,000.00','-','11','5,987,321.00','WDO70001121');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into saldo values('261011','1','-','1,000,000.00','12','6,987,321.00','TDO70001121');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into saldo values('131111','2','-','7,000,000.00','13','13,987,321.00','WDO70002121');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select*from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket|

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011|

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011|

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021|

+--------+-------+--------------+--------------+----+---------------+-----------+

3 rows in set (0.00 sec)

mysql> insert into transaksi values('241011','1,000,000.00','-','5,987,321.00');

Query OK, 1 row affected (0.00 sec)

mysql> insert into transaksi values('261011','-','1,000,000.00','6,987,321.00');

Query OK, 1 row affected (0.01 sec)

mysql> insert into transaksi values('131111','-','7,000,000.00','13,987,321.00')

;

Query OK, 1 row affected (0.00 sec)

mysql> select*from transaksi;

+--------+--------------+--------------+---------------+

| tgl    | debet        | kredit       | saldo         |

+--------+--------------+--------------+---------------+

| 241011 | 1,000,000.00 | -            | 5,987,321.00  |

| 261011 | -            | 1,000,000.00 | 6,987,321.00  |

| 131111 | -            | 7,000,000.00 | 13,987,321.00 |

+--------+--------------+--------------+---------------+

3 rows in set (0.00 sec)

4.                     Commit

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into saldo values('231111','2','-','2,150,000.00','14','16,137,321

.00','WDO70012121');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

mysql> select * from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket       |

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011 |

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011 |

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021 |

| 231111 | 2     | -            | 2,150,000.00 | 14 | 16,137,321.00 | WDO700121 |

+--------+-------+--------------+--------------+----+---------------+-----------+

4 rows in set (0.00 sec)

Autocommit

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

|            1 |

+--------------+

1 row in set (0.00 sec)

mysql> set @autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into saldo values('011211','3','4,000,000.00','-','15','12,137,321.00','ODO800111');

Query OK, 1 row affected (0.00 sec)

mysql> select * from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket       |

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011 |

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011 |

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021 |

| 231111 | 2     | -            | 2,150,000.00 | 14 | 16,137,321.00 | WDO700121 |

| 011211 | 3     | 4,000,000.00 | -            | 15 | 12,137,321.00 | ODO800111 |

+--------+-------+--------------+--------------+----+---------------+-----------+

5 rows in set (0.00 sec)

mysql>exit

setelah login ulang

mysql> use bank;

Database changed

mysql> select* from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket       |

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011 |

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011 |

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021 |

| 231111 | 2     | -            | 2,150,000.00 | 14 | 16,137,321.00 | WDO700121 |

+--------+-------+--------------+--------------+----+---------------+-----------+

5 rows in set (0.00 sec)

5.                     Rollback

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select* from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket       |

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011 |

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011 |

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021 |

| 231111 | 2     | -            | 2,150,000.00 | 14 | 16,137,321.00 | WDO700121 |

+--------+-------+--------------+--------------+----+---------------+-----------+

4 rows in set (0.00 sec)

mysql> insert into saldo values('011211','3','-','500,000.00','16','16,637,321.00','GDO800001');

Query OK, 1 row affected (0.00 sec)

mysql> insert into saldo values('011211','3','-','1,500,000.00','16','17,637,321.00','GDO800001');

ERROR 1062 (23000): Duplicate entry '16' for key 'PRIMARY'

Gagal memasukan data karena adanya duplikasi pada PRIMARY KEYnya

mysql> select* from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket       |

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011 |

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011 |

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021 |

| 231111 | 2     | -            | 2,150,000.00 | 14 | 16,137,321.00 | WDO700121 |

| 011211 | 3     | -            | 500,000.00   | 16 | 16,637,321.00 | GDO800001 |

+--------+-------+--------------+--------------+----+---------------+-----------+

5 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select* from saldo;

+--------+-------+--------------+--------------+----+---------------+-----------+

| tgl    | sandi | debet        | kredit       | pc | saldo         | ket       |

+--------+-------+--------------+--------------+----+---------------+-----------+

| 241011 | 1     | 1,000,000.00 | -            | 11 | 5,987,321.00  | WDO700011 |

| 261011 | 1     | -            | 1,000,000.00 | 12 | 6,987,321.00  | TDO700011 |

| 131111 | 2     | -            | 7,000,000.00 | 13 | 13,987,321.00 | WDO700021 |

| 231111 | 2     | -            | 2,150,000.00 | 14 | 16,137,321.00 | WDO700121 |

+--------+-------+--------------+--------------+----+---------------+-----------+

 5 rows in set (0.00 sec)