Help:COAP-3180/week3

Week 3 - COAP 3180

Topics Covered

Portalware configuration (continued):

• discussion of homework 2 if needed

Relational databases: Introduction to SQL (part 1):

• Retrieving data
• Definition of SQL tables and use of the PhpMySQL tool
• Relational tables (principles)

Main teaching materials

• Slides used: http://tecfa.unige.ch/guides/te/files/mysql-intro.pdf
• Lecture notes (very similar to slides): SQL and MySQL tutorial

Classroom activities

Monday

• Define a table with SQL code
• Learn how to inject SQL code in MySQL Admin (a web application to administer web databases) in order to create databases and tables
• Create a simple 1-table MySQL database with the database administration tool

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.

(1) 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)
);

(2) 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/');

Classroom activities Wednesday

• Continue creating a table (if needed)
• Relational databases with 2 or 3 tables (simple 1-N and N-N cases)
• Web hosting (discussion)
• The anatomy of Wordpress

Homework 3

Topic: Database definition (2-3 tables), Details to be announced on Wednesday.

Define an SQL file that will create 2-4 related tables. You can freely choose the application area, but it should somewhat relate to prior homework or the term project.

Minimal requirements:

• Minimum 2 tables and max. 4 tables. Make sure that the tables are related.
• At least 10 fields (for all tables)
• At least 1 integer and 1 varchar (for all tables)
• Each table must define a primary key
• You may look at real example and take inspiration from these under the condition that you make some modifications and that you quote the source in the comment !!

The SQL file must include some documentation on top that (1) explains the purpose of this database and of each table and that (2) provides some high-level design elements. See the SQL and MySQL tutorial for comment syntax.

Remarks and tips

This homework is not related to the term project, but it helps you preparing exams.

Before you submit your homework, make sure to test it, i.e. load it into the database, e.g. with PhPMyAdmin. If you this more than once, you must kill the old tables, use the following SQL command:

DROP TABLE IF EXISTS name_of_your_table;

E.g.

DROP TABLE IF EXISTS exercise;

To get the "2 related tables" structure right, think for example about a relationship between a person and what he can own or produce. E.g. One Author writes N books, One CD includes N titles.

An example discussed in Yarger et al. (1999:18ff) is a four tables structure: Artists - songs - CDs - Record labels. One artist can produce several songs. A CD contains several songs. A Record label publishes several CDs. A corresponding table structure would look like this:

Artist
     Artistid  primary key
     name      ....

CD 
     CDid      primary key
     title     ....
     LabelID   foreign key

Song
     Songid    primary key
     name      ....
     CDId      foreign key
     ArtistId  foreign key 

Label
     Labelid   primary key
     name      ....

Submission Dates

• This homework is due at start of wed week 4
• Students must upload the SQL file to the worldclassroom (don't forget the documentation inside this file).
• Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

• See the worldclassroom

• A well documented and well designe two table design may also get an A (so you don't must go for a three our four table solution ...)

Teaching materials and further reading

Online software

• We shall use MoWes again

Teaching materials (slides and reading)

• Slides used: http://tecfa.unige.ch/guides/te/files/mysql-intro.pdf
• Lecture notes (very similar to slides): SQL and MySQL tutorial

You probably will have to complete my lecture notes with some extra reading. Pick one of the following. (Unfortunately, some of these articles are presented as multiple pages ....)

• Introduction to Structured Query Language: Version 4.11 by James Hoffman. Good bet if you like concise texts.
• Getting Started With SQL by Ted Brockwood, Web developper's journal. Good as first introduction.
• SQLCourse and SQLCourse2
• Introduction to Databases for the Web: Pt. 1 by Selena Sol, database journal (good, but too many distracting ads)
• Yarger et al. (1999). MySQL & MSQL, O'Reilly, Chapter 2 on database design (the rest is outdated).