BPII - Vjezba 8

USE IB140261sara
GO

/*1. Unutar baze podataka kreirane u vježbi 7 dodati novu tabelu Komisija sa sljedećim poljima:
 Ime, polje za unos 30 karaktera (obavezan unos)
 Prezime, polje za unos 30 karaktera (obavezan unos)
 Titula, polje za unos 15 karaktera,
 Telefon, polje za unos 20 karaktera,
 Email, polje za unos 50 karaktera.
Napomena: Tabelu kreirati bez primarnog ključa.*/

CREATE TABLE Komisija
(
Ime nvarchar (30) NOT NULL,
Prezime nvarchar (30) NOT NULL,
Titula nvarchar (15),
Telefon nvarchar (20),
Email nvarchar (50)
);

/*2. U prethodno kreiranu tabelu, primjenom podupita,
importovati 10000 osoba iz baze podataka AdventureWorks2014, a zatim testirati rad upita (testni upiti)
provjeravajući aktualni plan izvršenja.*/

INSERT INTO Komisija(Ime,Prezime, Titula, Telefon, Email)
SELECT TOP 10000 pp.FirstName, pp.LastName, pp.Title, ppp.PhoneNumber, pea.EmailAddress
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.Person.PersonPhone AS ppp ON pp.BusinessEntityID=ppp.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID

SELECT * FROM Komisija
--Plan izvrsenja upita:
--Estimated operator cost: 0.094282
--Estimated I/O cost: 0.0832035
--Estimated CPU cost: 0.0110785
--Estimated Subtree cost: 0.094282
--Estimated row size: 129 B

/*3. U tabelu Komisija dodati primarni ključ pod nazivom ClanKomisijeID, automatski generator vrijednosti,
a zatim testirati izvršenje prethodno kreiranih upita, te provjeriti aktualni plan izvršenja.
U komentaru zapisati razlike uočene u planu izvršenja upita!*/
ALTER TABLE Komisija
ADD ClanKomisijeID int IDENTITY(1,1) PRIMARY KEY NOT NULL
SELECT * FROM Komisija
--Plan izvrsenja upita:
--Estimated operator cost: 0.0979857
--Estimated I/O cost: 0.0868287
--Estimated CPU cost: 0.011157
--Estimated Subtree cost: 0.0979857
--Estimated row size: 133 B

/*4. Tabelu Komisija povezati sa tabelom Testovi many-to-many relacijom,
te importovati testne podatke kao članove komisije za određene testove.*/
CREATE TABLE KomisijaTestovi
(
TestID int FOREIGN KEY REFERENCES Testovi (TestID),
ClanKomisijeID int FOREIGN KEY REFERENCES Komisija(ClanKomisijeID),
PRIMARY KEY (TestID, ClanKomisijeID)
);
SELECT * FROM KomisijaTestovi
SELECT * FROM Testovi
SELECT * FROM Komisija

INSERT INTO KomisijaTestovi(TestID, ClanKomisijeID)
VALUES
(1, 1),
(2,2),
(1,3),
(2,10),
(1,50),
(2,100)


/*5. Kreirati jednostavan non-clustered indeks nad tabelom Komisija (npr. polje Prezime)
i upitima testirati primjenu indeksa, provjeravajući aktualni plan izvršenja.*/
CREATE NONCLUSTERED INDEX KomisijaPrezime
ON Komisija (Prezime)
GO

/*6. Kreirati kompozitni non-clustered indeks nad tabelom Komisija
(npr. polja Ime i Prezime) i upitima testirati primjenu indeksa, provjeravajući aktualni plan izvršenja.*/
CREATE NONCLUSTERED INDEX KomisijaImePrezime
ON Komisija (Ime, Prezime)

--testiranje bez indeksa
ALTER INDEX KomisijaImePrezime
ON Komisija DISABLE
SELECT Ime, Prezime
FROM Komisija
--Estimated operator cost: 0.097857
--Estimated I/O cost: 0.0868287
--...

--testiranje sa indexom
ALTER INDEX KomisijaImePrezime
ON Komisija REBUILD
SELECT Ime, Prezime
FROM Komisija
--Estimated operator cost: 0.0275694
--Estimated I/O cost: 0.0387264
--...

/*7. Kreirati kompozitni non-clustered indeks sa uključenim dodatnim kolonama nad tabelom Komisija
i upitima testirati primjenu indeksa, provjeravajući aktualni plan izvršenja.*/
CREATE NONCLUSTERED INDEX komisijaKolone
ON Komisija (Telefon)
INCLUDE (Titula)


/*8. Kreirati unique non-clustered indeks nad tabelom Komisija, polje Email. INSERT komandom testirati
 funkcionalnost prethodno kreiranog indeksa.*/
 CREATE UNIQUE NONCLUSTERED INDEX komisijaEmail
 ON Komisija (Email)

 INSERT INTO Komisija (Ime, Prezime, Email)
 VALUES
 ('Sara', 'Saric', 'sara@gmail.com')

 /*9. Nad bazom podataka kreirati minimalno 5 kompleksnijih upita primjenom različitih
 elemenata SELECT komande. Za svaki upit provjeriti plan izvršenja i zapisati komentare.
 Također, primjenom alata SQL Server Profiler kreirati novi trace, izvršiti sve kreirane upite te rezultat dobiven
 alatom pohraniti na file system.*/

/*10. Primjenom alata SQL Server Database Engine Tuning Advisor, nad bazom podataka iskoristiti prethodno kreirani trace file i provjeriti preporuke dobivene od strane korištenog alata. Ukoliko alat ponudi određene preporuke, iste implementirati nad bazom podataka.*/