DROP DATABASE asrama;
CREATE DATABASE IF NOT EXISTS asrama;
USE asrama;

CREATE TABLE IF NOT EXISTS anak (
    id INT PRIMARY KEY AUTO INCREMENT,
    nama CHAR(40) NOT NULL,
    jk CHAR(1) NOT NULL,
    umur INT NOT NULL
);

CREATE TABLE IF NOT EXISTS sekolah (
    id INT PRIMARY KEY AUTO INCREMENT,
    nama CHAR(40) NOT NULL,
    tipe CHAR(6) NOT NULL
);

CREATE TABLE IF NOT EXISTS asosiasi_sekolah (
    id INT PRIMARY KEY AUTO INCREMENT,
    id_anak INT NOT NULL,
    id_sekolah INT NOT NULL,
    tahun_mulai INT,
    FOREIGN KEY (id_anak) REFERENCES anak(id),
    FOREIGN KEY (id_sekolah) REFERENCES sekolah(id)
);