Homework #1
(due date: 06/10/05)

Help on Homework has been updated (link).

1. Submit Instruction
   • Study MySQL by yourself and answer all questions as in section 3 below.
   • Answer all questions using MySQL statements.
     • If you wish to use another database, you are free to use it.
     • If you used another database systems (e.g, Oracle, Postgre, etc), please specify it.
   • Submit your homework in PDF format via e-mail by due date.
     • Your file name should be like 'hw-01-your_first_name.pdf'. (e.g., hw-01-adam.pdf)
   • Please bear in mind that the due date is firmly fixed, and you will have to submit by due time.
     • Due date is 23:59:59 on the 6th of October, 2005.

   　

2. Database Tables

   Suppose the following tables are stored in a database.

   AntiqueOwners
   OwnerID	OwnerLastName	OwnerFirstName
   01	Jones	Bill
   02	Smith	Bob
   15	Lawson	Patricia
   21	Akins	Jane
   50	Fowler	Sam

   　

   Orders
   OwnerID	ItemDesired
   02	Table
   02	Desk
   21	Chair
   15	Mirror

   　

   Antiques
   SellerID	BuyerID	Item
   01	50	Bed
   02	15	Table
   15	02	Chair
   21	50	Mirror
   50	01	Desk
   01	21	Cabinet
   02	21	Coffee Table
   15	50	Chair
   01	15	Jewelry Box
   02	21	Pottery
   21	02	Bookcase
   50	01	Plant Stand

   　

3. Questions
   1. Get the names of all antique owners.
   2. Who desired a table?
   3. Who purchased a 'Table'?
   4. Which users purchased the items they desired?
   5. What items did Sam fowler buy?
   6. Who sold what items to whom?
   7. Get the buyer's ID that who aren't antique owners before.
   8. Get the names of AntiqueOwners whose ID is in between 2 and 21.
   9. Who desired a 'Table' and a 'Desk'?
   10. Get the sum of all OwerIDs.

   　

4. Tips
   • Use google as much as you can to find an answer.
   • I will also provide some useful links at the reading assignment #1.

Help on Homework #1

1. Download MySQL from the website (link)
   • Download MySQL 4.1 version according to your platform

   　

2. Install MySQL for your own use (link)

   If you want to install MySQL on Windows XP, you will meet the the following steps (roughly) to install.

   • Choose 'Detailed Configuration'
   • Choose 'Developer Machine'
   • Choose 'Multifunctional Database'
   • Leave as it is when you meet InnoDB datafiles setup section
   • Choose 'Decision Support (DSS)/ OLAP'
   • Choose 'Enable TCP/IP Networking' and set the port number '3306'
   • In language settings, you probably want to choose 'Standard' unless you want to use other languages.
   • Tick 'Install as Windows Service' and tick also on 'Launch MySQL automatically'
   • Now, you are going to set a password for root access. Type password accordingly.
   • At this stage, you probably are almost close to the final installation. Press 'Execute' to start the configuration.
   • You will be able to see something like 'The service started successfully.' If you can see this message, your installation is likely to be successful.
   • You can now confirm MySQL installation by clicking 'Start' button (left-bottom on Windows XP screen)

   　

3. Connect to MySQL server
   • You can connect to your MySQL server by clicking 'Start -> All Programs -> MySQL -> MySQL Server 4.1 -> MySQL Command Line Client'.
   • It will ask you the password which you entered during the installation process.

   　

4. Test your MySQL

   

   　

5. Now, you will be able to do the following task!
   • Create a database
   • Creating tables
   • Putting data into tables
   • Selecting information from a table