M6uPJ6RJ

1CREATE TABLE IF NOT EXISTS student
2student_id CHAR(10),
3student_name VARCHAR(50), 
4degree_scheme VARCHAR(50), 
5PRIMARY KEY (student_id)) 
6ENGINE = InnoDB;
7
8
9CREATE TABLE IF NOT EXISTS modules
10module_id CHAR(10),
11module_name VARCHAR(50), 
12credits CHAR(6), 
13PRIMARY KEY (module_id))
14ENGINE = InnoDB;
15
16            
17CREATE TABLE IF NOT EXISTS staff
18(staff_id CHAR(10), 
19staff_name VARCHAR(50), 
20grade VARCHAR(50), 
21PRIMARY KEY (staff_id)) 
22ENGINE = InnoDB;
23
24
25CREATE TABLE IF NOT EXISTS registered
26(student_id CHAR(10),
27module_id CHAR(10),
28PRIMARY KEY (student_id, module_id), INDEX(student_id), INDEX(module_id),
29FOREIGN KEY (student_id) REFERENCES student (student_id),
30FOREIGN KEY (module_id) REFERENCES module (module_id))
31ENGINE = InnoDB;
32
33
34CREATE TABLE IF NOT EXISTS teaches
35(staff_id CHAR(10),
36module_id CHAR(10),
37PRIMARY KEY (staff_id, module_id), INDEX(staff_id), INDEX(module_id),
38FOREIGN KEY(staff_id) REFERENCES staff (staff_id),
39FOREIGN KEY(module_id) REFERENCES module (module_id))
40ENGINE = InnoDB;