HW#2 --- last modified September 27 2019 21:40:59.

Solution set.

Due date: Oct 2

Files to be submitted:
  Hw2.zip

Purpose: To gain experience with the relation model and SQL CRUD operations. To learn a little bit about DBMS utilities.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO1 (Course Learning Outcome 1) -- Write relational algebra queries and predict given a database instance what such a query will return.

CLO2 -- Write SQL commands to create databases, CReate tables, insert/Update/Delete/retrieve rows in a common database management system.

CLO3 -- Use a database management system's bulk loader to populate a database.

Specification:

This assignment consists of two parts a written assignment and a (SQL) coding assignment. Files for both should be submitted in the Hw2.zip file you submit.

For the written exercises, I want you to do the following problems as modified from the class text (you only have to do the parts without exclamation marks):

1. 2.4.3. As part of this problem, I want you to create an instance of your WW II ships database with at least eight tuples/relation they give. I.e, use the relation names they have, but don't use the instances they provide in the text, make up your own tuples, and make sure you make up at least 8 tuples/relation. As part of your solution to each part show how the relational algebra query you gave would evaluate its result on the tuples you listed. By show, I mean show at least some intermediate calculations for the query based on relational algebra expression you gave.
2. 2.4.4. In at least one case come up with an alternative tree that computes the same query.
3. 2.5.2. Modify (a) so that the bore must also be at least 10 inches. For (b) change 9 to 7 and 14 to 12. Do the problem with respect to your data above not the book's data.
4. 3.1.2.

Next for each of the relations in Exercise 2.4.3 suggest which columns would make a good primary key. Also, suggest SQL types for each of the columns. Write this up in Hw2.pdf. Then create in sqlite each of these tables with the primary keys and types you describe. Insert the rows listed in this problem. Use .help to see how to do this from a text file in sqlite. Submit in transcript.txt a transcript of your sessions to create and populate this database.