Managing data

Database

• Structured collection of data.

• Tables
• Fields
• Query
• Reports
Relational Database

• Stores data in separate tables instead of

a single store.
• Relationships between tables are set
• In theory, this provides a faster, more
flexible database system.
Example
• We wish to maintain a database of student names, IDs, addresses,
and any other information.

• Will be updated frequently with new names and information.

• Will want to retrieve data based on some predicate.

• e.g, ‘give me the names of all Ali students who live in
Egypt’.

• Will want to update database with new information about students,

not previously recorded.
• e.g., may decide we want to include IRD nos.
Databases


Relational Database structure
 Well developed theory and practise


Multi-user
 Multiprocessor, sometimes cluster based systems


Standards based queries
 Structured Query Language (SQL)
(SQL
Basic Database Server Concepts

Database runs as a server
 Attaches to either a default port or an administrator
specified port

Clients connect to database
 For secure systems

authenticated connections

usernames and passwords

Clients make queries on the database
 Retrieve content
 Insert content

SQL (Structured Query Language) is the language used
to insert and retrieve content
Database Management System
• Manages the storage and retrieval of
data to and from the database and hides
the complexity of what is actually going
on from the user.

Database
Database Management User
Ssytem

• MySQL is a relational database

management system
Client: makes a request
requests an Internet
Client resource by
(browser) specifying a URL and
providing input via HTTP
Web encoded strings
browser

GET hello.php HTTP/1.1 Server

Host: www.massey.ac.nz:80
os
Web
server

os

Internet

Network Core
Server: responds
• Webserver supports HTTP.

Server

Web
server
My codes
MySQL PHP
HTTP HTML interpreter
Client
Operating System

Web
TCP/IP
browser

Internet
Server: responds
Internet
MySQL
Operating System Server

MySQL server could be

anywhere in the world Web
server
My codes
PHP
HTTP HTML interpreter
Client
Operating System

Web
TCP/IP
browser

Internet
Table: Customers (data)
Table: Products (data)
Table: Purchases (data)
Table: PurchaseProducts (data)
Database Design
In MySQL there are three main types :
• text
• number
• Date/Time.
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special
characters). The fixed size is specified in parenthesis. Can store up to
255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special
characters). The maximum size is specified in parenthesis. Can store up
to 255 characters. Note: If you put a greater value than 255 it will be
converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in
an ENUM list. If a value is inserted that is not in the list, a blank value will
be inserted.Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM('X','Y','Z')

http://www.w3schools.com/sql/sql_datatypes.asp
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits
may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of
digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum
number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The
maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to
18446744073709551615 UNSIGNED*. The maximum number of digits may
be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of
digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of
digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The
maximum number of digits may be specified in the size parameter. The
maximum number of digits to the right of the decimal point is specified in the
d parameter
http://www.w3schools.com/sql/sql_datatypes.asp
DATE() A date. Format: YYYY-MM-DDNote: The supported range is from '1000-01-
01' to '9999-12-31'
DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MM:SSNote: The
supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds

since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD
HH:MM:SSNote: The supported range is from '1970-01-01 00:00:01' UTC to
'2038-01-09 03:14:07' UTC

TIME() A time. Format: HH:MM:SSNote: The supported range is from '-838:59:59' to

'838:59:59'
YEAR() A year in two-digit or four-digit format.Note: Values allowed in four-digit
format: 1901 to 2155. Values allowed in two-digit format: 70 to 69,
representing years from 1970 to 2069

http://www.w3schools.com/sql/sql_datatypes.asp
Create Database
Create Table: Customers
Specify the Table’s Fields &
Attributes: Customers
Table Edit Screen: Customers
Table: Products
Table: Products
Insert Record: Customers
Table: Customers (data)
Insert Record: Products
Table: Products (data)
Edit Record
Export
Deleting a Table
Restoring a database from an SQL
file
Database Design
Summary

• Concept of databases
• Tables and Fields
• Field Types
• phpMyAdmin Tool for manipulating databases
• Creation of a database
• How to add and edit records
• How to back-up a database
• Database Design