MGi2Cvzq

1-- Create a user to interact with this demo database
2CREATE USER "StudentsDemoDbAdmin" WITH PASSWORD '123';
3-- Grant access for this user to postgres's server databases
4GRANT "StudentsDemoDbAdmin" TO postgres;
5-- Create a database and set a default timezone as UTC this allows to get a UTC timestamp with now() function
6CREATE DATABASE "StudentsDemoDB" OWNER "StudentsDemoDbAdmin";
7ALTER DATABASE "StudentsDemoDB" SET timezone='UTC';
8
9-- Create tables & indexes
10CREATE TABLE IF NOT EXISTS "Specialty"
11(
12    "Id"                        UUID PRIMARY KEY,
13    "ShortName"                 VARCHAR(6),
14    "FullName"                  VARCHAR(256)
15);
16CREATE INDEX IF NOT EXISTS "index_Specialty" ON "Specialty" ("Id", "ShortName");
17
18CREATE TABLE IF NOT EXISTS "Groups"
19(
20    "Name"                      VARCHAR(7) PRIMARY KEY CHECK ( length("Name") > 0 ),
21    "StudingYear"               INTEGER NOT NULL,
22    "SpecialtyId"               UUID,
23    CONSTRAINT "fk_Group_Specialty"
24        FOREIGN KEY ("SpecialtyId")
25            REFERENCES "Specialty" ("Id")
26                ON DELETE SET NULL
27);
28CREATE INDEX IF NOT EXISTS "index_Groups" ON "Groups" ("Name");
29
30CREATE TABLE IF NOT EXISTS "Students"
31(
32    "Id"                        UUID PRIMARY KEY,
33    "FirstName"                 VARCHAR(64) CHECK ( length("FirstName") > 0 ),
34    "LastName"                  VARCHAR(64) CHECK ( length("LastName") > 0 ),
35    "StudentBookNumber"         CHAR(6) UNIQUE CHECK ( length("StudentBookNumber") = 6),
36    "Age"                       INT Check ( "Age" > 0 AND "Age" < 100 ),
37    "GroupId"                   VARCHAR(7) CHECK ( length("GroupId") > 0 ),
38    CONSTRAINT "fk_Student_Group"
39        FOREIGN KEY ("GroupId")
40            REFERENCES "Groups" ("Name")
41                ON DELETE SET NULL
42);
43CREATE INDEX IF NOT EXISTS "index_Students" ON "Students" ("Id", "FirstName", "LastName", "Age");
44
45-- Fill tables with a test data
46INSERT INTO "Specialty"("Id", "ShortName", "FullName")
47VALUES ('b380d9b8-0cd0-11ec-82a8-0242ac130003', 'IPI', 'Department of Informatics and Software Engineering (IPI)'),
48       ('ec8cfa2a-0cd0-11ec-82a8-0242ac130003', 'IST', 'Department of Information Systems and Technologies (IST)'),
49       ('f090bb98-0cd0-11ec-82a8-0242ac130003', 'OT', 'Department of Computer Engineering (OT)');
50
51INSERT INTO "Groups"("Name", "StudingYear", "SpecialtyId")
52VALUES ('IT-01', 2, 'b380d9b8-0cd0-11ec-82a8-0242ac130003'),
53       ('IM-01', 2, 'f090bb98-0cd0-11ec-82a8-0242ac130003'),
54       ('IK-01', 2, 'ec8cfa2a-0cd0-11ec-82a8-0242ac130003');
55
56INSERT INTO "Students"("Id", "FirstName", "LastName", "StudentBookNumber", "Age", "GroupId")
57VALUES ('5650e93c-f1f6-4b91-8b14-a90aa7544eda', 'Ivan', 'Ivanov', '189364', 18, 'IT-01'),
58       ('8967b5bd-f045-4769-8840-32fe4ae50e78', 'Petr', 'Petrov', '129472', 17, 'IM-01'),
59       ('4b16cdb3-8e98-4355-a579-627ce1f61d2e', 'Viktor', 'Viktorov', '145782', 19, 'IK-01');
60