New version page

UCI CS 122A - Assignment 3

Upgrade to remove ads

This preview shows page 1-2 out of 6 pages.

Save
View Full Document
Premium Document
Do you want full access? Go Premium and unlock all 6 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 6 pages.
Access to all documents
Download any document
Ad free experience

Upgrade to remove ads
Unformatted text preview:

CS 122A: Introduction to Data Management – Assignment 3Fall 2022Note: This assignment has only one part.Deadline : Oct 31 11:59 PMRelational Algebra QueriesFor answering the following questions, use the relations provided at the end of this assignment.Express in relational algebra the following queries. If it can not be expressed using relational algebra,then explain why. A file containing sample RELAX (https://dbis-uibk.github.io/relax/landing) dataset isprovided (relax_data_assignment3.txt). To help you in writing the relational algebra queries, we haveprovided the expected output on the sample dataset for each query. The order of the results does notmatter except when explicitly specified in the question.Please make sure the relational algebra queries are able to run on RELAX using the datasetprovided.1. Find all pairs of faculty members and graduate students where the member of facultyteaches a class that the student is enrolled in and they are both part of the sameresearch group.teacherstudent_id27221942. Find out the names of students connected to access point ap4 between times 1 and 11.name'Lillith_Mcneil''Cally_Sexton''Brady_Perry'3. Find the names of all department chairs who are teaching a coursename'Quintessa_Greene''Brennan_Gill''Leroy_Peck'4. List graduate student IDs who were present in a region in which a research group islocated, but the students were not part of that research group.Evaluating presence: If a user connected to an access point, they are considered to have beenpresent in the region that the access point is placed in for the duration of the connection.Connected.user_id48112328355. Find the total capacity of Building_Services_Bldg.building_namecapacity'Building_Services_Bldg'256. List the student ID, name and GPA for all students in the Sociology department advisedby Byron_Larsen.user_idnamecgpa20'Keane_Juarez'1.17. Find the number of faculty members in the Computer_Science department per researcharea.research_areafaculty_count'R4'2'R2'18. List the class names and their timings for classes held atEnv_Health__Safety_Services_Facility and start before time 20 on a Monday.namestart_timeend_time'C2'29'C6'17249. List the users who have visited all regions in Mesa_Arts_Building.Evaluating visits: If a user connected to an access point, they are considered to have visited theregion that the access point is placed in for the duration of the connection.user_id13610. [Ungraded - for practice only]a. Write the following query in two ways, one where the selection is done before the join andonce where it is done after:Find the departments of members of faculty interested in the area R4.What is the difference in the two ways and how would that affect the performance of thequery in the presence of a large number of records?Submission1. Please submit an appended pdf file with your answers to Relational Algebra Queries. Thename of the pdf file should be the last names of each team member placed together. Forexample if Donald Chamberlin and Peter Chen were teammates, they would submit:chamberlin_chen_assignment3.pdf.2. Upload pdf to Gradescope. Only one member of your team is required to submit the file. Besure to identify all the team members in the pdf file (name and student ID).Relational ModelNote: The relational model is part of the solution for Assignment 2. However, we have made somemodifications for interesting queries and removed some relations that were not involved in anyqueries.General rules:1. Underlined attributes are primary keys. Primary keys are not NULL.Building(building_name)Floor(building_name, floor_name, department_name)All non key attributes are not NULL.Floor(building_name) Building(building_name)⊆Floor(department_name) Department(dept_name)⊆Region(building_name, floor_name, region_name)Region(building_name, floor_name) Floor(building_name, floor_name)⊆Room(building_name, floor_name, region_name, room_name, capacity)All non key attributes are not NULL.Room(building_name, floor_name, region_name) Region(building_name, floor_name, region_name)⊆Office(building_name, floor_name, region_name, room_name)Office(building_name, floor_name, region_name, room_name) Room(building_name, floor_name,⊆region_name, room_name)Lab(building_name, floor_name, region_name, room_name)Lab(building_name, floor_name, region_name, room_name) Room(building_name, floor_name,⊆region_name, room_name)LectureHall(building_name, floor_name, region_name, room_name)LectureHall(building_name, floor_name, region_name, room_name) Room(building_name, floor_name,⊆region_name, room_name)Department(dept_name, start_date, phone_no, chair)Department(chair) Faculty(user_id)⊆User(user_id, name)All non key attributes are not NULL.Faculty(user_id, research_area)All non key attributes are not NULL.Faculty(user_id) User(user_id)⊆FacultyDepartment(user_id, dept_name)FacultyDepartment(user_id) Faculty(user_id)⊆Faculty(user_id) FacultyDepartment(user_id)⊆FacultyDepartment(dept_name) Department(dept_name)⊆Student(user_id, dept_name, gpa)All non key attributes are not NULL.Student(user_id) User(user_id)⊆Student(dept_name) Department(dept_name)⊆Undergraduate(user_id)Undergraduate(user_id) Student(user_id)⊆Graduate(user_id)Graduate(user_id) Student(user_id)⊆ResearchAssistant(user_id, advisor)All non key attributes are not NULL.ResearchAssistant(user_id) Graduate(user_id)⊆ResearchAssistant(advisor) Faculty(user_id)⊆TeachingAssistant(user_id)TeachingAssistant(user_id) Graduate(user_id)⊆Quarter(year, session, start_date, end_date)All non-key attributes are not null.Classes(class_id, teacher, name)All non-key attributes are not null.Classes(teacher) Faculty(user_id)⊆ClassEnrollment(class_id, student_id)ClassEnrollment(class_id) Classes(class_id)⊆Classes(class_id) ClassEnrollment(class_id)⊆ClassEnrollment(student_id) Student(user_id)⊆TAAssignment(class_id, ta_id)TAAssignment(ta_id) TeachingAssistant(user_id)⊆TeachingAssistant(user_id) TAAssignment(ta_id)⊆TAAssignment(class_id) Classes(class_id)⊆Classes(class_id) TAAssignment(class_id)⊆ResearchGroup(name, area)Not null attributes: All non key attributes are not nullInclusion Attributes: N/AResearchGroupRegion(name, building_name, floor_name, region_name)Not null attributes: All non key attributes are not nullInclusion Attributes: ResearchGroupRegion(Name) ResearchGroup(Name)⊆ResearchGroupName(building_name, floor_name, region_name) Region(building_name,⊆floor_name,


View Full Document
Download Assignment 3
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Assignment 3 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Assignment 3 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?