Interaction 3rd Semester - Databases on the Web

chantingrompΚινητά – Ασύρματες Τεχνολογίες

10 Δεκ 2013 (πριν από 3 χρόνια και 6 μήνες)

114 εμφανίσεις

Interaction 3rd Semester - Databases on the Web
Niels Müller Larsen
Interaction 3rd Semester - Databases on the Web
Niels Müller Larsen
Publication date 2013-04-29
Copyright © 2013 Niels Müller Larsen
This work is licensed under the Creative Commons Attribution 3.0 License. To view a copy of this license, visit http://creativecommons.org/
licenses/by/3.0/ or send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA.
iii
Table of Contents
Preface ..................................................................................................................... iv
1. Databases Intro ...................................................................................................... 1
2. Select and PHP ...................................................................................................... 6
3. From .................................................................................................................... 14
4. Where .................................................................................................................. 18
5. Group and Having ................................................................................................ 23
6. Entity Relationship Modelling ............................................................................... 26
7. ER, Types and Create ........................................................................................... 33
8. Case: Discussion Forum ....................................................................................... 37
9. Case: Discussion Forum ....................................................................................... 47
10. Case: Discussion Forum Presentation ................................................................... 50
11. Design to Reality ................................................................................................ 51
12. Design to Reality ................................................................................................ 67
A. Setting Up 3 Tier Client Server Environment ........................................................ 70
B. Model Solutions to Assignments ........................................................................... 76
Bibliography ............................................................................................................ 98
iv
Preface
Table of Contents
Introduction to the Module ........................................................................................ iv
Course Materials ....................................................................................................... iv
Calendar .................................................................................................................... v
Toolchain .................................................................................................................. vi
Introduction to the Module
Module Description
Name
The short name is Databases, the official name being Interaction 3rd Semester - Databases
Workload
The total module has a weight of 10.0 ECTS-points. The equivalent of a full year's work is
60 ECTS-points. In other words, this module carries 280 work hours, of which only 90 45-
minute lessons are with classroom attendance. The rest is your studying time, exercises, and
assignments.
Prerequisites
Completion of the first two semesters of the program.
Evaluation
A number of mandatory assignments will be given. They must be solved and approved. In
addition a mandatory project for the whole program will include this module.
Learning Objective
The objective of the module is, in short, enabling you to create and use databases as the
persistence layer of data for modern, dynamic web applications.
Course Materials
Textbooks
[ Lim08 ] Chapters 1-11, appendices A-B.
[ pow06 ] Chapters ad hoc.
Supplement
[ nml99 ] These notes.
Preface
v
Calendar
Danish Class (12a) - Schedule
Table 1.
No
When
Planned Content
1
Mon 2013-01-28
Intro, Toolchain, First Steps
[../site/ch01.xhtml]
2
Mon 2013-01-31
Select (and PHP) [../site/
ch02.xhtml]
3
Mon 2013-02-04
From [../site/ch03.xhtml]
4
Mon 2013-02-11
Where [../site/ch04.xhtml]
5
Mon 2013-02-18
Group and Having [../site/
ch05.xhtml]
6
Mon 2013-02-25
ER, Design [../site/
ch06.xhtml]
7
Mon 2013-03-04
ER, Types and Create [../site/
ch07.xhtml]
8
Mon 2013-03-18
Case: Forum [../site/
ch08.xhtml]
9
Tue 2013-03-19
Case: Forum [../site/
ch09.xhtml]
10
Mon 2013-04-08
Case: Forum [../site/
ch10.xhtml]
11
Wed 2013-04-17
Design to Reality. [../site/
ch11.xhtml]
12
Mon 2013-04-29
Wrap Up [../site/ch12.xhtml]
International Class (12x) - Schedule
Table 2.
No
When
Planned Content
1
Wed 2013-01-30
Intro, Toolchain, First Steps
[../site/ch01.xhtml]
2
Wed 2013-02-06
Select (and PHP) [../site/
ch02.xhtml]
3
Wed 2013-02-13
From [../site/ch03.xhtml]
4
Wed 2013-02-20
Where [../site/ch04.xhtml]
5
Tue 2013-02-26
Group and Having [../site/
ch05.xhtml]
6
Wed 2013-03-06
ER, Design [../site/
ch06.xhtml]
Preface
vi
7
Wed 2013-03-21
ER, Types and Create [../site/
ch07.xhtml]
8
Thu 2013-04-03
Case: Forum [../site/
ch08.xhtml]
9
Wed 2013-04-09
Case: Forum [../site/
ch09.xhtml]
10
Tue 2013-04-11
Case: Forum [../site/
ch10.xhtml]
11
Thu 2013-04-18
Design to Reality. [../site/
ch11.xhtml]
12
Thu 2013-04-26
Wrap Up [../site/ch12.xhtml]
Toolchain
This module teaches web backend technologies. In order to get the proper setting we must use
 A Database Management System, a DBMS.
 PHP, an acronym for PHP Home Pages, the most ubiquitous server side programming
language on the web.
As an introduction I outline the World Wide Web architecture and place the course disciplines
in a wider context. The aim is to get a high altitude perspective of your coming work for the
programme. You may choose to look at C/S from a Backend/PHP perspective.
Preface
vii
Figure 1. Two tier client/server
Please observe that the file system is local to the web server.
Preface
viii
Figure 2. Three tier client/server
Please notice that here, too, the file system is local to the web server, and the database location
is on principle transparent. This means we do not know where it is located. It is referenced
through it's URI (URL). In our development environment, all three (two) tiers are installed
on our own development computer, normally our laptop.
Installation of Web Server etc.
We need a web server to gain access to the web backend. The web server provides an interface
to PHP which again provides the interface to the database layer. For installing the necessary
software I refer you to Appendix A, Setting Up 3 Tier Client Server Environment.
Preface
ix
Editor
In order to work with SQL and PHP you also need a decent editor. I am sure you already
have a favorite editor.
Diagram Tool
In order draw ER-, and other, diagrams please download and install Dia [http://
live.gnome.org/Dia/Download] for your platform.
1
Chapter 1. Databases Intro
Table of Contents
References ................................................................................................................. 1
SQL Intro .................................................................................................................. 1
Toolchain ................................................................................................................... 3
Assignments ............................................................................................................... 3
References
[ Lim08 ] Chapter 1.
[ nml99 ] These notes.
SQL Intro
Intro SELECT
SQL, or Structured Query Language is a programming language for manipulating data in
databases, and for manipulating the databases as such. Let us start with an example, slightly
augmented from your textbook [Lim08]:
SELECT name FROM city WHERE id = 9;
 SELECT, FROM, and WHERE are keywords. Case insensitive by the way.
 name, city, and id are identifiers. In programming they would be variable names, here
the first and last are column names while the middle one is a table name.
 = is an operator.
 9 is a constant. Numeric in this case.
; is a statement delimiter.
This is an SQL declaration, or statement. This is what it consists of, nothing but a combination
of the above.
The same declaration as the one we have just seen could be written as follows:
SELECT name
FROM city
WHERE id = 9;

or even better:
select name
from city
where id = 9;

