XVZFWM5u

1DROP SCHEMA IF EXISTS studentrecord;
2CREATE SCHEMA IF NOT EXISTS studentrecord;
3USE studentrecord;
4 
5CREATE TABLE COURSE (
6    ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
7    Name VARCHAR(100) NOT NULL
8);
9 
10CREATE TABLE TEACHER (
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 MODULE (
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 course(ID),
27    FOREIGN KEY (TeacherID) REFERENCES teacher(PPS)
28);
29 
30CREATE TABLE COURSE_MODULE(
31    COURSE_ID INT,
32    MODULE_ID INT,
33    
34    PRIMARY KEY( COURSE_ID, MODULE_ID )
35);
36 
37CREATE TABLE STUDENT (
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 module( ModuleID )
47);
48
49CREATE TABLE ASSESSMENT (
50	ModuleID INT NOT NULL,
51    CourseID INT NOT NULL,
52    Name VARCHAR(250) NOT NULL,
53    
54    PRIMARY KEY( ModuleID, CourseID ),
55    FOREIGN KEY ( ModuleID ) REFERENCES module( ModuleID ),
56    FOREIGN KEY ( CourseID ) REFERENCES course( ID )
57);
58
59CREATE TABLE RESULT (
60    StudentPPS VARCHAR(50),
61    AssigmentID INT,
62    ModuleID INT,
63    Result INT,
64   
65    CONSTRAINT Result0To100 CHECK (
66       Result >= 0 AND Result <= 100
67    ),
68   
69    FOREIGN KEY (StudentPPS) REFERENCES student(PPS),
70    FOREIGN KEY (ModuleID) REFERENCES module(ModuleID),
71    PRIMARY KEY (StudentPPS, ModuleID)
72);
73 
74ALTER TABLE COURSE_MODULE
75    ADD CONSTRAINT JUNCTION_CONSTRAINT_COURSE
76    FOREIGN KEY (COURSE_ID) REFERENCES COURSE( ID )
77    ON UPDATE CASCADE
78    ON DELETE CASCADE;
79   
80ALTER TABLE COURSE_MODULE
81    ADD CONSTRAINT JUNCTION_CONSTRAINT_MODULE
82    FOREIGN KEY (MODULE_ID) REFERENCES MODULE( ModuleID )
83    ON UPDATE CASCADE
84    ON DELETE CASCADE