This
work is important to me. Solve it correctly, without mistakes, without
plagiarism. Follow the instructions. There is no chance to repeat it.
Therefore, you must do a complete and correct work 100%.If you are sure you can do it, accept it
College of Computing and Informatics
Assignment 1
Deadline: Tuesday 03/10/2023 @ 23:59
[Total Mark for this Assignment is 8]
Student Details:
Name: ###
ID: ###
CRN: ###
Instructions:
• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.
• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).
• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the
cover page.
• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.
• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.
Question One
Pg. 01
Learning
Outcome(s): LO1
Recognize
database file
organization and
indexing.
Question One
1.5 Marks
Suppose the size of the file that we are trying to sort is 512 disk blocks and
the available buffer space in main memory = 4 disk blocks. Calculate the
following:
1- The number of initial runs (nR) [0.75 mark]
2- The number of passes (nP) [0.75 mark]
Question Two
Pg. 02
Learning
Outcome(s): LO3
Question Two
2.5 Marks
Optimize the following SQL Query by using Query Tree and Heuristic Rules:
Analyze
algorithms for
query processing
SELECT C.Fname, E.Fname
FROM Employee AS E, Customer AS C, Order AS O
WHERE E.EMP_ID=O.E_ID AND C.City=’Jeddah’ AND
C.Customer_ID=O.C_ID AND E.EMP_ID=’12943’
You should show the Initial Query Tree and add a screenshot of the
Query Tree after applying each rule. [0.5 for each correct step]
Question Three
Pg. 03
Learning
Outcome(s): LO1
Recognize
database file
organization and
indexing.
Question Three
2 Marks
How are the Primary and Clustering indexes defined and how they are
different in the way they are defined? [0.5]
What are the similarities between the two? [0.75]
Why can we have at most one primary or clustering index on a file, but several
secondary indexes? [0.75]
Question Four
Pg. 04
Learning
Outcome(s): LO1
Recognize
database file
organization and
indexing.
Question Four
2 Marks
A file has a dense secondary index with blocking factor equal to 64 index
entries per block. If the total number of blocks for the first index level is 1024
blocks,
a. What is the total number of blocks needed for the second and third level
indexes? [1 Mark]
b. How many blocks need to be accessed to access a record in this three
level index? [1 Mark]
