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

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

---

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

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

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 скрипта е достапна тука: VidiDB_Creation.sql​

Тука може да се видат сите инсерти : console_2.sql​ console_4.sql​ console_5.sql​ console_6.sql​ console_7.sql​ console_8.sql​ console_9.sql​

3. Креирање на Погледи (Views) и нивна примена

Погледите (Views) во системот служат како апстракционен слој кој ги прикрива комплексните спојувања (JOIN наредби) помеѓу табелите. Тие овозможуваат побрз развој на апликативниот дел, стандардизиран пристап до аналитичките податоци и оптимизација на најчесто извршуваните логички процеси на платформата.

Погледи за Аналитика на Содржини и Корисничка Активност

1. view_top_rated_content

Уредно ги сумира сите медиумски содржини, пресметувајќи го нивниот просечен рејтинг и вкупниот број на гласови, додека динамички го одредува типот на содржината (филм или серија).

• Каде се користи: На почетната страница на стриминг апликацијата (Landing Page) во секциите од типот *"Најдобро оценети"* или *"Топ содржини на денот"*, како и во административниот панел за следење на трендови.
• За што служи: Да овозможи брзо филтрирање и прикажување на најпопуларните содржини според корисниците, притоа заокружувајќи ја оцената на две децимали за естетски приказ на интерфејсот.

CREATE OR REPLACE VIEW view_top_rated_content AS
SELECT
  m.ContentID,
  m.title,
  m.releaseDate,
  m.AgeRating,
  ROUND(AVG(r.RatingValue), 2) AS avg_rating,
  COUNT(r.RatingID) AS total_ratings,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type
FROM Media m
JOIN Rating r ON r.ContentContentID = m.ContentID
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
GROUP BY m.ContentID, m.title, m.releaseDate, m.AgeRating, mo.MovieID, se.SeriesID
ORDER BY avg_rating DESC;

2. view_user_watch_history

Ги спојува трансакциските записи од историјата на гледање со матичните кориснички податоци и метаподатоците за медиумите и уредите.

• Каде се користи: Во корисничкиот профил, поточно во секцијата *"Продолжи со гледање"* (Continue Watching) или *"Историја на прегледи"*.
• За што служи: Му дава увид на крајниот корисник кога последен пат гледал одредена содржина, до кој процент стигнал (Progress_percentage) и преку каков тип на уред пристапил (на пр. Smart TV, Mobile).

CREATE OR REPLACE VIEW view_user_watch_history AS
SELECT
  u.UserID,
  u.FirstName,
  u.LastName,
  m.title AS content_title,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type,
  wh.WatchedAt,
  wh.Progress_percentage,
  d.DeviceType
FROM WatchHistory wh
JOIN "User" u ON u.UserID = wh.UserUserID
JOIN Media m ON m.ContentID = wh.ContentContentID
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
LEFT JOIN Devices d ON d.DeviceID = wh.DevicesDeviceID;

3. view_content_details

Ова е најкомплексниот поглед кој врши агрегација на низи (STRING_AGG) за да ги спои сите поврзани уметници (глумци/режисери), жанрови и јазици за секој медиум посебно.

• Каде се користи: На деталната страница на кој било филм или серија (засебното корисничко мени кое се отвора пред да се кликне копчето "Play").
• За што служи: На едно место ги собира сите релевантни метаподатоци за содржината (целосен каст на артисти, јазични опции, жанрови, времетраење или број на сезони) заедно со вкупната оцена, со цел да се избегнат тешки латерални кверија при секое отворање на детали за филм.

CREATE OR REPLACE VIEW view_content_details AS
SELECT
  m.ContentID,
  m.title,
  m.releaseDate,
  m.AgeRating,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type,
  mo.Duration AS movie_duration,
  se.TotalSeasons,
  STRING_AGG(DISTINCT g.Name, ', ') AS genres,
  STRING_AGG(DISTINCT l.Name, ', ') AS languages,
  STRING_AGG(DISTINCT (a.FirstName || ' ' || a.LastName), ', ') AS artists,
  ROUND(AVG(r.RatingValue), 2) AS avg_rating,
  COUNT(DISTINCT r.RatingID) AS total_ratings
