UTS SIMBADA

UTS SISTEM BASIS DATA

Mengenai :

        1.  Flowchart tiket kereta api

        2.  ERD tiket kereta api

        3.  Sql mengenai tiket kereta api

 

Flowchart tiket kereta api

ERD tiket kereta api

 

Sql tiket kereta api

--Membuat tabel petugas

create table petugas(

                id_petugas number(10) not null,

                nama_p varchar2(20) not null,

                constraint pk_id_p primary key (id_petugas)

);

--Membuat tabel kereta

create table kereta(

                id_kereta number(10) not null,

                nama_k varchar2(20) not null,

                rute varchar(20) not null,

                constraint pk_id_k primary key (id_kereta)

);

--Membuat tabel gerbong

create table gerbong(

                id_gerbong number(10) not null,

                kelas varchar2(10) not null,

                jml_kursi number not null,

                constraint pk_id_g primary key (id_gerbong)

);

--Membuat tabel tiket

create table tiket(

                id_tiket number(10) not null,

                id_petugas number(10) not null,

                id_kereta number(10) not null,

                id_gerbong number(10) not null,

                nama varchar2(20) not null,

                tujuan varchar(20) not null,

                tanggal date,

                harga number,

                constraint pk_id_ii primary key (id_tiket),

                constraint fk_id_pi foreign key (id_petugas) references petugas (id_petugas),

                constraint fk_id_ki foreign key (id_kereta) references kereta (id_kereta),

                constraint fk_id_gi foreign key (id_gerbong) references gerbong (id_gerbong)

);

--Membuat sequence untuk id_petugas, id_kereta, id_gerbong, dan id_tiket

create sequence id_p increment by 1;

create sequence id_k increment by 1;

create sequence id_g increment by 1;

create sequence id_t increment by 1;

--Mengisi tabel petugas

insert into petugas values (id_p.nextval,'Santoso');

insert into petugas values (id_p.nextval,'Zaki');

insert into petugas values (id_p.nextval,'Beni');

--Mengisi tabel kereta

insert into kereta values (id_k.nextval,'Argotuit','BDG-JKT');

insert into kereta values (id_k.nextval,'Argotoet','JKT-BDG');

insert into kereta values (id_k.nextval,'Argotret','BGR-BDG');

--Mengisi tabel gerbong

insert into gerbong values (id_g.nextval,'Ekonomi',50);

insert into gerbong values (id_g.nextval,'AC',30);

insert into gerbong values (id_g.nextval,'Eksekutive',10);

--Mengisi tabel tiket

insert into tiket values (id_t.nextval,2,1,1,'Tono','JKT',to_date('10-jul-14','dd-mon-yy'),60000);

insert into tiket values (id_t.nextval,1,2,3,'Biri','BDG',to_date('01-jul-14','dd-mon-yy'),80000);

insert into tiket values (id_t.nextval,3,3,2,'Putera','BDG',to_date('05-jul-14','dd-mon-yy'),100000);

--Membuat trigger untuk mengurangi jumlah kursi pada tiap gerbong

create or replace trigger kurangi_kursi

        before insert on tiket for each row

begin

        if inserting then

                update gerbong set jml_kursi = jml_kursi - 1 where id_gerbong= :new.id_gerbong;

        end if;

end;

/

Referensi :
 - DIKTAT KULIAH HANDOUT Budi Laksono Putro, S.Si, MT dosen Ilmu komputer UPI
 - SISTEM INFORMASI PELAYANAN TIKET KERETA API DENGAN JSP pada halaman

    http://repo.eepis-its.edu/567/1/926.pdf