94xjBDLx

1CREATE TABLE WNIOSEK ( 
2	ID_WNIOSKU INT PRIMARY KEY, 
3	ID_KLIENTA INT NOT NULL, 
4	ILOSC_BONOW_SZKOLENIOWYCH INT NOT NULL,  
5	CEL_SZKOLENIA VARCHAR(256) NOT NULL, 
6	ID_PLACOWKA INT NOT NULL, 
7	ID_STATUSU INT NOT NULL, 
8	ID_DECYZJI INT NOT NULL 
9); 
10CREATE TABLE PLACOWKA ( 
11    ID_PLACOWKI INT PRIMARY KEY, 
12    MIASTO VARCHAR(64) NOT NULL, 
13    WOJEWODZTWO VARCHAR(64) NOT NULL, 
14    ULICA VARCHAR(256) NOT NULL, 
15    ID_DYREKTORA INT NOT NULL, 
16    KOD_POCZTOWY VARCHAR(6) NOT NULL 
17); 
18CREATE TABLE STATUS ( 
19    ID_STATUSU INT PRIMARY KEY, 
20    WARTOSC VARCHAR(16) NOT NULL--ALTER TABLE STATUS ADD CHECK (STATUS IN('ODRZUCONY','ROZSTRZYGNIETY') 
21); 
22CREATE TABLE DECYZJA ( 
23    ID_DECYZJI INT PRIMARY KEY, 
24    ID_DECYDENTA INT NOT NULL, 
25    UZASADNIENIE VARCHAR(256) NOT NULL, 
26    UWAGI VARCHAR(256) DEFAULT NULL 
27);     
28CREATE TABLE DECYDENT ( 
29    ID_DECYDENTA INT PRIMARY KEY, 
30    IMIE_P VARCHAR(32) NOT NULL, 
31    IMIE_D VARCHAR(32) DEFAULT NULL, 
32    NAZWISKO VARCHAR(256) DEFAULT NULL, 
33    STANOWISKO VARCHAR(256) NOT NULL
34); 
35CREATE TABLE DYREKTOR ( 
36    ID_DYREKTORA INT PRIMARY KEY, 
37    ID_PLACOWKI INT NOT NULL, 
38    IMIE_P VARCHAR(32) NOT NULL, 
39    IMIE_D VARCHAR(32) DEFAULT NULL, 
40    NAZWISKO VARCHAR(256) NOT NULL 
41);
42CREATE TABLE KLIENT (
43    ID_KLIENTA INT PRIMARY KEY,
44    IMIE_P VARCHAR(32) NOT NULL,
45    IMIE_D VARCHAR(32) DEFAULT NULL,
46    NAZWISKO VARCHAR(256) NOT NULL,
47    PESEL INT NOT NULL,
48    ADRES VARCHAR(256) NOT NULL,
49    WYKSZTALCENIE VARCHAR(256) NOT NULL, 
50    DATA_URODZENIA DATE NOT NULL
51);
52
53ALTER TABLE WNIOSEK ADD (
54    CONSTRAINT UK_PLACOWKI UNIQUE (ID_PLACOWKA)
55    );
56	
57ALTER TABLE WNIOSEK ADD (
58    CONSTRAINT UK_KLIENT UNIQUE (ID_KLIENTA)
59    );
60ALTER TABLE WNIOSEK ADD (
61    CONSTRAINT UK_STATUS UNIQUE (ID_STATUSU)
62    );
63ALTER TABLE WNIOSEK ADD (
64    CONSTRAINT UK_DECYZJA UNIQUE (ID_DECYZJI)
65    );
66
67ALTER TABLE PLACOWKA 
68ADD CONSTRAINT FK_WNIOSEK
69    FOREIGN KEY (ID_PLACOWKI)
70    REFERENCES WNIOSEK(ID_PLACOWKA);
71	
72ALTER TABLE KLIENT
73ADD CONSTRAINT FK_KLIENT
74    FOREIGN KEY (ID_KLIENTA) 
75    REFERENCES WNIOSEK(ID_KLIENTA);
76
77ALTER TABLE STATUS
78ADD CONSTRAINT FK_STATUS
79    FOREIGN KEY (ID_STATUSU) 
80    REFERENCES WNIOSEK(ID_STATUSU);
81    
82ALTER TABLE DECYZJA
83ADD CONSTRAINT FK_DECYZJA
84    FOREIGN KEY (ID_DECYZJI) 
85    REFERENCES WNIOSEK(ID_DECYZJI);
86    
87ALTER TABLE DYREKTOR ADD (
88    CONSTRAINT UK_DYREKTOR UNIQUE (ID_DYREKTORA)
89    );
90
91ALTER TABLE PLACOWKA ADD (
92    CONSTRAINT UK_PLACOWKA UNIQUE (ID_PLACOWKI)
93    );
94
95ALTER TABLE DYREKTOR
96ADD CONSTRAINT FK_PLACOWKA
97    FOREIGN KEY (ID_PLACOWKI) 
98    REFERENCES PLACOWKA(ID_PLACOWKI);
99                                            --- ?
100ALTER TABLE PLACOWKA
101ADD CONSTRAINT FK_DYREKTOR
102    FOREIGN KEY (ID_DYREKTORA) 
103    REFERENCES DYREKTOR(ID_DYREKTORA);
104    
105ALTER TABLE DECYZJA ADD (
106    CONSTRAINT UK_DECYDENT UNIQUE (ID_DECYDENTA)
107    );
108    
109ALTER TABLE DECYDENT
110ADD CONSTRAINT FK_DECYDENT
111    FOREIGN KEY (ID_DECYDENTA) 
112    REFERENCES DECYZJA(ID_DECYDENTA);
113	
114	
115	CREATE OR REPLACE VIEW V_ZLOZONY AS 
116    SELECT w.CEL_SZKOLENIA,k.IMIE_P,k.IMIE_D,k.NAZWISKO,k.PESEL,
117            k.ADRES,k.WYKSZTALCENIE,k.DATA_URODZENIA
118        FROM WNIOSEK w,KLIENT k
119        WHERE NOT(w.CEL_SZKOLENIA IS NULL OR k.IMIE_P IS NULL OR k.NAZWISKO IS NULL OR k.PESEL IS NULL 
120                        OR k.ADRES IS NULL OR k.WYKSZTALCENIE IS NULL OR k.DATA_URODZENIA IS NULL);
121    
122CREATE TRIGGER TR_ZLOZONY 
123    INSTEAD OF INSERT ON V_ZLOZONY 
124    BEGIN
125    UPDATE 
126    TABLE STATUS s
127    SET
128    s.WARTOSC = 'złożony' WHERE s.ID_STATUSU=WNIOSEK.ID_STATUSU;
129    END;
130
131    
132//////////////////
133    
134CREATE TRIGGER TR_DUPLIKAT 
135    INSTEAD OF INSERT ON V_ZLOZONY
136    BEGIN
137    IF (EXISTS (SELECT w.ID_WNIOSKU FROM WNIOSEK w,KLIENT k WHERE k.PESEL=:NEW.PESEL))
138    THEN
139    UPDATE TABLE
140    STATUS s
141    SET
142    s.WARTOSC='odrzucony'
143    WHERE s.ID_STATUSU=WNIOSEK.ID_WNIOSKU AND WNIOSEK.ID_WNIOSKU=NEW.ID_WNIOSKU AND DECYZJA.ID_DECYZJI=NEW.ID_DECYZJI;
144    END; 
145    
146    CREATE TRIGGER TR_DUPLIKAT_DEC 
147    INSTEAD OF INSERT ON V_ZLOZONY
148    IF EXISTS
149        SELECT *
150            FROM KLIENT k  
151            WHERE k.PESEL=:NEW.PESEL;
152     UPDATE TABLE
153     DECYZJA d 
154    SET
155    NEW.UZASADNIENIE='DOKUMENT ZOSTAŁ JUZ ZŁOŻONY',
156    NEW.UWAGI='DUPLIKAT WNIOSKU'
157    WHERE EXISTS (SELECT w.ID_WNIOSKU FROM WNIOSEK WHERE k.PESEL=NEW.PESEL);
158    
159    
160CREATE VIEW V_ODRZUCONY AS 
161    SELECT d.UZASADNIENIE,d.UWAGA
162        FROM WNIOSEK w ,STATUS s ,DECYZJA d, KLIENT k, DECYDENT de,PLACOWKA p,DYREKTOR dy
163        WHERE s.WARTOSC='odrzucony';
164    
165CREATE VIEW V_ROZPATRZONY AS
166    SELECT d.UZASADNIENIE,d.UWAGA
167        FROM WNIOSEK w ,STATUS s ,DECYZJA d, KLIENT k, DECYDENT de,PLACOWKA p,DYREKTOR dy
168        WHERE s.WARTOSC='rozpatrzony'; 
169        
170CREATE OR REPLACE TRIGGER TR_PLECWALIDATOR 
171    BEFORE                                                                                                                                                      
172    INSERT OR UPDATE ON KLIENT
173    DECLARE 
174    woman = 'Kobieta' varchar;
175    man = 'Mężczyzna' varchar;
176    bl_plec EXCEPTION;
177    BEGIN
178    EXCEPTION 
179    WHEN (((NEW.PESEL%2!=0) AND (NEW.PLEC!=man)) OR ((NEW.PESEL%2!=0) AND (NEW.PLEC!=woman)))
180        THEN RAISE bl_plec;
181    END;