use master
go
if exists (select name from sys.databases where name = 'CVDB')
begin
alter database CVDB set single_user with rollback immediate -- veritabanı bağlantısını koparmak için özel sorgu
drop database CVDB -- veritabanını silen esas sorgu
end
go
create database CVDB
go
USE [CVDB]
GO
/****** Object: Table [dbo].[Is] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Is](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Adi] [varchar](300) NOT NULL,
[Aciklamasi] [varchar](max) 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
/****** Object: Table [dbo].[Kisi] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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] NULL,
[ErkekMi] [bit] NOT NULL,
[KimlikNo] [char](11) 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
/****** Object: Table [dbo].[KisiIs] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KisiIs](
[KisiId] [int] NOT NULL,
[IsId] [int] NOT NULL,
[BaslangicTarihi] [datetime] NULL,
[BitisTarihi] [datetime] NULL,
CONSTRAINT [PK_KisiIs] PRIMARY KEY CLUSTERED
(
[KisiId] ASC,
[IsId] 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
/****** Object: Table [dbo].[KisiOkul] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KisiOkul](
[KisiId] [int] NOT NULL,
[OkulId] [int] NOT NULL,
[Ortalama] [float] NULL,
CONSTRAINT [PK_KisiOkul] PRIMARY KEY CLUSTERED
(
[KisiId] ASC,
[OkulId] 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
/****** Object: Table [dbo].[Okul] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Okul](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Adi] [varchar](250) 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
/****** Object: Table [dbo].[Sehir] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
/****** Object: Table [dbo].[Ulke] Script Date: 21.06.2023 01:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
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
SET IDENTITY_INSERT [dbo].[Kisi] ON
GO
INSERT [dbo].[Kisi] ([Id], [UlkeId], [SehirId], [Adi], [Soyadi], [DogumTarihi], [ErkekMi], [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], [ErkekMi], [KimlikNo], [Biyografi]) VALUES (2, NULL, NULL, N'Leo', N'Alsaç', NULL, 1, NULL, N'Çağıl Alsaç''ın köpeği.')
GO
SET IDENTITY_INSERT [dbo].[Kisi] OFF
GO
INSERT [dbo].[KisiIs] ([KisiId], [IsId], [BaslangicTarihi], [BitisTarihi]) VALUES (1, 1, CAST(N'2021-09-14T00:00:00.000' AS DateTime), CAST(N'2021-10-14T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KisiIs] ([KisiId], [IsId], [BaslangicTarihi], [BitisTarihi]) VALUES (2, 2, CAST(N'2014-05-15T00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[KisiOkul] ([KisiId], [OkulId], [Ortalama]) VALUES (1, 1, 4)
GO
INSERT [dbo].[KisiOkul] ([KisiId], [OkulId], [Ortalama]) VALUES (1, 2, 3.5)
GO
SET IDENTITY_INSERT [dbo].[Okul] ON
GO
INSERT [dbo].[Okul] ([Id], [Adi]) VALUES (1, N'Bilkent Üniversitesi')
GO
INSERT [dbo].[Okul] ([Id], [Adi]) VALUES (2, N'Başkent Üniversitesi')
GO
SET IDENTITY_INSERT [dbo].[Okul] 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].[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
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].[KisiOkul] WITH CHECK ADD CONSTRAINT [FK_KisiOkul_Kisi] FOREIGN KEY([KisiId])
REFERENCES [dbo].[Kisi] ([Id])
GO
ALTER TABLE [dbo].[KisiOkul] CHECK CONSTRAINT [FK_KisiOkul_Kisi]
GO
ALTER TABLE [dbo].[KisiOkul] WITH CHECK ADD CONSTRAINT [FK_KisiOkul_Okul] FOREIGN KEY([OkulId])
REFERENCES [dbo].[Okul] ([Id])
GO
ALTER TABLE [dbo].[KisiOkul] CHECK CONSTRAINT [FK_KisiOkul_Okul]
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