Friday, May 23, 2008

DDL

create table peserta
(
id_peserta varchar2(6) constraint pk_peserta primary key,
nama varchar2(20) not null,
alamat varchar2(30) not null,
no_telepon varchar2(15)
);

create table kursus
(
id_kursus varchar2(6) constraint pk_kursus primary key,
nama_kursus varchar2(20) not null,
biaya varchar2(15) not null,
lama_kursus char(2) not null,
kuota char(2)
);

create table pengajar
(
id_pengajar varchar2(6) constraint pk_pengajar primary key,
nama varchar2(20) not null,
alamat varchar2(30) not null,
no_telepon varchar2(15)
);
insert into pengajar values('1','rahmat','pga','123');

create table ruang
(
id_ruang varchar2(6) constraint pk_ruang primary key,
no_ruang varchar2(5) not null
);
insert into ruang values('11','a203');

create table jadwal
(
id_jadwal varchar2(6) constraint pk_jadwal primary key,
hari varchar2(10) not null,
jam varchar2(10) not null,
id_pengajar varchar2(6),
id_ruang varchar2(6),
constraint fk1_jadwal foreign key(id_pengajar) references pengajar(id_pengajar) on delete cascade,
constraint fk2_jadwal foreign key(id_ruang) references ruang(id_ruang) on delete cascade
);
insert into jadwal values('','senin','19.00','1','11');
insert into jadwal values ('','$id_pengajar','$id_ruang','$hari','$jam')

create table mendaftar
(
id_peserta varchar2(6),
id_kursus varchar2(6),
tgl_daftar date not null,
constraint fk1_mendaftar foreign key(id_peserta) references peserta(id_peserta) on delete cascade,
constraint fk2_mendaftar foreign key(id_kursus) references kursus(id_kursus) on delete cascade,
constraint pk_mendaftar primary key(id_peserta, id_kursus)
);

create table mendapat
(
id_peserta varchar2(6),
id_kursus varchar2(6),
id_jadwal varchar2(6),
tgl_daftar date not null,
constraint fk1_mendapat foreign key(id_peserta) references peserta(id_peserta) on delete cascade,
constraint fk2_mendapat foreign key(id_kursus) references kursus(id_kursus) on delete cascade,
constraint fk3_mendapat foreign key(id_jadwal) references jadwal(id_jadwal) on delete cascade,
constraint pk_mendapat primary key(id_peserta, id_kursus, id_jadwal)
);

No comments:

Post a Comment