UNIVERZITET U BEOGRADU FAKULTET ORGANIZACIONIH NAUKA
������� �� ��������
� B��� �������� 2 �
A�����: ����. �� Z���� M��������ć ����. �� N���� A��č�ć ���. �� S��đ�� B�������ć �� M����� J������ć M������� L����č�ć S�đ� B����������ć E���� M���������ć
B������, 2013.
������� 1.
D��������������......................................... .................................................. .................................... 4 1.1.
P����� ��������������� 2NF ........................................................ ............................................ 5
1.2.
P������ ��������������� 3NF (P����������) ....................................................... ....................... 7
1.3.
P������ ��������������� 3NF (S���� � ������� ����) ............................................... .................. 9
R�������� .............................................................................................................................................. 11 2.
T������ ................................................ .................................................. .......................................... 12 2.1.
��� �����, � ����� ��������� �� ��������� .................................................... .................. 12
2.2.
P������ ���� ������� ................................................ ..................................................... .......... 14
2.3.
P����� ������� ................................................. .................................................. ..................... 15
2.3.1.
T������ � O����� SUBP�� ....................................................... .......................................... 16
2.3.2.
T������ � M�������� SQL S����� SUBP�� .................................................... ..................... 21
2.3.3.
T������ � P������SQL SUBP�� .......................................................... ............................... 24
R�������� .............................................................................................................................................. 25 3.
O����������������� ����� .............................................. ..................................................... .......... 26 3.1.
K������� ���������� ������ ................................................ .................................................. .. 26
3.1.1.
S����������� ��� ............................................................ .................................................. 26
3.1.2.
D������� ��� .............................................. .................................................. ..................... 31
3.2.
K����������� ������ .................................................... .................................................. .......... 33
3.2.1.
R��������� ��� ................................................. .................................................. ............. 33
3.2.2.
T�� �����.................................................. .................................................. ..................... 33
3.2.3.
V������ ................................................... .................................................. ..................... 34
3.2.4.
N����� ����� ................................................... .................................................. ............. 34
R�������� .............................................................................................................................................. 36 2
4.
O����������� ................................................. .................................................. ............................... 37 4.1.
T������ ������������ ��������� �� ��������� �����������................................................ 37
4.1.1.
S������ D�������� V����� ������� ������������ ................................................. ............ 37
4.1.2.
R�������� S����� D����� ���� M����� ������� ������������ ........................................... 40
4.1.3.
K������ D������ ���� M����� ������� ������������ ............................................... ........ 42
4.1.4.
H��� � C���� V����� ������� ������������ ...................................................... ............. 42
4.2.
I������ ............................................... .................................................. .................................. 43
4.3.
V��������� ��������������� ................................................. .................................................. .. 45
4.4.
U����������� P�������� ...................................................... ................................................ .. 46
4.4.1.
P����� ������������ ���������: A������� ���� .................................................. .......... 46
4.4.2.
P����� ������������ ���������: U����� ����� ���������� ........................................... 49
R�������� .............................................................................................................................................. 53
3
1. ��������������� D�������������� �� �������� ����������� ����������� ���� �������� ���������� ������������ ��������, �� ����������� ��������� �����. D����č��� ��č���, ��������������� ����������� �������� ����������� �������� �� ���� � ���� �������� �����. D�������������� ����� � ����č�� ������������� ���� �������� �� ����č��� �����, ��� č��� �� ��� ������� ��č�� ����� ������� ������������� ������������ ����� ������� � ���� ����č��� �������������. N�� ���, ������� �������������� �������, ����� �� ���� ���������������, ��� č��� �� ���������� �������� �����, ����� ���������� �������� � �������� ���������� ��������. Z��� ������� ���������� � ����������� ����������� ��������, ��������� �� ���� ����������� ��� ������������ ��������� ���������������, ��. �����ć��� �č������ ����������� � ��������������� ������������ �������� (���. ��������� ������� ��� ���������������� �������� � ���� ��������). U �������� �� ���� ������� ��������������� 2NF � 3NF, ����������� ��������� PMOV � � � ���������� ������. Z� ����� ������, ��. �� ����� ���������������, ��������� �� �������������� � ����������ć� ������� ������ �� ������� ���������� ���������������� � ������������ ��������. P�� ���� �������������� �������, �������� �� ���� ������� ������������� � ���� ������. U ������ ������ ������������� ������� ������� ������ �� ��� ��������� ������� � ���� �� ������ ���� ������ ���� �� ������� ���������� ��� �������� �� DML ��������� ��� �������� ���� �� �č��������� � ���������������. O���� ��������� ������ ������������� ������� ��: T����� ������������1
T�� �������
K�����
P�������
I�����
DA
��� ����� �� �����? ������������������ (2�3 ������ )
������������2
U����� D����� I����� U�����
NE NE NE DA NE DA DA
������������ ������������1 ������������2 ������������3
D�����
������������������ ������������������ ������������������
Tabela 1 - Opšta struktura tabele specifikacije trigera
K�� ��� �� ���� ������, � ����� ������ ������ �� ����� ������ ��� ����� �� �������� ������. P���� �� �� ��� ������� DML ������� �, ������� �� ��������, ��������� ������ � ������ ������, ���� ���� ������ ���� �������� ������ ��� ������� �� ����������ć� DML ������� ��� �������, �������, ������� ������.
4
1.1.
������ ��������������� 2��
P������������ �� �� ��� �����ć� ������������� �����1:
Kupac(ŠifraKupca,
NazivKupca, Ra čunKupca, TelefonKupca, FaksKupca, Mati čniBroj, PIB, IDMesta) IDMesta) ŠifraRadnika) Porudžbenica(ŠifraKupca, BrojPorudžbenice, Datum, ŠifraRadnika)
Oč������� �� �� ć� �������� ���� �� �������������, ����� ����� �����, ����� č���� ���� ��������� ������������ � ������ �����. U������ �� ����� ����� ������������, ������������ ������ ����� � ������ ������������ �� ��������� �������� ������ (JOIN ��������) � ������ ����������ć�� SQL �����, ��� �� ��������� �������� ���������. K��� �� �� ������� ���������� �������� JOIN ��������, ����ć� �� �������� ��������������� ��������, ��� č��� �� ������� N����K���� �������� K���� ����������� � � �������� P����������� (��. � �������, ������� �� �������� PMOV). ��������������� ����� �� ����� �� ���� ��������:
Kupac(ŠifraKupca,
NazivKupca, Ra čunKupca, TelefonKupca, FaksKupca, MatičniBroj, PIB, IDMesta) IDMesta) Porudžbenica(ŠifraKupca,
BrojPorudžbenice,
Datum,
NazivKupca,
ŠifraRadnika) ŠifraRadnika)
K�� ��� �� ���� ������, ����� �������� ���������������, �������� P����������� ������ � ����������� ������� N����K����, ���� �� ����đ� ������ � � �������� K����. Oč������� �� �� ���đ���� ���������� ���� ���� �������� ���������� ���� ��������, ������� ����������� ������ ���� ������������ (���. ������� �� ���������� �� ��������� �������� N����K���� � ���������� K���� � P�����������, �� ���� ����� �����, ��. �� ����� �����, ���� �����č���). Z��� ���� �� ��������� 1
Zbog preglednosti, na PMOV – u neće biti prikazivani svi atributi entiteta niti sve njihove veze.
5
���������� � �������������� ����������ć� �������, ���� ć� ��������� ���������� � �������������� �������� � ���� �������� �������� ����������� DML ������� ���� ���� ������� �� �������� �����������. U ������ �� ��������� ��������������� �����, ������ ���� ���� �������� ���������� �������� ��: •
•
•
•
I����� ��������� �������� N����K���� � �������� K����. N�������� �� �� �� ���� ������ ������ � � �������� P�����������. D�������� ���� ������������. N� ��� �� ��������� ����������� ���� ��������� �������� N����K���� � �������� P�����������, ��ć �� ������� �������� ��������� �� ������ ����� ��������� �������� �����K���� � ������ ���� ��������. I����� ��������� �������� N����K���� � �������� P�����������. O�� ������ �� ��� ���� ����������, ������� �� �� ������ �� ����������������� �� ��������� �������ć� �������� N����K���� � �������� K����. I����� ��������� �������� �����K���� � �������� P�����������, ������� �� ����������. P�� ������ ��������� �������� �����K���� � �������� P�����������, �������, ��� ������ ����� ���� �� ������ ������������, ���� �� �������� ���������� ��������� �������� N����K���� � ����� ��������, ���� �� ��������� ������ ������������� �����.
O �č������ ����������� ���� �������� ��� ���� �������� ����� ������� č��� �� ������������� ���� � ���� ������. ������ ������������� ������� ������� �� �������� ������ ��������������� ���� �� �����. S������������ ������� ���� �� �� ��� ���č����� � ������ ���� ��ć� �� ����������� ����������� ���� ��������, � ��� č��� �� ���č����� ���� ��ć ������ć��� ��������������� ������������ (���. �������� ����� �� ����� ������� ��� ����� ������������ �� ������ć��� ��������������� ������������, �� ���� ���� ���� �������� ��� ������ ���� ć� ���� ������ć��� ��������). T����� �����
T�� ������� I�����
U�����
K�����
P������� NE
N����K����
DA
D����� ������������
��� ����� �� �����?
P������� ������ ��������� ������ N����K���� � ������ K����, �����ć� �� ������ ���� ��������� �������� ������� � ������ P�����������.
NE
I�����
DA
�����K����
DA
N����K����
DA
U�����
D�����
NE
* Ukoliko je uopšte dozvoljeno ažuriranje kolone ŠifraKupca 6
T����� ������� �������� ������ N����K���� �� ������ ����� ��������� ������ �����K����. T����� ���� ���������� ������ N����K���� �� ������ ��������� ��������� ������ �����K����.* Z�������� �������� ���������� ��� ������.
V���� ��������������� � ����� �� ������č�� ������� �� ������������� �������� (� ���� �������, K����) ������ � �������������ć� �������� (� ���� �������, P�����������), ����� �������� ����č� � �������������ć�� �������� ��� ���� ��������� ����č� �������������ć� ��������, ������ �� ��� � �������� U ���� �������, �������� �� P�� � ������� ��������������� ����������� �� ��� ��������� ����č� �������� P����������� (������� �����K����, ���� �� ����������� � �������� ����č �������� K����). T���đ�, � ���� ���č��� �������� �� ��������������� D���� �������� �����. N����, �� ������������� �������� P����������� ������ �� �����ć� ������������ ���������: ŠifraKupca, BrojPorudžbenice -> Datum, ŠifraRadnika
P���� ���������������, �� ��������������� �������� P����������� ���� �����ć� ������������ ����������: ŠifraKupca, BrojPorudžbenice -> Datum, NazivKupca, ŠifraRadnika ŠifraKupca -> NazivKupca
O������ �� �� ������� N����K����, ����� � �������� P����������� ����� ���������������, ��������� ������������ ������ �� �������� �������� �����K����, B���P�����������, ��� �� ������������ ������� � �� ����� � �� �������� ���� (�����K����), ��� �� ���� ������ �� ������ ������������� ���������� ���� �� ������ ����� ��������������� �������� P�����������. S���� ���, �������� P����������� ���� ���� � D����� ��������� �����.
1.2.
������� ��������������� 3�� (�����������)
P�������� ��������, P�� � ������� ����� ���������������, ���� �� ��������� � �� ��������������� T��ć� �������� �����, ���� �� ����������� ������� ������ � �������� �� ������ �������ć�� �������� ����č�. P������������ �� �� ��� �����ć� ������������� �����:
KUPAC(ŠifraKupca, NazivKupca, PIB, DržavaKupca, MestoKupca,
AdresaKupca,TelefonKupca,FaxKupca) ZAHTEV (ŠifraZahteva, DatumZahteva, ŠifraValute, ŠifraMetodaIsporuke,
ŠifraNačinaPlaćanja, ŠifraKupca)
7
K�� ��� �� ���� ������, ���������� �������� Z����� � �������� K���� � ���������� ������ �������� �� �������� ����č�� �����K���� � �������� Z�����. S��č�� ���������� �������, ���� �� �č������� �� ć� � ���� �� ��������, ����� ���� �����K����, č���� ���� �������� � ������ �����, ��. �������� �������� N����K���� �� �������� K����, ��� �� � SQL ������� ���� ��������� �������� JOIN ��������. A������� ���������� �������, � ���� �� �� ����� �������� P�� � ������� ���������������, ��� č��� �� �� ������� N����K���� ����� � �������� Z�����. ��������������� ����� �� ���� ��������:
KUPAC(ŠifraKupca, NazivKupca, PIB, DržavaKupca, MestoKupca,
AdresaKupca,TelefonKupca,FaxKupca) (ŠifraZahteva, DatumZahteva, ŠifraValute, ŠifraMetodaIsporuke, ZAHTEV ŠifraNačinaPlaćanja, ŠifraKupca, NazivKupca )
������ ������������� ������� �� ��������� ������ ��������������� �� ����: T����� �����
T�� ������� I����� U�����
K�����
P������� NE
N����K����
DA
D����� ������
��� ����� �� �����?
P������� ������ ��������� ����� N����K���� � ������ K����, �����ć� �� ������ ���� ������� �������� � ������ Z�����.
NE
I�����
DA
�����K����
DA
N����K����
DA
U�����
D�����
T����� ������� �������� ������ N����K���� �� ������ ����� ��������� �������� �����K����. T����� ���� ���������� ������ N����K���� �� ������ ��������� ��������� ������ �����K����. Z�������� �������� ���������� ��� ������.
NE
I��� �� � � ���� ���č��� �����ć��� P�� � ������� ����� ���������������, �� ������� �� ���������� �������, ���� �� �������� ��������������� T��ć� �������� �����. N����, �� ������������� �������� Z����� ������ �� �����ć� ������������ ���������: ŠifraZahteva -> DatumZahteva, ŠifraValute, ŠifraMetodaIsporuke, ŠifraNačinaPla ćanja, ŠifraKupca
8
P���� ���������������, �� ��������������� �������� Z����� ���� �����ć� ������������ ����������: ŠifraZahteva -> DatumZahteva, ŠifraValute, ŠifraMetodaIsporuke, ŠifraNačinaPla ćanja, ŠifraKupca, NazivKupca ŠifraKupca -> NazivKupca
M��� �� ��������� �� ���� ������� N����K���� ����������� ������������ ������ �� �������� �����Z������, ����� �������� �����K����. S���� ���, �������� Z����� ���� ���� � T��� ��������� �����.
1.3.
������� ��������������� 3�� (����� � ������� ����)
U ��������� ��� ������� �����ć��� �� P�� � ������� ����� ��������������� ����� �� ����������� ��������������� D���� � T��ć� �������� �����. O��� ć� ���� ��������� ��� ����� ����� ���������������, ����� � ������� ���� ���������������, ����� �� ���� ��������������� T��ć� �������� �����. P������������ �� �� ��� �����ć� ������������� �����:
Naselje(NaseljeID, PoštanskiBroj, Naziv, GradID) Grad (GradID, Naziv, DržavaID) Drzava(DržavaID, Naziv)
M��� �� ������������� �� ć�, �������� ���� �� ��������, č���� ���� �������� � ������ � ������ � ����� �� ������� ������. D����č��� ��č���, ��ć� ��������� ���������� �������� JOIN ��������, ��. ���������� �������� ������, ������� �� �� � SQL ������� ���� ��������� �������� �������� ������ N������ � G���, � ����� � �������� �� ������� D�����. K��� �� �� ���� �������� ������ �������, ���� �� S���� � ������� ���� ���������������, ���� �� ������������� ��������� �������� ����č� D�����ID � �������� N������, �������, ��������� ���� ����đ� N������ � D�����. T���� ��������������� ����� �� ���� ��������:
Naselje(NaseljeID, PoštanskiBroj, Naziv, GradID, DržavaID ) Grad (GradID, Naziv, DržavaID) Drzava (DržavaID, Naziv)
9
O��� ���������������� ���� ��������� �������� ������ �� ��������, ������� �� �� ���� �������� �������� �������� ���� ������ N������ � D����� ��� ���������� ������ G���, ���� �� ����� �������� � ������� �������� ��������� � ������� � ������. ������ ������������� ������� �� ���� ������ ��������������� �� ����: T����� ����
�������
T�� ������� I����� U�����
K����� D�����ID
D����� I����� U�����
P������� NE DA
U������ ������� N������ �� G���, ������� D�����ID � ������ N������.
NE DA G���ID
DA
D�����ID
DA NE
D�����
��� ����� �� �����?
U ������ D�����ID ������� ����������ć� �������� �� ����� �������� G���ID. U ������ D�����ID ������� ����������ć� �������� �� ������ ���� ��������� �� G���ID S���č��� �������� ������.
S� ���������� ��������� �č������� �� �� �� ����� ����� � ����� ����� �������������, ������� �� �� �������� T��ć� �������� �����. N����, �� ������������� �������� N������ ���� �����ć� ������������ ���������: NaseljeID -> PoštanskiBroj, Naziv, GradID
P���� ���������������, �� ��������������� �������� N������ ���� �����ć� ������������ ����������: NaseljeID -> PoštanskiBroj, Naziv, GradID, DržavaID GradID -> DržavaID
M��� �� ��������� �� ���� ������� D�����ID ����������� ������������ ������ �� �������� N������ID, ����� �������� G���ID. S���� ���, �������� N������ ���� ���� � T��� ��������� �����.
10
��������� D�������������� �� ��� ����� � �������� �������� ���� �� ���� ������ ��������. I���� �� �������� ���� ��������� � ������ ��, � �������� ����, ���������� ������� ���������������. 1. W�������� M., M������ B., �I����� ���������� D����� ��� ���� E������� �� A������ , S�������, 2007. 2. M������ C., �D������� A�������������: ��� C������� G���� �� ��������� ��� ����������� , A������ W�����, 2002. 3. C������ T., B��� C., �D������� ���������: A ������������ ����� �� �������� ���������� , P������ E�������� L������, 2004. 4. L��������� S., T����� T., N����� T., ��������� D������� D������ , M����� K�������, 2007. 5. B����� S., �� ��., �D������� D�����: K��� �� ���� , M����� K�������, 2009. 6. P����� G., �B�������� D������� D������ , W���� P���������, 2006. 7. H����� J., P������� M., M�F����� F., ������� D������� ����������� , P������ E��������, 2007. 8. H������� S., �D��� ��������� ���������� , J��� W���� & S���, 2002.
11
2. ������� T����� �� ���� ���������� ��� ������������ ��� ���� �� ���������� �������� ����� ��� ���� �� ���� ���������� ����đ�� ��� ����đ���� ������� ��� ��������. T������ �� �������č�� ����� ECA (E���� � ��������� � ������) �������. O�� ������� �� ��� ���� � ����������� �������, � ���� �� ���������� �� �����ć� ��č��: �� ����đ��
�����������
�� ����� �� ������
������������
D���đ�� �� ��������� ���������� ���� ��������, ����� �� ����������� SQL ��������, � ������ �� �������� SQL �������. P�� ����������� �� ������������� ��������� INSERT, UPDATE ��� DELETE. G��� �������� ������� �� �������� DML (D��� M����������� L�������) ������� ��� �� �� �������� ��� ��� DML ������� � ������� �� �������� ������������ ��������. N��� ������� ����đ� ���������� � ����� ����� ������� ���� �� ������� ��� ��������� ��������� DDL (D��� D��������� L�������) �������, ��� ��� �� ��������� ������, ��� ����� ����đ��� ��� ��� �� ������ � �������� �����������. O����� DDL ������� �� ���� ��������� � ���� �������� �� ������� ��������. D�� ������� �������������� ������� ��: •
������� �� ������� ��������� ��� ���������
•
������� �� ���� �������� ������ ��� �������� ���� �����������
2.1.
��� �����, � ����� ��������� �� ���������
T������ �� ��� ����� ������� �� �č������ ����������� ���� ��������. N� ��� ��č�� �� ���� �������������� ������� �����������, ���� �� ���� �� ��� �����, � �� ��: ������� ����������� ������ � �������� ������� ����������� (�� ���������� ����������� ���������). N� ������, � ���� ����� ������ �� ��������č��� � ������ �� �������������� ���� �� ������� ����đ���� ��������č���. K�������� ������������ ���� ���� ������� ������ � ���� ������ ��������č�. N� ������ ������� �����đ���� PMOV�� � ��������� �����, ����� �� ć� ������������ �� ���� ����� ������� ����č, ���� �� ������� �� ��������č�. O�� ��� �� ���� ������, � č���� ��������, ����� �� �� ������������ ����� � ����� ��������č�, � �� ���� ������� ����� ��� ���� ����������� ���������� �������� ������.
12
N� ���� ��������������� �������, ����� ������ ����� �������. N� ����� ���������������� ������, ������� �� ������������� �������������� �������� � ���č��� ���������� N�����D�������č�. J���� �� �� ������ N����� � ������ D�������č, �� ����� �����č� � ���������� ��� ����� ����� � ������ N�����������. U����� �� �� ����� ����� ������, ���� ć� �� ������� ����� ��� ���� �� ������ ����. T������ ��, ����đ�, ���� ��������� �� ���������� ��������, ���� �� ����� � �� ������������ ���� ������� ��������� �����ć����� T�SQL ������. T������ �����ć����� ���������������� ���� �������� �� ��������� ������� ���� ����đ� ��������� ���� ��������. D���� ������ �����ć���� ������� �� ��������� ����� �� ��������� ��č���. T�� �������� ������� �� ������� � ��� ���č���: •
•
U������ ���� �������� ����� �� ��č���, ����� �� ������ ���� ć� � ���� ���������� ��������� U ���č��� �� ��� �������� ��������� �� ��č���, �������� ć� ����ć� ������� �����, � ��� �������� ć� �� ������� ������ ���� ć� ������� ������������ ��������� �� ���������� ������ �� ��č��� ���������
13
2.2.
������� ���� �������
F�� � ����� ����� ���� ���� ������ ��� AFTER ��������, ��� � ��������� ����� ���� �� ���������� ���� ����� ��������, ��� ��� BEFORE �������� ��� ��������� �����. T�� ����� ��������� BEFORE ������� ��� � ����� ����� � ������� �� ��� ��� �� ���� �������� �� ��� ��������� ��� ����� ��� ������ ������������� �� ��� ��� ���� ��� ����� ����������. T�� ������ �� ���� BEFO RE ������� �� �������� �� ����� �� ��� ���� ��������������. W��� ��� �� ��� BEFORE �������� ���� ��� ��������� �� ��� ����� ���� ���� �����, ��� ������������ ������ ���, ��� ������ �� ��� BEFORE ������� ������������� �� ��� ���� �������� �� ��� ������� ����� ���������, �� ������� �� ��� �����. T��� ���� AFTER ������� ���������� ���� ��� ����� �� �����. T�� AFTER �������� ����� ������ ��� ���� �����, ������� �����, �� ������� �� ������ �������� �� ��� ��������. T�� ��������� ���������� ����� �� �������� ������� ��������� �������� �� ����� ���. B��������, BEFORE �������� ��� �� ��������� �� ��� ���������� ��������� �� ��� �������� ���������� ������. T��������, ��� � �������� ��� ���� ��: P������ ���������� �� ����� ���� A������������ �������� ������ ��� ����� �������� ���� R��� ���� ����� ������ ��� ����������������� �������� BEFORE �������� ��� ��� ���� ��� ������� ��������� ��� �������� ������� ���� ��� ��������� ������ ��� ������� ����� �� ������� �� ��� ��������. C�����������, ���� ��� ��������� ������ ��������� ����������� ��� �������.C���������, ��� ��� ���� AFTER �������� �� � ������ �� ����������� ����� ���� ���� �� ��� �������� ����� ���� � �������� ����� ������. A� � ���� �� �� �����������, AFTER �������� ������ ��� ��� �������� �� � ���������� �����. N��� ���� ���� ��� ��� ����� ��� ��������� ���������� �����������. C�����������, ��� ��� ��� ���� ������ �� ������� ���������� ���� �� ����������� ��� ���� �������. F�� �������: P������ ������ �� ������ ���������� �� ��� ��������. P������ ������� ������� ��� ��������, ��� �������, �� ������� ������. N ��� ���� ������� ��������� ������� ��� �������� ��� ��� ������ ���� �� ��� ������� �� ������ ����. I� ��������, ��� ��� ���� �� INSTEAD O F ������� �� � ����������� �� ��� ������� ��������� �� ��� ���� �� �� ������� ��. F�� �������, �� ��� ������ ���� �� ��� � ��� �������� �� ���������� ���� � �����, ��� INSERT ��������� �� ��� ���� �� ��� INSTE AD OF INSERT ������� ���� ������� ��� ������� ����������. • • •
• • •
�������� �� �����://���.���.���.����
14
2.3.
������ �������
T������ �� ���� ��������� ������ �� ���� ��������� ���������� ��� �� ������ �� ����� ��� ���� �� ��������. U ���������� �� ����, ����������� ��� ����� �������: ������� ���� �� �����ć� �� ����� ������� (��������������� �������) ������� ���� �� �����ć� �� ����� ���� (��������� �������) • •
S������� ����� �� ����������� ��� ������� �� �������, � ����� SQL: 1999 ������� �������� �� FOR EACH STATEMENT. U������ ��������� ��������� ������, ������ ��������� ��� � ��� ����č�� ��č� ���� ������� ����� �� ��������� �� ��� ���� �� ���, �������� ��� �������. U������ ������ ���������� ��� ��������� �����, ��� ������ ���� ��������. U ���������� �� �������� ���������� ����������� �����ć� ����� �������: •
������ O������ ������ �� ���������� �� ����� ��� ��� ���� ��� �� ����đ�� ������ ������. T����� �� ���� �� ��� ������ ������ ć� ���� ��������� ��� ����� ��� �� BEFORE ������ ������� ���������� ������ �� ����� ���. I� ��������� ���������, �������� �� ������č�� �� BEFORE �������� ������ ���� ���������� �� ����� ���� (FOR EACH ROW).
•
����� O������ ������ �� ���������� �� ����� ��� ��� ������ �� ����� �������, � ���������� �� ���������� ����� �������. O�� ������ �� �������� ����� ��������� ����� ���� �� ������� �������� ������� � ����� ������� ���� ���������, ��� ��� �� �������������� ����������, ���� ���� ���� �������� ����� ����������� �����. O��� ������ ��������� �� �� AFTER ������ ���� ���������� � �� ����� ���� � �� ����� ������� (FOR EACH ROW ��� FOR EACH STATEMENT)
•
������� �� INSTEAD OF ������� ������ ����� ������������ FOR EACH ROW, � ���� �� ���������� �������� ��� ��������. N� ����� ����� ������, ��� ����, �� �� ���� ���������� ��� ��������.
U ���������� ������ �������� �� �����ć���� ��� � ��� ���������. O��� ��č�� �������������� ���� �������� ������� �� �������� AFTER ������. K�� ��� �� ������� � ���������� ������, ��� AFTER ������� ������ �� �������� ��� ����� ��������� ����đ���. U����� ��� �� ������ ����� �������� ��č�� �������������� ���� ����ć ��� ��� ����� �������. T������ �� ���� �������� � ����� ����đ��� ���� ������� ������� ����������. U ���������� �� ���� ��������� ����������, ����������� �����ć� �������: • • •
������ ������ ������ 15
2.3.1. ������� � ������ ������ ��� �� ��č� ���� SUBP��, �� �����ć��� ����������� ���� ��������� ������ �������. U������ ������ �� ���������� ��� ���� ��������� ������, ���� ć��� �� ���������� ������� ���������� ����č�� ��č ��� ���� ���. K�� ��� �� ��ć ��������, � ��� ���č���, ����� SQL: 1999 ���������, ����������� �� ��������� ����� ������.
N� ��������� ������� �� ��������������� T��ć� �������� ����� � �� �����ć����� S���� � ������� ���� �������. N� ������ �� ��������� ��� ���� �������. Z�����, O����� � J����N������. N� ������� J����N������ ������� �� ������� A�������B��� �� ������ ��������� �������� ����č� IDO�����. ������ ������������� ������� �� ��������� ������ ��������������� �� ����: T����� �������������������
T�� ������� I�����
U�����
K�����
P������� NE
A�������B���
D����� ������������
��� ����� �� �����?
DA
U������ ������� J����N������ �� O�����OP���������JN, ������� A�������B��� � ������ J����N������
NE
I�����
DA
IDO�����
DA
A�������B���
DA
U�����
U ������ A�������B��� ������� ����������ć� �������� �� ����� �������� IDO����� U ������ A�������B��� ������� ����������ć� �������� �� ������ ���� ��������� �� IDO����� S���č��� �������� ������
D����� NE U ������ ������ ��ć� ��������� ���� �� ��������� ������� ���� ć� �� ����������� �����ć����� O����� SUBP��. 16
U ���č��� ���������� A��������B���� � ������ O�����OP���������JN, �����ć� �� ������ ���� ����� �� �������� �� ������ � ������ J����N������. O��� ������ ��ć� ���������� ��� AFTER UPDATE ������, � ��� �� ��č� ������������� ��������� �� ��� FOR EACH ROW ������ (P����� 2). K�� ��� ��� ��ć ���������, ������� ��������� AFTER ��������, ���� ����ć� �������������� ����� � ������ ��������� �����ć����� ��� � ��� ����č��� ��č�. D����, � ���� ���č��� ���� �� ������ ���������� A��������B���� � ������ O�����OP���������JN. T�� ���� �� ��� ������ ������� (AFTER), �����ć� �� ������ č��� �� ���� �� �� ��������� �������� A��������B����, ������� � ������ J����N������ � �� �� ����� ��� (FOR EACH ROW), ���� ��� ��� �������� �������� ����č� IDO����� ���� �������� ���������� ���� � ������ O�����OP���������JN. K��� �� ������ � ������ O�����OP���������JN ��ć ��������, SUBP ���� ���� ���������� � �������� �� ���� ���� �� ��������. D� ����� �����ć��� �� �� ��� �� ���� �������� A��������B���� ������� � ���� ������ ���������, ���� �� ������ �� �� ���������, ��������� ������, ��������� �� �� ���������� ��� ����� BEFORE ������, ���� ć� ��� ��������� ��������� �������� � ����� ����� ������ ��������� ������������ (P����� 1). P���� �� � O������� �������� �� �����ć� ��č��: CREATE OR REPLACE PACKAGE "PAKET" AS ODLUKA NUMBER:=0; SIFRA NUMBER:=0; IMEPREZIME VARCHAR2(20); IDZAP NUMBER:=0; END;
K������� ����� ć� �� ��������� �� ����������� ������� �����ć����� O����� SUBP��.
CREATE OR REPLACE TRIGGER "ODLUKAJNPRE" BEFORE INSERT OR UPDATE OR DELETE ON ODLUKAJN FOR EACH ROW BEGIN IF (INSERTING OR UPDATING) THEN BEGIN PAKET.SIFRA:=:NEW.ARHIVSKIBROJ; PAKET.ODLUKA:=:NEW.IDODLUKEOPOKRETANJU; PAKET.STARASIFRA:=:OLD.ARHIVSKIBROJ; PAKET.STARAODLUKA:=:OLD.IDODLUKEOPOKRETANJU;END; ELSE BEGIN PAKET.SIFRA:=:OLD.ARHIVSKIBROJ; PAKET.ODLUKA:=:OLD.IDODLUKEOPOKRETANJU;END; END IF; END; ������ 1 ������ � BEF��E I��E��, ��DA�E �� DE�E�E ������ CREATE OR REPLACE TRIGGER "ODLUKAJN" AFTER UPDATE OF ARHIVSKIBROJ ON ODLUKAOPOKRETANJUPOSTUPKA FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN EXECUTE IMMEDIATE 'ALTER TRIGGER NEMENJAJARHIVSKIBROJ DISABLE'; UPDATE JAVNANABAVKA
17
SET ARHIVSKIBROJ = PAKET.SIFRA WHERE IDODLUKEOPOKRETANJU = PAKET.ODLUKA; COMMIT; BEGIN EXECUTE IMMEDIATE 'ALTER TRIGGER NEMENJAJARHIVSKIBROJ ENABLE'; END; END; ������ 2 ������ � AF�E� ��DA�E
������
N� ��������� ������� �� ��������������� D���� �������� ����� � �� �����ć����� P���������� �������. N� ������ �� ��������� ��� ���� �������. Z�����, Z�������� � Č���K�������. N� ������� Č���K������� ������� �� ������� I��P������ �� ������ ��������� ����č� IDZ���������. ������ ������������� ������� �� ��������� ������ ��������������� �� ����: T����� ���������
T�� �������
K�����
P�������
I�����
U�����
NE
I��P������
D�����
�����������
DA
P������� ������ ��������� ������ I��P������ � ������ Z��������, �����ć� �� ������ ���� ��������� �������� ������� � ������ Č���K�������.
NE
I�����
U�����
��� ����� �� �����?
I��P������
D�����
DA
U ������ I��P������ ������� ����������ć� �������� �� ����� �������� IDZ���������.
DA
S����� �������� ������.
NE
18
O� ��������������� ������� ��ć� ������� ���� ���� �� ����� �������� �������� ����� č���� ��������. D��� ������ ć� ���� ��������� ��� AFTER INSERT �� �����������ć� FOR EACH ROW. P��č� �� ���� ��� � � ������� 2. Z��� ���� ����� �������, ������ �������� ��� �����, ���� ć� ��� �������� �������� � ����� č����, ������� � ����� IDZ��������� ���� �� ������ �� ������������� č����. P����ć� �� ������ ���� �� ���� ��� �������� ����� � ��������� ���������� �� ������ Z��������, ���� ���������� ��������� ����������� I�Z�� �� ������. D������� ��� � ������� �� ������ ����� � �����, � �� � �������� ����������� I��P������ ����� ���� �� ��������� �������� ����� �������� �� ����� č���� ��������.
CREATE OR REPLACE TRIGGER "CLANOVI" AFTER INSERT ON CLANKOMISIJE FOR EACH ROW DECLARE V_IMEPREZIME VARCHAR2(20); BEGIN SELECT IMEPREZIME INTO V_IMEPREZIME FROM ZAPOSLENI WHERE IDZAPOSLENOG = PAKET.IDZAP; PAKET.IMEPREZIME := V_IMEPREZIME; END; ������ 3 ������ � AF�E� I��E�� ������
N� ����� ����� �������� �� ������� ���� ć� ��������� ��� ������ �� ����������� AFTER DELETE ������� (P����� 4). N�� ���� ������� �� �������� � ���������� ���������������. T� ���č� �� �� ���� ������ ������� ��������� �� ���; ����� � ����� ć� ���� ���� ������� ������ � P���đ�č� � ����� � ����� ć� �� č����� ������� ������ � ����� P���đ�č���. CREATE OR REPLACE TRIGGER NEBRISI AFTER DELETE ON PONUDJAC BEGIN RAISE_APPLICATION_ERROR ( NUM => -20000, MSG => 'NE SMETE DA BRISETE POSTOJECE PONUDJACE'); END; ������ 4 ������ � AF�E� DE�E�E ������
U O����� SUBP�� ������� ������ �� ���������� FOR EACH STATEMENT ������, ���� �� �������� ���������� FOR EACH ROW. STATEMENT ����č�� ��č NE POSTOJI � O�������. K�� ��� ��� ��ć �����, ��� ���������� �������� �� �������� ���������� ���������������. K��� �� �� ���������� ��� ������ �� ��� ��� �������� ������ ��������ć��� ������ S��_O_P��������. U ���� 19
���č��� �� ��������� ���������� ����� ������� ����� ������� ���� �� ��������������� �� ��� �� ���������. U ��� ����� �� ������� INSTEAD OF ������� ���� �����ć����� �������� �������� � ����� ����đ�č� � �� ����� �������. M����� ��������� �� �� � ���� ���č��� �����ć���� INSTEAD OF ������� ������������ ��� �� �������� �������� ������� � ��� �����č��� ������ � P������� � P�������D������. Ponudjac (IDPonudjaca, NazivPunudjaca) PonudjacDetalji (IDPonudjaca, Delatnost, KontaktTelefon)
K�������� ������ P������� CREATE TABLE PONUDJAC ( IDPONUDJJACA NUMBER(7) PRIMARY KEY, NAZIVPONJUDJACA VARCHAR(20));
K�������� ������ P�������D������ CREATE TABLE PONUDJACDETALJI ( IDPONUDJJACA NUMBER(7) PRIMARY KEY, DELATNOST VARCHAR(20), KONTAKTTELEFON VARCHAR(20));
K�������� ������� S��_O_P�������� CREATE OR REPLACE VIEW SVE_O_PONUDJACU AS SELECT P.IDPONUDJACA, P.NAZIVPONUDJACA, D.DELATNOST, D.KONTAKTTELEFON FROM PONUDJAC P, PONUDJACDETALJI D WHERE P.IDPONUDJACA = D.IDPONUDJACA;
K�������� ������� POGLEDTRIGER ����ć� ����� ć� �� �����ć��� �������� ����� ����đ�č� ����� ������� �� ��������� �������� 5. CREATE OR REPLACE TRIGGER POGLEDTRIGER INSTEAD OF INSERT ON SVE_O_PONUDJACU REFERENCING NEW AS NOVI FOR EACH ROW BEGIN INSERT INTO PONUDJAC (IDPONUDJACA, NAZIVPONUDJACA) VALUES (:NOVI.IDPONUDJACA, : NOVI.NAZIVPONUDJACA); INSERT INTO PONUDJACDETALJI (IDPONUDJACA, DELATNOST, KONTAKTTELEFON) VALUES (:NOVI.IDPONUDJACA, : NOVI.DELATNOST, : NOVI.KONTAKTTELEFON); END; ������ 5 ������ � I���EAD �F ������
��������� ����� ����đ�č� ����� ������� SVE_O_PONUDJACU � INSERT INTO SVE_O_PONUDJACU VALUES (1,'NAZIV','DELATNOST','KONTAKT');
20
2.3.2. ������� � ��������� ��� ������ ������ U ���������� ��������� �� ��ć ���� ��� ������ �������. K�� ��� �� ��������� �� ���������, O����� �����ć��� �������������� ���� ������ �������. M�đ����, ��������� �� SQL S������� ���� �����. SQL S����� �� �������� FOR EACH ROW �������, ��ć ���� ������� ���� ��� ���� �� ���������� �� ����� ������� (��������� ����� ��������). U����� ���� ���� �� ��ć�� ������ ������, � �� ���� �� ������, ������ ��ć� �� �� ������� � ����� SUBP�� ������������. A�� �� ������� �� ����������� ������ ���� ������� �� č������� ���������, ����đ�, ������ ������ �� BEFORE ������� ������ ���� ���������� ��� FOR EACH ROW. O����� �������� �� ������č�� �� ���� ��� ������� ���� ������� � SQL S������. P������� �� �����ć� ����� DML (���. D��� ������������ ��������) �������:
•
•
•
AFTER ������� �� ���� ������ ��� ������� � ��������� ��� SQL S����� 2000. T���đ� �� ������� ��� ������� �FOR ��������, � č���� �� �����ć�� ���� ������ ������� � ������� �� �� �������� ��� ��� ������. R������ � ������ �� O����� �� ��� �� ��� ������� �� ���� ���������� �� ����� ����, ��ć ���� �� ����� �������. O����� �������������� �� ����. O�������� ���������� ���� ���� INSERT, UPDATE ��� DELETE, � ������� �� ������č��� �� ��� �� ��������. INSTEAD OF ������� ����� ���� ����� ��� � � O�������, � �� �� ��� �� ���������, ��� �� ���� ���� ���������� � ��� ��������. CLR �� ���� � ������ �� ������ ����� SUBP����, � ���� ���� ���������� ��� AFTER ��� ��� INSTEAD OF. T���đ� ���� ����� ����� DDL (���. D��� ���������� ��������) �������. U ��� ���č��� ��� ������� ������ ��������� ������������ ���������, ���������� ����� ��� ���� ������ ���� �� ���������� ������ ���������� ��������� �����ć����� .NET �����������.
S�� ������� ���� �� ��������� �����ć����� O�������, ���� ć� ���� ����������� �� ����ć SQL ��������. O�� ��� ��� ������ ���������� ���� �� ���� SUBP � ������� ����� �������������� ������ � ����� ���������. U �� ����� �� ������� ����č�� ������ �������� � �������. O�� ���������� ������ �� ���������� ��������. O�� �� ���� ��������� �� ������������ ������ �� ������ � ���������. D������� �������� �������� ���� ����ć�. I������� � ������� ������ �� ��� ����� ������� � ������� � ���� �����ć����� �����ć�: •
P��������� ��������������� ����������� ��đ� ��������
•
U��������� ��� ���������� �������� � ������ ��������
•
P������ ������� � �������� ������ � ������ �� �������� �������
•
I�������������� ������� � ������ ������ ��� � ����� �������� �������� � ����������� ����������� ������
21
T����� ������� č��� ������ ������ ���������� �������� DELETE ��� UPDATE �������. P������� ��������� ���� �� ���� �������, ����������ć� ������ �� ����� �� ������ ��� ����� �� ��������� ������ � ���������� �� � ������� ������. O�� � ����� ������ ������ �������č�� ������. I������� ������ ��������� ������ ���� �� ������ć��� INSERT ��� UPDATE ��������. P������� ��������� ����� �� ���� �������, ���� ������ �� ����������� ������ � � �������� ������ � � ������ ��� ����� �� ���� ������ ���������. R����� � �������� ������ �� ������� ������ ����� �� ����� ������. O�������� ���������� �� ���� ���������� ��� ��������� ��������, ����� ���� �� �������� �������� ����� ������. S���� ������ �� ������ � ������� ������, � ���� ������ �� ������� � �������� ������. P������ ���� �� ��������� ��������� � O�������, ���� ć� �� ����������� � �� ���� ć� �� ��������� ��č�� �������������� �����ć����� SQL S����� SUBP��. N������, ��� ��� ������� � ����� �������� ����� �������.
CREATE TRIGGER "CLANOVI" ON [DBO].[CLANKOMISIJE] AFTER INSERT AS BEGIN UPDATE [DBO].[CLANKOMISIJE] SET IMEPREZIME = ( SELECT [IMEPREZIME1] FROM [DBO].[ZAPOSLENI] WHERE [IDZAPOSLENOG] = (SELECT [IDZAPOSLENOG] FROM INSERTED) ) FROM [DBO].[CLANKOMISIJE] Y JOIN INSERTED I ON Y.IDZAPOSLENOG = I.IDZAPOSLENOG WHERE I.IMEPREZIME IS NULL END ������ 6 �� ��� ������ � AF�E� I��E�� ������ CREATE TRIGGER NEBRISI ON [DBO].[PONUDJAC] AFTER DELETE AS BEGIN RAISERROR('NE SME SE BRISATI!', 16, -1) ROLLBACK TRAN RETURN END ������ 7 �� ��� ������ � AF�E� DE�E�E ������ CREATE TRIGGER [dbo].[ODLUKAJN] ON [dbo].[ODLUKAOPOKRETANJUPOSTUPKA] AFTER UPDATE AS IF UPDATE (ARHIVSKIBROJ) BEGIN ALTER TABLE [dbo].[JAVNANABAVKA] DISABLE TRIGGER [NEMENJAJARHIVSKIBROJ] UPDATE [DBO].[JAVNANABAVKA] SET [ARHIVSKIBROJ] = (SELECT [ARHIVSKIBROJ] FROM INSERTED) WHERE IDODLUKEOPOKRETANJU = (SELECT [IDODLUKE] FROM INSERTED) ALTER TABLE [dbo].[JAVNANABAVKA] ENABLE TRIGGER [NEMENJAJARHIVSKIBROJ] END; ������ 8 �� ��� ������ � AF�E� ��DA�E ������
22
K�������� ������� POGLEDTRIGER ����ć� ����� ć� �� �����ć��� �������� ����� ����đ�č� ����� �������
CREATE TRIGGER POGLEDTRIGER ON SVE_O_PONUDJACU INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON IF (NOT EXISTS (SELECT P.IDPONUDJACA FROM PONUDJAC P, INSERTED I WHERE P.IDPONUDJACA = I.IDPONUDJACA)) INSERT INTO PONUDJAC SELECT IDPONUDJACA, NAZIVPONUDJACA FROM INSERTED IF (NOT EXISTS (SELECT E.IDPONUDJACA FROM PONUDJACDETALJI E, INSERTED WHERE E.IDPONUDJACA = INSERTED.IDPONUDJACA)) INSERT INTO PONUDJACDETALJI SELECT IDPONUDJACA, DELATNOST, KONTAKTTELEFON FROM INSERTED ELSE UPDATE PONUDJACDETALJI SET IDPONUDJACA = I.IDPONUDJACA, DELATNOST = I.DELATNOST, KONTAKTTELEFON = I.KONTAKTTELEFON FROM PONUDJACDETALJI E, INSERTED I WHERE E.IDPONUDJACA = I.IDPONUDJACA END ������ 9 �� ��� ������ � I���EAF �F ������
K�������� ����� ����đ�č� ����� ������� SVE_O_PONUDJACU � INSERT INTO [DBO].[SVE_O_PONUDJACU] VALUES (3,'PON3','DEL3','KON3'); CREATE TRIGGER NEMENJAJARHIVSKIBROJ ON [DBO].[JAVNANABAVKA] AFTER UPDATE AS BEGIN IF UPDATE (ARHIVSKIBROJ) RAISERROR('NE SME SE MENJATI!', 16, -1) END ������ 10 �� ��� ������ � ������� ����������
23
2.3.3. ������� � ���������� ������ P������SQL SUBP �����ć��� �������������� ���� ������� ���� �� ���������� � č������� ���������. M�đ����, ���� �� ����� ��� ������. Z� ������� �� O����� SUBP��, ���� �� INSTEAD OF ������� ���� ���������� � ��� ��������, � �� ���� ��� ���������. I��� ��������� �� ���� � ��� SQL S�������. J�� ����� ������ ����� � ����ć���� ����������� BEFORE � AFTER ������� ��� ��������, ��� � ��� ���č��� �� ���� ���� ��������� �� ����� �������. P���� ����������� ��������� ����������, ���������� ������� ���� �������� � TRUNCATE �������, ��� � ��� ���č��� �� ���� ���� ��������� �� ����� �������. U �������� �� ���� ������ ���� ��������� �������ć� ������ ������� ��� � �� �� �� ���� ���� ���������� ��� ������� ��� ��������. ����
����đ��
���������
���������������
INSERT/UPDATE/DELETE
������
������ � �������
TRUNCATE
�
������
INSERT/UPDATE/DELETE
T�����
T����� � �������
TRUNCATE
�
T�����
INSERT/UPDATE/DELETE
�������
�
TRUNCATE
�
�
BEFORE
AFTER
INSTEAD OF
������ 2 � I������������� ������ ������� �� ���� ������������
24
���������
1. L�������ć B., M��������ć Z., A��č�ć N., B�������ć S., �B��� ���������, B������, 2006. 2. �T���� �� DML T��������, ����://�������.���������.���/�����/�������/��178134%28�=���.105%29.���� 3. �P���������� CLR T��������, ����://�������.���������.���/�����/�������/��179562%28�=���.105%29.���� 4. �P������SQL 9.1.9 �������������.����
D�������������,
25
����://���.����������.���/����/9.1/������/����
3. ������������������ ����� D�� ������č������ ������ �������� �� ������ �� ��������� SUBP���� �� ��������� � �������� �����. R������� (������) ���� č��� �������������� ��������� ���� �������� �� ������������� (�����) ���������, �� ������������ �������������� �������� �������� ��� ������ �� ���������� ������� ��������. O����� ��������� ���������� ������ ���č���� ������č��� � ���������� ������������ ����� � ������������ ������ ��������. D� �� �� ���� ���������� ��������, ��������� ����� �� ��������� ������� � ���������� ��������� ������. T��� �������� �� ������������������� ������ � ��������� ���������� ������� ���� ������� �� ��������� �������� �������������� �������� � ��������� �����������. K����������� ����� ������������������� ���� �������� ���������� �� �� ����� ��đ������ ��������� ��������, ��� ������ �������� �������� �� ������ ���� ���� ������� ������������� ������ ��������, ��ć � �������č�� ���������� ������� � ������� ������ �������� ���� ���� ���� ������������ � �������������� ����������� ������. B���� �������������� ������������������� ������� �� ����ć���� ����������� ������. N���� �� �������� ��������� ��� �������č��� ��������. N��� �� ���� �� ���������� �������� (������ �� ��������� ������������� ����� ����, ������ �� ������� � ������). O����� �������� ��� ��������, ������� ��� ������� �� �����. N������� �������� �������������� ������������������� ������� ��������������� �� SQL:1999 ����������, ����� ������������� �������č�� ����������� ������ � ������������� ������ ��������.
3.1.
�������� ���������� ������
K������� ���������� ������ �� ������ ���� �������� �������� � ���� �� ������� �� ���� ��� ��� � ������������� (�����) ������. U����� �������� ����������� ������ ���������������� �� ������ � ���������� ����������, ��� ���������� ������ ���� �� ������� ���� ����������. K������� ���������� ��� ���� ���� �������� ��� ��� ������������ ���.
3.1.1. ������������ ��� S����������� ��� �����ć��� ����������� �������������, ���������� ������, ���� ���� ����� ����� ��� ���� ��������. A������� ���� ���� ���� ��� ����, �����č���ć� ����� ������������ ������, ������ ���, ���� �� ���� ������������� ��������� ����������� ����������. P���� ��������� ���������� ����������, ������������ ������ ����� � ���������, ���������� ��������, ���� č��� �������� ��� ������������� ������������� ����. S����������� ������ ���������� �����đ������, ��� ���č� �� �� ��� ����������� ����� ���� ���� ��������� ��� �������� � ������ ����� �������ć�� ����. S����������� ��� �� �������� �����ć�� ���������: 26
CREATE TYPE
[UNDER ] AS ( , ...) [[NOT] INSTANTIABLE] NOT FINAL [] [, ...]
S���� ������ �������� ����������� ���� A�����, ���� ������ �������� ����� �����, ���� ������ � ������� �����, ��������� � O�������: CREATE OR REPLACE TYPE obj_adresa_br AS OBJECT ( ulica varchar2(50), broj number, dodatakbroju VARCHAR2(10), MEMBER FUNCTION get_ulica RETURN varchar2, MEMBER FUNCTION get_broj RETURN number, MEMBER FUNCTION get_dodatakbroju RETURN varchar2 ) INSTANTIABLE NOT FINAL; CREATE OR REPLACE TYPE BODY obj_adresa_br AS MEMBER FUNCTION get_ulica RETURN varchar2 IS BEGIN RETURN SELF.ulica; END; MEMBER FUNCTION get_broj RETURN number IS BEGIN RETURN SELF.broj; END; MEMBER FUNCTION get_dodatakbroju RETURN varchar2 IS BEGIN RETURN SELF.dodatakbroju; END; END;
N������ �� ��������� ������ ������, ���� ć� �������� ������ �� ����������� ����� ��: CREATE TABLE adresa ( sifra_adr number NOT NULL, adresa obj_adresa_br, CONSTRAINT adresa_pk PRIMARY KEY (sifra_adr));
N������ �� ���� �������� � ������ ������, ���� ������ �������č�� ���������� ��� ��: INSERT INTO adresa VALUES (1,obj_adresa_br('Jove Ilica',154,’A’));
N������ �� ������ �������� �� ������ ������, ���� ������ �������� ���������� ��� ��: SELECT a.sifra_adr "Sifra adrese",
27
a.adresa.get_ulica() "Ime ulice", a.adresa.get_broj() "Broj", a.adresa.dodatakbroju.get_dodatakbroju()"Dodatak broju" FROM adresa a;
N������ �� ��������� �������� ��������� ���� � M�������� ��������� ���� �� � ��������: using using using using using using
System; System.Data; System.Data.SqlClient; System.Data.SqlTypes; Microsoft.SqlServer.Server; System.IO;
[Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 800)] public struct Adresa : INullable, IBinarySerialize { public bool IsNull { get { // Put your code here return m_Null; } } public static Adresa Null { get { Adresa h = new Adresa(); h.m_Null = true; return h; } } private string nazivUlice; private int broj; private string dodatakBroju; private bool m_Null; public Adresa(string nazivUlice, int broj, string dodatakBroju) { this.nazivUlice = nazivUlice; this.broj = broj; this.dodatakBroju = dodatakBroju; m_Null = false; } public override string ToString() { if (this.IsNull) return "null"; else { string delim = new string((new char[] { ';' })); return(this.nazivUlice + delim + this.broj + delim this.dodatakBroju); } }
28
+
public static Adresa Parse(SqlString s) { if (s.IsNull) return Null; else { Adresa addr = new Adresa(); string str = Convert.ToString(s); string[] a = null; a = str.Split(new char[] { ';' }); addr.nazivUlice = a[0] == null ? string.Empty a[0]; int broj = Convert.ToInt32(a[1]); ValidateBroj(broj); addr.broj = broj; if (a.Length == 3) { addr.dodatakBroju = a[2]; } else { addr.dodatakBroju = string.Empty; } addr.m_Null = false; return (addr); } }
:
private static void ValidateBroj(int broj) { if (broj < 0) { throw new ArgumentOutOfRangeException("Broj ne može biti manji od 0"); } } public string NazivUlice { get { return (this.nazivUlice); } set { this.nazivUlice = value; this.m_Null = false; } } public int Broj { get { return (this.broj); } set { this.broj = value;
29
this.m_Null = false; } } public string DodatakBroju { get { return (this.dodatakBroju); } set { if (!string.IsNullOrEmpty(value)) { this.dodatakBroju = value; this.m_Null = false; } } } public override bool Equals(object other) { return this.CompareTo(other) == 0; } public override int GetHashCode() { if (this.IsNull) return 0; return this.ToString().GetHashCode(); } public int CompareTo(object other) { if (other == null) return 1; //by definition Adresa addr = (Adresa)other; if (addr.Equals(null)) throw new ArgumentException("the argument to compare is not a adresa"); if (this.IsNull) { if (addr.IsNull) return 0; return -1; } if (addr.IsNull) return 1; return this.ToString().CompareTo(addr.ToString()); } public void Write(System.IO.BinaryWriter w) {
30
byte header = (byte)(this.IsNull ? 1 : 0); w.Write(header); if (header == 1) { return; } w.Write(this.NazivUlice); ValidateBroj(this.Broj); w.Write(this.Broj); w.Write(this.DodatakBroju); } public void Read(System.IO.BinaryReader r) { byte header = r.ReadByte(); if (header == 1) { this.m_Null = true; return; } this.m_Null = false; this.nazivUlice = r.ReadString(); int broj = r.ReadInt32(); ValidateBroj(broj); this.broj = broj; this.dodatakBroju = r.ReadString(); } }
3.1.2. �������� ��� D������� ��� �� �����������, ������������, ��������� �������č�� ���������� ���, č���� ���đ����� �� �������� ������ �����������. Z� �������� ��� �� ���� ��ć� �� �� ������������ ������������� SQL ��� �� �����č���� ���������� � ������ �� �������. D������� ��� �� �������� �����ć�� ���������: CREATE TYPE AS FINAL []
D������� ������ �� ���� ����č�� (FINAL), ��� ���č� �� �� ���� ����� ���������, ������� �� ���� ���� �������� �����đ������. D������� ��� � ������ ������� ������������� ��� ���� �������� ���������. O����� �����đ���� �����ć��� ���������� �������� �� �������� ���� � ������������� � �������. M��� �� ��������� CAST ������� �� �����đ���� �� �������� ���� � ������� ������������� ��� � �������. N� ��� ��č�� �� ������đ��� ���������� (����������) ���������� ���� �� �������� ���������� ��� �������� �������� ������� � ��������. D��������� �� � ��������� ������������ ����� ������, �������� � ���������, č���� ��������� ������� �� ���� ���������� ������� ��������������. I��� �� SQL:1999 ��������� �������� ����������� �������č�� ���������� ���, � PL/SQL��, ���� ������� O�����, ���� ����������� ������� ���� ���. 31
S����������� � �������� ��� �� SQL:1999 ��������� �� � O������� �������������� ����� O����� ����. N����, � O�������, O����� ��� �� ���� ������ ��������� �������� �������� ���� �� SQL:1999 ���������, ��� O����� ��� �� ��� ��� ���� �������� �������� ������������� ���� �� SQL:1999 ���������. D�� �� ������ ��������� ���� �����, ���� ��� ������� �������� ���� ������(9,2). CREATE OR REPLACE TYPE "dinar" AS OBJECT (vrednost number(9,2), MEMBER FUNCTION get_vrednost RETURN number) INSTANTIABLE NOT FINAL; / CREATE OR REPLACE TYPE BODY "DINAR" AS MEMBER FUNCTION get_vrednost RETURN number IS BEGIN RETURN SELF.vrednost; END; END;
K������� ��� �� �������� � ������ S�����_C��������, ��� ��� ������ ����. R�������� ����� S�����_C������� �� ��� � ��������. STAVKA_CENOVNIKA(SifraCenovnika, RedniBroj, Cena, SifraProizvoda)
N������ �� ��������� S�����_C������� ��: CREATE TABLE stavka_cenovnika( SifraCenovnika number, RedniBroj number, Cena dinar, SifraProizvoda number, CONSTRAINT scfk1 FOREIGN KEY (ŠifraCenovnika) REFERENCES cenovnik(ŠifraCenovnika), CONSTRAINT scfk2 FOREIGN KEY (ŠifraProizvoda) REFERENCES proizvod(ŠifraProizvoda), CONSTRAINT scpk PRIMARY KEY (ŠifraCenovnika, RedniBroj));
N������ �� ���� �������� � ������ �� �������č� ����������� ����� (������_���������): INSERT INTO stavka_cenovnika VALUES(1,1,dinar(1000),1)
N������ �� ������ �������� ������ �� �������č� ����������� ����� (������_���������): SELECT sc.sifracenovnika, sc.rednibroj, sc.cena.get_vrednost(), sc.sifraproizvoda FROM stavka_cenovnika sc WHERE sifraproizvoda=1;
N������ �� ��������� �������� ��������� ���� � M�������� ��������� ���� �� � ��������: CREATE TYPE [dbo].[dinar] FROM [decimal](9, 2);
32
3.2.
������������ ������
P� SQL:1999 ���������, ������������ ������ �� ���������� ������ (���������), ������ ����� � ���������. P���� ��������� ������ �� ����� ć� ���� ������� �������� � ������ ����� ���. I��� ����ć� �������đ�č� ���������� �������� ������ (����� O�������), ��� ������ �� ���� ���� � ���� ������� ������� � ��ć� ���� ����������� � ���������� ����.
3.2.1. ���������� ��� T����� ���������� �������� ��� ������������� �������� ���� ����� ���������� ��� ���, ���� ����� ��� ������������� �������. T���� ������ ���� ���� �������� ����č ��� ������ �� ������������ ����������� ���� ���������� �������� ������ �� ����������� ����� ��������. S������� REF ���� �� ���� ���� �����ć� ������: ATRIBUT REF(TIP) SCOPE RELACIJA
U������ �� TIP �� ������� ���� ������������ ���, ���� �� REF(TIP) ���������� ���, ������� ��� ��������� �� ������� ���� TIP. R������������� ���� ���� ����đ�� ����� (SCOPE), ���� �� ����������� ����đ����� ������ �������� č��� �� ������� ������������� (� ������� �� �� RELACIJA).
3.2.2. ��� ����� O��� ��� �� �������� ��� ��� (<����� �������>, <��� �������>). O� ��������� SQL:1999 ������� �� ����ć���� ����������� ������������ � ���������� ���� �� ���� �����, ������� ����������� ������ � ������ ���� ć� ����� ���������� ���������. P����� ��������� ��� ���� ��� �� � ��������. create table kolekcija_boja (boja raw(16));
P����� �� ���� ��������� � ������ ���� ������ ��� ���: insert into kolekcija_boja values ('FF0000');
P������������ ��������� �� ������ �� ��� ����� ��������: select * from kolekcija_boja where utl_raw.substr(boja, 1, 2) = 'FF';
33
3.2.3. ������� A���� ����������� ���������� ��� �� �������� �� ������. S�� �������� ������ ������� �� ����� ����. S���� ������� ��� ���� ������, ���� ������ ����� �������� � ����. B��� �������� � ���� ��������� �� ������� ������� � ���������� ������ �� ���� �������� �������� ��������� ��������. O����� ���������� �������� ������� ����������� ������, �� �� ������� �������� �� �������� ������. V����� ��������� �� ���� �������. S������� �� ��������� �������: CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type
K�������� ������ ��������� ��������� ������ �� ��� � ��������: CREATE TYPE phones AS VARRAY(10) OF varchar2(10);
S����� �������� �������� ��� �� ������� � ������: reate table suppliers (supcode number(5), Company varchar2(20), ph phones);
N������ �� ���� �������� � ������ �� ������� �����: insert into suppliers values (101,’Interface Computers’, Phones(‘64199705’,’55136663’)); insert into suppliers values (102,’Western Engg. Corp’, Phones(‘23203945’,’23203749’,’9396577727’));
N������ �� ������ �������� ������ �� ������� �����: Select * from suppliers;
3.2.4. ������ ����� N����� ����� �� ��� ���� �������� ������������� ��������, ��. �������� ������ ���� ���� �������� ��������� ����� ������. O�� ��� ��������� �������� ���� �������� ����� � ��ć� ���� �����ć�� � ���������� ����. P����� �� ��������� ���� �������č��� ����(������ D��������� ������� C�����L���):
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64); SELECT type, text FROM user_source WHERE name = 'COURSELIST'; CREATE TABLE department ( name VARCHAR2(20),
34
director VARCHAR2(20), office VARCHAR2(20), courses CourseList) NESTED TABLE courses STORE AS courses_tab;
N������ �� ������ �������� ������ �� ������ ����� �����:
SELECT column_name, data_type, data_length FROM user_tab_cols WHERE table_name = 'DEPARTMENT'; SELECT table_name, table_type_owner, table_type_name, parent_table_column FROM user_nested_tables;
N������ �� ���� �������� � ������ �� ������ ����� �����:
SELECT cardinality(courses) FROM department; INSERT INTO department (name, director, office, courses) VALUES ('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList( 'Expository Writing', 'Film and Literature', 'Modern Science Fiction', 'Discursive Writing', 'Modern English Grammar', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel')); SELECT * FROM department; SELECT cardinality(courses) FROM department;
35
��������� U �������� �� �������� ��������� �����ć��� �� ��������� � ������������������ ������. N����ć� ��� ��������� �� ������� �� ��������� [1], ���� ����������� � ����� ��������� ��������� (�������� � ������������������� ������, �������č�� ����������� � ������������� ��������). O����� ��������� �� �����ć��� ��� ���� �� ��������� �������č��� ������� [7 � 9] � �� ���������� ���������� ������ ���������� ������������������� ������ [2 � 6].
1. L�������ć B, M��������ć Z, A��č�ć N, B�������ć S �B��� ��������� FON, 2006. 2. C������ T., B��� C., �D������� ���������: A ������������ ����� �� �������� ���������� , P������ E�������� L������, 2004. 3. B����� S., �� ��., �D������� D�����: K��� �� ���� , M����� K�������, 2009. 4. P����� G., �B�������� D������� D������ , W���� P���������, 2006. 5. H����� J., P������� M., M�F����� F., ������� D������� ����������� , P������ E��������, 2007. 6. H������� S., �D��� ��������� ���������� , J��� W���� & S���, 2002. 7. F��������� S, P����� B ������� ��/��� ������������ O�R�����, 2009. 8. R��������� B, R������� E ������� ��/��� �� E������� P������� H���, 2009. 9. W�������� M, M������ B, �I����� ���������� D�������� ���� E������� �� A������ , S�������, 2007.
36
4. ������������ O����������� ���� �������� �������� �����č��� ������� ������ �� ���� ���������� ����������� ���� ���� ��������. U ���� ���� ��ć� �������: ������� ������������ ��������� �� �������� ��������� ���������, ������� � ���������� ���������������.
4.1.
������� ������������ ��������� �� ��������� �����������
O��� ć� ���� ������� ������� ������� ������������ ��������� �� �������� ��������� ���������. O������ ����� ������������ ��������� ��������� ��������� ���č�� �� ����� ���������������. I � ���č��� ������������ ��������� ��������� ��������� ������ �� �� �������������� ������������� ������ � ���� �� ����� ���������� ��������, �� ������ ����������� ����������� ���� ��������. I���, ����� ������� ����đ� ������������ ��������� ��������� ��������� � ��������������� �� � ���� ��� �� � ���č��� ������������ �� ���������� �������� �����, ��ć �� ���������� �������� ����� ���� �� �� ������� ������� �� č����� ����č��������� ��������� ���������. P�� ��������� �������ć� ����������� �� �������� ���� �� ����đ��� ��� ����č����� �� ������ ����� ��� ���� ������ ��������� (���. ������ �������� ��č��� �� �������� ��������, �������� ����� ������ ���� ������ ��č���). U �������� ć� ���� ������� �����č��� ������� ������������ ��������� �� ��������� �����������.
4.1.1. ������� ��������� ������ ������� ������������ U������ �� � ������� ��� ����� �������� č���� ������ ������� �� ����đ���� ����č��������� ���������, ������� �� ���� č����� �� �������� ��������, �������� ��������� ����č��������. K��� �� �� �����ć��� č������ �������� ���������, ����� �� ����������� ������ ����������ć� ������ � ���� �� ������ ����č����� �������� ��������. P������������ �� �� ��� �����ć� ������������ �����2:
Profaktura(BrDok, DatumIzdavanja, Napomena, DatumIsporuke,
RokVazenja, Avans, PIB, BrNar ) StavkaProfakture(BrDok, RedBr, Kolicina, Napomena, SifraArt) Artikal(SifraArt, Dimenzija, OznakaKvl, AktuelnaCena, SifraStd ,
SifraKvl)
2
Zbog preglednosti, na PMOV – u neće biti prikazivani svi atributi entiteta niti sve njihove veze.
37
M��� �� �č������� �� ć� ��� ���� �� ����������� ���� �������� ����č�������� ����� ������ ���������, ���� �� ������ ����� ������ ������ ���������� (���� �� ������ ��������� ����č��� ������� � ������� �������� ����; ������� Kolicina �������� StavkaProfakture � ������� AktuelnaCena �������� Artikal). K��� �� �� ������� ����č�������� ������ ��������� ���������� ��� ������ �����, � �������� Profaktura ������ �� ������� Ukupno, � ����� ć� �� č����� ������ �������� ����������. P�������� ������������ ����� ���� �� ��������:
Profaktura(BrDok, DatumIzdavanja, Napomena, DatumIsporuke, RokVazenja, Ukupno, Avans, PIB, BrNar ) StavkaProfakture(BrDok, RedBr, Kolicina, Napomena, SifraArt) Artikal(SifraArt, Dimenzija, OznakaKvl, AktuelnaCena, SifraStd ,
SifraKvl)
Oč������� �� �� �� �� ���� ��č�� �������� ����č�������� ������ ��������� ���������� ��� ������ �����, č��� �� ���������� ����� �������� �����������. N������, ���đ���� �������� ������ ������������ ���� ������� �� ����������������� ��������, �� �� ��������� ������� ������������ ���� ���� ć� ������ ����č������� �������� �������� ��� ������ DML ��������� ���� ���� ������� �� ����������� ���������������. T��� ��, �� ������, ��������� ������ ����č����� ������ �������� ��� ������ ��������� ���� ������ ����������. N� ���č�� ��č�� ��� � � ���� � ���������������, ��������� �� ������������� ������� ���� ć� �� ��������� ��� ������ DML ��������� �� ������� �� �������������� �������� � ������ ����č������� ������ �������� ���������� 3.
��������
��� ���� ������ ������� ������������
E�� D��� C����� ������� ������������ ����������� ������� ��� ������� D�������� ������
�������. O�� ������� ������������ ������� �� ���� � ���� �������� ������� ������ č��� ������ ������������� ��������� ��������� �������. P������������ �� �� ��� �����ć� ������������ �����:
3
U okviru projektnog rada, zahteva sa da se aplikacioni kôd koji vrši izračunavanje izvedene vrednosti izdvoji u uskladištenu proceduru, kao i da se specificira tabela trigera koji će pozivati posmatranu uskladištenu proceduru kada je to potrebno. Detaljnije o ovome u delu o uskladištenim procedurama.
38
(ProizvodID, Naziv, SkraceniNaziv, JedMereID, KategorijaID ) Proizvod CenaProizvoda(ProizvodID,DatumOd,Cena)
U������ ����� ������ �� �������� �������� ���� ���������, ����������ć� ���� �� �������� �������� �������� ����� ����� �������� ���������� �������� D����O� ����� �� ��������� ������, ��� ��� �� ��������� �����. ...WHERE proizvodid = ... AND
datumod = (SELECT max(datumod) FROM cenaproizvoda WHERE datumod <= sysdate AND proizvodid = ...)
N� ���� ��� �� �� ���������� � ���� ����� ����� ������ �� ������ ���� ��������� ���� �� ������ ������ ������ (������ ������� �������� �� �� ������� �����). K��� �� �� ������� �������� ��������, ���� �� �������� ������������ ���������� �������� DatumDo � �������� CenaProizvoda, ��� ��� �� ��������� �� ������ ������������� ������:
Proizvod (ProizvodID, Naziv, SkraceniNaziv, JedMereID, KategorijaID ) CenaProizvoda(ProizvodID,DatumOd,Cena, DatumDo)
N� ���� ���, ������ �������� ��������, �� ������������ �������� ���� ��������� ���� �� ��������� ������� ��������, ��� ��� �� ��������� �����. ...WHERE prozvodid = ... AND sysdate between datumod and nvl(datumdo, sysdate)...
I ���� �� ��������� �������������� ����������� ��� ����� ��, �� ����đ��� DML �������, ��������� ����������ć� �������� �� DatumDo. N� ������, ��� ��������� ���� ���� ��������� ��������� �� ���������� ����, �� �������� ������ DatumDo, ��������� �������� ������ DatumOd ���� ����. 39
��������
������� ��������� ������ ������� ������������
C������ I�������� C����� ������� ������������ ����������� ������� ��� ������� D�������� ������ ������� ������������. S��� ������� ������� �� � ����� ����������� ��� � ��������� ������� E�� D��� C����� �������. R������ �� � ���� ��� �� ������ �������� ���� ����������� ������� �����,
��� C������ I�������� C����� ������� ������������ ������� ������� ���� ��� ����� ���������� �� �� �� ���������� ���� ����������� � �������� �������� ���� ���������. U������ �� �������� ����� ������������ ����� ������� � ���������� ���� � E�� D��� C����� �������, C������ I�������� C����� ������� ������������ �� �� ����� ��������� ��� ��� �� ��������� �� ������ ������������� ������:
Proizvod (ProizvodID, Naziv, SkraceniNaziv, JedMereID, KategorijaID ) CenaProizvoda(ProizvodID,DatumOd,Cena, TrenutnaCena)
K�� ��� �� ���� ������, �������� CenaProizvoda ����� �� ������� TrenutnaCena ����� �� �� ����� ���� �������� �� �� �� �������� �������� ��� ��. U ���� ���č���, ������ ������ ��������� ��������, ������� �� ���� ����� �������� ��� ������� ���������, ��� ��� �� ���� � ��������. ...WHERE proizvodid = ... AND trenutnacena = ‘da’
N������, � ���� �� ��������� ����������� ��� ����� �� �� �������������� �������� ��� ������ DML ����������� (���. ��� ��������� ���� ���� �������� �� ��������� ��������� ������ TrenutnaCena � ����������� ���� � ���� ���� �� ��������� ���� ��������).
4.1.2. ��������� ������ ������ ���� ������ ������� ������������ P�� č������ ���������� ������� ����đ���� ������� ���� �����, č��� �� ���č�� �� ć� � ������� ���� �������� ���� �������� ��������. P���������� �����ć� ������������ ����� � ��� �� �� ������� č����� ��� ���� ���� �� ���� ���� �����:
40
Artikal(SifraArt, Dimenzija, OznakaKvl, SifraStd , SifraKvl) Cena (SifraArt,DatumOd,Iznos)
M��� �� ������������� �� ć� � ������� ���č��ć� ���� �������� �������� ���� ���������. U ��� ��������� �������� ������� ������������, ���� ������� ������� �� ��������� �������� ������ ������� ���� (E�� D��� C����� �������) � ��������� ���������� �������� ���� �� ����� ���� (C������ I�������� C����� �������). U ���č��� ��������� ������ D����� ���� ������ ������� ������������, ���������� �� ����� � ������ ������. D����č��� ��č���, � ���� �������, ����������� ������� ������ �� � �������� Artikal, ��� ��� �� ��������� �� �����ć�� ������������� ������.
Artikal(SifraArt, Dimenzija, OznakaKvl, AktuelnaCena, SifraStd ,
SifraKvl) Cena (SifraArt,DatumOd,Iznos)
R������� Artikal ������ �� ������� AktuelnaCena ���� ć� � ���� �������� �������� �������� ���� ���������. K�� � � ���������� ���č�������, ��������� �� ������������ ����� č����� �������������� �������� (�� ������, ��� ������ ��������� ���� ���� �������, ��������� �� ��������� �������� �������� ���� �� ������� ���� �� ���������� ��������� ���� ����).
41
4.1.3. ������� ������� ���� ������ ������� ������������ U ���č��� ���� �� ����� ��� ������ ������ ������� ������ ���� ������ ������ ������, ����ć� �� ��������� K������ D������ ���� ������ ������� ������������ ���� ����������� �������������� ������ ������ ������ � ������ ������. P������������ �� �� ��� �����ć� ������������ �����:
Student(StudentID, BrIndeksa, Ime, Prezime, DatumRo đenja ,
SifraSmera) ProsecnaOcena(StudentID,GodinaStudija,Vrednost)
K�� ��� �� ���� ������, �� ������ �������� ���� �� ���������� �����č��� ����� ���� �� ���� � ������ ������ �������. Oč������� �� �� ����� ������� ���� ����� ���������� č����� �����č�� �����. U���� ����, ����ć� �� � �������� ������� ������ č����� ��������, �� ����� �� ����� ������ �������, ��� ��� �� ��������� �� �����ć�� ������������� ������.
Student(StudentID, BrIndeksa, Ime, Prezime, DatumRo đenja, ProsekPrva, ProsekDruga, ProsekTreca, ProsekCetvrta, SifraSmera)
D����, ������ ������ (� ���� ������� S������) ������ �� ���� ������ ���� ������������ ����ć� ������ ������ ������ (� ���� ������� P�������O����), ��� č��� ������ ������ �������, ������� �� ���� ���� ������� �� ����.
4.1.4. ���� � ����� ������ ������� ������������ U ���č��� ���� � ���� �������� ������� ������, ������������� �� ������ ���� ����� ������, ��� č��� ������������� ������ � ���� ������ ��������� ���������� ���� ������, ����ć� �� ��������� H��� � C���� ������ ������� ������������. O�� ������� ����������� �� �� ��������� ������������� ������ ������ ��������� ������������ ����, ��� č��� ������������� ������ �������, ��� �� � �������������ć�
42
������ ������ ������� ���� ��������� ������������� ������. P������������ �� �� ��� �����ć� ������������ �����:
Student(StudentID, BrIndeksa, Ime, Prezime, DatumRo đenja, SmerID,
StatusID) Status(StatusID, Naziv)
Ako se uzme u obzir da relacija Status ima samo dve n – torke, obzirom da student može biti u statusu samofinansiraju ćeg ili budžetskog studenta, može se izvršiti Hard – Coded Values optimizacija, pri čemu se dobija slede ći konceptualni model:
Student(StudentID, BrIndeksa, Ime, Prezime, DatumRo đenja, Status,
SmerID)
K�� ��� �� ���� ������ �������� Status �� �������, ��� č��� �� �������� Student ����� ���� ������� Status ���� ć� �� ��������� �� č������ ������� � ��� �� ������� �������� ������. S��� ��������� ���� �� �������� �������� Status (���������������ć� � ���������), ���� �� ������ � ������������ ����. N� ������, ���� �� ���������� ��� CHECK ������č���� ��� ������� ��������� Status �������� Student.
4.2.
�������
I������ ������������� ��������� �������� ���� �����ć����� �������� ������� �������� ���� �� ������ � ���� ��������. D�� ������� ������� �������� ������� ��: •
•
P���������� ����������� � ������� ������� ��� �� ���� ����������� ��������. O��������� �������������� � ����������� ������ �� ��������� ���� �� �������� ��� ��������� �� �� ��������� � �������� ���� �� ����������� �� ��� �������. U ������ �� ���, ���� SUBP � � ���������� ������ ������� �� ��� �������� ������.
43
P���� ��������� ����č��� ���� �� ���č��ć� ���������� �����������, ������ ��������� �� ��������� ������� ��� ����� ���� ��: •
K����� ���� �� � ������� ��� ������� � JOIN ��������
•
K����� ���� ������ ����� ����� ���������
•
K����� ���� �� ��� ������� � WHERE ��������
•
K����� ���� �� ��� ������� � ORDER BY ��������
K����� ���� ���� ����� ��������� �� ������������ ��: •
K����� ���� �� ����� ������� � WHERE ��� ORDER BY ��������
•
K����� ���� ��������� �������� ���� ������
•
K����� ���� ������ ������ ���� ���� ���������, � ����� ���� �� ����� ������ ���� ������ ������ ���� ���������
P������������ �� �� ���� �����ć� ��������: Racun (SifraRacuna, Datum_izdavanja_racuna, Mesto_izdavanja,
Sifra_radnika, Sifra_valute, Sifra_metoda_isporuke)
M��� �� ������������� �� ć� �� � ������� ��� ���������� �������� č���� ��������� ������� Datum_izdavanja_racuna. Z��� ���� �� ��� ������� Datum_izdavanja_racuna ������ Racun �������� �������� ������ ���� �� �� ������� ����������� ����� ��� ��� �������. Opšta sintaksa za kreranje indeksa je: create [unique] index on ( [,, …])
N������� �������� �� ��������� ������� ���� �� ��������� � �����č���� SUBP, ��� č��� ����� �� SUBP �������� � ������� �������� �� ����������� �����č���� ����� ������� ��� ������� �� �����č���� ����������������, ��� ���� ��ć� ���� ����������.
U ������ �� ��������� ��������� ������ ���������, ��������� ������� ��� ������� Datum_izdavanja_racuna �������� �� �� ��������: create index racun_ind on racun (datum_izdavanja_racuna);
44
4.3.
���������� ���������������
P�������������� ������ ����������� ������ ����� ������ � ��� ��� ���� ������ ������ ���� �� �� �������� ����č��� �������� ���� SUBP ���� �� ������ ��� ����������� �����. V��������� ��������������� ������ ����������� ������ ������ ���������� ������ � ��� ������, ��� č��� �� � ����� ������ ��������� ������ ���� �� č��ć� ������� � �������, ��� �� ��������� ������, ������ ���� �� ��đ� �������, �������� � ����� ������. P����� ������ ���� �� �������� � � ���� �� ��� ������, ��� č��� �� ���� ������ ������� �� ������� ������� �������� � �������. U ������ �� ������ ��������� ����������� ���������������� ���� �� ��ć� �������� ����č ���������� ������, ������� �� ć� �� ������������� ������ ����đ� ������ ��������������� ������. P������������ �� �� ���� �����ć� ��������: Kupac(ŠifraKupca,
NazivKupca, RačunKupca, FaksKupca, MatičniBroj, PIB, Adresa, IDMesta)
TelefonKupca,
M��� �� ������������� �� �� � �������, ����� ��������� ����č� ŠifraKupca, č���� ������� �������� NazivKupca � Adresa, �� �� ��� ���� ��������� � ����� ������, ��� �� ������ �������� ��������� � ����� ������. T��� �� �������� �����ć� ��� ��������: Kupac(ŠifraKupca, NazivKupca, Adresa) Kupac_Detalji(ŠifraKupca, RačunKupca, TelefonKupca, FaksKupca,
MatičniBroj, PIB, IDMesta)
Z� ������ �������� ����������� ���������������� ����� �� �������� ������ ����� �� ������ �� ��� ������ �����������, �����č�� ��������ć� ��������� ��č���� ������. T���đ�, ��������� ������� �� �������� ��������� �� �������� �������� ���������� ��������������� � ������� �� ��������� ��������� ������ ���� �� �� ������ ��č���� ������. P����� ����� �� ������� ������ �� ������ Kupac � Kupac_Detalji � ������ ������ ���������� ������ Kupac ��� �� � ��������: CREATE OR REPLACE VIEW KUPAC_VIEW AS SELECT K.SifraKupca, K.NazivKupca, K.Adresa, KD.RacunKupca, KD.TelefonKupca, KD.FaksKupca, KD.MaticniBroj, KD.PIB, KD.IDMesta FROM KUPAC K, KUPAC_DETALJI KD WHERE K.SifraKupca = KD.SifraKupca;
N�� ����� ����������� �������� ����� �� ���� ���������� INSTEAD OF ������ ����� �� ��������� ���� ��� �������� �������� � ��������� ������ � ������ �������� ����������� ����������������, ��� č��� �� � ����� �� ������ ������ ��������� ���� ���������� �������� ���� �� ������ � ��� �������� (� ���������� �������, ��������� ���� � ������ Kupac_view �������� �� � ��������� ������ � ������ Kupac � Kupac_Detalji , ��� č��� �� � ������ Kupac ��������� ��������� ������ ŠifraKupca, Nazivkupca � Adresa, ��� �� ������ ��������� ��������� � ������ Kupac_Detalji).
45
4.4.
������������ ���������
M������������� �� ������ ����� �� ������ ������� ���� ��������� �� ����� ������ (������). ��� ć��� ������ ���� ��������� ����� ������. P�� ���������� �� ����������� ����� ���� ���� �������� ����� ��� ���� ����������. P���� ��������� �� ��������� ������� ������� � SQL�� � ���� ���� SQL ���� ���� �������� ���� ����� ���������. P�������� �� ������ ������ ���������� ����, ���� �����ć����� ������������� � ������� �������� ���������� ������. P�������� ������������ � ���� �������� �� ������������ (������) ���������. P�������� �� �������� �����ć�� ���������: CREATE [OR REPLACE] PROCEDURE [schema.] naziv_procedure [(parametar [, parametar ...])] AS [deklaracija_lokalnih_promenljivih] BEGIN izvršne_naredbe [EXCEPTION excption_handlers] END [naziv_procedure];
P�������� �� ���� ����� ���������, � ���� �� ����� ��������č��� ����� (������ � IN, ������� � OUT ��� ������/������� IN/OUT). S���� ��������� �� ������� �� �����ć�� ������: ���������, ���� ������ ��� ����������� ��č� AS (������ AS ����ć� �� ��������� � ����������� ��č IS �� ����� �������); ����č�� ��č� PROCEDURE ��� ���� ����� ����� ��������� � (�������) ������ ���������� � ���� ���������, ���� č��� ��� ��č� ����� ����������� ��č� AS. R�č REPLACE �� ������� � �����ć��� �� �� �������ć� ��������� �� ����đ���� ������� ������, ��. �� �� ������� ��� �������ć� ���������. P������������ ���� �� ���� ��������� ���������. U������ �� ������ ����� ����� ����, �� ���� ć� �������� �������� ���������. D� �� �������� ��� � ����ć����� �� ������ ���������, �������� �� �� ��� ����������� ��������� ��������� � ��������� ����. N���� ����������� ��č� AS ����� ��� �� ������������ �������� ������������. U������ �� ��������� �� �������, ��� ����������� ��č� AS ����� ����������� ��č BEGIN. N���� ����č�� ��č� BEGIN ����� �������� ��� ���������: ������� �������, ���� ć� ���� �������� ����� ��� ���� �� ��������� �������. M��� ��������� ����� ����� �������. I�� ������� ������� ������� ����� ����������� ��č EXCEPTION ����� ���� �� �������� ��č�� ����������� ���������. N� ����� ���� ��������� ����� ����������� ��č END ��� ���� ��, �������, ������ ����� ���������.
4.4.1. ������ ������������ ���������: �������� ���� U ���� ����, ��ć� ��������� � �������� ������� ��� ������� ������������� ���������. P���������, ��� ������, ���� ������ �� R�������� S����� D����� ���� M����� ������� ������������. U ���č��� ��������� ������ D����� ���� ������ ������� ������������, ���������� �� ����� � ������ ������. U ���� �������, ����������� ������� ������ �� � �������� Artikal, ��� ��� �� ��������� �� �����ć�� ������������� ������.
46
Artikal(SifraArt, Dimenzija, OznakaKvl, AktuelnaCena, SifraStd ,
SifraKvl) Cena (SifraArt,DatumOd,Iznos)
R������� Artikal ������ �� ������� AktuelnaCena ���� ć� � ���� �������� �������� �������� ���� ���������. N�������� �� ������������ ����� č����� �������������� �������� (�� ������, ��� ������ ��������� ���� ���� �������, ��������� �� ��������� �������� �������� ���� �� ������� ���� �� ���������� ��������� ���� ����). T����� �� ������������� ������� ���� ć� �������� ������������ ��������� �����:
T�����
T�� �������
K�����
P�������
I����� ��������
U�����
NE A�������C���
DA
D�����
U�����
Z������ ��������� ���������� ����� A�������C��� � ������ P�������
NE
I�����
C������������
��� ����� �� �����?
C���
D�����
47
DA
P������� ����� ���� C���P��������, ����� �� ������ ���� ������ ��������� �� ���������� ����� A�������C��� �� P��������
DA
P������� ���������� C���P��������, ����� �� ������ ���� ������ ��������� �� ���������� ����� A�������C��� �� P��������
DA
P������� �������� C���P��������, ����� �� ������ ���� ������ ��������� �� ���������� ����� A�������C��� �� P��������
P��������� ������� �� ������������� �������� ������� �� �������� ������ ������������ ���������. B�ć� �������� �������� ������� ���� ć� �� ���������� ���� �� ������ ������� ���������� ������� � ������ P�������. I������������� ���� ������� �����ć��� �� �� ����� ������� ���������� ����� A�������C��� � ����� ���������. V������� ��� ������ ��, ����� ���, ����ć� ������� ������č��� ������� ��������� �� ������������ ��������� A�������C���. P���� ����, �������� �� �������� ������� ���� ć� �� ������� ���� �� ���� ���� ����� ������� ������ C���P��������, ���� �������� �������ć�� ������� ������ C���P��������, ��� ������� �������ć� ������. U ������������� ����, �������������� ������� ���� ���� �������, � ���� ���� �������������� ���� ������� ���� ���� ����������� � ��� ������ �������. U �������� ����� ��� ������� �����ć���� �� ����� ��������� �� ����đ������ � ������������ �������� ����. P��� ������ ����đ��� ����� ��������� � ��������� �� � �����������: CREATE OR REPLACE TRIGGER "AKTUELNAC" BEFORE INSERT OR UPDATE OR DELETE ON cenaProizvoda FOR EACH ROW BEGIN IF (INSERTING OR UPDATING) THEN BEGIN cenap.sifra := :NEW.sifraproizvoda; END; ELSE BEGIN cenap.sifra := :OLD.sifraproizvoda; END; END IF; END;
T����� �� ��������� ���������: CREATE OR REPLACE TRIGGER "AKTUELNAC2" AFTER INSERT OR UPDATE OR DELETE ON cenaProizvoda DECLARE s NUMBER:=cenaP.sifra; BEGIN aktuelnacena(s); END;
U ���� ������� �� ��� ��� ������� ���� �� ����� � �� ���� � �� ���������� � �� �������� ��� ������� ����P��������. U ����� ������� �� ����� ���������, �� ���� �� ���� ����, ���������� ��� ��������, ��������� � �������� �����������, ��� ��������� ����� �������� �����, ���������� ��� ��������. N���� ��������� ���� �������, ����� �� ����� ������ ���� ������ ��������� ������������(�) � ������� ��� � ��� ��������� ����� ���������. U �������� ����� ��� ��������� �� �������� �������� ����:
CREATE OR REPLACE PROCEDURE "AKTUELNACENA" (SifraPro IN NUMBER) AS aktCena PROIZVOD.AKTUELNACENA%type; BEGIN aktCena:=0; SELECT cena INTO aktCena FROM CenaProizvoda
48
WHERE sifraProizvoda=SifraPro and datumOd=(select max(DATUMOD) cenaProizvoda where sifraProizvoda=SifraPro and datumOd<=sysdate);
from
UPDATE Proizvod SET AktuelnaCena = aktCena WHERE SifraProizvoda=SifraPro; END;
P�������� ��������� ����� ��� ������ (IN) ��������� ����� ��������� (���� NUMBER) �� ���� �� ��č��� �������� ����. V������� �������� ���� �� ��������� � ������� ����������� ���C���. U ���� ��������� ������� �� SQL �������� MAX(DATUMOD) ���� ����đ��� ����������� �����. Z� ��� ����� �� ����� �������� ���� � ��������� � �����������. U ������ �� ��������� �� �� �� ����� ��������� ���� ��� � � ������� ��������� � �� �� �� ����� �� ���� ���� ���� ����� �� ���������. O��� �� �� ������� � ����� ����ć� ����, ��. ���� č��� ����� ��č���� ������� ��� ���� ��������. N� ����� ����� ��������� ���� �� ���������� ������ P�������, ���������� ���� ������ �� ��������� �����. U �������� �� ��������� ��������� ���� ���������������, �������� � M�������� S QL S������ (T/SQL): CREATE PROCEDURE [dbo].[sp_AktuelnaCena] @SifraPro int = 0 AS BEGIN SET NOCOUNT ON; declare @AktuelnaCena int; SELECT @AktuelnaCena = Iznos FROM Cena WHERE SifraPro=@SifraPro and DatumOd=(select max(DATUMOD) from cena where SifraPro=@SifraPro and datumod<=getdate()); UPDATE Arikal SET AktuelnaCena = @AktuelnaCena WHERE SifraPro=@SifraPro; END GO
4.4.2. ������ ������������ ���������: ������ ����� ���������� P���������, ��� ������, ���� ������ �� S������ D�������� V����� ������� ������������. M��� �� �č������� �� ć� ��� ���� �� ����������� ���� �������� ����č�������� ����� ������ ���������, ���� �� ������ ���������� ������ ������ ���������� (��������� ����č��� ������� � ������� �������� ����; Kolicina �� �� �������� StavkaProfakture , � AktuelnaCena �� �������� Artikal ). K��� �� �� ������� ����č�������� ������ ��������� ���������� ��� ������ �����, � �������� Profaktura ������ �� ������� Ukupno, � ����� ć� �� č����� ������ �������� ����������. P�č���� ������������ ����� ����� ������������ �������:
49
Profaktura(BrDok, DatumIzdavanja, Napomena, DatumIsporuke, RokVazenja, Ukupno, Avans, PIB, BrNar ) StavkaProfakture(BrDok, RedBr, Kolicina, Napomena, SifraArt) Artikal(SifraArt, Dimenzija, OznakaKvl, AktuelnaCena, SifraStd ,
SifraKvl)
Oč������� �� �� �� �� ���� ��č�� �������� ����č�������� ������ ��������� ���������� ��� ������ �����, č��� �� ���������� ����� �������� �����������. N������, ���đ���� �������� ������ ������������ ���� ������� �� ����������������� ��������, �� �� ��������� ������� ������������ ���� ���� ć� ������ ����č������� �������� �������� ��� ������ DML ��������� ���� ���� ������� �� ����������� ���������������. T��� ��, �� ������, ��������� ������ ����č����� ������ �������� ��� ������ ��������� ���� ������ ����������. N� ���č�� ��č�� ��� � � ���� � ���������������, ��������� �� ������������� ������� ���� ć� �� ��������� ��� ������ DML ��������� �� ������� �� �������������� �������� � ������ ����č������� ������ �������� ����������. T����� �� ������������� ������� ���� ć� �������� ������������ ��������� �����: T�����
T�� �������
K�����
P�������
I����� ����������
U�����
NE U�����
DA
D�����
Z������ ��������� ����� U�����
����������
NE
I�����
DA
P������� ����� ���� S�����P���������, ����� �� ������ ���� ������ ��������� �� ���������� ����� U����� �� P���������
DA
P������� ���������� ���� S�����P���������, ����� �� ������ ���� ������ ��������� �� ���������� ����� U����� �� P���������
DA
P�������
������������������
U�����
��� ����� �� �����?
K�������
D����� 50
��������
S�����P���������, ����� �� ������ ���� ������ ��������� �� ���������� ����� U����� �� P���������
P��������� ������� �� ������������� �������� ������� �� �������� ������ ������������ ���������. B�ć� �������� �������� ������� ���� ć� �� ���������� ���� �� ������ ������� ���������� ������� � ������ P���������. I������������� ���� ������� �����ć��� �� �� ����� ������� ���������� ����� U����� � ����� P���������. V������� ��� ������ ��, ����� ���, ����ć� ������� ������č��� ������� ��������� �� ������������ ��������� U����� �� P���������. P���� ���������, �������� �� �������� ������� ���� ć� �� ������� ���� �� ���� ���� ����� ������� ������ S�����P���������, ���� �������� �������ć�� ������� ������ S�����P���������, ��� ������� �������ć� ������. U ������������� ����, �������������� ������ ���� ���� �������, � ���� ���� �������������� ���� ������� ���� ���� ����������� � ��� ������ �������. U �������� ����� ��� ������� �����ć���� �� ����� ��������� �� ����đ������ � ������������ ������� ������ ����������. P��� ������ ����đ��� ����� ���������� � ��������� �� � �����������:
CREATE OR REPLACE TRIGGER "SUMASP" BEFORE INSERT OR UPDATE OR DELETE ON stavkaProfakture FOR EACH ROW BEGIN IF (INSERTING OR UPDATING) then BEGIN stavkap.brdok := :NEW. brdok; END; ELSE BEGIN stavkap.brdok:= :OLD. brdok; END; END IF; END;
T����� �� ��������� ���������: CREATE OR REPLACE TRIGGER "SUMASP2" AFTER INSERT OR UPDATE OR DELETE ON stavkaProfakture DECLARE dok NUMBER:=stavkap.brdok; BEGIN sumaStavkiProfakture(dok); END;
U ���� ������� �� ��� ��� ������� ���� �� ����� �� ����, �� ���������� � �� �������� ��� ������� ������P���������. U ����� ������� �� B�D�� (����� ����������), �� ���� �� ���� ����, ���������� ��� ��������, ��������� � �������� ����������� �����, ��� ��������� ����� ������� �����, ���������� ��� ��������. N���� ��������� ���� �������, ����� �� ����� ������ ���� ������ ��������� 51
����S�����P���������(���) � ������� ��� ��� ��� ��������� ����� ����������. U �������� ����� ��� ��������� �� �������� ������� ������ ����������: CREATE OR REPLACE PROCEDURE "SUMASTAVKIPROFAKTURE" (sifDok IN NUMBER) AS suma NUMBER:=0; BEGIN SELECT SUM(p.aktuelnacena*sp.kolicina) INTO suma FROM proizvod p join stavkaprofakture sp on (p.sifraproizvoda=sp.sifraproizvoda) WHERE brdok=SifDok; UPDATE Profaktura SET Ukupno=suma WHERE brdok=SifDok; END;
P�������� ��������� ����� ��� ������ (IN) ��������� ����� ���������� (���� NUMBER) �� ���� �� ��č��� ������ ����� ��������� �� ��������. S��� ���������� ��������� ����č��� ������ ������P��������� � ��������C��� ������ P������� ������ �� � ����������� ����. Z����, ���� �� ���������� ������ P���������, ��. ����� ������ U����� �� �������ć� �� ����������� ����. U ������ �� ��������� �� �� �� ����� ��������� ���� ��� � � ������� ���������. U �������� �� ��������� ��������� ���� ���������������, �������� � M�������� SQL S������ (T/SQL):
CREATE PROCEDURE [dbo].[sp_SumaStavkiProfakture] @BrDok int = 0 AS BEGIN SET NOCOUNT ON; declare @ukupno numeric(18,0); SELECT @ukupno = SUM(p.aktuelnacena*sp.kolicina) FROM Arikal p inner join stavkaprofakture sp on p.sifrapro=sp.sifrapro WHERE brdok=@BrDok; UPDATE Profaktura SET Ukupno = @ukupno WHERE Profaktura.BrDok = @BrDok END
52