Klijenti - krediti - otplate

/*1. Kreirati bazu podataka koju ćete imenovati Vašim brojem dosijea.
Fajlove baze smjestiti na sljedeće lokacije:
 Data fajl -> D:\DBMS\Data
 Log fajl -> D:\DBMS\Log*/
CREATE DATABASE sarky12
ON
(NAME=sarky12_dat, FILENAME='C:\BP2\DATA\sarky12_mdf.', SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=10%)
LOG ON
(NAME=sarky12_log, FILENAME='C:\BP2\LOG\sarky12_ldf.', SIZE=2MB, MAXSIZE=UNLIMITED, FILEGROWTH=5%)

USE sarky12
GO
/*2. U bazi podataka kreirati sljedeće tabele:
a. Klijenti
 JMBG, polje za unos 13 karaktera (obavezan unos i jedinstvena vrijednost),
 Ime, polje za unos 30 karaktera (obavezan unos),
 Prezime, polje za unos 30 karaktera (obavezan unos),
 Adresa, polje za unos 100 karaktera (obavezan unos),
 Telefon, polje za unos 20 karaktera (obavezan unos),
 Email, polje za unos 50 karaktera (jedinstvena vrijednost),
 Kompanija, polje za unos 50 karaktera.*/
CREATE TABLE Klijenti
(
KlijentID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
JMBG nvarchar (13) UNIQUE NOT NULL,
Ime nvarchar (30) NOT NULL,
Prezime nvarchar (30) NOT NULL,
Adresa nvarchar (100) NOT NULL,
Telefon nvarchar (20) NOT NULL,
Email nvarchar (50) UNIQUE,
Kompanija nvarchar (50)
);
/*b. Krediti
 Datum, polje za unos datuma (obavezan unos),
 Namjena, polje za unos 50 karaktera (obavezan unos),
 Iznos, polje za decimalnog broja (obavezan unos),
 BrojRata, polje za unos cijelog broja (obavezan unos),
 Osiguran, polje za unos bit vrijednosti (obavezan unos),
 Opis, polje za unos dužeg niza karaktera*/
CREATE TABLE Krediti
(
KreditID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Datum smalldatetime NOT NULL,
Namjena nvarchar (50) NOT NULL,
Iznos decimal NOT NULL,
BrojRata int NOT NULL,
Osiguran bit NOT NULL,
Opis text
);
/*c. Otplate
 Datum, polje za unos datuma (obavezan unos)
 Iznos, polje za unos decimalnog broja (obavezan unos),
 Rata, polje za unos cijelog broja (obavezan unos),
 Opis, polje za unos dužeg niza karaktera.
Napomena: Klijent može uzeti više kredita, dok se kredit veže isključivo za jednog klijenta. Svaki kredit može imati
više otplata (otplata rata).
*/
CREATE TABLE Otplate
(KlijentID int FOREIGN KEY REFERENCES Klijenti(KlijentID),
KreditID int FOREIGN KEY REFERENCES Krediti(KreditID),
Datum smalldatetime NOT NULL,
Iznos decimal NOT NULL,
Rata int NOT NULL,
Opis text,
PRIMARY KEY (KlijentID, KreditID)
);
/*3. Koristeći AdventureWorks2014 bazu podataka, importovati 10 kupaca u tabelu Klijenti i to sljedeće kolone:
a. Zadnjih 13 karaktera kolone rowguid (Crticu '-' zamijeniti brojem 1)-> JMBG,
b. FirstName (Person) -> Ime,
c. LastName (Person) -> Prezime,
d. AddressLine1 (Address) -> Adresa,
e. PhoneNumber (PersonPhone) -> Telefon,
f. EmailAddress (EmailAddress) -> Email,
g. 'FIT' -> Kompanija
Također, u tabelu Krediti unijeti minimalno tri zapisa sa proizvoljnim podacima*/
SELECT RIGHT(REPLACE(pp.rowguid, '-', '1'), 13)
FROM AdventureWorks2014.Person.Person AS pp

INSERT INTO Klijenti (JMBG, Ime, Prezime, Adresa, Telefon, Email, Kompanija)
SELECT TOP 10
            RIGHT(REPLACE(pp.rowguid, '-', '1'), 13) AS [JMBG],
            pp.FirstName AS [ime],
            pp.LastName AS [prezime],
            pa.AddressLine1 AS [adresa],
            ppp.PhoneNumber AS [telefon],
            pea.EmailAddress AS [email],
            'FIT' AS [kompanija]
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.Person.Address AS pa ON pp.BusinessEntityID=pa.AddressID
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 Klijenti

INSERT INTO Krediti(Datum, Namjena, Iznos, BrojRata, Osiguran, Opis)
VALUES
( '2016-12-12 12:12:12', 'Stan', '1000', '3', '1', 'Neki opis'),
( '2015-11-11 11:11:11', 'Auto', '300', '2', '0', 'Neki opis'),
( '2010-10-10 10:10:10', 'Laptop', '500', '5', '1', 'Neki opis')

SELECT * FROM Krediti

/*4. Kreirati stored proceduru koja će na osnovu proslijeđenih parametara služiti za unos podataka u tabelu
Otplate. Proceduru pohraniti pod nazivom usp_Otplate_Insert. Obavezno testirati ispravnost kreirane
procedure (unijeti minimalno 5 zapisa sa proizvoljnim podacima).*/
GO
CREATE PROCEDURE usp_Otplate_Insert
@klijent int,
@kredit int,
@datum smalldatetime,
@iznos decimal,
@rata int,
@opis text
AS
    BEGIN
    INSERT INTO Otplate (KlijentID, KreditID, Datum, Iznos, Rata, Opis)
        VALUES (@klijent, @kredit, @datum, @iznos,@rata,@opis)
    END;

