Vilnius University DBVS lab work guidebook (kinda)

I was hesitant on whether to write a blog post about studies, but after searching the web and discovering the https://dziaugys.net/blogas/ blog, I decided to create something for a fun read in the future. Instead of focusing on my experience, the main priority is to give some tips and help for other students. In this post, I will focus only on DBVS (Duomenų bazių valdymo sistemos) otherwise know as DBMS (Database Management Systems) in English. To skip most of the ranting CLICK HERE

blah blah boring

The initial idea for this blog occurred to me in the end of the third semester when I had to write a program for the last lab of DBVS. The task involved implementing the 2nd (ER Model and DB scheme) and the 3rd lab (SQL commands) into a C program (or other programming language, depending on the the lab lecturer). Wow, that was a lot of text in (parenthesis), I am very good at writing (text).

blah blah still boring

When I was researching how to create the program, I came across notes of a former student (or Vycka, as he called himself 8 years ago). These notes really saved me a lot of time and made the task much easier as I had a great reference point. Thanks, if you are reading this! So essentially I will be making my work free real estate, adding a couple more pointers for easier navigation and better understanding (hopefully). This did net me a maximum possible mark from the labs, but I did go to Prof. Dr. Baronas class which might automatically get you a max assuming you are not late.

Here is some encouraging words in case you are doing this a day, or night before the deadlines. I believe it is possible to do all the labs in under 8 hours in perfect quality. And it might be possible to complete them in 1 to 3 by using AI, however this will make it difficult to answer any question or make any changes to the program (very high possibility).

I will be skipping the queries which were the first lab and will go straight into ER Diagrams and tables (nothing interesting here, I just have an addiction now I think (a play on previous parenthisis joke))… (funny)

FINALLY GOT TO THE POINT

First part (2nd lab)

Tools

For tools I recommend using these two online tools:

How to

First I highly recommend opening a couple of tabs of the material you have related to ER models and DB schemas, compared with the requirements your lab has to fulfill.

Now for the easy part you have to think of an enviroment/business to depict. You can choose whatever the lecturer recommends - probably a hospital or a bank but I would suggest choosing a more rare subject which is interesting to you (most likely to the lecturer too). You can pick some weird stuff, my friends created a powerlifting model so the range is quite huge. Or just copy paste requirements to GPT and work from there.

After figuring out the business its time to choose 4-5 main entities (depending on the requirements) and think of the processes that interconnect them. Entity connections and attributes are obvious if you put some thought into choosing the business, so I would recommend spending more time there.

Examples tend to help so you can either use the ones showcased in Vyckas web playground page or mine:

Image of ER-model

ER-model

Image of DB-scheme

DB-scheme

Second part (3rd lab)

This is probably the most time consuming part, thinking of logical triggers and fitting all the criteria can be difficult. No real programming here except for triggers. I suggest doing a few creates yourself to get familiar with syntax and logic of SQL and only then using AI. PAY ATTENTION TO REQUIREMENTS at least in Prof. Dr. Baronas lab, it is important to understand each requirement and concept. And here is the sacue 😋😋😋

Creates
Tables
create_tables.sql 
CREATE TABLE magr9049.Aukcionas (
Aukciono_id SERIAL NOT NULL,
Vedejo_id INTEGER NOT NULL,
Pavadinimas VARCHAR(64) NOT NULL,
Pradzios_laikas TIMESTAMP NOT NULL,
Pabaigos_laikas TIMESTAMP NOT NULL
CONSTRAINT TinkamasLaikas
CHECK (Pabaigos_laikas > Pradzios_laikas),
Vieta VARCHAR(64) DEFAULT 'Nežinoma',
PRIMARY KEY (Aukciono_id)
);

CREATE TABLE magr9049.Vedejas (
Vedejo_id SERIAL NOT NULL,
Vardas VARCHAR(16) NOT NULL,
Pavarde VARCHAR(16) NOT NULL,
Telefono_numeris INTEGER
CONSTRAINT TeigiamasSkaicius
CHECK (Telefono_numeris > 0),
PRIMARY KEY (Vedejo_id)
);

CREATE TABLE magr9049.Dalyvis (
Dalyvio_id SERIAL NOT NULL,
Vardas VARCHAR(16) NOT NULL,
Pavarde VARCHAR(16) NOT NULL,
PRIMARY KEY (Dalyvio_id)
);

