CS157a Fall 2019Practice Midterm 1

Studying for one of my tests does involve some memorization. I believe this is an important skill. Often people waste a lot of time and fail to remember the things they are trying to memorize. Please use a technique that has been shown to work such as the method of loci. Other memorization techniques can be found off the Wiki Page for Moonwalking with Einstein. Given this, to study for the midterm I would suggest you:

• Know how to do (by heart) all the practice problems.
• Go over your notes at least three times. Second and third time try to see how much you can remember from the first time.
• Go over the homework problems.
• Try to create your own problems similar to the ones I have given and solve them.
• Skim the relevant sections from the book.
• If you want to study in groups, at this point you are ready to quiz each other.

The practice midterm is below. Here are some facts about the actual midterm: (a) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (b) You should bring photo ID. (c) There will be more than one version of the test. Each version will be of comparable difficulty. (d) One problem (less typos) on the actual test will be from the practice test.

1. Define the following database related concepts (0.5pts each) and give an example (0.5pts each): (a) database, (b) universe of discourse, (c) information integration, (d) concurrency control manager.
2. List (0.5 each) and explain (0.5pts each) the ACID properties of a transaction.
3. Write down a relational schema for a blogging website that has at least four relations (1pt). Give example tuples for each relation (1pt). For one of these relation write down all the possible superkeys (0.5pt) and explain your answer (0.5pts). Give the SQL necessary to create tables corresponding to your four relations (1pt).
4. Give the SQL needed to: (a) Add a column when of type DATE to a CALENDAR_ITEM table (1pt), (b) remove the table USELESS from the current database (1pt), (c) create a table FOO with a column MONEY that can contain 10 digit numbers with two decimal places (1pt), (d) Create a table GOO with integers columns A and B where A is the primary key and B is a key (1pt).
5. Write relational algebra queries to compute each of the following: (a) Columns A, B from those tuples that are in R but not in either S or T (1pt), (b) First names of all EMPLOYEES(ID, FNAME, LNAME, SALARY) whose salary is less than 20000 that WORKS_ON(EMP_ID, PNAME) project X (1pt), (c) EMPLOYEES with at least two DEPENDENTS(EMP_ID, CHILD_NAME), (d) those tuples (a,b,c) such that (a,b) is in R(A, B) and (b,c) is in S(B,C). (1pt).
6. Show that every inclusion constraint `R subseteq S` can be expressed and an emptiness constraint `R' = \emptyset` and vice versa (1pt each direction). Define referential integrity constraint (1pt). Given an example of expression a referential integrity constraint using an inclusion constraint.
7. Suppose we have the relation R(A,B,C,D,E,F) with functional dependencies `AB ->C`, `BC->D`, `E->FE`, `FD->F`, `B->A`. Which of these FDs is trivial? (1pt) For each trivial FD give a proof of it from the emptyset of additional FDs using Armstrong's axioms (2pts). Compute the minimal basis for this set of FDs using the algorithm from class that was part of our projection algorithm (1pt).
8. Define (0.5pt each) and give an example (0.5pts each) of the following concepts: (a) Deletion anomaly, (b) Update anomaly, (c) BCNF, (d) lossless join decomposition.
9. Consider the table Bookings(title, theater, city) subject to the constraints `theater -> city`, `ti\t\l\e\, city -> theater`. Show (step-by-step (0.5pts/step) how our BCNF Decomposition would decompose this table into tables in BCNF (2pts). Show step-by-step (0.5pts/step + 0.5 for clarity) how our 3NF synthesis algorithm would work for the same set of FDs.
10. Define (0.5pt each) and give an example (0.5pts each) of the following concepts: (a) 2NF, (b) dependency preservation property, (c)multivalued dependency, (4) 4NF.