API tools faq
paste
Advertisement
Guest User

Code

a guest
Apr 14th, 2018
356
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 16.71 KB | None | 0 0
raw download clone embed print report
  1. static void createTables(Statement stmt) throws Exception{
  2.         // Create the CUSTOMERS table
  3.         stmt.executeUpdate("CREATE TABLE customers (" +
  4.                            "id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, " +
  5.                            "name VARCHAR(128) NOT NULL, " +
  6.                            "dob DATE NOT NULL, " +
  7.                            "phone INT NOT NULL, " +
  8.                            "email VARCHAR(128) NOT NULL, " +
  9.                            "ssn INT NOT NULL UNIQUE)");
  10.        
  11.         // Create the STAFF table
  12.         stmt.executeUpdate("CREATE TABLE staff ( " +
  13.                            "id INT  PRIMARY KEY AUTO_INCREMENT, " +
  14.                            "name VARCHAR(128) NOT NULL, " +
  15.                            "age INT, " +
  16.                            "title VARCHAR(128) NOT NULL, " +
  17.                            "department VARCHAR(128) NOT NULL, " +
  18.                            "phone INT NOT NULL, " +
  19.                            "address VARCHAR(128) NOT NULL, " +
  20.                            "availability TINYINT(1) NOT NULL)");
  21.        
  22.         // Create the HOTELS table
  23.         stmt.executeUpdate("CREATE TABLE hotels (" +
  24.                            "id INT PRIMARY KEY AUTO_INCREMENT, " +
  25.                            "name VARCHAR(128) NOT NULL, " +
  26.                            "address VARCHAR(128) NOT NULL, " +
  27.                            "phone INT NOT NULL, " +
  28.                            "manager_id INT, " +
  29.                            "CONSTRAINT manager_hotel_fk " +
  30.                            "FOREIGN KEY(manager_id) REFERENCES staff(id) " +
  31.                            "ON DELETE CASCADE ON UPDATE CASCADE)");
  32.        
  33.         // Create the ROOMS table
  34.         stmt.executeUpdate("CREATE TABLE rooms (" +
  35.                            "no INT NOT NULL, " +
  36.                            "hotel_id INT NOT NULL, " +
  37.                            "category VARCHAR(128) NOT NULL, " +
  38.                            "max_occupancy INT NOT NULL, " +
  39.                            "price INT NOT NULL, " +
  40.                            "is_available TINYINT(1) NOT NULL, " +
  41.                            "CONSTRAINT hotel_room_fk  " +
  42.                            "FOREIGN KEY(hotel_id) REFERENCES hotels(id)  " +
  43.                            "ON DELETE CASCADE ON UPDATE CASCADE, " +
  44.                            "PRIMARY KEY(no, hotel_id))");
  45.        
  46.         // Create the RESERVATIONS table
  47.         stmt.executeUpdate("CREATE TABLE reservations ( " +
  48.                            "id INT PRIMARY KEY AUTO_INCREMENT, " +
  49.                            "start_date DATE NOT NULL, " +
  50.                            "no_of_guests INT, " +
  51.                            "end_date DATE NOT NULL, " +
  52.                            "check_in_time TIME NOT NULL, " +
  53.                            "check_out_time TIME NOT NULL, " +
  54.                            "total_amount DECIMAL(10,2) NOT NULL, " +
  55.                            "payment_method VARCHAR(128) NOT NULL, " +
  56.                            "card_no BIGINT, " +
  57.                            "expiry DATE, " +
  58.                            "billing_address VARCHAR(128), " +
  59.                            "has_paid TINYINT(1) NOT NULL)");
  60.        
  61.         // Create the SERVICES table
  62.         stmt.executeUpdate("CREATE TABLE services ( " +
  63.                            "id INT AUTO_INCREMENT NOT NULL, " +
  64.                            "reservation_id INT NOT NULL, " +
  65.                            "name VARCHAR(128) NOT NULL, " +
  66.                            "price DECIMAL(10,2) NOT NULL, " +
  67.                            "CONSTRAINT reservation_service_fk " +
  68.                            "FOREIGN KEY(reservation_id) REFERENCES reservations(id) " +
  69.                            "ON DELETE CASCADE ON UPDATE CASCADE, " +
  70.                            "PRIMARY KEY(id, reservation_id))");
  71.        
  72.         // Create the STAFF_WORKS_AT table
  73.         stmt.executeUpdate("CREATE TABLE staff_works_at ( " +
  74.                            "hotel_id INT NOT NULL, " +
  75.                            "staff_id INT NOT NULL, " +
  76.                            "CONSTRAINT staff_works_at_pk PRIMARY KEY(hotel_id, staff_id), " +
  77.                            "CONSTRAINT hotel_staff_works_at_fk " +
  78.                            "FOREIGN KEY(hotel_id) REFERENCES hotels(id) " +
  79.                            "ON DELETE CASCADE ON UPDATE CASCADE, " +
  80.                            "CONSTRAINT staff_works_at_fk " +
  81.                            "FOREIGN KEY(staff_id) REFERENCES staff(id) " +
  82.                            "ON DELETE CASCADE ON UPDATE CASCADE)");
  83.        
  84.         // Create the CUSTOMER_MAKES table
  85.         stmt.executeUpdate("CREATE TABLE customer_makes ( " +
  86.                            "reservation_id INT NOT NULL," +
  87.                            "customer_id INT NOT NULL," +
  88.                            "CONSTRAINT customer_makes_pk PRIMARY KEY(customer_id, reservation_id)," +
  89.                            "CONSTRAINT reservation_customer_makes_fk " +
  90.                            "FOREIGN KEY(reservation_id) REFERENCES reservations(id)" +
  91.                            "ON DELETE CASCADE ON UPDATE CASCADE," +
  92.                            "CONSTRAINT customer_makes_fk " +
  93.                            "FOREIGN KEY(customer_id) REFERENCES customers(id)" +
  94.                            "ON DELETE CASCADE ON UPDATE CASCADE)");
  95.        
  96.         // Create the RESERVATION_FOR table
  97.         stmt.executeUpdate("CREATE TABLE reservation_for ( " +
  98.                            "reservation_id INT NOT NULL, " +
  99.                            "room_no INT NOT NULL, " +
  100.                            "hotel_id INT NOT NULL, " +
  101.                            "CONSTRAINT reservation_for_pk " +
  102.                            "PRIMARY KEY(reservation_id, room_no, hotel_id), " +
  103.                            "CONSTRAINT reservation_for_fk " +
  104.                            "FOREIGN KEY(reservation_id) REFERENCES reservations(id) " +
  105.                            "ON DELETE CASCADE ON UPDATE CASCADE, " +
  106.                            "CONSTRAINT room_reservation_for_fk " +
  107.                            "FOREIGN KEY(room_no,hotel_id) REFERENCES rooms(no,hotel_id) " +
  108.                            "ON DELETE CASCADE ON UPDATE CASCADE)");
  109.        
  110.         // Create the STAFF_PROVIDES table
  111.         stmt.executeUpdate("CREATE TABLE staff_provides ( " +
  112.                            "reservation_id INT NOT NULL, " +
  113.                            "staff_id INT NOT NULL, " +
  114.                            "service_id INT NOT NULL, " +
  115.                            "CONSTRAINT staff_provides_pk " +
  116.                            "PRIMARY KEY(reservation_id, staff_id, service_id), " +
  117.                            "CONSTRAINT staff_provides_fk " +
  118.                            "FOREIGN KEY(staff_id) REFERENCES staff(id)" +
  119.                            "ON DELETE CASCADE ON UPDATE CASCADE, " +
  120.                            "CONSTRAINT service_staff_provides_fk " +
  121.                            "FOREIGN KEY(service_id,reservation_id) REFERENCES services(id,reservation_id) " +
  122.                            "ON DELETE CASCADE ON UPDATE CASCADE)");
  123.     }
  124.    
  125.     static void populateDemoData(Statement stmt) throws Exception{
  126.         //Populate the CUSTOMERS table
  127.         stmt.executeUpdate("INSERT INTO customers (name,dob,phone,email,ssn) " +
  128.                            "VALUES ('David', '1980-01-30', 123, 'david@gmail.com', 5939846)");
  129.        
  130.         stmt.executeUpdate("INSERT INTO customers (name,dob,phone,email,ssn)" +
  131.                            "VALUES ('Sarah', '1971-01-30', 456, 'sarah@gmail.com', 7778352)");
  132.        
  133.         stmt.executeUpdate("INSERT INTO customers (name,dob,phone,email,ssn) " +
  134.                            "VALUES ('Joseph', '1987-01-30', 789, 'joseph@gmail.com', 8589430)");
  135.        
  136.         stmt.executeUpdate("INSERT INTO customers (name,dob,phone,email,ssn)" +
  137.                            "VALUES ('Lucy', '1985-01-30', 213, 'lucy@gmail.com', 4409328)");
  138.        
  139.         //Populate the STAFF table
  140.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability) " +
  141.                            "VALUES ('Mary', 40, 'Manager',  'Management', 654, '90 ABC St , Raleigh NC 27', 1)");
  142.        
  143.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability)" +
  144.                            "VALUES ('John', 45, 'Manager',  'Management', 564, '798 XYZ St , Rochester NY 54', 1)");
  145.        
  146.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability) " +
  147.                            "VALUES ('Carol', 55, 'Manager',  'Management', 564, '351 MH St , Greensboro NC 27', 1)");
  148.        
  149.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability)" +
  150.                            "VALUES ('Emma', 55, 'Front Desk Staff',  'Management', 546, '49 ABC St , Raleigh NC 27', 1)");
  151.        
  152.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability)" +
  153.                            "VALUES ('Ava', 55, 'Catering Staff',  'Catering', 777, '425 RG St , Raleigh NC 27', 1)");
  154.        
  155.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability)" +
  156.                            "VALUES ('Peter', 52, 'Manager',  'Management', 724, '475 RG St , Raleigh NC 27', 1)");
  157.        
  158.         stmt.executeUpdate("INSERT INTO staff (name,age,title,department,phone,address,availability)" +
  159.                            "VALUES ('Olivia', 27, 'Front Desk Staff',  'Management', 799, '325 PD St , Raleigh NC 27', 1)");
  160.        
  161.         // Populate the HOTELS table
  162.         stmt.executeUpdate("INSERT INTO hotels (name,address,phone,manager_id)" +
  163.                            "VALUES ('Hotel A', '21 ABC St , Raleigh NC 27', 919, 1)");
  164.         stmt.executeUpdate("INSERT INTO hotels (name,address,phone,manager_id)" +
  165.                            "VALUES ('Hotel B', '25 XYZ St , Rochester NY 54', 718, 2)");
  166.         stmt.executeUpdate("INSERT INTO hotels (name,address,phone,manager_id)" +
  167.                            "VALUES ('Hotel C', '29 PQR St , Greensboro NC 27', 984, 3)");
  168.         stmt.executeUpdate("INSERT INTO hotels (name,address,phone,manager_id)" +
  169.                            "VALUES ('Hotel D', '28 GHW St , Raleigh NC 32', 920, 6)");
  170.        
  171.         //Populate the ROOMS table
  172.         stmt.executeUpdate("INSERT INTO rooms (no,hotel_id, category, max_occupancy, price, is_available)" +
  173.                            "VALUES ( 1, 1, 'Economy', 1, 100, 1)");
  174.         stmt.executeUpdate("INSERT INTO rooms (no,hotel_id, category, max_occupancy, price, is_available)" +
  175.                            "VALUES ( 2, 1, 'Deluxe', 2, 200, 1)");
  176.         stmt.executeUpdate("INSERT INTO rooms (no,hotel_id, category, max_occupancy, price, is_available)" +
  177.                            "VALUES ( 3, 2, 'Economy', 1, 100, 1)");
  178.         stmt.executeUpdate("INSERT INTO rooms (no,hotel_id, category, max_occupancy, price, is_available)" +
  179.                            "VALUES ( 2, 3, 'Executive', 3, 1000, 0)");
  180.         stmt.executeUpdate("INSERT INTO rooms (no,hotel_id, category, max_occupancy, price, is_available)" +
  181.                            "VALUES ( 1, 4, 'Presidential', 4, 5000, 1)");
  182.         stmt.executeUpdate("INSERT INTO rooms (no,hotel_id, category, max_occupancy, price, is_available)" +
  183.                            "VALUES ( 5, 1, 'Deluxe', 2, 200, 1)");
  184.        
  185.         //Populate the STAFF_WORKS_AT table
  186.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  187.                            "VALUES (1, 1)");
  188.        
  189.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  190.                            "VALUES (2, 2)");
  191.        
  192.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  193.                            "VALUES (3, 3)");
  194.        
  195.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  196.                            "VALUES (1, 4)");
  197.        
  198.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  199.                            "VALUES (1, 5)");
  200.        
  201.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  202.                            "VALUES (4, 6)");
  203.        
  204.         stmt.executeUpdate("INSERT INTO staff_works_at (hotel_id, staff_id)" +
  205.                            "VALUES (4, 7)");
  206.        
  207.         //Populate the RESERVATIONS table
  208.         // no_of_guests start_date  end_date check_in_time check_out_time total_amount payment_method card_no expiry billing_address has_paid
  209.        
  210.         stmt.executeUpdate("INSERT INTO reservations (no_of_guests, start_date,  end_date, check_in_time, check_out_time, total_amount, payment_method, card_no, expiry, billing_address, has_paid)" +
  211.                            "VALUES (1, '2017-05-10', '2017-05-13', '3:17:00', '10:22:00', 0, 'credit', 1052, NULL, '980 TRT St , Raleigh NC', 0)");
  212.        
  213.         stmt.executeUpdate("INSERT INTO reservations (no_of_guests, start_date,  end_date, check_in_time, check_out_time, total_amount, payment_method, card_no, expiry, billing_address, has_paid)" +
  214.                            "VALUES (2, '2017-05-10', '2017-05-13', '4:11:00', '9:27:00', 0, 'credit', 3020, NULL, '7720 MHT St , Greensboro NC', 0)");
  215.        
  216.         stmt.executeUpdate("INSERT INTO reservations (no_of_guests, start_date,  end_date, check_in_time, check_out_time, total_amount, payment_method, card_no, expiry, billing_address, has_paid)" +
  217.                            "VALUES (1, '2016-05-10', '2016-05-14', '3:45:00', '11:10:00', 0, 'credit', 2497, NULL, '231 DRY St , Rochester NY 78', 0)");
  218.        
  219.         stmt.executeUpdate("INSERT INTO reservations (no_of_guests, start_date,  end_date, check_in_time, check_out_time, total_amount, payment_method, card_no, expiry, billing_address, has_paid)" +
  220.                            "VALUES (2, '2018-05-10', '2018-05-12', '2:30:00', '10:00:00', 0, 'cash', NULL, NULL, '24 BST Dr , Dallas TX 14', 0)");
  221.        
  222.         //Populate the RESERVATION_FOR table
  223.         //hotel,room.
  224.         stmt.executeUpdate("INSERT INTO reservation_for (reservation_id, room_no, hotel_id)" +
  225.                            "VALUES (1, 1, 1)");
  226.         stmt.executeUpdate("INSERT INTO reservation_for (reservation_id, room_no, hotel_id)" +
  227.                            "VALUES (2, 2, 1)");
  228.         stmt.executeUpdate("INSERT INTO reservation_for (reservation_id, room_no, hotel_id)" +
  229.                            "VALUES (3, 3, 2)");
  230.         stmt.executeUpdate("INSERT INTO reservation_for (reservation_id, room_no, hotel_id)" +
  231.                            "VALUES (4, 2, 3)");
  232.        
  233.         //Populate the CUSTOMER_MAKES table
  234.         stmt.executeUpdate("INSERT INTO customer_makes (reservation_id, customer_id)" +
  235.                            "VALUES (1, 1)");
  236.         stmt.executeUpdate("INSERT INTO customer_makes (reservation_id, customer_id)" +
  237.                            "VALUES (2, 2)");
  238.         stmt.executeUpdate("INSERT INTO customer_makes (reservation_id, customer_id)" +
  239.                            "VALUES (3, 3)");
  240.         stmt.executeUpdate("INSERT INTO customer_makes (reservation_id, customer_id)" +
  241.                            "VALUES (4, 4)");
  242.        
  243.         //Populate the SERVICES table
  244.         stmt.executeUpdate("INSERT INTO services (reservation_id, name, price)" +
  245.                            "VALUES (1, 'gyms', 15)");
  246.         stmt.executeUpdate("INSERT INTO services (reservation_id, name, price)" +
  247.                            "VALUES (1, 'dry cleaning', 16)");
  248.         stmt.executeUpdate("INSERT INTO services (reservation_id, name, price)" +
  249.                            "VALUES (2, 'gyms', 15)");
  250.         stmt.executeUpdate("INSERT INTO services (reservation_id, name, price)" +
  251.                            "VALUES (3, 'room service', 10)");
  252.         stmt.executeUpdate("INSERT INTO services (reservation_id, name, price)" +
  253.                            "VALUES (4, 'phone bills', 5)");
  254.        
  255.         //Populate the STAFF_PROVIDES table
  256.         //reservation - staffID - serviceID
  257.         stmt.executeUpdate("INSERT INTO staff_provides (reservation_id, staff_id, service_id)" +
  258.                            "VALUES (1, 4, 1)");
  259.         stmt.executeUpdate("INSERT INTO staff_provides (reservation_id, staff_id, service_id)" +
  260.                            "VALUES (1, 5, 2)");
  261.         stmt.executeUpdate("INSERT INTO staff_provides (reservation_id, staff_id, service_id)" +
  262.                            "VALUES (2, 4, 3)");
  263.         stmt.executeUpdate("INSERT INTO staff_provides (reservation_id, staff_id, service_id)" +
  264.                            "VALUES (3, 2, 4)");
  265.         stmt.executeUpdate("INSERT INTO staff_provides (reservation_id, staff_id, service_id)" +
  266.                            "VALUES (4, 3, 5)");
  267.         }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!