GUaecqWp

1DROP TABLE IF EXISTS TEACHER_X_STUDENT;
2DROP TABLE IF EXISTS USER_X_SUBJECT;
3DROP TABLE IF EXISTS PULPIT_X_DEPARTMENT;
4DROP TABLE IF EXISTS SUBJECT;
5DROP TABLE IF EXISTS "USER";
6DROP TABLE IF EXISTS "GROUP";
7DROP TABLE IF EXISTS SPECIALIZATION;
8DROP TABLE IF EXISTS DEPARTMENT;
9DROP TABLE IF EXISTS EDUCATIONAL_STRUCTURE;
10
11
12DROP DATABASE IF EXISTS TEST;
13CREATE DATABASE TEST;
14
15
16CREATE TABLE EDUCATIONAL_STRUCTURE (
17  structure_id SERIAL PRIMARY KEY,
18  structure_nm VARCHAR(100),
19  foundation_dt DATE,
20  pulpit_flg BOOLEAN NOT NULL DEFAULT FALSE
21);
22
23CREATE TABLE DEPARTMENT (
24  department_id SERIAL PRIMARY KEY,
25  school_structure_id INT REFERENCES EDUCATIONAL_STRUCTURE (structure_id),
26  department_nm VARCHAR(200),
27  foundation_dt DATE NOT NULL
28);
29
30CREATE TABLE SPECIALIZATION (
31  specialization_id INT PRIMARY KEY,
32  specialization_nm VARCHAR(200) NOT NULL
33);
34
35CREATE TABLE "GROUP"(
36  group_id SERIAL PRIMARY KEY,
37  department_id INT REFERENCES DEPARTMENT (department_id) NOT NULL,
38  specialization_id INT REFERENCES SPECIALIZATION (specialization_id) NOT NULL
39);
40
41CREATE TABLE "USER" (
42  user_id SERIAL PRIMARY KEY,
43  group_id INT REFERENCES "GROUP"(group_id),
44  first_nm VARCHAR(50) NOT NULL,
45  middle_nm VARCHAR(50),
46  last_nm VARCHAR(50) NOT NULL,
47  birdth_dt DATE NOT NULL,
48  pulpit_structure_id INT REFERENCES EDUCATIONAL_STRUCTURE (structure_id),
49  city_nm VARCHAR(50) NOT NULL,
50  phone_no VARCHAR(12) CHECK (phone_no ~ '(^\+7[0-9]{10}$)?'),
51  gender_code VARCHAR(1) NOT NULL,
52  income_amt FLOAT CHECK (income_amt >= 0) DEFAULT 0.0,
53  teacher_flg BOOLEAN NOT NULL
54);
55
56CREATE TABLE SUBJECT (
57  subject_id SERIAL PRIMARY KEY,
58  pulpit_structure_id INT REFERENCES EDUCATIONAL_STRUCTURE (structure_id) NOT NULL,
59  subject_nm VARCHAR(100) NOT NULL
60);
61
62CREATE TABLE PULPIT_X_DEPARTMENT (
63  pulpit_structure_id INT REFERENCES EDUCATIONAL_STRUCTURE (structure_id) NOT NULL,
64  department_id INT REFERENCES DEPARTMENT (department_id) NOT NULL
65);
66
67CREATE TABLE USER_X_SUBJECT (
68  user_id INT REFERENCES "USER" (user_id) NOT NULL,
69  subject_id INT REFERENCES SUBJECT (subject_id) NOT NULL,
70  lection_flg BOOLEAN NOT NULL DEFAULT FALSE
71);
72
73CREATE TABLE TEACHER_X_STUDENT (
74  teacher_user_id INT REFERENCES "USER" (user_id) NOT NULL,
75  student_user_id INT REFERENCES "USER" (user_id) NOT NULL,
76  lection_flg BOOLEAN NOT NULL DEFAULT FALSE
77);