webproject.mysql

create database webproject;
use webproject;
CREATE TABLE Users (
  user_id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(80) NOT NULL UNIQUE,
  password CHAR(41) NOT NULL,
  PRIMARY KEY (user_id)
);

CREATE TABLE Courses (
	course_id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(80) NOT NULL,
	course_type ENUM ('Z', 'I'),
	user_id INT,
	PRIMARY KEY (course_id),
	FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE Lectures (
    lecture_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL,
    course_id INT,
    PRIMARY KEY (lecture_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE LecturePortions (
   lecture_portion_id INT NOT NULL AUTO_INCREMENT,
   date DATETIME,
   lecture_id INT,
   PRIMARY KEY (lecture_portion_id),
   FOREIGN KEY (lecture_id) REFERENCES Lectures(lecture_id)
);

CREATE TABLE Students (
   student_id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(80) NOT NULL,
   lastname VARCHAR(80),
   PRIMARY KEY (student_id)
);

CREATE TABLE Students_LecturePortions_Junction (
   student_id INT NOT NULL,
   lecture_portion_id INT NOT NULL,
   PRIMARY KEY (student_id, lecture_portion_id),
   FOREIGN KEY (student_id) REFERENCES Students(student_id),
   FOREIGN KEY (lecture_portion_id) REFERENCES LecturePortions(lecture_portion_id)
);