Oregon State University Assignment 4 CS 540, Winter 2021
The assignment is to be turned in before Midnight (by 11:59pm) on March 2nd. You should turn
in the solutions to the written part of this assignment (questions 1 and 2) as a PDF file through
Canvas. These solutions should be produced using editing software programs, such as LaTeX or
Word, otherwise they will not be graded. You should turn in the source code to each
programming question (questions 3 and 4) separately through Canvas. Thus, each group will have
three distinct submissions in Canvas for this assignment. The assignment should be done in
groups of two students.
1: Query processing Algorithms (1.5 points)
Consider the natural join of the relation R(A,B) and S(A,C) on attribute A. Neither relations
have any indexes built on them. Assume that R and S have 80,000 and 20,000 blocks,
respectively. The cost of a join is the number of its block I/Os accesses. If the algorithms need to
sort the relations, they must use two-pass multi-way merge sort. You may choose the join
algorithms in your answers from the ones taught in the class.
(a) Assume that there are 10 blocks available in the main memory. What is the fastest join
algorithm for computing the join of R and S? What is the cost of this algorithm? (0.5 point)
(b) Assume that there are 350 blocks available in the main memory. What is the fastest join
algorithm to compute the join of R and S? What is the cost of this algorithm? (0.5 point)
(c) Assume that there are 200 blocks available in the main memory. What is the fastest join
algorithm to compute the join of R and S? What is the cost of this algorithm? (0.5 point)
2: Query processing (2 points)
(a) Assume that the entire of relation R(A,B) fits in the available main memory but relation
S(A,C) is too large to fit in the main memory. Find a fast join algorithm, i.e., an algorithm with
the lowest number of I/O access, for the natural join of R and S. Justify that your proposed
algorithm is the fastest possible join algorithm to compute the natural join of R and S. Next,
assume that there is a clustered index on attribute A of relation S. Explain whether or how this
will change your answer. (1 point)
(b) Consider relations R(A,B) an S(A,C) that each have 1 million tuples and are too large to fit
in main memory. A data scientist wants to compute 10000 (sample) tuples of the natural join of
R and S very fast. Since it is too time-consuming to compute the full natural join of R and S, the
data scientist selects 1% of relation R and 1% of relation S and computes their join. Explain
whether this algorithm returns the desired results. If it does not, propose an efficient algorithm
that returns the desired result without computing the full natural join of R and S. (1 point)
3: Sort-merge Join Algorithms (5.5 points)
(a) Consider the following relations:

作业将于3月2日午夜（晚上11:59）之前上交。你应该转
在此作业的书面部分（问题1和2）的解决方案中，将其作为PDF文件通过
帆布。这些解决方案应使用编辑软件程序（例如LaTeX或
话，否则他们将不会被评分。您应该将源代码上交给每个
通过Canvas分别编程问题（问题3和4）。因此，每个组将有
在Canvas中有3个与此提交不同的提交。作业应在
两个学生的小组。
1：查询处理算法（1.5分）
考虑关系R（A，B）和S（A，C​​）在属性A上的自然连接。
在其上建立任何索引。假设R和S具有80,000和20,000块，
分别。联接的成本是其块I / O访问的数量。如果算法需要
排序关系时，必须使用两次通过多路合并排序。您可以选择加入
课堂上讲解的答案中的算法。
（a）假设主存储器中有10个可用块。什么是最快的加入
R和S的连接的算法？这种算法的成本是多少？ （0.5分）
（b）假设主存储器中有350个可用块。什么是最快的加入
R和S的连接的算法？这种算法的成本是多少？ （0.5分）
（c）假设主存储器中有200个可用块。什么是最快的加入
R和S的连接的算法？这种算法的成本是多少？ （0.5分）
2：查询处理（2分）
（a）假设整个关系R（A，B）都适合可用的主存储器，但关系
S（A，C​​）太大，无法容纳在主存储器中。查找快速加入算法，即具有以下内容的算法
最少的I / O访问次数，以实现R和S的自然结合。请证明您的建议
算法是计算R和S自然连接的最快连接算法。接下来，
假设在关系S的属性A上存在聚集索引。请说明是否或如何
会改变你的答案。 （1分）代做CS 540程序语言
（b）考虑关系R（A，B）和关系S（A，C​​），每个关系有100万个元组并且太大而无法容纳
在主内存中。数据科学家想要计算的自然连接的10000个（样本）元组
R和S非常快。由于计算R和S的完全自然连接非常耗时，因此
数据科学家选择关系R的1％和关系S的1％并计算它们的连接。解释
该算法是否返回期望的结果。如果没有，请提出一个有效的算法
返回期望的结果而无需计算R和S的完全自然连接。（1分）
3：排序合并联接算法（5.5分）
（a）考虑以下关系：
1个

