/*DROP TABLE Sales;
DROP TABLE Product;
DROP TABLE Customer;
DROP TABLE Provider;
DROP TABLE Demo;*/

CREATE TABLE Product
(
 id_prod INTEGER NOT NULL PRIMARY KEY,
 Name VARCHAR2 (4000),
 Price FLOAT
 );

 CREATE TABLE Demo
 (
   id_demo INTEGER NOT NULL PRIMARY KEY,
   Name VARCHAR2 (4000),
   Parent INTEGER,
   FOREIGN KEY (Parent) REFERENCES Demo
   );

   CREATE TABLE Customer
   (
    id_c INTEGER NOT NULL PRIMARY KEY,
    Name VARCHAR2 (4000),
    id_demo INTEGER,
    FOREIGN KEY (id_demo) REFERENCES Demo
    );

   CREATE TABLE Provider
   (
   id_p INTEGER NOT NULL PRIMARY KEY,
   Name VARCHAR2 (4000),
   id_demo INTEGER,
   FOREIGN KEY (id_demo) REFERENCES Demo
   );

   CREATE TABLE Sales
   (
   id_prod INTEGER NOT NULL,
   FOREIGN KEY (id_prod) REFERENCES Product,
   id_c INTEGER NOT NULL,
   FOREIGN KEY (id_c) REFERENCES Customer,
   id_p INTEGER NOT NULL,
   FOREIGN KEY (id_p) REFERENCES Provider,
   Q INTEGER,
   date_s DATE NOT NULL
   );


   ALTER TABLE Sales ADD PRIMARY KEY (id_prod, id_c, id_p, date_s);

  DROP SEQUENCE ProdSeq;
  DROP SEQUENCE CustomerSeq;
  DROP SEQUENCE ProvSeq;

   CREATE SEQUENCE ProdSeq
 START WITH 1
 INCREMENT BY 1;


  CREATE SEQUENCE CustomerSeq
 START WITH 1
 INCREMENT BY 1;

  CREATE SEQUENCE ProvSeq
 START WITH 1
 INCREMENT BY 1;

   INSERT INTO PRODUCT VALUES(ProdSeq.NEXTVAL, 'Potatoes', 40.0);
   INSERT INTO PRODUCT VALUES(ProdSeq.NEXTVAL, 'Macaroni', 39.5);
   INSERT INTO PRODUCT VALUES(ProdSeq.NEXTVAL, 'Meat', 100.0);
   INSERT INTO PRODUCT VALUES(ProdSeq.NEXTVAL, 'Vodka', 250.0);
   INSERT INTO PRODUCT VALUES(ProdSeq.NEXTVAL, 'Beer', 60.35);


   INSERT INTO Demo VALUES(1,'РФ', NULL);
   INSERT INTO Demo VALUES(2, 'Северо-Кавказский ФО', 1);
   INSERT INTO Demo VALUES(3,'Центральный ФО',1);
   INSERT INTO Demo VALUES(4,'Приволжский ФО',1);
   INSERT INTO Demo VALUES(5,'Чеченская республика',2)
   INSERT INTO Demo VALUES(6,'Республика Дагестан',2)
   INSERT INTO Demo VALUES(7,'Московская область',3)
   INSERT INTO Demo VALUES(8,'Смоленская область',3)
   INSERT INTO Demo VALUES(9,'Пермский край',4)
   INSERT INTO Demo VALUES(10,'Республика Коми',4)

   INSERT INTO Provider VALUES(ProvSeq.NEXTVAL,'ОАО Ин-тур',9)
   INSERT INTO Provider VALUES(ProvSeq.NEXTVAL,'ЗАО Славянский Аппетит',9)
   INSERT INTO Provider VALUES(ProvSeq.NEXTVAL,'ОАО Повар',10)
   INSERT INTO Provider VALUES(ProvSeq.NEXTVAL,'ОАО Дерябин и Со',10)
   INSERT INTO Provider VALUES(ProvSeq.NEXTVAL,'ЗАО Построим дом',2)
   INSERT INTO Provider VALUES(ProvSeq.NEXTVAL,'ЗАО Privet ',2)

   INSERT INTO Customer VALUES
   INSERT INTO Customer VALUES
   INSERT INTO Customer VALUES
   INSERT INTO Customer VALUES
   INSERT INTO Customer VALUES
   INSERT INTO Customer VALUES
   INSERT INTO Customer VALUES

   COMMIT;