Each line is a clause. The select, and the from clauses are mandatory while the where
clause is optional.
Databases Intro
2
Two of the identifiers were columns while the remaining one was a table name. Let us for
the sake of clarity see [a fragment of] the table in question:
+------+---------------+-------------+-----------------+------------+
| id | name | countryCode | district | population |
+------+---------------+-------------+-----------------+------------+
| 3315 | København | DNK | København | 495699 |
| 3316 | Århus | DNK | Århus | 284846 |
| 3317 | Odense | DNK | Fyn | 183912 |
| 3318 | Aalborg | DNK | Nordjylland | 161161 |
| 3319 | Frederiksberg | DNK | Frederiksberg | 90327 |
| 2807 | Oslo | NOR | Oslo | 508726 |
| 2808 | Bergen | NOR | Hordaland | 230948 |
| 2809 | Trondheim | NOR | Sør-Trøndelag | 150166 |
| 2810 | Stavanger | NOR | Rogaland | 108848 |
| 2811 | Bærum | NOR | Akershus | 101340 |
+------+---------------+-------------+-----------------+------------+
Tables consist of rows and colums. A row hold information about some item. The columns
hold each atomic piece of information about the items.
The proper way of saying it would be to say that each column/row intersection holds a value
of a property of an item we are interested in knowing about.
Intro CREATE, DDL
All the select declarations in the world would be in vain were there no tables. Tables reside
in databases. This way you already know the secret.
 A database is a container for the values of properties of interesting items that we have
organised in tables. 
SQL has two main areas. Manipulation af databases and their structure, this is called DDL,
or better SQL Schema. The other, of which you have already seen the select declaration, is
called DML, or better SQL Data.
To get tables with data, first we must create a database, then we must create tables in that
database, and finally we must populate the tables with data, values from the real world.
Step by step, first we create the database:
create database newworld;
Then we create tables in that database:
create table city (
id int not null,
name varchar(42) not null,
countrycode char(3) not null,
district varchar(20) not null,
population int not null,
primary key (id)
);
Finally we must insert some data into the table, otherwise there would be nothing to read,
would there?
insert into city
(name, district, countrycode, population, id)
values
('Århus', 'Århus', 'DNK', 284846, 3316);
or perhaps:
Databases Intro
3
insert into city
values
(3316, 'Århus', 'DNK', 'Århus', 284846);
insert into city
values
(3318, 'Aalborg', 'DNK', 'Nordjylland', 161161);
Insert as many rows as you need or please.
you are bound to have to change some values in the database. Either you make errors and you
want to correct them, or some numbers change in real life, and the corresponding recorded
values must be updated.
update city
set name = 'Aarhus'
where id = 3316;
or perhaps
update city
set name = 'Aarhus', district = 'Aarhus', population = 300000
where id = 3316;
You may regret an entry altogether so much that you want to get rid of it
delete from city
where id = 3318;
To check or reassure yourself that everything is in order let's recap the select declaration
select *
from city
where countrycode='DNK';
Toolchain
To get started with databases as a real life endeavour as opposed to pure theory, you must in-
stall the Relational Database Management System, the RDBMS. Please refer to Appendix A,
Setting Up 3 Tier Client Server Environment.
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Databases Intro
4
Deadline will generally be the start of the following lesson.
Assignment 1.0
Please write the page illustrated in the following image. The page must be written as PHP, and
contain HTML5. Save the file as ass1.php within your webserver's reach, ie somewhere
under htdocs, we will continue work on it.
Figure 1.1. Sample
Requirements:
Databases Intro
5
 Header and footer text must come from php variables.
 The drop down to choose country must look like:
<option value="DNK">Denmark</option>
 The drop down values must come from an associative array:
$countries = array(
"DNK" => "Denmark",
"NOR" => "Norway",
"SWE" => "Sweden"
);
 Use foreach to loop through the array.
6
Chapter 2. Select and PHP
Table of Contents
References ................................................................................................................. 6
Model Solutions to Assignments from Previous Lesson ................................................ 6
The PHP Web Page, Recap ........................................................................................ 6
SELECT and PHP ..................................................................................................... 8
We Generalize to a Class ........................................................................................... 9
The Document and Database Class at Work ............................................................... 10
Select, More Variants ............................................................................................... 11
Assignments ............................................................................................................. 12
References
[ Lim08 ] Chapter 2.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
The PHP Web Page, Recap
Let us play a little bit with HTML5. There are some bits of it that are on each and every
page we shall write from now on until kingdom comes, as they say over there. First let me
show you a strange page:
Example 2.1. PHP OO Generated Page
<?php
require_once('HTML5.inc.php');
$doc = new HTML5("TeSt", "en");
print($doc->getTop());
?>
<!-- insert head content here -->
<?php
print($doc->getNeck());
?>
<!-- insert body content here -->
<?php
print($doc->getFoot());
?>

