Database Management Systems IT 344

( Instructions: This Assignment must be submitted on Blackboard via the allocated folder. Email submission will not be accepted. You are advised to make your work clear and well-presented , marks may be reduced for poor presentation . You MUST show all your work . Late submission will result in ZERO marks being awarded. Identical copy from students or other resources will result in ZERO marks for all involved students. Convert this Assignment to PDF just before submission. ) ( Student Details: Name: ### CRN : ### ID: ### )

College of Computing and Informatics

Year 2020, You are hired as a database developer in a newly established university. You are required to computerize the existing students information system. The details are as follows:

· Currently enrolled students "r" : 25,000.

· Block size "B" of disk to store data is 512 bytes.

· Record pointer "P" size is 7 bytes

· Each record is of fixed length with following attributes.

Your task is to:

a) Calculate number of file blocks "b" to store data of all the students.

b) Calculate umber of index blocks bi , if the file is ordered by StudentId and a primary index is constructed on same field.

c) show how binary search algorithm with primary indexing would be a better choice than binary search algorithm without primary indexing, (by compare the average cost in terms of block accesses)

[ Note: You may need to calculate some intermediate values to get final results ]

Consider the following Employee relation:

Construct a bitmap index on the attribute salary, dividing salary values into 2 ranges: below 40000, 40000 to below 60000.

( 4 Points/20 ) ( Learning Outcome(s): Explain how RAID technology improves reliability and performance. )Question Three

Explain how RAID technology improves reliability and performance.

( 3 Points/20 ) ( Learning Outcome(s): Discuss the reasons for converting SQL queries into relational algebra queries prior to optimization. )Question Four

Discuss the reasons for converting SQL queries into relational algebra queries prior to optimization.

( 4 Points/20 ) ( Learning Outcome(s): Draw query trees for SQL queries. )Question Five

Draw a query tree for the following relational algebra query.

sid, sname, age(age<30(color="Red"(bid=bid(B x sid=sid)S x R)))))

Where:

S represents Sailors (sid, sname, rating, age)

B represents Boats (bid, bname, color)

R represents Reserves (sid, bid, day, rname)