CREATE TABLE magr9049.Aukciono_dalyvis (
Aukciono_id INTEGER,
Dalyvio_id INTEGER,
PRIMARY KEY (Aukciono_id, Dalyvio_id),
FOREIGN KEY (Aukciono_id) REFERENCES magr9049.Aukcionas(Aukciono_id)
ON DELETE CASCADE ON UPDATE RESTRICT,
FOREIGN KEY (Dalyvio_id) REFERENCES magr9049.Dalyvis(Dalyvio_id)
ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE magr9049.Daiktas (
Daikto_id SERIAL NOT NULL,
Aukciono_id INTEGER,
Pavadinimas VARCHAR(64),
Kaina INTEGER,
Yra_parduotas BOOLEAN
DEFAULT FALSE
CONSTRAINT TinkamasBoolean
CHECK (Yra_parduotas = TRUE OR Yra_parduotas = FALSE),
PRIMARY KEY (Daikto_id),
FOREIGN KEY (Aukciono_id) REFERENCES magr9049.Aukcionas(Aukciono_id)
ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE magr9049.Kainos_siulymai (
Kainos_siulymo_id SERIAL NOT NULL,
Dalyvio_id INTEGER,
Daikto_id INTEGER,
Suma INTEGER
DEFAULT 1
CONSTRAINT TeigiamasSkaicius
CHECK (Suma > 0),
Yra_laimetojas BOOLEAN
DEFAULT FALSE
CONSTRAINT TinkamasBoolean
CHECK (Yra_laimetojas = TRUE OR Yra_laimetojas = FALSE),
PRIMARY KEY (Kainos_siulymo_id),
FOREIGN KEY (Dalyvio_id) REFERENCES magr9049.Dalyvis(Dalyvio_id)
ON DELETE CASCADE ON UPDATE RESTRICT,
FOREIGN KEY (Daikto_id) REFERENCES magr9049.Daiktas(Daikto_id)
ON DELETE CASCADE ON UPDATE RESTRICT
);
Indexes
create_indexes.sql 
CREATE UNIQUE INDEX Vedejo_Vardas_Pavarde ON magr9049.Vedejas(Vardas, Pavarde);
CREATE INDEX Daikto_kaina ON magr9049.Daiktas(Kaina);
Triggers
create_triggers.sql 
-- Trigeris ir funkcija patikrinti ar kainos pasiulymas yra didesnis negu daikto kaina
CREATE OR REPLACE FUNCTION Patikrinti_Kainos_Siulyma()
RETURNS TRIGGER AS $$
BEGIN
  IF (
    SELECT NEW.Suma
  ) <= (
    SELECT Kaina
    FROM magr9049.Daiktas
    WHERE Daikto_id = NEW.Daikto_id
  ) THEN
    RAISE EXCEPTION 'Kainos siulymas turi buti didesnis negu dabartine kaina.';
  ELSE
    -- Pakeisti kaina
    UPDATE magr9049.Daiktas
    SET Kaina = NEW.Suma
    WHERE Daikto_id = NEW.Daikto_id;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Kainos_Siulymo_Trigeris
BEFORE INSERT OR UPDATE ON magr9049.Kainos_siulymai
FOR EACH ROW EXECUTE FUNCTION Patikrinti_Kainos_Siulyma();

-- Trigeris ir funkcija pakeisti daikto pardavimo bukle
CREATE OR REPLACE FUNCTION Atnaujinti_Daikto_Busena()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.Yra_laimetojas = TRUE THEN
        IF (SELECT Yra_parduotas FROM magr9049.Daiktas WHERE Daikto_id = NEW.Daikto_id) = TRUE THEN
            RAISE EXCEPTION 'Daiktas jau yra parduotas!';
        ELSE
            UPDATE magr9049.Daiktas
            SET Yra_parduotas = TRUE
            WHERE Daikto_id = NEW.Daikto_id;
        END IF;
    END IF;
    /*IF NEW.Yra_laimetojas = TRUE THEN
        UPDATE magr9049.Daiktas
        SET Yra_parduotas = TRUE
        WHERE Daikto_id = NEW.Daikto_id;
    END IF;*/

    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Atnaujinti_Daikto_Busena_Trigeris
BEFORE INSERT OR UPDATE ON magr9049.Kainos_siulymai
FOR EACH ROW
EXECUTE FUNCTION Atnaujinti_Daikto_Busena();

-- Trigeris ir funkcija patikrinti ar daiktas yra parduotas
CREATE OR REPLACE FUNCTION Patikrinti_Daiktas_Parduotas()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.Yra_parduotas = TRUE AND NOT EXISTS (
        SELECT 1
        FROM magr9049.Kainos_siulymai ks
        WHERE ks.Daikto_id = NEW.Daikto_id AND ks.Yra_laimetojas = TRUE
    ) THEN
        RAISE EXCEPTION 'Daiktas negali buti parduotas jeigu neturi laimincio kainos pasiulymo.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Daiktas_Parduotas_Trigeris
BEFORE INSERT OR UPDATE ON magr9049.Daiktas
FOR EACH ROW
EXECUTE FUNCTION Patikrinti_Daiktas_Parduotas();
Views
create_views.sql 
CREATE VIEW magr9049.Dalyvauti_aukcionai AS
SELECT
    A.Aukciono_id,
    A.Pavadinimas,
    A.Pradzios_laikas,
    A.Pabaigos_laikas,
    A.Vieta,
    D.Vardas,
    D.Pavarde
FROM
    magr9049.Aukcionas A
JOIN
    magr9049.Aukciono_dalyvis AD ON A.Aukciono_id = AD.Aukciono_id
JOIN
    magr9049.Dalyvis D ON AD.Dalyvio_id = D.Dalyvio_id;

CREATE VIEW magr9049.Aukciono_apzvalga AS
SELECT
    D.Aukciono_id,
    COUNT(Daiktas.Daikto_id),
    AVG(Daiktas.Kaina)
FROM magr9049.Daiktas
JOIN magr9049.Aukcionas D ON Daiktas.Aukciono_id = D.Aukciono_id
GROUP BY D.Aukciono_id;

CREATE MATERIALIZED VIEW magr9049.Brangiausias_aukciono_daiktas AS
SELECT
  A.Aukciono_id,
  A.Pavadinimas AS Aukciono_Pavadinimas,
  D.Daikto_id,
  D.Pavadinimas AS Daikto_Pavadinimas,
  D.Kaina
FROM
  magr9049.Aukcionas A
JOIN
  magr9049.Daiktas D ON A.Aukciono_id = D.Aukciono_id
WHERE
  D.Kaina = (
    SELECT MAX(Kaina)
    FROM magr9049.Daiktas
    WHERE Aukciono_id = A.Aukciono_id
  );
REFRESH MATERIALIZED VIEW magr9049.Brangiausias_aukciono_daiktas;
Drops (deletes)
Tables
drop_tables.sql 
DROP TABLE magr9049.Vedejas;
DROP TABLE magr9049.Aukciono_dalyvis;
DROP TABLE magr9049.Kainos_siulymai;
DROP TABLE magr9049.Daiktas;
DROP TABLE magr9049.Dalyvis;
DROP TABLE magr9049.Aukcionas;
Indexes
drop_indexes.sql 
DROP INDEX Daikto_kaina;
DROP INDEX Vedejo_vardas_pavarde;
Triggers
drop_triggers.sql 
DROP TRIGGER Kainos_Siulymo_Trigeris ON magr9049.Kainos_siulymai;
DROP FUNCTION Patikrinti_Kainos_Siulyma();

DROP TRIGGER Daiktas_Parduotas_Trigeris ON magr9049.Daiktas;
DROP FUNCTION Patikrinti_Daiktas_Parduotas();

DROP TRIGGER Atnaujinti_Daikto_Busena_Trigeris ON magr9049.Kainos_siulymai;
DROP FUNCTION Atnaujinti_Daikto_Busena();
Views
drop_views.sql 
DROP VIEW magr9049.Aukciono_apzvalga;
DROP VIEW magr9049.Dalyvauti_aukcionai;
DROP MATERIALIZED VIEW magr9049.Brangiausias_aukciono_daiktas;
Data
Inserts
inserts.sql 
-- Insert into Aukcionas
INSERT INTO magr9049.Aukcionas
   (Vedejo_id, Pavadinimas, Pradzios_laikas, Pabaigos_laikas, Vieta)
VALUES
   (1, 'Vilniaus Universiteto', '2023-01-01 10:00:00', '2023-01-01 12:00:00', 'Didlaukio g. 47'),
   (2, 'Palangos Kurhauzo', '2023-02-01 15:00:00', '2023-02-01 17:00:00', 'Grafų Tiškevičių al. 1'),
   (1, 'Pokemonų Kortelių', '2023-02-14 14:00:00', '2023-02-14 15:30:00', 'Didlaukio g. 59');
-- Insert into Vedejas
INSERT INTO magr9049.Vedejas
   (Vardas, Pavarde, Telefono_numeris)
VALUES
   ('Antanas', 'Antanavičius', 123456789),
   ('Petras', 'Petravičius', 987654321);

-- Insert into Dalyvis
INSERT INTO magr9049.Dalyvis
   (Vardas, Pavarde)
VALUES
   ('Ona', 'Onavčienė'),
   ('Dovydas', 'Dovydavičius'),
   ('Lukas', 'Lukavičius'),
   ('Gedas', 'Gedavičius');

-- Insert into Aukciono_dalyvis
INSERT INTO magr9049.Aukciono_dalyvis
   (Aukciono_id, Dalyvio_id)
VALUES
   (1, 1),
   (1, 2),
   (3, 4),
   (2, 1),
   (2, 3),
   (2, 4),
   (1, 4),
   (3, 2);

-- Insert into Daiktas
INSERT INTO magr9049.Daiktas
   (Aukciono_id, Pavadinimas, Kaina)
VALUES
   (1, 'Pirmasis universiteto rašiklis', 100),
   (1, 'Seniausias universiteto kompiuteris', 120),
   (2, 'Originali Palangos tilto lenta', 200),
   (2, 'Sudegusio kurhauzo sienos dalis', 50),
   (3, 'Spindinti pikačiu kortelė', 300);

-- Insert into Kainos_siulymai
INSERT INTO magr9049.Kainos_siulymai
   (Dalyvio_id, Daikto_id, Suma, Yra_laimetojas)
VALUES
   (1, 1, 120, FALSE),
   (2, 1, 130, TRUE),
   (1, 2, 150, FALSE),
   (3, 2, 155, FALSE),
   (4, 2, 160, TRUE),
   (1, 3, 250, TRUE),
   (2, 4, 110, FALSE),
   (4, 4, 120, TRUE),
   (3, 5, 310, FALSE),
   (4, 5, 350, FALSE);
Tests
tests.sql 
-- Mazesnis kainos siulymas
INSERT INTO magr9049.Kainos_siulymai
   (Dalyvio_id, Daikto_id, Suma, Yra_laimetojas)
VALUES
   (1, 1, 50, FALSE);
-- Idedamas jau parduotas daiktas
INSERT INTO magr9049.Daiktas
   (Aukciono_id, Pavadinimas, Kaina, Yra_parduotas)
VALUES
   (1, 'Pirmasis universiteto rašiklis', 100, TRUE);
-- AUkcionas su neteisingu laiku
INSERT INTO magr9049.Aukcionas
   (Vedejo_id, Pavadinimas, Pradzios_laikas, Pabaigos_laikas, Vieta)
VALUES
   (1, 'KTU Artifaktai', '2023-01-01 12:00:00', '2023-01-01 11:00:00', 'Didlaukio g. 47');
-- Upadte test for trigger
UPDATE magr9049.Kainos_siulymai
SET suma = 340
WHERE kainos_siulymo_id = 10;

Third part (Program)

For the final part you have to write a program that implements your already written SQL. I wrote mine in C and mainly used Prof. Dr. Baronas book, slides and internet obviously (duh²). Not much I can add here, except to understand ROLLBACKS and COMMITS, that is what I personally was asked about everything else is just regular coding, nothing too difficult. Click here for candy (DEFINITELY NOT THE WHOLE SRC FOLDER 😉😉), definitely not perfectt but it completes all the requirements and should be a great reference point.

Endtoe

I am surely fully absolutely certainly and most definitely aware of all the legalities of sharing my University work, pls dont hurt meee 🥺🥺👉👈 One more recommendation regarding studying for the exam, in the rare case of not attending lectures and pooping your pants because
??? wtf does A → B ∈ F+, B → A ∉ F+, B → C ∈ F+ ir C ⊄ A∪B mean ???,
!!! I stil have PTSD from Algorithm Theory !!!,
I suggest undertaking the all nighter using this and also this could help as well. Best of luck!