Имплементација и Популација на Базата на Податоци (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​