Projekt końcowy — System wypożyczalni filmów VOD

Na podstawie wymagań określ typy relacji między encjami:

Napisz polecenia tworzące bazę vod z kodowaniem UTF-8 i tabelę kategorie:

CREATE DATABASE IF NOT EXISTS vod
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_polish_ci;

USE vod;

CREATE TABLE kategorie (
  id_kategorii INT           AUTO_INCREMENT PRIMARY KEY,
  nazwa        VARCHAR(80)   NOT NULL UNIQUE,
  opis         TEXT
);

Napisz CREATE TABLE dla: uzytkownicy, filmy, wypozyczenia, oceny.

CREATE TABLE uzytkownicy (
  id_uzytkownika  INT           AUTO_INCREMENT PRIMARY KEY,
  imie            VARCHAR(60)   NOT NULL,
  nazwisko        VARCHAR(80)   NOT NULL,
  email           VARCHAR(150)  NOT NULL UNIQUE,
  haslo_hash      VARCHAR(255)  NOT NULL,
  data_rejestracji TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  aktywny         BOOLEAN       DEFAULT TRUE
);

CREATE TABLE filmy (
  id_filmu     INT           AUTO_INCREMENT PRIMARY KEY,
  tytul        VARCHAR(200)  NOT NULL,
  rok          YEAR,
  opis         TEXT,
  cena_wyp     DECIMAL(6,2)  NOT NULL DEFAULT 9.99,
  id_kategorii INT,
  czas_min     SMALLINT UNSIGNED,
  aktywny      BOOLEAN       DEFAULT TRUE,
  FOREIGN KEY (id_kategorii) REFERENCES kategorie(id_kategorii)
    ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE wypozyczenia (
  id_wypozyczenia   INT           AUTO_INCREMENT PRIMARY KEY,
  id_uzytkownika    INT           NOT NULL,
  id_filmu          INT           NOT NULL,
  data_wyp          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  data_zwrotu_plan  DATE          NOT NULL,
  data_zwrotu_real  DATE          NULL,
  kwota             DECIMAL(6,2)  NOT NULL,
  FOREIGN KEY (id_uzytkownika) REFERENCES uzytkownicy(id_uzytkownika)
    ON DELETE RESTRICT,
  FOREIGN KEY (id_filmu) REFERENCES filmy(id_filmu)
    ON DELETE RESTRICT
);

CREATE TABLE oceny (
  id_oceny       INT      AUTO_INCREMENT PRIMARY KEY,
  id_uzytkownika INT      NOT NULL,
  id_filmu       INT      NOT NULL,
  ocena          TINYINT  NOT NULL,
  komentarz      TEXT,
  data_oceny     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_uzytkownika) REFERENCES uzytkownicy(id_uzytkownika)
    ON DELETE CASCADE,
  FOREIGN KEY (id_filmu) REFERENCES filmy(id_filmu)
    ON DELETE CASCADE,
  CONSTRAINT chk_ocena CHECK (ocena BETWEEN 1 AND 10),
  UNIQUE (id_uzytkownika, id_filmu) -- jedna ocena per user per film
);

Wstaw po co najmniej 3 rekordy do tabel: kategorie, uzytkownicy, filmy, wypozyczenia, oceny.

INSERT INTO kategorie (nazwa, opis) VALUES
  ('Akcja', 'Filmy pełne adrenaliny'),
  ('Komedia', 'Rozwesel się'),
  ('Sci-Fi', 'Nauka i fantastyka');

INSERT INTO uzytkownicy (imie, nazwisko, email, haslo_hash) VALUES
  ('Anna', 'Nowak', 'anna@mail.com', SHA2('haslo1',256)),
  ('Piotr', 'Wiśniewski', 'piotr@mail.com', SHA2('haslo2',256)),
  ('Maria', 'Kowalczyk', 'maria@mail.com', SHA2('haslo3',256));

INSERT INTO filmy (tytul, rok, cena_wyp, id_kategorii, czas_min) VALUES
  ('Matrix', 1999, 12.99, 3, 136),
  ('Interstellar', 2014, 14.99, 3, 169),
  ('Die Hard', 1988, 9.99, 1, 132);

INSERT INTO wypozyczenia (id_uzytkownika, id_filmu, data_zwrotu_plan, kwota) VALUES
  (1, 1, '2025-03-15', 12.99),
  (2, 2, '2025-03-16', 14.99),
  (1, 3, '2025-03-17', 9.99);

INSERT INTO oceny (id_uzytkownika, id_filmu, ocena, komentarz) VALUES
  (1, 1, 10, 'Kultowy film!'),
  (2, 2, 9,  'Niesamowite wizualnie'),
  (1, 3, 8,  'Klasyk akcji');

Napisz poniższe zapytania SQL:

1. Wszystkie aktywne filmy z nazwą kategorii (JOIN)
2. Top 3 filmy z najwyższą średnią oceną (GROUP BY, ORDER BY, LIMIT)
3. Łączny przychód z wypożyczeń per kategoria (JOIN, GROUP BY)

-- 1. Aktywne filmy z kategorią
SELECT f.tytul, f.rok, f.cena_wyp, k.nazwa AS kategoria
FROM filmy f
JOIN kategorie k ON f.id_kategorii = k.id_kategorii
WHERE f.aktywny = TRUE
ORDER BY f.tytul;

-- 2. Top 3 filmy po średniej ocenie
SELECT f.tytul, ROUND(AVG(o.ocena),2) AS srednia, COUNT(*) AS ile_ocen
FROM filmy f
JOIN oceny o ON f.id_filmu = o.id_filmu
GROUP BY f.id_filmu, f.tytul
ORDER BY srednia DESC
LIMIT 3;

-- 3. Przychód per kategoria
SELECT k.nazwa, SUM(w.kwota) AS przychod, COUNT(*) AS wypozyczen
FROM kategorie k
JOIN filmy f    ON k.id_kategorii = f.id_kategorii
JOIN wypozyczenia w ON f.id_filmu = w.id_filmu
GROUP BY k.id_kategorii, k.nazwa
ORDER BY przychod DESC;

Utwórz widok v_popularne_filmy pokazujący: tytuł, kategorię, średnią ocenę, liczbę wypożyczeń.

CREATE OR REPLACE VIEW v_popularne_filmy AS
SELECT
  f.tytul,
  k.nazwa          AS kategoria,
  ROUND(AVG(o.ocena),2) AS srednia_ocena,
  COUNT(DISTINCT w.id_wypozyczenia) AS liczba_wypozyczen
FROM filmy f
LEFT JOIN kategorie k    ON f.id_kategorii = k.id_kategorii
LEFT JOIN oceny o        ON f.id_filmu = o.id_filmu
LEFT JOIN wypozyczenia w ON f.id_filmu = w.id_filmu
GROUP BY f.id_filmu, f.tytul, k.nazwa
ORDER BY srednia_ocena DESC;

Utwórz użytkownika vod_app łączącego się z localhost z hasłem VodApp2024!. Nadaj mu uprawnienia SELECT, INSERT, UPDATE na całej bazie vod.

CREATE USER 'vod_app'@'localhost'
  IDENTIFIED BY 'VodApp2024!';

GRANT SELECT, INSERT, UPDATE
  ON vod.* TO 'vod_app'@'localhost';

FLUSH PRIVILEGES;