--Dimenzii -- DimDate - tip 1 - ne se menuva CREATE TABLE DimDate ( date_key INTEGER PRIMARY KEY, -- format YYYYMMDD full_date DATE NOT NULL, day_num SMALLINT NOT NULL, -- 1-31 day_name VARCHAR(10) NOT NULL, -- Monday, Tuesday... is_weekend BOOLEAN NOT NULL, week_num SMALLINT NOT NULL, -- 1-52 month_num SMALLINT NOT NULL, -- 1-12 month_name VARCHAR(10) NOT NULL, quarter SMALLINT NOT NULL, -- 1-4 year SMALLINT NOT NULL, season VARCHAR(10) NOT NULL -- Winter/Spring/Summer/Fall ); INSERT INTO DimDate SELECT TO_CHAR(d, 'YYYYMMDD')::INTEGER, d, EXTRACT(DAY FROM d)::SMALLINT, TO_CHAR(d, 'Day'), EXTRACT(DOW FROM d) IN (0, 6), EXTRACT(WEEK FROM d)::SMALLINT, EXTRACT(MONTH FROM d)::SMALLINT, TO_CHAR(d, 'Month'), EXTRACT(QUARTER FROM d)::SMALLINT, EXTRACT(YEAR FROM d)::SMALLINT, CASE WHEN EXTRACT(MONTH FROM d) IN (12,1,2) THEN 'Winter' WHEN EXTRACT(MONTH FROM d) IN (3,4,5) THEN 'Spring' WHEN EXTRACT(MONTH FROM d) IN (6,7,8) THEN 'Summer' ELSE 'Fall' END FROM generate_series('2015-01-01'::DATE, '2027-12-31'::DATE, '1 day') d; -- DimHotel - tip 1 - ne se menuva CREATE TABLE DimHotel ( hotel_key SERIAL PRIMARY KEY, hotel_id INTEGER NOT NULL, hotel_name VARCHAR(100) NOT NULL, city VARCHAR(100) NOT NULL, country VARCHAR(100) NOT NULL DEFAULT 'USA' ); INSERT INTO DimHotel (hotel_id, hotel_name, city) SELECT hotel_id, name, location FROM Hotel; -- DimRoomType - tip 1 - ne se menuva CREATE TABLE DimRoomType ( room_type_key SERIAL PRIMARY KEY, room_type_id INTEGER NOT NULL, type_name VARCHAR(50) NOT NULL, description TEXT, price_per_night NUMERIC(10,2) NOT NULL ); INSERT INTO DimRoomType (room_type_id, type_name, description, price_per_night) SELECT room_type_id, name, description, price_per_night FROM Room_Type; -- DimRoom - tip 2 - cenata moze da se promeni CREATE TABLE DimRoom ( room_key SERIAL PRIMARY KEY, room_id INTEGER NOT NULL, room_number VARCHAR(20) NOT NULL, capacity SMALLINT NOT NULL, room_type_key INTEGER REFERENCES DimRoomType(room_type_key), hotel_key INTEGER REFERENCES DimHotel(hotel_key), -- tip 2 koloni valid_from DATE NOT NULL DEFAULT '2015-01-01', valid_to DATE NOT NULL DEFAULT '9999-12-31', is_current BOOLEAN NOT NULL DEFAULT TRUE ); INSERT INTO DimRoom (room_id, room_number, capacity, room_type_key, hotel_key) SELECT r.room_id, r.room_number, r.capacity, rt.room_type_key, h.hotel_key FROM Room r JOIN DimRoomType rt ON rt.room_type_id = r.room_type_id JOIN DimHotel h ON h.hotel_id = r.hotel_id; -- DimSpecies + DimBreed - tip 1 - ne se menuvaat CREATE TABLE DimSpecies ( species_key SERIAL PRIMARY KEY, species_id INTEGER NOT NULL, species_name VARCHAR(50) NOT NULL, description TEXT ); INSERT INTO DimSpecies (species_id, species_name, description) SELECT species_id, name, description FROM Species; CREATE TABLE DimBreed ( breed_key SERIAL PRIMARY KEY, breed_id INTEGER NOT NULL, breed_name VARCHAR(100) NOT NULL, description TEXT, species_key INTEGER REFERENCES DimSpecies(species_key) ); INSERT INTO DimBreed (breed_id, breed_name, description, species_key) SELECT b.breed_id, b.name, b.description, s.species_key FROM Breed b JOIN DimSpecies s ON s.species_id = b.species_id; -- DimPet tip 2 - moze da se promeni CREATE TABLE DimPet ( pet_key SERIAL PRIMARY KEY, pet_id INTEGER NOT NULL, pet_name VARCHAR(100) NOT NULL, gender VARCHAR(10), date_of_birth DATE, age_group VARCHAR(20), -- 'Little', 'Adult', 'Senior' species_key INTEGER REFERENCES DimSpecies(species_key), breed_key INTEGER REFERENCES DimBreed(breed_key), -- tip 2 koloni valid_from DATE NOT NULL DEFAULT '2015-01-01', valid_to DATE NOT NULL DEFAULT '9999-12-31', is_current BOOLEAN NOT NULL DEFAULT TRUE ); INSERT INTO DimPet ( pet_id, pet_name, gender, date_of_birth, age_group, species_key, breed_key ) SELECT p.pet_id, p.name, p.gender, p.date_of_birth, CASE WHEN DATE_PART('year', AGE(p.date_of_birth)) < 1 THEN 'Baby' WHEN DATE_PART('year', AGE(p.date_of_birth)) < 3 THEN 'Young' WHEN DATE_PART('year', AGE(p.date_of_birth)) < 8 THEN 'Adult' ELSE 'Senior' END, s.species_key, b.breed_key FROM Pet p JOIN DimSpecies s ON s.species_id = p.species_id JOIN DimBreed b ON b.breed_id = p.breed_id; -- DimCustomer - tip - 2 moze da se promeni CREATE TABLE DimCustomer ( customer_key SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, full_name VARCHAR(100) NOT NULL, city VARCHAR(100), registration_date DATE, customer_segment VARCHAR(20), -- 'New', 'Regular', 'VIP' -- tip 2 koloni valid_from DATE NOT NULL DEFAULT '2015-01-01', valid_to DATE NOT NULL DEFAULT '9999-12-31', is_current BOOLEAN NOT NULL DEFAULT TRUE ); INSERT INTO DimCustomer ( customer_id, first_name, last_name, full_name, city, registration_date, customer_segment ) SELECT c.customer_id, c.first_name, c.last_name, c.first_name || ' ' || c.last_name, TRIM(SPLIT_PART(SPLIT_PART(c.address, ',', 2), ',', 1)), c.registration_date, CASE WHEN c.registration_date >= CURRENT_DATE - INTERVAL '1 year' THEN 'New' WHEN c.registration_date >= CURRENT_DATE - INTERVAL '3 years' THEN 'Regular' ELSE 'VIP' END FROM Customer c; -- DimEmployee - tip 2 - role moze da se promeni CREATE TABLE DimEmployee ( employee_key SERIAL PRIMARY KEY, employee_id INTEGER NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, full_name VARCHAR(100) NOT NULL, role VARCHAR(50) NOT NULL, hotel_key INTEGER REFERENCES DimHotel(hotel_key), hire_date DATE, -- tip 2 koloni valid_from DATE NOT NULL DEFAULT '2015-01-01', valid_to DATE NOT NULL DEFAULT '9999-12-31', is_current BOOLEAN NOT NULL DEFAULT TRUE ); INSERT INTO DimEmployee ( employee_id, first_name, last_name, full_name, role, hotel_key, hire_date ) SELECT e.employee_id, e.first_name, e.last_name, e.first_name || ' ' || e.last_name, e.role, h.hotel_key, e.hire_date FROM Employee e JOIN DimHotel h ON h.hotel_id = e.hotel_id; -- DimService - tip 1 - poretko se menuvaat uslugi CREATE TABLE DimService ( service_key SERIAL PRIMARY KEY, service_id INTEGER NOT NULL, service_name VARCHAR(100) NOT NULL, description TEXT, price NUMERIC(10,2) NOT NULL, duration_minutes INTEGER NOT NULL, price_category VARCHAR(20) -- 'Budget', 'Standard', 'Premium' ); INSERT INTO DimService ( service_id, service_name, description, price, duration_minutes, price_category ) SELECT service_id, name, description, price, duration_minutes, CASE WHEN price < 20 THEN 'Budget' WHEN price < 40 THEN 'Standard' ELSE 'Premium' END FROM Service; -- DimProduct - tip 1 - ne se menuva CREATE TABLE DimProduct ( product_key SERIAL PRIMARY KEY, product_id INTEGER NOT NULL, product_name VARCHAR(100) NOT NULL, category_name VARCHAR(50) NOT NULL, supplier_name VARCHAR(100) NOT NULL, price NUMERIC(10,2) NOT NULL, price_range VARCHAR(20) -- 'Low', 'Mid', 'High' ); INSERT INTO DimProduct ( product_id, product_name, category_name, supplier_name, price, price_range ) SELECT p.product_id, p.name, c.name, s.name, p.price, CASE WHEN p.price < 30 THEN 'Low' WHEN p.price < 70 THEN 'Mid' ELSE 'High' END FROM Product p JOIN Category c ON c.category_id = p.category_id JOIN Supplier s ON s.supplier_id = p.supplier_id;