SVEUČILIŠTE/ UNIVERZITET VITEZ U TRAVNIKU FAKULTET POSLOVNE INFORMATIKE U TRAVNIKU TRAVNIK
GORAN MILIĆ
BAZA PODATAKA – HONORARI AUTORA MAGAZINA INFO SEMINARSKI RAD
TRAVNIK, 2012.
SVEUČILIŠTE/ UNIVERZITET VITEZ U TRAVNIKU FAKULTET POSLOVNE INFORMATIKE U TRAVNIKU TRAVNIK
BAZA PODATAKA – HONORARI AUTORA MAGAZINA INFO SEMINARSKI RAD
IZJAVA: Ja, Goran Milić, student Sveučilišta/Univerziteta „VITEZ“ Travnik, Index broj: FIP 0020/10 DIT, odgovorno i uz moralnu i akademsku odgovornost izjavljujem da sam ovaj rad izradio potpuno samostalno uz korištenje citirane literature i pomoć asistenta odnosno profesora. Potpis:
Predmet:
BAZE PODATAKA
Mentor:
Prof. dr Zoran Avramović
Asistent:
Mr Mahir Zaimović
Student:
Goran Milić
Broj indeksa:
FPI 0020/10DIT
Smjer:
Informacijske tehnologije TRAVNIK, APRIL 2012.
SADRŽAJ
1 UVOD .................................................................................................................... 1 1.1
Predmet, objekt i cilj projektnog zadatka ....................................................... 1
1.2
Korišteni alati i tehnologije............................................................................. 1
1.3
Struktura rada .................................................................................................. 1
2 NAMJENA I OPIS BAZE PODATAKA .............................................................. 2 3 SQL KOD ZA KREIRANJE TABELA BAZE PODATAKA .............................. 3 3.1
Tabela Autori .................................................................................................. 3
3.2
Tabela Magazin............................................................................................... 3
3.3
Tabela Tekstovi............................................................................................... 3
3.4
Tabela Opstine ................................................................................................ 4
3.5
Tabela Banke .................................................................................................. 4
3.6
Tabela Rubrike ................................................................................................ 4
4 ER DIJAGRAMI BAZE PODATAKA ................................................................. 5 5 TIPOVI PODATAKA KORIŠTENI U TABELAMA .......................................... 6 5.1
Tabela Autori .................................................................................................. 6
5.2
Tabela Magazin............................................................................................... 6
5.3
Tabela Tekstovi............................................................................................... 7
5.4
Tabela Opstine ................................................................................................ 7
5.5
Tabela Banke .................................................................................................. 7
5.6
Tabela Rubrike ................................................................................................ 7
6 SQL KOD ZA POPUNJAVANJE TABELA PODACIMA .................................. 8 6.1
Tabela Autori .................................................................................................. 8
6.2
Tabela Magazin............................................................................................... 8
6.3
Tabela Tekstovi............................................................................................... 9
6.4
Tabela Opstine .............................................................................................. 10
6.5
Tabela Banke ................................................................................................ 10
6.6
Tabela Rubrike .............................................................................................. 10
7 SQL UPITI NAD POPUNJENIM TABELAMA ................................................ 11 7.1
Upit #1 .......................................................................................................... 11
7.2
Upit #2 .......................................................................................................... 11
7.3
Upit #3 .......................................................................................................... 12
7.4
Upit #4 – glavni upit ..................................................................................... 13
8 ZAKLJUČAK ...................................................................................................... 14 9 LITERATURA ..................................................................................................... 15
1 UVOD 1.1 Predmet, objekt i cilj projektnog zadatka
Cilj ovog projektnog zadatke je kreiranje baze podataka Honorari Autora Magazina Info korištenjem SQL jezika na platformi MS SQL Server 2008. Baza podataka treba da sadrži sve relevantne podatke kako bi se mogla voditi evidencija: o objavljenim tekstovima u magazinu Info, o autorima koji su objavljivali tekstove u magazinu Info, te o ostvarenim honorarima za napisane tekstove svakog od autora. 1.2 Korišteni alati i tehnologije
Za potrebe realizacije ovog projektnog zadataka korišten je alat Microsoft SQL Server 2008, koji je instaliran na operativni sistem Microsoft Windows Server 2008. Pristup bazi podataka za administraciju i menadžment je obavljan sa klijentskog operativnog sistema Microsoft Windows 2008. Korišteni su alati Microsoft SQL Server Management Studio i SQL Server Configuration Manager. Za crtanje ER dijagrama baze podataka korišten je Microsoft Visio 2007. 1.3 Struktura rada
U uvodnom dijelu ovog rada navedeni su predmet i cilj rada, opis korištenih alata i tehnologija, kao i struktura rada. U drugom dijelu ovog rada opisana je namjena baze podataka, te su opisane osnovne karakteristike baze podataka Honorari Autora Magazina Info. Treći dio rada daje prikaz SQL naredbi korištenih za kreiranje potrebnih tabela u bazi podataka Honorari Autora Magazina Info. U četvrtom dijelu je prikazan ER dijagram baze podataka Honorari Autora Magazina Info. Peti dio je posvećen korištenim tipovima podataka, dok se u šestom dijelu navode SQL komande korištene za popunjavanje baze podataka. U sedmom dijelu su prikazani SQL upiti koji su zapravo i svrha čitavog kreiranja baze podataka Honorari Autora Magazina Info. Na samom kraju dat je popis literature koja je korišten prilikom izrade rada. 1
2 NAMJENA I OPIS BAZE PODATAKA Baza podataka Honorari Autora Magazina Info ima četiri osnovna zadatka: •
Voenje evidencije o autorima Info magazina;
•
Voenje evidencije o objavljenim tekstovima;
•
Pregled objavljenih tekstova za pojedinog autora;
•
Izračun autorskih honorara na osnovu broja objavljenih stranica i kvalitete tekstova.
Da bi podržala postavljenje zadatke u bazi Honorari Autora Magazina Info je kreirano ukupno pet tabela. Tabela Autori je jedna od dvije osnovne tabele. Ona sadrži sve podatke potrebne za komunikaciju i rad sa autorom: e-mail adresu, telefon, adresu stanovanja, te podatke o banci radi uplate honorara. Polje CijenaStranice u ovoj tabeli sadrži visinu honorara za jednu objavljenu stranicu zavisno od kvalitete tekstova koje autor piše, a što odreuje redakcijski kolegiji. Primarni ključ ove tabele je AutorID. Tabela Tekstovi je druga osnovna tabele baze podataka Honorari Autora Magazina Info. Ona sadrži podatke o objavljenim tekstovima: naslov, podnaslov, rubriku u kojem je tekst objavljen, te dužinu teksta u stranicama. Ova tabela sadrži polja AutorTekstaID i ObjavljenoMagazinID, koja služe za povezivanje za tabelama Autori i Magazin.Primarni ključ ove tabele je polje TekstID. Tabela Magazin sadrži osnovne podatke o izdanjima magazina: godinu izdanja, mjesec izdanja i ukupni broj stranica magazina. Primarni ključ ove tabele je polje MagazinID koje predstavlja broj izdanja, pa se zbog toga ne generiše automatski već se unosi. Tri tabele Banke, Rubrike i Opstine su pomoćne i sadrže izdvojene podatke koji se ponavljaju. Ove tri tabele su rezultat normalizacije baze podataka Honorari Autora Magazina Info.
2
3 SQL KOD ZA KREIRANJE TABELA BAZE PODATAKA 3.1 Tabela Autori
CREATE TABLE Autori ( AutorID INT NOT NULL PRIMARY KEY IDENTITY, Ime NVARCHAR(20) NOT NULL, Prezime NVARCHAR(20) NOT NULL, Email VARCHAR(20) NOT NULL, Telefon VARCHAR(12) NOT NULL, Opstina INT NOT NULL, Adresa NVARCHAR(30) NOT NULL, JMBG VARCHAR(15) NOT NULL, Banka INT NOT NULL, BrojRacuna VARCHAR(20) NOT NULL, BrojZiroRacuna VARCHAR(20) NOT NULL, CijenaStranice FLOAT NOT NULL, ); 3.2 Tabela Magazin
CREATE TABLE Magazin ( MagazinID INT NOT NULL PRIMARY KEY IDENTITY, Godina VARCHAR(4) NOT NULL, Mjesec VARCHAR(2) NOT NULL, UkupnoStrana INT NOT NULL, ); 3.3 Tabela Tekstovi
CREATE TABLE Tekstovi ( TekstID INT NOT NULL PRIMARY KEY IDENTITY, Naslov NVARCHAR(20) NOT NULL, Podnaslov NVARCHAR(100) NOT NULL, 3
Rubrika INT NOT NULL, DuzinaTeksta INT NOT NULL, AutorTekstaID INT NOT NULL, ObjavljenoMagazinID INT NOT NULL, ); 3.4 Tabela Opstine
CREATE TABLE Opstine ( OpstinaID INT NOT NULL PRIMARY KEY IDENTITY, NazivOpstine NVARCHAR(20) NOT NULL, ); 3.5 Tabela Banke
CREATE TABLE Banke ( BankaID INT NOT NULL PRIMARY KEY IDENTITY, NazivBanke NVARCHAR(20) NOT NULL, );
3.6 Tabela Rubrike
CREATE TABLE Rubrike ( RubrikaID INT NOT NULL PRIMARY KEY IDENTITY, NazivRubrike NVARCHAR(20) NOT NULL, );
4
4 ER DIJAGRAM BAZE PODATAKA
Slika 1 - Shema baze podataka Honorari Info Magazin.
5
5 TIPOVI PODATAKA KORIŠTENI U TABELAMA
5.1 Tabela Autori
Atribut
Tip
AutorID
integer
Ime
nvarchar(20)
Prezime
nvarchar(20)
Email
varchar(20)
Telefon
varchar(12)
Opstina
integer
Adresa
nvarchar(30)
JMBG
varchar(15)
Banka
integer
BrojRacuna
varchar(20)
BrojZiroRacuna
varchar(20)
CijenaStranice
float
5.2 Tabela Magazin
Atribut
Tip
MagazinID
integer
Godina
varchar(4)
Mjesec
varchar(2)
UkupnoStrana
float
6
5.3 Tabela Tekstovi
Atribut
Tip
TekstID
integer
Naslov
nvarchar(20)
Podnaslov
nvarchar(100)
Rubrika
integer
DuzinaTeksta
float
AutorTekstaID
integer
ObjavljenoMagazinID
integer
5.4 Tabela Opstine
Atribut
Tip
OpstinaID
integer
NazivOpstine
nvarchar(20)
5.5 Tabela Banke
Atribut
Tip
BankaID
integer
Nazivbanke
nvarchar(20)
5.6 Tabela Rubrike
Atribut
Tip
RubrikaID
integer
NazivRubrike
nvarchar(20)
7
6 SQL KOD ZA POPUNJAVANJE TABELA PODACIMA
6.1 Tabela Autori
INSERT INTO Autori (Ime, Prezime, Email, Telefon, Opstina, Adresa, JMBG, Banka, BrojRacuna, BrojZiroRacuna, CijenaStranice) VALUES ('Goran', 'Milić', '
[email protected]', '062/345-901', 1, 'Gradačačka 27', '280997617003', 1, '85976423', '160000000782', 35), ('Senad', 'Karić', '
[email protected]', '065/846-762', 2, 'Bojnička 13', '210981617042', 1, '12312546', '1792000000043', 30), ('Maid', 'Lukić', '
[email protected]', '061/597-984', 3, 'Trg nezavisnosti 12', '23454534345', 1, '3463473457', '2100004000023', 35), ('Tarik',
'Skokić',
'
[email protected]',
'035/789-358',
1,
'Cvjetna
ulica
21',
'126543456345', 2, '658765823', '16000000000534', 25), ('Ana', 'Zarić', '
[email protected]', '033/564-876', 2, 'Emila Zole 12', '63456345773', 2, '345634563456', '160000234534', 30), ('Dragan', 'Lopot', '
[email protected]', '061/795-654', 3, 'Maršala Tita 132', '13453436453', 3, '4325345253', '2123452345623', 35);
6.2 Tabela Magazin
SET IDENTITY_INSERT Magazin ON INSERT INTO Magazin (MagazinID, Godina, Mjesec, UkupnoStrana) VALUES (171, 2012, 4, 112), (170, 2012, 3, 128), (169, 2012, 2, 128), 8
(168, 2012, 1, 128), (167, 2011, 12, 128), (166, 2011, 11, 128), (165, 2011, 10, 128), (164, 2011, 09, 112), (162, 2011, 07, 136), (161, 2011, 06, 128), (160, 2011, 05, 128);
Napomena: opcija IDENTITY_INSERT je uključena zbog ručnog unošenja primarnih ključeva kako bi njihovi brojevi odgovarali stvarnom broju magazina. Opasnost od duplicirana je minimalna pošto se tek po jedan magazin unosi svaki mjesec, a brojevi magazina su svakako jedinstveni.
6.3 Tabela Tekstovi
INSERT INTO Tekstovi (Naslov, Rubrika, DuzinaTeksta, AutorTekstaID, ObjavljenoMagazinID, Podnaslov) VALUES ('Canon FG34', 1, 2, 1, 171, 'Lorem ipsum dolor sit amet'), ('Epson TRG12', 1, 3, 1, 170, 'Lorem ipsum dolor sit amet'), ('Nikon RZEO12', 1, 1, 1, 169, 'Lorem ipsum dolor sit amet'), ('Acer 2376', 1, 2, 2, 171, 'Lorem ipsum dolor sit amet'), ('Firefox', 2, 2, 2, 170, 'Lorem ipsum dolor sit amet'), ('Google Chrome', 2, 2, 2, 169, 'Lorem ipsum dolor sit amet'), ('Internet Explorer 10', 2, 2, 1, 171, 'Lorem ipsum dolor sit amet'), ('MS Office 2012', 3, 4, 3, 171, 'Lorem ipsum dolor sit amet'), ('Windows 8', 3, 8, 1, 170, 'Lorem ipsum dolor sit amet'), ('Avira antivirus', 3, 2, 3, 169, 'Lorem ipsum dolor sit amet'), 9
('Windows Server 2012', 3, 4, 1, 170, 'Lorem ipsum dolor sit amet'), ('Ubuntu Linux', 3, 5, 2, 169, 'Lorem ipsum dolor sit amet'), ('Comodo Firewall', 3, 1, 3, 171, 'Lorem ipsum dolor sit amet'), ('Adobe Photoshop', 3, 3, 2, 169, 'Lorem ipsum dolor sit amet');
6.4 Tabela Opstine
INSERT INTO Opstine (NazivOpstine) VALUES ('Centar'), ('Tuzla'), ('Mostar'), ('Novi Grad');
6.5 Tabela Banke
INSERT INTO Banke (NazivBanke) VALUES ('Raiffeisen'), ('UniCredit'), ('Tuzlanksa banka'), ('Volks Bank');
6.6 Tabela Rubrike
INSERT INTO Rubrike (NazivRubrike) VALUES ('Hardver'), ('Internet'), ('Softver'), ('Vijesti');
10
7 SQL UPITI NAD POPUNJENIM TABELAMA
7.1 Upit #1
Upit koji kao rezultat daje popis tekstova, autora i duzine teksta iz broja 171: SELECT Autori.Ime, Autori.Prezime, Tekstovi.Naslov, Tekstovi.DuzinaTeksta FROM Autori INNER JOIN Tekstovi ON Autori.AutorID = Tekstovi.AutorTekstaID WHERE Tekstovi.ObjavljenoMagazinID = '171'
Rezultat: Ime
Prezime
Naslov
DuzinaTeksta
---------------- ------------------ ------------------ ---------------------Goran
Milić
Canon FG34
2
Senad
Karić
Acer 2376
2
Goran
Milić
Internet Explorer 10
2
Maid
Lukić
MS Office 2012
4
Maid
Lukić
Comodo Firewall
1
7.2 Upit #2
Upit koji kao rezultat daje popis svih tekstova jednog autora (Goran Milić): SELECT Tekstovi.Naslov, Tekstovi.DuzinaTeksta, Tekstovi.ObjavljenoMagazinID FROM Autori INNER JOIN Tekstovi ON Autori.AutorID = Tekstovi.AutorTekstaID WHERE Autori.Ime = 'Goran' AND Autori.Prezime = 'Milić'
11
Rezultat: Naslov
DuzinaTeksta ObjavljenoMagazinID
--------------------
------------
---------------------------
Canon FG34
2
171
Epson TRG12
3
170
Nikon RZEO12
1
169
Internet Explorer 10
2
171
Windows 8
8
170
Windows Server 2012
4
170
7.3 Upit #3
Upit koji kao rezultat daje prosječan broj strana koje autor piše u magazinu: SELECT Autori.Ime, Autori.Prezime, AVG(Tekstovi.DuzinaTeksta) AS 'Prosjecno strana' FROM Autori INNER JOIN Tekstovi ON Autori.AutorID = Tekstovi.AutorTekstaID GROUP BY Autori.Ime, Autori.Prezime
Rezultat: Ime
Prezime
Prosjecno strana
-------------------- -------------------- ---------------Senad
Karić
2
Maid
Lukić
2
Goran
Milić
3
12
7.4 Upit #4 – glavni upit
Upit koji kao rezultat daje ukupnu sumu honorara autora Goran Milić za broj 171: SELECT Autori.Ime, Autori.Prezime, SUM(Tekstovi.DuzinaTeksta) AS 'Ukupno Strana', Autori.CijenaStranice AS 'Cijena po stranici', SUM(Tekstovi.DuzinaTeksta) * Autori.CijenaStranice AS 'Ukupni honorar' FROM Autori INNER JOIN Tekstovi ON Autori.AutorID = Tekstovi.AutorTekstaID WHERE
Autori.Ime
=
'Goran'
AND
Autori.Prezime
=
'Milić'
AND
Tekstovi.ObjavljenoMagazinID= 171 GROUP BY Autori.Ime, Autori.Prezime, Autori.CijenaStranice
Rezultat: Ime
Prezime
Ukupno Strana Cijena po stranici Ukupni honorar
----------- --------------- ------------------- --------------------- -------------------Goran
Milić
4
35,00
140,00
13
8 ZAKLJUČAK
Baza podataka Honorari Autora Magazina Info u svom sadašnjem stanju predstavlja jako dobar okvir za kreiranje aplikacije koja bi olakšala posao urednicima Info magazina. Kreiranjem aplikacije bi se olakšao posao korisnicima koji ne poznaju dovoljno dobro SQL komande, a takoer bi olakšala unos novih podataka kroz filtriranje korisničkih unosa. Obzirom da je baza podataka Honorari Autora Magazina Info uraena u Microsoftovom SQL Serveru, kao prirodno okruženje za kreiranje aplikacije se nameću neki od Microsoftovih programskih jezika Visual Basic ili C#. Što se tiče mogućih proširenja baze Honorari Autora Magazina Info, u obzir bi došlo čuvanje
kompletnih tekstova umjesto trenutnih naslova i podnaslova. Ovim proširenjem
bi baza podataka postala još korisnija urednicima magazina Info, jer bi se mogla koristiti i za referenciranje prilikom pisanja tekstova. Takoer, ovakva baza bi mogla poslužiti i za kreiranje online pretrage tekstova koja bi se mogla postaviti na web sajta magazina Info.
14
9 LITERATURA
Knjige: 1. Gordana Pavlović-Lažetić, "Uvod u relacione baze podataka", Matematički fakultet, Beograd, 1996. 2. Mike Chapple, Microsoft SQL Server 2008 For Dummies, For Dummies, USA, 2008. 3. Allen G. Taylor, SQL For Dummies, For Dummies, USA, 1998.
Internet: 4. http://en.wikipedia.org/wiki/SQL, (18.04.2012.) 5. http://www.w3schools.com/sql/default.asp, (18.04.2012.)
15
