CREATE DATABASE Kutuphane
go
USE Kutuphane

Create Table Kutuphane
(
	id int identity (1,1),
	KutuphaneAdi VARCHAR(50),
	primary key(id)
)

INSERT INTO Kutuphane (KutuphaneAdi) VALUES ('Devlet Kutuphanesi')
INSERT INTO Kutuphane (KutuphaneAdi) VALUES ('Halk Kütüphanesi')


Create Table Yazar
(
	id int identity (1,1),
	Yazar VARCHAR(50),
	primary key(id)
)

INSERT INTO Yazar (Yazar) VALUES ('Necip Fazıl Kısakürek')
INSERT INTO Yazar (Yazar) VALUES ('Mehmet Akif Ersoy')

Create Table Kitap
(
	id int identity (1,1),
	Kitap VARCHAR(50),
	YazarId int,
	KutuphaneId int,
	Stok int
	primary key(id)
)
INSERT INTO Kitap (Kitap,YazarId,KutuphaneId,Stok) VALUES ('Çile',1,1,20)
INSERT INTO Kitap (Kitap,YazarId,KutuphaneId,Stok) VALUES ('Safaat',2,2,30)

Create Table Kisi
(
	id int identity (1,1),
	KisiAdSoyad VARCHAR(50),
	KutuphaneId int,
	primary key(id)
)

INSERT INTO Kisi (KisiAdSoyad,KutuphaneId) values ('Mehmet',1)
INSERT INTO Kisi (KisiAdSoyad,KutuphaneId) values ('Ezgi',1)
INSERT INTO Kisi (KisiAdSoyad,KutuphaneId) values ('Latife',1)
INSERT INTO Kisi (KisiAdSoyad,KutuphaneId) values ('Haluk',2)
INSERT INTO Kisi (KisiAdSoyad,KutuphaneId) values ('Rıdvan',1)

Create Table KitapAlis
(
	id int identity (1,1),
	KitapId INT,
	KutuphaneId int,
	KisiId INT
	primary key(id)
)


ALTER TABLE KitapAlis
ADD FOREIGN KEY (KitapId)
REFERENCES Kitap(id)


ALTER TABLE KitapAlis
ADD FOREIGN KEY (KutuphaneId)
REFERENCES Kutuphane(id)

ALTER TABLE KitapAlis
ADD FOREIGN KEY (KisiId)
REFERENCES Kisi(id)

go
CREATE Trigger StokDuzenle
ON KitapAlis
AFTER INSERT
AS
BEGIN
DECLARE @id int

SELECT @id = KisiId FROM inserted

update Kitap 
SET Stok = Stok -1 
WHERE id = @id

END
go
INSERT INTO KitapAlis(KitapId,KutuphaneId,KisiId ) VALUES (1,1,1)
go
CREATE VIEW KitapKutuphane 
AS
SELECT ku.KutuphaneAdi,k.Kitap,k.Stok FROM Kutuphane ku
INNER JOIN Kitap k ON K.KutuphaneId = ku.id

go

CREATE Procedure KitapAl
(
	@KisiId INT,
	@KutuphaneId INT,
	@KitapId INT
)
AS 
BEGIN 
INSERT INTO KitapAlis(KitapId,KutuphaneId,KisiId ) VALUES (@KitapId,@KutuphaneId,@KisiId)

END