Learning MSSQL Series 01: Building the Library Database
In this series we will work step by step on Microsoft SQL Server (MSSQL). Every post uses the same database: a small school library. In this first post we build the database from scratch: we create the tables, define the constraints and load the sample data.
All SQL files of the series are on GitHub. Instead of copying the code, you can download the files and open them directly in SSMS.
Note: This series was originally prepared for a course taught in Turkish, so table names, column names and the comments inside the code are in Turkish. The glossary below gives their English meanings, and the code is identical to the files on GitHub.
Glossary of Turkish Names
Turkish name | English meaning |
Kategoriler / KategoriAdi | Categories / category name |
Yazarlar / Ad, Soyad, Ulke | Authors / first name, last name, country |
Kitaplar / KitapAdi | Books / book title |
YayinYili, SayfaSayisi, StokAdedi | Publication year, page count, stock quantity |
KitapYazar | BookAuthor (junction table) |
Uyeler / Eposta, Telefon, KayitTarihi | Members / e-mail, phone, registration date |
Oduncler / OduncTarihi | Loans / loan date |
SonTeslimTarihi, IadeTarihi | Due date, return date |
Beklenen | Expected (result) |
The Scenario
A school library wants to keep track of its books, authors, categories and members:
A book can have more than one author, and an author can have more than one book.
Each book belongs to exactly one category.
Members borrow books and return them within a given period.
These sentences give us six tables: Kategoriler, Yazarlar, Kitaplar, KitapYazar, Uyeler and Oduncler.
ER Diagram

