CS157a Spring 2002Practice Midterm 2
The practice midterm is below. Here are some facts about the actual
midterm: (a) The midterm will be in class . (b) It is
closed book, closed notes. Nothing will be permitted on your desk except
your pen (pencil) and test. (c) You should bring photo ID.
(d) There will be more than one version of the test. Each version
will be of comparable difficulty. (e) If your cell-phone or beeper
goes off you will be excused from the test at that point and graded
on what you have done till your excusal. (f) One problem (less typos)
on the actual test will be from the practice test.
1. Give a short definition of each of the following terms:
(a) bulk transfer rate
(b) data striping
(c) shadowing
2. Suppose 10000 Employee records are stored in a file ordered by SSN.
The block size for the file is 2048 bytes and each record is 112 bytes.
The records are stored in an unspanned fashion. Calculate:
(a) The blocking factor for the Employee records.
(b) The number of blocks in the file.
(c) Average number of block accesses to look up a record using an SSN.
3. Suppose we have an external hash table with 3 buckets and each bucket
can hold two records. Show after each step what the hash table would look
like as records with the following key values:
7, 3, 5, 4, 0, 10
are loaded into the hash table using h(K) = K mod 3.
4. Explain the difference between a sparse index and a dense index.
Which of the following indices is which: (a) a primary index,
(b) a clustering index, (c) a secondary index on a key attribute.
5. Suppose the blocking factor for index entries is 50 entries/block.
How many first many first level entries can a
multilevel index with 3 levels have if the top level consists of one block?
6. Calculate p and p_leaf for a B+ - tree given that a block
has 2048 bytes, the search key field is 7 bytes long, a record pointer is
8 bytes long and a block pointer is 7 bytes long.
7. Define the following Relational Model concepts:
(a) first normal form assumption
(b) candidate key
(c) cascading delete operation
8. Show the following two sets of relational algebra operations are
of equivalent expressive power:
{ select, projection, union, difference, cartesian product} and
{ projection, join, union, difference }
9. Given a relational algebra expression which returns the
name of each Department, together with the number of
Employee's who work in that department.
10. Give the SQL commands to do each of the following:
(a) Retrieve the FNAME and LNAME of all Employee's who work
on Project X and whose SALARY is greater than 100000.
(b) All columns of all Employee's who are in the MATH Department
together with all columns of all Employee's who are in the
CS Department.
(c) Insert the tuple ( 1, 2, 3) into the table T.
For this problem recall the Company DB from the book. This DB has the tables:
Employee(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX SALARY, SUPERSSN DNO),
Works\_On(ESSN, PNO, HOURS),
Project(PNAME, PNUMBER, PLOCATION, DNUM),
and Department(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE).
|