1 / 45

Relational Database Systems

Relational Database Systems. Higher Information Systems Advanced Implementation in MySQL/PHP. Developing a solution. Tables Queries User Views Navigation. Introducing SQL.

svein
Télécharger la présentation

Relational Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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` > 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 database’s 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` > 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` > 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` > 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 NUMBER`=`LOAN.MEMBER NUMBER`WHERE `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 NUMBER=LOAN.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 NUMBER=LOAN.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 NUMBER=LOAN.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”GROUP 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 ascendingorder 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` > 1000ORDER BY SURNAME, FORENAME • SELECT FORENAME, SURNAMEFROM MEMBER WHERE `MEMBER NUMBER` > 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`)< 7 • to calculate a person’s 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

  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 = @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>"); }

  45. Macros

More Related