Напредни извештаи од базата

Извршување на релациска алгебра во RelaX

За да ги извршите изразите на релациска алгебра прикажани во овој документ:

1. Отворете го RelaX онлајн алатката: ​https://dbis-uibk.github.io/relax/calc/local/uibk/local/0
2. Кликнете на табот Group Editor
3. Избришете го целиот текст внатре и залепете ја содржината на дадениот dataset.txt​.
4. Кликнете на копчето Preview
5. Ако табелите се појават на левиот панел, спремно е.
6. Преминете на табот Relational Algebra и извршете ги барањата

1. Тековен залиха по складиште

Цел: Прикажува вкупниот број производи и вредност на залихата по складишта.

SELECT
    w.warehouse_id,
    w.name AS warehouse_name,
    SUM(ws.quantity_on_hand) AS total_units,
    SUM(ws.quantity_on_hand * p.unit_price) AS total_stock_value
FROM warehouse_stock ws
JOIN warehouse w ON ws.warehouse_id = w.warehouse_id
JOIN product p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, w.name
ORDER BY total_stock_value DESC;

Релациона алгебра:

τ total_stock_value (
  γ warehouse_id, warehouse_name ; SUM(quantity_on_hand)->total_units, SUM(stock_value)->total_stock_value (
    π warehouse_id, warehouse_name, quantity_on_hand, quantity_on_hand * unit_price -> stock_value (
      (warehouse_stock ⋈ ρ name->warehouse_name (warehouse)) ⋈ ρ name->product_name (product)
    )
  )
)

2. Приход од продажба по производ

Цел: Прикажува кој производ носи најмногу приход.

SELECT
    p.product_id,
    p.name AS product_name,
    SUM(si.quantity) AS total_units_sold,
    SUM(si.quantity * si.unit_price_at_sale) AS total_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY total_revenue DESC;

Релациона алгебра:

τ total_revenue (
  γ product_id, product_name ; SUM(quantity)->total_units_sold, SUM(revenue)->total_revenue (
    π product_id, product_name, quantity, quantity * unit_price_at_sale -> revenue (
      sale_item ⋈ ρ name->product_name (product)
    )
  )
)

3. Статус на нарачки за набавка

Цел: Прикажува колку е нарачано и колку примено од секој производ.

SELECT
    po.po_id,
    po.status,
    p.name AS product_name,
    poi.quantity AS ordered_quantity,
    poi.received_quantity,
    (poi.quantity - poi.received_quantity) AS pending_quantity
FROM purchase_order po
JOIN purchase_order_item poi ON po.po_id = poi.po_id
JOIN product p ON poi.product_id = p.product_id
ORDER BY po.po_id;

Релациона алгебра:

τ po_id (
  π po_id, status, product_name, quantity->ordered_quantity, received_quantity, quantity - received_quantity -> pending_quantity (
    purchase_order ⋈ purchase_order_item ⋈ ρ name->product_name (product)
  )
)

4. Продажба по категорија

Цел: Прикажува кои категории носат најголем приход.

SELECT
    c.category_id,
    c.name AS category_name,
    SUM(si.quantity * si.unit_price_at_sale) AS total_category_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
JOIN category c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY total_category_revenue DESC;

Релациона алгебра:

τ total_category_revenue (
  γ category_id, category_name ; SUM(revenue)->total_category_revenue (
    π category_id, category_name, quantity * unit_price_at_sale -> revenue (
      sale_item ⋈ ρ name->product_name, description->product_desc (product) ⋈ ρ name->category_name, description->category_desc (category)
    )
  )
)

5. Искористеност на капацитетот на магацините

Цел: Пресметува колкав процент од физичкиот простор во секој магацин е моментално пополнет.

SELECT 
    w.name AS warehouse_name, 
    w.capacity AS total_unit_capacity,
    SUM(ws.quantity_on_hand) AS units_in_stock,
    ROUND((SUM(ws.quantity_on_hand)::NUMERIC / w.capacity) * 100, 2) AS occupancy_percentage
FROM warehouse w
LEFT JOIN warehouse_stock ws ON w.warehouse_id = ws.warehouse_id
GROUP BY w.warehouse_id, w.name, w.capacity;

Релациона алгебра:

τ occupancy_percentage (
  π warehouse_name, capacity->total_unit_capacity, units_in_stock, (units_in_stock / capacity) * 100 -> occupancy_percentage (
    γ warehouse_id, warehouse_name, capacity ; SUM(quantity_on_hand)->units_in_stock (
      ρ name->warehouse_name (warehouse) ⟕ warehouse_stock
    )
  )
)

6. Анализа на застојни производи

Цел: Ги прикажува производите што не се продале во последните 90 дена и само зафаќаат простор.

SELECT 
    p.name AS product_name, 
    p.sku, 
    ws.quantity_on_hand, 
    COALESCE(MAX(s.date_time)::TEXT, 'NO SALES RECORDED') AS last_sold_date
FROM product p
JOIN warehouse_stock ws ON p.product_id = ws.product_id
LEFT JOIN sale_item si ON p.product_id = si.product_id
LEFT JOIN sale s ON si.sale_id = s.sale_id
GROUP BY p.product_id, p.name, p.sku, ws.quantity_on_hand
HAVING MAX(s.date_time) < CURRENT_DATE - INTERVAL '90 days' OR MAX(s.date_time) IS NULL;

Релациона алгебра:

τ product_name (
  π product_name, sku, quantity_on_hand (
    (ρ name->product_name (product) ⋈ warehouse_stock)
    -
    π product_name, sku, quantity_on_hand (
      ρ name->product_name (product) ⋈ warehouse_stock ⋈ sale_item
    )
  )
)