Help:COAP-3180/hw3: Difference between revisions

The educational technology and digital learning wiki
Jump to navigation Jump to search
Line 57: Line 57:


DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS exercice;
DROP TABLE IF EXISTS exercise;


CREATE TABLE student (
CREATE TABLE student (
Line 70: Line 70:
INSERT INTO student VALUES (NULL,'Testeuse','Sophie');
INSERT INTO student VALUES (NULL,'Testeuse','Sophie');


CREATE TABLE exercice (
CREATE TABLE exercise (
   id int(10) NOT NULL auto_increment,
   id int(10) NOT NULL auto_increment,
   title char(40) DEFAULT '' NOT NULL,
   title char(40) DEFAULT '' NOT NULL,
Line 80: Line 80:
);
);


INSERT INTO exercice VALUES (NULL,"Exercice 1",'1',"small comment",'http://tecfa.unige.ch/');
INSERT INTO exercise VALUES (NULL,"Exercise 1",'1',"small comment",'http://tecfa.unige.ch/');
INSERT INTO exercice VALUES (NULL,"Exercice 2",'1',"no comment",'http://tecfa.unige.ch/');
INSERT INTO exercise VALUES (NULL,"Exercise 2",'1',"no comment",'http://tecfa.unige.ch/');


</pre>
</pre>

Revision as of 18:26, 11 November 2007

Homework 3 - COAP 3180 Fall 2 2007

COAP 3180 Course Page

Summary

Continue the activity started in the classroom, i.e.

  • Define a SQL table with an SQL file
  • Create the table in a MySQL database by importing the file with the SQL instructions

Purpose: Become familiar with SQL table definitions and MySQL database management

Note: You will have to turn in a first version of this homework next Monday (week 4). We then will discuss what you should add to this mini-project.

Homework tasks

  1. Think about a simple web application that needs a database table, i.e. something that is quite similar to homework 1.
  2. With a simple text editor create a *.sql file and that includes the necessary SQL commands
  3. Import this file into a mySQL database
  4. Repair errors until it works

Play time

Warning. Be careful with quotes and double quotes.

  • Quotes must be straight (same for double quotes).
 '  = Ok
 ’  = BAD !!!

... So use a real text editor and not a word processor.

Fallback in case you don't have your own database:

Demo 1 example


CREATE TABLE demo1 (
  id int(10) NOT NULL auto_increment,
  login char(10) DEFAULT '' NOT NULL,
  password char(100),
  fullname char(40) DEFAULT '' NOT NULL,
  url char(60) DEFAULT '' NOT NULL,
  food int(11) DEFAULT '0' NOT NULL,
  work int(11) DEFAULT '0' NOT NULL,
  love int(11) DEFAULT '0' NOT NULL,
  leisure int(11) DEFAULT '0' NOT NULL,
  sports int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY login (login)
);

Student database with 2 tables


DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS exercise;

CREATE TABLE student (
  id int(10) NOT NULL auto_increment,
  name char(40) DEFAULT '' NOT NULL,
  first_name char(40) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO student VALUES (NULL,'Testeur','Bill');
INSERT INTO student VALUES (NULL,'Testeur','Joe');
INSERT INTO student VALUES (NULL,'Testeuse','Sophie');

CREATE TABLE exercise (
  id int(10) NOT NULL auto_increment,
  title char(40) DEFAULT '' NOT NULL,
  student_id int(10) NOT NULL,
  comments varchar(128),
  url char(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  KEY student_id (student_id)
);

INSERT INTO exercise VALUES (NULL,"Exercise 1",'1',"small comment",'http://tecfa.unige.ch/');
INSERT INTO exercise VALUES (NULL,"Exercise 2",'1',"no comment",'http://tecfa.unige.ch/');

Reading and links

Reading

Pick one of these. Some articles are presented as multiple pages (!)

Reference

Retrieved from "https://edutechwiki.unige.ch/mediawiki/index.php?title=Help:COAP-3180/hw3&oldid=16125"