What is an access path?

Given two files A and B and each file include 3 pages (each page contains 4 entries only) as shown as the following: A: , ,] B: , , ] Assume you have 3 buffer pages in the main memory.            Show the 2-way merge sort (sort each page separately in the first pass, and then merge in the subsequent passes) procedure for the given example.     2. Compute the I/O cost for this procedure (rounding the value up)

Assuming that you have a three-frame buffer pool. Please show the buffer content given the workload listed in the table below using LRU and MRU strategies. We start from T1 and moves forward by one on each page reference. For your solution, underline the letter in the frame in the buffer pool each time that memory access caused a “miss” in the buffer pool (i.e., a page is read that is not currently in the buffer pool), when the page is put in the buffer pool. When the buffer pool has unused slots (such as at the beginning, when all slots are empty), it will put newly read data in the first unused slot.  The pages to be read from the disk are labeled A through D.  For each access, the page is pinned, and then immediately unpinned. Assume one page on disk fits perfectly with one frame on the buffer pool.   Time Page Read T1 A T2 B T3 C T4 D T5 B T6 C T7 D T8 C T9 D T10 A    1. In your answer, please create two buffer pools, one for LRU strategy and one for MRU strategy. You could create a similar buffer pool by click to table icon   Buffer Pool LRU strategy Time Frame 1 Frame 2 Frame 3 T1   A (example)     T2       T3       T4       T5       T6       T7       T8       T9       T10         MRU strategy Time Frame 1 Frame 2 Frame 3 T1   A (example)     T2       T3       T4       T5       T6       T7       T8       T9       T10         2. Which strategy is working better in this scenario? Please justify your answer.  

Our university database, consider the following relations: Course(cname: string, meetsAt: string, room: string, fid: int); Faculty(fid: int, fname: string, deptId: string) And consider the following query         SELECT f.fname,          FROM    Course c, Faculty f         WHERE  c.fid = f.fid and c.meetsAt =’TuTh 9:30-10:45’ and f.deptId =’COMPSCI’; You are given the following information: T(Course) = 10000 records T(Faculty) = 3000 records B(Course)  = 300 pages B(Faculty)  = 100 pages V(Faculty, deptId) = 50 V(Course, meetsAt) = 100 You always used the block-based nested loop join M = 50 (pages) (we already excluded 1 output buffer and 1 input buffer for Block-based nested loop join so please use 50 pages in the calculation of join cost) First question: (15 points) Here are four different logical query plans. Identify two plans by their labels (a,b,c or d) that do the following: (i) one query plan would join two tables first, then perform selection on meetsAt and deptId, then display the name of faculty; (ii) the other query plan would modify the query plan mentioned in (i) by applying push selection rule.  To earn full credit, please justify your answer. (a)   (b) (c)  (d)    Second question (45 points) Compute the I/O costs for the query plans in (i) (20 points) and (ii) (20 points) . Which plan is better and why (5 points)  

Consider a relation R with five attributes X, Y, Z, W, V. You are given the following dependencies: {X -> Y, YZ-> W, X->Z} List all candidate keys for R. Please justify your answer.   Is R in 3NF? Please justify.   Is R in BCNF? Please justify.

Which of the following statements about relational algebra equivalence rules is correct?

Which one of the following statements about normal forms is FALSE?

A disk with sector size being 1024 bytes, 2000 tracks per surface, 50 sectors per track, 5 double-sided platters and average seek time: 10msec. Which of the followings is NOT a valid page size?

Consider the following relations:  Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, price: real) Write the following queries in SQL and translate each of them into two equivalent relational algebra:  a. Find the names of suppliers who supply all blue parts. b. Find the names and pids of parts that have a price less than 200 dollars So the deliverables should be two SQL statements and 4 relational algebras. For relational algebra, use the following notation:  Selection: 

Which of the following statements is FALSE when it comes to discuss why I/O costs are important in DBMS