I have attached it.
CS 331: Database System Design & Management Fall 2022 1 CS 331: Database System Design and Management Fall 2022 Assignment 2 Important instructions: MUST read before answering the assignment. 1. No questions will be answered on 11/12 and 11/13. 2. Email your questions to the TA: Anshu Singh (
[email protected]) and cc the instructor (
[email protected]). 3. This is the second assignment. You will get one more assignment. 4. A student can form a group of size at most two. 5. 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. 6. Points for each question are written in front of each question. 7. All assignments must be submitted to canvas on the due date. 8. Assignments should NOT be handwritten. Students may use Microsoft Word, Google Doc, or Overleaf. Handwritten assignments will be returned without grading them. 9. 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. mailto:
[email protected] CS 331: Database System Design & Management Fall 2022 2 10. Grading of assignments: In the final grade, an average of the best of the two assignments will be taken. For example, if one student receives the following points in the above three assignments: 100, 90, and 60 respectively, then only the following points will be considered 100 and 90 (points in the 3rd assignment will not be counted). To include the assignment points in the final grade, we do the following: (100+90)/10 = 19. 11. The grade of this assignment will be published before Nov 30th. 12. Due date: Monday, Nov 14th 9:00 AM. 13. Late submission of the assignment will be allowed for the given reasons. Each day will cause a deduction of 10 points, even if you give a reason, unless there are some health issues. However, after Nov 15th, assignments will not be accepted and result in zero point. Without reason, late submissions are not allowed. 14. No restriction on the number of pages to answer. 15. If you do not understand any question or if there is any typo, please let me know. 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 https://www5.njit.edu/policies/sites/policies/files/academic-integrity-code.pdf CS 331: Database System Design & Management Fall 2022 3 Question 1 [5+5 Points]. The following option is identical: 1. = SOME is identical to IN clause The following option is not identical: 1. <> SOME is not the same as NOT IN Prove the above statements. You may take an example of a table to prove this. Question 2 [18 Points (3 points each)]. Assume that the average seek time is 10 milliseconds and rotational latency is 6 milliseconds. Consider the following disk configuration: a. Sector size = 1024 bytes b. The number of tracks = 4000 at each surface c. The number of sectors = 100 per track d. Disk contains multiple platters e. Block size = 1024 bytes Consider the following for the database: a. A file with 200,000 rows b. Row size = 200 bytes. Each row is of the same size. c. A row exists in a single block only. Now answer the following questions: 1. How many rows will fit in a block? 2. How many blocks are needed to keep the entire file? 3. The file is arranged sequentially on the disk. Now, how many cylinders are needed? 4. How many records of 100 bytes each can be stored using the disk given in this question? 5. What time is required to read a file containing 100,000 records of 100 bytes each sequentially? CS 331: Database System Design & Management Fall 2022 4 6. What is the time required to read a file containing 100,000 records of 100 bytes each in a random order? Question 3 [72 Points (8 points each)]. 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 + 1 point to write the same query on the answer sheet = 7 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_assignment2.sql” and just copy- paste into MySQL. 1. Find all pairs of faculty members and graduate students where the member of faculty teaches a class that the student is enrolled in and they are both part of the same research group. Hint: you will get two rows. 2. Find out the names of students connected to access point ap4 between times 1 and 11. Hint: you will get 3 rows. 3. Find the names of all department chairs who are teaching a course. Hint: you will get 3 rows. 4. List graduate student IDs who were present in a region in which a research group is located, but the students were not part of that research group. Hint: Evaluating presence: If a user is connected to an access point, they are considered to have been present in the CS 331: Database System Design & Management Fall 2022 5 region that the access point is placed in for the duration of the connection. You will get 6 rows. 5. Find the total capacity of Building_Services_Bldg. Hint: you will get 1 row. 6. List the student ID, name and GPA for all students in the Sociology department advised by Byron_Larsen. Hint: you will get 1 row. 7. Find the number of faculty members in the Computer_Science department per research area. Hint: you will get 2 rows. 8. List the class names and their timings for classes held at Env_Health__Safety_Services_Facility and start before time 20 on a Monday. Hint: you will get 2 rows. 9. List the users who have visited all regions in Mesa_Arts_Building. Hint: you will get 2 rows.