Changes between Initial Version and Version 1 of ddlScript.sql

Timestamp:

01/20/25 18:21:13 (10 days ago)

Author:

221028

Comment:

--

ddlScript.sql

@@ +1 @@
+{{{#!sql
+DROP TABLE IF EXISTS ExperimentLabEquipment CASCADE;
+DROP TABLE IF EXISTS Experiment CASCADE;
+DROP TABLE IF EXISTS Reaction CASCADE;
+DROP TABLE IF EXISTS Elements CASCADE;
+DROP TABLE IF EXISTS LabEquipment CASCADE;
+DROP TABLE IF EXISTS USER CASCADE;
+
+
+CREATE TABLE USER (
+    user_id SERIAL PRIMARY KEY,
+    user_name VARCHAR(30) NOT NULL,
+    user_surname VARCHAR(30) NOT NULL,
+    password VARCHAR(100) NOT NULL,
+    role ENUM('student', 'teacher') NOT NULL
+);
+
+
+CREATE TABLE LabEquipment (
+    equipment_id SERIAL PRIMARY KEY,
+    equipment_name VARCHAR(50) NOT NULL UNIQUE,
+    type VARCHAR(50),
+    description TEXT,
+    safety_info TEXT
+);
+
+
+CREATE TABLE Elements (
+    element_id SERIAL PRIMARY KEY,
+    symbol VARCHAR(2) NOT NULL UNIQUE,
+    element_name VARCHAR(50) NOT NULL,
+    atomic_number INT NOT NULL,
+    atomic_weight FLOAT NOT NULL,
+    melting_point FLOAT,
+    boiling_point FLOAT,
+    hazard_type VARCHAR(50),
+    description_element TEXT
+);
+
+
+CREATE TABLE Reaction (
+    reaction_id SERIAL PRIMARY KEY,
+    element1_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
+    element2_id INT NOT NULL REFERENCES Elements(element_id) ON DELETE CASCADE,
+    product VARCHAR(50),
+    conditions TEXT
+);
+
+-- Создај табела за експерименти
+CREATE TABLE Experiment (
+    experiment_id SERIAL PRIMARY KEY,
+    user_id INT NOT NULL REFERENCES USER (user_id) ON DELETE CASCADE,
+    reaction_id INT NOT NULL REFERENCES Reaction(reaction_id) ON DELETE CASCADE,
+    result TEXT,
+    time_stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    safety_warning TEXT
+);
+
+-- Создај поврзувачка табела за експерименти и лабораториска опрема
+CREATE TABLE ExperimentLabEquipment (
+    experiment_id INT NOT NULL REFERENCES Experiment(experiment_id) ON DELETE CASCADE,
+    equipment_id INT NOT NULL REFERENCES LabEquipment(equipment_id) ON DELETE CASCADE,
+
+    PRIMARY KEY (experiment_id, equipment_id)
+);
+}}}