DROP TABLE WIERSZE_ZAMOWIEN cascade constraints;
DROP TABLE ZAMOWIENIA cascade constraints;
DROP TABLE KLIENCI cascade constraints;
DROP TABLE CENY cascade constraints;
DROP TABLE WYROBY cascade constraints;
DROP TABLE POZIOMY_PENSJI cascade constraints;
DROP TABLE PRACOWNICY cascade constraints;
DROP TABLE ETATY cascade constraints;
DROP TABLE WYDZIALY cascade constraints;
DROP TABLE SIEDZIBY cascade constraints;
CREATE TABLE SIEDZIBY (
ID_SIEDZIBY NUMBER(3),
ULICA VARCHAR2(20));
CREATE TABLE WYDZIALY (
ID_WYDZIALU NUMBER(2),
NAZWA VARCHAR2(20),
ID_SIEDZIBY NUMBER(3));
CREATE TABLE ETATY (
ID_ETATU NUMBER(3),
ETAT VARCHAR2(30));
CREATE TABLE PRACOWNICY (
ID_PRACOWNIKA NUMBER(4),
NAZWISKO VARCHAR2(15),
IMIE VARCHAR2(15),
DRUGIE_IMIE VARCHAR2(15),
PESEL NUMBER (11),
ID_ETATU NUMBER(3),
ID_SZEFA NUMBER(4),
DATA_ZATRUDNIENIA DATE,
PENSJA NUMBER(7,2),
PROWIZJA NUMBER(7,2),
ID_WYDZIALU NUMBER(2));
CREATE TABLE POZIOMY_PENSJI (
ID_POZIOMU NUMBER(3),
DOLNA_GRANICA NUMBER(7,2),
GORNA_GRANICA NUMBER(7,2));
CREATE TABLE WYROBY (
ID_PRODUKTU NUMBER (6),
NAZWA_PIWA VARCHAR2 (20),
ILOSC_ALK NUMBER (2,1),
EKSTRAKT NUMBER (2),
KOMENTARZ VARCHAR2 (2000));
CREATE TABLE CENY (
ID_PRODUKTU NUMBER (6),
CENA_NETTO NUMBER (8,2),
CENA_BRUTTO NUMBER (8,2),
DATA_WYPRODUKOWANIA DATE,
DATA_WYCOFANIA DATE);
CREATE TABLE KLIENCI (
ID_KLIENTA NUMBER (6),
NAZWA VARCHAR2 (45),
ADRES VARCHAR2 (40),
MIASTO VARCHAR2 (30),
WOJEWODZTWO VARCHAR2 (15),
KOD_POCZTOWY VARCHAR2 (9),
NR_KIERUNKOWY NUMBER (3),
TELEFON NUMBER (9),
ID_PRACOWNIKA NUMBER (4));
CREATE TABLE ZAMOWIENIA (
ID_ZAMOWIENIA NUMBER (4),
DATA_ZAMOWIENIA DATE,
ID_KLIENTA NUMBER (6),
DATA_WYSYLKI DATE,
WARTOSC NUMBER (8,2));
CREATE TABLE WIERSZE_ZAMOWIEN (
ID_ZAMOWIENIA NUMBER (4),
ID_WIERSZA_ZAM NUMBER (4),
ID_PRODUKTU NUMBER (6),
CENA NUMBER (8,2),
ILOSC NUMBER (8),
WARTOSC NUMBER (8,2));
GRANT SELECT ON SIEDZIBY TO PUBLIC;
GRANT SELECT ON WYDZIALY TO PUBLIC;
GRANT SELECT ON ETATY TO PUBLIC;
GRANT SELECT ON PRACOWNICY TO PUBLIC;
GRANT SELECT ON POZIOMY_PENSJI TO PUBLIC;
GRANT SELECT ON WYROBY TO PUBLIC;
GRANT SELECT ON CENY TO PUBLIC;
GRANT SELECT ON KLIENCI TO PUBLIC;
GRANT SELECT ON ZAMOWIENIA TO PUBLIC;
GRANT SELECT ON WIERSZE_ZAMOWIEN TO PUBLIC;
CREATE UNIQUE INDEX I_SIEDZIBY$ID_SIEDZIBY ON SIEDZIBY (ID_SIEDZIBY);
CREATE UNIQUE INDEX I_WYDZIALY$ID_WYDZIALU ON WYDZIALY (ID_WYDZIALU);
CREATE UNIQUE INDEX I_ETATY$ID_ETATU ON ETATY (ID_ETATU);
CREATE UNIQUE INDEX I_PRACOWNICY$ID_PRACOWNIKA ON PRACOWNICY (ID_PRACOWNIKA);
CREATE UNIQUE INDEX I_POZIOMY_PENSJI$ID_POZIOMU ON POZIOMY_PENSJI (ID_POZIOMU);
CREATE UNIQUE INDEX I_WYROBY$ID_PRODUKTU ON WYROBY (ID_PRODUKTU);
CREATE UNIQUE INDEX I_CENY ON CENY (ID_PRODUKTU, DATA_WYPRODUKOWANIA);
CREATE UNIQUE INDEX I_KLIENCI$ID_KLIENTA ON KLIENCI (ID_KLIENTA);
CREATE UNIQUE INDEX I_ZAMOWIENIA$ID_ZAMOWIENIA ON ZAMOWIENIA (ID_ZAMOWIENIA);
CREATE UNIQUE INDEX I_WIERSZE_ZAMOWIEN ON WIERSZE_ZAMOWIEN (ID_ZAMOWIENIA, ID_WIERSZA_ZAM);
/*OGRANICZENIA WYBORU TABELI KLIENCI*/
ALTER TABLE KLIENCI ADD
CHECK (ID_KLIENTA IS NOT NULL AND ID_KLIENTA > 0);
ALTER TABLE KLIENCI ADD
CHECK (ID_PRACOWNIKA IS NOT NULL AND ID_PRACOWNIKA > 0); /*ID_PRACOWNIKA =< PRACOWNICY.ID_PRACOWNIKA*/
ALTER TABLE KLIENCI ADD
CHECK (NAZWA IS NOT NULL);
ALTER TABLE KLIENCI ADD
CHECK (ADRES IS NOT NULL);
ALTER TABLE KLIENCI ADD
CHECK (MIASTO IS NOT NULL);
ALTER TABLE KLIENCI ADD
CHECK (NR_KIERUNKOWY IS NOT NULL AND NR_KIERUNKOWY >0);
ALTER TABLE KLIENCI ADD
CHECK (TELEFON IS NOT NULL AND TELEFON >=0); /*TELEFON >=0000000 (SIEDMIO LICZBOWY NR MINIMALNIE)*/
ALTER TABLE KLIENCI ADD
CHECK (WOJEWODZTWO IS NOT NULL);
ALTER TABLE KLIENCI ADD
CHECK (KOD_POCZTOWY IS NOT NULL AND KOD_POCZTOWY >0 ); /*KOD_POCZTOWY>=00000 (PIECIO LICZBOWY NR MINIMALNIE)*/
/*OGRANICZENIA WYBORU TABELI WYDZIALY*/
ALTER TABLE WYDZIALY ADD
CHECK (ID_WYDZIALU IS NOT NULL AND ID_WYDZIALU > 0);
ALTER TABLE WYDZIALY ADD
CHECK (NAZWA IS NOT NULL);
ALTER TABLE WYDZIALY ADD
CHECK (ID_SIEDZIBY IS NOT NULL AND ID_SIEDZIBY > 0); /*ID_SIEDZIBY =< SIEDZIBY.ID_SIEDZIBY*/
/*OGRANICZENIA WYBORU TABELI PRACOWNICY*/
ALTER TABLE PRACOWNICY ADD
CHECK (ID_PRACOWNIKA IS NOT NULL AND ID_PRACOWNIKA > 0);
ALTER TABLE PRACOWNICY ADD
CHECK (NAZWISKO IS NOT NULL);
ALTER TABLE PRACOWNICY ADD
CHECK (IMIE IS NOT NULL);
ALTER TABLE PRACOWNICY ADD
CHECK (DRUGIE_IMIE IS NOT NULL OR DRUGIE_IMIE IS NULL);
ALTER TABLE PRACOWNICY ADD
CHECK (ID_WYDZIALU IS NOT NULL AND ID_WYDZIALU > 0); /*ID_WYDZIALU =< WYDZIALY.ID_WYDZIALU*/
ALTER TABLE PRACOWNICY ADD
CHECK (PESEL IS NOT NULL); /*PESEL = 11*/
ALTER TABLE PRACOWNICY ADD
CHECK (ID_ETATU IS NOT NULL AND ID_ETATU > 0); /*ID_ETATU =< ETATY.ID_ETATU*/
ALTER TABLE PRACOWNICY ADD
CHECK (ID_SZEFA IS NOT NULL AND ID_SZEFA > 0); /*ID_SZEFA =< PRACOWNICY.ID_PRACOWNIKA*/
ALTER TABLE PRACOWNICY ADD
CHECK (DATA_ZATRUDNIENIA IS NOT NULL AND DATA_ZATRUDNIENIA >= TO_DATE('01/09/2003', 'DD/MM/YYYY'));
ALTER TABLE PRACOWNICY ADD
CHECK (PENSJA IS NOT NULL AND PENSJA >= 1500); /*PENSJA >=PENSJA.DOLNA_GRANICA AND PENSJA =ALTER TABLE PRACOWNICY ADD
CHECK (PROWIZJA IS NOT NULL OR PROWIZJA IN NULL);
ALTER TABLE PRACOWNICY ADD
CHECK (PROWIZJA > 0 AND PROWIZJA < 500);
/*OGRANICZENIA WYBORU TABELI WIERSZE_ZAMOWIEN*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
CHECK (ID_ZAMOWIENIA IS NOT NULL AND ID_ZAMOWIENIA >0); /*ID_ZAMOWIENIA =< ZAMOWIENIA.ID_ZAMOWIENIA*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
CHECK (ID_WIERSZA_ZAM IS NOT NULL AND ID_WIERSZA_ZAM >0);
ALTER TABLE WIERSZE_ZAMOWIEN ADD
CHECK (ID_PRODUKTU IS NOT NULL AND ID_PRODUKTU >0); /*ID_PRODUKTU =< WYROBY.ID_PRODUKTU*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
CHECK (CENA IS NOT NULL AND CENA >0.0 AND CENA<15.00); /*CENA=CENY.CENA_BRUTTO PRZYPISANE WARTOSCI AUTOMATYCZNE*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
CHECK (ILOSC IS NOT NULL AND ILOSC >0);
ALTER TABLE WIERSZE_ZAMOWIEN ADD
CHECK (WARTOSC IS NOT NULL AND WARTOSC=CENA*ILOSC);
/*OGRANICZENIA WYBORU TABELI ETATY*/
ALTER TABLE ETATY ADD
CHECK (ID_ETATU IS NOT NULL AND ID_ETATU > 0);
ALTER TABLE ETATY ADD
CHECK (ETAT IS NOT NULL);
/*OGRANICZENIA WYBORU TABELI SIEDZIBY*/
ALTER TABLE SIEDZIBY ADD
CHECK (ID_SIEDZIBY IS NOT NULL AND ID_SIEDZIBY > 0);
ALTER TABLE SIEDZIBY ADD
CHECK (ULICA IS NOT NULL);
/*OGRANICZENIA WYBORU TABELI CENY*/
ALTER TABLE CENY ADD
CHECK (ID_PRODUKTU IS NOT NULL AND ID_PRODUKTU > 0); /*ID_PRODUKTU =< WYROBY.ID_PRODUKTU*/
ALTER TABLE CENY ADD
CHECK (DATA_WYPRODUKOWANIA IS NOT NULL);
ALTER TABLE CENY ADD
CHECK (CENA_NETTO > 0 AND CENA_BRUTTO > 0);
ALTER TABLE CENY ADD
CHECK (CENA_BRUTTO=CENA_NETTO*1.23);
ALTER TABLE CENY ADD
CHECK (CENA_NETTO IS NULL OR CENA_BRUTTO IS NULL OR CENA_NETTO <= CENA_BRUTTO);
ALTER TABLE CENY ADD
CHECK (DATA_WYCOFANIA IS NULL AND DATA_WYCOFANIA IS NOT NULL OR DATA_WYPRODUKOWANIA <= DATA_WYCOFANIA);
ALTER TABLE CENY ADD
CHECK (DATA_WYCOFANIA >=TO_DATE('01/01/2012', 'DD/MM/YYYY') AND DATA_WYPRODUKOWANIA >=TO_DATE('01/01/2012', 'DD/MM/YYYY') );
/*OGRANICZENIA WYBORU TABELI WYROBY*/
ALTER TABLE WYROBY ADD
CHECK (ID_PRODUKTU IS NOT NULL AND ID_PRODUKTU > 0);
ALTER TABLE WYROBY ADD
CHECK (NAZWA_PIWA IS NOT NULL);
ALTER TABLE WYROBY ADD
CHECK (ILOSC_ALK IS NOT NULL AND ILOSC_ALK >0.0);
ALTER TABLE WYROBY ADD
CHECK (EKSTRAKT IS NOT NULL AND EKSTRAKT >=10);
/*OGRANICZENIA WYBORU TABELI POZIOMY_PENSJI*/
ALTER TABLE POZIOMY_PENSJI ADD
CHECK (ID_POZIOMU IS NOT NULL AND ID_POZIOMU > 0);
ALTER TABLE POZIOMY_PENSJI ADD
CHECK (DOLNA_GRANICA >=1500.00 AND GORNA_GRANICA >=2000.00);
/*ALTER TABLE POZIOMY_PENSJI ADD
CHECK (DOLNA_GRANICA =<3501.99 AND GORNA_GRANICA =<9999.99);*/
ALTER TABLE POZIOMY_PENSJI ADD
CHECK ((DOLNA_GRANICA IS NULL AND GORNA_GRANICA IS NOT NULL) OR (DOLNA_GRANICA IS NOT NULL AND GORNA_GRANICA IS NULL) OR (DOLNA_GRANICA <= GORNA_GRANICA));
/*OGRANICZENIA WYBORU TABELI ZAMOWIENIA*/
ALTER TABLE ZAMOWIENIA ADD
CHECK (ID_ZAMOWIENIA IS NOT NULL AND ID_ZAMOWIENIA > 0);
ALTER TABLE ZAMOWIENIA ADD
CHECK (ID_KLIENTA IS NOT NULL AND ID_KLIENTA >0); /*ID_KLIENTA =< KLIENCI.ID_KLIENTA*/
ALTER TABLE ZAMOWIENIA ADD
CHECK (DATA_ZAMOWIENIA >=TO_DATE('01/01/2012', 'DD/MM/YYYY') AND DATA_WYSYLKI >=TO_DATE('01/01/2012', 'DD/MM/YYYY'));
ALTER TABLE ZAMOWIENIA ADD
CHECK ((DATA_ZAMOWIENIA IS NOT NULL AND DATA_WYSYLKI IS NOT NULL) OR (DATA_WYSYLKI IS NULL) OR (DATA_ZAMOWIENIA>=DATA_WYSYLKI));
ALTER TABLE ZAMOWIENIA ADD
CHECK (WARTOSC >= 0); /*WARTOSC=WIERSZE_ZAMOWIEN.WARTOSC PRZYPISANE WARTOSCI AUTOMATYCZNE*/
/*KLUCZE PODSTAWOWE*/
ALTER TABLE KLIENCI ADD
PRIMARY KEY (ID_KLIENTA);
ALTER TABLE WYDZIALY ADD
PRIMARY KEY (ID_WYDZIALU);
ALTER TABLE PRACOWNICY ADD
PRIMARY KEY (ID_PRACOWNIKA);
ALTER TABLE WIERSZE_ZAMOWIEN ADD
PRIMARY KEY (ID_ZAMOWIENIA, ID_WIERSZA_ZAM);
ALTER TABLE ETATY ADD
PRIMARY KEY (ID_ETATU);
ALTER TABLE SIEDZIBY ADD
PRIMARY KEY (ID_SIEDZIBY);
ALTER TABLE CENY ADD
PRIMARY KEY (ID_PRODUKTU, DATA_WYPRODUKOWANIA);
ALTER TABLE WYROBY ADD
PRIMARY KEY (ID_PRODUKTU);
ALTER TABLE POZIOMY_PENSJI ADD
PRIMARY KEY (ID_POZIOMU);
ALTER TABLE ZAMOWIENIA ADD
PRIMARY KEY (ID_ZAMOWIENIA);
/*KLUCZE OBCE*/
ALTER TABLE KLIENCI ADD
FOREIGN KEY (ID_PRACOWNIKA) REFERENCES PRACOWNICY;
ALTER TABLE WYDZIALY ADD
FOREIGN KEY (ID_SIEDZIBY) REFERENCES SIEDZIBY;
ALTER TABLE PRACOWNICY ADD
FOREIGN KEY (ID_ETATU) REFERENCES ETATY;
ALTER TABLE PRACOWNICY ADD
FOREIGN KEY (ID_SZEFA) REFERENCES PRACOWNICY;
ALTER TABLE PRACOWNICY ADD
FOREIGN KEY (ID_WYDZIALU) REFERENCES WYDZIALY;
ALTER TABLE WIERSZE_ZAMOWIEN ADD
FOREIGN KEY (ID_ZAMOWIENIA) REFERENCES ZAMOWIENIA;
ALTER TABLE WIERSZE_ZAMOWIEN ADD
FOREIGN KEY (ID_PRODUKTU) REFERENCES WYROBY;
ALTER TABLE CENY ADD
FOREIGN KEY (ID_PRODUKTU) REFERENCES WYROBY;
ALTER TABLE ZAMOWIENIA ADD
FOREIGN KEY (ID_KLIENTA) REFERENCES KLIENCI;
exit;
1) jak skonstruować warunek który będzie odwoływał się do innej tabeli i nie będzie przekraczana jej wartość. przykład: