= Креирање и пополнување на база \\ = Креирање на база — DDL == DDL Скрипта за креирање на табелите [attachment:schema.sql DDL] ---- == Species == Ги содржи можните видови на животни Податоците се статички и се преземаат од `species.csv` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || species_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL UNIQUE || Име на вид || || description || VARCHAR(255) || || Опис на вид || ---- == Breed == Ги содржи можните раси на животни и нивните видови Податоците се статички и се преземаат од `breed.csv` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || breed_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL || Име на раса || || description || VARCHAR(255) || || Опис на раса || || species_id || INTEGER || NOT NULL FK || Вид на животно на кое му припаѓа расата || ---- == Category == Ги содржи можните категории на производи кои се набавуваат Податоците се статички и се внесуваат со `INSERT INTO ... VALUES` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || category_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL || Име на категорија || || description || VARCHAR(255) || || Опис на категорија || ---- == Room_Type == Ги содржи можните типови на соби Податоците се статички и се внесуваат со `INSERT INTO ... VALUES` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || room_type_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL UNIQUE || Име на тип на соба || || description || VARCHAR(255) || || Опис на тип на соба || || price_per_night || NUMERIC(10, 2) || NOT NULL DEFAULT 0.00 || Цена за една ноќ || ---- == Service == Ги содржи сервисите кои ги нуди хотелот Податоците се статички и се внесуваат со `INSERT INTO ... VALUES` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || service_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL UNIQUE || Име на сервис || || description || VARCHAR(255) || || Опис на сервис || || price || NUMERIC(10, 2) || NOT NULL DEFAULT 0.00 || Цена за сервис || || duration_minutes || INTEGER || || Времетраење на сервис во минути || ---- == Enum lookup табели == Секое поле кое претходно беше слободен текст (статус, метод на плаќање, дестинација, термин) сега е поддржано со мала reference табела наместо VARCHAR, следејќи го истиот принцип како Category / Room_Type / Service — со цел референцијален интегритет наместо произволен текст. Статичките вредности се внесуваат преку `INSERT INTO ... VALUES` во `inserts.sql`, а редоследот на инсертирање го фиксира `_id`-то на секоја вредност (пр. `OrderStatus` id 1 = 'pending'). Секоја табела ја следи истата структура: {{{ {name}_id SMALLINT PRIMARY KEY (GENERATED ALWAYS AS IDENTITY) name VARCHAR(20) NOT NULL UNIQUE }}} ||= Табела =||= Можни вредности || || `OrderStatus` || pending, confirmed, completed, cancelled || || `ReservationStatus` || confirmed, completed, pending, cancelled, no-show || || `ServiceReservationStatus` || scheduled, completed, cancelled, no-show || || `PaymentMethod` || credit card, debit card, cash, bank transfer, PayPal || || `PaymentStatus` || completed, refunded, pending || || `DeliveryStatus` || pending, in_transit, delivered, failed || || `DeliveryTimeSlot` || morning, afternoon, evening, overnight || || `PetDeliveryStatus` || scheduled, delivered, failed, confirmed || || `PetDeliveryDestination` || home, hotel, vet clinic, airport || || `MedicalRecordStatus` || healthy, sick, recovering || || `DateStatus` || occupied, available, maintenance || ---- == Hotel == Ги содржи хотелите кои го користат системот Податоците се статички и се внесуваат со `INSERT INTO ... VALUES` Број на редици: 10 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || hotel_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL || Име на хотел || || location || VARCHAR(100) || UNIQUE || Локација на хотел || ---- == Employee == Ги содржи вработените во хотелот Генерирање * Имиња по случаен избор од привремени табели `first_names_male`, `first_names_female`, `last_names` * Проверка за валидност на телефонски број и електронска пошта * Датум на вработување помеѓу 2016 и 2026 Број на редици: 300 (околу 30 вработени во секој хотел) ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || employee_id || INTEGER || PRIMARY KEY || Примарен клуч || || first_name || VARCHAR(50) || NOT NULL || Име на вработен || || last_name || VARCHAR(50) || NOT NULL || Презиме на вработен || || role || VARCHAR(50) || || Работна позиција на вработен || || phone || VARCHAR(20) || || Телефонски број на вработен || || email || VARCHAR(100) || || Електронска пошта на вработен || || hire_date || DATE || || Датум на вработување || || hotel_id || INTEGER || NOT NULL DEFAULT -1 FK || Хотел во кој работи вработениот || ---- == Room == Ги содржи индивидуалните соби во даден хотел Генерирање * Број на соба по случаен избор вклучувајќи број и буква Број на редици: 2,500 (околу 250 соби во секој хотел) ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || room_id || INTEGER || PRIMARY KEY || Примарен клуч || || room_number || VARCHAR(20) || NOT NULL || Број на соба, може да содржи и букви || || capacity || INTEGER || || Број на дозволени миленичиња во соба || || room_type_id || INTEGER || NOT NULL FK || Тип на соба || || hotel_id || INTEGER || NOT NULL DEFAULT -1 FK || Хотел во кој се наоѓа собата || ---- == Date == За секој датум се чува статус на собата Генерирање * Примарен клуч претставува комбинација на `date` и `room_id` * Содржи податоци за секој датум од 2015 до 2027 Број на редици: ~11,870,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || date || DATE || NOT NULL || Датум || || room_id || INTEGER || NOT NULL FK || Соба || || status_id || SMALLINT || FK || Статус на соба за дадениот датум (occupied/available/maintenance) || ---- == Supplier == Ги содржи добавувачите на производи Податоците се статички и се внесуваат со `INSERT INTO ... VALUES` Број на редици: 5 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || supplier_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL || Име на доставувачка компанија || || contact_name || VARCHAR(100) || || Име на доставувач || || phone || VARCHAR(20) || || Телефонски број || || email || VARCHAR(100) || || Електронска пошта || || address || VARCHAR(255) || || Адреса на добавувач || ---- == Customer == Ги содржи корисниците на системот Генерирање * Имиња по случаен избор од привремени табели `first_names_male`, `first_names_female`, `last_names` * Проверка за валидност на телефонски број и електронска пошта * Датум на регистрација помеѓу 2016 и 2026 Број на редици: 150,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || customer_id || INTEGER || PRIMARY KEY || Примарен клуч || || first_name || VARCHAR(50) || NOT NULL || Име на корисник || || last_name || VARCHAR(50) || NOT NULL || Презиме на корисник || || email || VARCHAR(100) || NOT NULL UNIQUE || Електронска пошта на корисник || || phone || VARCHAR(20) || || Телефонски број на корисник || || address || VARCHAR(255) || || Адреса на корисник || || registration_date || DATE || || Датум на креирање на профил || ---- == Pet == Ги содржи миленичињата кои се внесени во системот Генерирање * Имиња и пол по случаен избор * Датум на раѓање по случаен избор Број на редици: 200,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || pet_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL || Име на милениче || || date_of_birth || DATE || || Датум на раѓање на милениче || || gender || VARCHAR(10) || || Пол на милениче || || customer_id || INTEGER || NOT NULL FK || Корисник на кој му припаѓа миленичето || || species_id || INTEGER || NOT NULL FK || Вид на животно || || breed_id || INTEGER || FK || Раса на животно || ---- == Product == Ги содржи производите кои ги добавува хотелот Генерирање * Случаен избор на име, категорија и цена Број на редици: 500 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || product_id || INTEGER || PRIMARY KEY || Примарен клуч || || name || VARCHAR(100) || NOT NULL || Име на производ || || description || VARCHAR(255) || || Опис на производ || || price || NUMERIC(10, 2) || NOT NULL DEFAULT 0.00 || Цена на производ || || category_id || INTEGER || NOT NULL FK || Категорија на производ || || supplier_id || INTEGER || FK || Доставувач на производ || ---- == "Order" == Ги содржи нарачките на производи Генерирање * Датум, статус и цена по случаен избор Број на редици: 45,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || order_id || INTEGER || PRIMARY KEY || Примарен клуч || || order_date || DATE || NOT NULL DEFAULT CURRENT_DATE || Датум на нарачка || || status_id || SMALLINT || FK || Статус на нарачка || || total_amount || NUMERIC(10, 2) || DEFAULT 0.00 || Цена на нарачка || || customer_id || INTEGER || NOT NULL FK || Корисник кој ја направил нарачката || || hotel_id || INTEGER || NOT NULL FK || Хотелот преку кој е направена нарачката || ---- == OrderProduct == За секоја нарачка се чуваат производите кои се нарачани Генерирање * Број на производи и цена по случаен избор Број на редици: ~23,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || orderproduct_id || INTEGER || PRIMARY KEY || Примарен клуч || || quantity || INTEGER || DEFAULT 1 || Број на производи од даден тип во нарачката || || unit_price || NUMERIC(10, 2) || || Цена за една инстанца од производот || || order_id || INTEGER || NOT NULL FK || Нарачка || || product_id || INTEGER || NOT NULL FK || Производ || ---- == Delivery == Ги содржи доставите на производи во нарачка '''Забелешка:''' полето претходно наречено `delivered_at` (VARCHAR) чуваше термин на достава (morning/afternoon/evening/overnight), не временска ознака — преименувано во `delivery_time_slot_id` и поврзано со lookup табела. ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || delivery_id || INTEGER || PRIMARY KEY || Примарен клуч || || delivery_time_slot_id || SMALLINT || FK || Термин на достава || || status_id || SMALLINT || FK || Статус на достава || || hotel_id || INTEGER || NOT NULL FK || Хотелот поврзан со доставата || || purchase_id || INTEGER || NOT NULL FK || Клуч до OrderProduct || ---- == Reservation == Ги содржи резервациите Генерирање * 100 batch итерации, 100,000 записи по итерација * Датуми од 2015 до 2027 * Статуси по случаен избор Број на редици: 10,000,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || reservation_id || INTEGER || PRIMARY KEY || Примарен клуч || || reservation_date || DATE || NOT NULL DEFAULT CURRENT_DATE || Датум на резервација || || status_id || SMALLINT || FK || Статус на резервација || || notes || VARCHAR(255) || || Дополнителни информации за резервацијата || || total_cost || INTEGER || || Цена на резервацијата || || pet_id || INTEGER || NOT NULL FK || Милениче на кое се однесува резервацијата || || employee_id || INTEGER || FK || Вработен кој ја извршил резервацијата || '''Дополнително ограничување:''' `chk_reservation_status CHECK (status_id IN ('1','2','3','4','5'))` — експлицитно го ограничува `status_id` на познатите вредности 1–5. Технички е донекаде redundant со FK кон `ReservationStatus` (FK веќе гарантира дека вредноста постои во lookup табелата), но служи како дополнителна експлицитна заштита на ниво на самата табела. ---- == RoomReservation == Ги содржи резервациите за хотелски соби Генерирање * Секоја резервација добива точно една соба * Времетраење од 1 до 6 ноќевања со валидни `check_in_date` и `check_out_date` Број на редици: 10,000,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || room_reservation_id || INTEGER || PRIMARY KEY || Примарен клуч || || check_in_date || DATE || NOT NULL || Датум на пристигнување во хотелот || || check_out_date || DATE || NOT NULL, CHECK > check_in_date || Датум на излегување од хотелот || || reservation_id || INTEGER || NOT NULL FK UNIQUE || Резервација || || room_id || INTEGER || NOT NULL FK || Соба || '''Дополнителни ограничувања:''' * `chk_roomres_dates CHECK (check_out_date > check_in_date)` — заминувањето мора да е по пристигнувањето. * `chk_roomres_max_stay CHECK (check_out_date - check_in_date <= 365)` — максимален престој од 365 дена, спречува очигледно погрешен/нереален внес на датуми. ---- == ServiceReservation == Ги содржи резервациите за сервиси Генерирање * ~60% од резервациите имаат сервис * `scheduled_date` се наоѓа помеѓу `check_in_date` и `check_out_date` од `RoomReservation` * `scheduled_time` се избира од неколку можни термини Број на редици: ~9,000,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || service_reservation_id || INTEGER || PRIMARY KEY || Примарен клуч || || scheduled_date || DATE || NOT NULL || Датум на резервација || || scheduled_time || TIME || || Време на резервација || || status_id || SMALLINT || FK || Статус на резервација || || reservation_id || INTEGER || NOT NULL FK || Резервација || || service_id || INTEGER || NOT NULL FK || Сервис || '''Дополнително ограничување:''' `chk_svcres_status CHECK (status_id IN ('1','2','3','4'))` — исто како кај `Reservation`, редовно се преклопува со FK кон `ServiceReservationStatus`. ---- == Employee_Service == Ги содржи вработените кои можат да извршуваат даден сервис Генерирање * Примарен клуч претставува комбинација на `employee_id` и `service_id` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || employee_id || INTEGER || NOT NULL FK || Вработен || || service_id || INTEGER || NOT NULL FK || Сервис || ---- == Product_Service == Ги содржи производите кои се користат во даден сервис Генерирање * Примарен клуч претставува комбинација на `product_id` и `service_id` ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || product_id || INTEGER || NOT NULL FK || Производ || || service_id || INTEGER || NOT NULL FK || Сервис || ---- == Payment == Ги содржи плаќањата за резервациите Генерирање * Само за резервации кои се `confirmed` или `completed` * Случаен избор за датум, статус и начин на плаќање Број на редици: ~6,000,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || payment_id || INTEGER || PRIMARY KEY || Примарен клуч || || payment_date || DATE || NOT NULL DEFAULT CURRENT_DATE || Датум на плаќање || || amount || NUMERIC(10, 2) || NOT NULL || Платена сума || || payment_method_id || SMALLINT || FK || Начин на плаќање || || status_id || SMALLINT || FK || Статус на плаќање || || reservation_id || INTEGER || FK || Резервација || '''Дополнителни ограничувања:''' * `chk_payment_source CHECK (reservation_id IS NOT NULL)` — секое плаќање мора да е поврзано со резервација. * `chk_payment_positive CHECK (amount > 0)` — сумата на плаќање мора да е позитивна, спречува внес на нула/негативни износи. ---- == Review == Ги содржи рецензиите за хотели Генерирање * Случајно избрани оцени помеѓу 1 и 10 * Пораката се избира од дадени опции зависно од оцената Број на редици: 150,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || review_id || INTEGER || PRIMARY KEY || Примарен клуч || || review_date || DATE || || Датум на оставање на рецензија || || rating || INTEGER || CHECK BETWEEN 1 AND 10 || Оцена || || comment || VARCHAR(255) || || Коментар || || customer_id || INTEGER || NOT NULL FK || Корисник кој ја оставил рецензијата || || hotel_id || INTEGER || NOT NULL FK || Хотел за кој се однесува рецензијата || ---- == MedicalRecord == Ги содржи медицинските записи за миленичињата Генерирање * Случаен избор на име на ветеринар преку привремена табела `last_names` * Случаен избор за датум, статус, алергени, лекови и дијагноза Број на редици: 300,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || record_id || INTEGER || PRIMARY KEY || Примарен клуч || || record_date || DATE || || Датум на запис || || status_id || SMALLINT || FK || Статус на запис || || vet_name || VARCHAR(50) || || Име на ветеринар || || alergies || VARCHAR(255) || || Алергии на миленичето || || medications || VARCHAR(255) || || Лекови кои ги прима миленичето || || note || VARCHAR(255) || || Дополнителни информации || || diagnosis || VARCHAR(255) || || Дијагноза на миленичето || || pet_id || INTEGER || NOT NULL FK || Милениче || ---- == PetDelivery == Ги содржи доставите на миленичиња Генерирање * Случаен избор на време на достава, статус и дестинација Број на редици: 80,000 ||= Колона =||= Тип =||= Ограничувања =||= Објаснување || || pet_delivery_id || INTEGER || PRIMARY KEY || Примарен клуч || || delivery_time || TIMESTAMP || || Време на достава || || status_id || SMALLINT || FK || Статус на достава || || destination_id || SMALLINT || FK || Дестинација на достава || || reservation_id || INTEGER || NOT NULL FK || Резервација со која е поврзана доставата || || employee_id || INTEGER || FK || Вработен кој ја врши доставата || \\ = Вметнување на податоци - DML = == DML Скрипта за вметнување на податоци [attachment:inserts.sql DML] == Статички табели == === Species, Breed === За разлика од останатите статички табели, овие '''не''' се внесени преку `INSERT INTO ... VALUES`, туку се вчитани од надворешни CSV-датотеки (`species.csv`, `breed.csv`). Мора да се вчитат пред `Pet` генерирањето, бидејќи `Pet` и `Breed` зависат од нив преку FK. === Supplier, Category, Room_Type, Service === Внесени директно преку `INSERT INTO ... VALUES` - по 5 добавувачи, 5 категории, 5 типови соби, 8 сервиси. === Enum lookup табели === Единаесет табели (`OrderStatus`, `ReservationStatus`, `ServiceReservationStatus`, `PaymentMethod`, `PaymentStatus`, `DeliveryStatus`, `DeliveryTimeSlot`, `PetDeliveryStatus`, `PetDeliveryDestination`, `MedicalRecordStatus`, `DateStatus`) - внесени преку `INSERT INTO ... VALUES`. === Hotel === 10 хотели, внесени статички. ---- == Генерирани табели == === Employee === 300 записи (~30 по хотел). Имиња по случаен избор од привремени табели `first_names_male`/`first_names_female`/`last_names`. Улога, телефон и e-mail генерирани преку `random()`; датум на вработување во опсег до ~2000 дена наназад. === Room === 2,500 записи (250 по хотел). Број на соба составен од хотелски id + буква (A/B/C) + бројка; тип на соба циклично распределен преку `Room_Type`. === Customer === 150,000 записи. Истата шема на генерирање имиња како кај Employee; адреса избрана од привремена табела `addresses`; датум на регистрација до ~1500 дена наназад. === Product === 500 записи. Име составено од фиксна листа производи + реден број; категорија мапирана според фиксен пар име->категорија; добавувач избран случајно преку `ORDER BY md5(...)`. === Pet === 200,000 записи. Име избрано од фиксна листа од 30 имиња (циклично); вид и раса избрани случајно и меѓусебно усогласени (расата секогаш припаѓа на избраниот вид); сопственик поврзан случајно со постоечки клиент. === "Order" === 45,000 записи. Датум, статус (id 1–4) и сума по случаен избор; клиент и хотел поврзани случајно. === OrderProduct === ~23,000 записи (лимитирано на 23,000 нарачки). Количина и единечна цена по случаен избор. ---- == Batch-генерирани табели == === Reservation === 10,000,000 записи, 100 итерации × 100,000. Статусите се распределени преку фиксна тежинска табела (6/15 confirmed, 4/15 completed, 2/15 pending, 2/15 cancelled, 1/15 no-show). `reservation_date` е поставен на датумот на регистрација на клиентот + случаен офсет (7–507 дена), со цел да остане конзистентен со `RoomReservation.check_in_date`. Забелешки исто така се доделуваат преку фиксна листа со псевдослучаен избор. === RoomReservation === 10,000,000 записи, 100 итерации × 100,000. Секоја резервација добива точно една соба (`UNIQUE(reservation_id)`); должина на престој 1–6 ноќи. '''Проверка по вчитување:''' {{{#!sql SELECT COUNT(*) FROM RoomReservation; -- ~10,000,000 SELECT MIN(check_in_date), MAX(check_out_date) FROM RoomReservation; -- 2015–2028 }}} === Date === ~11,870,000 записи, генерирани по опсег на `room_id` (batch од 100 соби). Два чекора по опсег: 1. '''Occupied''' ноќи - извлечени директно од `RoomReservation` преку `generate_series` на секој опсег check_in–check_out. 2. '''Available/Maintenance''' - пополнува ги преостанатите датуми (2015-01-01 до 2027-12-31) за секоја соба, со тежина 3:1 available:maintenance (`ON CONFLICT DO NOTHING`, за да не ги пребрише веќе внесените occupied редови). === ServiceReservation === ~9,000,000 записи. 60% од резервациите со собa добиваат 0 сервиси, 35% 1 сервис, 18% 2 сервиси, 7% 3 сервиси (кумулативна распределба преку `random()`). `scheduled_date` паѓа во опсегот на самиот престој; `scheduled_time` избран од 9 фиксни термини (09:00–17:00). Сервисот по резервација е избран преку детерминистичка хеш-функција од `reservation_id`. === Payment === ~6,000,000 записи, 100 итерации × 100,000. Генерирани '''само''' за резервации со `status_id IN (1, 2)` (confirmed/completed) - оттука бројот на плаќања е помал од вкупниот број резервации. Начин на плаќање и статус на плаќање избрани случајно (статусот со тежина: 4/6 completed, 1/6 refunded, 1/6 pending). ---- == Помошни/зависни табели == === Employee_Service, Product_Service === Врски многу-кон-многу, генерирани преку `generate_series` + случаен избор со `DISTINCT` (дупликатите се отфрлаат преку `ON CONFLICT DO NOTHING`), па конечниот број редови може да е нешто помал од целната бројка во `generate_series`. === Review === 150,000 записи. Оценката (1–10) е избрана случајно; коментарот се избира од една од 5 категории текст (во зависност од опсегот на оценката: 9–10, 7–8, 5–6, 3–4, 1–2), секоја со по 10 варијанти. === MedicalRecord === 300,000 записи. Име на ветеринар составено од "Dr." + случајно презиме; статус, алергии, лекови, белешки и дијагноза избрани од фиксни листи (со можност за `NULL` кај алергии/лекови/белешки). === PetDelivery === 80,000 записи (лимитирано на првите 80,000 резервации по случаен редослед). Време на достава во последните 365 дена; статус и дестинација избрани псевдослучајно. === Delivery === Генерирана за секој ред од `OrderProduct` (join кон `"Order"` за `hotel_id`) - термин и статус на достава избрани случајно. ----