C M S I 4 8 6
IN T R O D U C T I O N T O DA T A B A S E SY S T E M S
Fall 2005
Assignment 1013
OK, you asked for it, so here it is — homework that makes you hit the computer (uh, figuratively, not
literally). This assignment tests your ability to translate what you’ve done mathematically into good old
SQL code. You’ve definitely seen enough of it to be able to figure your way through this, even though
we haven’t formally covered it in class yet, so here goes.
Not for Submission
You will probably need to refer (heavily) to Chapters 3 and 4 in SKS (latest edition; for other editions,
it’s whatever chapters deal with SQL and advanced SQL).
For Submission
Submit your work by e-mail and hardcopy as psql-ready SQL text files. By “psql-ready,” I mean that I
should be able to take your text files and pass them right into psql without any problems. To save time,
the first part is a group assignment, and the second part should be individualized. Thus, you may turn in
a single file for (1), then one file each for (2).
1. Do this together — in particular, you might want to divvy up the sample data work and paste
them together in the end: Write a sequence of SQL commands that creates then populates the
database described in the previous assignment — make sure to figure out how to specify
when an attribute is a primary key and when an attribute is a foreign key (including what
primary key that foreign key refers to). Your sample data should have at least 20 employees
and at least 5 companies. For the works and manages “relationship relations,” at least two
employees must work for each company, and every manager must have at least two under-
lings. Test your SQL file by sending it into an empty database via psql. The net result
should give you the desired tables with the data that you provided.
2. Do this individually — by this time, you should all have your sample database creation and
loading file done, and you can then break out and work on your own: Convert your relational
algebra expressions from the previous assignment into their equivalent SQL queries, and put
those together in another SQL text file. Remember that you can use white space liberally,
and two dashes “– –” indicate a comment — use those to make your SQL scripts more read-
able. Again, you should be able to test your SQL file by having psql read it; the query results
should also give you an idea of whether you got the queries right.