Vjezbe 10

--Vježba 10 – DDL komande

--1. Kreirati bazu podatka sa jednim data i jednim log fajlom. Imenovati je Vašim brojem dosijea. Prilikom kreiranje baze podataka za data fajl postaviti sljedeće parametre:
--Lokacija: D:\DBMS_Data
--Veličina: inicijalno 5 MB,  maksimalna veličina neograničena
--Uvećanje: 10%

--Za Log fajl postaviti sljedeće paramtre:
--Lokacija: C:\DBMS_Data\Log
--Veličina: inicijalno 2 MB,  maksimalna veličina neograničena
--Uvećanje: 5%
CREATE DATABASE [3020]
ON PRIMARY
(NAME='3020',FILENAME='D:\DBMS_Data\3020.mdf',SIZE=5120KB,FILEGROWTH=10%)
LOG ON
(NAME='3020_log',FILENAME='C:\DBMS_Data\Log\3020.ldf',SIZE=2048KB, FILEGROWTH=5%)
GO

--2. Koristeći SQL::DDL komande kreirati sljedeće tabele i osigurati referencijalni integritet:
--Edukatori (Ime, Prezime, Titula, Email, Telefon, CV, Slika)
--Predmeti (Naziv, Oznaka, ECTS [int])
--EdukatoriPredmeti (Edukator, Predmet, Zvanje, BrojSati)
USE [3020]
CREATE TABLE dbo.Edukatori
(EdukatorID int IDENTITY(1,1) PRIMARY KEY,
Ime NVARCHAR(20) NULL,
Prezime NVARCHAR(40) NULL,
Titula NVARCHAR(5) NULL,
Email NVARCHAR(30) NULL,
Telefon NVARCHAR(20) NULL,
CV VARBINARY(MAX) NULL,
Slika IMAGE NULL
)GO

CREATE TABLE dbo.Predmeti
(PredmetID int IDENTITY(1,1) PRIMARY KEY,
Naziv NVARCHAR(20) NOT NULL,
Oznaka NVARCHAR(10) NULL,
ECTS INT NULL
)GO

CREATE TABLE EdukatoriPredmeti
(EdukatorPredmetID int IDENTITY(1,1) PRIMARY KEY,
EdukatorID int NOT NULL FOREIGN KEY (EdukatorID) REFERENCES dbo.Edukatori(EdukatorID),
PredmetID int NOT NULL FOREIGN KEY (PredmetID) REFERENCES dbo.Predmeti(PredmetID),
Zvanje NVARCHAR(16) NULL,
BrojSati int NULL
)GO

--Također, kreirati tabelu Fakulteti (Naziv, Telefon, Fax) i povezati je sa prethodno kreiranim tabelama Edukatori i EdukatoriPredmeti. U tabelu Edukatori dodati polje Adresa.
--Za polje ECTS u tabeli Predmeti izmijeniti tip podatka u DECIMAL.
CREATE TABLE dbo.Fakulteti
(FakultetID int IDENTITY(1,1) PRIMARY KEY,
EdukatorPredmetID int NOT NULL FOREIGN KEY (EdukatorPredmetID) REFERENCES dbo.EdukatoriPredmeti(EdukatorPredmetID), -- ne treba dodavati EdukatorID FK jer ga imamo u EdukatorPredmetID
Naziv NVARCHAR(20) NULL,
Telefon NVARCHAR(16) NULL,
Fax NVARCHAR(16) NULL
)GO

ALTER TABLE dbo.Edukatori
ADD Adresa NVARCHAR(20) NULL;
GO

ALTER TABLE dbo.Predmeti
ALTER COLUMN ECTS DECIMAL NULL
GO

--3. Kreirati stored proceduru za upis podataka u tabelu Edukatori. Također, kreirati procedure za izmjenu i brisanje podataka u istoj tabeli.
--Koristeći procedure za upis podataka u tabelu dodati 5 novih edukatora.
SELECT *
FROM Edukatori
GO

