H6g4YA8L

1/*DROP DATABASE IF EXISTS db2*/
2CREATE DATABASE IF NOT EXISTS db2;
3USE db2;
4CREATE TABLE IF NOT EXISTS subjects
5(
6	subject_id INT NOT NULL PRIMARY KEY,
7    `name` VARCHAR(50)
8); 
9
10CREATE TABLE IF NOT EXISTS majors
11(
12	major_id INT NOT NULL PRIMARY KEY,
13    `name`  VARCHAR(50)
14);
15CREATE TABLE IF NOT EXISTS students
16(
17	student_id INT NOT NULL PRIMARY KEY,
18    student_number  VARCHAR(12),
19    student_name  VARCHAR(50),
20    major_id INT NOT NULL,
21    FOREIGN KEY (major_id) REFERENCES majors(major_id)
22); 
23
24CREATE TABLE IF NOT EXISTS payments
25(
26	payment_id INT NOT NULL PRIMARY KEY,
27    payment_date  DATE,
28    payment_amount DECIMAL(8,2),
29    student_id INT NOT NULL,
30    FOREIGN KEY (student_id) REFERENCES students(student_id)
31);
32
33CREATE TABLE IF NOT EXISTS agenda
34(
35	student_id INT NOT NULL,
36    subject_id INT NOT NULL,
37    FOREIGN KEY (student_id) REFERENCES students(student_id),
38    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id),
39    PRIMARY KEY(student_id,subject_id)
40);