DBPROJECT_IS1610_Yevsyukov_and_Kogai

CREATE DATABASE DBPROJECT;
COMMENT ON DATABASE "DBPROJECT"
    IS 'IS 1610
Yevsyukov Ilya + Kogai Marina';

CREATE TABLE Tovar (
    codtovara INT NOT NULL,
    ekzemplar CHAR(30) NOT NULL,
    CONSTRAINT pk_tovar PRIMARY KEY (codtovara)
);


CREATE TABLE Postavsiki (
    codpostavsika INT NOT NULL,
    firma CHAR(20) NOT NULL,
    CONSTRAINT pk_postavsiki PRIMARY KEY (codpostavsika)
);


CREATE TABLE Manageri_postavok (
    cod_meng_postav INT NOT NULL,
    surname CHAR(20) NOT NULL,
    yname CHAR(10) NOT NULL,
    lastname CHAR(20) NOT NULL,
    phone INT NOT NULL,
    CONSTRAINT pk_manageripostavok PRIMARY KEY (cod_meng_postav)
);


CREATE TABLE Postavka_tovara (
    codpostavki INT NOT NULL,
    codtovara INT NOT NULL,
    codpostavsika INT NOT NULL,
    colich_post_tov INT NOT NULL,
    cena_post_tov INT NOT NULL,
    cod_meng_postav INT NOT NULL,
    CONSTRAINT pk_postavkatovara PRIMARY KEY (codpostavki),
    CONSTRAINT fk_postavkatovara1 FOREIGN KEY (codtovara) REFERENCES Tovar (codtovara),
    CONSTRAINT fk_postavkatovara2 FOREIGN KEY (codpostavsika) REFERENCES Postavsiki (codpostavsika),
    CONSTRAINT fk_postavkatovara3 FOREIGN KEY (cod_meng_postav) REFERENCES Manageri_postavok (cod_meng_postav)
);


CREATE TABLE Prodavci (
     codprodavca INT NOT NULL,
     surname CHAR(20) NOT NULL,
     yname CHAR(10) NOT NULL,
     lastname CHAR(20) NOT NULL,
     phone INT NOT NULL,
    CONSTRAINT pk_prodavci PRIMARY KEY (codprodavca)
);


CREATE TABLE Prodaza_tovara (
     cod_vibitogo_cheka INT NOT NULL,
     codtovara INT NOT NULL,
     CodFirmaClient INT NOT NULL,
     colich_prod_tov INT NOT NULL,
     cena_prod_tov INT NOT NULL,
     codprodavca INT NOT NULL,
    CONSTRAINT pk_prdztovara PRIMARY KEY (cod_vibitogo_cheka),
    CONSTRAINT fk_prdztovara1 FOREIGN KEY (codtovara) REFERENCES Tovar (codtovara),
    CONSTRAINT fk_prdztovara2 FOREIGN KEY (codprodavca) REFERENCES Prodavci (codprodavca)
);

 /****INSERT INTO TABLES*****/


INSERT INTO Tovar(codtovara,ekzemplar) VALUES
	(1, 'Сыр копченый'),
	(2, 'Молоко'),
    (3, 'Творог'),
	(4, 'Сметана'),
	(5, 'Йогурт');


INSERT INTO Postavsiki(codpostavsika,firma) VALUES
    (11, 'Молоко'),
	(22, 'МолПрод'),
	(33, 'Снежинка'),
	(44, 'Меркурий'),
	(55, 'Молочный дом');


INSERT INTO Manageri_postavok(cod_meng_postav,surname,yname,lastname,phone) VALUES
    (111, 'Евсюков', 'Илья', 'Викторович', 87775653223),
    (222, 'Ким', 'Александра', 'Николаевна', 87089853222),
    (333, 'Когай', 'Марина', 'Александровна', 87054893175),
    (444, 'Махпиров', 'Илья', 'Иванович', 87012587532),
    (555, 'Коваленко', 'Илья', 'Сергеевич', 87775553565);


INSERT INTO Postavka_tovara(codpostavki,codtovara,codpostavsika,colich_post_tov,cena_post_tov,cod_meng_postav) VALUES
    (21152, 1, 22, 230, 190, 111),
    (21180, 2, 11, 230, 25, 111),
    (21153, 3, 44, 230, 80, 333),
    (21149, 4, 55, 230, 40, 222),
    (21176, 5, 33, 230, 30, 333);


INSERT INTO Prodavci(codprodavca,surname,yname,lastname,phone) VALUES
	(12, 'Анисимов', 'Максим', 'Викторович', 87775655473),
	(13, 'Никитин', 'Александр', 'Николаевич', 87089853242),
    (14, 'Пономарёва', 'Екатерина', 'Александровна', 87054898505),
    (15, 'Самойлова', 'Оксана', 'Геннадьевна', 87012583462),
    (16, 'Муравьёв', 'Илья', 'Сергеевич', 87773653565);


INSERT INTO Prodaza_tovara(cod_vibitogo_cheka,codtovara,CodFirmaClient,colich_prod_tov,cena_prod_tov,codprodavca) VALUES
    (234, 1, 177, 230, 200, 13),
    (123, 2, 265, 30, 35, 14),
    (565, 3, 146, 128, 90, 16),
    (90, 4, 180, 160, 50, 12),
    (77, 5, 119, 41, 40, 15);

SELECT * FROM Tovar;
SELECT * FROM Postavsiki;
SELECT * FROM Manageri_postavok;
SELECT * FROM Postavka_tovara;
SELECT * FROM Prodavci;
SELECT * FROM Prodaza_tovara;