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 ( XXXXXXXXXX) and cc the instructor
( XXXXXXXXXX).
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.
. 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
eadability 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: XXXXXXXXXX
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 answe
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 ‘ XXXXXXXXXX:00:00’ and ‘ XXXXXXXXXX:00:00’
Query 2: Find the Inside bins (bins inside the buildings) that are used by Visitors between ‘ XXXXXXXXXX:00:00’
and ‘ XXXXXXXXXX:00:00’
Query 3: Find the distinct names of all the students who used a recycling bin inco
ectly (put a wrong item in the
ecycling bin) at least once between ‘ XXXXXXXXXX:00:00’ and ‘ XXXXXXXXXX:00:00’ . Remember that
ObjectRecognitionSensor records a trash_type which can be used to determine if someone inco
ectly 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
in)
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
y 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 cu
ently full (the last load observation for that bin
is less than its capacity) and are located inside buildings. The cu
ent time is assumed as XXXXXXXXXX: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 | XXXXXXXXXX |
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 | XXXXXXXXXX | 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 e
oneous load sensor values by NULL when
the e
oneous values are inserted. Note that previous weight values recorded by the sensor that are not e
oneous
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 e
oneous if the
following condition holds:
E
oneous 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 e
oneous 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 co
ect 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, ' XXXXXXXXXX:00:55');
INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350,
50002, 15500, ' XXXXXXXXXX:00:55');
INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350,
50003, 17000, ' XXXXXXXXXX:45:55');
INSERT INTO LoadObservation(sensor_id, oid, Weight, timestamp) VALUES (350,
50004, 17500, ' XXXXXXXXXX:50:55');
Then run the following query and write the results. Is the result what you would expect? Make sure you include your
esults 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
in 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, ' XXXXXXXXXX:00:00', 5459, 3576);
INSERT INTO ObjectRecognitionObservation(sensor_id, oid, timestamp, trash_type)
VALUES (354, 200001, ' XXXXXXXXXX: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 alge
a, and prove/show the statement using two
tables?
Question 3: [20 points = XXXXXXXXXX]
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 co
esponding 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.