Developing Web Applications

1
Lecture 8 Introduction to database and MySQL
2
Typical Web Database Topology
3
Database Basics
Relationships among database, DBMS, and programs
4
What is a database?
5
Parts of a database

• Attributes (fields)
• An attribute or field is a component of a record
  that describes something about an item.
• Records (Tuples)
• A record is the representation of an individual
  item.
• Table (Relation)
• A collection of records
• Database
• A collection of tables and rules for accessing
  the tables

6
What is a relational database?

• Originally developed by E.F. Codd in 1970
• Organizes data into tables which have rows and
  columns of data
• Each row (formally called record or tuple) is a
  data item
• Each column (also called field) is a data
  attribute
• Different from flat file databases because you
  can define relationships between items in
  different tables.

7
Parts of a database
Record/Tuple
Tables
Attribute/Field

• Records become rows
• Attributes become columns
• Rules determine the relationship between the
  tables and tie the data together to form a
  database

8
The relational data model concepts

• Based on a simple data structure relation (or
  table)
• All entity and relationship sets are represented
  as tables
• Each row in a relation (a tuple)- represents an
  individual occurrence of that entity- each tuple
  must be uniquely identified by one or more
  attributes (the primary key)- no part of the
  primary key may have a null value (entity
  integrity rule)- each attribute must be an
  atomic ie a single value drawn from the domain of
  the attribute

9
Creating a database

• What information are we trying to store?
• How do we describe the information?
• Phone Book/Contact entries
• Name
• Address
• Company
• Phone Number
• URL/Web Page
• Age
• Height (in metres)
• Birthday
• When we added the entry

10
Data Types

• Binary
• Database specific binary objects
• Pictures, digital signatures, etc.
• Boolean
• True/False values
• Character
• Fixed width or variable size
• Numeric
• Integer, Real (floating decimal point), Money
• Temporal
• Time, Date, Timestamp

11
Phone Book/Contact Record

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

12
Normal Forms
13
What are the normal forms?

• Normal forms reduce the amount of redundancy and
  inconsistent dependency within databases.
• In 1972 Codd proposed three normal forms and
  through the years two more have been added.

14
The Zero Form

• Where most people start (and stop)
• No room for growth
• Usually wastes space

15
First Normal Form

• Eliminate repeating columns in each table
• Create a separate table for each set of related
  data
• Identify each set of related data with a primary
  key

Benefits Now we can have infinite phone numbers
or company addresses for each contact. Drawback
Now we have to type in everything over and over
again. This leads to inconsistency, redundancy
and wasting space. Thus, the second normal form
16
Second Normal Form

• Create separate tables for sets of values that
  apply to multiple records
• Relate these tables with a foreign key.

17
Third Normal Form

• Eliminate fields that do not depend on the
  primary key.

Is this enough? Codd thought soBut other forms
have been developed!
18
Why normalise?

• Increases the integrity of the data
• Reduces redundancy
• Improves efficiency
• Although normalisation can be hard, it is worth
  it in the long run.

19
Database Tables

• A database contains zero or more tables. Each
  table is identified by a name (e.g. "Customers"
  or "Orders"). Each table is a set of records
  (rows or tuples).
• An example -- a table called "Persons"
• LastName FirstName Address City
• Hansen Ola Timoteivn 10 Sandnes
• Svendson Tove Borgvn 23 Sandnes
• Pettersen Kari Storgt 20 Stavanger
• This table ontains three records (one for each
  person) and four columns (LastName, FirstName,
  Address, and City).

20
Queries

• A query is a request for specific information and
  has a recordset returned.
• Queries are formulated in the famous SQL language
• SELECT LastName FROM Persons
• The query above selects all the data in the
  "LastName" column from the "Persons" table, and
  will return a recordset like this
• LastName
• Hansen
• Svendson
• Pettersen

21
Basic SQL Commands

• Creating tables with CREATE
• Adding data with INSERT
• Viewing data with SELECT
• Removing data with DELETE
• Modifying data with UPDATE
• Destroying tables with DROP
• In PHP all these commands are issued by the
  mysql_query() function

22
MySQL

• MySQL is the most popular open-source database
  system.
• Databases are useful to store data permanently.
• MySQL is a relational database system, so
  table/row (tuple)/column (attribute).
• MySQL can be scaled down to support embedded
  database applications. This is a great thing.
• In fact MySQL is the de-facto standard database
  for web sites that support huge volumes of data
  (Yahoo, Google).

23
Connect to a Database

• Before you can query a database, you must create
  a connection to it.
• In PHP, this is done with the mysql_connect()
  function.
• Syntax mysql_connect(servername,username,password
  )
