9Bgpq5ET

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