TktNvjth

1DROP SCHEMA IF EXISTS studentrecord;
2CREATE SCHEMA IF NOT EXISTS studentrecord;
3Use studentrecord;
4
5CREATE TABLE COURSES (
6	ID int Primary Key auto_increment not null,
7    Name varchar(100) not null
8);
9
10CREATE TABLE TEACHERS (
11	PPS varchar(50) Primary Key not null,
12	FirstName varchar(50) not null,
13	SecondName varchar(50) not null,
14    Address varchar(300),
15	PhoneNumber varchar(50),
16    
17	DOB date
18);
19
20CREATE TABLE MODULES (
21	ModuleID int Primary Key auto_increment not null,
22	Name varchar(50) not null,
23	TeacherID varchar(50) not null,
24	CourseID int not null,
25    
26    FOREIGN KEY (CourseID) references courses(ID),
27	FOREIGN KEY (TeacherID) references teachers(PPS)
28);
29
30CREATE TABLE MOD_COURSE_JUNCTION (
31	COURSE_ID int,
32    MODULE_ID int,
33    
34    Primary Key( COURSE_ID, MODULE_ID )
35);
36
37CREATE TABLE STUDENTS (
38	PPS varchar(50) Primary Key not null,
39	FirstName varchar(50) not null,
40	SecondName varchar(50) not null,
41	ModuleID int not null,
42    Address varchar(300),
43	DOB datetime,
44	PhoneNumber varchar(50),
45    
46	FOREIGN KEY (ModuleID) references modules(ModuleID)
47);