codedatabase.sql

CREATE DATABASE IF NOT EXISTS skripsi_online;
USE skripsi_online;

-- Tabel Mahasiswa
CREATE TABLE mahasiswa (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nim VARCHAR(20) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    foto_profil VARCHAR(255) DEFAULT 'default.jpg',
    jurusan VARCHAR(100),
    semester INT,
    no_hp VARCHAR(20),
    alamat TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Dosen
CREATE TABLE dosen (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nip VARCHAR(20) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    foto_profil VARCHAR(255) DEFAULT 'default.jpg',
    bidang_keahlian VARCHAR(200),
    no_hp VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Skripsi
CREATE TABLE skripsi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    mahasiswa_id INT,
    dosen_id INT,
    judul VARCHAR(255) NOT NULL,
    abstrak TEXT,
    file_skripsi VARCHAR(255),
    status ENUM('draft', 'review', 'revisi', 'acc') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (mahasiswa_id) REFERENCES mahasiswa(id) ON DELETE CASCADE,
    FOREIGN KEY (dosen_id) REFERENCES dosen(id) ON DELETE SET NULL
);

-- Tabel Review
CREATE TABLE review (
    id INT PRIMARY KEY AUTO_INCREMENT,
    skripsi_id INT,
    dosen_id INT,
    komentar TEXT NOT NULL,
    file_review VARCHAR(255),
    status ENUM('revisi', 'acc') DEFAULT 'revisi',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (skripsi_id) REFERENCES skripsi(id) ON DELETE CASCADE,
    FOREIGN KEY (dosen_id) REFERENCES dosen(id) ON DELETE CASCADE
);

-- Tabel Jadwal Sidang
CREATE TABLE jadwal_sidang (
    id INT PRIMARY KEY AUTO_INCREMENT,
    skripsi_id INT,
    tanggal DATE NOT NULL,
    waktu TIME NOT NULL,
    ruangan VARCHAR(50),
    status ENUM('terjadwal', 'selesai', 'dibatalkan') DEFAULT 'terjadwal',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (skripsi_id) REFERENCES skripsi(id) ON DELETE CASCADE
);