daily pastebin goal
64%
SHARE
TWEET

Untitled

a guest Mar 23rd, 2019 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. use [master]
  2.  
  3. IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'BasketballStatistic')
  4.     CREATE DATABASE [BasketballStatistic]
  5. else
  6.     DROP DATABASE [BasketballStatistic]
  7.     CREATE DATABASE [BasketballStatistic]
  8. go
  9. use [BasketballStatistic]
  10.  
  11. CREATE TABLE Team
  12. (
  13. team_id INT IDENTITY(1,1) NOT NULL,
  14. team_country VARCHAR(50) NOT NULL,
  15. team_city VARCHAR(50) NOT NULL,
  16. team_name VARCHAR(50) NOT NULL,
  17. team_fondation_date DateTime Not null,
  18. team_main_stadion int not null,
  19. PRIMARY KEY(team_id)
  20. );/*+*/
  21.  
  22. CREATE TABLE Team_Statistic
  23. (
  24. team_statistic_id INT IDENTITY(1,1) NOT NULL,
  25. team_id int not Null,
  26.  
  27. wins int not null,
  28. loses int not null,
  29. loses_on_draw int not null,
  30. wins_on_draw int not null,
  31.  
  32. PRIMARY KEY(Team_statistic_id)
  33. );/*+*/
  34. /*CREATE TABLE Team_Stadion
  35. (
  36.     Team_Stadion_id INT IDENTITY(1,1) NOT NULL,
  37.     team_id INT not null,
  38.     Stadion_id INT NOT NULL,
  39.     Start_stadion_use DateTime Not null,
  40.     Finish_stadion_use DateTime Not null,
  41.     PRIMARY KEY(Team_Stadion_id)
  42. );*/
  43.  
  44.  
  45. CREATE TABLE Stadion
  46. (
  47. Stadion_id INT IDENTITY(1,1) NOT NULL,
  48.  
  49. Stadion_name VARCHAR(50) NOT NULL,
  50. Stadion_country VARCHAR(50) NOT NULL,
  51. Stadion_city VARCHAR(50) NOT NULL,
  52. Stadion_fondation_date DateTime Not null,
  53. Stadion_places_count int not null,
  54. Stadion_location_latitude float NOT NULL,
  55. Stadion_location_longitude fload NOT NULL,
  56.  
  57. PRIMARY KEY(Stadion_id)
  58. );/*+*/
  59.  
  60. CREATE TABLE Players
  61. (
  62. player_id INT IDENTITY(1,1) NOT NULL,
  63. current_team_id int not Null,
  64. player_name VARCHAR(50) NOT NULL,
  65. player_surname VARCHAR(50) NOT NULL,
  66. player_country  VARCHAR(50) NOT NULL,
  67. player_born_date DateTime Not null,
  68. PRIMARY KEY(player_id)
  69. );/*+*/
  70.  
  71.  
  72. CREATE TABLE Player_Contracts
  73. (
  74. Contract_id INT IDENTITY(1,1) NOT NULL,
  75. team_id int not Null,
  76. player_id int not Null,
  77. player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
  78. player_number_on_contract int not Null,
  79. contract_start_time DateTime not null,
  80. contract_deyure_end_time DateTime not null,
  81. contract_defacto_end_time DateTime,
  82. PRIMARY KEY(Contract_id)
  83. );
  84. /*+*/
  85. CREATE TABLE Player_statistic
  86. (
  87. statistic_id INT IDENTITY(1,1) NOT NULL,
  88. Contract_id int not Null,
  89.  
  90. total_time_played time not null,
  91. goals_from_game_bullits_count int not null,
  92. goals_from_game_count int not Null,
  93. key_passes_for_goals_count int not Null,
  94. saves_count int not Null,
  95. two_min_fare int not Null,
  96. five_min_fare int not_Null,
  97.  
  98. wins int not null,
  99. loses int not null,
  100. loses_on_draw int not null,
  101. wins_on_draw int not null,
  102. PRIMARY KEY(statistic_id)
  103. );/*+-*/
  104.  
  105. CREATE TABLE Coach
  106. (
  107. coach_id INT IDENTITY(1,1) NOT NULL,
  108. current_team_id int not Null,
  109. coach_name VARCHAR(50) NOT NULL,
  110. coach_surname VARCHAR(50) NOT NULL,
  111. coach_country  VARCHAR(50) NOT NULL,
  112. coach_city VARCHAR(50) NOT NULL,
  113. coach_born_date DateTime Not null,
  114. PRIMARY KEY(coach_id)
  115. );/*+*/
  116.  
  117. CREATE TABLE Coach_Contracts
  118. (
  119. Contract_id INT IDENTITY(1,1) NOT NULL,
  120. team_id int not Null,
  121. coach_id int not Null,
  122. contract_start_time DateTime not null,
  123. contract_deyure_end_time DateTime not null,
  124. contract_defacto_end_time DateTime,
  125. PRIMARY KEY(Contract_id)
  126. );
  127. /*+*/
  128. CREATE TABLE Coach_statistic
  129. (
  130. statistic_id INT IDENTITY(1,1) NOT NULL,
  131. Contract_id int not Null,
  132.  
  133. wins int not null,
  134. loses int not null,
  135. loses_on_draw int not null,
  136. wins_on_draw int not null,
  137.  
  138. PRIMARY KEY(statistic_id)
  139. );/*+*/
  140.  
  141.  
  142. CREATE TABLE referies
  143. (
  144.  referie_id INT IDENTITY(1,1) NOT NULL,
  145.  referie_name VARCHAR(50) NOT NULL,
  146.  referie_surname VARCHAR(50) NOT NULL,
  147.  referie_born_date DateTime Not null,
  148.  
  149.  
  150. PRIMARY KEY(referie_id)
  151. );/*+*/
  152.  
  153. /*common table for Tour and team*/
  154.  
  155. CREATE TABLE Tournament
  156. (
  157. Tournament_id INT IDENTITY(1,1) NOT NULL,
  158. Tournament_name VARCHAR(50) NOT NULL,
  159. Tournament_status  VARCHAR(50) NOT NULL,/*international local*/
  160. PRIMARY KEY(Tournament_id)
  161. );/*+*/
  162.  
  163. CREATE TABLE Game
  164. (
  165. Game_id INT IDENTITY(1,1) NOT NULL,
  166. Tournament_id int NOT NULL,
  167. Stadion_id int not Null,
  168. referie_id int not null,
  169. home_team int not null,
  170. guest_team int not null,
  171. home_team_score int not null,
  172. guest_team_score int not null,
  173. start_time date not null,
  174.  
  175. PRIMARY KEY(Game_id)
  176. );/*+*/
  177.  
  178. CREATE TABLE GameApplication
  179. (
  180. GameApplication_id INT IDENTITY(1,1) NOT NULL,
  181. Game_id int NOT NULL,
  182. Player_id int not null,
  183. Team_id int not null,
  184. PRIMARY KEY(GameApplication_id)
  185. );/*+*/
  186.  
  187.  
  188. CREATE TABLE GameLog
  189. (
  190. GameLog_id INT IDENTITY(1,1) NOT NULL,
  191. Game_id int not null,
  192.  
  193. action_time Time not null,
  194. main_game_period bit not null
  195. game_period int not null,
  196. player_id int not null,
  197. player_action int not null,/*action of player triger for statistic and score update*/
  198. PRIMARY KEY(GameLog_id)
  199. );
  200.  
  201. CREATE TABLE PlayerAction
  202. (
  203.     Player_Action_id INT IDENTITY(1,1) NOT NULL,
  204.     action_commited VARCHAR(50) NOT NULL, /*goal, assist, save, boolit, noboolit*/
  205.     PRIMARY KEY(Player_Action_id)
  206. );
  207.  
  208.  
  209.  
  210. ALTER TABLE Team_statistic
  211. WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
  212. REFERENCES Team(team_id);
  213.  
  214. ALTER TABLE Players
  215. WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
  216. REFERENCES Team(team_id);
  217.  
  218. ALTER TABLE Player_Contracts
  219. WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
  220. REFERENCES Team(team_id);
  221.  
  222. ALTER TABLE Player_Contracts
  223. WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
  224. REFERENCES Players(player_id);
  225.  
  226. ALTER TABLE Player_statistic
  227. WITH CHECK ADD CONSTRAINT FK_Contract_Id FOREIGN KEY(contract_id)
  228. REFERENCES Player_Contracts(contract_id );
  229.  
  230. ALTER TABLE Coach
  231. WITH CHECK ADD CONSTRAINT FK_Current_team_id FOREIGN KEY(current_team_id)
  232. REFERENCES Team(team_id);
  233.  
  234. ALTER TABLE Coach_Contracts
  235. WITH CHECK ADD CONSTRAINT FK_Team_id FOREIGN KEY(team_id)
  236. REFERENCES Team(team_id);
  237.  
  238. ALTER TABLE Coach_Contracts
  239. WITH CHECK ADD CONSTRAINT FK_Coach_id FOREIGN KEY(coach_id)
  240. REFERENCES Coach(coach_id);
RAW Paste Data
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
 
Top