API tools faq
paste
Guest User

Untitled

a guest
Jan 10th, 2018
66
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.14 KB | None | 0 0
raw download clone embed print report
  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,  'Tyre pressure low');
  20. INSERT INTO FaultType VALUES (2,  'Broken handlebars');
  21. INSERT INTO FaultType VALUES (3,  'Bell not working');
  22. INSERT INTO FaultType VALUES (4,  'Safety lights not working');
  23. INSERT INTO FaultType VALUES (5,  'Broken seat');
  24. INSERT INTO FaultType VALUES (6,  'Dented body');
  25. INSERT INTO FaultType VALUES (7,  'QR code scratched/faded');
  26. INSERT INTO FaultType VALUES (8,  'GPS tracker broken');
  27. INSERT INTO FaultType VALUES (9,  'Broken lock');
  28. INSERT INTO FaultType VALUES (10, 'Missing parts');
  29. GO
  30.  
  31.  
  32. CREATE TABLE Member (
  33.     MemberID        BIGINT      PRIMARY KEY,
  34.     Email           VARCHAR(50) NOT NULL,
  35.     Phone           CHAR(8)     NOT NULL,
  36.     RewardPts       INT         NOT NULL,
  37.     CreditBalance   INT         NOT NULL,
  38.     Deposit         BIT         NOT NULL DEFAULT 0,
  39. );
  40. GO
  41.  
  42. INSERT INTO Member VALUES('1','Ezykill6969@hotmail','82543873',72,14,1)
  43. INSERT INTO Member VALUES('2','Ashokcoolboy5@outlook.com','99663219',96,0,1)
  44. INSERT INTO Member VALUES('3','ravennn@outlook.com','81022436',5,30,1)
  45. INSERT INTO Member VALUES('4','weiwei@gmail.com','87356981',50,47,1)
  46. INSERT INTO Member VALUES('5','kingarthur@gmail.com','86154372',23,50,1)
  47. INSERT INTO Member VALUES('6','janet@gmail.com','95695848',53,1,1)
  48. INSERT INTO Member VALUES('7','hesus@gmail.com','96806298',92,19,1)
  49. INSERT INTO Member VALUES('8','jchrist@hotmail','80868883',36,38,1)
  50. INSERT INTO Member VALUES('9','hallah@outlook.com','97180005',88,10,1)
  51. INSERT INTO Member VALUES('10','joehoe@hotmail','87838482',24,39,1)
  52. INSERT INTO Member VALUES('11','annietan@yahoo.com','81144240',17,25,1)
  53. INSERT INTO Member VALUES('12','all69goh@outlook.com','89583856',66,14,1)
  54. INSERT INTO Member VALUES('13','monkeymarq@yahoo.com','83265188',95,12,1)
  55. INSERT INTO Member VALUES('14','hockeytan@hotmail','86260086',21,35,1)
  56. INSERT INTO Member VALUES('15','youmomlol@hotmail','89406150',9,24,1)
  57. GO
  58.  
  59.  
  60. CREATE TABLE BikeStop (
  61.     BikeStopID  BIGINT  PRIMARY KEY,
  62.     StopLat     FLOAT   NOT NULL,
  63.     StopLong    FLOAT   NOT NULL,
  64.     StopRadius  FLOAT   NOT NULL,
  65. );
  66. GO
  67.  
  68. INSERT INTO BikeStop VALUES(1,-87.3779,-33.1976,8);
  69. INSERT INTO BikeStop VALUES(2,16.8407,138.697,21);
  70. INSERT INTO BikeStop VALUES(3,30.3839,99.9450,25);
  71. INSERT INTO BikeStop VALUES(4,26.1532,-23.7384,15);
  72. INSERT INTO BikeStop VALUES(5,-66.9704,-74.9351,17);
  73. INSERT INTO BikeStop VALUES(6,-22.5941,144.6293,14);
  74. INSERT INTO BikeStop VALUES(7,-60.2102,18.1223,11);
  75. INSERT INTO BikeStop VALUES(8,80.9589,21.7501,6);
  76. INSERT INTO BikeStop VALUES(9,-2.1070,-68.5870,5);
  77. INSERT INTO BikeStop VALUES(10,-22.1847,83.4133,24);
  78. INSERT INTO BikeStop VALUES(11,34.2516,77.4688,19);
  79. INSERT INTO BikeStop VALUES(12,-3.8181,-48.3831,8);
  80. INSERT INTO BikeStop VALUES(13,71.9827,-48.4927,23);
  81. INSERT INTO BikeStop VALUES(14,77.8298,-83.6732,12);
  82. INSERT INTO BikeStop VALUES(15,-24.954,-57.9471,19);
  83. GO
  84.  
  85.  
  86. CREATE TABLE Bike (
  87.     BikeID      BIGINT      PRIMARY KEY,
  88.     Rate        SMALLMONEY  NOT NULL,
  89.     STATUS      VARCHAR(20) NOT NULL    CHECK (STATUS IN ('Available', 'Damaged')),
  90.     BikeStopID  BIGINT      NOT NULL    FOREIGN KEY REFERENCES BikeStop,
  91. )
  92. GO
  93.  
  94. INSERT INTO Bike VALUES (1,  0.40, 'Available', 1);
  95. INSERT INTO Bike VALUES (2,  0.32, 'Damaged',   2);
  96. INSERT INTO Bike VALUES (3,  0.40, 'Available', 3);
  97. INSERT INTO Bike VALUES (4,  0.40, 'Available', 4);
  98. INSERT INTO Bike VALUES (5,  0.40, 'Damaged',   5);
  99. INSERT INTO Bike VALUES (6,  0.55, 'Available', 6);
  100. INSERT INTO Bike VALUES (7,  0.40, 'Available', 7);
  101. INSERT INTO Bike VALUES (8,  0.20, 'Available', 8);
  102. INSERT INTO Bike VALUES (9,  0.40, 'Damaged', 9);
  103. INSERT INTO Bike VALUES (10, 0.40, 'Available', 10);
  104. INSERT INTO Bike VALUES (11,  0.55, 'Available', 11);
  105. INSERT INTO Bike VALUES (12,  0.40, 'Damaged', 12);
  106. INSERT INTO Bike VALUES (13,  0.20, 'Available', 13);
  107. INSERT INTO Bike VALUES (14,  0.40, 'Damaged', 14);
  108. INSERT INTO Bike VALUES (15, 0.40, 'Available', 15);
  109. GO
  110.  
  111.  
  112. CREATE TABLE Promotion (
  113.     PromoCode           CHAR(6)     PRIMARY KEY,
  114.     PromoRate           FLOAT       NOT NULL,
  115.     StartDate           DATETIME    NOT NULL,
  116.     EndDate             DATETIME    NOT NULL,
  117.     PromoDescription    TEXT        NULL,
  118. );
  119. GO
  120.  
  121.  
  122.  
  123. CREATE TABLE CreditCard (
  124.     CardNo      VARCHAR(19) PRIMARY KEY,
  125.     CVV         CHAR(4)     NOT NULL,
  126.     CardType    VARCHAR(20) NOT NULL,
  127.     ExpiryDate  DATETIME    NOT NULL,
  128.     MemberID    BIGINT      NOT NULL    FOREIGN KEY REFERENCES Member,
  129. );
  130. GO
  131.  
  132. INSERT INTO CreditCard VALUES('5935687892060702','5159','Network','2020-09-30',1);
  133. INSERT INTO CreditCard VALUES('8224503908962211','3742','American Express','2023-11-30',2);
  134. INSERT INTO CreditCard VALUES('1267066767278728','8360','MasterCard','2022-06-30',3);
  135. INSERT INTO CreditCard VALUES('8518818624861005','2004','Capital One','2019-03-31',4);
  136. INSERT INTO CreditCard VALUES('8334188285737418','7355','Network','2020-11-30',5);
  137. INSERT INTO CreditCard VALUES('8269070784041522','3672','MasterCard','2023-08-31',6);
  138. INSERT INTO CreditCard VALUES('7546628190228343','4758','Visa','2023-03-31',7);
  139. INSERT INTO CreditCard VALUES('8698548266606433','1796','Diners','2022-08-31',8);
  140. INSERT INTO CreditCard VALUES('3202744613765058','5960','Discover','2025-02-28',9);
  141. INSERT INTO CreditCard VALUES('7883726759101047','1532','American Express','2025-06-30',10);
  142. INSERT INTO CreditCard VALUES('8028876524515000','6937','Capital One','2024-09-30',11);
  143. INSERT INTO CreditCard VALUES('2163899734310162','8085','Diners','2023-11-30',12);
  144. INSERT INTO CreditCard VALUES('9177259930362024','2644','Visa','2022-01-31',13);
  145. INSERT INTO CreditCard VALUES('9050315501627512','2568','Visa','2025-02-28',14);
  146. INSERT INTO CreditCard VALUES('2519518809191926','4997','Capital One','2020-04-30',15);
  147. GO
  148.  
  149. CREATE TABLE CreditDebitTrans (
  150.     TransID         BIGINT      PRIMARY KEY,
  151.     TransType       VARCHAR(20) NOT NULL,
  152.     TransAmt        SMALLMONEY  NOT NULL,
  153.     TransDateTime   DATETIME    NOT NULL,
  154.     CardNo          VARCHAR(19) NOT NULL    FOREIGN KEY REFERENCES CreditCard,
  155. );
  156. GO
  157.  
  158.  
  159. CREATE TABLE Ride (
  160.     RideID          BIGINT      PRIMARY KEY,
  161.     PtsRedeemed     INT         NULL,
  162.     RidePts         INT         NOT NULL,
  163.     Cost            SMALLMONEY  NOT NULL,
  164.     Distance        FLOAT       NOT NULL,
  165.     DateTimeStart   DATETIME    NOT NULL,
  166.     DateTimeEnd     DATETIME    NOT NULL,
  167.     MemberID        BIGINT      NOT NULL    FOREIGN KEY REFERENCES Member,
  168.     BikeID          BIGINT      NOT NULL    FOREIGN KEY REFERENCES Bike,
  169.     PromoCode       CHAR(6)     NULL        FOREIGN KEY REFERENCES Promotion,
  170.     START           BIGINT      NOT NULL    FOREIGN KEY REFERENCES BikeStop(BikeStopID),
  171.     Finish          BIGINT      NOT NULL    FOREIGN KEY REFERENCES BikeStop(BikeStopID),
  172.     TransID         BIGINT      NULL        FOREIGN KEY REFERENCES CreditDebitTrans,
  173. );
  174. GO
  175.  
  176.  
  177. CREATE TABLE Maintenance (
  178.     MaintID     BIGINT      PRIMARY KEY,
  179.     MaintDate   DATETIME    NOT NULL,
  180.     BikeID      BIGINT      NOT NULL    FOREIGN KEY REFERENCES Bike,
  181. );
  182. GO
  183.  
  184.  
  185. CREATE TABLE Repair (
  186.     MaintID BIGINT  PRIMARY KEY FOREIGN KEY REFERENCES Maintenance,
  187. );
  188. GO
  189.  
  190.  
  191. CREATE TABLE MaintItem (
  192.     MaintID     BIGINT  NOT NULL    FOREIGN KEY REFERENCES Maintenance,
  193.     SNo         BIGINT  NOT NULL,
  194.     Descr       TEXT    NULL,
  195.     CONSTRAINT PK_MaintItem PRIMARY KEY (MaintID, SNo),
  196. );
  197. GO
  198.  
  199.  
  200. CREATE TABLE MaintType (
  201.     TypeCode    INT     PRIMARY KEY,
  202.     TypeDesc    TEXT    NULL,
  203. );
  204. GO
  205.  
  206.  
  207. CREATE TABLE Feedback (
  208.     FBID        BIGINT      PRIMARY KEY,
  209.     FBDateTime  DATETIME    NOT NULL,
  210.     MemberID    BIGINT      NOT NULL    FOREIGN KEY REFERENCES Member,
  211. );
  212. GO
  213.  
  214.  
  215. CREATE TABLE Comment (
  216.     FBID    BIGINT  PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
  217.     Message TEXT    NOT NULL,
  218. );
  219. GO
  220.  
  221.  
  222. CREATE TABLE Fault (
  223.     FBID        BIGINT      PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
  224.     FaultCode   SMALLINT    NOT NULL    FOREIGN KEY REFERENCES FaultType,
  225.     MaintID     BIGINT      NULL        FOREIGN KEY REFERENCES Repair,
  226.     BikeID      BIGINT      NOT NULL    FOREIGN KEY REFERENCES Bike,
  227. );
  228. GO
  229.  
  230. CREATE TABLE Reservation (
  231.     MemberID            BIGINT      FOREIGN KEY REFERENCES Member,
  232.     BikeID              BIGINT      FOREIGN KEY REFERENCES Bike,
  233.     ResStatus           VARCHAR(15) NOT NULL                        CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
  234.     DateTimeRes         DATETIME    NOT NULL,
  235.     DateTimeReservedFor DATETIME    NOT NULL,
  236.     CONSTRAINT PK_Reservation   PRIMARY KEY (MemberID, BikeID),
  237.     CONSTRAINT CHK_DateTime     CHECK (DateTimeRes < DateTimeReservedFor),
  238. )
  239. GO
  240.  
  241.  
  242. SELECT * FROM FaultType;
  243. SELECT * FROM Member;
  244. SELECT * FROM BikeStop;
  245. SELECT * FROM Bike;
  246. GO
Add Comment
Please, Sign In to add comment
Public Pastes
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!