Логички и физички дизајн

Релациска шема (мапирачка трансформација)

• примарни клучеви: bold и underline
• Надворешни клучеви: bold и * до името на надворешниот клуч, во заграда табелата кон која покажува

Шема

User(userId, firstName, lastName, email, password, role, phone, address, membershipLevel, registrationDate)

Restaurant(restaurantId, name, address, cuisineType, phone, operatingHours, website, socialMediaLinks, rating)

TableEntity(tableId, restaurantId*(Restaurant), capacity, location, isSmokingArea, description, reservationDurationHours)

Menu(menuId, restaurantId*(Restaurant), itemName, category, price, description)

MenuTag(menuTagId, menuId*(Menu), tagName, tagValue)

Reservation(reservationId, userId*(User), tableId*(TableEntity), restaurantId*(Restaurant), reservationDateTime, partySize, specialRequests, status, checkInTime, checkOutTime, paymentStatus)

PreorderedItem(preOrderedItemId, reservationId*(Reservation), name, quantity, price)

ReservationHistory(reservationHistoryId, userId*(User), tableId*(TableEntity), restaurantId*(Restaurant), reservationDateTime, partySize, specialRequests, status, cancellationReason, checkInDate)

PriceHistory(priceHistoryId, menuId*(Menu), oldPrice, changeDate)

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

DML скрипта за полнење на табелите со податоци

Тригери

CREATE OR REPLACE FUNCTION trg_validate_preorder_price()
RETURNS TRIGGER AS $$
DECLARE
    v_menu_price NUMERIC;
    v_restaurant_id INTEGER;
BEGIN
    SELECT restaurantid INTO v_restaurant_id
    FROM reservations
    WHERE reservationid = NEW.reservation_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Reservation ID % not found', NEW.reservation_id;
    END IF;

    SELECT price INTO v_menu_price
    FROM menus
    WHERE item_name = NEW.preordered_item_name AND restaurant_id = v_restaurant_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Menu item "%" not found for restaurant ID %', NEW.preordered_item_name, v_restaurant_id;
    END IF;

    IF NEW.quantity <= 0 THEN
        RAISE EXCEPTION 'Invalid quantity for item "%": must be greater than 0', NEW.preordered_item_name;
    END IF;

    IF NEW.price_at_order <> v_menu_price THEN
        RAISE EXCEPTION 'Price mismatch for item "%": expected %, got %',
            NEW.preordered_item_name, v_menu_price, NEW.price_at_order;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_preorder_price_trigger
BEFORE INSERT OR UPDATE ON reservation_preordered_items
FOR EACH ROW
EXECUTE FUNCTION trg_validate_preorder_price();

--Test cases

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (22, 'California Roll', 1, 5.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'California Roll', 1, 5.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'Roll', 1, 8.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'California Roll', 0, 8.99);

Погледи(Views)

create or replace view v_reservation_details as
select
    r.reservationid,
    u.first_name || ' ' || u.last_name as customer_name,
    res.name as restaurant_name,
    r.check_in_time,
    r.check_out_time,
    r.party_size,
    r.status,
    p.preordered_item_name,
    p.quantity,
    p.price_at_order
from reservations r
join app_user u on r.userid = u.user_id
join restaurants res on r.restaurantid = res.restaurantid
left join reservation_preordered_items p on r.reservationid = p.reservation_id;

create or replace view v_menu_with_tags as
select
    m.menu_id,
    m.item_name,
    m.description,
    m.menu_category,
    m.price,
    r.name as restaurant_name,
    t.tag_name,
    t.tag_value
from menus m
join restaurants r on m.restaurant_id = r.restaurantid
left join menu_tags t on m.menu_id = t.menu_id;

Релациски дијаграм изведен од DBeaver