query.msql

<!-- 
    BUG(S) TO FIX:
    1. CANT DELETE TABLE of mahasiswa. IT'LL GIVE AN ERROR MESSAGE WHEN WE TRY TO DELETE TABLE of mahasiswa. ON DELETE CASCASE (?)
    cannot update or delete a parent row!
 -->

create database pemilu;
use pemilu;
create table mahasiswa (
    NPM varchar(12) not null,
    Nama varchar(30) not null,
    TTL varchar(30) not null,
    No_Telepon varchar(13) not null,          
    Email varchar(30) not null,
    Jurusan varchar(20) not null,
    Angkatan int(4) not null,
    Alamat varchar(50) not null,
    Foto varchar(200),                             
    Validasi boolean default false,
    PRIMARY KEY (NPM) 
    );

create table kandidat (
    NPM varchar(12) not null UNIQUE,
    NO_URUT int(2) not null UNIQUE,
    VISI varchar(6000) not null,
    MISI varchar(6000) not null,
    PRIMARY KEY (NO_URUT),
    FOREIGN KEY (NPM) REFERENCES mahasiswa(NPM)
    );

create table TPS (
    Kode_TPS varchar(5) not null,
    Lokasi varchar(30) not null,
    PRIMARY KEY (Kode_TPS)
) ;

create table AKUN (
    USERNAME varchar(12) not null,
    PASSWORD varchar(20) not null,
    ADMIN boolean default false,
    FOREIGN KEY (USERNAME) REFERENCES mahasiswa(NPM),
    PRIMARY KEY (USERNAME)
);

create table vote (
    NPM varchar(12) not null UNIQUE,
    NO_URUT int(2) not null,
    Kode_TPS varchar(5) not null,
    Waktu datetime not null,
    FOREIGN KEY (NPM) REFERENCES mahasiswa(NPM),
    FOREIGN KEY (NO_URUT) REFERENCES kandidat(NO_URUT),
    FOREIGN KEY (Kode_TPS) REFERENCES TPS(Kode_TPS)
);