Credentials Presentation

1
Introduction to Database Design and Programming
2
Objectives

• In this lesson, you will learn
• To understand the relational database model.
• To be able to write database queries using SQL
  (Structured Query Language).
• To understand the MySQL database server.
• To learn various database interfaces.

3
Introduction

• Database
• Integrated collection of data
• Database management system (DBMS)?
• Store and organize data consistent with
  databases format
• Relational database
• SQL (Structured Query Language)?
• Queries
• Manipulate data

4
Relational Database Model

• Composed of tables
• Row
• Composed of column
• Primary key
• Reference data in the table
• A column or set of columns in table contains
  unique data

5
Relational Database Model
Relational database structure of an student table.
6
Relational Database Model
id
lastname
23603
Hadad
24568
Harris
34589
Hilliers
Table formed by selecting id and lastname data
from the Students table.
7
Relational Database Overview

• Primary key uniquely identifies each row
• Rule of Entity Integrity
• Lines connecting tables
• Relationships
• One-to-many relationship (most common)?
• Many-to-many (may indicate complex data)?
• One-to-one (usually extra info on another table)?
• Foreign key
• Join multiple tables
• Rule of Referential Integrity
• The referential integrity rule states that if a
  relational table has a foreign key, then every
  value of the foreign key must either be null or
  match the values in the relational table in which
  that foreign key is a primary key.

8
Relational Database Overview
9
Relational Database Overview
10
Relational Database Overview Photo gallery
Database
11
Relational Database Overview Photo gallery
Database
12
Relational Database Overview Photo gallery
Database
13
Relational Database Overview Photo gallery
Database
14
22.3  Relational Database Overview Books.mdb
Database
Fig. 22.11 Table relationships in Books.mdb.
15
SQL (Structured Query Language)
16
Basic SELECT Query

• SELECT FROM tableName
• SELECT FROM photos
• SELECT photographerID, lastName FROM
  photographers

17
Basic SELECT Query
18
WHERE Clause

• Specify selection criteria for query
• SELECT columnName1, columnName2, FROM tableName
  WHERE criteria
• SELECT photoID, name, description FROM photos
  WHERE photographer Brian Ferry

19
WHERE Clause
20
WHERE Clause
21
WHERE Clause
22
ORDER BY Clause

• Arranged in ascending or descending order
• SELECT columnName1, columnName2, FROM tableName
  ORDER BY column ASC
• SELECT photographerID, firstName, lastName FROM
  photographers ORDER BY lastName ASC
• SELECT columnName1, columnName2, FROM tableName
  ORDER BY column DESC
• SELECT photographerID, firstName, lastName FROM
  photographers ORDER BY lastName DESC

23
INSERT Statement

• Inserts new row in table
• INSERT INTO tableName ( columnName1, columnName2,
  , columnNameN ) VALUES ( value1 , value2, ,
  valueN )?

24
UPDATE Statement

• Modifies data in a table
• UPDATE tableName SET columnName1 value1,
  columnName2 value2, , columnNameN valueN
  WHERE criteria

25
DELETE Statement

• Removes data from a table
• DELETE FROM tableName WHERE criteria

26
CREATE Statement

• Creates a new table
• CREATE TABLE databaseName.books
• (ID VARCHAR( 10 ) NOT NULL ,
• title VARCHAR( 100 ) NOT NULL ,
• publisher VARCHAR( 50 ) NOT NULL ,
• price DOUBLE NOT NULL ,
• studentId INT NULL ,
• PRIMARY KEY ( isbn ) )?

27
INSERT Statement

• Creates a new table
• INSERT INTO greenjay_tutorial.books (
• isbn, title, publisher, price,
  studentId
• )VALUES (
• '059610197X', 'Head First HTML with CSS XHTML
  ', 'O''Reilly Media, Inc.', '39.95', NULL
• )?

28
Interfacing with a database

• Seven languages that interface to database
  modules
• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle
• Sybase
• FrontBase
• ODBC (Open Database Connectivity)?

29
MySQL

• Multi-user and multi-threaded RDBMS server
• Uses SQL to interact with and manipulate data
• Supports various programming languages
• Access tables from different databases
• Handle large databases

30
Database access and php

• We use a set of php functions to talk to the DB
• mysql_connect("db_location", "login",
  "password")
• mysql_error()?
• mysql_select_db("db_name", connection)?
• mysql_query("SELECT FROM students")?
• mysql_fetch_array(result_students)?
• mysql_close(connection)?
• We can then use a loop to go through each row of
  the database results
• This loop can then print out html around the
  variables that we get from the MySQL results

31
Web Resources

• www.sql.org
• www.mysql.com
• www.microsoft.com/sql
• www.microsoft.com/sql/downloads/default.asp
• www.postgresql.org
• www.interbase.com
• www.maverick-dbms.org
• www.devshed.com
• www.cql.com
• leap.sourceforge.net
• www.voicenet.com/gray/Home.html
• msdn.microsoft.com/library/devprods/vs6/vstudio/md
  ac200/mdac3sc7.htm
• www.w3schools.com/sql
• www.sqlmag.com