Fall 2004 CS 186 Discussion SectionII. Storing Data - Disks & FilesFIFOLRULIFOMRUCLOCKS2Q (Simplified 2Q)F2Q (Full 2Q)Fall 2004 CS 186 Discussion Section Exercises - Week 2 ending 9/10 I. RELATIONAL MODEL 1) Design relational tables to represent the following information. a. A company has employees working in departments. b. Name, age, salary and manager are employee information. c. Name, budget and chief manager are department information. Use simple data types (INTEGER, CHAR). 2) Write CREATE TABLE statements for creating the schema. 3) Rewrite the CREATE TABLE statements and establish primary key and foreign key constraints. 4) How to ensure that EmpName field will not be null?5) How to ensure that EmpId field will not be a negative integer? 6) How to ensure that Manager field in Employee is also an Employee. Further Exercises: 7) How to ensure that nobody can be his or her own boss? 8) How to ensure that EmpId and DepId always have 6 digits? II. STORING DATA - DISKS & FILES 1) Why is the access method used by disks is partly random and partly sequential? 2) Which among the following are best suited for databases and why? Answer in one or two sentences. a. RAM b. Hard disk c. Magnetic tape d. CD-RW 3) Operating Systems provide support for random access files. Isn’t that sufficient for a DBMS to store its data on a disk? Justify your answer with a couple of reasons.4) Why does a DBMS need buffer management when an Operating System can provide virtual memory? 5) What is a frame and what is a page? 6) Number of frames in a DBMS buffer pool will be: a. Always greater than number of pages in disk. b. Must be equal to number of pages in disk. c. Typically less than the number of pages in disk. 7) What does pinning and unpinning a frame mean? 8) When two transactions modify distinct portions of a frame, should the dirty flag be set to 2? Justify your answer in one sentence. 9) What will happen when all the frames in a buffer pool are pinned by one or more transactions and a transaction makes a new request for a new page to be loaded from the disk? 10) Assume that a database has 5 pages and 4 frames. Assume that LRU is the page replacement policy. Which of the following sequences of page references will cause a sequence flooding? a. 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, … b. 5, 4, 3, 2, 1, 5, 4, 3, 2, 1, 5, 4, 3, 2, 1, … c. 1, 3, 5, 2, 4, 1, 3, 5, 2, 4, 1, 3, 5, 2, 4, … d. 1, 2, 3, 4, 5, 4, 3, 2, 1, 2, 3, 4, 5, 4, 3, …11) Assume that a database has 4 pages and 3 frames. Assume that you start with an empty buffer pool. In each of the following tables, a. The page reference vector is specified in the top row. b. The frame numbers are specified in the left most column. c. The value in (i, j) empty cells should represent the page id in the i’th frame when the j’th page reference is made. d. The bottom most row should represent whether a page fault occurred or not (y/n). e. Assume that pages are unpinned immediately after they are pinned. Fill in the empty cells in following tables for each of the page replacement policies mentioned. FIFO Frames V V Read Page >> 2 3 1 2 3 4 4 3 2 1 2 3 4 1 2 A B C Page Fault? Total number of page faults = LRU Frames V V Read Page >> 2 3 1 2 3 4 4 3 2 1 2 3 4 1 2 A B C Page Fault? Total number of page faults = LIFO Frames V V Read Page >> 2 3 1 2 3 4 4 3 2 1 2 3 4 1 2 A B C Page Fault? Total number of page faults =MRU Frames V V Read Page >> 2 3 1 2 3 4 4 3 2 1 2 3 4 1 2 A B C Page Fault? Total number of page faults = CLOCK Frames V V Read Page >> 2 3 1 2 3 4 4 3 2 1 2 3 4 1 2 A B C Page Fault? Total number of page faults = 12) Similar to the above problem, do the following assuming a buffer size of 4 frames and disk space size of 5 pages. For Simplified 2Q assume that the A1 and Am queues have a threshold of 50% of buffer size each. S2Q (Simplified 2Q) Frames V V Read Page >> 2 3 1 4 3 4 5 3 5 1 2 A B C D Page Fault? Total number of page faults =For Full 2Q assume Kin is 25% of the page slots and Kout should hold as many identifiers for as many pages as would fit on 50% of the buffer. F2Q (Full 2Q) Frames V V Read Page >> 2 3 1 4 3 4 5 3 5 1 2 A B C D Page Fault? Total number of page faults
View Full Document
Unlocking...