DBMS II parc vjezbe

--1.    Kreirati bazu podataka pod nazivom: BrojDosijea (npr. 2046) bez posebnog kreiranja data i log fajla.
CREATE DATABASE [3020]
ON PRIMARY
(NAME='3020',FILENAME='D:\DBMS_Data\3020.mdf')
GO

--2.    U Vašoj bazi podataka kreirati tabele sa sljedećim parametrima:
--•   Studenti
--•   StudentID, automatski generator vrijednosti i primarni ključ
--•   BrojDosijea, polje za unos 10 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
--•   Ime, polje za unos 35 UNICODE karaktera (obavezan unos)
--•   Prezime, polje za unos 35 UNICODE karaktera (obavezan unos)
--•   Godina studija, polje za unos cijelog broja (obavezan unos)
--•   NacinStudiranja, polje za unos 10 UNICODE karaktera (obavezan unos) DEFAULT je Redovan
--•   Email, polje za unos 50 karaktera (nije obavezan)
--•   Nastava
--•   NastavaID, automatski generator vrijednosti i primarni ključ
--•   Datum, polje za unos datuma i vremana (obavezan unos)
--•   Predmet, polje za unos 20 UNICODE karaktera (obavezan unos)
--•   Nastavnik, polje za unos 50 UNICODE karaktera (obavezan unos)
--•   Ucionica, polje za unos 20 UNICODE karaktera (obavezan unos)
--•   Prisustvo
--•   PrisustvoID, automatski generator vrijednosti i primarni ključ
--•   StudentID, spoljni ključ prema tabeli Studenti
--•   NastavaID, spoljni ključ prema tabeli Nastava
USE [3020]
CREATE TABLE Studenti
(StudentID INT IDENTITY(1,1) PRIMARY KEY,
BrojDosijea NVARCHAR(10) NOT NULL UNIQUE,
Ime NVARCHAR(35) NOT NULL,
Prezime NVARCHAR(35) NOT NULL,
GodinaStudija INT NOT NULL,
NacinStudiranja NVARCHAR(10) NOT NULL DEFAULT 'Redovan',
Email VARCHAR(50)
)
GO
CREATE TABLE Nastava
(NastavaID INT IDENTITY(1,1) PRIMARY KEY,
Datum DATETIME NOT NULL,
Predmet NVARCHAR(20) NOT NULL,
Nastavnik NVARCHAR(50) NOT NULL,
Ucionica NVARCHAR(20) NOT NULL,
)
GO
CREATE TABLE Prisustvo
(PrisustvoID INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT FOREIGN KEY (StudentID) REFERENCES Studenti(StudentID),
NastavaID INT FOREIGN KEY (NastavaID) REFERENCES Nastava(NastavaID)
)
GO

--3.    Kreirati tabelu Predmeti sa sljedećim parametrima:
--•   PredmetID, automatski generator vrijednosti i primarni ključ
--•   Naziv, polje za unos 30 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
--Modifikovati tabelu Nastava (ukloniti kolonu Predmet) i povezati je sa tabelom Predmeti. Koristeći INSERT komandu u tabelu Predmeti unijeti tri zapisa.
CREATE TABLE Predmeti
(PredmetID INT IDENTITY(1,1) PRIMARY KEY,
Naziv NVARCHAR(30) UNIQUE
)
GO

ALTER TABLE Nastava
DROP COLUMN Predmet
GO

ALTER TABLE Nastava
ADD PredmetID INT FOREIGN KEY (PredmetID) REFERENCES Predmeti(PredmetID)
GO

INSERT INTO Predmeti
VALUES ('Reporting'),
('Sociologija'),
('Komunikacijske tehnologije')

--4.    Koristeći bazu podataka AdventureWorksLT2012 i tabelu SalesLT.Customer, preko INSERT i SELECT komande importovati 10 zapisa u tabelu Studenti i to sljedeće kolone:
--•   Prva tri karaktera kolone Phone -> BrojDosijea
--•   FirstName -> Ime
--•   LastName -> Prezime
--•   2 -> GodinaStudija
--•   DEFAULT -> NacinStudiranja
--•   EmailAddress -> Email
USE AdventureWorksLT2012
INSERT INTO [3020].dbo.Studenti(BrojDosijea,Ime,Prezime,GodinaStudija,Email)
SELECT TOP 10 LEFT(Phone,3),FirstName,LastName,2,EmailAddress
FROM SalesLT.Customer
GO

