HW#2 --- last modified September 22 2023 20:06:23.

Solution set.

Due date: Oct 4

Files to be submitted:
  Hw2.zip

Purpose:

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO1 (Course Learning Outcome 1) -- Explain basic database concepts, including the structure and operations of the relational data model.

CLO3 Conduct normalization to decompose relations into 3NF or BCNF when that removes anomalies.

CLO4 Use SQL as a data definition language (DDL) to create and alter databases, tables, views, and indexes

CLO5 Use SQL as a data manipulation language (DML) for querying and modifying databases

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. Remember to include a readme.txt file listing your teammates.

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. Exercise 2.4.1 where you modify the data in Fig 2.20 and 2.21 as follows: halve and round up all the model numbers, add fifty, if that number already exists. i.e., 1001 becomes 501, 1002 becomes 551. Subtract 0.05 from each entry in the speed column, make all prices 20% higher, and if a color value was true make it false and vice-versa. For queries involving numbers reduce the number by 10%, in part (c) change the maker from B to C. for part (e) change to sell PC's, but not Laptops.
2. Exercise 2.4.2 for the modified queries as modified above in the first problem. In at least one case come up with an alternative tree that computes the same query.
3. Using the tables from problem (1) express the following constraints using the constraint formalism from class:
   1. Printer model numbers must be model numbers of Products using the constraint mechanism from class.
   2. A laptop with a screen size less than 15.7 inches must have at least a 110 gigabyte hard disk or sell for at most $900.
4. Make a relation with types representing the data on a California ID/Driver's License, including Licence No, Expiration, Last Name, First Name, etc. What FD's would you expect to hold for this relation? What are the keys for the relation?

Next for each of the relations in Problem 1 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 Mysql each of these tables with the primary keys and types you describe. Submit in a create.sql file your table creation scripts. Next insert the rows listed in this problem. Use \h to see how to do execute statements from a text file in Mysql. Submit your insert statements as a file insert.sql file . The files create.sql and insert.sql will be auto-evaluated by the grader's code so can get immediate feedback on them if you do a submission, even if the rest of Hw is not yet submitted. Submit in a transcript.txt file a transcript of your sessions where you are using these file to create and populate this database.