Relationship | Type | How it is implemented |
A category contains many books | 1:N | Kitaplar.KategoriID → Kategoriler |
A book can have many authors, an author many books | N:M | KitapYazar junction table, composite PK (KitapID, YazarID) |
A member borrows many times | 1:N | Oduncler.UyeID → Uyeler |
A book is lent many times | 1:N | Oduncler.KitapID → Kitaplar |
Rule: In a 1:N relationship the foreign key always goes into the table on the "N" (many) side. An N:M relationship cannot be implemented directly; it needs a junction table that carries the primary keys of both sides.
Step 1: Creating the Database
CREATE DATABASE KutuphaneDB;
GO
USE KutuphaneDB;
GOStep 2: Creating the Tables
The order matters. First we create the tables that do not depend on any other table (Kategoriler, Yazarlar, Uyeler), then the tables with foreign keys (Kitaplar, KitapYazar, Oduncler). In the reverse order a FOREIGN KEY would point to a table that does not exist yet, and we would get an error.
Independent tables
CREATE TABLE Kategoriler (
KategoriID INT IDENTITY(1,1) PRIMARY KEY,
KategoriAdi NVARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE Yazarlar (
YazarID INT IDENTITY(1,1) PRIMARY KEY,
Ad NVARCHAR(50) NOT NULL,
Soyad NVARCHAR(50) NOT NULL,
Ulke NVARCHAR(50) NULL
);
CREATE TABLE Uyeler (
UyeID INT IDENTITY(1,1) PRIMARY KEY,
Ad NVARCHAR(50) NOT NULL,
Soyad NVARCHAR(50) NOT NULL,
Eposta NVARCHAR(100) NULL UNIQUE,
Telefon VARCHAR(15) NULL,
KayitTarihi DATE NOT NULL DEFAULT GETDATE()
);
GOTables with foreign keys
CREATE TABLE Kitaplar (
KitapID INT IDENTITY(1,1) PRIMARY KEY,
ISBN CHAR(13) NOT NULL UNIQUE,
KitapAdi NVARCHAR(150) NOT NULL,
YayinYili SMALLINT NULL,
SayfaSayisi INT NULL CHECK (SayfaSayisi > 0),
StokAdedi INT NOT NULL DEFAULT 1 CHECK (StokAdedi >= 0),
KategoriID INT NOT NULL,
CONSTRAINT FK_Kitaplar_Kategoriler
FOREIGN KEY (KategoriID) REFERENCES Kategoriler(KategoriID)
);
-- Kitaplar - Yazarlar arasındaki N:M ilişkiyi çözen ara tablo
CREATE TABLE KitapYazar (
KitapID INT NOT NULL,
YazarID INT NOT NULL,
CONSTRAINT PK_KitapYazar PRIMARY KEY (KitapID, YazarID), -- bileşik birincil anahtar
CONSTRAINT FK_KitapYazar_Kitaplar FOREIGN KEY (KitapID) REFERENCES Kitaplar(KitapID),
CONSTRAINT FK_KitapYazar_Yazarlar FOREIGN KEY (YazarID) REFERENCES Yazarlar(YazarID)
);
CREATE TABLE Oduncler (
OduncID INT IDENTITY(1,1) PRIMARY KEY,
KitapID INT NOT NULL,
UyeID INT NOT NULL,
OduncTarihi DATE NOT NULL DEFAULT GETDATE(),
SonTeslimTarihi DATE NOT NULL,
IadeTarihi DATE NULL, -- NULL ise kitap henüz iade edilmemiştir
CONSTRAINT FK_Oduncler_Kitaplar FOREIGN KEY (KitapID) REFERENCES Kitaplar(KitapID),
CONSTRAINT FK_Oduncler_Uyeler FOREIGN KEY (UyeID) REFERENCES Uyeler(UyeID),
CONSTRAINT CK_Oduncler_Tarih CHECK (SonTeslimTarihi >= OduncTarihi)
);
GOThe constraints we used
Constraint | What it does | Example |
PRIMARY KEY | Uniquely identifies each row | KitapID |
FOREIGN KEY | Links to a row in another table | Kitaplar.KategoriID |
UNIQUE | Prevents duplicate values | ISBN, Eposta |
NOT NULL | Prevents empty values | KitapAdi |
DEFAULT | Fills in a value when none is given | KayitTarihi = today |
CHECK | Rejects values that break a rule | StokAdedi >= 0 |
To store Turkish characters (ç, ğ, ı, ö, ş, ü) correctly we use NVARCHAR columns and the N'...' prefix for text values. Table and column names do not use Turkish characters.
Step 3: Inserting Data (INSERT)
All examples in the following posts are based on this data. To make the rows get the same IDs every time, we set the IDs ourselves with SET IDENTITY_INSERT ... ON. Normally you do not insert values into IDENTITY columns; SQL Server generates them.
Categories and authors
SET IDENTITY_INSERT Kategoriler ON;
INSERT INTO Kategoriler (KategoriID, KategoriAdi) VALUES
(1, N'Roman'), (2, N'Bilim'), (3, N'Tarih'), (4, N'Şiir'), (5, N'Çocuk'),
(6, N'Felsefe'), (7, N'Psikoloji'), (8, N'Biyografi'), (9, N'Bilim Kurgu'), (10, N'Polisiye');
SET IDENTITY_INSERT Kategoriler OFF;
GO
SET IDENTITY_INSERT Yazarlar ON;
INSERT INTO Yazarlar (YazarID, Ad, Soyad, Ulke) VALUES
(1, N'Sabahattin', N'Ali', N'Türkiye'),
(2, N'Yaşar', N'Kemal', N'Türkiye'),
(3, N'Orhan', N'Pamuk', N'Türkiye'),
(4, N'Stephen', N'Hawking', N'İngiltere'),
(5, N'Leonard', N'Mlodinow', N'ABD'),
(6, N'İlber', N'Ortaylı', N'Türkiye'),
(7, N'Nazım', N'Hikmet', N'Türkiye'),
(8, N'Antoine', N'de Saint-Exupéry', N'Fransa'),
(9, N'Fyodor', N'Dostoyevski', N'Rusya'),
(10, N'George', N'Orwell', N'İngiltere'),
(11, N'Agatha', N'Christie', N'İngiltere'),
(12, N'Isaac', N'Asimov', N'ABD'),
(13, N'Ahmet Hamdi', N'Tanpınar', N'Türkiye'),
(14, N'Platon', N'', N'Yunanistan'),
(15, N'Carl', N'Sagan', N'ABD');
SET IDENTITY_INSERT Yazarlar OFF;
GOBooks and the book-author links
SET IDENTITY_INSERT Kitaplar ON;
INSERT INTO Kitaplar (KitapID, ISBN, KitapAdi, YayinYili, SayfaSayisi, StokAdedi, KategoriID) VALUES
(1, '9780000000011', N'Kürk Mantolu Madonna', 1943, 160, 3, 1),
(2, '9780000000028', N'İnce Memed', 1955, 436, 2, 1),
(3, '9780000000035', N'Kırmızı Saçlı Kadın', 2016, 204, 1, 1),
(4, '9780000000042', N'Zamanın Kısa Tarihi', 1988, 256, 2, 2),
(5, '9780000000059', N'Zamanın Daha Kısa Tarihi', 2005, 176, 1, 2),
(6, '9780000000066', N'Osmanlı''yı Yeniden Keşfetmek', 2006, 296, 2, 3),
(7, '9780000000073', N'Memleketimden İnsan Manzaraları', 1966, 480, 1, 4),
(8, '9780000000080', N'Küçük Prens', 1943, 112, 4, 5),
(9, '9780000000097', N'Benim Adım Kırmızı', 1998, 472, 2, 1),
(10, '9780000000103', N'Suç ve Ceza', 1866, 687, 3, 1),
(11, '9780000000110', N'1984', 1949, 352, 3, 9),
(12, '9780000000127', N'Hayvan Çiftliği', 1945, 152, 2, 1),
(13, '9780000000134', N'Doğu Ekspresinde Cinayet', 1934, 256, 2, 10),
(14, '9780000000141', N'Ben, Robot', 1950, 272, 1, 9),
(15, '9780000000158', N'Saatleri Ayarlama Enstitüsü', 1961, 382, 2, 1),
(16, '9780000000165', N'Devlet', NULL, 400, 1, 6),
(17, '9780000000172', N'Kozmos', 1980, 396, 2, 2),
(18, '9780000000189', N'Huzur', 1949, 432, 1, 1);
SET IDENTITY_INSERT Kitaplar OFF;
GO
-- Kitap 5'in iki yazarı var (N:M ilişki örneği)
INSERT INTO KitapYazar (KitapID, YazarID) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 3),
(10, 9), (11, 10), (12, 10), (13, 11), (14, 12), (15, 13), (16, 14), (17, 15), (18, 13);
GOIf a text contains a single quote, write it twice: N'Osmanlı''yı Yeniden Keşfetmek'. We deliberately stored NULL as the publication year of the book "Devlet"; we will see how NULL values behave in queries in the next posts.
Members and loans
SET IDENTITY_INSERT Uyeler ON;
INSERT INTO Uyeler (UyeID, Ad, Soyad, Eposta, Telefon, KayitTarihi) VALUES
(1, N'Ayşe', N'Yılmaz', N'ayse.yilmaz@okul.edu.tr', '05321112233', '2026-09-08'),
(2, N'Mehmet', N'Demir', N'mehmet.demir@okul.edu.tr', '05332223344', '2026-09-08'),
(3, N'Zeynep', N'Kaya', N'zeynep.kaya@okul.edu.tr', NULL, '2026-09-10'),
(4, N'Can', N'Öztürk', N'can.ozturk@okul.edu.tr', '05443334455', '2026-09-15'),
(5, N'Elif', N'Şahin', NULL, '05554445566', '2026-09-20'),
(6, N'Burak', N'Çelik', N'burak.celik@okul.edu.tr', '05065556677', '2026-10-01'),
(7, N'Selin', N'Arslan', N'selin.arslan@okul.edu.tr', '05376667788', '2026-09-11'),
(8, N'Emre', N'Koç', N'emre.koc@okul.edu.tr', '05427778899', '2026-09-12'),
(9, N'Deniz', N'Aydın', N'deniz.aydin@okul.edu.tr', NULL, '2026-09-18'),
(10, N'Merve', N'Yıldız', N'merve.yildiz@okul.edu.tr', '05318889900', '2026-09-22'),
(11, N'Oğuz', N'Kurt', N'oguz.kurt@okul.edu.tr', '05459990011', '2026-09-25'),
(12, N'Ece', N'Polat', N'ece.polat@okul.edu.tr', '05360001122', '2026-10-05');
SET IDENTITY_INSERT Uyeler OFF;
GO
SET IDENTITY_INSERT Oduncler ON;
INSERT INTO Oduncler (OduncID, KitapID, UyeID, OduncTarihi, SonTeslimTarihi, IadeTarihi) VALUES
(1, 1, 1, '2026-09-10', '2026-09-24', '2026-09-22'),
(2, 8, 1, '2026-09-25', '2026-10-09', NULL),
(3, 2, 2, '2026-09-12', '2026-09-26', '2026-09-30'),
(4, 4, 2, '2026-09-28', '2026-10-12', NULL),
(5, 1, 3, '2026-09-15', '2026-09-29', NULL),
(6, 6, 3, '2026-09-16', '2026-09-30', '2026-09-29'),
(7, 9, 4, '2026-09-18', '2026-10-02', NULL),
(8, 5, 5, '2026-09-21', '2026-10-05', '2026-10-03'),
(9, 1, 5, '2026-10-01', '2026-10-15', NULL),
(10, 10, 7, '2026-09-22', '2026-10-06', '2026-10-05'),
(11, 11, 8, '2026-09-24', '2026-10-08', NULL),
(12, 13, 9, '2026-09-29', '2026-10-13', NULL),
(13, 11, 10, '2026-10-02', '2026-10-16', NULL),
(14, 15, 11, '2026-10-05', '2026-10-19', NULL),
(15, 12, 7, '2026-10-07', '2026-10-21', NULL),
(16, 17, 4, '2026-10-08', '2026-10-22', NULL);
SET IDENTITY_INSERT Oduncler OFF;
GOBecause the e-mail column is UNIQUE, SQL Server allows only one NULL in it; that is why Elif is the only member without an e-mail. Burak and Ece have never borrowed a book. These rows will be useful later in the LEFT JOIN examples.
Let's check that everything is in place:
SELECT N'Kategoriler' AS Tablo, COUNT(*) AS KayitSayisi FROM Kategoriler
UNION ALL SELECT N'Yazarlar', COUNT(*) FROM Yazarlar
UNION ALL SELECT N'Kitaplar', COUNT(*) FROM Kitaplar
UNION ALL SELECT N'KitapYazar', COUNT(*) FROM KitapYazar
UNION ALL SELECT N'Uyeler', COUNT(*) FROM Uyeler
UNION ALL SELECT N'Oduncler', COUNT(*) FROM Oduncler;
-- Beklenen: 10, 15, 18, 19, 12, 16Step 4: Updating Data (UPDATE)
-- Zeynep, Kürk Mantolu Madonna'yı bugün iade etti
UPDATE Oduncler
SET IadeTarihi = CAST(GETDATE() AS DATE)
WHERE UyeID = 3 AND KitapID = 1 AND IadeTarihi IS NULL;
-- Küçük Prens'in stoğu 2 adet artırıldı
UPDATE Kitaplar
SET StokAdedi = StokAdedi + 2
WHERE KitapAdi = N'Küçük Prens';Step 5: Deleting Data (DELETE)
-- Deneme amaçlı bir üye ekleyip silelim
INSERT INTO Uyeler (Ad, Soyad, Eposta) VALUES (N'Deneme', N'Üye', N'deneme@okul.edu.tr');
DELETE FROM Uyeler
WHERE Eposta = N'deneme@okul.edu.tr';Warning: If you forget the WHERE clause in UPDATE or DELETE, ALL rows of the table are affected. It is a good habit to run a SELECT with the same WHERE first to see which rows will change.
Steps 4 and 5 changed the data. To get the same results as the next posts, simply restore the data by running 01_Veri_Ekleme.sql from GitHub again.
Step 6: Testing the Constraints
The commands below fail on purpose. Run them one by one, read the error messages and find out which constraint was triggered.
-- 1) UNIQUE: aynı ISBN ile ikinci kitap (Msg 2627)
INSERT INTO Kitaplar (ISBN, KitapAdi, KategoriID) VALUES ('9780000000011', N'Kopya Kitap', 1);
-- 2) FOREIGN KEY: olmayan kategori (Msg 547)
INSERT INTO Kitaplar (ISBN, KitapAdi, KategoriID) VALUES ('9780000000999', N'Yeni Kitap', 99);
-- 3) CHECK: negatif stok (Msg 547)
UPDATE Kitaplar SET StokAdedi = -1 WHERE KitapID = 1;
-- 4) FOREIGN KEY: ödünç kaydı olan üyeyi silmek (Msg 547)
DELETE FROM Uyeler WHERE UyeID = 1;
-- 5) NOT NULL: adı olmayan yazar (Msg 515)
INSERT INTO Yazarlar (Soyad) VALUES (N'İsimsiz');What's Next?
Our database is ready. In the next post we will filter data with WHERE: comparison operators, BETWEEN with dates and text search with LIKE.
Video Lessons
You can find the video lessons for this topic in my YouTube playlist (in Turkish).



Comments