CS157a Fall 2023Practice 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 universe of discourse, (b) database, (c) information integration, (d) ACID properties of a transaction.
2. What is the semi-structured data model? Give the three facets of a data model needed to specify the semi-structured data model.
3. Write down a relational schema for a shopping cart website that has at least three 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 three relations (1pt).
4. Suppose we have relations: Movies(title, year, length, genre, studioName, producerC#), MovieExec(name, address, cert, netWorth), and StarsIn(movieTitle, movieYear, starName). Express the following using the relational algebra: (a) The title and year of each movie that is strictly between 100 and 150 minutes long, (b) the names of movie exec who produced a movie with Sylvester Stalone in it, (c) the referential integrity constraint that values for producerC# in Movies must come from the values of the cert column of MovieExec.
5. Give examples of the following: (a) two tables each of at least 4 rows, whose natural join is equal to their Cartesian product, (b) two tables with 4 rows each whose join is 1 row, (c) a join with a dangling tuple.
6. Use the closure algorithm from class to show step by step how to compute `{A,B}^+` assuming we have attributes `A,B,C,D,E` and FDs `BC->AE`, `B->C`, `AD->E`.
7. Suppose we have the relation `R(A, B, C, D, E)` on which the following FDs hold: `{A->BCE, C->D, B->A}`. Decompose `R` into a DB schema in BCNF using the algorithm from class showing important intermediate steps.
8. Define and give an example of the following concepts: (a) Deletion anomaly, (b) Update anomaly, (c) 1NF, (d) redundancy in tabular data.
9. Define (0.5pt each) and give an example (0.5pts each) of the following concepts: (a) 2NF, (b) lossless join property, (c) multivalued dependency, (4) 4NF.
10. Show how to represent the following items in an ER diagram with an example: (a) an entity with two attribute, one of which is a key, (b) a one-to-many relationship, (c) a recursive relationship, (d) a ternary relationship.