See the web page for homework guidelines, due dates, and policies.
Note that this homework is worth 50 points. The next homework will be
worth 150 points.
(15 points) Textbook, Exercise 12.4, Parts 1, 2, 3, and 6.
(10 points) Textbook, Exercise 14.8, Parts 1, 2, 3, and 5.
(15 points) Textbook, Exercise 16.4, Part 1.
(10 points) Textbook, Exercise 16.8, Instead of Parts 1, 2, and 3, answer the
following: Suppose your initial design was a heapfile per relation,
with an unclustered hash index on Emp.eid, an unclustered hash index on
Dept.did, and a dense unclustered B+Tree index on Emp.(did,sal) (i.e., a
2-field search key). This enables an index-only plan to be used for the
second query, so those users think you are wonderful. The users who ask
the first query, though, are upset with its performance, and will punish
you by making you administer SQL Server 6.5 systems for the rest of your
life unless you improve their query's performance. Help them by
giving a new physical design (with one relation no longer in BCNF).
Describe any new indexes and list other tradeoffs with your previous approach
(i.e., the approach given above).
If any indexes from the previous approach are no longer needed, say so.
Indicate the normal form for each of your physical relations.