top of page

Learning MSSQL Series 01: Building the Library Database

Writer: SEVDANUR GENC
SEVDANUR GENC
5 days ago
7 min read

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


ER diagram of the library database
Library ER diagram: 6 entities, 4 relationships (table names are in Turkish)

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;
GO


Step 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()
);
GO


Tables 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)
);
GO


The 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;
GO


Books 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);
GO

If 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;
GO

Because 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, 16


Step 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


©2026 by Sevdanur Genc

bottom of page