tugas basis data
DESCRIPTION
Tugas Mata Kuliah Basis data Pasca SarjanaTRANSCRIPT
Tugas Manajemen Basis Data
Analisis Dokumen Pertama
Jika Di generate database menjadi :
drop table if exists MEMBER;
Table: MEMBER
create table MEMBER( NAMALOGIN char(50) not null, NAMALENGKAP char(150), NAMAPANGGILAN char(50), PASSWORD longtext, PERUSAHAAN char(100), TELP1 char(100), TELP2 char(50), EMAIL char(100), ALAMAT text, primary key (NAMALOGIN));
6
Tugas Manajemen Basis Data
Analisis Dokumen Ke Dua
ERD / Conseptual Data Modeling Dokumen Ke DUA
Relasi / Physical Data Model / Fisik Database
7
Tugas Manajemen Basis Data
Fisik Database Dokumen Ke duaTable: DAFTARIKLAN
create table DAFTARIKLAN( IDIKLAN int not null, KD_KATEGORI int, IDKECAMATAN int, ALAMAT text, HARGAPROPERTI numeric(8,0), JUDUL char(100), KETERANGANIKLAN text, primary key (IDIKLAN));
Table: KABUPATEN
create table KABUPATEN( IDKABUPATEN int not null, NAMAKABUPATEN char(100), primary key (IDKABUPATEN));
Table: KATEGORI_IKLAN
create table KATEGORI_IKLAN( KD_KATEGORI int not null, NAMAKATEGORI char(100),
8
Tugas Manajemen Basis Data
primary key (KD_KATEGORI));
Table: KECAMATAN
create table KECAMATAN( IDKECAMATAN int not null, IDKABUPATEN int, NAMAKECAMATAN char(100), primary key (IDKECAMATAN));
Table: LAMPIRAN_IKLAN
create table LAMPIRAN_IKLAN( IDLAMPIRAN int not null, IDIKLAN int, FILE longblob, primary key (IDLAMPIRAN));
alter table DAFTARIKLAN add constraint FK_RELATIONSHIP_2 foreign key (IDKECAMATAN) references KECAMATAN (IDKECAMATAN) on delete restrict on update restrict;
alter table DAFTARIKLAN add constraint FK_RELATIONSHIP_3 foreign key (KD_KATEGORI) references KATEGORI_IKLAN (KD_KATEGORI) on delete restrict on update restrict;
alter table KECAMATAN add constraint FK_RELATIONSHIP_1 foreign key (IDKABUPATEN) references KABUPATEN (IDKABUPATEN) on delete restrict on update restrict;
alter table LAMPIRAN_IKLAN add constraint FK_RELATIONSHIP_4 foreign key (IDIKLAN) references DAFTARIKLAN (IDIKLAN) on delete restrict on update restrict;
ANALIS DOKUMEN KE TIGA
ERD /Conseptual Data Model Dokumen Ke Tiga
9
Tugas Manajemen Basis Data
Fisik Database
Table: INVOICE
create table INVOICE( NOINVOICE char(100) not null, NAMALOGIN char(50), KD_KATEGORI int, WAKTU timestamp, HARGA numeric(8,0), NAMAURAIAN char(100), primary key (NOINVOICE));
Table: KATEGORI_IKLAN
create table KATEGORI_IKLAN( KD_KATEGORI int not null, NAMAKATEGORI char(100), primary key (KD_KATEGORI));
Table: MEMBER create table MEMBER( NAMALOGIN char(50) not null, NAMALENGKAP char(150), NAMAPANGGILAN char(50), PASSWORD longtext, PERUSAHAAN char(100), TELP1 char(100), TELP2 char(50), EMAIL char(100), ALAMAT text, primary key (NAMALOGIN));
alter table INVOICE add constraint FK_RELATIONSHIP_1 foreign key (NAMALOGIN) references MEMBER (NAMALOGIN) on delete restrict on update restrict;alter table INVOICE add constraint FK_RELATIONSHIP_2 foreign key (KD_KATEGORI) references KATEGORI_IKLAN (KD_KATEGORI) on delete restrict on update restrict;
ANALISI DOKUMEN KE EMPAT
11
Tugas Manajemen Basis Data
ERD/Conseptual Data Model Dokumen Ke Empat
Relasi / Physical Data Model
12
Tugas Manajemen Basis Data
Fisik Database Dokumen Ke Empat
Table: DAFTARBANK
create table DAFTARBANK( KODEBANK int not null, NAMABANK char(500), primary key (KODEBANK));
Table: INVOICE
create table INVOICE( NOINVOICE char(100) not null,
13
Tugas Manajemen Basis Data
WAKTU timestamp, HARGA numeric(8,0), NAMAURAIAN char(100), primary key (NOINVOICE));
Table: MEMBER
create table MEMBER( NAMALOGIN char(50) not null, NAMALENGKAP char(150), NAMAPANGGILAN char(50), PASSWORD longtext, PERUSAHAAN char(100), TELP1 char(100), TELP2 char(50), EMAIL char(100), ALAMAT text, primary key (NAMALOGIN));
Table: METODETRANFER
create table METODETRANFER( IDMETODE int not null, NAMAMETODE char(100), primary key (IDMETODE));
Table: TRANSAKSIPEMBAYARAN
create table TRANSAKSIPEMBAYARAN( TGLKIRIM timestamp not null, IDMETODE int, NOINVOICE char(100), KODEBANK int, NAMALOGIN char(50), KETERANGAN text, NOREK int, NAMAPEMILIK char(100), primary key (TGLKIRIM));
14
Tugas Manajemen Basis Data
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_1 foreign key (NOINVOICE) references INVOICE (NOINVOICE) on delete restrict on update restrict;
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_2 foreign key (NAMALOGIN) references MEMBER (NAMALOGIN) on delete restrict on update restrict;
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_3 foreign key (IDMETODE) references METODETRANFER (IDMETODE) on delete restrict on update restrict;
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_4 foreign key (KODEBANK) references DAFTARBANK (KODEBANK) on delete restrict on update restrict;
Analisi GABUNGAN SEMUA DOKUMEN
ERD / Conseptual Data Model GABUNGAN SEMUA DOKUMEN
RELASI / physical Data model
15
Tugas Manajemen Basis Data
Fisik Database
Table: DAFTARBANK
create table DAFTARBANK( KODEBANK int not null, NAMABANK char(500), primary key (KODEBANK));
Table: DAFTARIKLAN
create table DAFTARIKLAN( IDIKLAN int not null,
16
Tugas Manajemen Basis Data
NAMALOGIN char(50), IDKECAMATAN int, KD_KATEGORI int, ALAMATPROPERY text, HARGAPROPERTI numeric(8,0), JUDUL char(100), KETERANGANIKLAN text, primary key (IDIKLAN));
Table: INVOICE
create table INVOICE( NOINVOICE2 char(100) not null, IDIKLAN int, WAKTU2 timestamp, HARGA2 numeric(8,0), primary key (NOINVOICE2));
Table: KABUPATEN
create table KABUPATEN( IDKABUPATEN int not null, NAMAKABUPATEN char(100), primary key (IDKABUPATEN));
Table: KATEGORI_IKLAN
create table KATEGORI_IKLAN( KD_KATEGORI int not null, NAMAKATEGORI char(100), primary key (KD_KATEGORI));
Table: KECAMATAN
create table KECAMATAN( IDKECAMATAN int not null,
17
Tugas Manajemen Basis Data
IDKABUPATEN int, NAMAKECAMATAN char(100), primary key (IDKECAMATAN));
Table: LAMPIRAN_IKLAN
create table LAMPIRAN_IKLAN( IDLAMPIRAN int not null, IDIKLAN int, FILE longblob, primary key (IDLAMPIRAN));
Table: MEMBER
create table MEMBER( NAMALOGIN char(50) not null, NAMALENGKAP char(150), NAMAPANGGILAN char(50), PASSWORD longtext, PERUSAHAAN char(100), TELP1 char(100), TELP2 char(50), EMAIL char(100), ALAMAT text, primary key (NAMALOGIN));
Table: METODETRANFER
create table METODETRANFER( IDMETODE int not null, NAMAMETODE char(100), primary key (IDMETODE));
Table: TRANSAKSIPEMBAYARAN
create table TRANSAKSIPEMBAYARAN(
18
Tugas Manajemen Basis Data
TGLKIRIM timestamp not null, IDMETODE int, NOINVOICE2 char(100), KODEBANK int, KETERANGAN text, NOREK int, NAMAPEMILIK char(100), primary key (TGLKIRIM));
alter table DAFTARIKLAN add constraint FK_RELATIONSHIP_2 foreign key (IDKECAMATAN) references KECAMATAN (IDKECAMATAN) on delete restrict on update restrict;
alter table DAFTARIKLAN add constraint FK_RELATIONSHIP_3 foreign key (KD_KATEGORI) references KATEGORI_IKLAN (KD_KATEGORI) on delete restrict on update restrict;
alter table DAFTARIKLAN add constraint FK_RELATIONSHIP_5 foreign key (NAMALOGIN) references MEMBER (NAMALOGIN) on delete restrict on update restrict;
alter table INVOICE add constraint FK_RELATIONSHIP_9 foreign key (IDIKLAN) references DAFTARIKLAN (IDIKLAN) on delete restrict on update restrict;
alter table KECAMATAN add constraint FK_RELATIONSHIP_1 foreign key (IDKABUPATEN) references KABUPATEN (IDKABUPATEN) on delete restrict on update restrict;
alter table LAMPIRAN_IKLAN add constraint FK_RELATIONSHIP_4 foreign key (IDIKLAN) references DAFTARIKLAN (IDIKLAN) on delete restrict on update restrict;
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_6 foreign key (IDMETODE) references METODETRANFER (IDMETODE) on delete restrict on update restrict;
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_7 foreign key (KODEBANK) references DAFTARBANK (KODEBANK) on delete restrict on update restrict;
alter table TRANSAKSIPEMBAYARAN add constraint FK_RELATIONSHIP_8 foreign key (NOINVOICE2) references INVOICE (NOINVOICE2) on delete restrict on update restrict;
19