期終考試題目，國外朋友的，俺不會jsp只好請教大大門 注意事項

FURTHER INFORMATION:

The following database tables were created with the following SQL statements. This information together with a sample data damp is available on ULearn (coursework’s area). This is only necessary if you want to install and use a database (i.e. MySQL) on your own PC instead of using the (in-campus) available database. To install MySQL go to: http://www.mysql.org/downloads/mysql/5.0.html.

You can also download the administrative tools to manage the data using a GUI (http://www.mysql.org/downloads/gui-tools/5.0.html). Finally the Java connector (JDBC driver) can be downloaded from:
http://www.mysql.org/downloads/connector/j/5.0.html

CREATE TABLE `webtech`.`tasks` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`description` TEXT,
`creator` INTEGER UNSIGNED NOT NULL,
`creation_date` DATETIME NOT NULL,
`start_date` DATETIME DEFAULT NULL,
`end_date` DATETIME DEFAULT NULL,
`category` INTEGER UNSIGNED NOT NULL,
`priority` SMALLINT(1) UNSIGNED DEFAULT '0' COMMENT '0: not assigned; 1: low; 2: med; 3: high',
`status` SMALLINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '0: not started; 2: started; 3: completed',
PRIMARY KEY(`id`)
)

CREATE TABLE `webtech`.`users` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(16) NOT NULL COMMENT 'Max 16 characters',
PRIMARY KEY(`id`)
)

CREATE TABLE `webtech`.`category` (
`id` INTEGER UNSIGNED NOT NULL auto_increment,
`name` VARCHAR(50) NOT NULL,
`colour` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`,`name`)

)


Exemplar SQL statements:

Get all task records of a user with id=1:
SELECT t.*, c.name FROM tasks t, category c WHERE t.creator=1;

Update the status and priority of a task record with id=1:
UPDATE tasks t SET t.`status`=1, t.priority=2 WHERE t.id=1;

Insert a new task category:
INSERT INTO category (`name`) VALUES ("my new category");

HINTS:
1. Make sure that you do not use Microsoft double quotes (“) or (”) within your source code and use (") instead. This sometimes is difficult to spot and can cause lots of problems!
2. Don’t forget to add a form element on the HTML page.
3. Name all elements you are going to use, giving them a unique name.
4. Keep the code simple and use proper indentation.
5. Give some friendly instructions (guidance) for the user accessing the page.



Formatting : There is no formatting. All your code should be written in plain text (i.e. use notepad for that or an HTML editor).


WARNING : You are free to use any books or electronic resources for your assignment. However you should write your own programming code, based on the knowledge acquired from these sources. Copying programming code that belongs to other people including your classmates is called plagiarism. Every coursework deemed to contain copied material will incur severe penalties in marking. It can be given a total mark of 0. To avoid this you must reference all your sources (i.e. as comments within your source code). If in doubt as to what constitutes plagiarism please come and see me immediately.


Marking scheme: The assignment’s mark is 30% from the overall mark. Each part of the coursework is marked according to the following schema: