I want a quote for the completion of the full assignment
IE 332 - Homework #2 Due: Oct 25th, 11:59pm Read Carefully. Important! As outlined in the course syllabus this homework is worth 8% of your final grade. The maximum attainable mark on this homework is 150. As was also outlined in the syllabus, there is a zero tolerance policy for any form of academic misconduct. The assignment can be done individually or in pairs. By electronically uploading this assignment to Gradescope you acknowledge these statements and accept any repercussions if in any violation of ANY Purdue Academic Misconduct policies. You must upload your homework on time for it to be graded. No late assignments will be accepted. Only the last uploaded version of your assignment will be graded. NOTE: You should aim to submit no later than 30 minutes before the deadline, as there could be last minute network traffic that would cause your assignment to be late, resulting in a grade of zero. You must use the provided LATEXtemplate on Brightspace to submit your assignment. Page i of i IE 332 Homework #2 Due: Oct 25 2021 1. A local coffee, tea, and food shop, known as Space Dollars, is becoming increasingly popular. They source their ingredients from all over the world, and have hundreds of orders per day per franchise. They need a new database to help manage their operations, and you need to design an ERD to capture the following information to represent a single store location: • Beverages and food both have lists of ingredients, the price, allergy information, and the time to make them. • Beverages are categorized as hot or cold, and food is either “pre-made” or “needs assembly”. • Ingredients have a source location, cost, and allergy information. • Customers have a name, an id if they’re a member, and members can accumulate rewards points. • Orders have a type (carry out, dine-in, delivery), a price, at least one item, and time placed. • There is an employee roster per franchise that includes their type (barista, manager, or custo- dian), salary, and their assigned shift. The shift set is identical between franchises to maintain consistent hours. You can assume each employee works for only one franchise at a time. (a) (20 points) Create a representative ERD using Crow’s Foot notation for the above system. Be sure to clearly and appropriately indicate any keys, weak entities, relationship constraints, etc. You can make realistic assumptions for variable types and constraints, for any not indicated above. (b) (5 points) After implementing the design in your ERD, a colleague decides to modify the design (BAD IDEA! this should have all be done before implementing in the first place!) by adding the following extra table to the database. Provide an explanation for what it does, and show the updated ERD. 1 CREATE TABLE Fran 2 ( 3 address varchar (255), 4 franId int , 5 totalProfit decimal , 6 timeSinceOpen timestamp 7 ); (c) (10 points) Analyze the code in part (b). Explain why it is correct or indicate any issues with it from a database design perspective. If any aspects are incorrect, correct them and include a short but clear justification for (i) the issue, and (ii) why your fix is best. (d) (15 points) Write all the necessary CREATE TABLE commands to create your entire ERD. You do not have to retype the code from part (c). 2. The ER diagram below describes the relationships of a ticket booking system for an airline company. (a) (15 points) Provide all the CREATE TABLE statements needed to create the database. Recall from lecture that the order in which the tables are created matters! IE 332 Homework #2 Page 2 of 4 (b) (10 points) Does the ERD violate any of the Normal Form (NF) rules? If yes, for each case, indicate: 1. where a rule was violated, 2. which of the NF rules was violated and why, and 3. how it could be fixed. (c) (6 points) For the attributes below, indicate if the use of ENUM type was correct: 1. payment type in the reservations table, and 2. aircraft type in the flights table. Justify your answers based on the properties of ENUM discussed in labs. 3. Use the ERD in Question 2 (disregard any normal form violations or issues with the use of ENUM, if they exist) to provide SQL statements to answer the following questions. Assume that flight prices are updated every month (i.e., valid from date starts at every 1st day of the month). (a) (5 points) What is the total number of flights leaving from airports in Turkey in 2021 with destination in airports in the US? (b) (6 points) For each travel class (provide the class name, not the ID), how much, on average, is a one-way ticket for a flight more expensive than a round-trip ticket for the same flight? Provide this information for prices valid during the month of December, 2021 (see observation in the question’s instructions). (c) (8 points) For each month of 2022, what are the flights with the highest round trip price? As in the previous item, use the observation in the question’s instructions. List the month’s number, the flight ID and the flight’s price. Hint: Take into account that there might be more than one flight with the highest price for a given month. You may want to use embedded queries to answer this item. (d) (8 points) For each aircraft manufacturer, list the number of (travel) class “Economy” reserva- tions that (i) include flights operated with aircraft produced by the aircraft manufacturer, and (ii) were made in 2021 by passengers with address in cities that have “new” in their names and are not in Australia. Order the list in descending order of number of reservations, breaking ties by the aircraft manufacturer’s name in alphabetical order. Display only the first 5 results. (e) (5 points) What are the passenger names and phone numbers of those who purchased more than one seat on flight DL284? (f) (5 points) How many passengers are flying from Shanghai to Chicago in the year of 2020 using a business class in a direct flight? IE 332 Homework #2 Page 3 of 4 4. Use the ERD in Question 2 (disregard any normal form violations or issues with the use of ENUM, if they exist) to provide SQL statements to answer the following questions. Assume that flight prices are updated every month (i.e., valid from date starts at every 1st day of the month). For each SQL query below, provide ONE SENTENCE English translation for the question being asked in the query. (a) (6 points) SELECT H.reservation_ID, count(H.flight_ID) AS c_flight_ID FROM has_flights H GROUP BY H.reservation_ID HAVING c_flight_ID > 1 ORDER BY c_flight_ID (b) (6 points) SELECT distinct R1.reservation_id FROM reservations R1 JOIN reservations R2 ON R1.reservation_date= DATE_ADD(R2.reservation_date,INTERVAL 30 DAY) WHERE R1.payment_amount > R2.payment_amount + 1000 Note: The DATE ADD() function adds a specified time interval to a date http://www-db.deis. unibo.it/courses/TW/DOCS/w3schools/sql/func_date_add.asp.html. (c) (6 points) SELECT F.flight_ID FROM flights F RIGHT JOIN costs C ON C.flight_ID = F.flight_ID JOIN travel_class T ON T.travel_class_ID = C.travel_class_ID WHERE T.name = "economy" AND T.num_luggage_included > 1 5. Watch the video at https://www.youtube.com/watch?v=iUtQN8LMEp0 and answer the following questions: (a) (1 point) What is a non-relational database? (b) (4 points) What are four pros of non-relational databases? (c) (3 points) What are three cons of non-relational database? (d) (1 point) Which database is often cited as being the most complicated to use? (e) (1 point) What is the most popular non-relational database? (f) (4 points) What are the four questions you should ask in order to determine which type of database to use? IE 332 Homework #2 Page 4 of 4 http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/func_date_add.asp.html http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/func_date_add.asp.html https://www.youtube.com/watch?v=iUtQN8LMEp0