Selasa, 15 Maret 2011

membuat database e-learning

ini contoh untuk membuat tabel dan menambahkan constrain primary key, foreign key, atau unique di sql . . .

database e-learning .

1. table dosen
==> create table dosen(id_dosen integer, NIP varchar2(10), nama varchar2(30), email varchar2(30), user_id varchar2(10),
password varchar2(10), constraint pk_id_dosen primary key(id_dosen), constraint uk_nip unique(nip)) ;

2. tabel mata_kuliah
==> create table mata_kuliah (id_mk integer, kode_mk varchar2(5), NIP varchar2(10), nama_mk varchar2(20), sks integer, constraint pk_id_mk primary key (id_mk),
constraint fk_nip foreign key(nip) references dosen (nip), constraint uk_kode_mk unique(kode_mk));

3. tabel mahasiswa
==> create table mahasiswa (id_mhs integer, nim varchar2(10), nama varchar2(30), email varchar2(30), user_id varchar2(10),
password varchar2(10), constraint pk_id_mhs primary key(id_mhs), constraint uk_nim unique(nim));

4. tabel materi
==> create table materi (id_materi integer, kode_mk varchar2(5), judul varchar2(20), deskripsi_materi varchar2(300), constraint pk_id_materi primary key(id_materi),
constraint fk_kode_mk foreign key(kode_mk) references mata_kuliah(kode_mk));

5. tabel soal
==> create table soal (id_soal integer, kode_mk varchar2(5), judul varchar2(20), soal varchar2(300), constraint pk_id_soal primary key(id_soal),
constraint fk_kode_mk2 foreign key(kode_mk) references mata_kuliah(kode_mk));

6. table nilai
==> create table nilai (id_nilai integer, kode_mk varchar2(5), nim varchar2(10), nilai integer,  constraint pk_id_nilai primary key(id_nilai),
constraint fk_nim foreign key(nim) references mahasiswa(nim));

7. table jawab
==> create table jawab (id_jawab integer, id_soal integer, nim varchar2(10), jawaban varchar2(300), tgl_kirim date, constraint pk_id_jawab primary key(id_jawab),
constraint fk_id_soal foreign key(id_soal) references soal(id_soal), constraint fk_nim2 foreign key(nim) references mahasiswa(nim));




jika di create table belum dimasukkan constraint, maka . . .


-  alter table dosen add constraint pk_id_dosen primary key(id_dosen);
-  alter table mata_kuliah add constraint pk_id_mk primary key(id_mk);
-  alter table mahasiswa add constraint pk_id_mhs primary key(id_mhs);
-  alter table materi add constraint pk_id_materi primary key(id_materi);
-  alter table soal add constraint pk_id_soal primary key(id_soal);
-  alter table nilai add constraint pk_id_nilai primary key(id_nilai);
-  alter table jawab add constraint pk_id_jawab primary key(id_jawab);

-  alter table dosen add constraint uk_nip unique(nip);
-  alter table mata_kuliah add constraint fk_nip foreign key(nip) references dosen (nip);
-  alter table mata_kuliah add constraint uk_kode_mk unique(kode_mk);
-  alter table mahasiswa add constraint uk_nim unique(nim);
-  alter table materi add constraint fk_kode_mk foreign key(kode_mk) references mata_kuliah(kode_mk);
-  alter table nilai add constraint fk_nim foreign key(nim) references mahasiswa(nim);
-  alter table soal add constraint fk_kode_mk2 foreign key(kode_mk) references mata_kuliah(kode_mk);
-  alter table jawab add constraint fk_id_soal foreign key(id_soal) references soal(id_soal);
-  alter table jawab add constraint fk_nim2 foreign key(nim) references mahasiswa(nim);

Tidak ada komentar:

Posting Komentar