Логички и физички дизајн
Релациска шема (мапирачка трансформација)
- примарни клучеви: 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
Attachments (6)
- dbeaverDijagram.jpg (81.4 KB ) - added by 6 months ago.
- final_er_norm.jpg (92.9 KB ) - added by 8 weeks ago.
- kreiranje.sql (2.8 KB ) - added by 8 weeks ago.
- polnenje.sql (1.2 KB ) - added by 8 weeks ago.
- finalDbeaverER.jpg (95.0 KB ) - added by 6 weeks ago.
- Validate preorder price trigger.txt (2.0 KB ) - added by 3 days ago.
Download all attachments as: .zip