Live [http://localhost/deformation.org/mdu/sem3/code/ass2Pdemo0.php]. Teacher's local-
host only.
Example 2.2. The Class Behind the PHP OO Generated Page
<?php
Select and PHP
7
class HTML5 {
const DOCT = '<!DOCTYPE html>';
const HTML = '<html lang="%s" xml:lang="%s">';
const CHARSET = '<meta charset="utf-8"/>';
const VIEWPORT = '<meta name="viewport" content="width=device-width"/>';
const NECK = " </head>\n <body>\n";
const FOOT = " </body>\n</html>\n";
const LINK = ' <link rel="stylesheet" href="%s"/>';
const SCRIPT = ' <script src="%s"></script>';
private $title;
private $lang;
private $top;
public function __construct($title, $lang='da') {
$this->title = $title;
$this->lang = $lang;
$this->createTop();
}
private function createTop() {
$this->top = "";
$this->top .= self::DOCT . "\n";
$this->top .= self::HTML . "\n";
$this->top .= " <head>\n";
$this->top .= " ".self::CHARSET."\n";
$this->top .= " ".self::VIEWPORT."\n";
$this->top .= " <title>%s</title>\n";
$this->top = sprintf($this->top
, $this->lang, $this->lang, $this->title);
}
function getFoot() {
return self::FOOT;
}
function getNeck() {
return self::NECK;
}
public function getTop() {
return $this->top;
}
public function getTitle() {
return $this->title;
}
public function prtLink($l) {
printf(self::LINK."\n", $l);
}
public function prtScript($s) {
printf(self::SCRIPT."\n", $s);
}
}
?>

Upon that absurd page let me use that as the container for the model solution we saw earlier:
Example 2.3. Remodelled Model Solution for Last Lesson's Assignment
<?php
$copy = "&copy; NML, 2013";
$countries = array(
"DNK" => "Denmark",
"NOR" => "Norway",
"SWE" => "Sweden"
);
Select and PHP
8
require_once('HTML5.inc.php');
$doc = new HTML5("The World database", "en");
print($doc->getTop());
?>
<link rel="stylesheet" href="./ass1M1.css"/>
<?php
print($doc->getNeck());
?>
<header>
<h1><?php print($doc->getTitle());?></h1>
</header>
<section id="country">
</section>
<section>
<form action="#" method="post">
<select>
<?php
foreach ($countries as $key => $val) {
printf("<option value='%s'>%s</option>\n", $key, $val);
}
?>
</select>
<input type="submit" value="Choose Country"/>
</form>
</section>
<footer>
<p><?php print($copy);?></p>
</footer>
<?php
print($doc->getFoot());
?>

Live [http://localhost/deformation.org/mdu/sem3/code/ass1M1.php]. Teacher's localhost on-
ly.
SELECT and PHP
The MySQL client program, whether it be mysql, the web app phpmyadmin, or the GUI
app MySQL workbench must do two things before it can start doing selects for you:
 connect to the database server
 tell the server which database to work with
Example 2.4. Connect to Server and Choosing Database
This code is common to ALL php programs using databases.
// connect to database server and use database
$host = "localhost";
$user = "nobody";
$pwd = "test";
$database = "world";
$dbh = mysql_connect($host, $user, $pwd) or die ("no server!");
mysql_select_db($database, $dbh) or die("no database");
Select and PHP
9
Next we can select and display. This code is NOT common, it is individual for the use case.
Example 2.5. Select from Table
$sql = "select id, name, countrycode, district, population";
$sql .= " from city";
$sql .= " where countrycode = '". $cc . "';";
$res = mysql_query($sql, $dbh) or die("failed select");
print("<table>");
while ($city = mysql_fetch_array($res)) {
printf("<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>\n",
$city['id'], $city['name'], $city['countrycode'],
$city['district'], $city['population']);
}
print("</table>");
We Generalize to a Class
The common database connection code is preferably placed in a class, so that it is so much
easier to reuse.
Example 2.6. DbH.inc.php
<?php
class DbH {
private $connection;
private $database;
private $host;
private $user;
private $password;
private $result;
public function __construct($database,
$host='localhost',
$user='nobody',
$password='test') {
$this->host = $host;
$this->database = $database;
$this->user = $user;
$this->password = $password;
$this->connect();
}
private function connect() {
try {
if (! $this->connection = mysql_connect($this->host, $this->user, $this->password))
throw new Exception("No connection to the MySQL server.");
if (! mysql_select_db($this->database, $this->connection))
throw new Exception("No connection to the MySQL " . $this->database . " database.");
}
catch (Exception $e) {
die($e->getMessage());
}
}
public function close() {
Select and PHP
10
mysql_close($this->connection);
}
function query($declaration) {
try {
if (! $this->result = mysql_query($declaration, $this->connection)) {
throw new Exception("Query error: " . mysql_error() . '<br />' . $declaration);
}
}
catch (Exception $e) {
die($e->getMessage());
}
return $this->result;
}

function fetch_array() {
return $rowArray = @mysql_fetch_array($this->result);
}

function fetch_object() {
return $rowObject = @mysql_fetch_object($this->result);
}
}
?>

The Document and Database Class at
Work
Let's see it in action:
Example 2.7. ass2Pdemo1.php
<?php
$copy = "&copy; NML, 2013";
$countries = array(
"DNK" => "Denmark",
"NOR" => "Norway",
"SWE" => "Sweden"
);
require_once('DbH.inc.php');
require_once('HTML5.inc.php');
$dbh = new DbH("world");
$doc = new HTML5("The World database", "en");
print($doc->getTop());
?>
<link rel="stylesheet" href="./ass1M1.css"/>
<?php
print($doc->getNeck());
?>
<header>
<h1><?php print($doc->getTitle());?></h1>
</header>
<section id="country">
<?php // ready to select
Select and PHP
11
$cc = 'DNK';
if (isset($_POST['cc']))
$cc = $_POST['cc'];

$sql = "select id, name, countrycode, district, population";
$sql .= " from city";
$sql .= " where countrycode = '". $cc . "';";
$dbh->query($sql);
print("<table>");
while ($city = $dbh->fetch_array()) {
printf("<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td class='am'>%s</td>
</tr>\n",
$city['id'],
$city['name'],
$city['countrycode'],
$city['district'],
number_format($city['population']));
}
print("</table>");
?>
</section>
<section>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<select name="cc">
<?php
foreach ($countries as $key => $val) {
printf("<option value='%s'>%s</option>\n", $key, $val);
}
?>
</select>
<input type="submit" value="Choose Country"/>
</form>
</section>
<footer>
<p><?php print($copy);?></p>
</footer>
<?php
print($doc->getFoot());
?>

Live [http://localhost/deformation.org/mdu/sem3/code/ass2Pdemo1.php]. Teacher's local-
host only.
Select, More Variants
Example 2.8. Select from several columns
select name, countrycode, population
from city
where countrycode = 'SWE';
Example 2.9. Select and Group
select countrycode, count(*) as "Number of cities"
Select and PHP
12
from city
group by countrycode;
Example 2.10. Countries With More Than N Cities
select countrycode, count(*) as "Number of cities"
from city
group by countrycode
having count(*) > 25;
Example 2.11. Ordering the Output
select name, countrycode, population
from city
where countrycode = 'SWE'
order by name;
Example 2.12. Ordering the Output
select name, countrycode, population
from city
where countrycode = 'SWE'
order by population desc;
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 2.0
Please refer to the example Example 2.7, ass2Pdemo1.php from today's lesson. Where you
find the foreach loop, you must
 read the countrycodes from the city table
 loop through them
 on each iteration place the current on in <option value="zzz">zzz</option>
Select and PHP
13
 You cannot yet read the name of the country anywhere.
Assignment 2.1
In the world database you will find a table countrylanguage.
 Write an SQL statement that reads all columns from 'AFG'
 Write an SQL statement that reads all columns from 'TJK'
 Write an SQL statement that reads all columns from 'UZB'
 Assuming they were the only countries in the world, how would you calculate how many
persons speak each language in the world? Reply in text, verbally, no SQL.
 What actual information is missing to do the real calculation?
Assignment 2.2
From the table countrylanguage:
 Write an SQL statement that reads all countries that have 'Danish' as a language.
 Write an SQL statement that counts the languages per country.
 Write an SQL statement that shows how many languages they speak in 'ZAF', South Africa.
 Write an SQL statement that shows what languages they speak in South Africa.
14
Chapter 3. From
Table of Contents
References ............................................................................................................... 14
Model Solutions to Assignments from Previous Lesson .............................................. 14
SELECT  FROM .................................................................................................. 14
SELECT  FROM  JOIN .................................................................................... 14
Joins from the World Database ................................................................................. 15
Select With Subquery ............................................................................................... 16
Assignments ............................................................................................................. 16
References
[ Lim08 ] Chapter 3.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
SELECT  FROM
Let us expand the knowledge on select
Example 3.1. Parsing for errors
select id, name, countrycode
from citi
where countrycode = 'SWE';
Example 3.2. From one table
select id, name, countrycode
from city
where countrycode = 'SWE';
select language, countrycode, isofficial
from countrylanguage
where countrycode = 'USA';
select code, name, continent
from country
where continent = 'North America';
SELECT  FROM  JOIN
To join means joining content from two or more tables in the result set. In other words reading
from more tables. We have several kinds:
 inner join with the keyword join, or, indeed inner join.
From
15
 outer join, keywords
 left outer join
 right outer join
 full outer join
 cross join, keyword cross join.
Example 3.3. Inner join
select a, b
from a join b
on a = b;
or, totally equivalent
select a, b
from a inner join b
on a = b;
Example 3.4. Left outer join
select a, b
from a left outer join b
on a = b;
Example 3.5. Right outer join
select a, b
from a right outer join b
on a = b;
Example 3.6. Full outer join
select a, b
from a full outer join b
on a = b;
Oops! Not supported. It is in the SQL std. but not in MySQL or MariaDB. Shit happens. It
is, however, not very important.
Joins from the World Database
Example 3.7. Inner join of Countrylanguage and Country
select code, name, language
from countrylanguage join country
on code = countrycode
where continent = 'North America';
Example 3.8. Inner join of Countrylanguage and Country, Aliases
select cty.name, cnty.name
from city cty join country cnty
on cty.countrycode = cnty.code
From
16
where continent = 'South America';
Example 3.9. Left outer join
select city.name, country.name
from country left join city
on countrycode = code
where continent = 'Antarctica';
Example 3.10. Right outer join
select city.name, country.name
from city right join country
on countrycode = code
where continent = 'Antarctica';
Select With Subquery
Subqueries are also sometimes called embedded queries. The subquery results in a derived
table that is forms part of the outer query.
Example 3.11. Query with Subquery
select city.name, country.name, continent
from city join country
on code=countrycode
where city.population = (select min(population) from city);
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 3.0
Please write a PHP page showing a table with country name, population, head of state, for
all countries such that they have no cities in the database;
From
17
Methodology
 Create the SQL statement in the database client program.
 Test the statement iteratively until it is correct.
 Insert it into the PHP.
Assignment 3.1
Please write a PHP page showing a table with country name, language, percentage, and pop-
ulation, for all languages in the countrylanguage table;
Methodology
 Create the SQL statement in the database client program.
 Test the statement iteratively until it is correct.
 Insert it into the PHP.
18
Chapter 4. Where
Table of Contents
References ............................................................................................................... 18
Model Solutions to Assignments from Previous Lesson .............................................. 18
SELECT  FROM  WHERE ............................................................................... 18
SELECT  FROM  WHERE  <>= ................................................................... 18
SELECT  FROM  WHERE  AND/OR ........................................................... 19
SELECT  FROM  WHERE  IN/EXISTS ........................................................ 20
Assignments ............................................................................................................. 21
References
[ Lim08 ] Chapter 4.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
SELECT  FROM  WHERE
Today we shall enlarge the scope of what we have been doing so far with the where clause.
As usual we shall do it through examples similar to the one in the textbook.
Example 4.1. NOT not
The not is used to invert the true/false of the operator in question.
select language, countrycode, percentage
from countrylanguage
where not countrycode = 'DNK';
Example 4.2. NOT <>
select language, countrycode, percentage
from countrylanguage
where countrycode <> 'DNK';
SELECT  FROM  WHERE  <>=
Example 4.3. <>=
Regular arithmetic comparison operators apply.
select language,countrycode,percentage
from countrylanguage
where percentage >= 98.0;
Where
19
Example 4.4. = Alphabetically
Equals and NOT equals. Notice the apostrophes. Not quotes.
select language,countrycode,percentage
from countrylanguage
where countrycode = 'DNK';
Example 4.5. Like
We also have some others than the arithmetic operators
select name, countrycode
from city
where name like '%polis'
order by name;
select name, countrycode
from city
where name like '___'
order by name;
Example 4.6. Between
We also have some others than the arithmetic operators
select c.name, country.name, c.population
from city c join country
on code = countrycode
where c.population between 42 and 1000
order by c.population desc;
Example 4.7. Dates
Dates behave in comparisons similar to alphabetic attributes.
select c.name, country.name, c.population
from city c join country
on code = countrycode
where c.reviseddate > '2012-01-01'
order by c.name;
select c.name, country.name, c.population, reviseddate
from city c join country
on code = countrycode
where c.reviseddate >= '2013-01-01'
order by c.name;
SELECT  FROM  WHERE  AND/OR
We expand the arsenal yet again with compound operators.
Example 4.8. AND
select c.name, country.name, c.population
from city c join country
on code = countrycode
where c.population > 42
and c.population < 1000
order by c.population desc;
Where
20
Example 4.9. OR
select name, countrycode, population
from city
where countrycode = 'DNK'
or countrycode = 'NOR'
or countrycode = 'FIN'
order by countrycode, population desc;
Example 4.10. AND OR
select name, countrycode, population
from city
where countrycode = 'DNK'
or countrycode = 'NOR'
and population > 500000
order by countrycode, population desc;
Example 4.11. AND OR
select name, countrycode, population
from city
where (countrycode = 'DNK'
or countrycode = 'NOR')
and population > 500000
order by countrycode, population desc;
SELECT  FROM  WHERE  IN/EX-
ISTS
Selects sometimes use set operations on tables. Let us first look at the compund or-based
query, where we select cities from Denmark, Norway, or Finland.
Example 4.12. OR
select name, countrycode, population
from city
where countrycode = 'DNK'
or countrycode = 'NOR'
or countrycode = 'FIN'
order by countrycode, population desc;
That query may be phrased as getting the cities in the set of countries where Denmark, Nor-
way, and Finland are the only members.
Example 4.13. IN
select name, countrycode, population
from city
where countrycode in ('DNK', 'NOR', 'FIN')
order by countrycode, population desc;
What if we want to see all cities from countries where the biggest language is spoken by
more than 50% of the population.
Example 4.14. EXISTS and Correlation
select c.name, c.countrycode
Where
21
from city c
where exists (select max(percentage)
from countrylanguage
where countrycode = c.countrycode
having max(percentage) > 50)
order by c.countrycode;
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 4.0
Please write a PHP page showing a table with the city name, city population, country name,
and country population in countries with less than 10 cities. Please use the IN keyword.
Methodology
 Create the SQL statement in the database client program.
 Test the statement iteratively until it is correct.
 Insert it into the PHP.
Assignment 4.1
Aarhus is looking for friendship cities in Asia. Please write a PHP page showing all Asian
cities with populations in the [250000;350000] bracket.
Methodology
 Create the SQL statement in the database client program.
 Test the statement iteratively until it is correct.
 Insert it into the PHP.
Where
22
Assignment 4.2
Create these queries, no PHP is necessary.
 Find occurrence of same city name in several countries. List name and country. Use IN.
 Find occurrence of same city name in several countries. List name and country. Use EX-
ISTS.
 Produce a list of all kingdoms in the world. The country table has a governmentform
attribute.
 Write a query that convinces me that all capital cities as stated in the country table
also occur in the city table.
23
Chapter 5. Group and Having
Table of Contents
References ............................................................................................................... 23
Model Solutions to Assignments from Previous Lesson .............................................. 23
SELECT  FROM  WHERE ............................................................................... 23
Assignments ............................................................................................................. 24
References
[ Lim08 ] Chapter 5 - 6. Parts of chapters 7 and 8;
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
SELECT  FROM  WHERE
In this lesson we will wrap up the basics of the select. There is, especially in chapters 7 and
8 some demos of functions that you may need frequently in real life, but at present will be
overload. Please refer to these chapters for reference, and supplement that with good use of
the documentation of your chosen RDBMS software, here, for now this means MySQL.
Example 5.1. Ordering Without Grouping
select co.name, co.population, ci.name, ci.population
from country co join city ci
on code=ci.countrycode
where continent = 'South America'
order by co.name, ci.name;
Example 5.2. Grouping
select co.name, co.population, count(ci.name), sum(ci.population)
from country co join city ci
on code=ci.countrycode
where continent = 'South America'
group by co.name;
Example 5.3. Ordering and Grouping
select co.name, co.population, count(ci.name), sum(ci.population)
from country co join city ci
on code=ci.countrycode
where continent = 'South America'
group by co.name
order by population desc;
Example 5.4. Filtering
select co.name, co.population, count(ci.name), sum(ci.population)
Group and Having
24
from country co join city ci
on code=ci.countrycode where continent = 'South America'
group by co.name
having count(ci.name) > 10
order by population desc;
Example 5.5. Filtering with Aliases
select co.name, co.population, count(ci.name) citycount, sum(ci.population)
from country co join city ci
on code=ci.countrycode where continent = 'South America'
group by co.name
having citycount > 10
order by population desc;
Example 5.6. Calculations
select co.name
, co.population
, count(ci.name) citycount
, sum(ci.population) citypop
, sum(ci.population) * 100 / co.population "Urbanization"
from country co join city ci
on code=ci.countrycode
where continent = 'South America'
group by co.name
order by urbanization desc;
Example 5.7. Distinct, need
select language
from countrylanguage
order by language;
Example 5.8. Distinct, yes
select distinct language
from countrylanguage
order by language;
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Group and Having
25
Deadline will generally be the start of the following lesson.
Assignment 5.0
Find your solution to the section called Assignment 2.1. Reformulate the SQL so that it
combines the rows for the three countries:
 Create an SQL declaration that writes an extra column compared to the above. This column
must calculate the number of people speaking that language. Do it by simple arithmetic.
Save the solution in a text file named a50a.sql.
 Change a50a.sql so that it orders the output by language. Hand in as a50b.sql.
 Change a50b.sql so that it groups the output by language. Hand in as a50c.sql.
Assignment 5.1
Integrate a50c.sql into a PHP-program.
Assignment 5.2
Change the SQL in a50c.sql and integrate it into a PHP-program such that it calculates
and list the number of people speaking all languages in the world database.
Assignment 5.3
Try to write an SQL declaration that ranks Danish in the order of languages spoken by most
people in the world as defined by the world database. Hand in as a53.sql.
Assignment 5.4
Write a PHP program that:
 Ranks all the languages, re 5.2.
 Has a form that allows the user to select which language he wants the rank of.
 Highlights the selected table row. You don't need to repeat the result separately.
26
Chapter 6. Entity Relationship Modelling
Table of Contents
References ............................................................................................................... 26
Model Solutions to Assignments from Previous Lesson .............................................. 26
ER Modelling, Semantic Models ............................................................................... 27
A Model .................................................................................................................. 31
Assignments ............................................................................................................. 31
References
[ Lim08 ] Chapter 10.
[ nml99 ] These notes.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
Entity Relationship Modelling
27
ER Modelling, Semantic Models
Figure 6.1. The World database
Above you will find a complete model of the database we have been working with thus far.
Entity Relationship Modelling
28
Figure 6.2. Entity, Relationship, Attributes
In the above figure you will find the graphic objects of the ER-model.
Figure 6.3. The Student - Class Relationship
A relationship is always between entities.
Entity Relationship Modelling
29
Figure 6.4. The Student - Class Relationship, Participation
Participation illustrates the how of the relationship.
Figure 6.5. The Student - Class Relationship, Cardinality
While the cardinality gives the numeric rules.
Entity Relationship Modelling
30
Figure 6.6. Relationship Degrees
Sometimes the relationship is not between two entities.
Entity Relationship Modelling
31
A Model
Figure 6.7. A Wireframe of a CMS
Now, let us play together to see if we can make a model out ot this. Basically we want ALL
content to come from the database.
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
Entity Relationship Modelling
32
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 6.0 - Obligatory
In groups of 2 or 3:
In the textbook there are several references to a forum. Read those references, and then
discuss what a typical forum page could look like. Then create a wireframe diagram. Hand
in the diagram.
Then, based on today's lecture and discussions, please try to list some possible entities, their
attributes, and the relationships between them. This should be done as a verbal exercise. Hand
in the result as a text document.
Finally, try to create an ER-diagram in dia of the model. Save it. Export to png format and
hand that in.
33
Chapter 7. ER, Types and Create
Table of Contents
References ............................................................................................................... 33
Model Solutions to Assignments from Previous Lesson .............................................. 33
Data Types .............................................................................................................. 33
The Creation ............................................................................................................ 35
Assignments ............................................................................................................. 36
References
[ Lim08 ] Chapter 9 - 10.
[ nml99 ] These notes.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
Data Types
Table 7.1. SQL 2003 Datatypes
Category
Type
Comments
Binary
binary large object
(blob)
For pictures, sounds, movies,
and other binary data.
Bit strings
bit, bit varying
Binary or hexidecimal, More
structured than blobs. Vary-
ing means that length may
vary.
Boolean
boolean
Truth values. Strangely,
there's three! true, false,
and unknown
Characters
char, character vary-
ing (varchar), na-
tional charac-
ter (nchar), nation-
al character vary-
ing (nvarchar), char-
acter large objec-
ct (clob), national
character large ob-
ject (nclob)
Strings of charcters from the
relevant character set. Fixed
length or varying length.
Varying more economical,
uses only the length of en-
tereds data while ficed length
equals used length.
Numerical
integer (int), small-
int, numeric, decimal
(dec), float(p,s), re-
Former four are precise, the
rest approximate. Approxi-
mations are given a number
of digits (p) and a number of
ER, Types and Create
34
Category
Type
Comments
al, double precision
(double),
decimals (s). Real and double
have a maximum number of
digits.
Temporal
date, time, time
with zone, timestamp,
timestamp with zone,
interval
No special comments for date
and time. Zone implies a suf-
fix (eg UCT). Interval is used
for elapsed times.
ER, Types and Create
35
The Creation
Figure 7.1. Sample
First we create the database
ER, Types and Create
36
create database newworld;
Then we create tables in that database:
create table country (
code char(3) not null default '',
name char(52) not null default '',
continent enum('Asia','Europe','North America'
,'Africa','Oceania','Antarctica'
,'South America') not null default 'Asia',
region char(26) not null default '',
surfacearea float(10,2) not null default '0.00',
indepyear smallint default null,
population int not null default '0',
lifeexpectancy float(3,1) default null,
gnp float(10,2) default null,
gnpold float(10,2) default null,
localname char(45) not null default '',
governmentform char(45) not null default '',
headofstate char(60) default null,
capital int default null,
code2 char(2) not null default '',
primary key (code),
unique (code2)
)
create table city (
id int not null,
name varchar(42) not null,
countrycode char(3) not null,
district varchar(20) not null,
population int not null,
primary key (id),
foreign key (countrycode) references country(code)
);
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 7.0
Write the create table declarations for the ER model from previous lesson.
37
Chapter 8. Case: Discussion Forum
Table of Contents
References ............................................................................................................... 37
Model Solutions to Assignments from Previous Lesson .............................................. 37
Demoing the sha1() .................................................................................................. 37
Login ....................................................................................................................... 37
Enter Post and Insert into Forum Db ......................................................................... 40
List Posts and Display .............................................................................................. 41
Upload Images ......................................................................................................... 43
Assignments ............................................................................................................. 45
References
[ Lim08 ] Appendix B.
[ nml99 ] These notes.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
Demoing the sha1()
I have written a tiny testpage [../code/sha1demo.php] for sha1().
Login
Example 8.1. The Forum user Table
describe user;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| uid | int(11) | NO | PRI | NULL | auto_increment |
| alias | varchar(16) | NO | UNI | NULL | |
| type | enum('A','O') | NO | | O | |
| pwd | char(40) | NO | | NULL | |
| lastonline | datetime | NO | | NULL | |
+------------+---------------+------+-----+---------+----------------+
Example 8.2. Put a User Into the forum user Table
insert into user
(alias, type, pwd, lastonline)
values
('nml','A',sha1('test'),'2013-03-06');
The above is meant for the login mechanism, that will allow users only to use the pages,
and consequently also to write posts to the forum. In time these posts will be signed by the
authenticated user.
Case: Discussion Forum
38
Figure 8.1. The Login/Front Page for the Forum
Example 8.3. forumindex.php
<?php
session_start();
require_once('DbH.inc.php');
$dbh = new DbH("forum");
require_once("forumauthentication.inc.php");
require_once('HTML5.inc.php');
$doc = new HTML5("The Forum", "en");
print($doc->getTop());
print($doc->prtLink("./ass1M1.css"));
?>
<?php
if (!isset($_SESSION['forumuser'])) {
if (isset($_POST['userid']) && isset($_POST['pwd'])) {
forumAuthenticate($_POST['userid'], $_POST['pwd'], '#', $dbh);
}
}

print($doc->getNeck());
printf("<header><h1>%s</h1></header>", $doc->getTitle());
Case: Discussion Forum
39
include "forumNav.inc.php";
?>
<section id='login'>
<?php
if ( (isset($_GET['errorcode']) && $_GET['errorcode'] == 2)
|| !isset($_SESSION['forumuser'])) {
?>
<p style='color:red; text-align: center;'>Først login!</p>
<form
action="<?php echo 'https://localhost'.$_SERVER['PHP_SELF'];?>"
method="post"
id="formLogin">
<ul>
<li>
<input type='text' name='userid' size='15' /> User id
</li>
<li>
<input type='password' name='pwd' size='15' /> Pwd
</li>
<li>
<button class="colred" type="submit">Login</button>
</li>
</ul>
</form>
<?php
} else {
?>
<p>
<button class="colgron" id="logout"
onclick="window.location='./forumlogout.php'">
Logout
</button>
</p>
<?php
}
?>
</section>
<?php
print($doc->getFoot());
?>

Live on localhost [http://localhost/deformation.org/mdu/sem3/code/forumindex.php]
Example 8.4. forumlogout.php
<?php
session_start();
// get session cookie handle
$_SESSION = array();
// unset all session vars
if (isset($_COOKIE[session_name()])) {
setcookie(session_name(), '', time()-86400, '/');
// get name dynamically
// cookie text blanked
// expired 24 hours ago
// for the whole domain
}
session_destroy();
// destroy session
header("Location: ./forumindex.php");
?>

Case: Discussion Forum
40
Example 8.5. forumauthentication.inc.php
<?php
function forumCheckUser($user, $pwd, $db) {
$sql = "select alias, pwd";
$sql .= " from user";
$sql .= " where pwd = '" . sha1($pwd) . "'";
$sql .= " and alias = '" . $user . "';";
$db->query($sql);
$out = $db->fetch_object();
if ($out->pwd == sha1($pwd) && $out->alias == $user) {
$_SESSION['forumuser'] = $out->alias;
} else {
throw new Exception("3");
}
}

function forumAuthenticate($user, $pwd, $return, $db) {
try {
forumCheckUser($user, $pwd, $db);
}
catch (Exception $e) {
$return .= "?errorcode=3";
header("Location: " . $return);
}
}
?>

Enter Post and Insert into Forum Db
Example 8.6. forumpost.php
<?php
session_start();
if (!isset($_SESSION['forumuser'])) { // am I logged on?
header("Location: ../code/forumindex.php?errorcode=2");
} // if not, go and do it!
$copy = "&copy; NML, 2013";
$w = time();
$today = strftime("%F", $w);
require_once('DbH.inc.php');
require_once('HTML5.inc.php');
$dbh = new DbH("forum");
$doc = new HTML5("The Forum", "en");
print($doc->getTop());
print($doc->prtLink("./ass1M1.css"));
print($doc->prtScript("./forumjslib.js"));
?>
<!-- additional head content here -->
<?php
print($doc->getNeck());
?>
<header>
<h1><?php print($doc->getTitle());?></h1>
</header>
<section>
<h2>Enter Post</h2>
Case: Discussion Forum
41
<?php
printf("<p>\n<b>User:</b> %s\n<b>Date:</b> %s\n</p>"
, $_SESSION['forumuser']
, $today);
include "./postform.inc.php";
?>
</section>
<footer>
<p><?php print($copy);?></p>
</footer>
<?php
print($doc->getFoot());
?>

Example 8.7. forumpostDb.php
<?php
session_start();
if (!isset($_SESSION['forumuser'])) { // am I logged on?
header("Location: ../code/forumindex.php?errorcode=2");
} // if not, go and do it!
if (!(
(isset($_POST['subject']) && $_POST['subject'] != '')
&& (isset($_POST['content']) && $_POST['content'] != '')
)
) {
header("Location: ../code/forumindex.php");
}
require_once('DbH.inc.php');
$dbh = new DbH("forum");
$today = strftime("%F %T", time());
$sql = "insert into post";
$sql .= " (author, clocked, subject, content)";
$sql .= " values";
$sql .= sprintf("('%s', '%s', '%s', '%s');"
, $_SESSION['forumuser']
, $today
, addslashes($_POST['subject'])
, addslashes($_POST['content'])
);
$dbh->query($sql);
$dbh->query('commit;');
header("Location: ../code/forumindex.php");
?>

List Posts and Display
Example 8.8. forumpostlist.php
<?php
session_start();
if (!isset($_SESSION['forumuser'])) { // am I logged on?
header("Location: ../code/forumindex.php?errorcode=2");
} // if not, go and do it!
$copy = "&copy; NML, 2013";
require_once('DbH.inc.php');
require_once('HTML5.inc.php');
Case: Discussion Forum
42
$dbh = new DbH("forum");
$doc = new HTML5("The Forum", "en");
print($doc->getTop());
print($doc->prtLink("./ass1M1.css"));
print($doc->prtScript("./forumjslib.js"));
?>
<!-- additional head content here -->
<?php
print($doc->getNeck());
?>
<header>
<h1><?php print($doc->getTitle());?></h1>
</header>
<section>
<?php
$sql = sprintf("select author, clocked, subject
from post
order by clocked desc;");
if (isset($_GET['t'])) {
$sql = sprintf("select post.author, post.clocked, post.subject, post.content
from post left join thread
on thread.author = post.author
and thread.clocked = post.clocked
order by post.subject, post.clocked;");
}
$dbh->query($sql);
print("<table>");
$caption = "Most Recent Posts";
if (isset($_GET['t'])) {
$caption = "Threads";
}
printf("<caption>%s</caption>", $caption);
while ($post = $dbh->fetch_array()) {
printf("<tr>
<td>%s</td>
<td>%s</td>
<td><a href='./forumpostdisplay.php?au=%s&amp;cl=%s'>%s</a></td>
</tr>\n"
, $post['author']
, $post['clocked']
, $post['author']
, $post['clocked']
, $post['subject']);
}
print("</table>");
?>
<nav>
<ul>
<li>
<a href="./forumindex.php">Go Back to the Menu</a>
</li>
</ul>
</nav>
</section>
<footer>
<p><?php print($copy);?></p>
</footer>
<?php
print($doc->getFoot());
?>

Live on localhost [http://localhost/deformation.org/mdu/sem3/code/forumpostlist.php]
Case: Discussion Forum
43
Upload Images
Example 8.9. forumimageupload.php
<?php
session_start();
if (!isset($_SESSION['forumuser'])) { // am I logged on?
header("Location: ../code/forumindex.php?errorcode=2");
} // if not, go and do it!
$copy = "&copy; NML, 2013";
$w = time();
$today = strftime("%F", $w);
require_once('DbH.inc.php');
require_once('HTML5.inc.php');
$dbh = new DbH("forum");
$doc = new HTML5("The Forum", "en");
print($doc->getTop());
print($doc->prtLink("./ass1M1.css"));
print($doc->prtScript("./forumjslib.js"));
?>
<!-- additional head content here -->
<?php
print($doc->getNeck());
?>
<header>
<h1><?php print($doc->getTitle());?></h1>
</header>
<section>
<h2>Upload an Image</h2>
<?php
printf("<p>\n<b>User:</b> %s\n<b>Date:</b> %s\n</p>"
, $_SESSION['forumuser']
, $today);
?>
<form action="./forumimagepostDb.php"
enctype="multipart/form-data"
method="post">
<p>
<label for="img">Image:</label>
<br/>
<input type="file" name="image" id="img"/>
<input name="MAX_FILE_SIZE" value="65535" type="hidden"/>
</p>
<p>
<label for="alt">Alternate Text:</label>
<br/>
<input type="text" name="alttext" id="alt"
size="64" maxlength="96"/>
</p>
<p>
<label for="cap">Caption:</label>
<br/>
<input type="text" name="caption" id="cap"
size="64" maxlength="96"/>
</p>
<p>
<label for="avaj">Avatar:</label>
<input type="radio" name="avatar" id="avaj"/>
<label for="avan">Not Avatar:</label>
Case: Discussion Forum
44
<input type="radio" name="avatar" id="avan" checked/>
</p>
<p>
<label for="copy">Copyright:</label>
<input type="radio" name="copyright" id="copy" checked/>
<label for="nocopy">Not Copyright:</label>
<input type="radio" name="copyright" id="nocopy"/>
</p>
<p>
<input type="submit" value="Post"/>
</p>
</form>
</section>
<footer>
<p><?php print($copy);?></p>
</footer>
<?php
print($doc->getFoot());
?>

Live on localhost [http://localhost/deformation.org/mdu/sem3/code/forumimageupload.php]
Example 8.10. forumimagepostDb.php
<?php
session_start();
if (!isset($_SESSION['forumuser'])) { // am I logged on?
header("Location: ../code/forumindex.php?errorcode=2");
} // if not, go and do it!
if (!(
(isset($_POST['alttext']) && $_POST['alttext'] != '')
&& (isset($_POST['caption']) && $_POST['caption'] != '')
&& (isset($_FILES['image']) && $_FILES['image']['size'] > 0)
)
) {
header("Location: ../code/forumindex.php?x=1");
}
require_once('DbH.inc.php');
$dbh = new DbH("forum");
$today = strftime("%F %T", time());
// Temporary file name stored on the server
$tmpName = $_FILES['image']['tmp_name'];
// Read the file
$fp = fopen($tmpName, 'r');
$image = fread($fp, filesize($tmpName));
$image = addslashes($image);
fclose($fp);
$avatar = false;
if ($_POST['avatar'] == 'on')
$avatar = true;
$copyr = true;
if ($_POST['copyright'] == 'off')
$copyr = false;
$sql = "insert into image";
$sql .= " (mimetype, alttext, caption, avatar";
$sql .= ", copyrightrestricted, user, imageitself)";
$sql .= " values";
$sql .= sprintf("('%s','%s','%s','%s','%s','%s','%s')"
, $_FILES['image']['type']
Case: Discussion Forum
45
, $_POST['alttext']
, $_POST['caption']
, $avatar
, $copyr
, $_SESSION['forumuser']
, $image
);
$dbh->query($sql);
$dbh->query('commit;');
header("Location: ../code/forumindex.php?x=2");
?>

Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 8.0
Install the forum programs from todays lesson in your forum project directory.
When clicking on a post in the list of posts, the click should take you to a program displaying
the post in it's entirety, and supply a button or link to allow the reader to post a reply.
Assignment 8.1
Augment the program in which to enter new forms so that it accomodates displaying a post,
if any, to which the post being entered is a reply.
Please notice that when posting a reply in the post table, you must also insert a row into the
thread table in order to connect the reply with the original post the user is replying to. This
means two inserts into the database. Therefore the two inserts must be spanned by a start
transaction;, end transaction; block.
The insert sequence will be, in pseudocode:
start transaction;
insert into post ...;
Case: Discussion Forum
46
insert into thread ...;
end transaction;
commit;
47
Chapter 9. Case: Discussion Forum
Table of Contents
References ............................................................................................................... 47
Model Solutions to Assignments from Previous Lesson .............................................. 47
Display Uploaded Images - an Example .................................................................... 47
Assignments ............................................................................................................. 48
References
[ Lim08 ] Appendix B.
[ nml99 ] These notes.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
Display Uploaded Images - an Example
Example 9.1. forumimagedisplaySolo.php
<?php
session_start();
if (!isset($_SESSION['forumuser'])) { // am I logged on?
header("Location: ../code/forumindex.php?errorcode=2");
} // if not, go and do it!
$copy = "&copy; NML, 2013";
$w = time();
$today = strftime("%F", $w);
require_once('DbH.inc.php');
require_once('HTML5.inc.php');
$dbh = new DbH("forum");
$doc = new HTML5("The Forum", "en");
print($doc->getTop());
print($doc->prtLink("./ass1M1.css"));
?>
<!-- additional head content here -->
<style>
#sep {
border-top: 2px solid blue;
}
</style>
<?php
print($doc->getNeck());
printf("<header><h1>%s</h1></header>", $doc->getTitle());
include "forumNav.inc.php";
?>
<section>
<h2>Display Images</h2>
Case: Discussion Forum
48
<?php
printf("<p>\n<b>User:</b> %s\n<b>Date:</b> %s\n</p>"
, $_SESSION['forumuser']
, $today);

$sql = "select id, mimetype, alttext, caption, avatar
, copyrightrestricted
, user";
$sql .= " from image;";
$dbh->query($sql);
print("<table>");

while($out = $dbh->fetch_object()) {
printf("<tr id='sep'><td>%s</td>
<td rows='7'><img src='forumImageView.php?pid=%s'/></td></tr>\n"
, $out->id, $out->id
);
printf("<tr><td>%s</td></tr>\n", $out->mimetype);
printf("<tr><td>%s</td></tr>\n", $out->alttext);
printf("<tr><td>%s</td></tr>\n", $out->caption);
printf("<tr><td>%s</td></tr>\n", $out->avatar);
printf("<tr><td>%s</td></tr>\n", $out->copyrightrestricted);
printf("<tr><td>%s</td></tr>\n", $out->user);
}
print ("</table>");
?>
<?php
print($doc->getFoot());
?>

Live on localhost [http://localhost/deformation.org/mdu/sem3/code/
forumimagedisplaySolo.php]
Example 9.2. forumImageView.php
<?php
require_once('DbH.inc.php');
$dbh = new DbH("forum");
if(isset($_GET['pid']) && is_numeric($_GET['pid'])) {
$sql = "select mimetype, imageitself";
$sql .= " from image";
$sql .= " where id = " . $_GET['pid'];
$result = $dbh->query($sql)
or die("Error:"."<br/>".$sql);
$out = $dbh->fetch_array($result);
header("Content-type: " . $out['mimetype']);
echo $out['imageitself'];
}

Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Case: Discussion Forum
49
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 9.0
Functionality still missing in our forum:
 User self creation?
 Use images in post?
 Update/delete posts?
 Update/delete images?
 Update/delete users?
?
Choose one or two from the above list ind code them.
Consider updating the menu to cater for your choices.
Hand in with assignments from lesson 8.
50
Chapter 10. Case: Discussion Forum Presentation
Table of Contents
References ............................................................................................................... 50
 ............................................................................................................................ 50
Assignments ............................................................................................................. 50
References
[ Lim08 ] Appendix B.
[ nml99 ] These notes.

This lesson is reserved to finish assignments from lesson 8 and 9, the forum assignments.
Your's truly will be present for supervision and help.
Assignments
Handing In Assignments
What?Hand in ONE, repeat 1, compressed archive, ie zip/jar/tgz-file.
Content Text files in pdf-format, or
code files in x.java, y.html, z.sql, etc.
Filenames The archive file must be named as follows: EAANILA1_n.zip.
Where EAANILA1 is your login id,
and n is the current number of the lesson.
zip, or one of the other file formats, re first point above.
Where and When In the appropriate hand in folder in fronter.com/eaaa.
Deadline will generally be the start of the following lesson.
Assignment 10.0
This lesson was reserved to finish assignments from lesson 8 and 9, the forum assignments.
51
Chapter 11. Design to Reality
Table of Contents
References ............................................................................................................... 51
Model Solutions to Assignments from Previous Lesson .............................................. 51
ER2RM ................................................................................................................... 51
Step 1 - Regular (strong) entities ............................................................................ 52
Step 2 - Weak Entities ........................................................................................... 52
Step 3a - 1:1 Relationships ..................................................................................... 54
Step 3b - 1:1 Relationships .................................................................................... 55
Step 4a - 1:N Relationships .................................................................................... 56
Step 4b - 1:N Relationships .................................................................................... 58
Step 5 - N:M ......................................................................................................... 59
Step 6 - Multivalued attributes ................................................................................ 60
Step 7 - N-ary relationships .................................................................................... 61
Step 8 - Generalizations/Specializations .................................................................. 63
Step 9 - Aggregations ............................................................................................ 64
Assignments ............................................................................................................. 65
References
[ Lim08 ] Chapter 10.
[ nml99 ] These notes.
Model Solutions to Assignments from
Previous Lesson
Please navigate to Appendix B, Model Solutions to Assignments.
ER2RM
 Entity Relationship [Model] to Relational Model!
 This process is a 9 step process of transformation
 The ER diagram will be transformed into the Relational Model
 This mean reading the diagram and creating CREATE TABLE declarations
Design to Reality
52
Step 1 - Regular (strong) entities
Figure 11.1. Input 1st step
 All attributes atomically into a create table
 Select a primary key from the candidate keys
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
create table e2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
);
Step 2 - Weak Entities
Figure 11.2. Input 2nd step
 All attributes atomically into a create table
Design to Reality
53
 Appoint the primary key from the strong entity as primary key
 Declare the primary key attribute as foreign key as well
 Add a serial no if necessary
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
);
create table we(
ck <datatype> not null,
lnr <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck, lnr),
foreign key(ck) references e1(ck)
);
Design to Reality
54
Step 3a - 1:1 Relationships
Figure 11.3. Input 3rd step
 Total participation
 Two possible design options
 Heuristic design, the default, aka Bjørns Method
 Alternative design, only if total participation
 Choose alternative design
 Foreign key must be declared
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
Design to Reality
55
create table e2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
ai <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(ai),
foreign key(ai) references e1(ck)
);
Step 3b - 1:1 Relationships
Figure 11.4. Input 3rd step
 Partial participation
 Heuristic design
 Primary key, contribution from either, not both
 Declare foreign keys
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
Design to Reality
56
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
create table e2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
);
create table r (
cka <datatype> not null,
ckb <datatype> not null,
primary key(?),
unique(?),
foreign key(cka) references e1(ck),
foreign key(ckb) references e2(ck)
);
Step 4a - 1:N Relationships
Figure 11.5. Input 4th step
Design to Reality
57
 Total participation
 Alternative design
 Foreign key attribute on n-side
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
create table e2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
ai <datatype> not null,
an <datatype> not null,
primary key(ck),
foreign key(ai) references e1(ck)
);
Design to Reality
58
Step 4b - 1:N Relationships
Figure 11.6. Input 4th step
 Partial participation
 Heuristic design
 Primary key contributions also declared as foreign keys
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
create table e2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
Design to Reality
59
);
create table r (
cka <datatype> not null,
ckb <datatype> not null,
primary key(?),
unique(?),
foreign key(cka) references e1(ck),
foreign key(ckb) references e2(ck)
);
Step 5 - N:M
Figure 11.7. Input 5th step
 Total or partial participation
 Always heuristic design
 Possibly composite key
 Key contributions from n-participation entities
 Primary key contributions also declared as foreign keys
