YIFIS/BD.sql

USE [gr606_alvvi]
GO
/****** Object:  Table [dbo].[Jump]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jump](
	[IdJump] [int] IDENTITY(1,1) NOT NULL,
	[IdJumpUserList] [int] NOT NULL,
	[IdInstructor] [int] NOT NULL,
	[DataJump] [date] NOT NULL,
	[TimeJump] [time](7) NOT NULL,
	[IdParachute] [int] NULL,
 CONSTRAINT [PK_Jump] PRIMARY KEY CLUSTERED 
(
	[IdJump] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[JumpUserList]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JumpUserList](
	[IdJumpUserList] [int] IDENTITY(1,1) NOT NULL,
	[IdUser] [int] NOT NULL,
	[IdJump] [int] NOT NULL,
 CONSTRAINT [PK_JumpUserList] PRIMARY KEY CLUSTERED 
(
	[IdJumpUserList] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Lessons]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Lessons](
	[IdLessons] [int] IDENTITY(1,1) NOT NULL,
	[NumberLessons] [int] NOT NULL,
	[IdUserList] [int] NOT NULL,
	[DateLessons] [date] NOT NULL,
	[ClassTime] [time](7) NOT NULL,
	[LessonsTopic] [nvarchar](100) NOT NULL,
	[IdTypeLessons] [int] NOT NULL,
	[IdInsrtuctor] [int] NOT NULL,
 CONSTRAINT [PK_Lessons] PRIMARY KEY CLUSTERED 
(
	[IdLessons] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Parachute]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parachute](
	[IdParachute] [int] IDENTITY(1,1) NOT NULL,
	[NomerParashuta] [int] NOT NULL,
	[DateAssemblies] [date] NOT NULL,
	[TimeAssemblies] [time](7) NOT NULL,
	[IdTypeParachute] [int] NOT NULL,
 CONSTRAINT [PK_Parachute] PRIMARY KEY CLUSTERED 
(
	[IdParachute] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PassportData]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PassportData](
	[IdPassportData] [int] IDENTITY(1,1) NOT NULL,
	[Series] [varchar](4) NOT NULL,
	[Number] [nchar](6) NOT NULL,
	[DateOfIssue] [date] NOT NULL,
	[Issued] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_PassportData] PRIMARY KEY CLUSTERED 
(
	[IdPassportData] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Role]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[IdRole] [int] IDENTITY(1,1) NOT NULL,
	[Role] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[IdRole] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TypeLessons]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TypeLessons](
	[IdTypeLessons] [int] IDENTITY(1,1) NOT NULL,
	[TypeLessons] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TypeLessons] PRIMARY KEY CLUSTERED 
(
	[IdTypeLessons] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TypeParachute]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TypeParachute](
	[IdTypeParachute] [int] IDENTITY(1,1) NOT NULL,
	[TypeParachute] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TypeParachute] PRIMARY KEY CLUSTERED 
(
	[IdTypeParachute] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[User]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[IdUser] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Surname] [nvarchar](100) NOT NULL,
	[MiddleName] [nvarchar](100) NOT NULL,
	[TrainingGroup] [int] NOT NULL,
	[DateOfBirth] [nvarchar](100) NOT NULL,
	[Email] [nvarchar](100) NOT NULL,
	[AircraftNumber] [int] NOT NULL,
	[IdParachute] [int] NULL,
	[PhoneNumber] [varchar](12) NOT NULL,
	[IdPassportData] [int] NOT NULL,
	[Login] [nvarchar](100) NOT NULL,
	[Password] [nvarchar](100) NOT NULL,
	[IdRole] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[IdUser] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserList]    Script Date: 03.06.2022 13:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserList](
	[IsUserList] [int] IDENTITY(1,1) NOT NULL,
	[IdUser] [int] NOT NULL,
	[IdLesson] [int] NOT NULL,
 CONSTRAINT [PK_UserList] PRIMARY KEY CLUSTERED 
(
	[IsUserList] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Jump] ON 

INSERT [dbo].[Jump] ([IdJump], [IdJumpUserList], [IdInstructor], [DataJump], [TimeJump], [IdParachute]) VALUES (2, 1, 5, CAST(N'2022-05-31' AS Date), CAST(N'11:11:00' AS Time), 4)
SET IDENTITY_INSERT [dbo].[Jump] OFF
GO
SET IDENTITY_INSERT [dbo].[JumpUserList] ON 

INSERT [dbo].[JumpUserList] ([IdJumpUserList], [IdUser], [IdJump]) VALUES (1, 4, 2)
SET IDENTITY_INSERT [dbo].[JumpUserList] OFF
GO
SET IDENTITY_INSERT [dbo].[Lessons] ON 

INSERT [dbo].[Lessons] ([IdLessons], [NumberLessons], [IdUserList], [DateLessons], [ClassTime], [LessonsTopic], [IdTypeLessons], [IdInsrtuctor]) VALUES (4, 1, 1, CAST(N'2022-06-02' AS Date), CAST(N'11:30:00' AS Time), N'Учение', 1, 4)
SET IDENTITY_INSERT [dbo].[Lessons] OFF
GO
SET IDENTITY_INSERT [dbo].[Parachute] ON 

INSERT [dbo].[Parachute] ([IdParachute], [NomerParashuta], [DateAssemblies], [TimeAssemblies], [IdTypeParachute]) VALUES (4, 1, CAST(N'2022-04-23' AS Date), CAST(N'12:30:11' AS Time), 1)
SET IDENTITY_INSERT [dbo].[Parachute] OFF
GO
SET IDENTITY_INSERT [dbo].[PassportData] ON 

INSERT [dbo].[PassportData] ([IdPassportData], [Series], [Number], [DateOfIssue], [Issued]) VALUES (1, N'1818', N'111111', CAST(N'2018-08-05' AS Date), N'Томск')
SET IDENTITY_INSERT [dbo].[PassportData] OFF
GO
SET IDENTITY_INSERT [dbo].[Role] ON 

INSERT [dbo].[Role] ([IdRole], [Role]) VALUES (1, N'Ученик')
INSERT [dbo].[Role] ([IdRole], [Role]) VALUES (2, N'Инструктор')
SET IDENTITY_INSERT [dbo].[Role] OFF
GO
SET IDENTITY_INSERT [dbo].[TypeLessons] ON 

INSERT [dbo].[TypeLessons] ([IdTypeLessons], [TypeLessons]) VALUES (1, N'Учение')
SET IDENTITY_INSERT [dbo].[TypeLessons] OFF
GO
SET IDENTITY_INSERT [dbo].[TypeParachute] ON 

INSERT [dbo].[TypeParachute] ([IdTypeParachute], [TypeParachute]) VALUES (1, N'Парашут')
SET IDENTITY_INSERT [dbo].[TypeParachute] OFF
GO
SET IDENTITY_INSERT [dbo].[User] ON 

INSERT [dbo].[User] ([IdUser], [Name], [Surname], [MiddleName], [TrainingGroup], [DateOfBirth], [Email], [AircraftNumber], [IdParachute], [PhoneNumber], [IdPassportData], [Login], [Password], [IdRole]) VALUES (4, N'Владисла', N'Александров', N'Викторович', 3, N'08.06.2004', N'vlad2004@bk.ru', 11, 4, N'+78005553535', 1, N'admin', N'admin', 1)
INSERT [dbo].[User] ([IdUser], [Name], [Surname], [MiddleName], [TrainingGroup], [DateOfBirth], [Email], [AircraftNumber], [IdParachute], [PhoneNumber], [IdPassportData], [Login], [Password], [IdRole]) VALUES (5, N'Иванов', N'Иван', N'Иванович', 2, N'18.07.1988', N'instructor@gmail.com', 11, 4, N'+78007348238', 1, N'1', N'1', 2)
SET IDENTITY_INSERT [dbo].[User] OFF
GO
SET IDENTITY_INSERT [dbo].[UserList] ON 

INSERT [dbo].[UserList] ([IsUserList], [IdUser], [IdLesson]) VALUES (1, 4, 1)
SET IDENTITY_INSERT [dbo].[UserList] OFF
GO
ALTER TABLE [dbo].[Jump]  WITH CHECK ADD  CONSTRAINT [FK_Jump_Parachute] FOREIGN KEY([IdParachute])
REFERENCES [dbo].[Parachute] ([IdParachute])
GO
ALTER TABLE [dbo].[Jump] CHECK CONSTRAINT [FK_Jump_Parachute]
GO
ALTER TABLE [dbo].[JumpUserList]  WITH CHECK ADD  CONSTRAINT [FK_JumpUserList_Jump] FOREIGN KEY([IdJump])
REFERENCES [dbo].[Jump] ([IdJump])
GO
ALTER TABLE [dbo].[JumpUserList] CHECK CONSTRAINT [FK_JumpUserList_Jump]
GO
ALTER TABLE [dbo].[JumpUserList]  WITH CHECK ADD  CONSTRAINT [FK_JumpUserList_User] FOREIGN KEY([IdUser])
REFERENCES [dbo].[User] ([IdUser])
GO
ALTER TABLE [dbo].[JumpUserList] CHECK CONSTRAINT [FK_JumpUserList_User]
GO
ALTER TABLE [dbo].[Lessons]  WITH CHECK ADD  CONSTRAINT [FK_Lessons_TypeLessons] FOREIGN KEY([IdTypeLessons])
REFERENCES [dbo].[TypeLessons] ([IdTypeLessons])
GO
ALTER TABLE [dbo].[Lessons] CHECK CONSTRAINT [FK_Lessons_TypeLessons]
GO
ALTER TABLE [dbo].[Lessons]  WITH CHECK ADD  CONSTRAINT [FK_Lessons_User1] FOREIGN KEY([IdInsrtuctor])
REFERENCES [dbo].[User] ([IdUser])
GO
ALTER TABLE [dbo].[Lessons] CHECK CONSTRAINT [FK_Lessons_User1]
GO
ALTER TABLE [dbo].[Lessons]  WITH CHECK ADD  CONSTRAINT [FK_Lessons_UserList] FOREIGN KEY([IdUserList])
REFERENCES [dbo].[UserList] ([IsUserList])
GO
ALTER TABLE [dbo].[Lessons] CHECK CONSTRAINT [FK_Lessons_UserList]
GO
ALTER TABLE [dbo].[Parachute]  WITH CHECK ADD  CONSTRAINT [FK_Parachute_TypeParachute1] FOREIGN KEY([IdTypeParachute])
REFERENCES [dbo].[TypeParachute] ([IdTypeParachute])
GO
ALTER TABLE [dbo].[Parachute] CHECK CONSTRAINT [FK_Parachute_TypeParachute1]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_PassportData] FOREIGN KEY([IdPassportData])
REFERENCES [dbo].[PassportData] ([IdPassportData])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_PassportData]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Role] FOREIGN KEY([IdRole])
REFERENCES [dbo].[Role] ([IdRole])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role]
GO