please read assignment project pdf there is all questions and instructions. second I am provided data in sql data assignment file. third there is template also provided where you have to write query along with world document. mostly all info and instruction provided in assignment project file pdf
CS 331: Database System Design & Management Fall 2022 1 CS 331: Database System Design and Management Fall 2022 Assignment for Replacing the Project Important instructions: MUST read before answering the assignment. 1. No questions will be answered on 12/07. 2. Email your questions to the TA: Anshu Singh (
[email protected]) and cc the instructor (
[email protected]). 3. Groups are NOT allowed. 4. Each student will submit the following two things in a “ZIP” folder: a. PDF file of their answer sheet. b. SQL queries written on a given template. c. Not following will result in zero points. 5. Points for each question are written in front of each question. 6. All assignments must be submitted to canvas on the due date. 7. Assignments should NOT be handwritten. Students may use Microsoft Word, Google Doc, or Overleaf. Handwritten assignments will be returned without grading them. 8. The answer to each question must be detailed and you are not allowed to copy-paste. For each answer, try to provide examples and figures. Write answers using bullets, if possible, to increase the readability of your answer. Use bold, italic, or underline to highlight an important point in your answer. 9. Due date: Dec 08th 12:00 AM. 10. Late submission of the assignment will NOT be allowed, unless there are some health issues. 11. If you do not understand any question or if there is any typo, please let me know. mailto:
[email protected] CS 331: Database System Design & Management Fall 2022 2 Academic Integrity Any type of cheating and/or plagiarism is NOT allowed. If your answer is identical to the answer/material available anywhere (since two people when answering even a very simple question, can never use the exact same word with a very high probability), this will result in zero. We will follow NJIT University Policy on Academic Integrity. Please see details here: https://www5.njit.edu/policies/sites/policies/files/academic-integrity-code.pdf Question 1 [100 points] For answering the following questions, use the file containing the schema and data provided with the assignment. You MUST provide the following for each query: • SQL queries written over the given template AND WRITE SQL QUERY on the answer sheet as well; not doing so will result in zero point for this question. (6 points for writing the query on the template + 3 point to write the same query on the answer sheet = 9 points) • Provide screenshots of the query and the result. (1 point) • Write only one query per page. Important: Not providing SQL queries on the given template will result in zero. To generate data, you need to access the file “sql_data_assignment_project.sql” and just copy-paste into MySQL. Query 1: Find names of users that used a waste bin between ‘2019-10-26 14:00:00’ and ‘2019-10-26 15:00:00’ Query 2: Find the Inside bins (bins inside the buildings) that are used by Visitors between ‘2019-10-26 14:00:00’ and ‘2019-10-26 15:00:00’ Query 3: Find the distinct names of all the students who used a recycling bin incorrectly (put a wrong item in the recycling bin) at least once between ‘2019-10-26 14:00:00’ and ‘2019-10-26 15:00:00’ . Remember that ObjectRecognitionSensor records a trash_type which can be used to determine if someone incorrectly throws a wrong type of trash in a waste bin. https://www5.njit.edu/policies/sites/policies/files/academic-integrity-code.pdf CS 331: Database System Design & Management Fall 2022 3 Query 4: Find the users who had more than 100 landfill disposal events (trash of any type thrown into the landfill bin) Query 5: Print top 10 users - user_id and their rank - in ascending order of their ranks where rank is given by the total compost trash disposed by them (total weight of compost trash thrown, regardless of trash type, into the compost bin). Partial credit will be given for printing the top 10 user_id without their rank. Query 6: University has adopted Smart Waste Management and is replacing all its trash cans with smart bins. There are different kinds of users for the Smart Waste Management System such as App Users, Data Analysts, and Facility Managers. The campus management would like to control what data is accessible to which type of users by creating appropriate views and issuing appropriate GRANT permissions. They have asked you as database experts to help them by creating such views. They have three types of users: App Users, Sustainability Analysts, and Facility Managers. App Users: are interested in knowing the locations of various types of bins to throw away their trash. However, the campus only wants to show information of the bins which are not currently full (the last load observation for that bin is less than its capacity) and are located inside buildings. The current time is assumed as 2019-10-26 13:00:00. Create a view for App users, and the view should look like as follows: Waste bin id | x | y | Type of bin ---------------------------------------------------------------- 142 | 1000 | 1400 | Recycle Use the following queries to verify your answers: Select * from App_Users; Query 7: Continue with query 6. Now consider a new user as follows: Sustainability Analysts: are interested in learning the patterns of waste disposal by students on the university campus to promote sustainability among students. The security policy, however, requires that analyst should not be allowed to have access to data about exactly which student threw away the trash and what type of trash they disposed. They only need to know information about the bin’s location, the department names of the student, and the total weight of the trash thrown away by them in the bins. Create a view for Sustainability Analysts, and the view should look like as follows: Waste bin id | x | y | Department | Total weight | ----------------------------------------------------------------------------------------------- 183 | 100 | 100 | Computer Science | 100 | Use the following queries to verify your answers: Select* from Sustainability_Analysts; CS 331: Database System Design & Management Fall 2022 4 Query 8: Continue with query 6. Now consider a new user as follows: Facility Managers: are interested in keeping track of user’s waste disposal activities. They want to know what is their name, how many times each day they disposed off trash in each type of bin. Create a view for Facility Managers, and the view should look like as follows: Name | Day | Compost Bin | LandFill Bin | Recycle Bin | ------------------------------------------------------------------------------------------------------ ABC | 2019-10-01 | 10 | 20 | 5 | Use the following queries to verify your answers: Select* from Facility_Managers; Query 9: Our goal in this question is to write a trigger that replaces erroneous load sensor values by NULL when the erroneous values are inserted. Note that previous weight values recorded by the sensor that are not erroneous should not get replaced with null, only if any of the following conditions holds for a record before insertion, weight for that record should set to null and then get inserted. A load sensor value is considered to be erroneous if the following condition holds: Erroneous Sensor Detection Condition: If: (a) A load sensor value differs from the previous value of the same sensor by more than 1000 units. The value will be considered erroneous only if the previous value was recent, i.e., recorded in the last 24 hours. OR (b) There is a null reading already recorded by that load sensor. Checking if the previous value is null and if any values already generated by that sensor is null, will be accepted as correct solutions. (Hint: For finding the time difference use DATEDIFF function). After writing your trigger, run the following statements: INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350, 50001, 15000, '2017-07-07 20:00:55'); INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350, 50002, 15500, '2017-07-17 22:00:55'); INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350, 50003, 17000, '2017-07-18 20:45:55'); INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350, 50004, 17500, '2017-07-20 20:50:55'); Then run the following query and write the results. Is the result what you would expect? Make sure you include your results and explanations in your answers. Select * from LoadObservation where sensor_id = 350 and oid > 50000; CS 331: Database System Design & Management Fall 2022 5 Query 10: In this query, you will write a trigger that will record trash violations from object recognition observations. You will first need to create a trash violation table named TrashViolations with the attributes of TVID, user_id, timestamp, waste_bin_id, trash_type, that will be used to store the trash code violations. A trash violation happens when trash is thrown in the wrong bin. For example, if compost trash is thrown in the recycle bin or recyclable trash is thrown in landfill. Your goal is to write a trigger that executes when entries for a trash throwing event is inserted into the ObjectRecognitionObservations table, and inserts the violations appropriately in the TrashViolations table. [Hint: You will need WasteBin(and other related tables), LocationSensor, LocationObservation table as well, to create an entry for the TrashViolations table] After creating TrashViolations table and capacity_check trigger, run the following statements: INSERT INTO LocationObservation(sensor_id, oid, timestamp, X, Y) VALUES (1, 100001, '2017-11-15 14:00:00', 5459, 3576); INSERT INTO ObjectRecognitionObservation(sensor_id, oid, timestamp, trash_type) VALUES (354, 200001, '2017-11-15 14:00:00', 'LandFill'); Run the following query and write the results. Is the result what you would expect? Make sure you include your results and explanations in your answers. Select * from TrashViolations; Question 2: [10 points] How do we write the division operator over two tables in relational algebra, and prove/show the statement using two tables? Question 3: [20 points = 5 + 10 + 5] We have discussed self-join in the class. Create an example table or use the tables given in this assignment 1. Write an English statement over the table that require to execute self-join 2. Show the corresponding SQL query and write the SQL query here 3. Write the answer to the SQL query using a screenshot. Question 4: [20 points] What is TOAST in PostgreSQL and how does it work? -- Question 1 -- Query is written below. -- Question 2 -- Query is written below. -- Question 3 -- Query is written below. -- Question 4 -- Query is written below. -- Question 5 -- Query is written below. -- Question 6 -- Query is written below. -- Question 7 -- Query is written below. -- Question 8 -- Query is written below. -- Question 9 -- Query is written below. -- Question 10 -- Query is written below.