Delightful Homework #1

To be submitted via email or in class on February 20, 2008 by 5PM.

Concepts

In your own words, answer the following questions.

  1. What is a database?
  2. What is a relation?
  3. What is an RDBMS? (provide more than just an expansion of the acronym)

SQL & Database Design

Given the following tables, answer the questions that follow. Note: There may be more than one right answer!

If you'd like to create this database (recommended) you can use this mysql dump file by doing the following from within a mysql client session:

categories
id int name varchar(16) user_id int
1 personal 1
2 business 1
3 school 1
4 fitness 1
5 goals 1
6 fun 2
7 work 2
priorities
id int name varchar(16)
1 high
2 kinda-high
3 med
4 sorta-low
5 whenever
users
id int username varchar(16) password varchar(255)
1 gwbush <a long hash>
2 lskywalker <a long hash>
tasks
id int description varchar(64) due_date date status tinyint category_id int priority_id int
1 walk dog 2008-03-02 0 1 1
2 change oil 2008-03-02 0 NULL 3
3 water plants 2008-03-02 0 NULL 1
4 wash car 2008-03-02 0 1 3
5 implement project database 2008-03-02 0 3 2
13 read 'SQL Performance Tuning' 2008-03-02 0 2 1
12 pick up keg for party 2008-03-02 0 6 1
11 get pa system from Mike 2008-03-02 0 6 2
10 pick up fog machine 2008-03-02 0 6 2
9 get cleaning supplies 2008-03-02 0 6 3
8 call in sick 2008-03-02 0 6 1
14 update kernel 2008-03-02 0 7 2

Assume that all id fields are attributed with NOT NULL AUTO_INCREMENT PRIMARY KEY

  1. What is a valid create table statement for the priorities table?
  2. What is the sql statement that would insert a new record in the priorities table?
  3. What is the sql statement that would delete the category called 'work'?
  4. What is one important side effect of executing the previous delete statement? (hint: look at the tasks table)
  5. Given a user id of 1, what is a valid sql query that displays all the tasks for that user? (May not be possible. If not, explain why and explain what change should be made to the database design.)
  6. Given a task id of 1, what is a valid sql query that displays all the tasks for the user associated with that task? (May not be possible. If not, explain why and explain what change should be made to the database design.)
  7. If the application that used this database wanted to allow each user to have their own custom priorities, what one change would have to be made to this database?

[end delightful homework 1]