CS634 – Homework 1

Released Jan. 29, Due Mon., Feb. 8

Relationships, Creating and Loading Databases, Generated PKs (15 points)

 

Instructions: The homework is due BEFORE CLASS on the duedate. Please hand in paper copies (either typeset or hand-written copies are fine, as long as the hand writing is clear). You also need to create files which you must place in your UNIX course directory for CS634 in directory hw1.

Problem 1

1. Copy the tables directory from $cs634 (/data/htdocs/cs634) to your cs634/hw1 directory, ending up with directory cs634/hw1/tables. Use its contents to load the sample databases from the book into your Oracle and mysql accounts.  Report any problems (other than the expected ones involving .bad files shown in the script output typescript.dbs2) by Wed., Feb. 19.
2. Determine why Oracle fails to load the three rows shown in the .bad files on the handed-out run.  Explain both the catalog and suppliers rows—there are different reasons.  Examine how mysql handles these three rows: any output related to them in the load?  What do they look like when loaded?  Briefly summarize the root cause of the difference between how the two databases handle these loads.
3. Edit createdb.sql by changing one digit of one number so that the loads on Oracle and mysql work without error and load the same data. Explain in your homework what that edit was.

Problem 2

1. Draw an E-R diagram for emp-dept-works tables of created.sql and explain why the diagram should be drawn that way.
2. Draw an E-R diagram for the sailors-reserves-boats tables of text pg. 134 and create table reserves on pg 166. Explain why the diagram should be drawn that way.

Problem 3

We looked at the flights table in class and determined it wasn’t normalized.

1. Draw the E-R diagram of flights-aircraft-emplyees-certified as supplied.
2. Normalize the database and draw the new E-R diagram.  Show the changed/new create table statements.

Problem 4 Review of SQL

Exercise 5.2 in the textbook, except use the table schemas in createdb.sql, slightly different. Try the queries out on Oracle and mysql by writing a file queries.sql and then executing it on both databases, leaving their output in queries-oracle.txt and queries-mysql.txt in your cs634/hw1 directory, along with queries.sql of course. Show queries.sql in your homework paper. If any query needs relational division, you can say so and skip the SQL, since there are no division examples using SQL in the text to help you. It's very tricky to do division in SQL. Examples of division queries are given on pg. 116 in the relational algebra coverage, specifically Q9 and Q10.

Problem 5 Generating Ids

Create a table with abstract schema:

Employee (eid:integer, ename:string, age:integer, salary:USmoney)

a. The exact types used are up to you, as long as they can do the job and are portable. The eid should be generated by auto_increment on mysql and by a sequence on Oracle.  Note that neither of these features are covered by SQL-92, so portability isn't expected. The create table statement is different in the two cases. Write create_employee_oracle.sql and create_employee_mysql.sql for the two cases, where the Oracle file creates the sequence as well as the table.  Create one data file  employees.txt that works for both cases with 3 employees. Write a shell file employees_oracle.sh and employees_mysql.sh that builds and loads the table, and drop_employees_oracle.sql and drop_employees_mysql.sql that drop the table and sequence. All the files should be in your hw1 directory. Show the create_employee_* and employee_* files in your homework paper, along with a script of runs that shows the loaded table.

b. Discuss how to do the id generation portably, within SQL-92. You need another table.