Homework #4

For this exercise, you will explore the SQLite database and the course Git repositories.

Choose a programming language for the following activities. This is up to you, but it might be a good time to start looking at Python or Ruby, which you will be using for your project at the end of the semester.

Find the SQLite library for your language. If there isn't one, choose a different language.
Find yourself a Git client. A few have been mentioned on the repositories page but whatever client works for you should be fine.

Familiarize yourself with the basics of Git. That might mean going through a Git tutorial, or just learning a few basic commands.
Clone the Git repository created for you. (The repository name is your student number.) Create a directory hw4 in the repository for your work on this exercise.
Create a SQLite database and define tables in it. You can do this with your language's library, but the SQLite command line (sqlite3 hw4.db) is probably easier. This code will create the tables:
```
PRAGMA foreign_keys=ON;
CREATE TABLE `people` ( 
  `id` integer PRIMARY KEY,
  `firstname` varchar(100),
  `lastname` varchar(100)
);
CREATE TABLE `purchases` (
  `id` integer PRIMARY KEY,
  `person_id` integer NOT NULL,
  `date` datetime NOT NULL,
  `price` decimal(5,2) NOT NULL,
  FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE INDEX `key_person_id` ON `purchases` (`person_id`);
```
What we have defined is a table of people, where each person has a first and last name. The table of purchases has a date, price, and person who bought each thing. The person is a reference to a row in the table of people.

Now add a few rows of data to the tables:

INSERT INTO `people` (`id`, `firstname`, `lastname`) VALUES (1, 'Greg', 'Baker');
INSERT INTO `people` (`id`, `firstname`, `lastname`) VALUES (2, 'Xin', 'Wang');
INSERT INTO `purchases` (`person_id`, `date`, `price`) VALUES (1, '2013-03-18', 23.1);
INSERT INTO `purchases` (`person_id`, `date`, `price`) VALUES (1, '2013-03-20', 1.9);
INSERT INTO `purchases` (`person_id`, `date`, `price`) VALUES (2, '2013-03-14', 8.1);
INSERT INTO `purchases` (`person_id`, `date`, `price`) VALUES (2, '2013-03-25', 4);

You can have a look at the data in your database (to inspect it or do backup) with the “.dump” command. Try it.

In the language you chose, write a program that opens the database you just created. It should output the total price of the purchases made by each person.

With the sample data as provided, the output should be:
```
Greg Baker: 25.0
Xin Wang: 12.1
```
Add the program you wrote and the database file to your repository. (You wouldn't usually check in a database file. It's not something that needs to be versioned. For this week, we'll take it as evidence that you did the homework.)

Commit and push your work in the repository to the server.
Your group will have to make a list of possible choices for the technology evaluation topic by April 5. Please coordinate with your group to do that.
Send an email to zju.webdev@gmail.com with the subject “Homework #4”. Be sure to include your student number in the body of the email so we find the right repository.