wehub-resource-sync
2196 lines
63 KiB
PL/PgSQL
2196 lines
63 KiB
PL/PgSQL
-- Downloaded from: https://github.com/MostafaProgramming/100719549/blob/dde5242880a860cda0275c4ccfcf5b33a9fa4e40/db_backup.sql
|
|
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
-- Dumped from database version 17.0
|
|
-- Dumped by pg_dump version 17.0
|
|
|
|
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET transaction_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET xmloption = content;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
--
|
|
-- Name: decrease_stock_level(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.decrease_stock_level() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
-- Ensure stock level does not go below zero
|
|
IF (SELECT stock_level FROM product WHERE product_id = NEW.product_id) < NEW.quantity_sold THEN
|
|
RAISE EXCEPTION 'Insufficient stock for product_id %', NEW.product_id;
|
|
END IF;
|
|
|
|
-- Decrease the stock level for the product
|
|
UPDATE product
|
|
SET stock_level = stock_level - NEW.quantity_sold
|
|
WHERE product_id = NEW.product_id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.decrease_stock_level() OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: update_delivery_date(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.update_delivery_date() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
-- If status is changed to 'Completed', set delivery_date to current date
|
|
IF NEW.order_status = 'Completed' THEN
|
|
NEW.delivery_date = CURRENT_DATE;
|
|
-- If status is not 'Completed', set delivery_date to NULL
|
|
ELSE
|
|
NEW.delivery_date = NULL;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.update_delivery_date() OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: update_inventory_on_purchase_order(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.update_inventory_on_purchase_order() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
-- Check if the status is being changed to 'Completed'
|
|
IF NEW.order_status = 'Completed' AND OLD.order_status != 'Completed' THEN
|
|
-- Update the inventory table for the respective location and product
|
|
UPDATE inventory
|
|
SET quantity = quantity + NEW.quantity,
|
|
last_updated = NOW()
|
|
WHERE product_id = NEW.product_id AND location_id = NEW.location_id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.update_inventory_on_purchase_order() OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: update_inventory_on_sale(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.update_inventory_on_sale() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
-- Check if the product exists in the inventory for the given store
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM inventory
|
|
WHERE product_id = NEW.product_id AND location_id = NEW.store_id
|
|
) THEN
|
|
-- Update the inventory quantity for the product at the store
|
|
UPDATE inventory
|
|
SET quantity = quantity - NEW.quantity_sold,
|
|
last_updated = NOW()
|
|
WHERE product_id = NEW.product_id AND location_id = NEW.store_id;
|
|
ELSE
|
|
-- If the inventory record doesn't exist for the store, raise an error
|
|
RAISE EXCEPTION 'Product % not found in inventory for store %', NEW.product_id, NEW.store_id;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.update_inventory_on_sale() OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: update_inventory_on_transfer(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.update_inventory_on_transfer() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
-- Reduce stock at the source location
|
|
UPDATE inventory
|
|
SET quantity = quantity - NEW.quantity,
|
|
last_updated = NOW()
|
|
WHERE product_id = NEW.product_id AND location_id = NEW.source_location_id;
|
|
|
|
-- Check if the product exists in the inventory at the destination location
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM inventory
|
|
WHERE product_id = NEW.product_id AND location_id = NEW.destination_location_id
|
|
) THEN
|
|
-- Increase stock at the destination location
|
|
UPDATE inventory
|
|
SET quantity = quantity + NEW.quantity,
|
|
last_updated = NOW()
|
|
WHERE product_id = NEW.product_id AND location_id = NEW.destination_location_id;
|
|
ELSE
|
|
-- If the product does not exist in the destination, create a new record
|
|
INSERT INTO inventory (product_id, location_id, quantity, last_updated)
|
|
VALUES (NEW.product_id, NEW.destination_location_id, NEW.quantity, NOW());
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.update_inventory_on_transfer() OWNER TO postgres;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- Name: attendance; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.attendance (
|
|
attendance_id integer NOT NULL,
|
|
person_id integer,
|
|
date date,
|
|
status character varying(25)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.attendance OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: attendance_attendance_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.attendance_attendance_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.attendance_attendance_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: attendance_attendance_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.attendance_attendance_id_seq OWNED BY public.attendance.attendance_id;
|
|
|
|
|
|
--
|
|
-- Name: budget; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.budget (
|
|
budget_id integer NOT NULL,
|
|
department_id integer,
|
|
allocated_amount numeric(10,2),
|
|
start_date date DEFAULT CURRENT_DATE,
|
|
end_date date,
|
|
description character varying(256),
|
|
category character varying(256)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.budget OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: budget_budget_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.budget_budget_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.budget_budget_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: budget_budget_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.budget_budget_id_seq OWNED BY public.budget.budget_id;
|
|
|
|
|
|
--
|
|
-- Name: department; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.department (
|
|
department_id integer NOT NULL,
|
|
department_name character varying(50),
|
|
budget numeric(10,2),
|
|
manager_id integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.department OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: department_department_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.department_department_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.department_department_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: department_department_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.department_department_id_seq OWNED BY public.department.department_id;
|
|
|
|
|
|
--
|
|
-- Name: employee; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.employee (
|
|
employee_id integer NOT NULL,
|
|
person_id integer,
|
|
absences integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.employee OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: employee_employee_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.employee_employee_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.employee_employee_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: employee_employee_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.employee_employee_id_seq OWNED BY public.employee.employee_id;
|
|
|
|
|
|
--
|
|
-- Name: executive; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.executive (
|
|
executive_id integer NOT NULL,
|
|
person_id integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.executive OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: executive_executive_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.executive_executive_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.executive_executive_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: executive_executive_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.executive_executive_id_seq OWNED BY public.executive.executive_id;
|
|
|
|
|
|
--
|
|
-- Name: expenses; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.expenses (
|
|
expense_id integer NOT NULL,
|
|
amount numeric(10,2),
|
|
department_id integer,
|
|
budget_id integer,
|
|
category character varying(50),
|
|
date_of_expense date,
|
|
description character varying(100),
|
|
person_id integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.expenses OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: expenses_expense_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.expenses_expense_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.expenses_expense_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: expenses_expense_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.expenses_expense_id_seq OWNED BY public.expenses.expense_id;
|
|
|
|
|
|
--
|
|
-- Name: financial_report; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.financial_report (
|
|
report_id integer NOT NULL,
|
|
department_id integer,
|
|
store_id integer,
|
|
report_type character varying(25),
|
|
generated_date date,
|
|
period_start date,
|
|
period_end date,
|
|
total_expenses numeric(10,2),
|
|
total_revenue numeric(10,2),
|
|
net_profit numeric(10,2)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.financial_report OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: financialreport_report_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.financialreport_report_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.financialreport_report_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: financialreport_report_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.financialreport_report_id_seq OWNED BY public.financial_report.report_id;
|
|
|
|
|
|
--
|
|
-- Name: inventory; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.inventory (
|
|
inventory_id integer NOT NULL,
|
|
product_id integer,
|
|
location_id integer,
|
|
quantity integer,
|
|
last_updated date
|
|
);
|
|
|
|
|
|
ALTER TABLE public.inventory OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: inventory_inventory_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.inventory_inventory_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.inventory_inventory_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: inventory_inventory_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.inventory_inventory_id_seq OWNED BY public.inventory.inventory_id;
|
|
|
|
|
|
--
|
|
-- Name: location; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.location (
|
|
location_id integer NOT NULL,
|
|
location_name character varying(25),
|
|
location character varying(100),
|
|
manager_id integer,
|
|
contact_number character varying(15)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.location OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: location_location_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.location_location_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.location_location_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: location_location_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.location_location_id_seq OWNED BY public.location.location_id;
|
|
|
|
|
|
--
|
|
-- Name: manager; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.manager (
|
|
manager_id integer NOT NULL,
|
|
person_id integer,
|
|
absences integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.manager OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: manager_manager_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.manager_manager_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.manager_manager_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: manager_manager_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.manager_manager_id_seq OWNED BY public.manager.manager_id;
|
|
|
|
|
|
--
|
|
-- Name: payroll; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.payroll (
|
|
payroll_id integer NOT NULL,
|
|
person_id integer,
|
|
department_id integer,
|
|
budget_id integer,
|
|
salary numeric(10,2),
|
|
bonuses numeric(10,2),
|
|
deductions numeric(10,2),
|
|
net_salary numeric(10,2),
|
|
pay_date date
|
|
);
|
|
|
|
|
|
ALTER TABLE public.payroll OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: payroll_payroll_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.payroll_payroll_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.payroll_payroll_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: payroll_payroll_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.payroll_payroll_id_seq OWNED BY public.payroll.payroll_id;
|
|
|
|
|
|
--
|
|
-- Name: person; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.person (
|
|
person_id integer NOT NULL,
|
|
name character varying(100),
|
|
email character varying(100),
|
|
role character varying(50),
|
|
salary numeric(10,2),
|
|
department_id integer,
|
|
location_id integer,
|
|
hire_date date,
|
|
location character varying(100)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.person OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: person_person_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.person_person_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.person_person_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: person_person_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.person_person_id_seq OWNED BY public.person.person_id;
|
|
|
|
|
|
--
|
|
-- Name: product; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.product (
|
|
product_id integer NOT NULL,
|
|
product_name character varying(100),
|
|
category character varying(50),
|
|
price numeric(10,2),
|
|
stock_level integer,
|
|
reorder_level integer,
|
|
last_purchase_date date,
|
|
supplier_id integer,
|
|
cost numeric(10,2)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.product OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: product_product_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.product_product_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.product_product_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: product_product_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.product_product_id_seq OWNED BY public.product.product_id;
|
|
|
|
|
|
--
|
|
-- Name: purchase_order; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.purchase_order (
|
|
purchase_order_id integer NOT NULL,
|
|
supplier_id integer,
|
|
product_id integer,
|
|
order_date date,
|
|
delivery_date date,
|
|
total_amount numeric(10,2),
|
|
order_status character varying(25),
|
|
quantity integer DEFAULT 1 NOT NULL,
|
|
location_id integer,
|
|
CONSTRAINT purchase_order_status_check CHECK (((order_status)::text = ANY ((ARRAY['Pending'::character varying, 'Completed'::character varying, 'Cancelled'::character varying, 'Awaiting Approval'::character varying, 'Late'::character varying])::text[])))
|
|
);
|
|
|
|
|
|
ALTER TABLE public.purchase_order OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: purchaseorder_purchase_order_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.purchaseorder_purchase_order_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.purchaseorder_purchase_order_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: purchaseorder_purchase_order_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.purchaseorder_purchase_order_id_seq OWNED BY public.purchase_order.purchase_order_id;
|
|
|
|
|
|
--
|
|
-- Name: sales_forecast; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.sales_forecast (
|
|
forecast_id integer NOT NULL,
|
|
store_id integer,
|
|
product_id integer,
|
|
start_date date,
|
|
end_date date,
|
|
predicted_sales integer,
|
|
created_date date
|
|
);
|
|
|
|
|
|
ALTER TABLE public.sales_forecast OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: sales_record; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.sales_record (
|
|
sales_id integer NOT NULL,
|
|
store_id integer,
|
|
manager_id integer,
|
|
product_id integer,
|
|
quantity_sold integer,
|
|
date_of_sale date,
|
|
total_amount numeric(10,2),
|
|
payment_method character varying(25)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.sales_record OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: salesforecast_forecast_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.salesforecast_forecast_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.salesforecast_forecast_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: salesforecast_forecast_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.salesforecast_forecast_id_seq OWNED BY public.sales_forecast.forecast_id;
|
|
|
|
|
|
--
|
|
-- Name: salesrecord_sales_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.salesrecord_sales_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.salesrecord_sales_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: salesrecord_sales_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.salesrecord_sales_id_seq OWNED BY public.sales_record.sales_id;
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.stock_transfer (
|
|
transfer_id integer NOT NULL,
|
|
source_location_id integer,
|
|
destination_location_id integer,
|
|
product_id integer,
|
|
quantity integer,
|
|
transfer_date date,
|
|
status character varying(25)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.stock_transfer OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: stocktransfer_transfer_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.stocktransfer_transfer_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.stocktransfer_transfer_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: stocktransfer_transfer_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.stocktransfer_transfer_id_seq OWNED BY public.stock_transfer.transfer_id;
|
|
|
|
|
|
--
|
|
-- Name: store; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.store (
|
|
store_id integer NOT NULL,
|
|
location_id integer,
|
|
operating_hours character varying(50)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.store OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: store_store_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.store_store_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.store_store_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: store_store_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.store_store_id_seq OWNED BY public.store.store_id;
|
|
|
|
|
|
--
|
|
-- Name: supplier; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.supplier (
|
|
supplier_id integer NOT NULL,
|
|
supplier_name character varying(50),
|
|
contact_details character varying(25),
|
|
location character varying(25),
|
|
contract_terms character varying(250)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.supplier OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: supplier_supplier_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.supplier_supplier_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.supplier_supplier_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: supplier_supplier_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.supplier_supplier_id_seq OWNED BY public.supplier.supplier_id;
|
|
|
|
|
|
--
|
|
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.users (
|
|
user_id integer NOT NULL,
|
|
person_id integer,
|
|
email character varying(50),
|
|
password character varying(255),
|
|
role character varying(25),
|
|
location_id integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.users OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: users_user_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.users_user_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.users_user_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: users_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.users_user_id_seq OWNED BY public.users.user_id;
|
|
|
|
|
|
--
|
|
-- Name: warehouse; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.warehouse (
|
|
warehouse_id integer NOT NULL,
|
|
location_id integer,
|
|
capacity integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.warehouse OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: warehouse_warehouse_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.warehouse_warehouse_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.warehouse_warehouse_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: warehouse_warehouse_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.warehouse_warehouse_id_seq OWNED BY public.warehouse.warehouse_id;
|
|
|
|
|
|
--
|
|
-- Name: attendance attendance_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.attendance ALTER COLUMN attendance_id SET DEFAULT nextval('public.attendance_attendance_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: budget budget_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.budget ALTER COLUMN budget_id SET DEFAULT nextval('public.budget_budget_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: department department_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.department ALTER COLUMN department_id SET DEFAULT nextval('public.department_department_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: employee employee_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.employee ALTER COLUMN employee_id SET DEFAULT nextval('public.employee_employee_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: executive executive_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.executive ALTER COLUMN executive_id SET DEFAULT nextval('public.executive_executive_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: expenses expense_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses ALTER COLUMN expense_id SET DEFAULT nextval('public.expenses_expense_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: financial_report report_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.financial_report ALTER COLUMN report_id SET DEFAULT nextval('public.financialreport_report_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: inventory inventory_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.inventory ALTER COLUMN inventory_id SET DEFAULT nextval('public.inventory_inventory_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: location location_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.location ALTER COLUMN location_id SET DEFAULT nextval('public.location_location_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: manager manager_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.manager ALTER COLUMN manager_id SET DEFAULT nextval('public.manager_manager_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: payroll payroll_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.payroll ALTER COLUMN payroll_id SET DEFAULT nextval('public.payroll_payroll_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: person person_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.person ALTER COLUMN person_id SET DEFAULT nextval('public.person_person_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: product product_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.product ALTER COLUMN product_id SET DEFAULT nextval('public.product_product_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: purchase_order purchase_order_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.purchase_order ALTER COLUMN purchase_order_id SET DEFAULT nextval('public.purchaseorder_purchase_order_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: sales_forecast forecast_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_forecast ALTER COLUMN forecast_id SET DEFAULT nextval('public.salesforecast_forecast_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: sales_record sales_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_record ALTER COLUMN sales_id SET DEFAULT nextval('public.salesrecord_sales_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer transfer_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.stock_transfer ALTER COLUMN transfer_id SET DEFAULT nextval('public.stocktransfer_transfer_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: store store_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.store ALTER COLUMN store_id SET DEFAULT nextval('public.store_store_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: supplier supplier_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.supplier ALTER COLUMN supplier_id SET DEFAULT nextval('public.supplier_supplier_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: users user_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.users ALTER COLUMN user_id SET DEFAULT nextval('public.users_user_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: warehouse warehouse_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.warehouse ALTER COLUMN warehouse_id SET DEFAULT nextval('public.warehouse_warehouse_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Data for Name: attendance; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.attendance (attendance_id, person_id, date, status) FROM stdin;
|
|
1 3 2025-01-02 Present
|
|
2 3 2025-01-01 Absent
|
|
3 3 2024-12-31 Absent
|
|
4 3 2025-01-03 Present
|
|
5 2 2025-01-02 Present
|
|
6 3 2025-01-02 Absent
|
|
7 4 2025-01-01 Present
|
|
8 4 2025-01-02 Present
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: budget; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.budget (budget_id, department_id, allocated_amount, start_date, end_date, description, category) FROM stdin;
|
|
104 2 15000.00 2025-01-01 2025-12-31 Budget for job postings, recruitment drives, and onboarding Recruitment
|
|
105 2 20000.00 2025-01-01 2025-12-31 Budget for employee upskilling and training programs Employee Training
|
|
106 2 10000.00 2025-01-01 2025-12-31 Budget for team-building activities and welfare programs Employee Welfare
|
|
110 4 25000.00 2025-01-01 2025-12-31 Budget for vendor-related activities and agreements Vendor Management
|
|
111 4 50000.00 2025-01-01 2025-12-31 Budget for warehouse and stock handling costs Inventory Management
|
|
112 4 15000.00 2025-01-01 2025-12-31 Budget for office and operational supplies Supplies
|
|
113 5 20000.00 2025-01-01 2025-12-31 Budget for electricity, water, and other utilities Utilities
|
|
114 5 30000.00 2025-01-01 2025-12-31 Budget for transportation and delivery costs Logistics
|
|
115 5 20000.00 2025-01-01 2025-12-31 Budget for equipment and infrastructure maintenance Maintenance
|
|
107 1 30000.00 2025-01-01 2025-12-31 Travel budget for sales personnel Travel
|
|
108 1 40000.00 2025-01-01 2025-12-31 Budget for promotional campaigns and marketing materials Promotions
|
|
109 1 15000.00 2025-01-01 2025-12-31 Budget for client dinners and meetings Client Entertainment
|
|
101 3 50000.00 2025-01-01 2025-12-31 Annual budget for financial research Financial Research
|
|
102 3 30000.00 2025-01-01 2025-12-31 Budget for getting projected forecasts Forecasting Methods
|
|
103 3 20000.00 2025-01-01 2025-12-31 Budget for Outsourcing Outsourcing Finance
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: department; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.department (department_id, department_name, budget, manager_id) FROM stdin;
|
|
5 Operations 700000.00 \N
|
|
1 Sales 500000.00 6
|
|
2 HR 350000.00 7
|
|
3 Finance 600000.00 8
|
|
4 Procurement 600000.00 9
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: employee; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.employee (employee_id, person_id, absences) FROM stdin;
|
|
4 6 0
|
|
6 17 0
|
|
7 18 0
|
|
8 19 0
|
|
9 20 0
|
|
10 21 0
|
|
11 22 0
|
|
12 23 0
|
|
13 24 0
|
|
14 25 0
|
|
15 26 0
|
|
16 27 0
|
|
17 28 0
|
|
18 29 0
|
|
19 30 0
|
|
20 31 0
|
|
1 1 0
|
|
2 2 0
|
|
3 3 0
|
|
5 10 0
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: executive; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.executive (executive_id, person_id) FROM stdin;
|
|
1 7
|
|
2 8
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: expenses; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.expenses (expense_id, amount, department_id, budget_id, category, date_of_expense, description, person_id) FROM stdin;
|
|
26 5000.00 3 101 Financial Research 2025-01-15 Consultation with financial analysts 15
|
|
27 3000.00 3 102 Forecasting Methods 2025-02-01 Workshop on forecasting techniques 19
|
|
28 5000.00 3 103 Outsourcing Finance 2025-03-10 Payment to outsourced accounting firm 15
|
|
29 4000.00 2 104 Recruitment 2025-01-20 Job advertisement costs for new hires 14
|
|
30 3000.00 2 105 Employee Training 2025-02-15 Workshop for upskilling employees 14
|
|
31 2000.00 2 106 Employee Welfare 2025-03-10 Snacks and refreshments for team-building events 17
|
|
32 5000.00 1 107 Travel 2025-01-25 Travel costs for client meetings 22
|
|
33 10000.00 1 108 Promotions 2025-02-20 Social media and online campaigns 24
|
|
34 3000.00 1 109 Client Entertainment 2025-03-05 Client dinner at a corporate event 13
|
|
35 8000.00 5 110 Vendor Management 2025-01-12 Annual vendor contract renewal fees 4
|
|
36 12000.00 5 111 Inventory Management 2025-02-01 Restocking of warehouse supplies 12
|
|
37 1500.00 5 112 Supplies 2025-02-18 Office supplies for operations 5
|
|
38 2000.00 5 113 Utilities 2025-03-10 Electricity bill for March 9
|
|
39 4000.00 5 114 Logistics 2025-03-15 Transportation costs for product deliveries 11
|
|
40 5000.00 5 115 Maintenance 2025-03-25 Equipment servicing and repair 9
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: financial_report; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.financial_report (report_id, department_id, store_id, report_type, generated_date, period_start, period_end, total_expenses, total_revenue, net_profit) FROM stdin;
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: inventory; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.inventory (inventory_id, product_id, location_id, quantity, last_updated) FROM stdin;
|
|
1 1001 1 120 2025-01-01
|
|
2 1001 2 100 2025-01-01
|
|
3 1001 3 80 2025-01-01
|
|
4 1001 4 80 2025-01-01
|
|
5 1001 5 88 2025-01-01
|
|
6 1002 1 30 2025-01-01
|
|
7 1002 2 25 2025-01-01
|
|
8 1002 3 20 2025-01-01
|
|
10 1002 5 29 2025-01-01
|
|
12 1003 2 50 2025-01-01
|
|
13 1003 3 40 2025-01-01
|
|
15 1003 5 50 2025-01-01
|
|
16 1005 1 50 2025-01-01
|
|
17 1005 2 40 2025-01-01
|
|
18 1005 3 40 2025-01-01
|
|
19 1005 4 50 2025-01-01
|
|
21 1007 1 15 2025-01-01
|
|
22 1007 2 15 2025-01-01
|
|
23 1007 3 10 2025-01-01
|
|
24 1007 4 20 2025-01-01
|
|
25 1007 5 19 2025-01-01
|
|
26 1009 1 40 2025-01-01
|
|
27 1009 2 35 2025-01-01
|
|
28 1009 3 30 2025-01-01
|
|
29 1009 4 40 2025-01-01
|
|
30 1009 5 39 2025-01-01
|
|
31 1010 1 70 2025-01-01
|
|
32 1010 2 60 2025-01-01
|
|
33 1010 3 60 2025-01-01
|
|
34 1010 4 70 2025-01-01
|
|
35 1010 5 68 2025-01-01
|
|
36 1004 1 130 2025-01-01
|
|
37 1004 2 120 2025-01-01
|
|
38 1004 3 120 2025-01-01
|
|
39 1004 4 130 2025-01-01
|
|
41 1008 1 80 2025-01-01
|
|
42 1008 2 75 2025-01-01
|
|
43 1008 3 70 2025-01-01
|
|
44 1008 4 75 2025-01-01
|
|
45 1008 5 76 2025-01-01
|
|
47 1006 2 8 2025-01-01
|
|
48 1006 3 6 2025-01-01
|
|
49 1006 4 8 2025-01-01
|
|
50 1006 5 10 2025-01-01
|
|
46 1006 1 5 2025-01-05
|
|
11 1003 1 60 2025-01-05
|
|
14 1003 4 60 2025-01-05
|
|
9 1002 4 49 2025-01-05
|
|
40 1004 5 148 2025-01-05
|
|
20 1005 5 57 2025-01-05
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: location; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.location (location_id, location_name, location, manager_id, contact_number) FROM stdin;
|
|
1 Las Vegas Store Las Vegas, NV 1 702-555-0100
|
|
2 Sydney Store Sydney, Australia 2 61-2-5550-1234
|
|
5 New York Warehouse New York, NY 3 212-555-0200
|
|
3 New York Store New York, NY 4 212-555-0200
|
|
4 Washington Warehouse Washington 5 212-555-0200
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: manager; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.manager (manager_id, person_id, absences) FROM stdin;
|
|
1 4 1
|
|
2 5 0
|
|
3 9 2
|
|
4 11 0
|
|
5 12 0
|
|
6 13 0
|
|
7 14 0
|
|
8 15 0
|
|
9 16 0
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: payroll; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.payroll (payroll_id, person_id, department_id, budget_id, salary, bonuses, deductions, net_salary, pay_date) FROM stdin;
|
|
6 6 2 108 76000.00 4500.00 2000.00 78500.00 2025-01-31
|
|
7 7 5 113 58000.00 2000.00 500.00 59500.00 2025-01-31
|
|
8 8 5 113 60000.00 1000.00 800.00 60200.00 2025-01-31
|
|
9 9 5 113 55000.00 1500.00 700.00 55800.00 2025-01-31
|
|
10 10 4 106 80000.00 5000.00 3000.00 82000.00 2025-01-31
|
|
11 1 3 101 90000.00 3000.00 2000.00 91000.00 2024-12-31
|
|
12 2 3 101 95000.00 3500.00 2500.00 96000.00 2024-12-31
|
|
13 3 1 102 78000.00 3000.00 2000.00 79000.00 2024-11-30
|
|
14 4 2 105 77000.00 2500.00 3000.00 76500.00 2024-11-30
|
|
15 5 4 106 74000.00 2000.00 1000.00 75000.00 2024-10-31
|
|
16 6 2 108 76000.00 3500.00 1500.00 78000.00 2024-09-30
|
|
17 7 5 113 58000.00 1000.00 500.00 58500.00 2024-12-31
|
|
18 8 5 113 60000.00 1200.00 800.00 60400.00 2024-11-30
|
|
19 9 5 113 55000.00 1000.00 600.00 55400.00 2024-10-31
|
|
20 10 4 106 80000.00 4000.00 2500.00 81500.00 2024-08-31
|
|
21 11 1 102 85000.00 2500.00 1500.00 86000.00 2025-01-31
|
|
22 12 2 108 50000.00 2000.00 800.00 51200.00 2024-12-31
|
|
23 13 3 101 47000.00 3000.00 1000.00 49000.00 2024-12-31
|
|
24 14 5 113 75000.00 4000.00 3000.00 76000.00 2024-12-31
|
|
25 15 4 106 72000.00 3000.00 2500.00 72500.00 2024-11-30
|
|
26 16 5 113 42000.00 2000.00 500.00 43500.00 2024-10-31
|
|
27 17 4 106 75000.00 4500.00 2000.00 77500.00 2024-09-30
|
|
28 18 1 102 56000.00 2000.00 500.00 57500.00 2025-01-31
|
|
29 19 1 102 60000.00 3000.00 1500.00 61500.00 2025-01-31
|
|
30 20 5 113 43000.00 1000.00 700.00 42300.00 2024-12-31
|
|
1 1 5 101 90000.00 5000.00 2000.00 93000.00 2025-01-31
|
|
2 2 5 101 95000.00 4000.00 3000.00 96000.00 2025-01-31
|
|
3 3 5 102 78000.00 3000.00 2500.00 78500.00 2025-01-31
|
|
4 4 5 105 77000.00 4000.00 3000.00 78000.00 2025-01-31
|
|
5 5 5 106 74000.00 2000.00 1000.00 75000.00 2025-01-31
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: person; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.person (person_id, name, email, role, salary, department_id, location_id, hire_date, location) FROM stdin;
|
|
7 Valentina Shevchenko valentina.shevchenko@example.com Executive 90000.00 \N \N 2017-05-30 London
|
|
8 Francis Ngannou francis.ngannou@example.com Executive 95000.00 \N \N 2016-04-12 London
|
|
13 Deontay Wilder deontay.wilder@example.com Sales Manager 78000.00 1 \N 2019-09-10 London
|
|
14 Anthony Joshua anthony.joshua@example.com HR Manager 77000.00 2 \N 2021-03-22 London
|
|
15 Vasiliy Lomachenko vasiliy.lomachenko@example.com Finance Manager 76000.00 3 \N 2017-12-05 London
|
|
16 Gervonta Davis gervonta.davis@example.com Procurement Manager 74000.00 4 \N 2022-01-20 London
|
|
17 Lionel Messi lionel.messi@example.com HR Employee 55000.00 2 \N 2021-02-10 London
|
|
18 Cristiano Ronaldo cristiano.ronaldo@example.com HR Employee 56000.00 2 \N 2020-11-25 London
|
|
19 Neymar Jr neymar.jr@example.com Finance Employee 54000.00 3 \N 2022-07-18 London
|
|
20 Kylian Mbappe kylian.mbappe@example.com Procurement Employee 58000.00 4 \N 2019-01-12 London
|
|
21 Robert Lewandowski robert.lewandowski@example.com Procurement Employee 57000.00 4 \N 2020-08-05 London
|
|
22 Kevin De Bruyne kevin.debruyne@example.com Sales Employee 55000.00 1 \N 2021-06-15 London
|
|
23 Virgil Van Dijk virgil.vandijk@example.com Sales Employee 60000.00 1 \N 2018-10-23 London
|
|
24 Mohamed Salah mohamed.salah@example.com Sales Employee 58000.00 1 \N 2022-02-18 London
|
|
1 Conor McGregor conor.mcgregor@example.com Store Employee 85000.00 5 1 2021-01-15 Las Vegas
|
|
2 Israel Adesanya israel.adesanya@example.com Store Employee 42000.00 5 1 2020-11-12 Las Vegas
|
|
3 Kamaru Usman kamaru.usman@example.com Store Employee 47000.00 5 2 2022-03-01 Sydney
|
|
4 Amanda Nunes amanda.nunes@example.com Store Manager 75000.00 5 1 2019-06-01 Las Vegas
|
|
5 Alexander Volkanovski alexander.volkanovski@example.com Store Manager 72000.00 5 2 2018-10-20 Sydney
|
|
6 Jon Jones jon.jones@example.com Store Employee 43000.00 5 2 2021-12-10 Sydney
|
|
9 Sean OMalley sean.omalley@example.com Warehouse Manager 76000.00 5 5 2020-09-15 New York
|
|
10 Dustin Poirier dustin.poirier@example.com Warehouse Employee 40000.00 5 5 2022-07-08 New York
|
|
11 Canelo Alvarez canelo.alvarez@example.com Store Manager 75000.00 5 3 2020-04-01 New York
|
|
12 Tyson Fury tyson.fury@example.com Warehouse Manager 80000.00 5 4 2018-06-15 Washington
|
|
25 Harry Kane harry.kane@example.com Store Employee 59000.00 5 3 2020-03-15 New York
|
|
26 Erling Haaland erling.haaland@example.com Store Employee 56000.00 5 3 2021-09-01 New York
|
|
27 Luka Modric luka.modric@example.com Store Employee 57000.00 5 3 2022-01-30 New York
|
|
28 Karim Benzema karim.benzema@example.com Store Employee 58000.00 5 3 2019-04-25 New York
|
|
29 Sadio Mane sadio.mane@example.com Warehouse Employee 54000.00 5 4 2021-07-20 Washington
|
|
30 Bruno Fernandes bruno.fernandes@example.com Warehouse Employee 55000.00 5 4 2020-11-10 Washington
|
|
31 Marcus Rashford marcus.rashford@example.com Warehouse Employee 60000.00 5 4 2022-05-18 Washington
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: product; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.product (product_id, product_name, category, price, stock_level, reorder_level, last_purchase_date, supplier_id, cost) FROM stdin;
|
|
1001 High-Pressure Turbine Blade Engine Component 2500.00 468 100 2024-01-10 1 900.00
|
|
1002 Combustion Chamber Engine Component 12000.00 134 50 2024-01-15 2 4000.00
|
|
1003 Fan Blade Engine Component 1800.00 260 75 2024-01-20 3 1200.00
|
|
1005 Compressor Disk Engine Component 5000.00 231 75 2024-02-01 5 3200.00
|
|
1007 Ignition System Engine Component 4500.00 79 25 2024-02-10 1 2700.00
|
|
1009 Bearing Housing Engine Component 3500.00 184 50 2024-02-20 3 2800.00
|
|
1010 Thermal Insulation Blanket Engine Component 1500.00 328 90 2024-02-25 4 950.00
|
|
1004 Fuel Nozzle Engine Component 800.00 640 200 2024-01-25 4 550.00
|
|
1008 Oil Pump Engine Component 1200.00 376 100 2024-02-15 2 700.00
|
|
1006 Thrust Reverser Engine Component 15000.00 37 20 2024-02-05 6 12500.00
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: purchase_order; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.purchase_order (purchase_order_id, supplier_id, product_id, order_date, delivery_date, total_amount, order_status, quantity, location_id) FROM stdin;
|
|
8 2 1002 2024-01-15 2024-02-05 3000.00 Completed 15 4
|
|
10 4 1004 2024-01-25 2024-02-15 4000.00 Completed 8 5
|
|
12 6 1006 2024-02-05 \N 6000.00 Pending 12 5
|
|
9 3 1003 2024-01-20 \N 7500.00 Cancelled 25 4
|
|
7 1 1002 2024-01-10 \N 5000.00 Awaiting Approval 19 4
|
|
11 5 1005 2024-02-01 2025-01-05 2000.00 Completed 6 5
|
|
13 2 1002 2025-01-08 \N 24000.00 Awaiting Approval 2 5
|
|
14 3 1003 2025-01-08 \N 3600.00 Awaiting Approval 2 5
|
|
15 1 1001 2025-01-08 \N 32500.00 Awaiting Approval 13 5
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: sales_forecast; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.sales_forecast (forecast_id, store_id, product_id, start_date, end_date, predicted_sales, created_date) FROM stdin;
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: sales_record; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.sales_record (sales_id, store_id, manager_id, product_id, quantity_sold, date_of_sale, total_amount, payment_method) FROM stdin;
|
|
1 1 1 1001 5 2025-01-05 12500.00 Credit Card
|
|
2 1 1 1003 10 2025-01-06 18000.00 Cash
|
|
3 1 1 1007 3 2025-01-07 13500.00 Debit Card
|
|
4 2 2 1002 2 2025-01-05 24000.00 Cash
|
|
5 2 2 1005 4 2025-01-06 20000.00 Credit Card
|
|
6 2 2 1008 6 2025-01-07 21000.00 Debit Card
|
|
7 3 4 1004 7 2025-01-05 5600.00 Cash
|
|
8 3 4 1006 1 2025-01-06 15000.00 Credit Card
|
|
9 3 4 1010 3 2025-01-07 4500.00 Debit Card
|
|
10 1 1 1001 10 2025-01-01 25000.00 Credit Card
|
|
11 1 1 1002 5 2025-01-02 60000.00 Cash
|
|
12 1 1 1003 8 2025-01-05 14400.00 Debit Card
|
|
13 1 1 1004 20 2025-01-10 16000.00 Credit Card
|
|
14 1 1 1005 3 2025-02-01 15000.00 Cash
|
|
15 1 1 1006 2 2025-02-15 30000.00 Credit Card
|
|
16 1 1 1007 5 2025-02-20 22500.00 Cash
|
|
17 1 1 1008 10 2025-03-01 35000.00 Credit Card
|
|
18 1 1 1009 4 2025-03-15 14000.00 Debit Card
|
|
19 1 1 1010 6 2025-03-20 9000.00 Cash
|
|
20 2 2 1001 12 2025-01-01 30000.00 Cash
|
|
21 2 2 1003 15 2025-01-12 27000.00 Debit Card
|
|
22 2 2 1004 25 2025-02-05 20000.00 Credit Card
|
|
23 2 2 1005 10 2025-02-14 50000.00 Cash
|
|
24 2 2 1006 4 2025-02-28 60000.00 Debit Card
|
|
25 2 2 1007 7 2025-03-10 31500.00 Cash
|
|
26 2 2 1008 6 2025-03-15 21000.00 Credit Card
|
|
27 2 2 1009 8 2025-03-20 28000.00 Cash
|
|
28 2 2 1010 10 2025-03-25 15000.00 Debit Card
|
|
29 2 2 1002 6 2025-03-30 72000.00 Credit Card
|
|
30 3 4 1001 5 2025-01-03 12500.00 Debit Card
|
|
31 3 4 1002 3 2025-01-15 36000.00 Cash
|
|
32 3 4 1003 7 2025-02-05 12600.00 Credit Card
|
|
33 3 4 1005 2 2025-02-12 10000.00 Cash
|
|
34 3 4 1007 6 2025-02-20 27000.00 Debit Card
|
|
35 3 4 1009 4 2025-02-25 14000.00 Credit Card
|
|
36 3 4 1010 3 2025-03-05 4500.00 Cash
|
|
37 3 4 1004 8 2025-03-10 6400.00 Debit Card
|
|
38 3 4 1008 2 2025-03-15 7000.00 Credit Card
|
|
39 3 4 1006 1 2025-03-20 15000.00 Cash
|
|
40 1 1 1006 5 2025-04-13 12500.00 Credit Card
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: stock_transfer; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.stock_transfer (transfer_id, source_location_id, destination_location_id, product_id, quantity, transfer_date, status) FROM stdin;
|
|
1 1 4 1003 10 2025-01-05 Completed
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: store; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.store (store_id, location_id, operating_hours) FROM stdin;
|
|
2 2 09:00-17:00
|
|
3 3 09:00-17:00
|
|
1 1 09:00-15:00
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: supplier; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.supplier (supplier_id, supplier_name, contact_details, location, contract_terms) FROM stdin;
|
|
1 Jet Engine Components Co. 123-456-7890 Las Vegas, USA Supply jet engine components for 3 years
|
|
2 Precision Engineering Ltd. 987-654-3210 New York, USA Exclusive supplier of precision-machined parts
|
|
3 High-Tech Materials Inc. 555-666-7777 Washington, USA Provide high-strength alloys and composites
|
|
4 Aerospace Electronics PLC 222-333-4444 Sydney, Australia Electronics and sensors for Rolls-Royce engines
|
|
5 Advanced Manufacturing Solutions 444-555-6666 Coventry, UK Support with additive manufacturing technologies
|
|
6 Global Aviation Supplies 777-888-9999 Istanbul, Turkey Provide consumables and tools for aviation operations
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.users (user_id, person_id, email, password, role, location_id) FROM stdin;
|
|
1 4 store1@example.com $2b$10$YFogi0fPHcFT1iM/IMzE5ePCbHIKEkkwnjZhkKycUeQn6ivDt73XS Store Manager \N
|
|
3 11 store3@example.com $2b$10$aCzTVMWeLeZd1nknHMuVTe.jB5bpR2TewJC84/4vn7eg5Re5GTNQa Store Manager \N
|
|
4 9 warehouse1@example.com $2b$10$cwiQEcv7yngU9oiUgEzCPersfMaWUwqpDQvMxerDOiLgw6SXm1zT2 Warehouse Manager \N
|
|
5 12 warehouse2@example.com $2b$10$9TbMLjz6seUrR7uY.wnthen3pK8Iu272lznYUzNRhrueOEWRVQfJu Warehouse Manager \N
|
|
6 13 sales@example.com $2b$10$BW76tOwtn4kiuNC7S4/XpeGn8RJMmscIE2OlVmjXr6C3eaH.Iulhy Sales Manager \N
|
|
7 14 hr@example.com $2b$10$2t8yE0Q.vLVGr91L1QK7Aut5rapNE6iIZcUfi/l/XH1BHau9979C. HR Manager \N
|
|
8 15 finance@example.com $2b$10$JrDox9vWSXlIiH6vI4HvMuJyQhCqJcbg/NmByHCB8yaY4vLP3Occy Finance Manager \N
|
|
9 16 procurement@example.com $2b$10$Zv.kP.kipkm9aFAxYOBbMOwa4iRkLp3MVQHm.kIDfDA21DFY.NQBq Procurement Manager \N
|
|
2 5 store2@example.com $2b$10$BWQJHwNs4N88rmebcI0r4eD5oMguGhk1RnGew/RmKyM0QpMhzJ58e Store Manager \N
|
|
\.
|
|
|
|
|
|
--
|
|
-- Data for Name: warehouse; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.warehouse (warehouse_id, location_id, capacity) FROM stdin;
|
|
\.
|
|
|
|
|
|
--
|
|
-- Name: attendance_attendance_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.attendance_attendance_id_seq', 8, true);
|
|
|
|
|
|
--
|
|
-- Name: budget_budget_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.budget_budget_id_seq', 1, false);
|
|
|
|
|
|
--
|
|
-- Name: department_department_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.department_department_id_seq', 5, true);
|
|
|
|
|
|
--
|
|
-- Name: employee_employee_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.employee_employee_id_seq', 20, true);
|
|
|
|
|
|
--
|
|
-- Name: executive_executive_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.executive_executive_id_seq', 2, true);
|
|
|
|
|
|
--
|
|
-- Name: expenses_expense_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.expenses_expense_id_seq', 40, true);
|
|
|
|
|
|
--
|
|
-- Name: financialreport_report_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.financialreport_report_id_seq', 1, false);
|
|
|
|
|
|
--
|
|
-- Name: inventory_inventory_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.inventory_inventory_id_seq', 50, true);
|
|
|
|
|
|
--
|
|
-- Name: location_location_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.location_location_id_seq', 10, true);
|
|
|
|
|
|
--
|
|
-- Name: manager_manager_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.manager_manager_id_seq', 15, true);
|
|
|
|
|
|
--
|
|
-- Name: payroll_payroll_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.payroll_payroll_id_seq', 30, true);
|
|
|
|
|
|
--
|
|
-- Name: person_person_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.person_person_id_seq', 52, true);
|
|
|
|
|
|
--
|
|
-- Name: product_product_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.product_product_id_seq', 10, true);
|
|
|
|
|
|
--
|
|
-- Name: purchaseorder_purchase_order_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.purchaseorder_purchase_order_id_seq', 15, true);
|
|
|
|
|
|
--
|
|
-- Name: salesforecast_forecast_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.salesforecast_forecast_id_seq', 1, false);
|
|
|
|
|
|
--
|
|
-- Name: salesrecord_sales_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.salesrecord_sales_id_seq', 40, true);
|
|
|
|
|
|
--
|
|
-- Name: stocktransfer_transfer_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.stocktransfer_transfer_id_seq', 1, true);
|
|
|
|
|
|
--
|
|
-- Name: store_store_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.store_store_id_seq', 3, true);
|
|
|
|
|
|
--
|
|
-- Name: supplier_supplier_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.supplier_supplier_id_seq', 6, true);
|
|
|
|
|
|
--
|
|
-- Name: users_user_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.users_user_id_seq', 9, true);
|
|
|
|
|
|
--
|
|
-- Name: warehouse_warehouse_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.warehouse_warehouse_id_seq', 1, false);
|
|
|
|
|
|
--
|
|
-- Name: attendance attendance_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.attendance
|
|
ADD CONSTRAINT attendance_pkey PRIMARY KEY (attendance_id);
|
|
|
|
|
|
--
|
|
-- Name: budget budget_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.budget
|
|
ADD CONSTRAINT budget_pkey PRIMARY KEY (budget_id);
|
|
|
|
|
|
--
|
|
-- Name: department department_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.department
|
|
ADD CONSTRAINT department_pkey PRIMARY KEY (department_id);
|
|
|
|
|
|
--
|
|
-- Name: employee employee_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.employee
|
|
ADD CONSTRAINT employee_pkey PRIMARY KEY (employee_id);
|
|
|
|
|
|
--
|
|
-- Name: executive executive_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.executive
|
|
ADD CONSTRAINT executive_pkey PRIMARY KEY (executive_id);
|
|
|
|
|
|
--
|
|
-- Name: expenses expenses_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses
|
|
ADD CONSTRAINT expenses_pkey PRIMARY KEY (expense_id);
|
|
|
|
|
|
--
|
|
-- Name: financial_report financialreport_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.financial_report
|
|
ADD CONSTRAINT financialreport_pkey PRIMARY KEY (report_id);
|
|
|
|
|
|
--
|
|
-- Name: inventory inventory_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.inventory
|
|
ADD CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id);
|
|
|
|
|
|
--
|
|
-- Name: location location_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.location
|
|
ADD CONSTRAINT location_pkey PRIMARY KEY (location_id);
|
|
|
|
|
|
--
|
|
-- Name: manager manager_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.manager
|
|
ADD CONSTRAINT manager_pkey PRIMARY KEY (manager_id);
|
|
|
|
|
|
--
|
|
-- Name: payroll payroll_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.payroll
|
|
ADD CONSTRAINT payroll_pkey PRIMARY KEY (payroll_id);
|
|
|
|
|
|
--
|
|
-- Name: person person_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.person
|
|
ADD CONSTRAINT person_email_key UNIQUE (email);
|
|
|
|
|
|
--
|
|
-- Name: person person_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.person
|
|
ADD CONSTRAINT person_pkey PRIMARY KEY (person_id);
|
|
|
|
|
|
--
|
|
-- Name: product product_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.product
|
|
ADD CONSTRAINT product_pkey PRIMARY KEY (product_id);
|
|
|
|
|
|
--
|
|
-- Name: purchase_order purchaseorder_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.purchase_order
|
|
ADD CONSTRAINT purchaseorder_pkey PRIMARY KEY (purchase_order_id);
|
|
|
|
|
|
--
|
|
-- Name: sales_forecast salesforecast_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_forecast
|
|
ADD CONSTRAINT salesforecast_pkey PRIMARY KEY (forecast_id);
|
|
|
|
|
|
--
|
|
-- Name: sales_record salesrecord_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_record
|
|
ADD CONSTRAINT salesrecord_pkey PRIMARY KEY (sales_id);
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer stocktransfer_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.stock_transfer
|
|
ADD CONSTRAINT stocktransfer_pkey PRIMARY KEY (transfer_id);
|
|
|
|
|
|
--
|
|
-- Name: store store_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.store
|
|
ADD CONSTRAINT store_pkey PRIMARY KEY (store_id);
|
|
|
|
|
|
--
|
|
-- Name: supplier supplier_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.supplier
|
|
ADD CONSTRAINT supplier_pkey PRIMARY KEY (supplier_id);
|
|
|
|
|
|
--
|
|
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.users
|
|
ADD CONSTRAINT users_email_key UNIQUE (email);
|
|
|
|
|
|
--
|
|
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.users
|
|
ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
|
|
|
|
|
|
--
|
|
-- Name: warehouse warehouse_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.warehouse
|
|
ADD CONSTRAINT warehouse_pkey PRIMARY KEY (warehouse_id);
|
|
|
|
|
|
--
|
|
-- Name: purchase_order set_delivery_date; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER set_delivery_date BEFORE UPDATE ON public.purchase_order FOR EACH ROW WHEN (((old.order_status)::text IS DISTINCT FROM (new.order_status)::text)) EXECUTE FUNCTION public.update_delivery_date();
|
|
|
|
|
|
--
|
|
-- Name: sales_record trigger_decrease_stock; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER trigger_decrease_stock AFTER INSERT ON public.sales_record FOR EACH ROW EXECUTE FUNCTION public.decrease_stock_level();
|
|
|
|
|
|
--
|
|
-- Name: purchase_order trigger_update_inventory_on_purchase; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER trigger_update_inventory_on_purchase AFTER UPDATE ON public.purchase_order FOR EACH ROW WHEN (((old.order_status)::text IS DISTINCT FROM (new.order_status)::text)) EXECUTE FUNCTION public.update_inventory_on_purchase_order();
|
|
|
|
|
|
--
|
|
-- Name: sales_record trigger_update_inventory_on_sale; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER trigger_update_inventory_on_sale AFTER INSERT ON public.sales_record FOR EACH ROW EXECUTE FUNCTION public.update_inventory_on_sale();
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer trigger_update_inventory_on_transfer; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER trigger_update_inventory_on_transfer AFTER INSERT ON public.stock_transfer FOR EACH ROW EXECUTE FUNCTION public.update_inventory_on_transfer();
|
|
|
|
|
|
--
|
|
-- Name: attendance attendance_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.attendance
|
|
ADD CONSTRAINT attendance_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id);
|
|
|
|
|
|
--
|
|
-- Name: budget budget_department_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.budget
|
|
ADD CONSTRAINT budget_department_id_fkey FOREIGN KEY (department_id) REFERENCES public.department(department_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: employee employee_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.employee
|
|
ADD CONSTRAINT employee_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: executive executive_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.executive
|
|
ADD CONSTRAINT executive_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: expenses expenses_budget_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses
|
|
ADD CONSTRAINT expenses_budget_id_fkey FOREIGN KEY (budget_id) REFERENCES public.budget(budget_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: expenses expenses_department_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses
|
|
ADD CONSTRAINT expenses_department_id_fkey FOREIGN KEY (department_id) REFERENCES public.department(department_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: expenses expenses_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses
|
|
ADD CONSTRAINT expenses_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: financial_report financialreport_department_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.financial_report
|
|
ADD CONSTRAINT financialreport_department_id_fkey FOREIGN KEY (department_id) REFERENCES public.department(department_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: financial_report financialreport_store_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.financial_report
|
|
ADD CONSTRAINT financialreport_store_id_fkey FOREIGN KEY (store_id) REFERENCES public.store(store_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: department fk_department_manager; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.department
|
|
ADD CONSTRAINT fk_department_manager FOREIGN KEY (manager_id) REFERENCES public.manager(manager_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: location fk_location_manager; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.location
|
|
ADD CONSTRAINT fk_location_manager FOREIGN KEY (manager_id) REFERENCES public.manager(manager_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: inventory inventory_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.inventory
|
|
ADD CONSTRAINT inventory_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: inventory inventory_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.inventory
|
|
ADD CONSTRAINT inventory_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(product_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: manager manager_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.manager
|
|
ADD CONSTRAINT manager_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: payroll payroll_budget_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.payroll
|
|
ADD CONSTRAINT payroll_budget_id_fkey FOREIGN KEY (budget_id) REFERENCES public.budget(budget_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: payroll payroll_department_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.payroll
|
|
ADD CONSTRAINT payroll_department_id_fkey FOREIGN KEY (department_id) REFERENCES public.department(department_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: payroll payroll_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.payroll
|
|
ADD CONSTRAINT payroll_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id) ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: person person_department_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.person
|
|
ADD CONSTRAINT person_department_id_fkey FOREIGN KEY (department_id) REFERENCES public.department(department_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: person person_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.person
|
|
ADD CONSTRAINT person_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: product product_supplier_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.product
|
|
ADD CONSTRAINT product_supplier_id_fkey FOREIGN KEY (supplier_id) REFERENCES public.supplier(supplier_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: purchase_order purchase_order_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.purchase_order
|
|
ADD CONSTRAINT purchase_order_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: purchase_order purchaseorder_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.purchase_order
|
|
ADD CONSTRAINT purchaseorder_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(product_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: purchase_order purchaseorder_supplier_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.purchase_order
|
|
ADD CONSTRAINT purchaseorder_supplier_id_fkey FOREIGN KEY (supplier_id) REFERENCES public.supplier(supplier_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: sales_forecast salesforecast_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_forecast
|
|
ADD CONSTRAINT salesforecast_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(product_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: sales_forecast salesforecast_store_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_forecast
|
|
ADD CONSTRAINT salesforecast_store_id_fkey FOREIGN KEY (store_id) REFERENCES public.store(store_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: sales_record salesrecord_manager_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_record
|
|
ADD CONSTRAINT salesrecord_manager_id_fkey FOREIGN KEY (manager_id) REFERENCES public.manager(manager_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: sales_record salesrecord_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_record
|
|
ADD CONSTRAINT salesrecord_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(product_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: sales_record salesrecord_store_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.sales_record
|
|
ADD CONSTRAINT salesrecord_store_id_fkey FOREIGN KEY (store_id) REFERENCES public.store(store_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer stocktransfer_destination_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.stock_transfer
|
|
ADD CONSTRAINT stocktransfer_destination_location_id_fkey FOREIGN KEY (destination_location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer stocktransfer_product_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.stock_transfer
|
|
ADD CONSTRAINT stocktransfer_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(product_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: stock_transfer stocktransfer_source_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.stock_transfer
|
|
ADD CONSTRAINT stocktransfer_source_location_id_fkey FOREIGN KEY (source_location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: store store_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.store
|
|
ADD CONSTRAINT store_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: users users_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.users
|
|
ADD CONSTRAINT users_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: users users_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.users
|
|
ADD CONSTRAINT users_person_id_fkey FOREIGN KEY (person_id) REFERENCES public.person(person_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- Name: warehouse warehouse_location_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.warehouse
|
|
ADD CONSTRAINT warehouse_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|