sql

USE class_diagram;

DROP TABLE IF EXISTS progress;
DROP TABLE IF EXISTS module;
DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS student;

CREATE TABLE student (
    student_id INT(11) NOT NULL,
    name VARCHAR(45),
    email VARCHAR(45),
    PRIMARY KEY(student_id)
);

CREATE TABLE teacher (
    teacher_id INT(11) NOT NULL,
    name VARCHAR(45),
    email VARCHAR(45),
    PRIMARY KEY(teacher_id)
);

CREATE TABLE module (
    module_id INT(11) NOT NULL,
    name VARCHAR(45),
    description VARCHAR(252),
    course VARCHAR(45),
    PRIMARY KEY(module_id)
);

CREATE TABLE progress (
    progress_id INT(11) NOT NULL,
    delivered DATE,
    reviewed DATE,
    approved BOOLEAN,
    comment VARCHAR(252),
    s_id INT(11),
    t_id INT(11),
    m_id INT(11),
    PRIMARY KEY(progress_id),
    FOREIGN KEY(s_id)REFERENCES student(student_id),
    FOREIGN KEY(t_id)REFERENCES teacher(teacher_id),
    FOREIGN KEY(m_id)REFERENCES module(module_id)
);

CREATE TABLE uploadFiles (
    -- comments
    -- id
    -- delivery date
    -- file name

    -- foreign key to progress

);


-- -----------------------------------------------------
-- Data for table `student`
-- -----------------------------------------------------
INSERT INTO `student`
(`student_id`, `name`, `email`)
VALUES
(0001, `Student Studentson`, `student@gmail.com`),
(0002, `Even Nilsen`, `even@gmail.com`),
(0003, `Jorgen Gramstad`, `jorgen@gmail.com`),
(0004, `Jonas Dam`, `jonas@gmail.com`),
(0005, `Simen Fuglestad`, `simen@gmail.com`);

-- -----------------------------------------------------
-- Data for table `teacher`
-- -----------------------------------------------------
INSERT INTO `teacher`
(`teacher_id`, `name`, `email`)
VALUES
(0001, `Teacher Teacherson `, `teacher@gmail.com`),
(0002, `Doc Brown`, `brown@gmail.com`),
(0003, `Benjamin Kloster`, `kloster@gmail.com`),
(0004, `Coffee Machinicus`, `coffee@gmail.com`);

-- -----------------------------------------------------
-- Data for table `module`
-- -----------------------------------------------------
INSERT INTO `module`
(`student_id`, `name`, `email`)
VALUES
(0001, `module1`, `BlueJ Introduction`, `is109`),
(0002, `module2`, `Class Definition`, `is109`),
(0003, `module3`, `Class Definition 2`, `is109`),
(0004, `module4`, `Objects and Loops`, `is109`),
(0005, `module5`, `Designing Classes`, `is109`),
(0006, `module6`, `Documentation`, `is110`),
(0007, `module7`, `Designing Classes 2`, `is110`),
(0008, `module8`, `Testing and Debugging`, `is110`),
(0009, `module9`, `Inheritance`, `is110`),
(0010, `module10`, `Inheritance 2`, `is110`),
(0011, `module11`, `Abstract Classes and Interfaces`, `is110`),
(0012, `module11`, `GUI`, `is110`),
(0013, `module13`, `Errorhandling`, `is110`),
(0014, `module14`, `Programmingproject`, `is110`);

-- Obligatory
-- Prerequisites

-- -----------------------------------------------------
-- Data for table `progress`
-- -----------------------------------------------------
INSERT INTO `progress`
(`student_id`, `name`, `email`)
VALUES
(0001, `Teacher Teacherson `, `teacher@gmail.com`),
(0002, `Doc Brown`, `brown@gmail.com`),
(0003, `Benjamin Kloster`, `kloster@gmail.com`),
(0004, `Coffee Machinicus`, `coffee@gmail.com`);

/*
CREATE TABLE progress (
    progress_id INT(11) NOT NULL,
    delivered DATE,
    reviewed DATE,
    approved BOOLEAN,
    comment VARCHAR(252),
    s_id INT(11),
    t_id INT(11),
    m_id INT(11),
    PRIMARY KEY(progress_id),
    FOREIGN KEY(s_id)REFERENCES student(student_id),
    FOREIGN KEY(t_id)REFERENCES teacher(teacher_id),
    FOREIGN KEY(m_id)REFERENCES module(module_id)
);

*/