Relational Database
Systems
Higher Information Systems
Advanced Implementation in
MySQL/PHP
Developing a solution
Tables
Queries
User Views
Navigation
Introducing SQL
Relational database management
systems (RDBMSs) are based on a
data definition language called
Structured Query Language or SQL
(often pronounced sequel).
MySQL takes its name from SQL.
Introducing SQL
SQL is a data definition language
CREATE DATABASE `DVD Rentals`
CREATE TABLE Member(
`Member Number` integer,
Name varchar(30),
Address varchar(50),
`Post Code` varchar(7),
`Telephone Number` varchar(11))
Introducing SQL
SQL is also a data manipulation language
INSERT INTO Member
VALUES (`233`, `Jo Soap`, `1 Getting Close`,
`IS99 2QZ`, `123456`)
SELECT * FROM Member
WHERE `Member Number` BETWEEN 1 and
250
ORDER BY `Member Number` DESC
DELETE FROM Member
WHERE `Member Number` > 500
phpMyAdmin
A graphical user interface for
MySQL
phpMyAdmin
Viewing a table definition
phpMyAdmin
Adding a record
phpMyAdmin
Selecting records
phpMyAdmin
Viewing selected records
phpMyAdmin
Viewing selected records
Referential Integrity
For the database to work properly, it is
essential to guarantee that a foreign key
always refers to a record which exists in
the other table.
This is called referential integrity.
For example, an entry in the Loan table
can only refer to an existing record in
the Member table, because a loan can
only be made by a member.
Referential Integrity
MySQL has no internal facility to maintain
referential integrity (with the default
MyISAM table type) - instead, the
databases interface must be designed in
such a way that a check is made on any
value entered to ensure it is valid.
Usually this is done by using a list on a
web page to display the available choices
for a field value from which the user may
make a selection.
Validation: Presence Check
Validation: Range Check
Range checking is performed by
the interface
Use PHP scripting to check numeric
values
Validation: Restricted
Choice Check
Validation: Default Value
Formatting
All formatting of data for output is
done using PHP functions
e.g. printf
Queries
Searching
Sorting
Calculations
All searching, sorting and
calculation queries are performed
using an SQL expression string
Searching
Searching is the process of selecting
records from a table or combination of
tables.
To perform the query, three items must
be identified:
Which fields will be used to identify the
records required?
Which tables are required to produce the
data required?
What are the criteria for identifying the
records required?
Search 1: Single Condition
Identify the names and telephone
numbers of club members with
Member Number over 1000
SELECT FORENAME, SURNAME,
`TELEPHONE NUMBER`
FROM MEMBER
WHERE `MEMBER NUMBER` >
1000
Search 1: Single Condition
The asterisk * symbol can be used
in an expression string to extract
all fields from a table
SELECT *
FROM MEMBER
WHERE `MEMBER NUMBER` >
1000
Search 2:
Multiple Conditions (AND)
Identify the names and telephone
numbers of club members with
Surnames beginning with M and
Member Number over 1000
SELECT FORENAME, SURNAME,
`TELEPHONE NUMBER`
FROM MEMBER
WHERE SURNAME LIKE M* AND
`MEMBER NUMBER` > 1000
Search 3:
Multiple Conditions (OR)
Identify the names and telephone
numbers of club members with
Surnames beginning with M or N
SELECT FORENAME, SURNAME,
`TELEPHONE NUMBER`
FROM MEMBER
WHERE SURNAME LIKE M* OR
SURNAME LIKE N*
Search 4: More than one
table
Identify the names of club members who rented
a DVD in August 2005
In order to extract the related records from each
table, a join operation is performed in SQL
This will combine those records from each table
which have a corresponding record in the other
table into a single set of related records (called
an answer set)
The keyword used to perform this is INNER JOIN
Search 4: More than one
table
Identify the names of club members
who rented a DVD in August 2005
SELECT FORENAME, SURNAME, `DATE
HIRED`
FROM (MEMBER INNER JOIN LOAN) ON
`[Link]
NUMBER`=`[Link] NUMBER`
WHERE `DATE HIRED` LIKE */8/2005
Search 5:
More than two tables
Identify the names of members who
have rented Shrek
This time all four tables are required:
Member, Loan, DVD and Film.
A nested INNER JOIN statement must
be performed to combine these
together into a single answer set
Search 5:
More than two tables
Identify the names of members who have
rented Shrek
SELECT `FORENAME`, `SURNAME`, `TITLE`
FROM
(((MEMBER INNER JOIN LOAN
ON [Link] NUMBER=[Link]
NUMBER)
INNER JOIN DVD
ON [Link] CODE = [Link] CODE)
INNER JOIN FILM
ON [Link] CODE = [Link] CODE)
WHERE `TITLE` = SHREK
Search 6:
Dealing with duplicates
List those members who have rented
Shrek or Finding Nemo
SELECT `FORENAME`, `SURNAME`, `TITLE`
FROM (((MEMBER INNER JOIN LOAN
ON [Link] NUMBER=[Link]
NUMBER)
INNER JOIN DVD
ON [Link] CODE = [Link] CODE)
INNER JOIN FILM
ON [Link] CODE = [Link] CODE)
WHERE `TITLE` = SHREK OR `TITLE` = FINDING
NEMO
Search 6:
Dealing with duplicates
The Group By option is used to eliminate
duplicate results
SELECT `FORENAME`, `SURNAME`, `TITLE`
FROM (((MEMBER INNER JOIN LOAN
ON [Link] NUMBER=[Link] NUMBER)
INNER JOIN DVD
ON [Link] CODE = [Link] CODE)
INNER JOIN FILM
ON [Link] CODE = [Link] CODE)
WHERE `TITLE` = SHREK OR `TITLE` = FINDING
NEMO
GROUP BY *
Sorting
To perform a sort, two items must
be identified:
Which field (or fields) will be used to
decide the order of records?
For each field selected, will the order
of sorting be ascending or
descending?
Sorting
To produce a list of people with the
tallest first, the records would be sorted
in descending order of height.
To produce a list of people with
youngest first, the records would be
sorted in ascending order of age.
A very common way of ordering records
relating to people is in alphabetical
order. To achieve alphabetical ordering
requires the records to be sorted in
ascending order of surname.
Complex Sorting
A complex sort involves more than one
sort condition involving two or more fields.
To achieve telephone book order, the
name is sorted in ascending order of
surname, then ascending order of
forename. In this case, the Surname field
is the primary sort key, and the
Forename field is the secondary sort
key.
Sorting
SELECT FORENAME, SURNAME
FROM MEMBER
WHERE `MEMBER NUMBER` > 1000
ORDER BY SURNAME, FORENAME
SELECT FORENAME, SURNAME
FROM MEMBER
WHERE `MEMBER NUMBER` > 1000
ORDER BY `MEMBER NUMBER` DESC
Calculations
Horizontal calculations are often known as calculated fields, and
vertical calculations are known as summary fields.
Functions (MySQL)
Functions (PHP)
Working with Dates
to search for all DVDs rented in the last
week, you would use the expression
SELECT * FROM LOAN WHERE
DATEDIFF(CURDATE(),`DATE HIRED`) < 7
to calculate a persons age in years from
their date of birth, you would use the
expression
SELECT (YEAR(CURDATE()) YEAR(`DATE
OF BIRTH`)
(RIGHT(CURDATE(),5)<RIGHT(`DATE OF
BIRTH`,5)) AS AGE
Parameterised Queries
User Views
User views are created using forms and
reports.
A form or report is usually based on a
query which selects the required fields
from the appropriate tables, sorting the
results if necessary, and performing any
horizontal calculations.
In a MySQL application, all output is
generally in the form of a web page.
Report Structure
Summary Information
Sum
to add values to give a total, e.g. Total
Cost of DVD Rentals last month
Average to find an average value, e.g. Average
Cost of Hire per DVD
Count
to count the number of records found,
e.g. Number of DVDs rented per
member
Maximum
to find the highest value, e.g.
Highest number of rentals per DVD (to
find the most popular DVD)
Minimum to find the lowest value, e.g. Lowest
number of rentals per member
Summary Information
In a MySQL application, the aggregate
functions SUM, AVG, COUNT, MAX and
MIN can be used in an SQL expression
string to produce summary information
for a set of extracted records
However, certain summary information,
such as page totals, would have to be
programmed using PHP (e.g. counting
the number of records being displayed)
Scripting
In a MySQL application, all navigation
and user interface features are
implemented using scripting
$result = @mysql_query("SELECT Forename, Surname
FROM Member");
while ( $row = mysql_fetch_array($result) ) {
$forename = $row["Forename"];
$surname = $row["Surname"];
echo("<tr><td>$forename</td><td>$surname</td>
</tr>");
}
Macros