E-Commerce Database

MSSQL Server üzerinde 3NF normalize edilmiş e-commerce veritabanı. Multi-table relational schema, audit log, transactional procedure, table-valued functions, advanced trigger pattern'leri içerir. İzmir Ekonomi Üniversitesi MBP 104 Visual Programming 2 dersi projesi.

Author: İlker Efe İpek Database name: ilkerefeipek_20232425024 DBMS: Microsoft SQL Server (T-SQL)

---

İçindekiler

• Özellikler
• Kurulum
• Repo Yapısı
• Schema Genel Bakış
• Functions
• Stored Procedures
• Triggers
• Sample Queries
• Tasarım Dokümantasyonu

---

Özellikler

• 12 tablo (3NF normalize)
• 4 fonksiyon — 2 scalar + 2 table-valued
• 5 stored procedure — 1 tanesi transactional (TRY/CATCH + ROLLBACK)
• 4 trigger — audit log (Users + Products), stock decrement, default address hint
• 8 non-clustered index — sık WHERE/JOIN kolonlarında, INCLUDE + filtered indexes
• 13+ constraint — UNIQUE (filtered), CHECK (VAT rates, rating range, no self-message), DEFAULT
• Soft delete pattern (Users.Active, Products.Active, Categories.Active)
• Snapshot pattern — OrderDetails historical price/VAT/discount
• JSON-based audit log — FOR JSON PATH ile değişiklik snapshot'ı
• Idempotent script — DROP IF EXISTS her create öncesi
• Sample data — 200+ satır demo veri (TR context)
• 6 advanced query dosyası — JOIN, CTE, recursive CTE, window function, ROLLUP/CUBE, subquery, business reports

---

Kurulum

Ön koşul

• Microsoft SQL Server 2019+ (Express, Developer, veya Standard)
• SQL Server Management Studio (SSMS) veya Azure Data Studio

Adımlar

1. Bu repo'yu klonlayın:

   git clone https://github.com/ilkerefeipek/proje2.git
   cd proje2

2. SSMS açın, sırasıyla şu script'leri çalıştırın:

   sql/99-full-script.sql       — DB + tüm tablo/constraint/index/fn/proc/trigger
   sample-data/seed.sql         — Demo veri (200+ satır)
   

   99-full-script.sql idempotent — birden fazla çalıştırılabilir, mevcut DB silinip yeniden oluşturulur.

3. Örnek query'leri deneyin:

   queries/01-basic-joins.sql
   queries/02-cte-and-recursion.sql
   queries/03-window-functions.sql
   queries/04-aggregations.sql
   queries/05-subqueries.sql
   queries/06-business-reports.sql
   

Hızlı doğrulama

Tüm objelerin oluştuğunu kontrol etmek için:

USE [ilkerefeipek_20232425024];
SELECT type_desc, COUNT(*) AS Count
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc;

Beklenen:

• USER_TABLE: 12
• SQL_SCALAR_FUNCTION: 2
• SQL_INLINE_TABLE_VALUED_FUNCTION: 1
• SQL_TABLE_VALUED_FUNCTION: 1
• SQL_STORED_PROCEDURE: 5
• SQL_TRIGGER: 4
• DEFAULT_CONSTRAINT, CHECK_CONSTRAINT, FOREIGN_KEY_CONSTRAINT, PRIMARY_KEY_CONSTRAINT, UNIQUE_CONSTRAINT (NCI ile)

---

Repo Yapısı

proje2/
├── README.md                          # bu dosya
├── .gitignore                         # *.bak, *.mdf, *.ldf
├── docs/
│   ├── schema-design.md              # tasarım kararları, FK cascade policy, audit pattern
│   ├── normalization-steps.md        # 1NF → 2NF → 3NF dönüşüm
│   └── naming-convention.md          # tablo/constraint/index naming kuralları
├── sql/
│   ├── 00-create-database.sql        # DB drop + create (TR collation)
│   ├── 01-tables.sql                 # 12 tablo (FK dependency sırası)
│   ├── 02-constraints.sql            # UNIQUE (filtered) + CHECK constraints
│   ├── 03-indexes.sql                # 8 NCI (INCLUDE + filtered indexes)
│   ├── 04-functions.sql              # 2 scalar + 2 TVF
│   ├── 05-procedures.sql             # 5 procedure (1 transactional)
│   ├── 06-triggers.sql               # 4 trigger
│   └── 99-full-script.sql            # tüm yukarısı concatenated (idempotent)
├── sample-data/
│   └── seed.sql                      # 200+ satır demo veri (TR context)
└── queries/
    ├── 01-basic-joins.sql            # INNER, LEFT, NULL check
    ├── 02-cte-and-recursion.sql      # CTE + recursive CTE
    ├── 03-window-functions.sql       # ROW_NUMBER, RANK, LAG, NTILE
    ├── 04-aggregations.sql           # GROUP BY, HAVING, ROLLUP, CUBE, GROUPING SETS
    ├── 05-subqueries.sql             # Correlated, EXISTS, IN, derived table
    └── 06-business-reports.sql       # Top products, RFM, status pipeline, audit trail

