/***************************************************************************/
/*    Uebung 7    (a) bis (f)                                              */ 
/*                                                                         */
/*    Anlegen der Tabellen (Console-Anwendung)                             */
/*                                                                         */
/*    Version 2 nur fuer MYSQL (Version 5.1)                               */
/*                                                                         */
/***************************************************************************/
/*                                                                         */
/*    Uebung 7 (a)                                                         */
/*                                                                         */
/*    Tabelle entfernen                                                    */
/*    (Reihenfolge wegen Fremdschluessel beachten)                         */
/***************************************************************************/
DROP TABLE IF EXISTS delivery;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS vendor;
/***************************************************************************/
/*     Tabellen erstellen                                                  */
/*     (Reihenfolge wegen Fremdschluessel beachten)                        */
/***************************************************************************/
CREATE TABLE vendor
(
  LNR           DECIMAL(2),
  Name          VARCHAR(20) NOT NULL,
  Ort           VARCHAR(20) NOT NULL,
  Status        DECIMAL(2),
  CONSTRAINT    vendor_PK    PRIMARY KEY (LNR),
  CONSTRAINT    Status_zul   CHECK (Status in (10,20,30))
);
CREATE TABLE product
(
  ANR           CHAR(2),
  Bezeichnung   VARCHAR(20) NOT NULL,
  Laenge        DECIMAL(3),
  Breite        DECIMAL(3),
  Material      VARCHAR(20) NOT NULL,
  LNR           DECIMAL(2),
  CONSTRAINT    product_vendor_FK foreign key (LNR) references vendor,
  CONSTRAINT    product_PK  PRIMARY KEY (ANR),
  CONSTRAINT    Breite_zul  CHECK (Breite > 0),
  CONSTRAINT    Laenge_zul  CHECK (Laenge > 0)
);
CREATE TABLE delivery
(
   LNR          DECIMAL(2),
   ANR          CHAR(2),
   Mindestmenge DECIMAL(3),
   Lieferzeit   DECIMAL(3),
   Preis        DECIMAL(5,2),
   CONSTRAINT   delivery_PK PRIMARY KEY (LNR , ANR),
   CONSTRAINT   delivery_vendor_FK FOREIGN KEY (LNR) REFERENCES vendor (LNR),
   CONSTRAINT   delivery_product_FK  FOREIGN KEY  (ANR) REFERENCES product (ANR),
   CONSTRAINT   Mindestmenge_zul   CHECK (Mindestmenge > 0),
   CONSTRAINT   Lieferzeit_zul     CHECK (Lieferzeit >= 0),
   CONSTRAINT   Preis_zul          CHECK (Preis >= 0)
);
/*                                                                         */ 
/*    Uebung 7 (b)                                                         */
/*                                                                         */
insert into vendor (select * from Lieferant);
insert into product (select * from Artikel);
insert into delivery (select * from liefert);
/*                                                                         */ 
/*    Uebung 7 (c)                                                         */
/*                                                                         */
insert into product values ('B1','Bett',200,100,'Holz',null);
insert into product values ('B2','Bett',200,150,'Holz',null);
select * from product;
insert into vendor values (7,'Schulze','Gera',10);
select * from vendor;

select * from delivery where LNR = 8;
/*                                                                         */
/*    Uebung 7 (d)                                                         */
/*                                                                         */
update delivery set preis = preis * 0.9  where LNR = 8;
select * from delivery where LNR = 8;
/*                                                                         */
/*    Uebung 7 (e)                                                         */
/*                                                                         */
/*    (Reihenfolge wegen referentieller Integritaet beachten)              */
/*                                                                         */
delete from delivery where ANR ='S1';
delete from product  where ANR ='S1';
select * from product;
select * from delivery;
/*                                                                         */ 
/*    Uebung 7 (f)     (Der neue Lieferant hat die LNR = 7)                */
/*                                                                         */
insert into delivery
(
	select 7,ANR,MIndestmenge, Lieferzeit, Preis*1.1 
	from   delivery
	where  LNR = 8 
);
select * from delivery;



