Advanced Databases / Data Distribution System Use the data distribution scheme f
ID: 3701020 • Letter: A
Question
Advanced Databases / Data Distribution System
Use the data distribution scheme for a distributed University example described below.
A University has multiple locations across a wide geographic area (large city?). There are servers located at each of the five campuses (North, South, East, West, and Main campuses).
Each of the campuses is connected to the University network. The servers at each site contain the following tables: Student, Faculty, Class, and Enroll. The primary copy of the Enroll table is on the Main campus server; secondary copies of the Enroll table exist on the North, South, East and West campus servers.
1) Describe two strategies for this query: Find the ClassNumber and the names of all students enrolled in all classes with schedule MWF9.
2) Describe two strategies for this query: Find the ClassNumber, facId of the teacher, and the campus of all classes being taken by RosemaryHughes, who is a student at the North campus.
3) Consider the query "Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus." The query is entered at the North campus. Describe a strategy that does not use the semijoin.
4) Consider the query "Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus." The query is entered at the North campus. Describe a strategy that does use a semijoin.
North Site StudentNorth Faculty North Enroll North (secondary) Class Network East Site Studentpast FacultyEast Enrollast (secondary) Class West Site StudentWest Faculty West Main Site StudentMain FacultyMairn Class Enroll (primary) EnrollwWest (secondary) Class South Site Studentsouth Facultysouth Enrollsouth (secondary) ClassExplanation / Answer
1) Describe two strategies for this query: Find the ClassNumber and the names of all students enrolled in all classes with schedule MWF9.
Strategy1:- SELECT CLASS, STUDENT FROM MAIN_SITE WHERE ENROLLED = 'MWF9';
Strategy2:- SELECT CLASS, STUDENT FROM NORTH_SITE WHERE ENROLLED = 'MWF9' UNION
SELECT CLASS, STUDENT FROM WEST_SITE WHERE ENROLLED = 'MWF9' UNION
SELECT CLASS, STUDENT FROM EAST_SITE WHERE ENROLLED = 'MWF9' UNION
SELECT CLASS, STUDENT FROM SOUTH_SITE WHERE ENROLLED = 'MWF9';
2) Describe two strategies for this query: Find the ClassNumber, facId of the teacher, and the campus of all classes being taken by RosemaryHughes, who is a student at the North campus.
Strategy1:- SELECT CLASS.CLASSNO, FACULTY.FACID, CLASS FROM NORTH_SITE WHERE STUDENT.NAME = 'RosemaryHughes';
Strategy2:- SELECT CLASS.CLASSNO, FACULTY.FACID, CLASS FROM NORTH_SITE JOIN MAIN_SITE ON NORTH_SITE.STUDENT.STUDENTID = MAIN_SITE.STUDENT.STUDENTID AND NORTH_SITE.STUDENT.NAME = 'RosemaryHughes';
3) Consider the query "Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus." The query is entered at the North campus. Describe a strategy that does not use the semijoin.
SELECT CLASS.ClassNumber, Student.LastName, Class.Schedule, Enrolled from North_Site where Faculty.Name = 'Professor Smith' and Enrolled = TRUE. This query does not contain any semijoin query structure.
4) Consider the query "Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus." The query is entered at the North campus. Describe a strategy that does use a semijoin.
SELECT CLASS.ClassNumber, Student.LastName, Class.Schedule, Enrolled from North_Site where Faculty.Name IN (SELECT Faculty.Name from NORTH_SITE Where Name = 'Professor Smith') AND ENROLLED = TRUE; This query uses a semijoin structure.
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.