Oregon State University Assignment 4 CS 540, Winter 2021
Dept (did (integer), dname (string), budget (double), managerid (integer))
Emp (eid (integer), ename (string), age (integer), salary (double))
Fields of types integer, double, and string occupy 4, 8, and 40 bytes, respectively. Each block can
fit at most one tuple of an input relation. There are at most 22 blocks available to the join
algorithm in the main memory. Implement the optimized sort-merge join algorithm for
Dept ./Dept.managerid=Emp.eid Emp in C++.
• Each input relation is stored in a separate CSV file, i.e., each tuple is in a separate line and
fields of each record are separated by commas.
• The result of the join must be stored in a new CSV file. The files that store relations Dept
and Emp are Dept.csv and Emp.csv, respectively.
• Your program must assume that the input files are in the current working directory, i.e., the
one from which your program is running.
• The program must store the result in a new CSV file with the name join.csv in the current
working directory.
• Your program must run on hadoop-master.engr.oregonstate.edu. Submissions should also
include the g++ command (including arguments) that was used to compile the program.
Each student has an account on the hadoop-master.engr.oregonstate.edu server, which is a
Linux machine. You can use the following bash command to connect to it:
> ssh your_onid_username@hadoop-master.engr.oregonstate.edu
It will prompt you for your ONID password. You will need to be connected to the VPN in
order to access the server.
• You must name the file that contains the source code of the main() function main3.cpp. If
you place your source code in multiple files, you may submit all of them in a single zip file.
• You may use following commands to compile and run C++ code:
> g++ main3.cpp -o main3.out
> main3.out
4: External Memory Sorting (6 points)
(a) Consider the following relation:
Emp (eid (integer), ename (string), age (integer), salary (double))
Fields of types integer, double, and string occupy 4, 8, and 40 bytes, respectively. Each block can
fit at most one tuple of an input relation. There are at most 22 blocks available to the sort
algorithm in the main memory. Implement the multi-pass multi-way sorting for the relation Emp
in C++. The relation should be sorted by eid.
2
Oregon State University Assignment 4 CS 540, Winter 2021
• The input relation is stored in a CSV file, i.e., each tuple is in a separate line and fields of
each record are separated by commas.
• The result of the sort must be stored in a new CSV file. The file that stores the relation
Emp is Emp.csv.
• Your program must assume that the input file is in the current working directory, i.e., the
one from which your program is running.
• The program must store the result in a new CSV file with the name EmpSorted.csv in the
current working directory.
• Your program must run on hadoop-master.engr.oregonstate.edu. Submissions should also
include the g++ command (including arguments) that was used to compile the program.
Each student has an account on hadoop-master.engr.oregonstate.edu server, which is a
Linux machine. You can use the following bash command to connect to it:
> ssh your_onid_username@hadoop-master.engr.oregonstate.edu
It will prompt you for your ONID password. You will need to be connected to the VPN in
order to access the server.
• You must name the file that contains the source code of the main() function main4.cpp. If
you place your source code in multiple files, you may submit all of them in a single zip file.
• You may use following commands to compile and run C++ code:
> g++ main4.cpp -o main4.out
> main4.out
如有需要，请加QQ：99515681 或WX：codehelp