---

Schema Genel Bakış

Categories ─┐ self-ref (hierarchy)
            │
Suppliers ──┤
            ├─→ Products
Statuses ───┤      │
            │      │
            │      ↓
Users ──┬─→ Orders ─→ OrderDetails
        ├─→ Addresses        │
        ├─→ UserTelephones   │
        ├─→ Comments ←───────┘ (ProductID FK)
        └─→ Messages (sender/receiver self)

AuditLog (no FK — cross-cutting history table)

Detaylı tasarım: docs/schema-design.md

Tablolar

Tablo	Sorumluluk
Users	Kullanıcı (admin / customer / support), soft delete
UserTelephones	Kullanıcı telefon numaraları (1:N)
Addresses	Teslimat adresleri (1:N)
Categories	Ürün kategorileri, self-ref hierarchy
Suppliers	Tedarikçi bilgileri
Products	Katalog ürünleri, soft delete, KDV/indirim
Statuses	Sipariş lifecycle lookup
Orders	Sipariş header
OrderDetails	Sipariş kalemleri (snapshot price/VAT/discount)
Comments	Ürün yorumları + rating (1-5)
Messages	Kullanıcı arası mesajlaşma
AuditLog	Cross-cutting audit log (JSON snapshot)

---

Functions

Scalar

Function	İmza	Açıklama
fn_KdvDahilFiyat	(price DECIMAL, vat TINYINT) → DECIMAL	KDV dahil fiyat hesabı
fn_OrderTotal	(orderId INT) → DECIMAL	Sipariş toplamı (discount + VAT applied)

Table-Valued

Function	İmza	Tip	Açıklama
fn_UserOrderHistory	(userId INT) → TABLE	Inline TVF	Kullanıcı sipariş geçmişi özeti
fn_CategoryProductSummary	(categoryId INT) → @Result TABLE	Multi-statement TVF	Kategoriye göre ürün + stock status

Kullanım örneği

SELECT dbo.fn_KdvDahilFiyat(1000.00, 20);  -- → 1200.00

SELECT * FROM dbo.fn_UserOrderHistory(4);

SELECT * FROM dbo.fn_CategoryProductSummary(1);

---

Stored Procedures

Procedure	Açıklama
sp_AddUser	Validation + insert. Duplicate email kontrolü
sp_DeleteUser	Soft delete (Active = 0) — trigger audit kaydı oluşturur
sp_UpdateUserStatus	Admin: role + active toggle
sp_CreateOrderWithDetails	Transactional order + details (TRY/CATCH + ROLLBACK + XACT_ABORT). JSON parametre ile order items
sp_LowStockReport	Stok düşük ürünler (threshold parametreli)

Kullanım örneği

-- Yeni user
DECLARE @NewID INT;
EXEC sp_AddUser
    @UserName     = N'Test',
    @UserSurname  = N'Kullanıcı',
    @EmailAddress = N'test@example.com',
    @Password     = N'$2b$12$hash...',
    @NewUserID    = @NewID OUTPUT;

-- Transactional sipariş
DECLARE @NewOrder INT;
EXEC sp_CreateOrderWithDetails
    @UserID     = 4,
    @AddressID  = 3,
    @StatusID   = 1,
    @OrderItems = N'[{"ProductID":1,"Quantity":2},{"ProductID":7,"Quantity":1}]',
    @NewOrderID = @NewOrder OUTPUT;
SELECT @NewOrder;

---

Triggers

Trigger	Tablo	Olay	Etki
trg_DecrementStock	OrderDetails	AFTER INSERT	Products.Stock azaltma
trg_UserAudit	Users	AFTER INSERT/UPDATE/DELETE	AuditLog JSON snapshot
trg_ProductsAudit	Products	AFTER INSERT/UPDATE/DELETE	AuditLog JSON snapshot (stock-only update'leri filtrelenir)
trg_AutoDefaultAddress	Users	AFTER INSERT	AuditLog reminder entry (uygulama katmanı address eklemesi gerekiyor)

---

Sample Queries

queries/ altında 6 dosya, her biri farklı bir SQL teknik gösterir:

1. 01-basic-joins.sql — INNER, LEFT, RIGHT, NULL check ile orphan detection
2. 02-cte-and-recursion.sql — CTE, recursive CTE (Categories hierarchy)
3. 03-window-functions.sql — ROW_NUMBER, RANK, NTILE, LAG, running totals
4. 04-aggregations.sql — GROUP BY, HAVING, ROLLUP, CUBE, GROUPING SETS
5. 05-subqueries.sql — Correlated, EXISTS, NOT EXISTS, IN, derived table
6. 06-business-reports.sql — Top products, RFM segmentation, status funnel, audit trail

---

Tasarım Dokümantasyonu

• Schema Design Rationale — neden bu yapı
• Normalization Steps — 1NF → 2NF → 3NF dönüşüm
• Naming Convention — tablo/constraint/index isimlendirme

---

Lisans

Akademik amaçlı kullanım — İzmir Ekonomi Üniversitesi MBP 104 dersi.