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

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

• примарни клучеви: 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 скрипта за полнење на табелите со податоци

Тригери

Погледи(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