This shows you the differences between two versions of the page.
— |
kurs:pizza_sql [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | {{ kurs:cyberpizza.gif}} | ||
+ | ====== Tabellen anlegen ====== | ||
+ | Irene/31.1.2006/1.2.2006\\ | ||
+ | <code sql>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 | ||
+ | / | ||
+ | </code> | ||
+ | |||
+ | ===== Bestellkarte ===== | ||
+ | <code sql>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) | ||
+ | ) | ||
+ | </code> | ||
+ | |||
+ | ===== Pizzazutaten ===== | ||
+ | <code sql> | ||
+ | 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) | ||
+ | </code> | ||
+ | |||
+ | ===== Kunden ===== | ||
+ | <code sql> | ||
+ | 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) | ||
+ | ) | ||
+ | </code> | ||
+ | |||
+ | ===== Bestellung ===== | ||
+ | <code sql> | ||
+ | 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) | ||
+ | </code> | ||
+ | |||
+ | |||
+ | ===== Bestell-Details ===== | ||
+ | <code sql> | ||
+ | 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 | ||
+ | </code> | ||