CREATE PROCEDURE UpisEdukatora
@Ime NVARCHAR(20)=NULL,
@Prezime NVARCHAR(40)=NULL,
@Titula NVARCHAR(5)=NULL,
@Email NVARCHAR(30)=NULL,
@Telefon NVARCHAR(16)=NULL,
@CV VARBINARY(MAX)=NULL,
@Slika IMAGE=NULL,
@Adresa NVARCHAR(20)=NULL
AS
INSERT INTO Edukatori
VALUES(@Ime,@Prezime,@Titula,@Email,@Telefon,@CV,@Slika,@Adresa)
GO

CREATE PROCEDURE IzmjenaEdukatora
@EdukatorID INT,
@Ime NVARCHAR(20)=NULL,
@Prezime NVARCHAR(40)=NULL,
@Titula NVARCHAR(5)=NULL,
@Email NVARCHAR(30)=NULL,
@Telefon NVARCHAR(16)=NULL,
@CV VARBINARY(MAX)=NULL,
@Slika IMAGE=NULL,
@Adresa NVARCHAR(20)=NULL
AS
UPDATE Edukatori
SET Ime=@Ime,Prezime=@Prezime,Titula=@Titula,Email=@Email,Telefon=@Telefon,CV=@CV,Adresa=@Adresa
WHERE EdukatorID=@EdukatorID
GO

CREATE PROCEDURE BrisanjeEdukatora
@EdukatorID int
AS
DELETE FROM Edukatori
WHERE EdukatorID=@EdukatorID
GO

EXEC UpisEdukatora @Ime='Mike',@Prezime='Smith',@Titula='Mr.',@Email='[email protected]',@Telefon=NULL,@CV=NULL,@Adresa=NULL
GO
EXEC UpisEdukatora
GO
EXEC UpisEdukatora  @Prezime='Neko', @Email='[email protected]'
GO
EXEC UpisEdukatora 'David','Theblack',NULL,NULL,NULL,NULL,NULL,'Neverstreet'
GO
EXEC UpisEdukatora 'Mali','Hamo',@Email='[email protected]'
GO

--4. U tabele Predmeti i Fakulteti dodati po 5 zapisa. Također, u tabelu EduktoriPredmeti dodati proizvoljne podatke (ko predaje određeni predmet, te na kojem fakultetu).
SELECT *
FROM Predmeti
GO

INSERT INTO Predmeti
VALUES('Demokratija i ljudska prava','smijesno..',1.0),
('Lokalna samouprava i javna prava','zalosno..',1),
('Medjunarodni odnosi i diplomatija','LOL..',2),
('Sigurnosne i mirovne studije','kek..',1),
('Socijalna politika','HAHAHHA..',1)
GO

SELECT *
FROM Fakulteti
GO

INSERT INTO Fakulteti
VALUES('Fakultet plitkih nauka','+387/33 203-562','+387/33 666-884')
GO

UPDATE Edukatori
SET FakultetID=3
GO

SELECT *
FROM Edukatori
GO

SELECT *
FROM EdukatoriPredmeti
GO

INSERT INTO EdukatoriPredmeti
VALUES(6,4,'U penziju',6),
(6,4,'U penziju',6),
(2,4,'U penziju',3),
(3,5,'U penziju',2),
(3,6,'U penziju',12),
(4,6,'U penziju',8),
(5,7,'U penziju',6),
(5,8,'U penziju',6)
GO

--5. Kreirati pogled (View) koji obuhvata sljedeće podatke: ime i prezime edukatora (spojeno), titula, oznaka predmeta, naziv predmeta, broj ECTS kredita, broj sati.
CREATE VIEW osnovno
AS
SELECT E.Ime+' '+E.Prezime AS 'Ime i prezime',E.Titula AS 'Titula',P.Oznaka 'Oznaka predmeta',P.Naziv'Naziv predmeta',P.ECTS'Broj ECTS kredita',EP.BrojSati'Broj sati'
FROM Edukatori AS E JOIN EdukatoriPredmeti AS EP
ON E.EdukatorID=EP.EdukatorID JOIN Predmeti AS P
ON EP.PredmetID=P.PredmetID JOIN Fakulteti AS F
ON E.FakultetID=F.FakultetID

