ER dijagrami i SQL Zadaci 1 9 Dio

E-R dijagrami i SQL Zadaci 1

9. Dio baze podataka nabavke poslovnog IS trgovinskog preduzeća dat je sljedećim relacijama: dokumenti ( DOK_ID, DATUM_DOK, DOK_IZRADIO) dobavljaci (SIF_DOB, NAZIV_DOB, ADRESA) zagl_fakture (DOK_ID, SIF_DOB, UK_VRIJ) roba (SIF_ROBE, NAZIV_ROBE) stavke_fakture (DOK_ID, SIF_DOB, SIF_ROBE, KOLICINA, VRIJ_STAVKE) • • • Nacrtati odgovarajući E-R dijagram. Dopuniti E-R dijagram tako da sadrži podatke o cjenovnicima dobavljača. Predstaviti relacije tabelama i svaku popuniti sa po najmanje 2 zapisa. 2

DATUM_DOK DOK_ID SIF_DOB NAZIV_DOB UK_VRIJ dokumenti n zagl_fakt 1 dobavljaci n ADRESA DOK_IZRADIO KOLICINA stavke_fakt VRIJ_STAVKE m roba SIF_ROBE NAZIV_ROBE 3

DATUM_DOK DOK_ID SIF_DOB NAZIV_DOB UK_VRIJ dokumenti n zagl_fakt 1 dobavljaci m n ADRESA DOK_IZRADIO KOLICINA stavke_fakt VRIJ_STAVKE m roba SIF_ROBE n cjenovnik NAZIV_ROBE CIJENA 4

dokumenti DOK_ID DATUM_DOK DOK_IZRADIO 100 12. 10. 2010 Jovanović Marko 101 12. 10. 2010 Lakić Dušica zagl_fakt DOK_ID SIF_DOB UK_VRIJ 100 333 150 101 444 370, 30 dobavljaci SIF_DOB NAZIV_DOB ADRESA 333 Mex PG 444 Albatros Bar 555 SLCom Bijelo Polje 5

zagl_fakt DOK_ID SIF_DOB UK_VRIJ 100 333 150 101 444 370, 30 stavke_fakt DOK_ID SIF_DOB SIF_ROBE KOLICINA VRIJ_STAVKE 100 333 11 -2 35 40, 3 100 333 11 -3 40 52, 05 101 444 11 -4 15 7, 5 roba SIF_ROBE NAZIV_ROBE 11 -2 Ulje 11 -3 Šećer 11 -4 So 6

dobavljaci SIF_DOB NAZIV_DOB ADRESA 333 Mex PG 444 Albatros Bar 555 SLCom Bijelo Polje cjenovnik SIF_DOB SIF_ROBE CIJENA 333 11 -2 1, 7 444 11 -4 0, 5 roba SIF_ROBE NAZIV_ROBE 11 -2 Ulje 11 -3 Šećer 11 -4 So 7

10. Putem SQL-a odgovoriti na sljedeće upite a) Naći spisak dobavljača od kojih je roba Ulje nabavljana u periodu od 1. 1. 2010 do 1. 3. 2010. b) Naći ukupnu vrijednost nabavljene robe od dobavljača Albatros za prvu polovinu tekuće godine. c) Naći sve fakture dobavljača SLCom po kojima je dobavljana roba Šećer u mjesecu januaru tekuće godine. d) Fakturu sa brojem 1112 treba stornirati (poništiti). Ažurirati bazu podataka. e) Naći spisak roba sa cijenama koje se nabavljaju od dobavljača SLCom. 8

a) SELECT SIF_DOB, NAZIV_DOB FROM dokumenti X, dobavljaci D, stavke_fakt S, roba R WHERE X. DOK_ID = S. DOK_ID AND D. SIF_DOB = S. SIF_DOB AND R. SIF_ROBE = S. SIF_ROBE AND R. NAZIV_ROBE = “Ulje” AND X. DATUM_DOK BETWEEN “ 1. 1. 2010” AND “ 1. 3. 2010”; 9

b) SELECT SUM(UK_VRIJ) FROM dokumenti X, zagl_fakt Z, dobavljaci D WHERE X. DOK_ID = Z. DOK_ID AND D. SIF_DOB = Z. SIF_DOB AND D. NAZIV_DOB = “Albatros” AND X. DATUM_DOK BETWEEN “ 1. 1. 2010” AND “ 30. 06. 2010”; 10

c) SELECT DOK_ID, UK_VRIJ, NAZIV_DOB FROM dokumenti X, zagl_fakt Z, dobavljaci D, stavke_fakt S, roba R WHERE X. DOK_ID = Z. DOK_ID AND D. SIF_DOB = Z. SIF_DOB AND Z. DOK_ID = S. DOK_ID AND R. SIF_ROBE = S. SIF_ROBE AND R. NAZIV_ROBE = “Šećer” AND D. NAZIV_DOB = “SLCom” AND X. DATUM_DOK BETWEEN “ 1. 1. 2010” AND “ 31. 1. 2010”; 11

d) DELETE FROM stavke_fakt WHERE DOK_ID = “ 1112”; DELETE FROM zagl_fakt WHERE DOK_ID = “ 1112”; DELETE FROM dokumenti WHERE DOK_ID = “ 1112”; 12

e) SELECT SIF_ROBE, NAZIV_ROBE, CIJENA FROM roba R, cjenovnik C, dobavljaci D WHERE R. SIF_ROBE = C. SIF_ROBE AND D. SIF_DOB = C. SIF_DOB AND D. NAZIV_DOB = “SLCom”; 13

