SHARE
TWEET

Untitled

a guest Aug 12th, 2017 559 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE IF NOT EXISTS Admin_Requirements (
  2.   Req_ID mediumint(9) NOT NULL AUTO_INCREMENT,
  3.   Req_Description text NOT NULL,
  4.   PRIMARY KEY (Req_ID)
  5. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
  6.  
  7.  
  8. INSERT INTO Admin_Requirements (Req_ID, Req_Description) VALUES
  9. (1, 'Casual Dress'),
  10. (2, 'Fancy Dress'),
  11. (3, 'Corporate/Business'),
  12. (4, 'Jersey Shore'),
  13. (5, 'Age 18+'),
  14. (6, 'Age 21+'),
  15. (7, 'College ID'),
  16. (8, 'Underwear only'),
  17. (9, 'Swimsuit'),
  18. (10, 'Cover fee');
  19.  
  20.  
  21. CREATE TABLE IF NOT EXISTS Entertainment (
  22.   E_ID mediumint(9) NOT NULL AUTO_INCREMENT,
  23.   E_Description mediumtext NOT NULL,
  24.   PRIMARY KEY (E_ID)
  25. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
  26.  
  27.  
  28. INSERT INTO Entertainment (E_ID, E_Description) VALUES
  29. (1, 'House Music'),
  30. (2, 'Dub_Step Music'),
  31. (3, 'Trance Music'),
  32. (4, 'HipHop Music'),
  33. (5, 'Rap Music'),
  34. (6, 'Live DJ'),
  35. (7, 'Highlighter party');
  36.  
  37.  
  38.  
  39. CREATE TABLE IF NOT EXISTS Food (
  40.   Party_ID bigint(20) NOT NULL AUTO_INCREMENT,
  41.   Name VARCHAR(256) NOT NULL,
  42.   Price int(11) NOT NULL,
  43.   PRIMARY KEY (Party_ID,Name)
  44. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
  45.  
  46.  
  47.  
  48. INSERT INTO Food (Party_ID, Name, Price) VALUES
  49. (1, 'Chinese', 2),
  50. (1, 'Pizza', 4),
  51. (2, 'Jack D.', 3),
  52. (2, 'Vodka', 4),
  53. (5, 'beer', 3),
  54. (7, 'pasta', 6),
  55. (7, 'peaches', 2),
  56. (8, 'shots', 2),
  57. (8, 'vodka', 10),
  58. (9, 'oranges', 1),
  59. (11, 'bannana', 8),
  60. (13, 'Vodka', 3);
  61.  
  62.  
  63. CREATE TABLE IF NOT EXISTS Messages (
  64.   TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  65.   TO VARCHAR(256) NOT NULL,
  66.   FROM VARCHAR(256) NOT NULL,
  67.   Contents longtext NOT NULL,
  68.   PRIMARY KEY (TIME,TO,FROM),
  69.   KEY TO (TO),
  70.   KEY FROM (FROM)
  71. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  72.  
  73.  
  74. INSERT INTO Messages (TIME, TO, FROM, Contents) VALUES
  75. ('2011_04_16 14:49:01', 'Test1@pitt.edu', 'Test2@pitt.edu', 'This is a test message from 1_2'),
  76. ('2011_04_16 14:49:32', 'Test2@pitt.edu', 'Test4@pitt.edu', 'This is a test message from 2_4'),
  77. ('2011_04_16 14:49:55', 'Test3@pitt.edu', 'Test1@pitt.edu', 'This is a test message from 3_1'),
  78. ('2011_04_18 15:32:29', 'Test7@pitt.edu', 'Test9@pitt.edu', 'weruihuhihiu'),
  79. ('2011_04_18 15:33:05', 'Test5@pitt.edu', 'Test1@pitt.edu', 'This is super');
  80.  
  81.  
  82. CREATE TABLE IF NOT EXISTS Party (
  83.   Party_ID bigint(20) NOT NULL AUTO_INCREMENT,
  84.   Zip int(5) NOT NULL,
  85.   Address mediumtext NOT NULL,
  86.   User_ID VARCHAR(256) NOT NULL,
  87.   Additional_Description mediumtext NOT NULL,
  88.   Activite int(1) NOT NULL,
  89.   PRIMARY KEY (Party_ID),
  90.   KEY Zip (Zip),
  91.   KEY User_ID (User_ID)
  92. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
  93.  
  94.  
  95. INSERT INTO Party (Party_ID, Zip, Address, User_ID, Additional_Description, Activite) VALUES
  96. (1, 15213, '238 Meyran Ave.', 'Test3@pitt.edu', 'Addmission price is 5 dollars', 1),
  97. (2, 10013, '174 Canal Street', 'Test2@pitt.edu', 'Theme is togas', 1),
  98. (3, 15213, '444 Bouquet street', 'Test9@pitt.edu', 'uew', 0),
  99. (4, 15213, '345 apple street', 'Test8@pitt.edu', 'hihi', 1),
  100. (5, 10013, '658 boring street', 'Test4@pitt.edu', 'joewoi', 1),
  101. (6, 82353, '222 melwood', 'Test3@pitt.edu', 'djhoi', 0),
  102. (7, 15213, '234 Craig', 'Test10@pitt.edu', 'eawfjoi', 1),
  103. (8, 32144, '777 nothing', 'Test5@pitt.edu', 'uaewifr', 1),
  104. (9, 28917, '888 melwood', 'Test8@pitt.edu', 'adusfhisa', 1),
  105. (10, 10013, '567 fake street', 'Test7@pitt.edu', 'hiw2', 1),
  106. (11, 48392, '999 bayard ave', 'Test5@pitt.edu', '324', 1),
  107. (12, 12345, '483 fake street', 'Test7@pitt.edu', 'awei', 0),
  108. (13, 10013, '174 aiid', 'Test6@pitt.edu', 'fas', 1);
  109.  
  110.  
  111. CREATE TABLE IF NOT EXISTS Party_entertainment (
  112.   Party_ID bigint(20) NOT NULL,
  113.   E_ID mediumint(9) NOT NULL,
  114.   PRIMARY KEY (Party_ID,E_ID),
  115.   KEY E_ID (E_ID)
  116. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  117.  
  118.  
  119. INSERT INTO Party_entertainment (Party_ID, E_ID) VALUES
  120. (1, 1),
  121. (8, 1),
  122. (9, 2),
  123. (4, 3),
  124. (7, 3),
  125. (9, 3),
  126. (11, 4),
  127. (4, 5),
  128. (9, 5),
  129. (2, 6),
  130. (5, 6),
  131. (11, 6),
  132. (8, 7),
  133. (11, 7);
  134.  
  135.  
  136. CREATE TABLE IF NOT EXISTS Party_requirements (
  137.   Party_ID bigint(20) NOT NULL,
  138.   Req_ID mediumint(9) NOT NULL,
  139.   PRIMARY KEY (Party_ID,Req_ID),
  140.   KEY Req_ID (Req_ID)
  141. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  142.  
  143.  
  144. INSERT INTO Party_requirements (Party_ID, Req_ID) VALUES
  145. (7, 1),
  146. (4, 2),
  147. (8, 2),
  148. (2, 3),
  149. (9, 4),
  150. (2, 5),
  151. (6, 5),
  152. (9, 6),
  153. (1, 7),
  154. (11, 7),
  155. (4, 8),
  156. (11, 9),
  157. (1, 10),
  158. (5, 10),
  159. (10, 10);
  160.  
  161.  
  162.  
  163. CREATE TABLE IF NOT EXISTS RANK (
  164.   Party_ID bigint(20) NOT NULL,
  165.   Email VARCHAR(256) NOT NULL,
  166.   RANK int(11) NOT NULL,
  167.   PRIMARY KEY (Party_ID,Email),
  168.   KEY Email (Email)
  169. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  170.  
  171.  
  172.  
  173. INSERT INTO RANK (Party_ID, Email, RANK) VALUES
  174. (1, 'Test1@pitt.edu', 6),
  175. (1, 'Test4@pitt.edu', 2),
  176. (2, 'Test3@pitt.edu', 1),
  177. (4, 'Test5@pitt.edu', 1),
  178. (9, 'Test7@pitt.edu', 4),
  179. (12, 'Test10@pitt.edu', 7);
  180.  
  181.  
  182.  
  183. CREATE TABLE IF NOT EXISTS Users (
  184.   Email VARCHAR(256) NOT NULL,
  185.   Password longtext NOT NULL,
  186.   Zip int(5) NOT NULL,
  187.   RANK mediumint(9) NOT NULL,
  188.   Active int(1) NOT NULL,
  189.   PRIMARY KEY (Email)
  190. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  191.  
  192. INSERT INTO Users (Email, Password, Zip, RANK, Active) VALUES
  193. ('Test10@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 89402, 10, 1),
  194. ('Test1@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 15213, 1, 1),
  195. ('Test2@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 10013, 2, 1),
  196. ('Test3@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 7052, 3, 1),
  197. ('Test4@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 12345, 5, 0),
  198. ('Test5@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 10013, 6, 0),
  199. ('Test6@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 15213, 3, 1),
  200. ('Test7@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 15213, 8, 0),
  201. ('Test8@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 45930, 8, 1),
  202. ('Test9@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 28917, 9, 1);
  203.  
  204.  
  205. ALTER TABLE Food
  206.   ADD CONSTRAINT Food_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID);
  207.  
  208.  
  209. ALTER TABLE Messages
  210.   ADD CONSTRAINT Messages_ibfk_1 FOREIGN KEY (TO) REFERENCES Users (Email),
  211.   ADD CONSTRAINT Messages_ibfk_2 FOREIGN KEY (FROM) REFERENCES Users (Email);
  212.  
  213.  
  214. ALTER TABLE Party
  215.   ADD CONSTRAINT Party_ibfk_1 FOREIGN KEY (User_ID) REFERENCES Users (Email);
  216.  
  217. ALTER TABLE Party_entertainment
  218.   ADD CONSTRAINT Party@002dentertainment_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID),
  219.   ADD CONSTRAINT Party@002dentertainment_ibfk_2 FOREIGN KEY (E_ID) REFERENCES Entertainment (E_ID);
  220.  
  221.  
  222. ALTER TABLE Party_requirements
  223.   ADD CONSTRAINT Party@002drequirements_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID),
  224.   ADD CONSTRAINT Party@002drequirements_ibfk_2 FOREIGN KEY (Req_ID) REFERENCES Admin Requirements (Req_ID);
  225.  
  226.  
  227. ALTER TABLE RANK
  228.   ADD CONSTRAINT Rank_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID),
  229.   ADD CONSTRAINT Rank_ibfk_2 FOREIGN KEY (Email) REFERENCES Users (Email);
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