Advertisement
Guest User

Untitled

a guest
Jan 11th, 2018
503
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 19.66 KB | None | 0 0
  1. USE NP40Book;
  2. GO
  3.  
  4. DROP DATABASE IF EXISTS MokeBike;
  5. GO
  6.  
  7. CREATE DATABASE MokeBike;
  8. GO
  9. USE MokeBike;
  10. GO
  11.  
  12.  
  13. CREATE TABLE FaultType (
  14.     FaultCode   SMALLINT    PRIMARY KEY,
  15.     FaultDesc   TEXT        NULL,
  16. );
  17. GO
  18.  
  19. INSERT INTO FaultType VALUES (1,  'Fault1');
  20. INSERT INTO FaultType VALUES (2,  'Fault2');
  21. INSERT INTO FaultType VALUES (3,  'Fault3');
  22. INSERT INTO FaultType VALUES (4,  'Fault4');
  23. INSERT INTO FaultType VALUES (5,  'Fault5');
  24. INSERT INTO FaultType VALUES (6,  'Fault6');
  25. INSERT INTO FaultType VALUES (7,  'Fault7');
  26. INSERT INTO FaultType VALUES (8,  'Fault8');
  27. INSERT INTO FaultType VALUES (9,  'Fault9');
  28. INSERT INTO FaultType VALUES (10, 'Fault10');
  29. INSERT INTO FaultType VALUES (11, 'Fault11');
  30. INSERT INTO FaultType VALUES (12, 'Fault12');
  31. INSERT INTO FaultType VALUES (13, 'Fault13');
  32. INSERT INTO FaultType VALUES (14, 'Fault14');
  33. INSERT INTO FaultType VALUES (15, 'Fault15');
  34. GO
  35.  
  36.  
  37. CREATE TABLE Member (
  38.     MemberID        BIGINT      PRIMARY KEY,
  39.     Email           VARCHAR(50) NOT NULL,
  40.     Phone           CHAR(8)     NOT NULL,
  41.     RewardPts       INT         NOT NULL,
  42.     CreditBalance   SMALLMONEY  NOT NULL,
  43.     Deposit         BIT         NOT NULL DEFAULT 0,
  44. );
  45. GO
  46.  
  47. INSERT INTO Member VALUES(1,  'member1@amail.com',  '81111111', 10,  10.00,  1)
  48. INSERT INTO Member VALUES(2,  'member2@bmail.com',  '82222222', 20,  20.00,  1)
  49. INSERT INTO Member VALUES(3,  'member3@gmail.com',  '83333333', 30,  30.00,  1)
  50. INSERT INTO Member VALUES(4,  'member4@dmail.com',  '84444444', 40,  40.00,  1)
  51. INSERT INTO Member VALUES(5,  'member5@email.com',  '85555555', 50,  50.00,  1)
  52. INSERT INTO Member VALUES(6,  'member6@amail.com',  '86666666', 60,  60.00,  1)
  53. INSERT INTO Member VALUES(7,  'member7@bmail.com',  '87777777', 70,  70.00,  1)
  54. INSERT INTO Member VALUES(8,  'member8@gmail.com',  '88888888', 80,  80.00,  1)
  55. INSERT INTO Member VALUES(9,  'member9@dmail.com',  '89999999', 90,  90.00,  1)
  56. INSERT INTO Member VALUES(10, 'member10@email.com', '90000000', 100, 100.00, 1)
  57. INSERT INTO Member VALUES(11, 'member11@amail.com', '91111111', 110, 110.00, 1)
  58. INSERT INTO Member VALUES(12, 'member12@bmail.com', '92222222', 120, 120.00, 1)
  59. INSERT INTO Member VALUES(13, 'member13@gmail.com', '93333333', 130, 130.00, 1)
  60. INSERT INTO Member VALUES(14, 'member14@dmail.com', '94444444', 140, 140.00, 1)
  61. INSERT INTO Member VALUES(15, 'member15@email.com', '95555555', 150, 150.00, 1)
  62. GO
  63.  
  64.  
  65. CREATE TABLE BikeStop (
  66.     BikeStopID  BIGINT  PRIMARY KEY,
  67.     StopLat     FLOAT   NOT NULL,
  68.     StopLong    FLOAT   NOT NULL,
  69.     StopRadius  FLOAT   NOT NULL,
  70. );
  71. GO
  72.  
  73. INSERT INTO BikeStop VALUES(1,  1.29027001, 103.85195901, 1);
  74. INSERT INTO BikeStop VALUES(2,  1.29027002, 103.85195902, 2);
  75. INSERT INTO BikeStop VALUES(3,  1.29027003, 103.85195903, 3);
  76. INSERT INTO BikeStop VALUES(4,  1.29027004, 103.85195904, 4);
  77. INSERT INTO BikeStop VALUES(5,  1.29027005, 103.85195905, 5);
  78. INSERT INTO BikeStop VALUES(6,  1.29027006, 103.85195906, 6);
  79. INSERT INTO BikeStop VALUES(7,  1.29027007, 103.85195907, 7);
  80. INSERT INTO BikeStop VALUES(8,  1.29027008, 103.85195908, 8);
  81. INSERT INTO BikeStop VALUES(9,  1.29027009, 103.85195909, 9);
  82. INSERT INTO BikeStop VALUES(10, 1.29027010, 103.85195910, 10);
  83. INSERT INTO BikeStop VALUES(11, 1.29027011, 103.85195911, 11);
  84. INSERT INTO BikeStop VALUES(12, 1.29027012, 103.85195912, 12);
  85. INSERT INTO BikeStop VALUES(13, 1.29027013, 103.85195913, 13);
  86. INSERT INTO BikeStop VALUES(14, 1.29027014, 103.85195914, 14);
  87. INSERT INTO BikeStop VALUES(15, 1.29027015, 103.85195915, 15);
  88. GO
  89.  
  90.  
  91. CREATE TABLE Bike (
  92.     BikeID      BIGINT      PRIMARY KEY,
  93.     Rate        SMALLMONEY  NOT NULL,
  94.     Status      VARCHAR(20) NOT NULL    CHECK (Status IN ('Available', 'Damaged', 'Under Repair')),
  95.     BikeStopID  BIGINT      NOT NULL    FOREIGN KEY REFERENCES BikeStop,
  96. )
  97. GO
  98.  
  99. INSERT INTO Bike VALUES (1,  0.40, 'Available',    1);
  100. INSERT INTO Bike VALUES (2,  0.32, 'Damaged',      2);
  101. INSERT INTO Bike VALUES (3,  0.40, 'Available',    3);
  102. INSERT INTO Bike VALUES (4,  0.40, 'Available',    4);
  103. INSERT INTO Bike VALUES (5,  0.40, 'Damaged',      5);
  104. INSERT INTO Bike VALUES (6,  0.55, 'Available',    6);
  105. INSERT INTO Bike VALUES (7,  0.40, 'Under Repair', 7);
  106. INSERT INTO Bike VALUES (8,  0.20, 'Available',    8);
  107. INSERT INTO Bike VALUES (9,  0.40, 'Damaged',      9);
  108. INSERT INTO Bike VALUES (10, 0.40, 'Available',    10);
  109. INSERT INTO Bike VALUES (11, 0.55, 'Available',    11);
  110. INSERT INTO Bike VALUES (12, 0.40, 'Damaged',      12);
  111. INSERT INTO Bike VALUES (13, 0.20, 'Available',    13);
  112. INSERT INTO Bike VALUES (14, 0.40, 'Damaged',      14);
  113. INSERT INTO Bike VALUES (15, 0.40, 'Available',    15);
  114. GO
  115.  
  116.  
  117. CREATE TABLE Promotion (
  118.     PromoCode           CHAR(6)     PRIMARY KEY,
  119.     PromoRate           FLOAT       NOT NULL,
  120.     StartDate           DATETIME    NOT NULL,
  121.     EndDate             DATETIME    NOT NULL,
  122.     PromoDescription    TEXT        NULL,
  123. );
  124. GO
  125.  
  126. INSERT INTO Promotion VALUES ('AAAAAA', 0.01, '2017-06-01', '2017-07-01', 'DescriptionA');
  127. INSERT INTO Promotion VALUES ('BBBBBB', 0.02, '2017-06-02', '2017-07-02', 'DescriptionB');
  128. INSERT INTO Promotion VALUES ('CCCCCC', 0.03, '2017-06-03', '2017-07-03', 'DescriptionC');
  129. INSERT INTO Promotion VALUES ('DDDDDD', 0.04, '2017-06-04', '2017-07-04', 'DescriptionD');
  130. INSERT INTO Promotion VALUES ('EEEEEE', 0.05, '2017-06-05', '2017-07-05', 'DescriptionE');
  131. INSERT INTO Promotion VALUES ('FFFFFF', 0.06, '2017-06-06', '2017-07-06', 'DescriptionF');
  132. INSERT INTO Promotion VALUES ('GGGGGG', 0.07, '2017-06-07', '2017-07-07', 'DescriptionG');
  133. INSERT INTO Promotion VALUES ('HHHHHH', 0.08, '2017-06-08', '2017-07-08', 'DescriptionH');
  134. INSERT INTO Promotion VALUES ('IIIIII', 0.09, '2017-06-09', '2017-07-09', 'DescriptionI');
  135. INSERT INTO Promotion VALUES ('JJJJJJ', 0.10, '2017-06-10', '2017-07-10', 'DescriptionJ');
  136. INSERT INTO Promotion VALUES ('KKKKKK', 0.11, '2017-06-11', '2017-07-11', 'DescriptionK');
  137. INSERT INTO Promotion VALUES ('LLLLLL', 0.12, '2017-06-12', '2017-07-12', 'DescriptionL');
  138. INSERT INTO Promotion VALUES ('MMMMMM', 0.13, '2017-06-13', '2017-07-13', 'DescriptionM');
  139. INSERT INTO Promotion VALUES ('NNNNNN', 0.14, '2017-06-14', '2017-07-14', 'DescriptionN');
  140. INSERT INTO Promotion VALUES ('OOOOOO', 0.15, '2017-06-15', '2017-07-15', 'DescriptionO');
  141. GO
  142.  
  143.  
  144. CREATE TABLE CreditCard (
  145.     CardNo      VARCHAR(19) PRIMARY KEY,
  146.     CVV         CHAR(4)     NOT NULL,
  147.     CardType    VARCHAR(20) NOT NULL,
  148.     ExpiryDate  DATETIME    NOT NULL,
  149.     MemberID    BIGINT      NOT NULL    FOREIGN KEY REFERENCES Member,
  150. );
  151. GO
  152.  
  153. INSERT INTO CreditCard VALUES ('0101010101010101', '0101', 'Visa',       '2020-09-30', 1);
  154. INSERT INTO CreditCard VALUES ('0202020202020202', '0202', 'Visa',       '2023-11-30', 2);
  155. INSERT INTO CreditCard VALUES ('0303030303030303', '0303', 'MasterCard', '2022-06-30', 3);
  156. INSERT INTO CreditCard VALUES ('0404040404040404', '0404', 'Visa',       '2019-03-31', 4);
  157. INSERT INTO CreditCard VALUES ('0505050505050505', '0505', 'Visa',       '2020-11-30', 5);
  158. INSERT INTO CreditCard VALUES ('0606060606060606', '0606', 'MasterCard', '2023-08-31', 6);
  159. INSERT INTO CreditCard VALUES ('0707070707070707', '0707', 'Visa',       '2023-03-31', 7);
  160. INSERT INTO CreditCard VALUES ('0808080808080808', '0808', 'Diners',     '2022-08-31', 8);
  161. INSERT INTO CreditCard VALUES ('0909090909090909', '0909', 'Visa',       '2025-02-28', 9);
  162. INSERT INTO CreditCard VALUES ('1010101010101010', '1010', 'Diners',     '2025-06-30', 10);
  163. INSERT INTO CreditCard VALUES ('1111111111111111', '1111', 'MasterCard', '2024-09-30', 11);
  164. INSERT INTO CreditCard VALUES ('1212121212121212', '1212', 'Diners',     '2023-11-30', 12);
  165. INSERT INTO CreditCard VALUES ('1313131313131313', '1313', 'Visa',       '2022-01-31', 13);
  166. INSERT INTO CreditCard VALUES ('1414141414141414', '1414', 'Visa',       '2025-02-28', 14);
  167. INSERT INTO CreditCard VALUES ('1515151515151515', '1515', 'Diners',     '2020-04-30', 15);
  168. GO
  169.  
  170.  
  171. CREATE TABLE CreditDebitTrans (
  172.     TransID         BIGINT      PRIMARY KEY,
  173.     TransType       VARCHAR(10) NOT NULL        CHECK (TransType IN ('Debit', 'Topup')),
  174.     TransAmt        SMALLMONEY  NOT NULL,
  175.     TransDateTime   DATETIME    NOT NULL,
  176.     CardNo          VARCHAR(19) NOT NULL        FOREIGN KEY REFERENCES CreditCard,
  177. );
  178. GO
  179.  
  180. INSERT INTO CreditDebitTrans VALUES (1,  'Debit', 1.00,  '2017-09-15', '0101010101010101');
  181. INSERT INTO CreditDebitTrans VALUES (2,  'Topup', 2.00,  '2017-09-30', '0101010101010101');
  182. INSERT INTO CreditDebitTrans VALUES (3,  'Debit', 3.00,  '2017-05-30', '0202020202020202');
  183. INSERT INTO CreditDebitTrans VALUES (4,  'Topup', 4.00,  '2017-09-30', '0202020202020202');
  184. INSERT INTO CreditDebitTrans VALUES (5,  'Debit', 5.00,  '2017-07-30', '0303030303030303');
  185. INSERT INTO CreditDebitTrans VALUES (6,  'Topup', 6.00,  '2017-09-30', '0303030303030303');
  186. INSERT INTO CreditDebitTrans VALUES (7,  'Debit', 7.00,  '2017-09-30', '0404040404040404');
  187. INSERT INTO CreditDebitTrans VALUES (8,  'Topup', 8.00,  '2017-02-28', '0404040404040404');
  188. INSERT INTO CreditDebitTrans VALUES (9,  'Debit', 9.00,  '2017-09-10', '0505050505050505');
  189. INSERT INTO CreditDebitTrans VALUES (10, 'Topup', 10.00, '2017-01-30', '0505050505050505');
  190. INSERT INTO CreditDebitTrans VALUES (11, 'Debit', 11.00, '2017-09-30', '0606060606060606');
  191. INSERT INTO CreditDebitTrans VALUES (12, 'Topup', 12.00, '2017-10-21', '0606060606060606');
  192. INSERT INTO CreditDebitTrans VALUES (13, 'Debit', 13.00, '2017-05-30', '0707070707070707');
  193. INSERT INTO CreditDebitTrans VALUES (14, 'Topup', 14.00, '2017-12-30', '0707070707070707');
  194. INSERT INTO CreditDebitTrans VALUES (15, 'Debit', 15.00, '2017-01-22', '0808080808080808');
  195. INSERT INTO CreditDebitTrans VALUES (16, 'Topup', 16.00, '2017-09-24', '0808080808080808');
  196. GO
  197.  
  198.  
  199. CREATE TABLE Ride (
  200.     RideID          BIGINT      PRIMARY KEY,
  201.     PtsRedeemed     INT         NULL,
  202.     RidePts         INT         NOT NULL,
  203.     Cost            SMALLMONEY  NOT NULL,
  204.     Distance        FLOAT       NOT NULL,
  205.     DateTimeStart   DATETIME    NOT NULL,
  206.     DateTimeEnd     DATETIME    NOT NULL,
  207.     MemberID        BIGINT      NOT NULL    FOREIGN KEY REFERENCES Member,
  208.     BikeID          BIGINT      NOT NULL    FOREIGN KEY REFERENCES Bike,
  209.     PromoCode       CHAR(6)     NULL        FOREIGN KEY REFERENCES Promotion,
  210.     Start           BIGINT      NOT NULL    FOREIGN KEY REFERENCES BikeStop(BikeStopID),
  211.     Finish          BIGINT      NOT NULL    FOREIGN KEY REFERENCES BikeStop(BikeStopID),
  212.     TransID         BIGINT      NULL        FOREIGN KEY REFERENCES CreditDebitTrans,
  213. );
  214. GO
  215.  
  216. INSERT INTO Ride VALUES (1,  50,   10, 0.10, 1.0,  '2017-04-01 09:05', '2017-04-01 10:05', 1,  15, NULL,     1,  15, 1);
  217. INSERT INTO Ride VALUES (2,  100,  10, 0.20, 2.0,  '2017-04-02 08:05', '2017-04-02 10:05', 2,  14, 'AAAAAA', 2,  14, 1);
  218. INSERT INTO Ride VALUES (3,  NULL, 10, 0.30, 3.0,  '2017-05-01 09:15', '2017-05-01 10:05', 3,  13, NULL,     3,  13, 1);
  219. INSERT INTO Ride VALUES (4,  100,  10, 0.40, 4.0,  '2017-08-07 07:00', '2017-08-07 23:10', 4,  12, NULL,     4,  12, 1);
  220. INSERT INTO Ride VALUES (5,  50,   10, 0.50, 5.0,  '2017-04-24 09:25', '2017-04-24 10:05', 5,  11, NULL,     5,  11, 1);
  221. INSERT INTO Ride VALUES (6,  NULL, 10, 0.60, 6.0,  '2017-05-22 13:05', '2017-05-22 16:05', 6,  10, 'CCCCCC', 6,  10, 1);
  222. INSERT INTO Ride VALUES (7,  100,  10, 0.70, 7.0,  '2017-04-16 09:55', '2017-04-16 13:05', 7,  9,  NULL,     7,  9,  1);
  223. INSERT INTO Ride VALUES (8,  NULL, 10, 0.80, 8.0,  '2017-11-18 14:00', '2017-11-18 14:05', 8,  8,  'DDDDDD', 8,  8,  1);
  224. INSERT INTO Ride VALUES (9,  NULL, 10, 0.90, 9.0,  '2017-04-28 18:35', '2017-04-28 19:05', 9,  7,  NULL,     9,  7,  1);
  225. INSERT INTO Ride VALUES (10, 50,   10, 1.00, 10.0, '2017-12-15 16:05', '2017-12-15 16:45', 10, 6,  'EEEEEE', 10, 6,  1);
  226. INSERT INTO Ride VALUES (11, NULL, 10, 1.10, 11.0, '2017-04-10 09:35', '2017-04-10 10:05', 11, 5,  NULL,     11, 5,  1);
  227. INSERT INTO Ride VALUES (12, 50,   10, 1.20, 12.0, '2017-02-21 17:00', '2017-02-21 17:25', 12, 4,  NULL,     12, 4,  1);
  228. INSERT INTO Ride VALUES (13, NULL, 10, 1.30, 13.0, '2017-05-08 19:55', '2017-05-08 20:35', 13, 3,  'FFFFFF', 13, 3,  1);
  229. INSERT INTO Ride VALUES (14, NULL, 10, 1.40, 14.0, '2017-12-03 20:15', '2017-12-03 20:15', 14, 2,  NULL,     14, 2,  1);
  230. INSERT INTO Ride VALUES (15, NULL, 10, 1.50, 15.0, '2017-03-20 09:05', '2017-03-20 10:05', 15, 1,  NULL,     15, 1,  1);
  231. GO
  232.  
  233.  
  234. CREATE TABLE Maintenance (
  235.     MaintID     BIGINT      PRIMARY KEY,
  236.     MaintDate   DATETIME    NOT NULL,
  237.     BikeID      BIGINT      NOT NULL    FOREIGN KEY REFERENCES Bike,
  238. );
  239. GO
  240.  
  241. INSERT INTO Maintenance VALUES(1,  '2017-01-01', 1);
  242. INSERT INTO Maintenance VALUES(2,  '2017-02-01', 2);
  243. INSERT INTO Maintenance VALUES(3,  '2017-03-01', 3);
  244. INSERT INTO Maintenance VALUES(4,  '2017-04-01', 4);
  245. INSERT INTO Maintenance VALUES(5,  '2017-05-01', 5);
  246. INSERT INTO Maintenance VALUES(6,  '2017-06-01', 6);
  247. INSERT INTO Maintenance VALUES(7,  '2017-07-01', 7);
  248. INSERT INTO Maintenance VALUES(8,  '2017-08-01', 8);
  249. INSERT INTO Maintenance VALUES(9,  '2017-09-01', 9);
  250. INSERT INTO Maintenance VALUES(10, '2017-10-01', 10);
  251. INSERT INTO Maintenance VALUES(11, '2017-11-01', 11);
  252. INSERT INTO Maintenance VALUES(12, '2017-12-01', 12);
  253. INSERT INTO Maintenance VALUES(13, '2018-01-01', 13);
  254. INSERT INTO Maintenance VALUES(14, '2018-02-01', 14);
  255. INSERT INTO Maintenance VALUES(15, '2018-03-01', 15);
  256. GO
  257.  
  258.  
  259. CREATE TABLE Repair (
  260.     MaintID BIGINT  PRIMARY KEY FOREIGN KEY REFERENCES Maintenance,
  261. );
  262. GO
  263.  
  264. INSERT INTO Repair VALUES(1);
  265. INSERT INTO Repair VALUES(2);
  266. INSERT INTO Repair VALUES(3);
  267. INSERT INTO Repair VALUES(4);
  268. INSERT INTO Repair VALUES(5);
  269. INSERT INTO Repair VALUES(6);
  270. INSERT INTO Repair VALUES(7);
  271. INSERT INTO Repair VALUES(8);
  272. INSERT INTO Repair VALUES(9);
  273. INSERT INTO Repair VALUES(10);
  274. INSERT INTO Repair VALUES(11);
  275. INSERT INTO Repair VALUES(12);
  276. INSERT INTO Repair VALUES(13);
  277. INSERT INTO Repair VALUES(14);
  278. INSERT INTO Repair VALUES(15);
  279. GO
  280.  
  281. CREATE TABLE MaintType (
  282.     TypeCode    INT     PRIMARY KEY,
  283.     TypeDesc    TEXT    NULL,
  284. );
  285. GO
  286.  
  287. INSERT INTO MaintType VALUES(1,  'Description1');
  288. INSERT INTO MaintType VALUES(2,  'Description2');
  289. INSERT INTO MaintType VALUES(3,  'Description3');
  290. INSERT INTO MaintType VALUES(4,  'Description4');
  291. INSERT INTO MaintType VALUES(5,  'Description5');
  292. INSERT INTO MaintType VALUES(6,  'Description6');
  293. INSERT INTO MaintType VALUES(7,  'Description7');
  294. INSERT INTO MaintType VALUES(8,  'Description8');
  295. INSERT INTO MaintType VALUES(9,  'Description9');
  296. INSERT INTO MaintType VALUES(10, 'Description10');
  297. INSERT INTO MaintType VALUES(11, 'Description11');
  298. INSERT INTO MaintType VALUES(12, 'Description12');
  299. INSERT INTO MaintType VALUES(13, 'Description13');
  300. INSERT INTO MaintType VALUES(14, 'Description14');
  301. INSERT INTO MaintType VALUES(15, 'Description15');
  302. GO
  303.  
  304. CREATE TABLE MaintItem (
  305.     MaintID     BIGINT  NOT NULL    FOREIGN KEY REFERENCES Maintenance,
  306.     SNo         BIGINT  NOT NULL,
  307.     Descr       TEXT    NULL,
  308.     TypeCode    INT     NOT NULL    FOREIGN KEY REFERENCES MaintType
  309.     CONSTRAINT PK_MaintItem PRIMARY KEY (MaintID, SNo),
  310. );
  311. GO
  312.  
  313. INSERT INTO MaintItem VALUES(1, 1 ,  'Description1',  1);
  314. INSERT INTO MaintItem VALUES(2, 2 ,  'Description2',  2);
  315. INSERT INTO MaintItem VALUES(3, 3 ,  'Description3',  3);
  316. INSERT INTO MaintItem VALUES(4, 4 ,  'Description4',  4);
  317. INSERT INTO MaintItem VALUES(5, 5 ,  'Description5',  5);
  318. INSERT INTO MaintItem VALUES(6, 6 ,  'Description6',  6);
  319. INSERT INTO MaintItem VALUES(7, 7 ,  'Description7',  7);
  320. INSERT INTO MaintItem VALUES(8, 8 ,  'Description8',  8);
  321. INSERT INTO MaintItem VALUES(9, 9 ,  'Description9',  9);
  322. INSERT INTO MaintItem VALUES(10, 10, 'Description10', 10);
  323. INSERT INTO MaintItem VALUES(11, 11, 'Description11', 11);
  324. INSERT INTO MaintItem VALUES(12, 12, 'Description12', 12);
  325. INSERT INTO MaintItem VALUES(13, 13, 'Description13', 13);
  326. INSERT INTO MaintItem VALUES(14, 14, 'Description14', 14);
  327. INSERT INTO MaintItem VALUES(15, 15, 'Description15', 15);
  328. GO
  329.  
  330. CREATE TABLE Feedback (
  331.     FBID        BIGINT      PRIMARY KEY,
  332.     FBDateTime  DATETIME    NOT NULL,
  333.     MemberID    BIGINT      NOT NULL    FOREIGN KEY REFERENCES Member,
  334. );
  335. GO
  336.  
  337. INSERT INTO Feedback VALUES(1,  '2017-01-01', 1);
  338. INSERT INTO Feedback VALUES(2,  '2017-02-01', 2);
  339. INSERT INTO Feedback VALUES(3,  '2017-03-01', 3);
  340. INSERT INTO Feedback VALUES(4,  '2017-04-01', 4);
  341. INSERT INTO Feedback VALUES(5,  '2017-05-01', 5);
  342. INSERT INTO Feedback VALUES(6,  '2017-06-01', 6);
  343. INSERT INTO Feedback VALUES(7,  '2017-07-01', 7);
  344. INSERT INTO Feedback VALUES(8,  '2017-08-01', 8);
  345. INSERT INTO Feedback VALUES(9,  '2017-09-01', 9);
  346. INSERT INTO Feedback VALUES(10, '2017-10-01', 10);
  347. INSERT INTO Feedback VALUES(11, '2017-11-01', 11);
  348. INSERT INTO Feedback VALUES(12, '2017-12-01', 12);
  349. INSERT INTO Feedback VALUES(13, '2018-01-01', 13);
  350. INSERT INTO Feedback VALUES(14, '2018-02-01', 14);
  351. INSERT INTO Feedback VALUES(15, '2018-03-01', 15);
  352. GO
  353.  
  354. CREATE TABLE Comment (
  355.     FBID    BIGINT  PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
  356.     Message TEXT    NOT NULL,
  357. );
  358. GO
  359.  
  360. INSERT INTO Comment VALUES(1,  'Message1');
  361. INSERT INTO Comment VALUES(2,  'Message2');
  362. INSERT INTO Comment VALUES(3,  'Message3');
  363. INSERT INTO Comment VALUES(4,  'Message4');
  364. INSERT INTO Comment VALUES(5,  'Message5');
  365. INSERT INTO Comment VALUES(6,  'Message6');
  366. INSERT INTO Comment VALUES(7,  'Message7');
  367. INSERT INTO Comment VALUES(8,  'Message8');
  368. INSERT INTO Comment VALUES(9,  'Message9');
  369. INSERT INTO Comment VALUES(10, 'Message10');
  370. INSERT INTO Comment VALUES(11, 'Message11');
  371. INSERT INTO Comment VALUES(12, 'Message12');
  372. INSERT INTO Comment VALUES(13, 'Message13');
  373. INSERT INTO Comment VALUES(14, 'Message14');
  374. INSERT INTO Comment VALUES(15, 'Message15');
  375. GO
  376.  
  377. CREATE TABLE Fault (
  378.     FBID        BIGINT      PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
  379.     FaultCode   SMALLINT    NOT NULL    FOREIGN KEY REFERENCES FaultType,
  380.     MaintID     BIGINT      NULL        FOREIGN KEY REFERENCES Repair,
  381.     BikeID      BIGINT      NOT NULL    FOREIGN KEY REFERENCES Bike,
  382. );
  383. GO
  384.  
  385. INSERT INTO Fault VALUES (1,  1,  1,  1);
  386. INSERT INTO Fault VALUES (2,  2,  2,  2);
  387. INSERT INTO Fault VALUES (3,  3,  2,  2);
  388. INSERT INTO Fault VALUES (4,  4,  4,  4);
  389. INSERT INTO Fault VALUES (5,  5,  5,  5);
  390. INSERT INTO Fault VALUES (6,  6,  6,  6);
  391. INSERT INTO Fault VALUES (7,  7,  7,  7);
  392. INSERT INTO Fault VALUES (8,  8,  8,  8);
  393. INSERT INTO Fault VALUES (9,  9,  9,  9);
  394. INSERT INTO Fault VALUES (10, 10, 10, 10);
  395. INSERT INTO Fault VALUES (11, 11, 11, 11);
  396. INSERT INTO Fault VALUES (12, 12, 12, 12);
  397. INSERT INTO Fault VALUES (13, 13, 13, 13);
  398. INSERT INTO Fault VALUES (14, 14, 14, 14);
  399. INSERT INTO Fault VALUES (15, 15, 15, 15);
  400. GO
  401.  
  402. CREATE TABLE Reservation (
  403.     MemberID            BIGINT      FOREIGN KEY REFERENCES Member,
  404.     BikeID              BIGINT      FOREIGN KEY REFERENCES Bike,
  405.     ResStatus           VARCHAR(15) NOT NULL                        CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
  406.     DateTimeRes         DATETIME    NOT NULL,
  407.     DateTimeReservedFor DATETIME    NOT NULL,
  408.     CONSTRAINT PK_Reservation   PRIMARY KEY (MemberID, BikeID),
  409.     CONSTRAINT CHK_DateTime     CHECK (DateTimeRes < DateTimeReservedFor),
  410. )
  411. GO
  412.  
  413. INSERT INTO Reservation VALUES(1,  1,  'Confirmed', '2017-01-01 08:05', '2017-01-01 09:05');
  414. INSERT INTO Reservation VALUES(2,  2,  'Cancelled',  '2017-01-02 10:05', '2017-01-02 16:05');
  415. INSERT INTO Reservation VALUES(3,  3,  'NoShow',    '2017-01-03 12:05', '2017-01-03 13:05');
  416. INSERT INTO Reservation VALUES(4,  4,  'Confirmed', '2017-01-04 13:05', '2017-01-04 15:05');
  417. INSERT INTO Reservation VALUES(5,  5,  'Confirmed', '2017-01-05 12:05', '2017-01-05 13:05');
  418. INSERT INTO Reservation VALUES(6,  6,  'Confirmed', '2017-01-06 13:05', '2017-01-06 14:05');
  419. INSERT INTO Reservation VALUES(7,  7,  'Confirmed', '2017-01-07 14:05', '2017-01-07 16:05');
  420. INSERT INTO Reservation VALUES(8,  8,  'Confirmed', '2017-01-08 12:05', '2017-01-08 16:05');
  421. INSERT INTO Reservation VALUES(9,  9,  'Confirmed', '2017-01-09 08:05', '2017-01-09 15:05');
  422. INSERT INTO Reservation VALUES(10, 10, 'Confirmed', '2017-01-10 08:05', '2017-01-10 14:05');
  423. INSERT INTO Reservation VALUES(11, 11, 'Confirmed', '2017-01-11 11:05', '2017-01-11 13:05');
  424. INSERT INTO Reservation VALUES(12, 12, 'Confirmed', '2017-01-12 12:05', '2017-01-12 13:05');
  425. INSERT INTO Reservation VALUES(13, 13, 'Confirmed', '2017-01-13 14:05', '2017-01-13 15:05');
  426. INSERT INTO Reservation VALUES(14, 14, 'Confirmed', '2017-01-14 13:05', '2017-01-14 15:05');
  427. INSERT INTO Reservation VALUES(15, 15, 'Confirmed', '2017-01-15 20:05', '2017-01-15 22:05');
  428. GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement