DB

CREATE DATABASE goods_sales
WITH ENCODING='UTF8'
 CONNECTION LIMIT=-1;

CREATE TABLE public.trade_enterprise
(
company_id integer NOT NULL,
company_name text,
director text,
phone integer,
CONSTRAINT trade_enterprise_pkey PRIMARY KEY (company_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.trade_enterprise
OWNER TO postgres;


CREATE TABLE public.goods
(
goods_id integer NOT NULL,
goods_name text,
goods_type_name text,
CONSTRAINT goods_pkey PRIMARY KEY (goods_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.goods
OWNER TO postgres;

CREATE TABLE public.types_of_goods
(
type_id integer NOT NULL,
type_name text,
CONSTRAINT types_of_goods_pkey PRIMARY KEY (type_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.types_of_goods
OWNER TO postgres;

CREATE TABLE public.goods_sale
(
company_id integer NOT NULL,
goods_id integer,
cost_of_goods_sold double precision,
sale_date date,
CONSTRAINT goods_sale_pkey PRIMARY KEY (company_id),
CONSTRAINT goods_sale_company_id_fkey FOREIGN KEY (company_id)
REFERENCES public.trade_enterprise (company_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT goods_sale_goods_id_fkey FOREIGN KEY (goods_id)
REFERENCES public.goods (goods_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.goods_sale
OWNER TO postgres;


INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('1', 'toothbrush', 'hygiene');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('2', 'soap', 'hygiene');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('3', 'sunscreen', 'rest');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('4', 'washcloth', 'hygiene');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('5', 'running_track', 'sport');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('6', 'bike', 'sport');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('7', 'milk', 'food');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('8', 'cookies', 'food');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('9', 'spoon', 'kitchen');
INSERT INTO goods (goods_id, goods_name, goods_type_name) VALUES
('10', 'speakers', 'music');