API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 24th, 2017
79
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.60 KB | None | 0 0
raw download clone embed print report
  1. -- drop database if exists
  2. BEGIN TRY
  3. DROP Database bank_model
  4. END TRY
  5.  
  6. BEGIN CATCH
  7. -- database can't exist
  8. END CATCH
  9.  
  10. -- create new database
  11. CREATE DATABASE bank_model
  12. go
  13.  
  14. USE bank_model
  15. GO
  16.  
  17. CREATE TABLE [bank_user] (
  18. bank_user_id int NOT NULL IDENTITY(1, 1),
  19. bank_user_name nvarchar(255) NOT NULL,
  20. bank_user_gender nvarchar(20) NOT NULL,
  21. bank_user_birthday date NOT NULL,
  22. PRIMARY KEY(bank_user_id)
  23.  
  24. )
  25. GO
  26.  
  27. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Davis Brown','m','1997-12-12');
  28. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Michael Richardson','m','1996-12-12');
  29. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Julia Richards','f','1999-11-12');
  30. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Mary Simpson','f','1999-09-12');
  31. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('John Jackson','m','1976-12-25');
  32. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Annabeth Gordon','f','1945-04-19');
  33. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Barbara Wayne','f','1988-05-12');
  34. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Harrison White','m','1998-01-25');
  35. INSERT INTO [dbo].[bank_user] ([bank_user_name],[bank_user_gender],[bank_user_birthday]) VALUES ('Samantha Travis','f','1998-04-01');
  36.  
  37. CREATE TABLE [dbo].[bank_transaction_type](
  38. [bank_transaction_type_id] [int],
  39. [bank_transaction_type_name] [nvarchar] (30)
  40. )
  41. GO
  42.  
  43. INSERT INTO [dbo].[bank_transaction_type] ([bank_transaction_type_id],[bank_transaction_type_name]) VALUES (1, 'Cash pay');
  44. INSERT INTO [dbo].[bank_transaction_type] ([bank_transaction_type_id],[bank_transaction_type_name]) VALUES (2, 'Cash withdrawal');
  45. INSERT INTO [dbo].[bank_transaction_type] ([bank_transaction_type_id],[bank_transaction_type_name]) VALUES (3, 'Replenishment of cash');
  46.  
  47. CREATE TABLE [dbo].[bank_user_gender] (
  48. [bank_user_gender_short] [nvarchar] (5),
  49. [bank_user_gender_long] [nvarchar] (50)
  50. )
  51. GO
  52.  
  53. INSERT INTO [dbo].[bank_user_gender] ([bank_user_gender_short],[bank_user_gender_long]) VALUES ('f','female');
  54. INSERT INTO [dbo].[bank_user_gender] ([bank_user_gender_short],[bank_user_gender_long]) VALUES ('m','male');
  55.  
  56. CREATE TABLE [dbo].[bank_currency](
  57. [bank_currency_id] [INT],
  58. [bank_currency_name] [nvarchar] (30)
  59. )
  60. GO
  61.  
  62. INSERT INTO [dbo].[bank_currency] ([bank_currency_id],[bank_currency_name]) VALUES (1, 'KZT');
  63. INSERT INTO [dbo].[bank_currency] ([bank_currency_id],[bank_currency_name]) VALUES (2, 'RUB');
  64. INSERT INTO [dbo].[bank_currency] ([bank_currency_id],[bank_currency_name]) VALUES (3, 'USD');
  65. INSERT INTO [dbo].[bank_currency] ([bank_currency_id],[bank_currency_name]) VALUES (4, 'EUR');
  66.  
  67. CREATE TABLE [dbo].[bank_transaction_t] (
  68. [bank_transaction_t_id] [INT] IDENTITY(1,1),
  69. [bank_transaction_t_type] [int],
  70. [bank_transaction_t_account_id_from] [INT],
  71. [bank_transaction_t_account_id_to] [INT],
  72. [bank_transaction_t_time] [DATETIME],
  73. [bank_transaction_t_amount] [money]
  74. )
  75.  
  76. create table [dbo].[bank_transaction_session](
  77. [bank_transaction_session_id] int,
  78. [bank_transaction_session_transaction_id] int
  79. );
  80.  
  81. ---transactions table for update-----
  82.  
  83. CREATE TABLE [bank_accounts] (
  84. bank_accounts_id int NOT NULL IDENTITY(1, 1),
  85. bank_accounts_user_id int NOT NULL,
  86. bank_accounts_hash nvarchar(20) NOT NULL,
  87. bank_accounts_status int NOT NULL,
  88. bank_accounts_currency int NOT NULL,
  89. bank_accounts_amount money NOT NULL,
  90. bank_accounts_type nvarchar(20) NOT NULL,
  91. PRIMARY KEY(bank_accounts_id)
  92.  
  93. )
  94. GO
  95.  
  96. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (2,'971212021',1,550.45,1,'dep');
  97. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (2,'971212022',1,1550.45,1,'cred');
  98. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (5,'761225051',1,111550.78,1,'pen');
  99. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (6,'450419061',1,112550.78,1,'pen');
  100. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (7,'880512071',1,56572.78,1,'pen');
  101. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (5,'761225052',1,11750.78,1,'dep');
  102. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (1,'971212011',1,114545.99,1,'cred');
  103. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (3,'991112031',1,117821.78,1,'dep');
  104. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (4,'990912041',1,245550.78,1,'dep');
  105. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (5,'761225053',0,785150.78,1,'cred');
  106. INSERT INTO [dbo].[bank_accounts]([bank_accounts_user_id],[bank_accounts_hash],[bank_accounts_status],[bank_accounts_amount],[bank_accounts_currency],[bank_accounts_type]) VALUES (4,'990912042',0,0.78,1,'cred');
  107.  
  108. CREATE TABLE [bank_transaction_a] (
  109. bank_transaction_a_id int NOT NULL,
  110. bank_transaction_a_time date NOT NULL,
  111. bank_transaction_a_type int NOT NULL,
  112. bank_transaction_a_amount money NOT NULL,
  113. bank_transaction_a_currency_from int NOT NULL,
  114. bank_transaction_a_currency_to int NOT NULL,
  115. bank_transaction_a_account_id_from nvarchar(20) NOT NULL,
  116. bank_transaction_a_account_id_to nvarchar(20) NOT NULL,
  117. bank_transaction_a_account_status_from int NOT NULL,
  118. bank_transaction_a_account_status_to int NOT NULL,
  119. bank_transaction_a_amount_from int NOT NULL,
  120. bank_transaction_a_amount_to int NOT NULL,
  121. CONSTRAINT [PK_BANK_TRANSACTION_A] PRIMARY KEY CLUSTERED
  122. (
  123. [bank_transaction_a_id] ASC
  124. ) WITH (IGNORE_DUP_KEY = OFF)
  125.  
  126. )
  127. GO
  128.  
  129. ALTER TABLE [bank_accounts] WITH CHECK ADD CONSTRAINT [bank_accounts_fk0] FOREIGN KEY ([bank_accounts_user_id]) REFERENCES [bank_user]([bank_user_id])
  130. ON UPDATE CASCADE
  131. GO
  132. ALTER TABLE [bank_accounts] CHECK CONSTRAINT [bank_accounts_fk0]
  133. GO
  134.  
  135. ALTER TABLE [bank_transaction_a] WITH CHECK ADD CONSTRAINT [bank_transaction_a_fk0] FOREIGN KEY ([bank_transaction_a_user_id_from]) REFERENCES [bank_user]([bank_user_id])
  136. ON UPDATE CASCADE
  137. GO
  138. ALTER TABLE [bank_transaction_a] CHECK CONSTRAINT [bank_transaction_a_fk0]
  139. GO
  140. ALTER TABLE [bank_transaction_a] WITH CHECK ADD CONSTRAINT [bank_transaction_a_fk1] FOREIGN KEY ([bank_transaction_a_user_id_to]) REFERENCES [bank_user]([bank_user_id])
  141. ON UPDATE
  142. GO
  143. ALTER TABLE [bank_transaction_a] CHECK CONSTRAINT [bank_transaction_a_fk1]
  144. GO
  145. ALTER TABLE [bank_transaction_a] WITH CHECK ADD CONSTRAINT [bank_transaction_a_fk2] FOREIGN KEY ([bank_transaction_a_account_id_from]) REFERENCES [bank_accounts]([bank_accounts_id])
  146. ON UPDATE CASCADE
  147. GO
  148. ALTER TABLE [bank_transaction_a] CHECK CONSTRAINT [bank_transaction_a_fk2]
  149. GO
  150. ALTER TABLE [bank_transaction_a] WITH CHECK ADD CONSTRAINT [bank_transaction_a_fk3] FOREIGN KEY ([bank_transaction_a_account_id_to]) REFERENCES [bank_accounts]([bank_accounts_id])
  151. ON UPDATE
  152. GO
  153. ALTER TABLE [bank_transaction_a] CHECK CONSTRAINT [bank_transaction_a_fk3]
  154. GO
  155.  
  156.  
  157. INSERT INTO [dbo].[bank_transaction_session] VALUES (1,1);
  158.  
  159. ---index section----------------
  160. CREATE INDEX idx_tr_time ON [dbo].[bank_transaction_a]([bank_transaction_a_time]);
  161. CREATE INDEX idx_tr_amount ON [dbo].[bank_transaction_a]([bank_transaction_a_amount]);
  162. CREATE INDEX idx_tr_type ON [dbo].[bank_transaction_a]([bank_transaction_a_type]);
  163. CREATE INDEX idx_cl_acid ON [dbo].[bank_accounts]([bank_accounts_hash]);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!