SHARE
TWEET

Untitled

a guest Oct 4th, 2017 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE employee -- Creates table 'employee' (Strong entity)
  2. (
  3.     emp_id INT NOT NULL,
  4.     name varchar(255),
  5.     email varchar(255),
  6.     address varchar(255),
  7.     city varchar(255),
  8.     state varchar(255),
  9.     postal_code char(6),
  10.     PRIMARY KEY(emp_id)
  11. );
  12.  
  13. INSERT INTO employee -- Inserts 1 row of data into employee table
  14. VALUES ('500111222', 'Jared', 'jared.hump@hotmail.com', '122 Canada Drive', 'Toronto', 'Ontario', 'L6Y0R3');
  15.  
  16. INSERT INTO employee -- Inserts 1 row of data into employee table
  17. VALUES ('500111223', 'Ishaan', 'ishaan.patel@hotmail.com', '134 Church Street', 'Toronto', 'Ontario', 'L6Y0Q9');
  18.  
  19. INSERT INTO employee -- Inserts 1 row of data into employee table
  20. VALUES ('500111224', 'Jess', 'jess.olive@live.com', '73 Suits Street', 'New York', 'New York', '155333');
  21.  
  22. INSERT INTO employee -- Inserts 1 row of data into employee table
  23. VALUES ('500111225', 'Hema', 'hema.often@hotmail.com', '188 Sunny View Street', 'Brampton', 'Ontario', 'L6Y0P6');
  24.  
  25. SELECT * FROM employee; -- To view table of employee
  26.  
  27. CREATE TABLE receptionist AS -- Duplicate employee into new table, 'receptionist' (Strong entity)
  28. SELECT * FROM employee
  29.  
  30. CREATE TABLE staff AS -- Duplicate employee into new table, 'staff' (Strong entity)
  31. SELECT * FROM employee
  32.  
  33. INSERT INTO receptionist
  34. SELECT * FROM employee
  35. WHERE
  36.  
  37. INSERT INTO staff
  38. SELECT * FROM employee
  39. WHERE
  40.  
  41. SELECT * FROM receptionist;
  42. SELECT * FROM staff;
  43.  
  44. CREATE TABLE dependent
  45. (
  46.     name varchar(255) NOT NULL,
  47.     emp_id INT,
  48.     email varchar(255),
  49.     address varchar(255),
  50.     PRIMARY KEY(name),
  51.     FOREIGN KEY(emp_id) REFERENCES employee(emp_id)
  52. );
  53.  
  54. INSERT INTO dependent (name, email, address)
  55. VALUES ('John','john.dep@live.com','123 Church Street');
  56.  
  57. INSERT INTO dependent (name, email, address)
  58. VALUES ('Steve','steve.dep@live.com','188 Church Street');
  59.  
  60. INSERT INTO dependent (name, email, address)
  61. VALUES ('Anurag','anurag.dep@live.com','423 Church Street');
  62.  
  63. INSERT INTO dependent (name, email, address)
  64. VALUES ('Harry','harry.dep@live.com','500 Church Street');
  65.  
  66. SELECT * FROM dependent;
  67.  
  68. CREATE TABLE dependent_of
  69. (
  70.     emp_id INT NOT NULL,
  71.     name varchar(255) NOT NULL,
  72.     relationship varchar(255),
  73.     FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
  74.     FOREIGN KEY(name) REFERENCES dependent(name)
  75. );
  76.  
  77. CREATE TABLE hotel
  78. (
  79.     hotel_id INT NOT NULL
  80.     name varchar(255),
  81.     location varchar(255),
  82.     num_rooms INTEGER,
  83.     PRIMARY KEY(hotel_id)
  84. );
  85.  
  86. CREATE TABLE has
  87. (
  88.     hotel_id INT NOT NULL,
  89.     emp_id INT NOT NULL,
  90.     supplier_id INT NOT NULL,
  91.     FOREIGN KEY(hotel_id) REFERENCES hotel(hotel_id),
  92.     FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
  93.     FOREIGN KEY(supplier_id) REFERENCES supplier(supplier_id)
  94. );
  95.  
  96. CREATE TABLE guests
  97. (
  98.     guest_id INT NOT NULL,
  99.     number_guests INT,
  100.     surname varchar(255),
  101.     first_name varchar(255),
  102.     address varchar(255),
  103.     email varchar(255),
  104.     phone_number char,
  105.     PRIMARY KEY(guest_id)
  106. );
  107.  
  108. AlTER TABLE guests
  109. DROP COLUMN num_guests;
  110.  
  111. CREATE TABLE book_or_request
  112. (
  113.     book_date varchar(255),
  114.     guest_id INT NOT NULL,
  115.     room_id INT NOT NULL,
  116.     cancel_date varchar(255),
  117.     extend_date varchar(255),
  118.     check_in varchar(255),
  119.     check_out varchar(255),
  120.     PRIMARY KEY(book_date),
  121.     FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
  122.     FOREIGN KEY(room_id) REFERENCES rooms(room_id)
  123. );
  124.  
  125. CREATE TABLE rooms
  126. (
  127.     room_id INT NOT NULL,
  128.     status char(10),
  129.     price varchar(225),
  130.     type char(10),
  131.     PRIMARY KEY(room_id)
  132. );
  133.  
  134. CREATE TABLE facilities
  135. (
  136.     facility_id INT NOT NULL,
  137.     name varchar(225),
  138.     status varchar(255),
  139.     PRIMARY KEY(facility_id)
  140. );
  141.  
  142. CREATE TABLE payment
  143. (
  144.     bill INT NOT NULL,
  145.     guest_id INT NOT NULL,
  146.     PRIMARY KEY(bill),
  147.     FOREIGN KEY(guest_id) REFERENCES guests(quest_id)
  148. );
  149.  
  150. CREATE TABLE supplier
  151. (
  152.     supplier_id INT  NOT NULL,
  153.     PRIMARY KEY(supplier_id)
  154. );    
  155.  
  156. CREATE TABLE season
  157. (
  158.     name varchar(255) NOT NULL,
  159.     end_date char(15),
  160.     start_date char(15),
  161.     PRIMARY KEY(name)
  162. );
  163.  
  164. CREATE TABLE discount
  165. (
  166.     amount_off INT NOT NULL,
  167.     PRIMARY KEY(amount_off)
  168. );    
  169.  
  170. CREATE TABLE reservations
  171. (
  172.     confirm_guest varchar(255) NOT NULL,
  173.     guest_id INT NOT NULL,
  174.     book_date varchar(255),
  175.     check_in varchar(255),
  176.     check_out varchar(255),
  177.     extend_date varchar(255),
  178.     cancel_date varchar(255),
  179.     PRIMARY KEY(confirm_guest),
  180.     FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
  181.     FOREIGN KEY(book_date) REFERENCES book_or_request(book_date)
  182. );
  183.    
  184. CREATE TABLE maintains
  185. (
  186.     emp_id INT,
  187.     room_id INT,
  188.     facility_id INT,
  189.     FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
  190.     FOREIGN KEY(room_id) REFERENCES rooms(room_id),
  191.     FOREIGN KEY(facility_id) REFERENCES facilities(facility_id)
  192. );
  193.  
  194. CREATE TABLE confirms
  195. (
  196.     emp_id INT,
  197.     guest_id INT,
  198.     bill INT,
  199.     FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
  200.     FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
  201.     FOREIGN KEY(bill) REFERENCES payment(bill)
  202. );
  203.  
  204. CREATE TABLE orders
  205. (
  206.     emp_id INT,
  207.     supplier_id INT,
  208.     amount INT,
  209.     item INT,
  210.     date_in varchar(255),
  211.     date_out varchar(255),
  212.     PRIMARY KEY(amount),
  213.     FOREIGN KEY(emp_id) REFERENCES employee(emp_id),
  214.     FOREIGN KEY(supplier_id) REFERENCES supplier(supplier_id)
  215. );
  216.  
  217. CREATE TABLE pays
  218. (
  219.     guest_id INT,
  220.     bill INT,
  221.     method varchar(255),
  222.     PRIMARY KEY(method),
  223.     FOREIGN KEY(guest_id) REFERENCES guests(guest_id),
  224.     FOREIGN KEY(bill) REFERENCES payment(bill)
  225. );
  226.  
  227. CREATE TABLE affects
  228. (
  229.     points INT NOT NULL,
  230.     name varchar(255),
  231.     amount_off INT,
  232.     PRIMARY KEY(points),
  233.     FOREIGN KEY(name) REFERENCES season(name),
  234.     FOREIGN KEY(amount_off) REFERENCES discount(amount_off)
  235. );
  236.  
  237. CREATE TABLE reduces
  238. (
  239.     amount_off INT,
  240.     bill INT,
  241.     FOREIGN KEY(amount_off) REFERENCES discount(amount_off),
  242.     FOREIGN KEY(bill) REFERENCES payment(bill)
  243. );
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top