FROM Media m
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
LEFT JOIN Content_Genre cg ON cg.ContentContentID = m.ContentID
LEFT JOIN Genre g ON g.GenreID = cg.GenreGenreID
LEFT JOIN Content_Language cl ON cl.ContentContentID = m.ContentID
LEFT JOIN Language l ON l.LanguageID = cl.LanguageLanguageID
LEFT JOIN Content_Artist ca ON ca.ContentContentID = m.ContentID
LEFT JOIN Artist a ON a.ArtistID = ca.ArtistArtistID
LEFT JOIN Rating r ON r.ContentContentID = m.ContentID
GROUP BY m.ContentID, m.title, m.releaseDate, m.AgeRating, mo.MovieID, mo.Duration, se.TotalSeasons;

---

Погледи за Претплати, Структурирани Серии и Персонализација

4. view_active_subscriptions

Ги филтрира само тековно активните кориснички претплати и врши агрегација на бројот на моментално логирани уреди по претплата.

• Каде се користи: Кај апликацискиот заштитен механизам (Auth/Security Middleware) и кај сметководствениот/бизнис панелот за менаџмент на платформата.
• За што служи: Системот го користи за брза авторизација кога корисникот сака да пушти видео, проверувајќи дали претплатата му е активна, кој пакет го поседува, каква видео сесија му е дозволена и дали бројот на активни уреди ја надминува границата на пакетот (MaxDevices).

CREATE OR REPLACE VIEW view_active_subscriptions AS
SELECT
  u.UserID,
  u.FirstName,
  u.LastName,
  u.Email,
  s.Name AS subscription_plan,
  s.Price,
  s.MaxDevices,
  s.VideoQuality,
  us.Start_date,
  us.End_date,
  us.Status,
  us.Auto_renew,
  COUNT(d.DeviceID) AS active_devices
FROM User_Subscription us
JOIN "User" u ON u.UserID = us.UserUserID
JOIN Subscription s ON s.SubscriptionID = us.SubscriptionSubscriptionID
LEFT JOIN Devices d ON d.UserSubscriptionID = us.UserSubscriptionID
WHERE us.Status = 'Active'
GROUP BY u.UserID, u.FirstName, u.LastName, u.Email,
         s.Name, s.Price, s.MaxDevices, s.VideoQuality,
         us.Start_date, us.End_date, us.Status, us.Auto_renew;

5. view_series_episodes

Ја мапира целата хиерархиска структура на една серија, почнувајќи од самата серија, преку сезоните, па сè до финалните епизоди подредени по логички редослед.

• Каде се користи: Се активира кога корисникот ќе избере серија и сака да ја погледне листата на епизоди поделени по соодветни сезони (на пр. паѓачко мени за Сезона 1, Сезона 2 итн.).
• За што служи: Овозможува структуриран приказ на насловите на епизодите, нивните редни броеви и нивното времетраење, подредени точно хронолошки за полесна навигација (ORDER BY m.title, sea.SeasonNumber, e.episodeNumber).

CREATE OR REPLACE VIEW view_series_episodes AS
SELECT
  m.ContentID AS series_id,
  m.title AS series_title,
  se.TotalSeasons,
  sea.SeasonID,
  sea.SeasonNumber,
  sea.ReleaseYear,
  e.EpisodeID,
  e.episodeNumber,
  e.Title AS episode_title,
  e.Duration AS episode_duration
FROM Series se
JOIN Media m ON m.ContentID = se.SeriesID
JOIN Season sea ON sea.SeriesSeriesID = se.SeriesID
JOIN Episode e ON e.SeasonSeasonID = sea.SeasonID
ORDER BY m.title, sea.SeasonNumber, e.episodeNumber;

6. view_user_watchlist

Го отсликува персонализираниот избор на содржини за секој корисник поединечно, подредувајќи ги најновите додадени ставки најгоре.

• Каде се користи: Во корисничкото мени под опцијата *"Моја Листа"* (My Watchlist / Watch Later).
• За што служи: Овозможува брз дофат до содржините кои корисникот намерно ги зачувал за да ги гледа подоцна, прикажувајќи ги основните информации како наслов, тип на содржина и возрасна рестрикција, за брза одлука пред гледање.

CREATE OR REPLACE VIEW view_user_watchlist AS
SELECT
  u.UserID,
  u.FirstName,
  u.LastName,
  m.title AS content_title,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type,
  m.AgeRating,
  m.releaseDate,
  wl.dateAdded
FROM Watchlist wl
JOIN "User" u ON u.UserID = wl.UserUserID
JOIN Media m ON m.ContentID = wl.ContentContentID
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
ORDER BY wl.dateAdded DESC;

SQL датотеката за views може да ја погледнете тука: console_10.sql​