rySX83hu

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 RESULT (
50	StudentPPS varchar(50),
51    AssigmentID int,
52    ModuleID int,
53    Result int,
54    
55    CONSTRAINT Result0To100 CHECK (
56	   Result >= 0 AND Result <= 100
57	),
58    
59    FOREIGN KEY (StudentPPS) references student(PPS),
60    FOREIGN KEY (ModuleID) references module(ModuleID),
61    PRIMARY KEY (StudentPPS, ModuleID)
62);
63
64ALTER TABLE COURSE_MODULE
65	ADD CONSTRAINT JUNCTION_CONSTRAINT_COURSE
66	FOREIGN KEY (COURSE_ID) references COURSE( ID )
67	ON UPDATE CASCADE
68	ON DELETE CASCADE;
69    
70ALTER TABLE COURSE_MODULE
71	ADD CONSTRAINT JUNCTION_CONSTRAINT_MODULE
72	FOREIGN KEY (MODULE_ID) references MODULE( ModuleID )
73	ON UPDATE CASCADE
74	ON DELETE CASCADE