dbd_lab5 export

--------------------------------------------------------
--  File created - Monday-October-31-2016
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table BINDPRODUCTSPROPERTIES
--------------------------------------------------------

  CREATE TABLE "BINDPRODUCTSPROPERTIES"
   (	"PRODUCTID" NUMBER(4,0),
	"PROPERTYID" NUMBER(4,0)
   ) ;
--------------------------------------------------------
--  DDL for Table CATEGORIES
--------------------------------------------------------

  CREATE TABLE "CATEGORIES"
   (	"CATEGORYID" NUMBER(4,0),
	"CATEGORYNAME" VARCHAR2(40 BYTE),
	"OTHERCATID" NUMBER(4,0)
   ) ;
--------------------------------------------------------
--  DDL for Table CLIENTS
--------------------------------------------------------

  CREATE TABLE "CLIENTS"
   (	"CLIENTID" NUMBER(4,0),
	"CLIENTNAME" VARCHAR2(40 BYTE),
	"CLIENTADDRESS" VARCHAR2(200 BYTE)
   ) ;
--------------------------------------------------------
--  DDL for Table ORDEREDITEMS
--------------------------------------------------------

  CREATE TABLE "ORDEREDITEMS"
   (	"ORDERID" NUMBER(4,0),
	"PRODUCTID" NUMBER(4,0),
	"QUANTITY" NUMBER(4,0)
   ) ;
--------------------------------------------------------
--  DDL for Table ORDERS
--------------------------------------------------------

  CREATE TABLE "ORDERS"
   (	"ORDERID" NUMBER(4,0),
	"CLIENTID" NUMBER(4,0),
	"REQUESTDATE" DATE,
	"CONFIRMATIONDATE" DATE
   ) ;
--------------------------------------------------------
--  DDL for Table PRICES
--------------------------------------------------------

  CREATE TABLE "PRICES"
   (	"PRODUCTID" NUMBER(4,0),
	"STARTDATE" DATE,
	"ENDDATE" DATE,
	"PRICE" NUMBER(6,2)
   ) ;
--------------------------------------------------------
--  DDL for Table PRODUCTS
--------------------------------------------------------

  CREATE TABLE "PRODUCTS"
   (	"PRODUCTID" NUMBER(4,0),
	"PRODUCTNAME" VARCHAR2(40 BYTE),
	"CATEGORYID" NUMBER(4,0)
   ) ;
--------------------------------------------------------
--  DDL for Table PROPERTIES
--------------------------------------------------------

  CREATE TABLE "PROPERTIES"
   (	"PROPERTYID" NUMBER(4,0),
	"PROPERTYNAME" VARCHAR2(40 BYTE),
	"PROPERTYVALUE" VARCHAR2(40 BYTE)
   ) ;
REM INSERTING into BINDPRODUCTSPROPERTIES
Insert into BINDPRODUCTSPROPERTIES (PRODUCTID,PROPERTYID) values (0,0);
Insert into BINDPRODUCTSPROPERTIES (PRODUCTID,PROPERTYID) values (1,1);
Insert into BINDPRODUCTSPROPERTIES (PRODUCTID,PROPERTYID) values (2,2);
Insert into BINDPRODUCTSPROPERTIES (PRODUCTID,PROPERTYID) values (3,3);
Insert into BINDPRODUCTSPROPERTIES (PRODUCTID,PROPERTYID) values (4,0);
REM INSERTING into CATEGORIES
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (0,'PC',null);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (1,'Toys',null);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (2,'Luxury',null);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (3,'Clothes',null);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (4,'Monitor',1);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (5,'Clock',2);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (6,'Clock',3);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (7,'Desk',0);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (8,'Cables',0);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (9,'Routers',0);
Insert into CATEGORIES (CATEGORYID,CATEGORYNAME,OTHERCATID) values (10,'Laptops',0);
REM INSERTING into CLIENTS
Insert into CLIENTS (CLIENTID,CLIENTNAME,CLIENTADDRESS) values (0,'Giovanni Giordo','Timisoara');
Insert into CLIENTS (CLIENTID,CLIENTNAME,CLIENTADDRESS) values (1,'Eric Thomas','Arad');
Insert into CLIENTS (CLIENTID,CLIENTNAME,CLIENTADDRESS) values (2,'Catalin Popescu','Timisoara');
Insert into CLIENTS (CLIENTID,CLIENTNAME,CLIENTADDRESS) values (3,'Maria Floreasca','Bucuresti');
REM INSERTING into ORDEREDITEMS
Insert into ORDEREDITEMS (ORDERID,PRODUCTID,QUANTITY) values (0,0,3);
REM INSERTING into ORDERS
Insert into ORDERS (ORDERID,CLIENTID,REQUESTDATE,CONFIRMATIONDATE) values (0,0,to_date('15-NOV-16 00:00:00','DD-MON-RR HH24:MI:SS'),to_date('26-NOV-16 00:00:00','DD-MON-RR HH24:MI:SS'));
REM INSERTING into PRICES
Insert into PRICES (PRODUCTID,STARTDATE,ENDDATE,PRICE) values (0,to_date('15-NOV-16 00:00:00','DD-MON-RR HH24:MI:SS'),to_date('26-NOV-16 00:00:00','DD-MON-RR HH24:MI:SS'),114);
REM INSERTING into PRODUCTS
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (0,'laptop',0);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (1,'Teddy Bear',1);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (2,'Watch',2);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (3,'Gloves',3);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (4,'Monitor',0);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (5,'Clock',5);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (6,'Clock',0);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (7,'Mouse',0);
Insert into PRODUCTS (PRODUCTID,PRODUCTNAME,CATEGORYID) values (8,'usedlaptop',10);
REM INSERTING into PROPERTIES
Insert into PROPERTIES (PROPERTYID,PROPERTYNAME,PROPERTYVALUE) values (0,'Color','Black');
Insert into PROPERTIES (PROPERTYID,PROPERTYNAME,PROPERTYVALUE) values (1,'Color','Gold');
Insert into PROPERTIES (PROPERTYID,PROPERTYNAME,PROPERTYVALUE) values (2,'Brand','Calvin Klein');
Insert into PROPERTIES (PROPERTYID,PROPERTYNAME,PROPERTYVALUE) values (3,'Material','Wool');
--------------------------------------------------------
--  DDL for Index PK_CLIENT
--------------------------------------------------------

  CREATE UNIQUE INDEX "PK_CLIENT" ON "CLIENTS" ("CLIENTID")
  ;
