Spring 2007

Homework

**General Instructions:**

- The numbers refer to the exercises at the end of the listed section
in the textbook.
- TYPE your solutions. Grading will be based on correctness,
clarity, and whether your solution is of the appropriate length.
- Don't forget to acknowledge all sources of assistance.
Unless otherwise instructed, you must write up your homework
on your own. Turn in your homework together with this
cover sheet.
- Homework is always due at the
**beginning**of class.**Change in turn-in procedure:**Please turn in paper copy of the entire assignment plus cover sheet to class. Do *not* use the turn-in program. I apologize for the confusion. - You are encouraged to work extra problems: the more you work, the more you will learn. You can check your work with the starred ones (solutions are on the textbook webpage).

- Exercises for Section 2.1 (pp. 36ff):
- Ex. 2.1.1 (don't turn it in, just try it and then compare to the solutions available on the textbook's web page)
- Ex. 2.1.3
- Ex. 2.1.9

- Exercises for Section 2.2 (pp. 44ff):
- Ex. 2.2.1 (don't turn it in, just try it and then compare to the solutions)

- Exercises for Section 2.3 (pp. 53ff):
- Ex. 2.3.1 (b)

- Exercises for Section 2.4 (pp. 58ff):
- Ex. 2.4.2
- Ex. 2.4.4 (a)

- Exercises for Section 3.1 (pp. 64ff):
- Ex. 3.1.1 (don't turn it in, just try it and then compare to the solutions available on the textbook's web page)

- Exercises for Section 3.2 (pp. 75ff):
- Ex. 3.2.2
- Ex. 3.2.4

- Exercises for Section 3.3 (pp. 80ff):
- Ex. 3.3.1 (don't turn it in, just try it and then compare to the solutions available on the textbook's web page)
- Ex. 3.3.2 (a)

- Exercises for Section 3.4 (pp. 88ff):
- Ex. 3.4.1
- Ex. 3.4.5

- Exercises for Section 3.5 (pp. 100ff):
- Ex. 3.5.1 (don't turn it in, just try it and then compare to the solutions available on the textbook's web page)
- Ex. 3.5.2
- Ex. 3.5.10 (c) (look at solution for (a) for help)

- Exercises for Section 3.6 (pp. 117ff):
- Consider the relation schemas and FD's given in Ex. 3.6.1 (d) and (e).
(*correction: used to say Ex 3.6.7, but there is no such Ex.*)
For each of them, follow the process from the classroom exercise
on Sep 13:
- Compute all nontrivial and nonredundant FD's that follow from the given FD's. Compute the keys.
- Decompose the relation into collections of relations that are in BCNF (make sure it is clear in what order you are considering violating FD's). At each step, for each subrelation, clearly label the FD's and keys.
- Starting again with the original relation, decompose into 3NF.

- Consider the relation schemas and FD's given in Ex. 3.6.1 (d) and (e).
(*correction: used to say Ex 3.6.7, but there is no such Ex.*)
For each of them, follow the process from the classroom exercise
on Sep 13:
- Exercises for Section 3.7 (pp. 126ff):
- (Call this Exercise A) Suppose we have a relation R(A,B,C,D,E) with a MVD B ->-> E. If we know that the tuples (a1,b,c1,d1,e1), (a2,b,c2,d2,e2), and (a3,b,c3,d3,e3) are in the current instance of R, what other tuples do we know must also be in R?
- Ex. 3.7.2 (a) (don't turn it in, just try it and then compare to the solutions available on the textbook's web page)

- Exercises for Section 5.2 (pp. 207ff):
- Ex. 5.2.1, parts (a) through (h). For (a), (f), and (h), don't turn anything in - just compare your answers to the solutions.
- Ex. 5.2.2, parts (a) through (h). For (a), (f) and (h), don't turn anything in - just compare your answers to the solutions.
- Ex. 5.2.7. Don't turn it in, just compare your answer to the solutions.

- Exercises for Section 5.3 (pp. 220ff):
- Ex. 5.3.2

- Exercises for Section 5.4 (pp. 230ff):
- Ex. 5.4.1, all parts. For the starred parts, don't turn anything in, just compare your answer to the solutions.

- PREVIEW OF HW 4: For some of the exercises in Chapter 6, you are to make a MySQL database containing the data given in Figures 5.10 and 5.11. This will be excellent warm-up practice in using MySQL. Use your database to actually execute the queries that are asked for in the questions. I will assign some of the parts of Ex. 6.1.3, 6.2.2, 6.3.1, and 6.4.6 Details and due dates will be available later, however if you have time before Exam 1, you can start setting up your MySQL database for these exercises.

Make a MySQL database containing the data given in Figures 5.10 and 5.11. Write MySQL queries for all the exercises below and execute the queries on your database. Print out the console output (including your query and the result of the query), label the problem numbers, and turn in the hard copy.

- Exercises for Section 6.1 (pp. 252ff):
- Ex. 6.1.3, parts (c), (d), and (f)

- Exercises for Section 6.2 (pp. 262ff):
- Ex. 6.2.2, parts (c), (d), and (e)

- Exercises for Section 6.3 (pp. 274ff):
- Ex. 6.3.1, parts (b), (c), and (d). Note that each part requires you to write TWO DIFFERENT queries

- Exercises for Section 6.4 (pp. 284ff):
- Ex. 6.4.6, parts (b), (c), (d), (e), and (g)

- Exercises for Section 11.3 (p. 524):
- Ex. 11.3.1. Do all parts. Check your answers for (a), (c) and (d) against solutions on web - these will not be graded.

- Exercises for Section 11.5 (pp. 544ff):
- Ex. 11.5.1

- Exercises for Section 11.6 (p. 550):
- Ex. 11.6.1, (b) and (c)
- Ex. 11.6.2, (b) and (c)

- Exercises for Section 11.7 (pp. 561ff):
- Ex. 11.7.1
- Ex. 11.7.4 (b)
- Ex. 11.7.5 (b)
- Ex. 11.7.6

- Exercises for Section 12.2 (pp. 577ff):
- Ex. 12.2.2
- Ex. 12.2.4

- Exercises for Section 12.3 (p. 589):
- Ex. 12.3.10. Just read and understand the solution, don't turn anything in.

- Exercises for Section 12.4 (pp. 596ff):
- Ex. 12.4.3

- Exercises for Section 12.5 (p. 602):
- Ex. 12.5.2

- Exercises for Section 13.1 (pp. 620ff):
- Ex. 13.1.2
- Ex. 13.1.7 (a). Solution is on the web - you are to draw a diagram of the result.

- Exercises for Section 13.2 (pp. 630ff):
- Ex. 13.2.1. Just read and understand the solution, don't turn anything in.
- Ex. 13.2.4 (a). Just read and understand the solution, don't turn anything in.
- Ex. 13.2.6, (b) and (c). You may find it helpful to read the solution for (a).

- Exercises for Section 13.3 (p. 646ff):
- Ex. 13.3.1 (b)
- Ex. 13.3.5 (b), (c), (f) and (h). Describe the sequence of actions in each part. Draw new tree for (f) and (h).

- Exercises for Section 13.4 (p. 660ff):
- Ex. 13.4.1. Suppose (i) through (iv) occur in series (first (i), then (ii), etc.).

- Exercises for Section 15.2 (p. 732ff):
- Ex. 15.2.3

- Exercises for Section 15.3 (p. 737ff):
- Ex. 15.3.3 (a)

- Exercises for Section 15.4 (p. 748ff):
- Ex. 15.4.1 (a)
- Ex. 15.4.3 (a)
- Ex. 15.4.7 (b)

- Exercises for Section 15.6 (p. 763ff):
- Ex. 15.6.1 (b)
- Ex. 15.6.2 (b)

- Exercises for Section 16.1 (p. 795ff):
- Ex. 16.1.3 (b)

- Exercises for Section 16.2 (p. 809ff):
- Ex. 16.2.2 (d)
- Ex. 16.2.2 (b)

- Exercises for Section 16.3 (p. 820ff):
- Ex. 16.3.1 (b) (ignore part of the question about commutative and associative groups)
- Ex. 16.3.2 (b)

- Exercises for Section 16.4 (p. 834ff):
- Ex. 16.4.1 (c), (e), (f) and (h)

- Exercises for Section 17.1 (p. 883ff):
- Ex. 17.1.1 (b)

- Exercises for Section 17.2 (p. 895ff):
- Ex. 17.2.1 (b)
- Ex. 17.2.4 (c)

- Exercises for Section 18.2 (p. 930ff):
- Ex. 18.2.4 (b) and (d). You need not answer question (iii).

- Exercises for Section 18.3 (p. 938ff):
- Ex. 18.3.3 (b) and (d)

- Exercises for Section 19.1 (p. 1001ff):
- Ex. 19.1.2 (b) and (c)