= Имплементација и Популација на Базата на Податоци (DatabaseCreation) =

Оваа страница содржи преглед на дефинитивната DDL скрипта за креирање на релационата шема на стриминг платформата, како и детален опис на методологијата користена за генерирање на Big Data волумен на записи со цел тестирање на перформансите.

---

== 1. Дефинитивна DDL Скрипта (Структура и Ограничувања) ==

Следната скрипта го дефинира финалниот изглед на релационата база. Во неа се имплементирани строги деловни правила преку `CHECK` ограничувања (на пример: валидација на email формати, опсег на оцена од 1 до 5, прогрес од 0 до 100%, и позитивни вредности за времетраење).

{{{
#!sql
CREATE TABLE "User" (
  UserID SERIAL PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255),
  Username varchar(255),
  Email varchar(255) NOT NULL UNIQUE,
  password varchar(255),
  Date_registered date,
  SubscriptionSubscriptionID int4 NOT NULL,

  CONSTRAINT chk_user_email_format
    CHECK (Email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),

  CONSTRAINT fk_user_subscription
    FOREIGN KEY (SubscriptionSubscriptionID)
    REFERENCES Subscription (SubscriptionID)
);

--------------------------------------------------
CREATE TABLE Subscription (
  SubscriptionID SERIAL PRIMARY KEY,
  Name varchar(255),
  Price numeric(19, 0),
  MaxDevices int4,
  VideoQuality varchar(255),

  CONSTRAINT chk_subscription_price
    CHECK (Price >= 0),

  CONSTRAINT chk_max_devices
    CHECK (MaxDevices > 0)
);
--------------------------------------------------
CREATE TABLE User_Subscription (
  UserSubscriptionID SERIAL PRIMARY KEY,
  UserUserID int4 NOT NULL,
  SubscriptionSubscriptionID int4 NOT NULL,
  Start_date date,
  End_date date,
  Status varchar(255),
  Auto_renew int4,

  CONSTRAINT chk_auto_renew_bool
    CHECK (Auto_renew IN (0,1)),

  CONSTRAINT chk_subscription_dates
    CHECK (End_date IS NULL OR Start_date <= End_date),

  CONSTRAINT fk_us_user
    FOREIGN KEY (UserUserID)
    REFERENCES "User" (UserID),

  CONSTRAINT fk_us_subscription
    FOREIGN KEY (SubscriptionSubscriptionID)
    REFERENCES Subscription (SubscriptionID),

  CONSTRAINT unique_user_subscription
    UNIQUE (UserUserID, SubscriptionSubscriptionID, Start_date)
);
--------------------------------------------------
CREATE TABLE Media (
  ContentID SERIAL PRIMARY KEY,
  title varchar(255),
  description varchar(255),
  releaseDate date,
  AgeRating numeric(19, 0),

  CONSTRAINT chk_age_rating
    CHECK (AgeRating >= 0 AND AgeRating <= 21)
);
--------------------------------------------------

CREATE TABLE Movie (
  MovieID SERIAL PRIMARY KEY,
  Duration int4,

  CONSTRAINT chk_movie_duration
    CHECK (Duration > 0),

  CONSTRAINT fk_movie_media
    FOREIGN KEY (MovieID)
    REFERENCES Media (ContentID)
);

--------------------------------------------------
CREATE TABLE Series (
  SeriesID SERIAL PRIMARY KEY,
  TotalSeasons int4,

  CONSTRAINT fk_series_media
    FOREIGN KEY (SeriesID)
    REFERENCES Media (ContentID)
);
--------------------------------------------------
CREATE TABLE Season (
  SeasonID SERIAL PRIMARY KEY,
  SeasonNumber int4,
  ReleaseYear int4,
  SeriesSeriesID int4 NOT NULL,

  CONSTRAINT chk_season_number
    CHECK (SeasonNumber > 0),

  CONSTRAINT fk_season_series
    FOREIGN KEY (SeriesSeriesID)
    REFERENCES Series (SeriesID)
);
--------------------------------------------------

CREATE TABLE Episode (
  EpisodeID SERIAL PRIMARY KEY,
  Title varchar(255),
  episodeNumber int4,
  Duration int4,
  SeasonSeasonID int4 NOT NULL,

  CONSTRAINT chk_episode_duration
    CHECK (Duration > 0),

  CONSTRAINT chk_episode_number
    CHECK (episodeNumber > 0),

  CONSTRAINT fk_episode_season
    FOREIGN KEY (SeasonSeasonID)
    REFERENCES Season (SeasonID)
);

--------------------------------------------------

CREATE TABLE Watchable (
  WatchableID SERIAL PRIMARY KEY,
  duration int4,

  CONSTRAINT chk_watchable_duration
    CHECK (duration > 0),

  CONSTRAINT fk_watchable_movie
    FOREIGN KEY (WatchableID)
    REFERENCES Movie (MovieID),

  CONSTRAINT fk_watchable_episode
    FOREIGN KEY (WatchableID)
    REFERENCES Episode (EpisodeID)
);

--------------------------------------------------

CREATE TABLE Artist (
  ArtistID SERIAL PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255),
  BirthDate date,
  RoleType varchar(255)
);

--------------------------------------------------

CREATE TABLE Content_Artist (
  ContentContentID int4,
  ArtistArtistID int4,
  PRIMARY KEY (ContentContentID, ArtistArtistID),

  CONSTRAINT fk_ca_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID),

  CONSTRAINT fk_ca_artist
    FOREIGN KEY (ArtistArtistID)
    REFERENCES Artist (ArtistID)
);

--------------------------------------------------

CREATE TABLE Genre (
  GenreID SERIAL PRIMARY KEY,
  Name varchar(255)
);

CREATE TABLE Content_Genre (
  ContentContentID int4,
  GenreGenreID int4,
  PRIMARY KEY (ContentContentID, GenreGenreID),

  CONSTRAINT fk_cg_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID),

  CONSTRAINT fk_cg_genre
    FOREIGN KEY (GenreGenreID)
    REFERENCES Genre (GenreID)
);

--------------------------------------------------

CREATE TABLE Language (
  LanguageID SERIAL PRIMARY KEY,
  Name varchar(255)
);

CREATE TABLE Content_Language (
  ContentContentID int4,
  LanguageLanguageID int4,
  PRIMARY KEY (ContentContentID, LanguageLanguageID),

  CONSTRAINT fk_cl_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID),

  CONSTRAINT fk_cl_language
    FOREIGN KEY (LanguageLanguageID)
    REFERENCES Language (LanguageID)
);

--------------------------------------------------
CREATE TABLE Rating (
  RatingID SERIAL PRIMARY KEY,
  Rating_Date date,
  RatingValue int4,
  UserUserID int4 NOT NULL,
  ContentContentID int4 NOT NULL,

  CONSTRAINT chk_rating_value
    CHECK (RatingValue BETWEEN 1 AND 5),

  CONSTRAINT unique_user_content_rating
    UNIQUE (UserUserID, ContentContentID),

  CONSTRAINT fk_rating_user
    FOREIGN KEY (UserUserID)
    REFERENCES "User" (UserID),

  CONSTRAINT fk_rating_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID)
);
--------------------------------------------------

CREATE TABLE Review (
  ReviewID SERIAL PRIMARY KEY,
  Comment varchar(255),
  ReviewDate date,
  UserUserID int4 NOT NULL,
  ContentContentID int4 NOT NULL,

  CONSTRAINT fk_review_user
    FOREIGN KEY (UserUserID)
    REFERENCES "User" (UserID),

  CONSTRAINT fk_review_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID)
);

--------------------------------------------------

CREATE TABLE Devices (
  DeviceID SERIAL PRIMARY KEY,
  DeviceType varchar(255),
  LastLogIn date,
  UserSubscriptionID int4 NOT NULL,

  CONSTRAINT fk_device_subscription
    FOREIGN KEY (UserSubscriptionID)
    REFERENCES User_Subscription (UserSubscriptionID)
);

--------------------------------------------------
CREATE TABLE WatchHistory (
  HistoryID SERIAL PRIMARY KEY,
  WatchedAt date,
  Progress_percentage int4,
  UserUserID int4 NOT NULL,
  ContentContentID int4 NOT NULL,
  WatchableWatchableID int4 NOT NULL,
  DevicesDeviceID int4 NOT NULL,

  CONSTRAINT chk_progress_percentage
    CHECK (Progress_percentage BETWEEN 0 AND 100),

  CONSTRAINT fk_wh_user
    FOREIGN KEY (UserUserID)
    REFERENCES "User" (UserID),

  CONSTRAINT fk_wh_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID),

  CONSTRAINT fk_wh_watchable
    FOREIGN KEY (WatchableWatchableID)
    REFERENCES Watchable (WatchableID),

  CONSTRAINT fk_wh_device
    FOREIGN KEY (DevicesDeviceID)
    REFERENCES Devices (DeviceID)
);
--------------------------------------------------

CREATE TABLE Watchlist (
  WatchlistID SERIAL PRIMARY KEY,
  dateAdded date,
  UserUserID int4 NOT NULL,
  ContentContentID int4 NOT NULL,
  WatchableWatchableID int4 NOT NULL,

  CONSTRAINT fk_wl_user
    FOREIGN KEY (UserUserID)
    REFERENCES "User" (UserID),

  CONSTRAINT fk_wl_content
    FOREIGN KEY (ContentContentID)
    REFERENCES Media (ContentID),

  CONSTRAINT fk_wl_watchable
    FOREIGN KEY (WatchableWatchableID)
    REFERENCES Watchable (WatchableID)
);

--------------------------------------------------

CREATE TABLE User_Devices (
  UserUserID int4,
  DevicesDeviceID int4,
  PRIMARY KEY (UserUserID, DevicesDeviceID),

  CONSTRAINT fk_ud_user
    FOREIGN KEY (UserUserID)
    REFERENCES "User" (UserID),

  CONSTRAINT fk_ud_device
    FOREIGN KEY (DevicesDeviceID)
    REFERENCES Devices (DeviceID)
);
}}}

---

