The minimum requirement for active attendance: 3 tasks done
The first exam will be as indicated: Nov 3rd at 15:00-18:00 in Porthania I. The requirements of the exam: pages 1-181 in lecture notes and exercises 1-5. (The corresponding parts in E&N will appear on the English course page by Monday, Oct 25th.) On week 44 (Nov 1-5) we have neither lectures nor exercise groups.
1. In the beginning of the chapters involved in indexing (E&N2, 5.1; E&N3, 6.1) there is a preliminary example of an index concept: a word index of a book. Let us suppose that the contents of a book and its word index are implemented as a relational database. Sketch the relations needed and give a SQL query to find out on which pages in a book there are some material about (a) cats, (b) cats and dogs. (Obs. The word index is not an index in a normal database meaning but implemented as ordinary relations.) 2. Consider creating a sparse ISAM index (see page 115 in E&N2, p. 168 in E&N3) for a set of data records with keys 3, 5, 7, 9, 11, 13, 15, 17, 19 and 21. We assume that a disk page (disk block) can hold two data records or three index records. At creation time, we use a 100 % fill-factor, that is, every data page is filled with two records. Show the index structure. Also explain how the record with key 15 is deleted from the index, and how a record with key 1 is inserted into the index. 3. We create index structures to the EMPLOYEE relation using the following (INGRES) commands: modify EMPLOYEE to isam on LNAME, FNAME create unique index XSSN on EMPLOYEE(SSN) modify XSSN to hash on SSN create index XDNO on EMPLOYEE(DNO) Explain the meaning of these commands. What kind of indexes are obtained and how can these be utilized? 4. Assume that the relation EMPLOYEE has index structures as given in the previous problem. What kind of record searches and updates are done in the following operations? insert into EMPLOYEE values (...) delete from EMPLOYEE where SSN = '123456789' update EMPLOYEE set SALARY = SALARY * 1.1 where SSN = '123456789' update EMPLOYEE set DNO = 4 where FNAME = 'John' and LNAME = 'Smith' 5. On page 168 of the lecture notes, the size of a sparse ISAM index for a file has been calculated when the (1 million) data records of the file occupy 50000 4KB-pages when stored in a heap file and the length of an index record is 20 B. In the sparse ISAM index the data records occupy 100000 pages because a 50 % fill-factor was used. Let us keep the data records in a heap and use a dense ISAM index instead of a sparse one. Estimate the size of this index, when we use a 50 % fill-factor at the leaf-level of the index. The upper levels of the index, of course, are filled 100 % full, because an ISAM index is a static structure. What is the size of the index if we use 80 % fill-factor? 6. Consider the database of problem 4 of homework 3 (Person, Dead, Married, Divorced). What kind of index structures would you create for the database relations? We require that the first query of this assignment (The names of Melanie Griffith's husbands and the associated wedding dates) be executed efficiently. Explain the principle of executing the query.