Query Akademik

use Akademik
go
create table dosen
(
nidn varchar(10) primary key not null,
nama_dosen varchar(35) unique not null,
perguruan_tinggi varchar(30)not null,
program_studi varchar(50) not null,
jab_fung varchar(30) not null,
pendidikan_tertinggi varchar(3) not null,
status_ikatan_kerja varchar(30) not null,
status_aktifitas varchar(15) not null,
tgl_mulai_masuk_dosen smalldatetime null,
nomor_serdos varchar(10) not null,
tempat_lahir varchar(30) not null,
tgl_lahir smalldatetime null,
jenis_kelamin char(1) not null check(jenis_kelamin in ('L','P')),
agama varchar(15) check(agama in
('Islam','Katolik','Protestan','Hindu','Budha','Kong Hu Chu')) not null,
alamat varchar(60) not null,
)
go
create table kuliah
(
kd_mata_kuliah varchar(10) primary key not null,
nama_mata_kuliah varchar(20) not null unique,
sks int,
syarat varchar(30) not null,
keterangan varchar(30) not null,
)
go
create table jam_mengajar
(
kd_mengajar varchar(10) primary key not null,
waktu varchar(15) not null unique,
)
go
create table kelas
(
kelas varchar(10) primary key,
kode_dosen varchar(10) not null unique,
kapasitas int default 40,
jumlah int,
)
go
create table mahasiswa
(
nim varchar(10) primary key,
jenjang char(3) not null unique,
program_studi varchar(35) not null unique,
nama_mahasiswa varchar(35) not null unique,
tgl_mulai_masuk_mahasiswa smalldatetime null,
tempat_lahir varchar(30) not null,
tgl_lahir datetime null,
jenis_kelamin char(1) check(jenis_kelamin in
('L','P'))not null,
agama varchar(20) check(agama in
('Islam','Katolik','Protestan','Hindu','Budha','Kong Hu Chu','Aliran Kepercayaan')) not null,
alamat varchar(60) not null,
kelas varchar(10) foreign key references kelas(kelas),
status_aktifitas varchar(15) not null,
)
go
create table frs
(
nim varchar(10) foreign key references mahasiswa(nim)on
delete cascade on update cascade,
jenjang char(3) foreign key references mahasiswa(jenjang),
program_studi varchar(35) foreign key references mahasiswa(program_studi),
nama_mahasiswa varchar(35) foreign key references mahasiswa(nama_mahasiswa),
kd_mata_kuliah varchar(10)foreign key references kuliah(kd_mata_kuliah),
semester varchar(10) not null,
sks int,
kelas varchar(10) foreign key references kelas(kelas)on
delete cascade on update cascade,
nama_dosen varchar(35) foreign key references dosen(nama_dosen),
kode_dosen varchar(10) foreign key references kelas(kode_dosen),
nama_mata_kuliah varchar(20) foreign key references kuliah(nama_mata_kuliah),
kehadiran int,
tugas int,
uts int,
uas int,
nilai_akhir int,
nilai_huruf varchar(5) not null,
keterangan varchar(15) not null,
)
go
create table jadwal_dosen
(
kode_dosen varchar(10) foreign key references kelas(kode_dosen) on
delete cascade on update cascade,
kd_mata_kuliah varchar(10) default 5 foreign key references kuliah(kd_mata_kuliah) on delete cascade on update cascade,
hari char(10) check(hari in ('Senin','Selasa','Rabu','Kamis','Jumat','Sabtu','Minggu')),
kd_mengajar varchar(10) foreign key references jam_mengajar(kd_mengajar) on delete cascade on update cascade
)