== 2. Стратегија и Процес за Популација на Податоци (Big Data Generation) ==

Генерирањето на релевантни податоци во вака структуриран симулациски систем беше поделено во неколку клучни чекори, користејќи комбинација од надворешно подготвени податоци и напредни програмски функции внатре во самиот DBMS (PostgreSQL). Целта беше да се достигне волумен од преку **40 милиони записи** за симулација на реално оптоварување.

=== Чекор 1: Внесување на Матични Податоци и Шифрарници ===
Првично, базата се полни со фиксни вредности за табелите кои служат како шифрарници: `Subscription` пакетите (димензионирани со соодветни цени и дозволен број уреди), основните јазици (`Language`) и филмските жанрови (`Genre`). На овој начин се овозможува понатамошно конзистентно референцирање од другите трансакциски табели.

=== Чекор 2: Импорт на Структурирани Корисници и Содржини ===
Со цел имињата, насловите и описите во базата да имаат реална форма, користен е надворешен софтвер за симулација на податоци (Mockaroo) за експорт во CSV датотеки. Преку оптимизираната `COPY FROM` наредба во PostgreSQL, во базата се внесени почетни множества на уметници, медиуми (филмови и серии) и кориснички профили.

Почетното множество на корисници програмски се мултиплицираше до **1,000,000 кориснички записи** во табелата `User` со користење на Декартов производ и функцијата `generate_series(1, 1000)`. Со овој пристап се креираа уникатни комбинации на кориснички имиња и е-маил адреси кои целосно ги задоволуваат рестрикциите во `chk_user_email_format` и `UNIQUE` дефиницијата на мејлот.

=== Чекор 3: Автоматско Креирање Логички Врски (Сезони и Watchable) ===
Наместо мануелен внес, логиката за сезони на сериите се потпира на вредноста `TotalSeasons` од табелата `Series`. Преку внатрешен `SELECT` и `generate_series` за секоја серија се изгенерираа точен број на соодветни сезони во табелата `Season`.

За задоволување на супер-тип/под-тип релацијата кај `Watchable`, извршени се две посебни `INSERT` процедури кои автоматски ги повлекоа времетраењата од соодветните филмски записи (`Movie`) и епизоди (`Episode`), распределувајќи го точниот ID опсег за да се зачува релацискиот интегритет и ограничувањето `chk_watchable_duration`.

=== Чекор 4: Масовна Дистрибуција (Доделување Жанрови, Јазици и Уметници) ===
За поврзување на табелите од тип повеќе-кон-повеќе (`Content_Genre`, `Content_Language`, `Content_Artist`), користена е конструкцијата `CROSS JOIN LATERAL` со `LIMIT (1 + random())`. Ова овозможи секој содржински запис во `Media` да добие случаен број на жанрови (од 1 до 3) или јазици, што ја рефлектира природната дистрибуција на реалните платформи.

=== Чекор 5: Генерирање на Милионски Трансакциски Податоци ===
Најголемиот волумен во базата се создаде во трансакциските табели, каде што се евидентира корисничкото однесување:

* '''Review (10,000,000 записи):''' Симулирано преку внатрешно дефинирана низа од реални коментари. Со користење на латерално поврзување и случаен избор, се генерираа 10 милиони коментари распоредени низ временска рамка од две години наназад, поврзани со точните клучни релации на `User` и `Media`.
* '''WatchHistory (10,000,000 записи):''' Податоците се генерирани со валидација на содржината (дали корисникот гледа епизода или филм), зачувувајќи го точниот уред кој е поврзан со неговата активна претплата, и генерирајќи случаен напредок (`Progress_percentage`) од 0 до 100 согласно соодветниот констреинт `chk_progress_percentage`.
* '''Rating (Преку 24,000,000 записи):''' Со цел да се создаде масивна матрица за тестирање, земен е примерок од 50,000 корисници кои се споени со сите достапни медиуми во базата преку `CROSS JOIN`. Вредностите за рејтингот се генерирани случајно во опсег од 1 до 5 согласно ограничувањето `chk_rating_value`. Благодарение на клаузулата `ON CONFLICT DO NOTHING` и уникатниот индекс `unique_user_content_rating`, успешно се избегнати дупликати во комбинацијата корисник-медиум.

> '''Заклучок за перформанси:''' Сите овие масовни трансакциски табели намерно се доведени до толкав волумен со цел во следниот чекор да се демонстрира клучната улога на '''индексирањето''' (Indexing) и извршните планови (`EXPLAIN ANALYZE`), каде што соодветно поставените индекси драстично го намалуваат времето на извршување на комплексните кверија од неколку часа на неколку милисекунди.

=== SQL Датотеки ==
Оригиналната SQL скрипта е достапна тука: [attachment:VidiDB_Creation.sql VidiDB_Creation.sql]
Тука може да се видат сите инсерти : [attachment:console_2.sql console_2.sql] [attachment:console_4.sql console_4.sql] [attachment:console_5.sql console_5.sql]
[attachment:console_6.sql console_6.sql] [attachment:console_7.sql console_7.sql] [attachment:console_8.sql console_8.sql] [attachment:console_9.sql console_9.sql]