EXECUTE usp_Otplate_Insert '9','3','2017-01-02 15:45:22' ,'200', '2', 'Neki opis'
EXECUTE usp_Otplate_Insert '8','2','2016-02-03 16:46:23' ,'100', '1', 'Neki opis'
EXECUTE usp_Otplate_Insert '10','1','2015-01-04 15:47:24' ,'500', '3', 'Neki opis'
EXECUTE usp_Otplate_Insert '7','3','2011-07-03 17:45:27' ,'300', '2', 'Neki opis'
EXECUTE usp_Otplate_Insert '7','2','2017-01-01 14:44:22' ,'700', '7', 'Neki opis'

SELECT * FROM Krediti
SELECT * FROM Otplate

/*5. Kreirati view (pogled) nad podacima koji će prikazivati sljedeća polja:
jmbg, ime i prezime, adresa, telefon i email klijenta, zatim datum, namjenu i iznos kredita,
te ukupan broj otplaćenih rata i ukupan otplaćeni iznos.
View pohranite pod nazivom view_Krediti_Otplate.*/
GO
CREATE VIEW view_Krediti_Otplate
AS
    SELECT Klijenti.JMBG AS [JMBG], Klijenti.Ime AS [Ime], Klijenti.Prezime [Prezime],
           Klijenti.Adresa AS [Adresa], Klijenti.Telefon AS [Telefon], Klijenti.Email AS [email],
           Krediti.Datum AS [Datum], Krediti.Namjena AS [Namjena], Krediti.Iznos AS [Iznos],
           o.Rata AS [Ukupan broj otplaćenih rata],
           o.Iznos AS [Ukupan otplaćeni iznos]
    FROM Otplate AS o
    INNER JOIN Krediti ON o.KreditID=Krediti.KreditID
    INNER JOIN Klijenti ON o.KlijentID=Klijenti.KlijentID

    SELECT * FROM view_Krediti_Otplate


/*6. Kreirati stored proceduru koja će na osnovu proslijeđenog parametra @JMBG prikazivati podatke o otplati
kredita. Kao izvor podataka koristiti prethodno kreirani view. Proceduru pohraniti pod nazivom
usp_Krediti_Otplate_SelectByJMBG. Obavezno testirati ispravnost kreirane procedure.
*/
GO
CREATE PROCEDURE usp_Krediti_Otplate_SelectByJMBG
@JMBG nvarchar (13)
AS
    BEGIN
        SELECT view_Krediti_Otplate.Ime, view_Krediti_Otplate.Prezime, view_Krediti_Otplate.Iznos,
        view_Krediti_Otplate.[Ukupan broj otplaćenih rata], view_Krediti_Otplate.[Ukupan otplaćeni iznos]
        FROM view_Krediti_Otplate
        WHERE view_Krediti_Otplate.JMBG=@JMBG

    END;
EXECUTE usp_Krediti_Otplate_SelectByJMBG '1C8BDB9C5A1C9'

SELECT * FROM Klijenti
SELECT * FROM Krediti
SELECT * FROM Otplate

/*7. Kreirati proceduru koja će služiti za izmjenu podataka o otplati kredita. Proceduru pohraniti pod nazivom
usp_Otplate_Update. Obavezno testirati ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE usp_Otplate_Update
@kredit int,
@klijent int,
@datum smalldatetime,
@iznos decimal,
@rata int
AS
    BEGIN
        UPDATE Otplate
        SET KreditID=@kredit, KlijentID=@klijent, Datum=@datum, Iznos=@iznos, Rata=@rata
        WHERE Otplate.KlijentID
        =(
        SELECT Klijenti.KlijentID
        FROM Klijenti
        WHERE Klijenti.KlijentID=@klijent
        )
        AND
        Otplate.KreditID
        =(
        SELECT Krediti.KreditID
        FROM Krediti
        WHERE Krediti.KreditID=@kredit
        )
    END;

EXECUTE usp_Otplate_Update '2', '7', '2016-02-03 15:45:46', '200' ,'2'

/*8. Kreirati stored proceduru koja će služiti za brisanje kredita zajedno sa svim otplatama. Proceduru pohranite
pod nazivom usp_Krediti_Delete. Obavezno testirati ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE usp_Krediti_Delete
@kredit int
AS
    BEGIN
        DELETE FROM Otplate
        WHERE Otplate.KreditID
        =(
        SELECT Krediti.KreditID
        FROM Krediti
        WHERE Krediti.KreditID=@kredit)

        DELETE FROM Krediti
        WHERE KreditID=@kredit
    END;

EXECUTE usp_Krediti_Delete '2'


/*9. Kreirati trigger koji će spriječiti brisanje zapisa u tabeli Otplate. Trigger pohranite pod nazivom
tr_Otplate_IO_Delete. Obavezno testirati ispravnost kreiranog triggera.*/
GO
CREATE TRIGGER tr_Otplate_IO_Delete
ON Otplate
FOR DELETE
AS
    PRINT'Zabranjeno briasanje!'
    ROLLBACK;

DELETE Otplate

/*10. Uraditi full backup Vaše baze podataka na lokaciju D:\DBMS\Backup.*/
BACKUP DATABASE sarky12
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\sarky12.bak'