• Parameter Description
• servername Optional. Specifies the server to
  connect to. Default value is "localhost3306"
• username Optional. Specifies the username to
  log in with. Default value is the name of the
  user that owns the server process
• password Optional. Specifies the password to
  log in with. Default is ""

24
Example

• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• // some code ?gt

25
Closing a Connection

• The connection will be closed automatically when
  the script ends.
• To close the connection earlier, use the
  mysql_close() function
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• // some code
• mysql_close(con) ?gt

26
Create a Database

• Use CREATE DATABASE statement to create a
  database
• Syntax
• CREATE DATABASE database_name
• Example
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• if (mysql_query("CREATE DATABASE my_db",con))
  
• echo "Database created"
• else
• echo "Error creating database " .
  mysql_error()
• mysql_close(con) ?gt

27
Create a Table

• Use CREATE TABLE statement to create a table.
• Syntax
• CREATE TABLE table_name ( column_name1
  data_type, column_name2 data_type, column_name3
  data_type, .... )

28
Example

• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• // Create database
• if (mysql_query("CREATE DATABASE my_db",con))
  
• echo "Database created"
• else
• echo "Error creating database " .
  mysql_error()
• mysql_select_db("my_db", con) // select
  database before create table
• sql "CREATE TABLE Persons ( FirstName
  varchar(15), LastName varchar(15), Age int )"
• mysql_query(sql,con) // Execute query
• mysql_close(con) ?gt // Close connection

29
Primary Keys and Auto Increment Fields

• A primary key is used to uniquely identify the
  rows in a table. Each table should have a primary
  key field. Each primary key value must be unique
  within the table.
• The primary key field is often an ID number, and
  is often used with the AUTO_INCREMENT setting.
  AUTO_INCREMENT automatically increases the value
  of the field by 1 each time a new record is
  added.
• sql "CREATE TABLE Persons  (
• personID int NOT NULL AUTO_INCREMENT,
• PRIMARY KEY(personID),
• FirstName varchar(15),
• LastName varchar(15),
• Age int )"
• mysql_query(sql,con)

30
Insert Data Record Into a Database Table

• Use INSERT INTO statement to add new records
• Syntax
• The first form doesn't specify the column names,
  only their values
• INSERT INTO table_name VALUES (value1, value2,
  value3,...)
• The second form specifies both the column names
  and the values to be inserted
• INSERT INTO table_name (column1, column2,
  column3,...) VALUES (value1, value2, value3,...)
• Example
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• mysql_select_db("my_db", con)
• mysql_query("INSERT INTO Persons (FirstName,
  LastName, Age) VALUES ('Peter', 'Griffin',
  '35')")
• mysql_query("INSERT INTO Persons (FirstName,
  LastName, Age) VALUES ('Glenn', 'Quagmire',
  '33')")
• mysql_close(con) ?gt

31
Select Data From a Database Table

• Use SELECT statement to select data from a
  database.
• Syntax
• SELECT column_name(s) FROM table_name
• Example
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• mysql_select_db("my_db", con)
• result mysql_query("SELECT FROM Persons")
• while(row mysql_fetch_array(result))
• echo row'FirstName' . " " .
  row'LastName' echo "ltbr /gt"
• mysql_close(con) ?gt

Peter Griffin Glenn Quagmire
32
Display the Result in an HTML Table

• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• mysql_select_db("my_db", con)
• result mysql_query("SELECT FROM Persons")
• echo "lttable border'1'gt lttrgt ltthgtFirstnamelt/thgt
  ltthgtLastnamelt/thgt lt/trgt" while(row
  mysql_fetch_array(result))
• echo "lttrgt"
• echo "lttdgt" . row'FirstName' . "lt/tdgt"
• echo "lttdgt" . row'LastName' . "lt/tdgt"
• echo "lt/trgt"
• echo "lt/tablegt"
• mysql_close(con) ?gt

33
The WHERE clause

• The WHERE clause is used to filter records -- to
  extract only those records that satisfy a
  specified criterion.
• Syntax
• SELECT column_name(s)
• FROM table_name
• WHERE column_name operator value
• Example
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con) die('Could not connect ' .
  mysql_error())
