Changes between Version 5 and Version 6 of ddlScript.sql


Ignore:
Timestamp:
09/04/25 06:19:50 (8 weeks ago)
Author:
221028
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v5 v6  
    11{{{#!sql
    2 -- Drop existing tables if they exist
    3 DROP TABLE IF EXISTS UserParticipatesInExperiment CASCADE;
    4 DROP TABLE IF EXISTS UserViewsExperiment CASCADE;
    5 DROP TABLE IF EXISTS UserViewsElement CASCADE;
    6 DROP TABLE IF EXISTS UserViewsLabEquipment CASCADE;
    7 DROP TABLE IF EXISTS ExperimentLabEquipment CASCADE;
    8 DROP TABLE IF EXISTS Experiment CASCADE;
    9 DROP TABLE IF EXISTS Reaction CASCADE;
    10 DROP TABLE IF EXISTS Elements CASCADE;
    11 DROP TABLE IF EXISTS LabEquipment CASCADE;
    12 DROP TABLE IF EXISTS Student CASCADE;
    13 DROP TABLE IF EXISTS Teacher CASCADE;
     2DROP TABLE IF EXISTS userparticipatesinexperiment CASCADE;
     3DROP TABLE IF EXISTS userviewsexperiment CASCADE;
     4DROP TABLE IF EXISTS userviewselement CASCADE;
     5DROP TABLE IF EXISTS userviewslabequipment CASCADE;
     6DROP TABLE IF EXISTS experimentlabequipment CASCADE;
     7DROP TABLE IF EXISTS experiment CASCADE;
     8DROP TABLE IF EXISTS reaction CASCADE;
     9DROP TABLE IF EXISTS elements CASCADE;
     10DROP TABLE IF EXISTS labequipment CASCADE;
     11DROP TABLE IF EXISTS student CASCADE;
     12DROP TABLE IF EXISTS teacher CASCADE;
    1413DROP TABLE IF EXISTS "User" CASCADE;
    1514
    16 -- Create User table
     15DROP TYPE IF EXISTS user_role CASCADE;
     16
     17-- ENUM за улога
     18CREATE TYPE user_role AS ENUM ('student', 'teacher');
     19
     20-- USER
    1721CREATE TABLE "User" (
    18     user_id SERIAL PRIMARY KEY,
    19     user_name VARCHAR(30) NOT NULL,
    20     user_surname VARCHAR(30) NOT NULL,
    21     email VARCHAR(50) NOT NULL UNIQUE,
    22     password VARCHAR(100) NOT NULL,
    23     role ENUM('student', 'teacher') NOT NULL
     22    user_id       SERIAL PRIMARY KEY,
     23    user_name     VARCHAR(30) NOT NULL,
     24    user_surname  VARCHAR(30) NOT NULL,
     25    email         VARCHAR(50) NOT NULL UNIQUE,
     26    password      VARCHAR(100) NOT NULL,
     27    role          user_role    NOT NULL
    2428);
    2529
    26 -- Create Student table (inherits from User)
    27 CREATE TABLE Student (
    28     student_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE,
    29     teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE
    30 );
    31 
    32 -- Create Teacher table (inherits from User)
    33 CREATE TABLE Teacher (
     30CREATE TABLE teacher (
    3431    teacher_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE
    3532);
    3633
    37 -- Create LabEquipment table
    38 CREATE TABLE LabEquipment (
    39     equipment_id SERIAL PRIMARY KEY,
    40     equipment_name VARCHAR(50) NOT NULL UNIQUE,
    41     type VARCHAR(50),
    42     description TEXT,
    43     safety_info TEXT,
    44     teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE
     34CREATE TABLE student (
     35    student_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE,
     36    teacher_id INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE
    4537);
    4638
    47 -- Create Elements table
    48 CREATE TABLE Elements (
    49     element_id SERIAL PRIMARY KEY,
    50     symbol VARCHAR(2) NOT NULL UNIQUE,
    51     element_name VARCHAR(50) NOT NULL,
    52     atomic_number INT NOT NULL,
    53     atomic_weight FLOAT NOT NULL,
    54     melting_point FLOAT,
    55     boiling_point FLOAT,
    56     hazard_type VARCHAR(50),
    57     description_element TEXT,
    58     teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE
     39-- LABEQUIPMENT
     40CREATE TABLE labequipment (
     41    equipment_id   SERIAL PRIMARY KEY,
     42    equipment_name VARCHAR(50) NOT NULL UNIQUE,
     43    type           VARCHAR(50),
     44    description    TEXT,
     45    safety_info    TEXT,
     46    teacher_id     INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE
    5947);
    6048
    61 -- Create Reaction table
    62 CREATE TABLE Reaction (
    63     reaction_id SERIAL PRIMARY KEY,
    64     teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE,
    65     element1_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
    66     element2_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
    67     product VARCHAR(50),
    68     conditions TEXT
     49-- ELEMENTS
     50CREATE TABLE elements (
     51    element_id          SERIAL PRIMARY KEY,
     52    symbol              VARCHAR(2)  NOT NULL UNIQUE,
     53    element_name        VARCHAR(50) NOT NULL,
     54    atomic_number       INT         NOT NULL,
     55    atomic_weight       DOUBLE PRECISION NOT NULL,
     56    melting_point       DOUBLE PRECISION,
     57    boiling_point       DOUBLE PRECISION,
     58    hazard_type         VARCHAR(50),
     59    description_element TEXT,
     60    teacher_id          INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE
    6961);
    7062
    71 -- Create Experiment table
    72 CREATE TABLE Experiment (
    73     experiment_id SERIAL PRIMARY KEY,
    74     teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE,
    75     reaction_id INT NOT NULL REFERENCES Reaction(reaction_id) ON DELETE CASCADE,
    76     result TEXT,
    77     time_stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     63-- REACTION
     64CREATE TABLE reaction (
     65    reaction_id SERIAL PRIMARY KEY,
     66    teacher_id  INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE,
     67    element1_id INT NOT NULL REFERENCES elements(element_id) ON DELETE CASCADE,
     68    element2_id INT NOT NULL REFERENCES elements(element_id) ON DELETE CASCADE,
     69    product     VARCHAR(100),
     70    conditions  TEXT,
     71    CONSTRAINT chk_reaction_diff_elements CHECK (element1_id <> element2_id)
     72);
     73
     74-- EXPERIMENT
     75CREATE TABLE experiment (
     76    experiment_id  SERIAL PRIMARY KEY,
     77    teacher_id     INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE,
     78    reaction_id    INT NOT NULL REFERENCES reaction(reaction_id) ON DELETE CASCADE,
     79    result         TEXT,
     80    time_stamp     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    7881    safety_warning TEXT
    7982);
    8083
    81 -- Join table for linking experiments and lab equipment
    82 CREATE TABLE ExperimentLabEquipment (
    83     experiment_id INT NOT NULL REFERENCES Experiment(experiment_id) ON DELETE CASCADE,
    84     equipment_id INT NOT NULL REFERENCES LabEquipment(equipment_id) ON DELETE CASCADE,
     84-- EXPERIMENT <-> LABEQUIPMENT (N:M)
     85CREATE TABLE experimentlabequipment (
     86    experiment_id INT NOT NULL REFERENCES experiment(experiment_id) ON DELETE CASCADE,
     87    equipment_id  INT NOT NULL REFERENCES labequipment(equipment_id) ON DELETE CASCADE,
    8588    PRIMARY KEY (experiment_id, equipment_id)
    8689);
    8790
    88 -- Join table for tracking which users viewed elements
    89 CREATE TABLE UserViewsElement (
    90     user_id INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    91     element_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
     91-- USER VIEWS
     92CREATE TABLE userviewselement (
     93    user_id    INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
     94    element_id INT NOT NULL REFERENCES elements(element_id) ON DELETE CASCADE,
    9295    PRIMARY KEY (user_id, element_id)
    9396);
    9497
    95 -- Join table for tracking which users viewed lab equipment
    96 CREATE TABLE UserViewsLabEquipment (
    97     user_id INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    98     equipment_id INT NOT NULL REFERENCES LabEquipment(equipment_id) ON DELETE CASCADE,
     98CREATE TABLE userviewslabequipment (
     99    user_id      INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
     100    equipment_id INT NOT NULL REFERENCES labequipment(equipment_id) ON DELETE CASCADE,
    99101    PRIMARY KEY (user_id, equipment_id)
    100102);
    101103
    102 -- Join table for tracking which users viewed experiments
    103 CREATE TABLE UserViewsExperiment (
    104     user_id INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    105     experiment_id INT NOT NULL REFERENCES Experiment(experiment_id) ON DELETE CASCADE,
    106     PRIMARY KEY (user_id, experiment_id)
    107 );
    108 
    109 -- Join table for tracking which users participated in an experiment
    110 CREATE TABLE UserParticipatesInExperiment (
    111     user_id INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    112     experiment_id INT NOT NULL REFERENCES Experiment(experiment_id) ON DELETE CASCADE,
     104-- USER PARTICIPATION
     105CREATE TABLE userparticipatesinexperiment (
     106    user_id                 INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
     107    experiment_id           INT NOT NULL REFERENCES experiment(experiment_id) ON DELETE CASCADE,
    113108    PRIMARY KEY (user_id, experiment_id)
    114109);