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
- 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.
- 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.
- 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.
- Draw an E-R diagram for emp-dept-works tables of created.sql and
explain why the diagram should be drawn that way.
- 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.
We looked at the flights table in class and determined it wasn’t
Problem 4 Review of SQL
- Draw the E-R diagram of flights-aircraft-emplyees-certified as
- Normalize the database and draw the new E-R diagram. Show the
changed/new create table statements.
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.
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.