Websites and Databases
Chapter 3 – Developing Websites
What is a Database?
A database (DB) is a computerised collection of
… typically organized to make common
retrievals of information easy and efficient.
A database management system (DBMS) is
the software mechanism for managing that data.
Databases were created (originally in the
1960s) to solve problems with file-oriented
systems by being:
Fast and easy to use
Allowing the easy sharing of data between
Databases for Websites
Databases have quickly become integral
to the design, development, and services
offered by web sites.
Consider sites like Amazon.com that must
be able to allow users to quickly jump
through a vast virtual warehouse of books
and compact disks.
Databases for Websites
Databases for Websites
How could Amazon.com create web pages
for every single item in their inventory and
how could they keep all those pages up-
Their web pages are created on-the-fly by
a program that "queries" a database of
inventory items and produces an HTML
page based on the results of that query.
Database complexity varies according to the
system it is being used for.
A database can be as complex and demanding
as an account tracking system used by a bank
for managing changing accounts of thousands of
… or as simple as a collection of electronic
business cards on your laptop.
Yet, the most important thing is that its
DBMS allows you to store data and get it
or modify it when you need to easily and
efficiently, regardless of the amount of
data being manipulated.
What the data is and how demanding you
will be when retrieving and modifying that
data is simply a matter of scale (or size).
There are two DBMS types:
Microsoft SQL Server
Industrial DBMSs are used to store huge
amounts of data…
… and process thousands of transactions and
Did you know?
MS SQL Server was Microsoft’s response to Oracle…
… while MySQL was Linux’s marketing response to
MS SQL Server!
Although MS Access is just a Home DB, it is
also used for small enterprises…
… however one must keep in mind that it has
huge limitations for industrial use.
On the other hand, given that normal users are
so familiar with it, Access is preferred as a front
end to the bigger DBMS in offices.
This is usually because users do not need initial
training on it and is simple to use.
There are two main DB types:
Also known as OLAP
(On-Line Analytical Processing)
Primarily static, read-only databases which store
archived, historical data used for analysis.
Also known as OLTP
(On-Line Transactional Processing)
Used to manage more dynamic bits of data. They allow
you to modify that data (add, change or delete data).
What is SQL?
SQL is a standard computer language for
accessing and manipulating databases.
Stands for Structured Query Language.
Allows you to access a database.
Is an ANSI standard computer language.
Is easy to learn.
SQL is a syntax for executing queries. But the
SQL language also includes a syntax to update,
insert, and delete records.
These query and update commands together
form the Data Manipulation Language (DML)
part of SQL:
SELECT – extracts data from a DB table
UPDATE – updates data in a DB table
DELETE – deletes data from a DB table
INSERT INTO – inserts new data into a DB table
A database most often contains one or more
Each table is identified by a name.
e.g. "Customers" or "Orders"
Tables contain records (rows) with data.
Data can also be identified by the field (column)
that it falls under.
Database Tables (cont.)
It is important to note that how or where the
tables of data are stored makes no difference.
Each table can be identified by a unique name
and that name can be used by the database to
find the table behind the scenes.
As a user, all you need to know is the table
name in order to use it. You do not need to
worry about the complexities of how the data is
stored on the hard drive.
Tables and SQL
Below is an example of a table called "Persons":
How many records and fields does the table
What does the following SQL statement do?
A database server is left running 24 hours a
day, and 7 days a week – like this, the server
can handle database requests at any hour.
Database requests come in from "clients" who
access the database through its command line
interface or by connecting to a database
Requests are handled as they come in and
multiple requests can be handled at one time.
For web applications which must be available for
world wide time zone usage, it is essential to
build upon a client/server database which can
run all the time.
Hopefully, you will also choose a database with
full functionality such as security and
concurrency control (making sure that two users
cannot simultaneously change a record).
Dynamic websites typically generate web pages
on-the-fly by making use of a back-end
A database is a store of structured data.
A database management system is a software
application used to manage data stored in some
DBMS types include MS SQL Server, MySQL
and Oracle for industrial purposes…
… while MS Access is usually used for home
use or as a front-end for small enterprises.
DBMSs use SQL statements to be able to
query a database for information.
DB types include OLAP and OLTP.
On-Line Analytical Processing: one can only
read/view archived data in a DB
SELECT statements only are used.
On-Line Transactional Processing: one can
read/view, add, modify and delete data in a DB
SELECT, INSERT INTO, UPDATE and DELETE
statements are used.
Sol, S., “Introduction to Databases for the Web: Pt. 1”, 1998,
W3 Schools, “SQL Introduction”, 2005, [Online]