Design to Reality
60
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
create table e2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
);
create table r (
cka <datatype> not null,
ckb <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(?),
unique(?),
foreign key(cka) references e1(ck),
foreign key(ckb) references e2(ck)
);
Step 6 - Multivalued attributes
 Have you done atomic creation of attributes
 This step has been done already
 If not, change the multivalued attributes to individual attributes
 Change your create table declarations accordingly
Design to Reality
61
Step 7 - N-ary relationships
Figure 11.8. Input 7th step
 Any participation
 Always heuristic design
 Possibly composite key
 Key contributions from n-participation entities
 Primary key contributions also declared as foreign keys
create table e1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
);
create table e2(
ck <datatype> not null,
Design to Reality
62
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
);
create table en(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck)
);
create table r (
cka <datatype> not null,
ckb <datatype> not null,
ckn <datatype> not null,
primary key(?),
unique(?),
foreign key(cka) references e1(ck),
foreign key(ckb) references e2(ck)
foreign key(ckn) references en(ck),
);
Design to Reality
63
Step 8 - Generalizations/Specializations
Figure 11.9. Input 8th step
 Individual relationships between superclass and each subclass
 Is-a
 Therefore subclass gets same key as superclass
 Subclass primary key also foreign key
create table sup1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
unique(an)
Design to Reality
64
);
create table sub1(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
foreign key(ck) references sup1(ck)
);
create table sub2(
ck <datatype> not null,
a1 <datatype> not null,
a2 <datatype> not null,
an <datatype> not null,
primary key(ck),
foreign key(ck) references sup1(ck)
);
Step 9 - Aggregations