Question

Let us consider a university with three (3) located in Detroit, Flint, and Lansing.

Assume we have the following single relation (one record per student):

Student (ID, First_Name, Last_Name, Major, GPA, scholarship_amount)

We consider the following three kinds of queries are executed:

• Queries to enroll and add new students to the university (at any of the three campuses) are all executed at the Detroit campus.

• Queries to update student GPAs are all performed at the Flint campus.

• Queries to update scholarship amounts received by students are all performed at the Lansing campuses

We assume the following:

• Every semester, 1000 new students enroll at the university and have their records added to table

• Every semester, 9000 students have their GPAs updated in the table.

• Every semester, 500 students receive scholarships and have their scholarship amounts updated in the table.

We consider the following four scenarios:

1. The Student table is stored at the Detroit campus. Nothing is stored at the Flint and Lansing campuses.

2. The Student table is stored at the Flint campus. Nothing is stored at the Detroit and Lansing campuses.

3. The Student table is stored at the Lansing campus. Nothing is stored at the Flint and Detroit campuses.

4. The Student table is replicated at the Detroit, Lansing, and Flint campuses.

Let us consider the number of tuples send over the network as our cost.

1. Compare the cost of the four scenarios. Show the details of your work.

2. Which solution is the best?

Answer 1

1. The Student table is replicated at the Detroit, Lansing, and Flint campuses which will cost lesser than The Student table is stored at the one campus and Nothing is stored at the other two campuses. In the latter situation first the table will be sent to one college and latter that college will send to other two colleges . But in the previous case the table will be replicated to all the three colleges at the same time which ensures both cost and time efficiency.

2. The Student table is replicated at the Detroit, Lansing, and Flint campuses - Solution d is the best out of the scenarios.

Hope this helps you. For further clarification please add comments below. Please give an upvote. Thank you.