Advertisement
mikronik24

Untitled

May 23rd, 2022
687
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [master]
  2. GO
  3. /****** Object:  Database [HospitalProject]    Script Date: 23.05.2022 12:29:25 ******/
  4. CREATE DATABASE [HospitalProject]
  5.  CONTAINMENT = NONE
  6.  ON  PRIMARY
  7. ( NAME = N'HospitalProject', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HospitalProject.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
  8.  LOG ON
  9. ( NAME = N'HospitalProject_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HospitalProject_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
  10.  WITH CATALOG_COLLATION = DATABASE_DEFAULT
  11. GO
  12. ALTER DATABASE [HospitalProject] SET COMPATIBILITY_LEVEL = 150
  13. GO
  14. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
  15. begin
  16. EXEC [HospitalProject].[dbo].[sp_fulltext_database] @action = 'enable'
  17. end
  18. GO
  19. ALTER DATABASE [HospitalProject] SET ANSI_NULL_DEFAULT OFF
  20. GO
  21. ALTER DATABASE [HospitalProject] SET ANSI_NULLS OFF
  22. GO
  23. ALTER DATABASE [HospitalProject] SET ANSI_PADDING OFF
  24. GO
  25. ALTER DATABASE [HospitalProject] SET ANSI_WARNINGS OFF
  26. GO
  27. ALTER DATABASE [HospitalProject] SET ARITHABORT OFF
  28. GO
  29. ALTER DATABASE [HospitalProject] SET AUTO_CLOSE OFF
  30. GO
  31. ALTER DATABASE [HospitalProject] SET AUTO_SHRINK OFF
  32. GO
  33. ALTER DATABASE [HospitalProject] SET AUTO_UPDATE_STATISTICS ON
  34. GO
  35. ALTER DATABASE [HospitalProject] SET CURSOR_CLOSE_ON_COMMIT OFF
  36. GO
  37. ALTER DATABASE [HospitalProject] SET CURSOR_DEFAULT  GLOBAL
  38. GO
  39. ALTER DATABASE [HospitalProject] SET CONCAT_NULL_YIELDS_NULL OFF
  40. GO
  41. ALTER DATABASE [HospitalProject] SET NUMERIC_ROUNDABORT OFF
  42. GO
  43. ALTER DATABASE [HospitalProject] SET QUOTED_IDENTIFIER OFF
  44. GO
  45. ALTER DATABASE [HospitalProject] SET RECURSIVE_TRIGGERS OFF
  46. GO
  47. ALTER DATABASE [HospitalProject] SET  ENABLE_BROKER
  48. GO
  49. ALTER DATABASE [HospitalProject] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
  50. GO
  51. ALTER DATABASE [HospitalProject] SET DATE_CORRELATION_OPTIMIZATION OFF
  52. GO
  53. ALTER DATABASE [HospitalProject] SET TRUSTWORTHY OFF
  54. GO
  55. ALTER DATABASE [HospitalProject] SET ALLOW_SNAPSHOT_ISOLATION OFF
  56. GO
  57. ALTER DATABASE [HospitalProject] SET PARAMETERIZATION SIMPLE
  58. GO
  59. ALTER DATABASE [HospitalProject] SET READ_COMMITTED_SNAPSHOT ON
  60. GO
  61. ALTER DATABASE [HospitalProject] SET HONOR_BROKER_PRIORITY OFF
  62. GO
  63. ALTER DATABASE [HospitalProject] SET RECOVERY FULL
  64. GO
  65. ALTER DATABASE [HospitalProject] SET  MULTI_USER
  66. GO
  67. ALTER DATABASE [HospitalProject] SET PAGE_VERIFY CHECKSUM  
  68. GO
  69. ALTER DATABASE [HospitalProject] SET DB_CHAINING OFF
  70. GO
  71. ALTER DATABASE [HospitalProject] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
  72. GO
  73. ALTER DATABASE [HospitalProject] SET TARGET_RECOVERY_TIME = 60 SECONDS
  74. GO
  75. ALTER DATABASE [HospitalProject] SET DELAYED_DURABILITY = DISABLED
  76. GO
  77. ALTER DATABASE [HospitalProject] SET ACCELERATED_DATABASE_RECOVERY = OFF  
  78. GO
  79. EXEC sys.sp_db_vardecimal_storage_format N'HospitalProject', N'ON'
  80. GO
  81. ALTER DATABASE [HospitalProject] SET QUERY_STORE = OFF
  82. GO
  83. USE [HospitalProject]
  84. GO
  85. /****** Object:  Table [dbo].[__EFMigrationsHistory]    Script Date: 23.05.2022 12:29:26 ******/
  86. SET ANSI_NULLS ON
  87. GO
  88. SET QUOTED_IDENTIFIER ON
  89. GO
  90. CREATE TABLE [dbo].[__EFMigrationsHistory](
  91.     [MigrationId] [nvarchar](150) NOT NULL,
  92.     [ProductVersion] [nvarchar](32) NOT NULL,
  93.  CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY CLUSTERED
  94. (
  95.     [MigrationId] ASC
  96. )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]
  97. ) ON [PRIMARY]
  98. GO
  99. /****** Object:  Table [dbo].[department]    Script Date: 23.05.2022 12:29:26 ******/
  100. SET ANSI_NULLS ON
  101. GO
  102. SET QUOTED_IDENTIFIER ON
  103. GO
  104. CREATE TABLE [dbo].[department](
  105.     [departmentId] [int] IDENTITY(1,1) NOT NULL,
  106.     [departmentName] [nvarchar](max) NULL,
  107.     [hospitalId] [int] NOT NULL,
  108.  CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED
  109. (
  110.     [departmentId] ASC
  111. )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]
  112. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  113. GO
  114. /****** Object:  Table [dbo].[doctor]    Script Date: 23.05.2022 12:29:26 ******/
  115. SET ANSI_NULLS ON
  116. GO
  117. SET QUOTED_IDENTIFIER ON
  118. GO
  119. CREATE TABLE [dbo].[doctor](
  120.     [doctorId] [int] IDENTITY(1,1) NOT NULL,
  121.     [firstName] [nvarchar](max) NULL,
  122.     [lastName] [nvarchar](max) NULL,
  123.     [specialityId] [int] NOT NULL,
  124.  CONSTRAINT [PK_doctor] PRIMARY KEY CLUSTERED
  125. (
  126.     [doctorId] ASC
  127. )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]
  128. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  129. GO
  130. /****** Object:  Table [dbo].[doctor_department]    Script Date: 23.05.2022 12:29:26 ******/
  131. SET ANSI_NULLS ON
  132. GO
  133. SET QUOTED_IDENTIFIER ON
  134. GO
  135. CREATE TABLE [dbo].[doctor_department](
  136.     [doctorId] [int] NOT NULL,
  137.     [departmentId] [int] NOT NULL
  138. ) ON [PRIMARY]
  139. GO
  140. /****** Object:  Table [dbo].[hospital]    Script Date: 23.05.2022 12:29:26 ******/
  141. SET ANSI_NULLS ON
  142. GO
  143. SET QUOTED_IDENTIFIER ON
  144. GO
  145. CREATE TABLE [dbo].[hospital](
  146.     [hospitalId] [int] IDENTITY(1,1) NOT NULL,
  147.     [hospitalName] [nvarchar](max) NULL,
  148.  CONSTRAINT [PK_hospital] PRIMARY KEY CLUSTERED
  149. (
  150.     [hospitalId] ASC
  151. )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]
  152. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  153. GO
  154. /****** Object:  Table [dbo].[speciality]    Script Date: 23.05.2022 12:29:26 ******/
  155. SET ANSI_NULLS ON
  156. GO
  157. SET QUOTED_IDENTIFIER ON
  158. GO
  159. CREATE TABLE [dbo].[speciality](
  160.     [specialityId] [int] IDENTITY(1,1) NOT NULL,
  161.     [specialityName] [nvarchar](max) NULL,
  162.  CONSTRAINT [PK_speciality] PRIMARY KEY CLUSTERED
  163. (
  164.     [specialityId] ASC
  165. )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]
  166. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  167. GO
  168. /****** Object:  Index [IX_department_hospitalId]    Script Date: 23.05.2022 12:29:26 ******/
  169. CREATE NONCLUSTERED INDEX [IX_department_hospitalId] ON [dbo].[department]
  170. (
  171.     [hospitalId] ASC
  172. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
  173. GO
  174. /****** Object:  Index [IX_doctor_specialityId]    Script Date: 23.05.2022 12:29:26 ******/
  175. CREATE NONCLUSTERED INDEX [IX_doctor_specialityId] ON [dbo].[doctor]
  176. (
  177.     [specialityId] ASC
  178. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
  179. GO
  180. /****** Object:  Index [IX_doctor_department_departmentId]    Script Date: 23.05.2022 12:29:26 ******/
  181. CREATE NONCLUSTERED INDEX [IX_doctor_department_departmentId] ON [dbo].[doctor_department]
  182. (
  183.     [departmentId] ASC
  184. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
  185. GO
  186. /****** Object:  Index [IX_doctor_department_doctorId]    Script Date: 23.05.2022 12:29:26 ******/
  187. CREATE NONCLUSTERED INDEX [IX_doctor_department_doctorId] ON [dbo].[doctor_department]
  188. (
  189.     [doctorId] ASC
  190. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
  191. GO
  192. ALTER TABLE [dbo].[department]  WITH CHECK ADD  CONSTRAINT [FK_department_hospital_hospitalId] FOREIGN KEY([hospitalId])
  193. REFERENCES [dbo].[hospital] ([hospitalId])
  194. ON DELETE CASCADE
  195. GO
  196. ALTER TABLE [dbo].[department] CHECK CONSTRAINT [FK_department_hospital_hospitalId]
  197. GO
  198. ALTER TABLE [dbo].[doctor]  WITH CHECK ADD  CONSTRAINT [FK_doctor_speciality_specialityId] FOREIGN KEY([specialityId])
  199. REFERENCES [dbo].[speciality] ([specialityId])
  200. ON DELETE CASCADE
  201. GO
  202. ALTER TABLE [dbo].[doctor] CHECK CONSTRAINT [FK_doctor_speciality_specialityId]
  203. GO
  204. ALTER TABLE [dbo].[doctor_department]  WITH CHECK ADD  CONSTRAINT [FK_doctor_department_department_departmentId] FOREIGN KEY([departmentId])
  205. REFERENCES [dbo].[department] ([departmentId])
  206. ON DELETE CASCADE
  207. GO
  208. ALTER TABLE [dbo].[doctor_department] CHECK CONSTRAINT [FK_doctor_department_department_departmentId]
  209. GO
  210. ALTER TABLE [dbo].[doctor_department]  WITH CHECK ADD  CONSTRAINT [FK_doctor_department_doctor_doctorId] FOREIGN KEY([doctorId])
  211. REFERENCES [dbo].[doctor] ([doctorId])
  212. ON DELETE CASCADE
  213. GO
  214. ALTER TABLE [dbo].[doctor_department] CHECK CONSTRAINT [FK_doctor_department_doctor_doctorId]
  215. GO
  216. /****** Object:  StoredProcedure [dbo].[addproc]    Script Date: 23.05.2022 12:29:26 ******/
  217. SET ANSI_NULLS ON
  218. GO
  219. SET QUOTED_IDENTIFIER ON
  220. GO
  221. -- =============================================
  222. -- Author:      <Author,,Name>
  223. -- Create date: <Create Date,,>
  224. -- Description: <Description,,>
  225. -- =============================================
  226. CREATE PROCEDURE [dbo].[addproc]
  227.     -- Add the parameters for the stored procedure here
  228.     @firstName nvarchar(30),
  229.     @lastName nvarchar(30),
  230.     @specialityId int
  231. AS
  232. BEGIN
  233.     -- SET NOCOUNT ON added to prevent extra result sets from
  234.     -- interfering with SELECT statements.
  235.     SET NOCOUNT ON;
  236.  
  237.     -- Insert statements for procedure here
  238.     INSERT INTO doctor (firstName, lastName, specialityId)
  239.     VALUES(@firstName, @lastName, @specialityId);
  240.     SELECT CAST(SCOPE_IDENTITY() as int);
  241. END
  242. GO
  243. /****** Object:  StoredProcedure [dbo].[deleteproc]    Script Date: 23.05.2022 12:29:26 ******/
  244. SET ANSI_NULLS ON
  245. GO
  246. SET QUOTED_IDENTIFIER ON
  247. GO
  248. -- =============================================
  249. -- Author:      <Author,,Name>
  250. -- Create date: <Create Date,,>
  251. -- Description: <Description,,>
  252. -- =============================================
  253. CREATE PROCEDURE [dbo].[deleteproc]
  254.     @id int
  255. AS
  256. BEGIN
  257.     -- SET NOCOUNT ON added to prevent extra result sets from
  258.     -- interfering with SELECT statements.
  259.     SET NOCOUNT ON;
  260.  
  261.     -- Insert statements for procedure here
  262.     DELETE FROM doctor WHERE doctorId = @Id
  263. END
  264. GO
  265. /****** Object:  StoredProcedure [dbo].[findproc]    Script Date: 23.05.2022 12:29:26 ******/
  266. SET ANSI_NULLS ON
  267. GO
  268. SET QUOTED_IDENTIFIER ON
  269. GO
  270. CREATE PROCEDURE [dbo].[findproc]
  271.     @id int
  272. AS
  273. BEGIN
  274.     -- SET NOCOUNT ON added to prevent extra result sets from
  275.     -- interfering with SELECT statements.
  276.     SET NOCOUNT ON;
  277.  
  278.     -- Insert statements for procedure here
  279.     SELECT * FROM doctor WHERE doctorId = @id
  280. END
  281. GO
  282. /****** Object:  StoredProcedure [dbo].[getallproc]    Script Date: 23.05.2022 12:29:26 ******/
  283. SET ANSI_NULLS ON
  284. GO
  285. SET QUOTED_IDENTIFIER ON
  286. GO
  287. -- =============================================
  288. -- Author:      <Author,,Name>
  289. -- Create date: <Create Date,,>
  290. -- Description: <Description,,>
  291. -- =============================================
  292. CREATE PROCEDURE [dbo].[getallproc]
  293. AS
  294. BEGIN
  295.     -- SET NOCOUNT ON added to prevent extra result sets from
  296.     -- interfering with SELECT statements.
  297.     SET NOCOUNT ON;
  298.  
  299.     -- Insert statements for procedure here
  300.     SELECT d.doctorId, d.firstName, d.lastName, s.specialityName
  301.     FROM doctor d
  302.     inner join speciality s on s.specialityId = d.specialityId
  303. END
  304. GO
  305. /****** Object:  StoredProcedure [dbo].[updateproc]    Script Date: 23.05.2022 12:29:26 ******/
  306. SET ANSI_NULLS ON
  307. GO
  308. SET QUOTED_IDENTIFIER ON
  309. GO
  310. -- =============================================
  311. -- Author:      <Author,,Name>
  312. -- Create date: <Create Date,,>
  313. -- Description: <Description,,>
  314. -- =============================================
  315. CREATE PROCEDURE [dbo].[updateproc]
  316. @firstName nvarchar(30),
  317. @lastName nvarchar(30),
  318. @specialityId int,
  319. @doctorId int
  320. AS
  321. BEGIN
  322.     -- SET NOCOUNT ON added to prevent extra result sets from
  323.     -- interfering with SELECT statements.
  324.     SET NOCOUNT ON;
  325.  
  326.     -- Insert statements for procedure here
  327.     UPDATE doctor
  328.     SET firstName = @firstName,
  329.     lastName = @lastName,
  330.     specialityId = @specialityId
  331.     WHERE doctorId = @doctorId
  332. END
  333. GO
  334. USE [master]
  335. GO
  336. ALTER DATABASE [HospitalProject] SET  READ_WRITE
  337. GO
  338.    
Advertisement
RAW Paste Data Copied
Advertisement