0CreBXma

1DROP DATABASE IF EXISTS moodle;
2CREATE DATABASE moodle;
3
4CREATE TABLE role(
5	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
6    name VARCHAR(255) NOT NULL
7);
8
9CREATE TABLE user(
10	id INT NOT NULL PRIMARY KEY,
11    firstname VARCHAR(255) NOT NULL,
12    lastname VARCHAR(255) NOT NULL,
13    role_id INT NOT NULL,
14    CONSTRAINT FOREIGN KEY (role_id) REFERENCES role(id)
15);
16
17CREATE TABLE user_ip_address(
18	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
19    user_id INT NOT NULL,
20    ip_address VARCHAR(255) NOT NULL,
21    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user(id) 
22);
23
24CREATE TABLE category(
25	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
26    name VARCHAR(255) NOT NULL
27);
28
29CREATE TABLE course(
30	id INT NOT NULL  PRIMARY KEY,
31    name VARCHAR(255) NOT NULL,
32    category_id INT NOT NULL,
33    CONSTRAINT FOREIGN	KEY (category_id) REFERENCES category(id)
34);
35
36CREATE TABLE course_module(
37	id INT NOT NULL  PRIMARY KEY,
38    resource_name VARCHAR(255) NOT NULL,
39    course_id INT NOT NULL,
40    CONSTRAINT FOREIGN	KEY (course_id) REFERENCES course(id)
41);
42
43CREATE TABLE course_enrollment(
44	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
45    user_enrolling_id INT NOT NULL,
46    user_being_enrolled_id INT NOT NULL
47    CONSTRAINT FOREIGN KEY (user_enrolling_id) REFERENCES user(id),
48    CONSTRAINT FOREIGN KEY (user_being_enrolled_id) REFERENCES user(id)
49);
50
51CREATE TABLE course_module_updated(
52	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
53    user_id INT NOT NULL,
54    course_module_id INT NOT NULL,
55    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user(id),
56    CONSTRAINT FOREIGN KEY (course_module_id) REFERENCES course_module(id)
57);