• mysql_select_db("my_db", con)
• result mysql_query("SELECT FROM Persons
  WHERE FirstName'Peter'")
• while(row mysql_fetch_array(result))
• echo row'FirstName' . " " .
  row'LastName' echo "ltbr /gt"
• ?gt

34
The ORDER BY Keyword

• The ORDER BY keyword is used to sort the data in
  a recordset ASC (default) or DESC
• Syntax
• SELECT column_name(s)
• FROM table_name
• ORDER BY column_name(s) ASCDESC
• Example
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• mysql_select_db("my_db", con)
• result mysql_query("SELECT FROM Persons
  ORDER BY age")
• while(row mysql_fetch_array(result))
• echo row'FirstName' echo " " .
  row'LastName' echo " " . row'Age' echo
  "ltbr /gt"
• mysql_close(con) ?gt

35
Update Data In a Database

• The UPDATE statement is used to modify existing
  records in a table.
• Syntax
• UPDATE table_name
• SET column1value, column2value2,...
• WHERE some_columnsome_value Note The WHERE
  clause specifies which record or records should
  be updated. If you omit the WHERE clause, all
  records will be updated!

36
Update Example

• Suppose table "Persons is like this
• FirstName LastName Age
• Peter Griffin 35
• Glenn Quagmire 33
• The following example updates some data in the
  "Persons" table
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• mysql_select_db("my_db", con)
• mysql_query("UPDATE Persons SET Age '36' WHERE
  FirstName 'Peter' AND LastName 'Griffin'")
• mysql_close(con) ?gt
• After the update, the "Persons" table will look
  like this
• FirstName LastName Age
• Peter Griffin 36
• Glenn Quagmire 33

37
Delete Data In a Database

• The DELETE FROM statement is used to delete
  records from a database table.
• Syntax
• DELETE FROM table_name
• WHERE some_column some_value

38
Example

• Look at the following "Persons" table
• FirstName LastName Age
• Peter Griffin 35
• Glenn Quagmire 33
• The following example deletes all the records in
  the "Persons" table where LastName'Griffin'
• lt?php
• con mysql_connect("localhost","peter","abc123"
  )
• if (!con)
• die('Could not connect ' . mysql_error())
• mysql_select_db("my_db", con)
• mysql_query("DELETE FROM Persons WHERE
  LastName'Griffin'") mysql_close(con) ?gt
• After the deletion, the table will look like
  this
• FirstName LastName Age
• Glenn Quagmire 33

39
All you need to know about SQL in 30 minutes (or
less)
40
Basic SQL Commands

• Creating tables with CREATE
• Adding data with INSERT
• Viewing data with SELECT
• Removing data with DELETE
• Modifying data with UPDATE
• Destroying tables with DROP

41
Creating tables with CREATE

• Generic form
• CREATE TABLE tablename (
• column_name data_type attributes,
• column_name data_type attributes,
• )
• Table and column names cant have spaces or be
  reserved words like TABLE, CREATE, etc.

42
Phone Book/Contact Record

• Name Character
• Address Character
• Company Character
• Phone Number Character
• URL/Web Page Character
• Age Integer
• Height Real (float)
• Birthday Date
• When we added the entry Timestamp

43
Phone Book/Contact Table

• CREATE TABLE contacts (
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )
• Plan your tables very carefully!
• Once created, they are difficult to change!

44
Phone Book/Contact Table

• CREATE TABLE contacts (
• ContactID INT PRIMARY KEY,
• Name VARCHAR(40),
• Address VARCHAR(60),
• Company VARCHAR(60),
• Phone VARCHAR(11),
• URL VARCHAR(80),
• Age INT,
• Height FLOAT,
• Birthday DATE,
• WhenEntered TIMESTAMP
• )
• If you are going to use the relational nature of
  a database,dont forget you need to have a
  unique way to access records!
• There is a way to make the key automatically
  increment,so you dont have to worry about which
  one is next.

45
Data Types

• Binary
• Database specific binary objects (BLOB)
• Boolean
• True/False values (BOOLEAN)
• Character
• Fixed width (CHAR) or variable size (VARCHAR)
• Numeric
• Integer (INT), Real (FLOAT), Money (MONEY)
• Temporal
• Time (TIME), Date (DATE), Timestamp (TIMESTAMP)

46
Adding data with INSERT

• Generic Form
• INSERT INTO tablename (column_name,)
• VALUES (value,)

47
Inserting a record into contacts

• INSERT INTO contacts (contactid,name,address,compa
  ny,phone,url,age,height,birthday,whenentered)
• VALUES
• (1,Joe,123 Any St.,ABC,
• 800-555-1212,http//abc.com,30,1.9,6/14/197
  2,now())

48
Inserting a partial record

• INSERT INTO contacts (contactid,name,phone)
• VALUES (2,Jane,212-555-1212)

49
Automatic key generation

• CREATE SEQUENCE contactidseq
• Change the ContactID line in the CREATE TABLE
  to
• ContactID INT DEFAULT nextval(contactidseq)
  PRIMARY KEY
• Or when inserting into a table
• INSERT contacts (contactid,name,phone)
• VALUES (nextval(contactidseq),Jack, 716-5
  55-1212)

50
Viewing data with SELECT

• Generic Form
• SELECT column, FROM table, WHERE condition
  GROUP BY group_by_expression HAVING condition
  ORDER BY order_expression
• The most used command
• Probably the most complicated also
• If used improperly, can cause very long waits
  because complex computations

51
A few simple SELECTs

• SELECT FROM contacts
• Display all records in the contacts table
• SELECT contactid,name FROM contacts
• Display only the record number and names
• SELECT DISTINCT url FROM contacts
• Display only one entry for every value of URL.

52
Refining selections with WHERE

• The WHERE subclause allows you to select
  records based on a condition.
• SELECT FROM contacts WHERE agelt10
• Display records from contacts where agelt10
• SELECT FROM contacts WHERE age BETWEEN 18 AND
  35
• Display records where age is 18-35

53
Additional selections

• The LIKE condition
• Allows you to look at strings that are alike
• SELECT FROM contacts WHERE name LIKE J
• Display records where the name starts with J
• SELECT FROM contacts WHERE url LIKE .com
• Display records where url ends in .com

54
Removing data with DELETE

• Generic Form
• DELETE FROM table WHERE condition
• DELETE FROM contacts WHERE agelt13

55
Modifying data with UPDATE

• Generic Form
• UPDATE table SET columnexpression
• WHERE condition
• UPDATE contacts SET companyAOL
• WHERE companyTime Warner

56
Destroying tables with DROP

• Generic Form
• DROP TABLE tablename
• DROP TABLE contacts

57
More about SELECT
58
Normal Forms and SELECT

• Good database design using the normal forms
  requires data to be separated into different
  tables
• SELECT allows us to join the data back together
• We can use views to create virtual tables

59
Joining together tables

• SELECT name,phone,zip FROM people, phonenumbers,
  address WHERE people.addressidaddress.addressid
  AND people.idphonenumbers.id

60
Different types of JOINs

• Inner Join
• Unmatched rows in either table arent printed
• Left Outer Join
• All records from the left side are printed
• Right Outer Join
• All records from the right side are printed
• Full Outer Join
• All records are printed
• Multiple Table Join
• Join records from multiple tables

61
General form of SELECT/JOIN

• SELECT columns,
• FROM left_table
• join_type JOIN right_table ON condition
• SELECT name,phone FROM people
• JOIN phonenumbers ON people.idphonenumbers.id

62
Other versions

• SELECT name,phone FROM people
• LEFT JOIN phonenumbers ON people.idphonenumbers
  .id
• SELECT name,phone FROM people
• RIGHT JOIN phonenumbers ON people.idphonenumber
  s.id
• SELECT name,phone FROM people
• FULL JOIN phonenumbers ON people.idphonenumbers
  .id

63
Theta style vs. ANSI

• Theta Style (used in most SQL books)
• SELECT name,phone,zip FROM people, phonenumbers,
  address
• WHERE people.addressidaddress.addressid AND
  people.idphonenumbers.id
• ANSI Style uses JOIN
• SELECT name,phone,zip FROM people
• JOIN phonenumbers ON people.idphonenumbers.id
• JOIN address ON people.addressidaddress.addressi
  d

64
Other SELECT examples

• SELECT FROM contacts WHERE name is NULL
• SELECT FROM contacts WHERE zip IN
  (14454,12345)
• SELECT FROM contacts WHERE zip IN ( SELECT
  zip FROM address WHERE stateNY )

65
GROUP BY/HAVING

• The GROUP BY clause allows you to group results
  together with aggregate functions
• AVG(), COUNT(), MAX(), MIN(), SUM()
• COUNT DISTINCT
• HAVING allows you to search the GROUP BY results

66
GROUP BY Examples

• SELECT company,count(company)FROM contactsGROUP
  BY company
• SELECT company,count(company)FROM contactsGROUP
  BY companyHAVING count(company) gt 5

67
ORDER BY

• The ORDER BY clause allows you to sort the
  results returned by SELECT.
• SELECT FROM contacts
• ORDER BY company
• SELECT FROM contacts
• ORDER BY company, name

68
Views

• You can use CREATE VIEW to create a virtual
  table from a SELECT statement.
• CREATE VIEW contactview AS
• (SELECT name,phone,zip FROM
• people,phonenumbers,address
• WHERE people.idphonenumbers.id AND
  people.addressidaddress.addressid)

69

• Summary
• When considering a developing a database
• Consider physical topology/architecture
• Consider logical topology/architecture
• Design a model (E-R)
• Design data structure
• Normalise
• Implement
• Develop applications to query