Evo mog resenja. Poduze je ali samo tako mogu u potpunosti da se izrazim.Source govori sam za sebe :)
Code:
CREATE TABLE Alati (
id_alata INTEGER NOT NULL,
naziv_alata VARCHAR(50) NOT NULL,
CONSTRAINT pk_ala PRIMARY KEY (id_alata)
);
CREATE TABLE Materijali (
id_materijala INTEGER NOT NULL,
naziv_materijala VARCHAR(50) NOT NULL,
CONSTRAINT pk_mat PRIMARY KEY (id_materijala)
);
CREATE TABLE Radna_Mesta (
id_radnog_mesta INTEGER NOT NULL,
opis_radnog_mesta VARCHAR(50) NOT NULL,
CONSTRAINT pk_ram PRIMARY KEY (id_radnog_mesta)
);
CREATE TABLE Radnici (
id_radnika INTEGER NOT NULL,
id_radnog_mesta INTEGER NOT NULL,
ime_radnika VARCHAR(15) NOT NULL,
prezime_radnika VARCHAR(30) NOT NULL,
CONSTRAINT pk_rad PRIMARY KEY (id_radnika),
CONSTRAINT un_rad UNIQUE (id_radnika, id_radnog_mesta),
CONSTRAINT fk_rad_ram FOREIGN KEY (id_radnog_mesta)
REFERENCES Radna_Mesta (id_radnog_mesta)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE Zadaci (
id_zadatka INTEGER NOT NULL,
id_radnog_mesta INTEGER NOT NULL,
naziv_zadatka VARCHAR(50) NOT NULL,
trajanje_zadatka INTEGER NOT NULL CHECK (trajanje_zadatka > 0), -- u danima??
CONSTRAINT pk_zad PRIMARY KEY (id_zadatka),
CONSTRAINT un_zad UNIQUE (id_zadatka, id_radnog_mesta),
CONSTRAINT fk_zad_ram FOREIGN KEY (id_radnog_mesta)
REFERENCES Radna_Mesta (id_radnog_mesta)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE Poslovi (
id_radnika INTEGER NOT NULL,
id_zadatka INTEGER NOT NULL,
id_radnog_mesta INTEGER NOT NULL,
datum_dodele DATE NOT NULL,
datum_pocetka DATE,
datum_zavrsetka DATE,
CONSTRAINT pk_pos PRIMARY KEY (id_radnika,id_zadatka),
CONSTRAINT fk_pos_rad FOREIGN KEY (id_radnika, id_radnog_mesta)
REFERENCES Radnici (id_radnika, id_radnog_mesta)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_pos_zad FOREIGN KEY (id_zadatka, id_radnog_mesta)
REFERENCES Zadaci (id_zadatka, id_radnog_mesta)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ch_pos_datum_pocetka CHECK ( (datum_dodele <= datum_pocetka)
OR (datum_pocetka IS NULL)),
CONSTRAINT ch_pos_datum_zavrsetka CHECK ( (datum_pocetka <= datum_zavrsetka)
OR (datum_zavrsetka IS NULL))
);
CREATE TABLE Potrebni_Alati (
id_zadatka INTEGER NOT NULL,
id_alata INTEGER NOT NULL,
normativna_kolicina NUMERIC(16,3) NOT NULL CHECK (normativna_kolicina > 0),
CONSTRAINT pk_poa PRIMARY KEY (id_zadatka, id_alata),
CONSTRAINT fk_poa_zad FOREIGN KEY (id_zadatka)
REFERENCES Zadaci (id_zadatka)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_poa_ala FOREIGN KEY (id_alata)
REFERENCES Alati (id_alata)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE Potrebni_Materijali (
id_zadatka INTEGER NOT NULL,
id_materijala INTEGER NOT NULL,
normativna_kolicina NUMERIC(16,3) NOT NULL CHECK (normativna_kolicina > 0),
CONSTRAINT pk_pom PRIMARY KEY (id_zadatka, id_materijala),
CONSTRAINT fk_pom_zad FOREIGN KEY (id_zadatka)
REFERENCES Zadaci (id_zadatka)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_pom_mat FOREIGN KEY (id_materijala)
REFERENCES Materijali (id_materijala)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE Izdati_Alati (
id_radnika INTEGER NOT NULL,
id_zadatka INTEGER NOT NULL,
id_alata INTEGER NOT NULL,
datum_izdavanja DATE NOT NULL,
izdata_kolicina NUMERIC(16,3) NOT NULL CHECK (izdata_kolicina > 0),
datum_vracanja DATE,
CONSTRAINT pk_iza PRIMARY KEY (id_radnika, id_zadatka, id_alata),
CONSTRAINT fk_iza_pos FOREIGN KEY (id_radnika, id_zadatka)
REFERENCES Poslovi (id_radnika, id_zadatka)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_iza_poa FOREIGN KEY (id_zadatka, id_alata)
REFERENCES Potrebni_Alati (id_zadatka, id_alata)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ch_iza_datum_izdavanja CHECK( (datum_izdavanja <= datum_vracanja)
OR (datum_vracanja IS NULL))
);
CREATE TABLE Izdati_Materijali(
id_radnika INTEGER NOT NULL,
id_zadatka INTEGER NOT NULL,
id_materijala INTEGER NOT NULL,
datum_izdavanja DATE NOT NULL,
izdata_kolicina NUMERIC(16,3) NOT NULL CHECK (izdata_kolicina > 0),
vracena_kolicina NUMERIC(16,3) NOT NULL CHECK (vracena_kolicina >= 0),
datum_vracanja DATE,
CONSTRAINT pk_izm PRIMARY KEY (id_radnika, id_zadatka, id_materijala),
CONSTRAINT fk_izm_pos FOREIGN KEY (id_radnika, id_zadatka)
REFERENCES Poslovi (id_radnika, id_zadatka)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_izm_pom FOREIGN KEY (id_zadatka, id_materijala)
REFERENCES Potrebni_Materijali (id_zadatka, id_materijala)
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ch_izm_datum_izdavanja CHECK( (datum_izdavanja <= datum_vracanja)
OR (datum_vracanja IS NULL))
);
Napomena: SQL skript je proveren na PostgreSQL DBMS-u. PostgreSQL ima DATE tip podataka, dok koliko znam MS SQL nema - Find/Replace All sa DATETIME.
Pojasnjenje tabela:
-
Tabele 'Alati', 'Materijali', 'Radna_Mesta' su trivijalne.
- Tabela
'Radni_Zadaci' referencira ka tabeli 'Radna_Mesta'. Ovoj tabeli je dodat UNIQUE index (kasnije ce biti objasnjeno zasto).
- Tabela
'Radnici' referencira ka tabeli 'Radna_Mesta' u smislu da je radnik rasporedjen (kvalifikovan) na odredjeno radno mesto. Ovoj tabeli je dodat UNIQUE index (kasnije ce biti objasnjeno zasto).
- Tabela
'Zadaci' takodje referencira ka tabeli 'Radna_Mesta' u smislu da se zadatak izvrsava na tom radnom mestu. Ovoj tabeli je dodat UNIQUE index (kasnije ce biti objasnjeno zasto).
- Tabela
'Poslovi' referencira ka tabelama 'Radnici' i 'Zadaci' preko gore spominjanih UNIQUE indexa. Ovim je obezbedjeno da se radnik ne moze poslati da radi zadatak za koji nije kvalifikovan i koji se ne radi na njegovom radnom mestu. Tabeli je takodje dodata prosta provera ispravnosti datuma.
- Tabela
'Potrebni_Alati' ima kompozitni prirodni kljuc od dva atributa. Na ovaj nacin je spreceno da se za isti zadatak navede potreba za dva ista alata, i obratno.
- Tabela
'Potrebni_Materijali' takodje ima kompozitni prirodni kljuc od dva atributa. Slicno predhodnoj tabeli, na ovaj nacin je spreceno da se za isti zadatak navede potreba za dva ista materijala, i obratno.
- Tabela
'Izdati_Alati' referencira ka tabeli 'Radnik' kome je taj alat izdat. Takodje tabela referencira ka tabeli 'Potrebni_Alati'! Na ovaj nacin je spreceno da se radniku izda alat koji mu nije potreban za obavljanje konkretnog zadatka. Citiracu Brokerov originalni zadatak: 'podatke o alatima koji su radniku izdati za obavljanje
konkretnog posla'. Tabela opet ima kompozitni kljuc koji je ovog puta sastavljen od tri atributa. Tabeli sam takodje dodao trivijalnu proveru ispravnosti datuma vracanja alata.
- Tabela
'Izdati_Materijali' referencira ka tabeli 'Radnik' kome je materijal izdat. Takodje tabela referencira ka tabeli 'Potrebni_Materijali'! Na ovaj nacin je spreceno da se radniku izda materijal koji mu nije potreban za obavljanje konkretnog zadatka. Opet citiram Brokerov originalni zadatak: 'podatke o materijalima koji su radniku izdati za obavljanje
konkretnog posla'. Tabela opet ima kompozitni kljuc koji je takodje sastavljen od tri atributa. Tabeli sam takodje dodao trivijalnu proveru ispravnosti datuma vracanja materijala i vracene kolicine.
Svoje resenje sam prezentovao u SQL-u jer je to univerzalni jezik baza podataka zasnovanih na relacionoj teoriji. Lako vam je da uradite copy/paste uz blage izmene i mozete proveriti moje tvrdnje na RDBMS-ima koje upotrebljavate.
Resenja (ili je bolje reci resenje?) koja su ponudili Amladja i Broker prvo pate od nedostatka referencijalnih integriteta (u daljem tekstu RI).
Ako ne modelirate i ne upotrebljavate RI onda slobodno umesto Relacionih sistema za upravljanje podacima (RDBMS) mozete koristiti FAJL SISTEME!
Navedena resenja su data u obliku kvazi matematicke notacije relacionog modela podataka.
U toj notaciji se RI pisu upotrebom projekcije i relacije 'je podskup od'. Na primer
Code:
RADNA_MESTA (ID_RADNOG_MESTA, Naziv)
RADNIK (ID_RADNIKA, Prezime, Ime, DatumRodjenja, ID_RADNOG_MESTA)
RADNIK[ID_RADNOG_MESTA] je podskup od RADNA_MESTA[ID_RADNOG_MESTA]
Ponudjenim resenjima takodje nedostaje provera unetih podataka koje sam naveo u resenju koje sam ja prikazao. U tim resenjima je sasvim legitimno dati posao radniku na nekom zadatku, a da radnik nije ni kvalifikovan za taj zadatak. Sasvim je legitimno radniku izdati alat koji mu nije potreban.
Price da se to resava u aplikativnom delu ne drze vodu, jer kako cete spreciti cak i ispod prosecnog korisnika SQL DML-a da uradi
Code:
INSERT INTO POSLOVI (ID_POSLA, ID_RADNIKA, ID_ZADATKA, Dodeljen, Zapocet, Zavrsen)
VALUES (1, 1, 1, '2006-07-15', '2006-07-07', '2006-01-01');
COMMIT;
ako u tabeli RADNIK imate da je radnik sa RADNIK.ID_RADNIKA = 1 zaposlen na radnom mestu RADNIK.ID_RADNOG_MESTA = 2? (Obratite paznju da je posao uradjen pre nego sto je i dodeljen!)
Ovakvo nesto se moze spreciti samo fizickim metodama represalija nad zaposlenima u firmi. Zabrana instaliranja generickih SQL programa cak i administratorima jer boze moj on je mozda drug nekome ko zna SQL.
I na kraju zbog jake simetricnosti modela (slicnost izmedju 'Alati' i 'Materijali' i njihove upotrebe) bi se dalo razmisljati i o generalizaciji ove dve tabele u tabelu 'Resursi', pa onda samo jedna tabela 'Potrebni_resursi' i jos jedna tabela za 'Izdati_Resursi'. Ali ova ideja je data nakon povrsnog razmatranja modeliranog sistema i sigurno bi trebalo dublje analizirati sistem da bi ova ideja bila primenjena.
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo
Re: Teorija Vs. Praksa
Re: Teorija Vs. Praksa
- da bi ga Seka lakše sprovela.
Re: Teorija Vs. Praksa
Re: Teorija Vs. Praksa