use Student_Portal;

create table Courses
(	CourseID	varchar(4) not null,
	CourseName	varchar(255) not null,
	CourseDescription text not null,
	PRIMARY KEY (CourseID));

create table Students
(	StudentID	int(11)	AUTO_INCREMENT,
	StudentPass		char(32) not null,
	StudentFirstName	varchar(255)	not null,
	StudentLastName		varchar(255)	not null,
	StudentDOB	date	not null,
	StudentAddress	text not null,
	StudentEmail	varchar(255),
	StudentPhone	double,
	StudentDOA	date not null,
	CourseID	varchar(4) not null,
	PRIMARY KEY (StudentID),
	FOREIGN KEY (CourseID) REFERENCES Courses (CourseID));

create table Lecturers
(	LecturerID varchar (255) not null,
	LecturerName varchar(255) not null,
	LecturerEmail varchar(255) not null,
	LecturerPhone double not null,
	PRIMARY KEY (LecturerID));
	
create table Modules
(	ModuleID varchar(8) not null,
	ModuleName varchar(255) not null,
	ModuleDescription text not null,
	ModuleCredit double not null,
	LecturerID varchar(255) not null,
	CourseID varchar(4) not null,
	PRIMARY KEY (ModuleID),
	FOREIGN KEY (CourseID) REFERENCES Courses (CourseID),
	FOREIGN KEY (LecturerID) REFERENCES Lecturers (LecturerID));

create table Grades
(	StudentID int(11) not null,
	ModuleID varchar(8) not null,
	Grade double,
	GradeDate date,
	CONSTRAINT GRAD_PK PRIMARY KEY (StudentID,ModuleID),
	FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
	FOREIGN KEY (ModuleID) REFERENCES Modules (ModuleID));
	
create table Administrator
(	AdminID varchar(50) not null,
	AdminPass char(32),
	PRIMARY KEY (AdminID));