Lab 7: Exploring SQL (II)

Due: You will do this lab in class on Wednesday, November 21. You need not turn anything in.

• Better understand joins.

Collaboration: Please work in groups of 2-3. At least one member of each group should have their Wiki-20 project handy (we're going to use it as a sandbox for experimentation).

• Preparation
• Exercises
• Exercise 1: SQLObject's ForeignKey
• Exercise 2: SQLObject's MultipleJoin
• Exercise 3: Foreign keys and joins in SQL
• Exercise 4: Many-to-many relationships in SQL
• Exercise 5: SQLObject's RelatedJoin
• Explorations

Preparation

Open a terminal windows (or tabs).  In one of them, run tunnel-mysql. In another, find your Wiki-20 directory.

You may also wish to have the readings for today open in your web browser.

If you have not done so, you may wish to finish the first SQL lab through exercise 5.

Exercises

Exercise 1: SQLObject's ForeignKey

1. In model.py, add a Person class that inherits from SQLObject.  Person should have two Unicode columns: name and homepage.
2. Start tg-admin shell and tell the Person class to create its table. Create some instances of Person. Quit the shell.
3. We want each link to have a creator. In model.py, add the following to the Link class:
   

     creator = ForeignKey('Person', name="creator")

4. We also need to add the corresponding database column. Start a MySQL client as follows. You will be prompted to enter your MySQL password.
   

   mysql -u <username> -p -h 127.0.0.1

5. Use your database.
6. Create an integer column for the link's creator id:
   

   ALTER TABLE link ADD creator_id INT;

7. Quit mysql and start tg-admin shell again. Verify that this worked by first creating a new person, and then creating a link which has that person as its creator. Once you are satisfied, quit python for the time being.

ercise 2: SQLObject's MultipleJoin

1. In model.py, add a MultipleJoin column to the Person class as follows.
   

     links = MultipleJoin('Link', joinColumn='creator_id')

2. Run tg-admin shell again.
3. For each link, print the creator (to verify that what we did in Exercise 1 still works).
4. To verify that the MultipleJoin works, try typing
   

   Person.get(1).links

5. Quit python.

Exercise 3: Foreign keys and joins in SQL

1. Start a MySQL client as follows. You will be prompted to enter your MySQL password.
   

   mysql -u <username> -p -h 127.0.0.1

2. Select the appropriate database to use.
3. What do you expect to see in the description of the link table? Type desc link; to test your hypothesis.
4. What do you expect to see in the description of the person table? Type desc person; to test your hypothesis.
5. Write a select statement to find all the links that have the person with id 1 as their creator.
6. What do you expect the result from select * from link, person; to be? Try it and see. (The result is called a cross join because it is the cross-product of the two tables.)
7. Now add a WHERE clause to the preceding select statement so that only the rows where the person is the link's creator are selected.
8. Modify the statement you just wrote so that you only select the href, the description, and the person's name (leaving out the id's and the person's home page.)

Exercise 4: Many-to-many relationships in SQL

1. In the mysql shell, create a new table named tag as follows.
   

   CREATE TABLE tag (
   id		INT AUTO_INCREMENT PRIMARY KEY,
   tagphrase  	text
   );

2. Type desc tag; to verify the table schema is correct. Insert a few tags into the table (for example, add a tag where the phrase is "CSC223"), and then select all rows from the tag table to check your work.
3. Now we'll add the join table. The table should be named link_tag and it should have two int columns: one named link_id and one named tag_id. If a tag describes a link, then a row should appear in the table with that link's id and that tag's id.
   

   Here is the result of describing that table after it has been created; write your own statement with to create the table. Write your own CREATE TABLE statement to create this table.  Then, use desc link_tag; to verify that it worked. Here is an example description:

   mysql> desc link_tag;
   +---------+---------+------+-----+---------+-------+
   | Field   | Type    | Null | Key | Default | Extra |
   +---------+---------+------+-----+---------+-------+
   | link_id | int(11) | YES  |     | NULL    |       |
   | tag_id  | int(11) | YES  |     | NULL    |       |
   +---------+---------+------+-----+---------+-------+
   2 rows in set (0.00 sec)

4. Now you can relate tags to links. Insert a few rows with appropriate tag and link ids into the link_tag table.
5. What do you think the following will do? Give it a try.
   

   SELECT href, tagphrase FROM tag, link, link_tag 
   WHERE link.id = link_tag.link_id
     AND tag.id  = link_tag.tag_id;
   

6. Write a query to select all tags corresponding to a particular link.
7. Write a query to select all links corresponding to a particular tag.
8. Quit mysql when you are done.

Exercise 5: SQLObject's RelatedJoin

1. In model.py, add a Tag class. Its columns should correspond to those of the tag table we created in the last exercise.
2. Start tg-admin shell.
3. Type Tag.get(1) to verify that the Tag class works. You shouldn't need to use Tag.createTable() to create the table; why not?
4. Quit the shell. 
5. Now we need to add a RelatedJoin to the Tag and the Link classes so that SQLObject knows they are related. In model.py add a RelatedJoin property to the Link class:
   

     tags = RelatedJoin('Tag')

6. Add a similar line to the Tag class and save your work.
7. Start tg-admin shell again. Verify that each link object has a tags attribute (with the appropriate values) and that each tag object has a links attribute.
8. Note that I carefully chose names for the link_tag table that exactly match the names SQLObject would have used itself. What if we hadn't been so careful? How would we get the RelatedJoins to use different table and column names?

Explorations

If you still have some time, switch to your project database (USE databasename) and explore the joins in your project.

Or, explore the use of ORDER BY, GROUP BY, and LIMIT.

Be sure to quit the MySQL client (using exit or CTRL-D) when you are done.

Janet Davis (davisjan@cs.grinnell.edu)