Assignment 2 questions PDF

Preview

Summary

Fall 2017..
assignment 2 (SQL assignment)...

Description

CS630 Homework 2

Getting started with mysql, foreign keys, SQL

due Monday Oct. 10 by midnight in your cs630/hw2 directory. 1. Getting started with mysql: a. Check your mysql account by simply logging into it. For user joe, login to 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: [mysql] user=joe password=joe 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 mysql> b. Load the R&G book's tables into your mysql account by executing "sh dbbook_mysql.sh" on topcat. 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 topcat 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 student" output. To do this, log into topcat, 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.

2. Foreign Keys. R&G Exercise 3.8 Note that these tables are in tables/createdb.sql, but you may change their create-tables if you think you have a better setup for part 3. 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. 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. Put your insert statement into insertnull.sql, and show that file and results on Oracle and 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. 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.script. You can copy problem4.sql to your current directory on cs.umb.edu by "cp /data/htdocs/cs630/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.script. 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.script. 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.script....