Consider following relations: customer_order (oid: int, name: string, address: string) items (itemid: int, oid: int, ite

[correctanswer]

Consider Following Relations Customer Order Oid Int Name String Address String Items Itemid Int Oid Int Ite 1 (83.28 KiB) Viewed 61 times

Consider following relations: customer_order (oid: int, name: string, address: string) items (itemid: int, oid: int, itemname: string, quantity: int, itemPrice: float) The oid attribute is a primary key for the customer_order relation, and a foreign key for the items relation. The customer_order relation is 2500 disk blocks in size, with 80 records per block taking up 50 bytes each. The items relation is 8000 disk blocks in size, with 120 records per block taking up 33 bytes each. Assume that both relations are stored on disk as heap files, with no indexes available. I/O operations take 10ms, and 150 buffer pages are available for processing. (a) How long would it take to join the two files on the oid attribute using a block nested loops join? Show your working, and clearly indicate how the buffer pages are allocated. (6 marks) (b) Suppose that part of the customer_order relation has been read into memory. When matching tuples from the items relation, one approach would be to conduct a linear search through the entire in-memory portion of the customer_order relation, per tuple in the items relation. What is a disadvantage of this approach? Explain a more efficient approach for matching tuples in this case. Write approximately five or six sentences for your answer. (6 marks) (c) How long would it take to join the two files on the oid attribute using a hash join? Show your working. (4 marks)