Your goals for this assignment are to enhance your knowledge of relations, use the relational algebra, create a database, practice making simple SQL queries using SimpleDB and DERBY, and help you understand the concept of functional dependency and keys in relational tables. This homework is not hard, but it will take you several hours. Start working on it ASAP.
1. Use the data in Figure 2.20, p.53 and represent the tuples of makers G and H as a relation using set notation.
2. Start the SimpleDB server and create the tables for a “shipsDB” database. To do this, copy the CreateStudentDB code and rename it CreateShipsTables. Modify it to create the Classes, Ships, Battles and Outcomes tables. Turn in screen shots of the Eclipse console window showing the output of the server console window (after also running the modified client), the output of running the modified client code and a listing of your CreateShipsTables code.
3. Run the SimpleDB client SQLInterpreter.java and explain the output of the following commands. select SId, Sname from student; //order of the names in the projection?
select EId from section, enroll; //which table is scanned in the inner loop?
select EId from enroll, section;
select Title, SectId from course, section where CId=CourseId and Prof=‘turing‘;
select SName,GradYear from student, dept where MajorId = DId and DId=20;
select SName,GradYear from student, dept where MajorId = 20; //same as previous?
4. Work textbook exercise 2.4.3 (p.55) by the writing relational algebra using MS Word equation editor for parts b, c, and e. Also draw an expression tree for parts c and e.
5. Work textbook exercise 2.5.2 (p.63) part b.
6. Write SQL statements for DERBY ij for exercise 2.4.1 (p.52) parts a, b, and c. Turn in the ij console input and output showing your SQL and its output.
7. Load the following ships data into the shipsDB using SimpleDB with the SQLinterpreter.java program. Turn in a screen snapshot of the console output to show that you did this.
8. A. Define the term Functional Dependency as it applies to attributes in a single table. Give an example using the ENROLL table discussed in class.
B. What makes a functional dependency trivial?
C. Assume a relation R(A,B,C,D) with primary key AB is in 1st normal form, but not 2nd normal form. Give at least three potential FDs that determine only part of a row.
D. What if R above is in 2nd normal form, but not 3rd normal form. Give an example FD that prevents R
from being in 3rd normal form.
E. What if R has a key AB. What FD must be true for R to be in BCNF?
F. Assume R has the FDs: A→ ABCD and C→D, the last of which causes a violation of 3rd normal form. Decompose R into two relations R1 and R2 that are in 3NF. What attributes of R are in R1 and what attributes of R are in R2?
G. SQL permits arithmetic like Select A*D from R; Given that information and R1 and R2 from part F, write a select statement using JOIN that returns A*D.
9. Exercise 3.2.2, b and c, for part i relation S(A,B,C,D), p.83
10. Given R(A,B,C,D,E) and FDs: A→B, BC→E and DE→A, explain why ABCDE, ABCD and BDE are not candidate keys, and why ACD is (See p. 72).
11. Exercise 3.5.2, p. 105, is relation COURSES(C,T,H,R,S,C) in 3rd normal form? BCNF? Explain.
12. Using the SQL in #2 and #7 above, create the shipsDB using Derby. Turn in a screen shot of a side-by–side comparison of the shipsDB folder using SimpleDB vs the shipsDB folder using Derby. What’s the main take away about DBMS file storage after your inspection of the two folders?