Lab 3. DB Design and SQL
CS320, Winter 2012

Due: Monday, March 5

Please upload your SQL scripts to CSNS. Note that file uploading will be disabled automatically after 6PM of the due date, and no late submissions will be accepted for this assignment.


1. CREATE (10pt)

Given classes WikiPage, Revision, and User, please design a database schema for the data described by these classes. The table and column names should follow these naming conventions:

For this exercise you need to submit an SQL script lab3-create.sql which contains CREATE TABLE statements to create the tables in a MySQL database.

2. INSERT (6pt)

Run your lab3-create.sql in your MySQL database to create the tables. Use INSERT statements to populate the tables with some test data so that each table contains at least two records. For this exercise you need to submit an SQL script lab3-insert.sql that contains the INSERT statements.

3. SELECT (18pt)

Complete the following queries. Note that when we say "wiki page <path>" (e.g. wiki page index), we mean the wiki page whose path is <path>.

  1. List the content, author name, and timestamp of the latest revision of the wiki page index.
  2. List the id, author name, and timestamp of all the revisions of the wiki page index. The results should be sorted by timestamp in ascending order.
  3. List the id, path, author name (of the latest revision), and timestamp (of the latest revision) of all the wiki pages. The results should be sorted by timestamp in descending order.
  4. Find whether the wiki page mypage1 exists. The result should be either Yes or No. HINT: use the CASE operator or the IF function.
  5. Find the number of revisions created by the user John Doe.
  6. Find the number of pages edited by the user John Doe.

For this exercise you need to submit an SQL script lab3-query.sql which contains your queries.