wiki:DatabaseCreation

Version 5 (modified by 231178, 4 days ago) ( diff )

Додадена документација за DDL

Креирање и пополнување на база


Креирање на база — DDL

DDL Скрипта за креирање на табелите

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 Скрипта за вметнување на податоци

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 ноќи.

Проверка по вчитување: 

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) - термин и статус на достава избрани случајно.

Attachments (2)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.