Changes between Version 4 and Version 5 of ddlScript.sql


Ignore:
Timestamp:
02/06/25 16:30:33 (2 weeks ago)
Author:
221028
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v4 v5  
    11{{{#!sql
     2-- Drop existing tables if they exist
     3DROP TABLE IF EXISTS UserParticipatesInExperiment CASCADE;
     4DROP TABLE IF EXISTS UserViewsExperiment CASCADE;
     5DROP TABLE IF EXISTS UserViewsElement CASCADE;
     6DROP TABLE IF EXISTS UserViewsLabEquipment CASCADE;
    27DROP TABLE IF EXISTS ExperimentLabEquipment CASCADE;
    38DROP TABLE IF EXISTS Experiment CASCADE;
    … …  
    510DROP TABLE IF EXISTS Elements CASCADE;
    611DROP TABLE IF EXISTS LabEquipment CASCADE;
     12DROP TABLE IF EXISTS Student CASCADE;
     13DROP TABLE IF EXISTS Teacher CASCADE;
    714DROP TABLE IF EXISTS "User" CASCADE;
    815
    9 
     16-- Create User table
    1017CREATE TABLE "User" (
    1118    user_id SERIAL PRIMARY KEY,
    1219    user_name VARCHAR(30) NOT NULL,
    1320    user_surname VARCHAR(30) NOT NULL,
    14     email VARCHAR(50) NOT NULL UNIQUE
     21    email VARCHAR(50) NOT NULL UNIQUE,
    1522    password VARCHAR(100) NOT NULL,
    1623    role ENUM('student', 'teacher') NOT NULL
    1724);
    1825
     26-- Create Student table (inherits from User)
     27CREATE 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);
    1931
     32-- Create Teacher table (inherits from User)
     33CREATE TABLE Teacher (
     34    teacher_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE
     35);
     36
     37-- Create LabEquipment table
    2038CREATE TABLE LabEquipment (
    2139    equipment_id SERIAL PRIMARY KEY,
    … …  
    2341    type VARCHAR(50),
    2442    description TEXT,
    25     safety_info TEXT
     43    safety_info TEXT,
     44    teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE
    2645);
    2746
    28 
     47-- Create Elements table
    2948CREATE TABLE Elements (
    3049    element_id SERIAL PRIMARY KEY,
    … …  
    3655    boiling_point FLOAT,
    3756    hazard_type VARCHAR(50),
    38     description_element TEXT
     57    description_element TEXT,
     58    teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE
    3959);
    4060
    41 
     61-- Create Reaction table
    4262CREATE TABLE Reaction (
    4363    reaction_id SERIAL PRIMARY KEY,
     64    teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE,
    4465    element1_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
    4566    element2_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
    … …  
    4869);
    4970
    50 
     71-- Create Experiment table
    5172CREATE TABLE Experiment (
    5273    experiment_id SERIAL PRIMARY KEY,
    53     user_id INT NOT NULL REFERENCES "User" (user_id) ON DELETE CASCADE,
     74    teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE,
    5475    reaction_id INT NOT NULL REFERENCES Reaction(reaction_id) ON DELETE CASCADE,
    5576    result TEXT,
    … …  
    5879);
    5980
    60 
     81-- Join table for linking experiments and lab equipment
    6182CREATE TABLE ExperimentLabEquipment (
    6283    experiment_id INT NOT NULL REFERENCES Experiment(experiment_id) ON DELETE CASCADE,
    6384    equipment_id INT NOT NULL REFERENCES LabEquipment(equipment_id) ON DELETE CASCADE,
    64 
    6585    PRIMARY KEY (experiment_id, equipment_id)
    6686);
     87
     88-- Join table for tracking which users viewed elements
     89CREATE 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,
     92    PRIMARY KEY (user_id, element_id)
     93);
     94
     95-- Join table for tracking which users viewed lab equipment
     96CREATE 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,
     99    PRIMARY KEY (user_id, equipment_id)
     100);
     101
     102-- Join table for tracking which users viewed experiments
     103CREATE 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
     110CREATE 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,
     113    PRIMARY KEY (user_id, experiment_id)
     114);
    67115}}}