creation.sql

CREATE DATABASE KargoDb
	ON PRIMARY	(
					NAME='KargoDb',
					FILENAME = 'C:\database\KargoDb.mdf',
					SIZE = 10MB,
					MAXSIZE = 250MB,
					FILEGROWTH = 10MB
)
	LOG ON		(
				NAME = 'KargoLog',
				FILENAME = 'C:\database\KargoLog.ldf' ,
				SIZE = 2MB,
				MAXSIZE = 50MB,
				FILEGROWTH = 1MB
				)
GO

USE KargoDb
GO

CREATE TABLE tblUrunKategorisi
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Kg_Fiyat DECIMAL NOT NULL,
	Kategori_Tipi VARCHAR(100) NOT NULL,
	Desi_Fiyat DECIMAL NOT NULL,
)
GO

CREATE TABLE tblMusteri
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Ad VARCHAR(50) NOT NULL,
	Soyad VARCHAR(50) NOT NULL,
	Mail VARCHAR(100) NOT NULL CONSTRAINT checkMailMusteri Check (Mail Like '%@%.%'),
	Dogum_Tarihi DATE NOT NULL,
	Cinsiyet VARCHAR(15) NOT NULL,
	Telno VARCHAR(16) NOT NULL CONSTRAINT checkTelMusteri Check (Telno Like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
	TC VARCHAR(11) UNIQUE NOT NULL CONSTRAINT checkTCMusteri Check (TC Like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

)
GO

CREATE TABLE tblUlke
(
	ID TINYINT IDENTITY(1,1) PRIMARY KEY,
	Ulke VARCHAR(100) NOT NULL,
)
GO

CREATE TABLE tblSehir
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Sehir VARCHAR(100) NOT NULL,
	UlkeId TINYINT FOREIGN KEY REFERENCES tblUlke(ID)
)

GO

CREATE TABLE tblIlce
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Ilce VARCHAR(100) NOT NULL,
	SehirId INT FOREIGN KEY REFERENCES tblSehir(ID)
)
GO

CREATE TABLE tblMahalle
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Mahalle VARCHAR(100) NOT NULL,
	IlceId INT FOREIGN KEY REFERENCES tblIlce(ID)
)
GO

CREATE TABLE tblAdres
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Postakodu VARCHAR(7) NOT NULL,
	Acik_adres VARCHAR(500) NOT NULL,
	UlkeId TINYINT FOREIGN KEY REFERENCES tblUlke(ID) NOT NULL,
	SehirId INT FOREIGN KEY REFERENCES tblSehir(ID) NOT NULL,
	IlceId INT FOREIGN KEY REFERENCES tblIlce(ID) NOT NULL,
	MahalleId INT FOREIGN KEY REFERENCES tblMahalle(ID) NOT NULL,
	MusteriId INT FOREIGN KEY REFERENCES tblMusteri(ID),
)
GO

CREATE TABLE tblSube
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Sube_Kodu VARCHAR(50) UNIQUE NOT NULL,
	Telno VARCHAR(16) NOT NULL CONSTRAINT checkTelSube Check (Telno Like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
	AdresId INT FOREIGN KEY REFERENCES tblAdres(ID) NOT NULL
)
GO

CREATE TABLE tblCalisan
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Ad VARCHAR(50) NOT NULL,
	Soyad VARCHAR(50) NOT NULL,
	Mail VARCHAR(100) NOT NULL CONSTRAINT checkMailCalisan Check (Mail Like '%@%.%'),
	Dogum_Tarihi DATE NOT NULL,
	Cinsiyet VARCHAR(15) NOT NULL,
	Telno VARCHAR(16) NOT NULL CONSTRAINT checkTelCalisan Check (Telno Like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
	TC VARCHAR(11) UNIQUE NOT NULL CONSTRAINT checkTCCalisan Check (TC Like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
	SubeId INT FOREIGN KEY REFERENCES tblSube(ID) NOT NULL
)
GO

CREATE TABLE tblArac
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Plaka VARCHAR(10) NOT NULL
)
GO

CREATE TABLE tblOdemeTipi
(
	ID TINYINT IDENTITY(1,1) PRIMARY KEY,
	Tip VARCHAR(50) NOT NULL
)

GO

CREATE TABLE tblKargoDurumu
(
	ID TINYINT IDENTITY(1,1) PRIMARY KEY,
	Durum VARCHAR(50) NOT NULL
)
GO

CREATE TABLE tblGeridonut
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Tarih DATE NOT NULL,
	Icerik VARCHAR(500) NOT NULL,
	MusteriId INT FOREIGN KEY REFERENCES tblMusteri(Id) NOT NULL

)

GO

CREATE TABLE tblKargo
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Teslim_Alým_Tarihi DATE NOT NULL DEFAULT GETDATE(),
	Teslim_Edilme_Tarihi DATE NOT NULL,
	Barkod_Numarasý VARCHAR(200) NOT NULL,
	Toplam_Ucret INT NOT NULL,
	CikisAdresId INT FOREIGN KEY REFERENCES tblAdres(ID) NOT NULL,
	VarisAdresId INT FOREIGN KEY REFERENCES tblAdres(ID) NOT NULL,
	OdemeTipiId TINYINT FOREIGN KEY REFERENCES tblOdemeTipi(ID) NOT NULL,
	TeslimAlanCalisanId INT FOREIGN KEY REFERENCES tblCalisan(ID) NOT NULL,
	VarisSubeId INT FOREIGN KEY REFERENCES tblSube(ID) NOT NULL,
	AlisSubeId INT FOREIGN KEY REFERENCES tblSube(ID) NOT NULL,
	KargoSonDurumId TINYINT FOREIGN KEY REFERENCES tblKargoDurumu(ID) NOT NULL,
	TeslimAlanMusteriId INT FOREIGN KEY REFERENCES tblMusteri(ID) NOT NULL,
	GönderenMusteriId INT FOREIGN KEY REFERENCES tblMusteri(ID) NOT NULL,
	GeriDonutId INT FOREIGN KEY REFERENCES tblGeriDonut(ID)
)

GO

CREATE TABLE tblUrun
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Kg DECIMAL NOT NULL,
	En DECIMAL NOT NULL,
	Fiyat DECIMAL NOT NULL,
	Desi DECIMAL NOT NULL,
	Boy DECIMAL NOT NULL,
	Yukseklik DECIMAL NOT NULL,
	UrunKategoriId INT FOREIGN KEY REFERENCES tblUrunKategorisi(ID) NOT NULL,
	KargoId INT FOREIGN KEY REFERENCES tblKargo(Id)

)

GO 

CREATE TABLE tblKargoHareketleri
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	CikisTarihi DATE NOT NULL DEFAULT GETDATE(),
	VarisTarihi DATE NOT NULL,
	CikisSaati TIME,
	VarisSaati TIME,
	AracId INT FOREIGN KEY REFERENCES tblArac(Id),
	VarisSubeId INT FOREIGN KEY REFERENCES tblSube(Id),
	CikisSubeId INT FOREIGN KEY REFERENCES tblSube(Id),
	KargoDurumId TINYINT FOREIGN KEY REFERENCES tblKargoDurumu(Id),
	KargoId INT FOREIGN KEY REFERENCES tblKargo(Id),
)

GO