Relational Database Systems

1
Relational Database Systems

• Higher Information Systems
• Advanced Implementation in MySQL/PHP

2
Developing a solution

• Tables
• Queries
• User Views
• Navigation

3
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.

4
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))

5
Introducing SQL

• SQL is also a data manipulation language
• INSERT INTO MemberVALUES (233, Jo Soap, 1
  Getting Close, IS99 2QZ, 123456)
• SELECT FROM MemberWHERE Member Number
  BETWEEN 1 and 250ORDER BY Member Number DESC
• DELETE FROM MemberWHERE Member Number gt 500

6
phpMyAdmin

• A graphical user interface for MySQL

7
phpMyAdmin

• Viewing a table definition

8
phpMyAdmin

• Adding a record

9
phpMyAdmin

• Selecting records

10
phpMyAdmin

• Viewing selected records

11
phpMyAdmin

• Viewing selected records

12
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.

13
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.

14
Validation Presence Check
15
Validation Range Check

• Range checking is performed by the interface
• Use PHP scripting to check numeric values

16
Validation Restricted Choice Check
17
Validation Default Value
18
Formatting

• All formatting of data for output is done using
  PHP functions
• e.g. printf

19
Queries

• Searching
• Sorting
• Calculations
• All searching, sorting and calculation queries
  are performed using an SQL expression string

20
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?

21
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 gt 1000

22
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 gt
  1000

23
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 gt 1000

24
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

25
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

26
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 MEMBER.MEMBER
  NUMBERLOAN.MEMBER NUMBERWHERE DATE HIRED
  LIKE /8/2005

27
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

28
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 MEMBER.MEMBER
  NUMBERLOAN.MEMBER NUMBER) INNER JOIN DVD ON
  LOAN.DVD CODE DVD.DVD CODE)INNER JOIN FILM ON
  DVD.FILM CODE FILM.FILM CODE)WHERE TITLE
  SHREK

29
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 MEMBER.MEMBER
  NUMBERLOAN.MEMBER NUMBER) INNER JOIN DVD ON
  LOAN.DVD CODE DVD.DVD CODE)INNER JOIN FILM ON
  DVD.FILM CODE FILM.FILM CODE)WHERE TITLE
  SHREK OR TITLE FINDING NEMO

30
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 MEMBER.MEMBER
  NUMBERLOAN.MEMBER NUMBER) INNER JOIN DVD ON
  LOAN.DVD CODE DVD.DVD CODE)INNER JOIN FILM ON
  DVD.FILM CODE FILM.FILM CODE)WHERE TITLE
  SHREK OR TITLE FINDING NEMOGROUP BY

31
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?

32
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.

33
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.

34
Sorting

• SELECT FORENAME, SURNAMEFROM MEMBER WHERE
  MEMBER NUMBER gt 1000ORDER BY SURNAME, FORENAME
• SELECT FORENAME, SURNAMEFROM MEMBER WHERE
  MEMBER NUMBER gt 1000ORDER BY MEMBER NUMBER
  DESC

35
Calculations

• Horizontal calculations are often known as
  calculated fields, and vertical calculations are
  known as summary fields.

36
Functions (MySQL)
37
Functions (PHP)
38
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) lt 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)ltRIGHT(DATE OF BIRTH,5)) AS
  AGE

39
Parameterised Queries
40
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.

41
Report Structure
42
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

43
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)

44
Scripting

• In a MySQL application, all navigation and user
  interface features are implemented using
  scripting
• result _at_mysql_query("SELECT Forename, Surname
  FROM Member")
• while ( row mysql_fetch_array(result) )
• forename row"Forename"
• surname row"Surname"
• echo("lttrgtlttdgtforenamelt/tdgtlttdgtsurnamelt/tdgtlt/tr
  gt")

45
Macros