11. Dio baze podataka proizvodnje u jednom poslovnom informacionom sistemu sadrži sledeće relacije : dokumenti ( JED_BR_DOK, VRS_DOK, DATUM_DOK) radna_mj (SIF_RM, NAZIV_RM, BR_RADNIKA) trebovanje ( JED_BR_DOK, SIF_RM, SKLADISTE) materijali ( SIFRA_MAT, NAZIV_MAT, KOLICINA, JED_MJERE, JED_CIJ_KOST) stavke_treb ( JED_BR_DOK, SIF_RM , SIFRA_MAT, KOLIČINA) • • • Nacrtati odgovarajući E-R dijagram za ovaj dio baze podataka. Dopuniti E-R dijagram tako da sadrži informaciju o tome koje proizvodi određeno radno mjesto, kao i o standardnim utrošcima pojedinih materijala za proizvodnju pojedinih proizvoda. Predstaviti relacijama dopunjeni dio E-R dijagrama. 14

DATUM_DOK JED_BR_DOK SIF_RM NAZIV_RM SKLADISTE dokumenti n trebovanje 1 radna_mjesta n BR_RADNIKA VRS_DOK KOLICINA stavke_treb m materijali SIFRA_MAT JED_MJERE JED_CIJ_KOST NAZIV_MAT KOLICINA 15

DATUM_DOK JED_BR_DOK SIF_RM proizvodnja NAZIV_RM SKLADISTE dokumenti n 1 trebovanje radna_mjesta 1 SIF_PR n BR_RADNIKA VRS_DOK KOLICINA n proizvodi stavke_treb JED_MJERE m JED_CIJ_KOST materijali SIFRA_MAT KOLICINA m NAZ_PR n NAZIV_MAT ST_KOL standardi 16

proizvodi (SIF_PR, NAZ_PR, SIF_RM) standardi (SIF_PR, SIFRA_MAT, ST_KOL) 17

12. Putem SQL-a izraziti sledeće upite bazi iz prethodnog zadatka: a) b) c) d) e) f) Naći sva trebovanja materijala Brasno Tip A u periodu od 10. 03. 2010 do 10. 04. 2010. Naći spisak materijala i njihovu kolicinu koji su trebovani iz skladišta S 1 za radno mjesto Proizvodnja hleba dana 20. 04. 2010. Naći ukupnu količinu materijala So koja je iz skladišta S 1 trebovana za radno mjesto Proizvodnja hleba u martu 2010. Naći spisak materijala sa standardnim utrošcima za proizvodnju proizvoda Bio Hleb. Trebovanje po dokumentu broj 23 -001 treba stornirati (poništiti). Ažurirati bazu podataka. Naći ukupan broj dokumenata kojim su trebovani materijali za proizvodnju u toku 2010 godine. 18

a) SELECT JED_BR_DOK, NAZIV_RM, SKLADISTE FROM dokumenti D, trebovanje T, radna_mjesta R stavke_treb S, materijali M WHERE D. JED_BR_DOK = T. JED_BR_DOK AND T. SIF_RM = R. SIF_RM AND T. JED_BR_DOK = S. JED_BR_DOK AND S. SIFRA_MAT = M. SIFRA_MAT AND M. NAZIV_MAT = “Brašno tip A” AND D. DATUM_DOK BETWEEN “ 10. 03. 2010” AND “ 10. 04. 2010”; 19

b) SELECT M. SIFRA_MAT, M. NAZIV_MAT, S. KOLICINA FROM dokumenti D, trebovanje T, radna_mjesta R stavke_treb S, materijali M WHERE D. JED_BR_DOK = T. JED_BR_DOK AND T. SIF_RM = R. SIF_RM AND T. JED_BR_DOK = S. JED_BR_DOK AND S. SIFRA_MAT = M. SIFRA_MAT AND T. SKLADISTE = “S 1” AND R. NAZIV_RM = “Proizvodnja hleba” AND D. DATUM_DOK = “ 20. 04. 2010”; 20

c) SELECT SUM ( S. KOLICINA ) FROM dokumenti D, trebovanje T, radna_mjesta R stavke_treb S, materijali M WHERE D. JED_BR_DOK = T. JED_BR_DOK AND T. SIF_RM = R. SIF_RM AND T. JED_BR_DOK = S. JED_BR_DOK AND S. SIFRA_MAT = M. SIFRA_MAT AND M. NAZIV_MAT = “So” AND T. SKLADISTE = “S 1” AND R. NAZIV_RM = “Proizvodnja hleba” AND D. DATUM_DOK BETWEEN “ 01. 03. 2010” AND “ 31. 03. 2010”; 21

d) SELECT SIFRA_MAT, NAZIV_MAT, ST_KOL FROM materijali M, standardi S, proizvodi P WHERE M. SIFRA_MAT = S. SIFRA_MAT AND P. SIF_PR = S. SIF_PR AND P. NAZ_PR = “Bio hleb”; 22

e) DELETE FROM stavke_treb WHERE JED_BR_DOK = “ 23 -001”; DELETE FROM trebovanje WHERE JED_BR_DOK = “ 23 -001”; DELETE FROM dokumenti WHERE JED_BR_DOK = “ 23 -001”; 23

f) SELECT COUNT (JED_BR_DOK ) FROM dokumenti D, trebovanje T WHERE D. JED_BR_DOK = T. JED_BR_DOK AND D. DATUM_DOK BETWEEN “ 01. 2010” AND “ 31. 12. 2010”; 24