YiFis/bd.sql

USE [Hospital]
GO
/****** Object:  Table [dbo].[Departament]    Script Date: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Departament](
	[DepartamentId] [int] IDENTITY(1,1) NOT NULL,
	[TitleDepartament] [nvarchar](100) NULL,
 CONSTRAINT [PK_Departament] PRIMARY KEY CLUSTERED 
(
	[DepartamentId] 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].[Doctor]    Script Date: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Doctor](
	[IdDoctor] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](100) NULL,
	[FirstName] [nvarchar](100) NULL,
	[MiddleName] [nvarchar](100) NULL,
	[PostId] [int] NULL,
	[PhoneNumber] [varchar](20) NULL,
	[DepartamentId] [int] NULL,
	[CabinetNumber] [int] NULL,
 CONSTRAINT [PK_Doctor] PRIMARY KEY CLUSTERED 
(
	[IdDoctor] 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].[ListNews]    Script Date: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ListNews](
	[IdNews] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](100) NULL,
	[DiscriptNews] [nvarchar](max) NULL,
	[DatePublication] [date] NULL,
 CONSTRAINT [PK_ListNews] PRIMARY KEY CLUSTERED 
(
	[IdNews] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Patient]    Script Date: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Patient](
	[IdPatient] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](100) NULL,
	[FirstName] [nvarchar](100) NULL,
	[MiddleName] [nvarchar](100) NULL,
	[Gender] [nvarchar](10) NULL,
	[DateofBirth] [date] NULL,
	[Snils] [varchar](15) NULL,
 CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED 
(
	[IdPatient] 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].[Post]    Script Date: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Post](
	[PostId] [int] IDENTITY(1,1) NOT NULL,
	[TitlePost] [nvarchar](100) NULL,
 CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED 
(
	[PostId] 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].[Status]    Script Date: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Status](
	[IdStatus] [int] IDENTITY(1,1) NOT NULL,
	[TitleStatus] [nvarchar](100) NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
	[IdStatus] 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: 23.06.2022 12:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[IdUser] [int] IDENTITY(1,1) NOT NULL,
	[LoginUser] [varchar](100) NULL,
	[PasswordUser] [varchar](100) NULL,
	[IdPatient] [int] NULL,
	[IdDoctor] [int] NULL,
	[IdStatus] [int] 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
SET IDENTITY_INSERT [dbo].[Departament] ON 

INSERT [dbo].[Departament] ([DepartamentId], [TitleDepartament]) VALUES (1, N'Терапевтический корпус')
INSERT [dbo].[Departament] ([DepartamentId], [TitleDepartament]) VALUES (2, N'Санитарный пропускник (санпропускник)')
INSERT [dbo].[Departament] ([DepartamentId], [TitleDepartament]) VALUES (3, N'Хирургический корпус')
INSERT [dbo].[Departament] ([DepartamentId], [TitleDepartament]) VALUES (4, N'Травмпункт')
INSERT [dbo].[Departament] ([DepartamentId], [TitleDepartament]) VALUES (5, N'Клиническое отделение')
SET IDENTITY_INSERT [dbo].[Departament] OFF
GO
SET IDENTITY_INSERT [dbo].[Doctor] ON 

INSERT [dbo].[Doctor] ([IdDoctor], [LastName], [FirstName], [MiddleName], [PostId], [PhoneNumber], [DepartamentId], [CabinetNumber]) VALUES (1, N'Данил', N'Васильев', N'Незнаюкин', 1, N'88005553535', 4, 225)
SET IDENTITY_INSERT [dbo].[Doctor] OFF
GO
SET IDENTITY_INSERT [dbo].[ListNews] ON 

INSERT [dbo].[ListNews] ([IdNews], [Title], [DiscriptNews], [DatePublication]) VALUES (1, N'Первая новость', N'Это тестовая новость для проверки работы', CAST(N'2022-06-22' AS Date))
INSERT [dbo].[ListNews] ([IdNews], [Title], [DiscriptNews], [DatePublication]) VALUES (2, N'Втарая новость', N'Это тестовая новость для проверки работы', CAST(N'2022-06-22' AS Date))
SET IDENTITY_INSERT [dbo].[ListNews] OFF
GO
SET IDENTITY_INSERT [dbo].[Patient] ON 

INSERT [dbo].[Patient] ([IdPatient], [LastName], [FirstName], [MiddleName], [Gender], [DateofBirth], [Snils]) VALUES (1, N'Иванов', N'Иван', N'Иванович', N'Муж', CAST(N'2003-12-02' AS Date), N'12312312312')
INSERT [dbo].[Patient] ([IdPatient], [LastName], [FirstName], [MiddleName], [Gender], [DateofBirth], [Snils]) VALUES (2, N'qwe', N'qweq', N'qwe', N'Муж', CAST(N'2022-06-07' AS Date), N'12312312376')
SET IDENTITY_INSERT [dbo].[Patient] OFF
GO
SET IDENTITY_INSERT [dbo].[Post] ON 

INSERT [dbo].[Post] ([PostId], [TitlePost]) VALUES (1, N'Врач')
INSERT [dbo].[Post] ([PostId], [TitlePost]) VALUES (2, N'Мед-сестра')
INSERT [dbo].[Post] ([PostId], [TitlePost]) VALUES (3, N'Глав-врач')
SET IDENTITY_INSERT [dbo].[Post] OFF
GO
SET IDENTITY_INSERT [dbo].[Status] ON 

INSERT [dbo].[Status] ([IdStatus], [TitleStatus]) VALUES (1, N'Поциент')
INSERT [dbo].[Status] ([IdStatus], [TitleStatus]) VALUES (2, N'Врач')
SET IDENTITY_INSERT [dbo].[Status] OFF
GO
SET IDENTITY_INSERT [dbo].[User] ON 

INSERT [dbo].[User] ([IdUser], [LoginUser], [PasswordUser], [IdPatient], [IdDoctor], [IdStatus]) VALUES (1, N'qwe', N'qwe', 1, NULL, 1)
INSERT [dbo].[User] ([IdUser], [LoginUser], [PasswordUser], [IdPatient], [IdDoctor], [IdStatus]) VALUES (2, N'12', N'12', NULL, 1, 2)
INSERT [dbo].[User] ([IdUser], [LoginUser], [PasswordUser], [IdPatient], [IdDoctor], [IdStatus]) VALUES (3, N'asd', N'asd', 2, NULL, 1)
SET IDENTITY_INSERT [dbo].[User] OFF
GO
ALTER TABLE [dbo].[Doctor]  WITH CHECK ADD  CONSTRAINT [FK_Doctor_Departament] FOREIGN KEY([DepartamentId])
REFERENCES [dbo].[Departament] ([DepartamentId])
GO
ALTER TABLE [dbo].[Doctor] CHECK CONSTRAINT [FK_Doctor_Departament]
GO
ALTER TABLE [dbo].[Doctor]  WITH CHECK ADD  CONSTRAINT [FK_Doctor_Post] FOREIGN KEY([PostId])
REFERENCES [dbo].[Post] ([PostId])
GO
ALTER TABLE [dbo].[Doctor] CHECK CONSTRAINT [FK_Doctor_Post]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Doctor] FOREIGN KEY([IdDoctor])
REFERENCES [dbo].[Doctor] ([IdDoctor])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Doctor]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Patient] FOREIGN KEY([IdPatient])
REFERENCES [dbo].[Patient] ([IdPatient])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Patient]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Status] FOREIGN KEY([IdStatus])
REFERENCES [dbo].[Status] ([IdStatus])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Status]
GO