use master
go
if exists (select name from sys.databases where name = 'CVliteDB')
begin
alter database CVliteDB set single_user with rollback immediate -- veritabanı bağlantısını koparmak için özel sorgu
drop database CVliteDB -- veritabanını silen esas sorgu
end
go
create database CVliteDB
go
USE [CVliteDB]
GO
CREATE TABLE [dbo].[Ulke](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Adi] [varchar](100) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sehir](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UlkeId] [int] NOT NULL,
[Adi] [varchar](150) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Kisi](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UlkeId] [int] NULL,
[SehirId] [int] NULL,
[Adi] [varchar](50) NOT NULL,
[Soyadi] [varchar](50) NOT NULL,
[DogumTarihi] [date] NOT NULL,
[Cinsiyeti] int NOT NULL,
[KimlikNo] [char](11) NOT NULL,
[Biyografi] [text] NULL,
CONSTRAINT [PK_Kisi] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Is](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Adi] [varchar](300) NOT NULL,
[Aciklamasi] [varchar](max) NULL,
[HaftalikCalismaSaati] float NULL
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[KisiIs](
[KisiId] [int] NOT NULL,
[IsId] [int] NOT NULL,
[BaslangicTarihi] [datetime] NOT NULL,
[BitisTarihi] [datetime] NULL,
CONSTRAINT [PK_KisiIs] PRIMARY KEY CLUSTERED
(
[KisiId] ASC,
[IsId] ASC,
[BaslangicTarihi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Kisi] WITH CHECK ADD CONSTRAINT [FK_Kisi_Sehir] FOREIGN KEY([SehirId])
REFERENCES [dbo].[Sehir] ([Id])
GO
ALTER TABLE [dbo].[Kisi] CHECK CONSTRAINT [FK_Kisi_Sehir]
GO
ALTER TABLE [dbo].[Kisi] WITH CHECK ADD CONSTRAINT [FK_Kisi_Ulke] FOREIGN KEY([UlkeId])
REFERENCES [dbo].[Ulke] ([Id])
GO
ALTER TABLE [dbo].[Kisi] CHECK CONSTRAINT [FK_Kisi_Ulke]
GO
ALTER TABLE [dbo].[KisiIs] WITH CHECK ADD CONSTRAINT [FK_KisiIs_Is] FOREIGN KEY([IsId])
REFERENCES [dbo].[Is] ([Id])
GO
ALTER TABLE [dbo].[KisiIs] CHECK CONSTRAINT [FK_KisiIs_Is]
GO
ALTER TABLE [dbo].[KisiIs] WITH CHECK ADD CONSTRAINT [FK_KisiIs_Kisi] FOREIGN KEY([KisiId])
REFERENCES [dbo].[Kisi] ([Id])
GO
ALTER TABLE [dbo].[KisiIs] CHECK CONSTRAINT [FK_KisiIs_Kisi]
GO
ALTER TABLE [dbo].[Sehir] WITH CHECK ADD CONSTRAINT [FK_Sehir_Ulke] FOREIGN KEY([UlkeId])
REFERENCES [dbo].[Ulke] ([Id])
GO
ALTER TABLE [dbo].[Sehir] CHECK CONSTRAINT [FK_Sehir_Ulke]
GO
SET IDENTITY_INSERT [dbo].[Ulke] ON
GO
INSERT [dbo].[Ulke] ([Id], [Adi]) VALUES (1, N'Türkiye')
GO
INSERT [dbo].[Ulke] ([Id], [Adi]) VALUES (3, N'Amerika Birleşik Devletleri')
GO
SET IDENTITY_INSERT [dbo].[Ulke] OFF
GO
SET IDENTITY_INSERT [dbo].[Sehir] ON
GO
INSERT [dbo].[Sehir] ([Id], [UlkeId], [Adi]) VALUES (1, 1, N'Ankara')
GO
INSERT [dbo].[Sehir] ([Id], [UlkeId], [Adi]) VALUES (2, 1, N'İstanbul')
GO
INSERT [dbo].[Sehir] ([Id], [UlkeId], [Adi]) VALUES (5, 3, N'New York')
GO
SET IDENTITY_INSERT [dbo].[Sehir] OFF
GO
SET IDENTITY_INSERT [dbo].[Kisi] ON
GO
INSERT [dbo].[Kisi] ([Id], [UlkeId], [SehirId], [Adi], [Soyadi], [DogumTarihi], [Cinsiyeti], [KimlikNo], [Biyografi]) VALUES (1, 1, 1, N'Çağıl', N'Alsaç', CAST(N'1980-01-01' AS Date), 1, N'12345678901', NULL)
GO
INSERT [dbo].[Kisi] ([Id], [UlkeId], [SehirId], [Adi], [Soyadi], [DogumTarihi], [Cinsiyeti], [KimlikNo], [Biyografi]) VALUES (2, NULL, NULL, N'Luna', N'Alsaç', CAST(N'2022-01-01' AS Date), 2, N'98765432109', N'Çağıl Alsaç''ın köpeği.')
GO
SET IDENTITY_INSERT [dbo].[Kisi] OFF
GO
SET IDENTITY_INSERT [dbo].[Is] ON
GO
INSERT [dbo].[Is] ([Id], [Adi], [Aciklamasi]) VALUES (1, N'Yazılım Mühendisliği', NULL)
GO
INSERT [dbo].[Is] ([Id], [Adi], [Aciklamasi]) VALUES (2, N'Evcil Hayvanlık', N'Ekmek elden su gölden.')
GO
SET IDENTITY_INSERT [dbo].[Is] OFF
GO
INSERT [dbo].[KisiIs] ([KisiId], [IsId], [BaslangicTarihi], [BitisTarihi]) VALUES (1, 1, CAST(N'2023-08-05T00:00:00.000' AS DateTime), CAST(N'2023-09-05T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KisiIs] ([KisiId], [IsId], [BaslangicTarihi], [BitisTarihi]) VALUES (1, 1, CAST(N'2023-10-01T00:00:00.000' AS DateTime), CAST(N'2023-11-30T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KisiIs] ([KisiId], [IsId], [BaslangicTarihi], [BitisTarihi]) VALUES (2, 2, CAST(N'2023-08-01T00:00:00.000' AS DateTime), NULL)
GO