Irene/31.1.2006/1.2.2006

DROP USER PIZZA;
  DROP USER PIZZA CASCADE;
 
CREATE USER pizza
  IDENTIFIED BY <Enter USER Password Here>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  /
  GRANT UNLIMITED TABLESPACE TO pizza
  /
  GRANT CONNECT TO pizza
  /
  GRANT RESOURCE TO pizza
  /
  ALTER USER pizza DEFAULT ROLE ALL
  /

CREATE TABLE PIZZA.Speisekarte ( 
  SK_ID           	NUMBER(7) NOT NULL,
  SK_Name         	VARCHAR2(30) NULL,
  SK_Beschreibung	        VARCHAR2(200) NULL,
  SK_Preis        	NUMBER(4,2) NULL,
  PRIMARY KEY(SK_ID)
)

CREATE TABLE PIZZA.Pizzazutaten ( 
  PZ_ID   	NUMBER(7) NOT NULL,
  PZ_SK_ID	NUMBER(7) NULL,
  PZ_Name 	VARCHAR2(30) NULL,
  PZ_Preis	NUMBER(4,2) NULL,
  PRIMARY KEY(PZ_ID)
)
 
ALTER TABLE PIZZA.Pizzazutaten
  ADD  CONSTRAINT PZ_SK_ID
    FOREIGN KEY(PZ_SK_ID)
    REFERENCES PIZZA.Speisekarte(SK_ID)

CREATE TABLE PIZZA.Kunden (
  K_ID          NUMBER(7) NOT NULL,
  K_NAME        VARCHAR2(50) NULL,
  K_PASSWORT    VARCHAR2(30) NULL,
  K_ADRESSE     VARCHAR2(50) NULL,
  K_TELEFON     VARCHAR2(30) NULL,
  K_MAIL        VARCHAR2(30) NULL,
  PRIMARY KEY(K_ID)
)

CREATE TABLE PIZZA.Bestellung 
(
  B_ID       NUMBER(7) NOT NULL,
  B_BD_ID    NUMBER(7) NULL,
  B_K_ID     NUMBER(7) NULL,
  PRIMARY KEY(B_ID)
)
 
ALTER TABLE PIZZA.Bestellung
  ADD CONSTRAINT B_BD_ID
    FOREIGN KEY(B_BD_ID)
    REFERENCES PIZZA.Bestell_Details(BD_ID)
 
ALTER TABLE PIZZA.Bestellung
  ADD CONSTRAINT B_K_ID
    FOREIGN KEY(B_K_ID)
    REFERENCES PIZZA.Kunden(K_ID)

CREATE TABLE PIZZA.Bestell_Details 
(
  BD_ID            NUMBER(7) NOT NULL,
  BD_SK_ID         NUMBER(7) NULL,
  BD_B_ID          NUMBER(7) NULL,
  PRIMARY KEY(BD_ID)
)
 
ALTER TABLE PIZZA.Bestell_Details
  ADD CONSTRAINT BD_BK_ID
    FOREIGN KEY(BD_BK_ID)
    REFERENCES PIZZA.Speisekarte
 
ALTER TABLE PIZZA.Bestell_Details
  ADD CONSTRAINT BD_B_ID
    FOREIGN KEY(BD_B_ID)
    REFERENCES PIZZA.Bestellung