CS630 Homework 2  Getting started with mysql, foreign keys, SQL

due Tueday Feb. 28 by midnight in your cs630/hw2 directory.Updated parts are highlighted in yellow.

1. Getting started with mysql:

Note: The load files for mysql were edited on Monday, Feb.13 to fix a compatibility problem preventing the flights table from being created and the loads working on pe07, which has mysql 5.7. So copy at least dbbook_mysql.sh and crflights_mysql.sql from $cs630/tables to your own tables directory to bring it up to date. Also, several references to topcat have been fixed to read "pe07".

a. Check your mysql account by simply logging into it. For user joe, login to pe07 from users or topcat and do the command:
mysql -u joe -D joedb -p
Password: joe

Or if you don't use -D joedb on the command line, use "use joedb" once inside mysql. Use "show tables" to look for tables. Unless you used this account for other classes, you should see an empty set of tables. If you do see old tables and want me to reinitialize your account, let me know by email.

You already have copied the $cs630/tables directory to your own ~/cs630/tables directory as directed in homework 1. Again cd to cs630/tables and use "ls" to list the files (while logged in on either system). You should see dbbook_mysql.sh. In order to load tables with bulk load (rather than individual inserts, a slower process), we need to set up easy no-password access to mysql, as described in the comments in dbbook_mysql.sh. For example, user joe creates file .my.cnf in his login directory with contents:

Since this lists your password, it should be protected as a file: "chmod o-r .my.cnf" makes it unreadable to other users. We will check for the existence and protection of this file. Now check that you can log into mysql without a password:
mysql -u joe -D joedb

b. Load the R&G book's tables into your mysql account by executing "sh dbbook_mysql.sh" on pe07. You will be prompted for your mysql username and database name (joe and joedb, for example). When it is done, check your tables by using mysql on pe07 with "show tables" and  "select * from student;", etc.

c. Drop the tables by using
mysql -u joe -D joedb < dropdb.sql

Use "show tables" to see the result, then load the tables again. This words for Oracle as well.

d. In your homework paper, report success or continuing problems in access to mysql, or trouble loading the tables. Make a typescript showing just "select * from boats" output. To do this, log into pe07, run the "script" command, then use mysql to access your tables and do the select, then exit from mysql and then exit again, which finishes the script in file "typescript". Rename typescript to "dbbook_test.txt" in your hw2 directory. Although ".txt" doesn't mean anything to the Linux OS, it indicates a text file to most users because of its Windows meaning.

2. Foreign Keys. R&G Exercise 3.8  Note that these tables are in tables/createdb.sql, and you can change their create-tables temporarily for experiments if you wish, and just reload the database afterwards. Put this answer in problem2.txt in your hw2 directory.  It would be a good idea to test your SQL by putting your solution in a .sql file and running it, but that is not required for the homework. For part 6, consider the Legal department instead of the Toy department, since it shows up in our loaded data.

3. Null foreign keys. Note that createdb.sql's dept table has a nullable managerid. Show that because of this, you can insert a row with a null managerid and have a department without a real manager, even though we have a foreign key constraint on managerid. Put your insert statement into insertnull.sql, and show that file and results on Oracle and (optionally) mysql (of select * from dept) in problem3.txt in your hw2 directory. In practice, we prefer not null foreign key columns to avoid this behavior. Sometimes a null FK is appropriate, in the case that the referenced information is optional or not available yet when this row is inserted. For example, the nullable column fid in table class is explained by needing to plan courses ahead of time, when the teacher is not yet assigned.

SQL Exercises: Execute on Oracle. Optionally, also execute them on mysql and put results in problem4.mysql_script, for example. In problem 4, the lack of INTERSECT and EXCEPT may make it impossible to do the query in mysql without a subquery.

4. SQL without subqueries. Our first coverage (Lectures 4 and 5) was of queries we can do in Relational Algebra without using division. Do the queries of Exercise 4.3, parts 1-4 and 9-11 in SQL without using subqueries (i.e., your answer must have only one SELECT in it, plus one more for each UNION or INTERSECTION or EXCEPT/MINUS in use. There are some SQL answers in the online solutions, but most have subqueries. You may use UNION and INTERSECTION or EXCEPT (or MINUS) here.  Create a script problem4.sql with these queries, in order. problem4.sql is supplied with the first problem solved. Follow the directions in that script, and provide the output in problem4.txt. You can copy problem4.sql to your current directory on cs.umb.edu by "cp /courses/cs630/public_html/problem4.sql ."

5. SQL with subqueries if desired. Do the queries of Exercise 5.2, parts 1, 4, 5 and 11 in SQL and create a script problem5.sql (following the pattern of problem4.sql) and its output in problem5.txt. For problem 11, output the sname as well as the pname and cost.

6. SQL with group by, having. Create a script problem6.sql (following the pattern of problem4.sql) and its output in problem6.txt.

a. Exercise 5.2, part 10.

Then use the schema of Exercise 5-1 (but change student's level to standing to match our createdb.sql) to do these:

b. Find the major and the average age of students for that major, for each major. List major and average age.

c. For each room that has a class, find how many different departments use that room for classes.List room and count of departments.

d. Display results from c. only for rooms used by 2 or more departments.

d. For each class, list how many students are in each major. List class name, major, and count of students

7. SQL with more advanced features if needed. Do the queries of Exercise 5.4 after adding dname to Dept in the schema (it is this way in createdb.sql). Do parts 1-4 in SQL and create a script problem7.sql (following the pattern of problem4.sql) and its output in problem7.txt.

Files for grading in cs630/hw2:

problem2.txt (no computer output here)
insertnull.sql, problem3.txt
problem4.sql, problem4.txt
problem 5.sql, problem5.txt
problem6.sql, problem6.txt
problem7.sql, problem7.txt