DROP SCHEMA IF EXISTS studentrecord;
CREATE SCHEMA IF NOT EXISTS studentrecord;
Use studentrecord;

CREATE TABLE COURSE (
	ID int Primary Key auto_increment not null,
    Name varchar(100) not null
);

CREATE TABLE TEACHER (
	PPS varchar(50) Primary Key not null,
	FirstName varchar(50) not null,
	SecondName varchar(50) not null,
    Address varchar(300),
	PhoneNumber varchar(50),

	DOB date
);

CREATE TABLE MODULE (
	ModuleID int Primary Key auto_increment not null,
	Name varchar(50) not null,
	TeacherID varchar(50) not null,
	CourseID int not null,

    FOREIGN KEY (CourseID) references course(ID),
	FOREIGN KEY (TeacherID) references teacher(PPS)
);

CREATE TABLE COURSE_MODULE(
	COURSE_ID int,
	MODULE_ID int,

	Primary Key( COURSE_ID, MODULE_ID )
);

CREATE TABLE STUDENT (
	PPS varchar(50) Primary Key not null,
	FirstName varchar(50) not null,
	SecondName varchar(50) not null,
	ModuleID int not null,
    Address varchar(300),
	DOB datetime,
	PhoneNumber varchar(50),

	FOREIGN KEY (ModuleID) references module(ModuleID)
);

CREATE TABLE RESULT (
	StudentPPS varchar(50),
    AssigmentID int,
    ModuleID int,
    Result int,

    CONSTRAINT Result0To100 CHECK (
	   Result >= 0 AND Result <= 100
	),

    FOREIGN KEY (StudentPPS) references student(PPS),
    FOREIGN KEY (ModuleID) references module(ModuleID),
    PRIMARY KEY (StudentPPS, ModuleID)
);

ALTER TABLE COURSE_MODULE
	ADD CONSTRAINT JUNCTION_CONSTRAINT_COURSE
	FOREIGN KEY (COURSE_ID) references COURSE( ID )
	ON UPDATE CASCADE
	ON DELETE CASCADE;

ALTER TABLE COURSE_MODULE
	ADD CONSTRAINT JUNCTION_CONSTRAINT_MODULE
	FOREIGN KEY (MODULE_ID) references MODULE( ModuleID )
	ON UPDATE CASCADE
	ON DELETE CASCADE