USE [3020]
SELECT *
FROM Studenti
GO

--5.    U Vašoj bazi podataka kreirajte stored proceduru koja će na osnovu proslijeđenih parametara raditi izmjenu (UPDATE) podataka u tabeli Studenti. Proceduru pohranite pod nazivom usp_Studenti_Update.
--Koristeći prethodno kreiranu proceduru izmijenite jedan zapis sa Vašim podacima.
CREATE PROCEDURE usp_Studenti_Update
@StudentID INT,
@BrojDosijea NVARCHAR(10),
@Ime NVARCHAR(35),
@Prezime NVARCHAR(35),
@GodinaStudija INT,
@NacinStudiranja NVARCHAR(10),
@Email VARCHAR(50)
AS
UPDATE Studenti
SET BrojDosijea=@BrojDosijea, Ime=@Ime,Prezime=@Prezime,GodinaStudija=@GodinaStudija,NacinStudiranja=@NacinStudiranja,Email=@Email
WHERE StudentID=@StudentID
GO

SELECT *
FROM Studenti
GO
SELECT *
FROM Predmeti
GO
SELECT *
FROM Nastava
GO
SELECT *
FROM Prisustvo
GO

EXEC usp_Studenti_Update 2,666,Mike,Smith,2,'Redovan','mike@aol.ba'
GO
--6.    U Vašoj bazi podataka kreirajte stored proceduru koja će raditi INSERT podataka u tabelu Nastava. Podaci se moraju unijeti preko parametara.
--Također, u istoj proceduri dodati prisustvo na nastavi (koristeći INSERT SELECT komandu dodati prisustvo sve studente za prethodno dodanu nastavu). Proceduru pohranite pod nazivom usp_Nastava_Insert.
CREATE PROCEDURE usp_Nastava_Insert
@Datum DATETIME,
@Nastavnik NVARCHAR(50),
@Ucionica NVARCHAR(20),
@PredmetID INT
AS
BEGIN
INSERT INTO Nastava(Datum,Nastavnik,Ucionica,PredmetID)
VALUES(@Datum,@Nastavnik,@Ucionica,@PredmetID)
INSERT INTO Prisustvo
SELECT StudentID,@@IDENTITY
FROM Studenti
END
GO

EXEC usp_Nastava_Insert '6/27/2014',Sejfudin,AMF,2
GO

--7.    Koristeći proceduru koju ste kreirali u prethodnom zadatku dodati novu nastavu. Za parametar @Datum proslijediti trenutni datum i vrijeme, a ostale parametre upisati ručno.
DECLARE @sad DATETIME
SET @sad=GETDATE()
EXEC usp_Nastava_Insert @sad,Sejfudin,AMF,2
GO

--8.    U Vašoj bazi podataka kreirajte stored proceduru koja ća na osnovu proslijeđenih parametara (@NastavaID i @StudentID) brisati prisustvo na nastavi. Proceduru pohranite pod nazivom usp_Prisustvo_Delete.
CREATE PROCEDURE usp_Prisustvo_Delete
@StudentID INT,
@NastavaID INT
AS
DELETE FROM Prisustvo
WHERE NastavaID=@NastavaID AND StudentID=@StudentID
GO
EXEC usp_Prisustvo_Delete 2,8
GO

--9.    U Vašoj bazi podataka kreirajte view koji će sadržavati sljedeća polja: broj dosijea, ime i prezime studenta, datum nastave, učionicu, nastavnika i predmet. View pohranite pod nazivom view_Studenti_Nastava.
CREATE VIEW view_Studenti_Nastava
AS
SELECT BrojDosijea'Broj dosijea',Ime,Prezime,Nastava.Datum,Nastava.Ucionica,Nastava.Nastavnik,Predmeti.Naziv
FROM Studenti JOIN Prisustvo
ON Studenti.StudentID=Prisustvo.PrisustvoID JOIN Nastava
ON Nastava.NastavaID=Prisustvo.NastavaID JOIN Predmeti
ON Predmeti.PredmetID=Nastava.PredmetID
GO

SELECT *
FROM view_Studenti_Nastava