--------------------------------------------------------
--  DDL for Index PK_PROPERTY
--------------------------------------------------------

  CREATE UNIQUE INDEX "PK_PROPERTY" ON "PROPERTIES" ("PROPERTYID")
  ;
--------------------------------------------------------
--  DDL for Index PK_CAT
--------------------------------------------------------

  CREATE UNIQUE INDEX "PK_CAT" ON "CATEGORIES" ("CATEGORYID")
  ;
--------------------------------------------------------
--  DDL for Index PK_PRODUCT
--------------------------------------------------------

  CREATE UNIQUE INDEX "PK_PRODUCT" ON "PRODUCTS" ("PRODUCTID")
  ;
--------------------------------------------------------
--  DDL for Index PK_ORDER
--------------------------------------------------------

  CREATE UNIQUE INDEX "PK_ORDER" ON "ORDERS" ("ORDERID")
  ;
--------------------------------------------------------
--  Constraints for Table PRODUCTS
--------------------------------------------------------

  ALTER TABLE "PRODUCTS" ADD CONSTRAINT "PK_PRODUCT" PRIMARY KEY ("PRODUCTID") ENABLE;
--------------------------------------------------------
--  Constraints for Table CATEGORIES
--------------------------------------------------------

  ALTER TABLE "CATEGORIES" ADD CONSTRAINT "PK_CAT" PRIMARY KEY ("CATEGORYID") ENABLE;
--------------------------------------------------------
--  Constraints for Table PROPERTIES
--------------------------------------------------------

  ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PK_PROPERTY" PRIMARY KEY ("PROPERTYID") ENABLE;
--------------------------------------------------------
--  Constraints for Table CLIENTS
--------------------------------------------------------

  ALTER TABLE "CLIENTS" ADD CONSTRAINT "PK_CLIENT" PRIMARY KEY ("CLIENTID") ENABLE;
--------------------------------------------------------
--  Constraints for Table ORDERS
--------------------------------------------------------

  ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDER" PRIMARY KEY ("ORDERID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table BINDPRODUCTSPROPERTIES
--------------------------------------------------------

  ALTER TABLE "BINDPRODUCTSPROPERTIES" ADD CONSTRAINT "FK_PRODUCT" FOREIGN KEY ("PRODUCTID")
	  REFERENCES "PRODUCTS" ("PRODUCTID") ENABLE;

  ALTER TABLE "BINDPRODUCTSPROPERTIES" ADD CONSTRAINT "FK_PROPERTY" FOREIGN KEY ("PROPERTYID")
	  REFERENCES "PROPERTIES" ("PROPERTYID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table CATEGORIES
--------------------------------------------------------

  ALTER TABLE "CATEGORIES" ADD CONSTRAINT "FK_CAT" FOREIGN KEY ("OTHERCATID")
	  REFERENCES "CATEGORIES" ("CATEGORYID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table ORDEREDITEMS
--------------------------------------------------------

  ALTER TABLE "ORDEREDITEMS" ADD CONSTRAINT "FK_ORDER" FOREIGN KEY ("ORDERID")
	  REFERENCES "ORDERS" ("ORDERID") ENABLE;

  ALTER TABLE "ORDEREDITEMS" ADD CONSTRAINT "FK_ORDERED_ITEM" FOREIGN KEY ("PRODUCTID")
	  REFERENCES "PRODUCTS" ("PRODUCTID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table ORDERS
--------------------------------------------------------

  ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_CLIENT" FOREIGN KEY ("CLIENTID")
	  REFERENCES "CLIENTS" ("CLIENTID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table PRICES
--------------------------------------------------------

  ALTER TABLE "PRICES" ADD CONSTRAINT "FK_PRICE_PRODUCT" FOREIGN KEY ("PRODUCTID")
	  REFERENCES "PRODUCTS" ("PRODUCTID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table PRODUCTS
--------------------------------------------------------

  ALTER TABLE "PRODUCTS" ADD CONSTRAINT "FK_CATEGORY" FOREIGN KEY ("CATEGORYID")
	  REFERENCES "CATEGORIES" ("CATEGORYID") ENABLE;