SELECT *
FROM osnovno
GO
--6. Kreirati stored proceduru koja će na osnovu proslijeđenog parametra NazivPredmeta prikazti nastavno osoblje angažovano na predmetu. Iskoristiti prethodno kreirani view.
CREATE PROCEDURE ProsljediPredmet
@ImePredmeta NVARCHAR(40)
AS
SELECT *
FROM osnovno
WHERE [Naziv predmeta]=@ImePredmeta
GO

EXEC ProsljediPredmet 'Demokratija i ljudska prava'
GO

--Izmijeniti prethodno kreiranu stored proceduru tako da prima dva parametra: NazivPredmeta i ImePrezime edukatora. U zavisnosti od proslijeđenog/ih parametra,
--procedura treba da prikaže podatke o predmetima i nastavnom osoblju.
ALTER PROCEDURE ProsljediPredmet
@ImePredmeta NVARCHAR(40),
@ImeEdukatora NVARCHAR(20)
AS
SELECT *
FROM osnovno
WHERE [Naziv predmeta]=@ImePredmeta AND [Ime i prezime]=@ImeEdukatora
GO

EXEC ProsljediPredmet 'Demokratija i ljudska prava','Mike Smith'
GO
--7. Koristeći proceduru za brisanje zapisa u tabeli Edukatori (Zadatak 3) obrisati 2 edukatora. Ukoliko edukatori imaju dodijeljene predmete,
--modifikovati proceduru tako da prethodno obriše sve edukatoru dodijeljene predmete, a zatim obriše zapis iz tabele Edukatori.
SELECT *
FROM Edukatori
GO

EXEC BrisanjeEdukatora 3

ALTER PROCEDURE BrisanjeEdukatora
@EdukatorID int
AS
DELETE FROM EdukatoriPredmeti
WHERE EdukatoriPredmeti.EdukatorID=@EdukatorID
DELETE FROM Edukatori
WHERE EdukatorID=@EdukatorID
GO

EXEC BrisanjeEdukatora 3

--8. Kreirati trigger koji će u tabelu EdukatoriAudit zapisati detalje izmjene podataka nad tabelom Edukatori.
CREATE TABLE tEdukatoriaudit
(
PromjenaID int IDENTITY(1,1) PRIMARY KEY,
AIme NVARCHAR(20) NULL,
APrezime NVARCHAR(40) NULL,
ATitula NVARCHAR(5) NULL,
AEmail NVARCHAR(30) NULL,
ATelefon NVARCHAR(20) NULL,
vrijeme datetime NULL,
korisnik NVARCHAR(40)
)
GO

CREATE TRIGGER Edukatoriaudit
ON Edukatori
FOR INSERT,UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tEdukatoriaudit
(AIme,Aprezime,ATitula,AEmail,ATelefon,vrijeme,korisnik)
SELECT E.Ime,E.Prezime,E.Titula,E.Email,E.Telefon,SYSDATETIME(),SYSTEM_USER
FROM Edukatori AS E;
END;

--9. Koristeći proceduru za izmjenu zapisa u tabeli Edukatori (Zadatak 3) izmijeniti mail i telefon određenom edukatoru.
SELECT *
FROM Edukatori
GO

EXEC IzmjenaEdukatora 5,'David','TheBlack','Mr.','[email protected]',NULL,NULL,NULL,'New street 22'
GO

SELECT *
FROM tEdukatoriaudit
GO

--10. Uraditi full backup baze podataka na lokaciju D:\DB_Backup.
BACKUP DATABASE [3020]
TO DISK='D:\DB_Backup\3020.bak'
WITH NOFORMAT