create database perizinan;
use perizinan;

-- membuat tabel siswa
create table siswa(
nis varchar(10) not null,
nama varchar(50)  not null,
jk enum("L","P") not null,
no_hp varchar(13),
    primary key(nis)
);

-- membuat tabel guru
create table guru(
    nip varchar(10) not null,
    nama varchar(50) not null,
        primary key(nip)
);

-- membuat tabel izin
create table izin(
    no_surat int(4) not null,
    nis varchar(10) not null,
    nip varchar(10) not null,
    tgl_izin date not null,
    tgl_kembali date not null,
    jenis char(1) not null,
    alasan varchar(100) not null,
        primary key(no_surat),
        foreign key(nis) references siswa(nis),
        foreign key(nip) references guru(nip)
);

-- membaut tabel kelas
create table kelas(
    id_kelas int(2) not null,
    nama_kelas varchar(7) not null,
    primary key(id_kelas)
);

create table link_kelas(
    id_link_kelas int(5) not null,
    nis varchar(10) not null,
    id_kelas int(2) not null,
        primary key(id_link_kelas),
        foreign key(nis) references siswa(nis),
        foreign key(id_kelas) references kelas(id_kelas)
);

-- masukkan data siswa
insert into siswa values 
("SW20260001","Joko Santoso","L","082139855931"),
("SW20260002","Eva Rahayu","P","082139855932"),
("SW20260003","Sugeng Widodo","L","082139855933"),
("SW20260004","Rehana","P","082139855934"),
("SW20260005","Dita Leni Ravia","P","082139855935");

-- masukkan data guru
insert into guru values
("GR20260001","Sanubari"),
("GR20260002","Roro Kidul"),
("GR20260003","Rizal Munir"),
("GR20260004","Pambudi"),
("GR20260005","Rahayuningsih");

-- masukkan data kelas
insert into kelas values
("1","10 PPLG"),
("2","10 LPS"),
("3","11 RPL"),
("4","11 PBS"),
("5","12 RPL"),
("6","12 PBS");

-- masukkan data link_kelas
insert into link_kelas values
("1","SW20260001","1"),
("2","SW20260002","1"),
("3","SW20260003","2"),
("4","SW20260004","2"),
("5","SW20260005","3");

-- masukkan data perizinan
insert into izin values
("0001","SW20260001","GR20260001","2026/07/27","2026/07/28","I","Latihan Paskibraka"),
("0002","SW20260003","GR20260001","2026/07/27","2026/07/28","S","Sakit magh"),
("0003","SW20260005","GR20260002","2026/07/28","2026/07/31","S","Sakit panas"),
("0004","SW20260001","GR20260002","2026/07/28","2026/07/31","I","Latihan Paskibraka"),
("0005","SW20260001","GR20260003","2026/07/29","2026/08/02","S","Sakit, keseleo pas latihan paskibraka"),
("0006","SW20260002","GR20260003","2026/07/29","2026/07/30","I","Acara pernikahan saudara");