CSE 544
Homework 1
Due Wednesday, April 14

The purpose of this assignment is to become familiar with SQL and relational databases.

Tools for the assignment

For this assignment, you will need the database we have created. This database is available in MS Access form at http://www.cs.washington.edu/education/courses/544/CurrentQtr/homeworks/hw1.mdb

To use the database, we recommend either InterDev or MS Access, both of which should be installed by default on any NT machine. InterDev will be under Visual Studio, and Access will be under MS Office. InterDev offers better visualizations and is closer to standard SQL, but it’s a little more complicated. If you can't get InterDev to work, Access should be fine. For both, you should be warned that you may not be able to do some more complicated SQL things.

For those of you running Linux, this assignment should not take long, so you're probably better off either running hydra or going to a lab, but you're welcome to try any Linux database out there. We have not tried any, but you can get a DB2 beta version for Linux at http://www.software.ibm.com/data/db2/linux. You can get a comma delimited version at http://www.cs.washington.edu/education/courses/544/homeworks/CurrentQtr/hw1.tar.gz to use with it

Using InterDev

Start up InterDev, and choose a new Database Project (under the "Visual Studio" option). Click "Okay". It will then ask what kind of Data Source you want to use. Click the "File Data Source" tab (since you'll be using a local file rather than a database running on another machine), and select "MS Access 97 Database (not shareable).dsn" Click okay, and then find the database in the dialog box.

The main InterDev window will have a number of windows, including one that says "Data View" (if you accidentally lose this window, you can bring it back by going to the "View" menu and selecting "Other Windows à Data View"), where you’ll see your project name, and a list of tables. Double click on one of the tables (you will not be able to use the Query toolbar unless one of the tables is selected), and then pull up the "Query Toolbar" by going to the "View" menu and selecting "Toolbars à Query". If it has a check next to it, it’s already been pulled up. It may be at the far right of your screen; it looks like this:

Click on the SQL box, and it will pull up a box for you to enter your SQL query. To execute the query, click on the "!" button. The other views may be helpful to figuring out what is going on, particularly the "Diagram Pane" button.

To make a view, create a query that creates the view in normal SQL syntax

Using Access

To use Access, start it up, and select "Open an Existing Database". Find the hw1.mdb where ever you have stored it, and click on it and "ok".

This will bring up a window which will have "hw1: Database" at the top. In it there are tables, queries (or views) and other things. To create a new query, click on the "queries" tab, then create a "new" query. Request the "Design View" which will pop up a prompt for which table you want to use. Add them as desired (you can add more later), and then click close. You should now be looking at a window that says "Query1: Select Query" or something like that.

You should do most of your query designing in SQL, but feel free to play with the other views. To change to SQL, right click in the window and choose "SQL view". Type in your query, and click on the "!" at the top of the screen to run the query.

You will now see a window with the answers to the query. To go back to designing the query, right click and choose "SQL view" again.

To save the query (Access’ way of creating a view), try to close it, and when it asks if you want to save it, say yes.

Caveats about Access

• Access uses * as its wild card character in LIKE statements as opposed to %

Additional assignment information

• Several things may be unclear on the slides; first, "group by" and "order by" are both two words. Second, for most implementations of SQL, rather than using the doublequote(") to match with a specific string, they will use the single quote (') Both have been changed on the slides linked to on the web
• Some versions of IE and Netscape have trouble saving the database correctly. If you have trouble (access will probably tell you that the file is not an access database), you can either get it on the NT machines on \\rfilesrv2\students\rap\cse544\hw1.mdb, or on the unix machines as /cse/www/education/courses/544/99sp/homeworks/hw1.mdb

The Schema

Our database is filled with data about the graduate students and professors of the department (if you don’t see yourself there, consider if you were prompt in entering yourself in the Who’s Who database). There are four relations, "Advise", "Faculty", "Offices" and "WhosWho", with the following attributes:

Advise:

• Userid (the userid of the student)
• Advisor

Faculty:

• URL
• Firstname
• Lastname
• Interest
• email

Offices: (of graduate students)

• Last
• First
• Email
• Office
• Telephone

WhosWho: (of graduate students)

• Userid
• Year (of entry)
• Birthday

Answer the following questions. For each answer, provide the answers, the SQL query you used, and any assumptions you made. Please note that your query must answer the question exactly. If the answer asks for a count of something, return that count, etc; we do not want duplicates in the answers. If you create a view, please include the view definition in your answer. The questions are (roughly) in order from least to most difficult.

1. What areas are our faculty interested in?(we are looking for the set of everyone's interests)
2. How many students are in the WhosWho relation?
3. How many students in the WhosWho relation went to Duke University?
4. What’s the userid of the student has been here the longest, and when did he enter?
5. Which universities does the department have more than two students from, and how many are from each?
6. How many different schools do we have graduate students from?
7. Which students in the same office came from the same undergraduate school?

   It's okay to have just pairs of the students; just don't list the pairs more than once. For example, if abc, def, and ghi all went to the same undergraduate school in were in the same office, your answer would look something like:

   abc	def
   abc	ghi
   def	ghi

   But you wouldn't have both "ghi abc" and "abc ghi".

8. Which school has the most students from it?
9. What are the userids of students who entered before 1993 and are not advised by someone interested in OS
10. What is an interesting result (not listed) involving at least one join?