Home arrow MySQL arrow Page 7 - Database Applications and the Web

The Database Tier - MySQL

With most of the services on the web being powered by web database applications, it becomes important for any web developer to know how bring together the web and databases to build applications. This article gets you started. It is excerpted from chapter one of the book Web Database Applications with PHP and MySQL, written by Hugh E. Williams & David Lane (O'Reilly, 2004; ISBN: 0596005431).

TABLE OF CONTENTS:
  1. Database Applications and the Web
  2. The Web
  3. HTTP: the Hypertext Transfer Protocol
  4. Thickening the Client in the Three-Tier Model
  5. Web Scripting with PHP
  6. Introducing PHP5
  7. The Database Tier
  8. Why use a database server?
  9. The MySQL server
By: O'Reilly Media
Rating: starstarstarstarstar / 45
September 15, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The database tier stores and retrieves data. Itís also responsible for managing updates, allowing simultaneous (concurrent) access from web servers, providing security, ensuring the integrity of data, and providing support services such as data backup. Importantly, a good database tier must allow quick and flexible access to millions upon millions of facts.

Managing data in the database tier requires complex software. Fortunately, most database management systems (DBMSs) or servers are designed so that the software complexities are hidden. To effectively use a database server, skills are required to design a database and formulate queries using the SQL language; SQL is discussed in Chapter 5. An understanding of the underlying architecture of the database server is unimportant to most users.

In this book, we use the MySQL server to manage data. It has a well-deserved reputation for speed: it can manage many millions of facts, itís very scalable, and particularly suited to the characteristics of web database applications. Also, like PHP and Apache, MySQL is open source software. However, there are downsides to MySQL that we discuss later in this section.

The first step in successful web database application development is understanding system requirements and designing databases. We discuss techniques for modeling system requirements, converting a model into a database, and the principles of database technology in Appendix E. In this section, we focus on the database tier and introduce database software by contrasting it with other techniques for storing data. Chapters 5 and 15 cover the standards and software we use in more detail.

There are other server choices for storing data in the database tier. These include search engines, document management systems, and gateway services such as email software. Our discussions in this book focus on the MySQL server in the database tier.

Database Management Systems

A database server or DBMS searches and manages data thatís stored in databases. A database is a collection of related data, and an application can have more than one database. A database might contain a few entries that make up a simple address book of names, addresses, and phone numbers. At the other extreme, a database can contain tens or hundreds of millions of records that describe the catalog, purchases, orders, and payroll of a large company. Most web database applications have small-to medium-size databases that store thousands, or tens of thousands, of records.

Database servers are complex software. However, the important component for web database application development is the applications interface thatís used to access the database server. For all but the largest applications, understanding and configuring the internals of a database server is usually unnecessary.

SQL

The database server applications interface is accessed using SQL. Itís a standard query language thatís used to define and manipulate databases and data, and itís supported by all popular database servers.

SQL has had a complicated life. It began at the IBM San Jose Research Laboratory in the early 1970s, where it was known as Sequel; some users still call it Sequel, though itís more correctly referred to by the three-letter acronym, SQL. After almost 16 years of development and differing implementations, the standards organizations ANSI and ISO published an SQL standard in 1986. IBM published a different standard one year later!

Since the mid-1980s, three subsequent standards have been published by ANSI and ISO. The first, SQL-89, is the most widely, completely implemented SQL in popular database servers. Many servers implement only some features of the next release, SQL-2 or SQL-92, and almost no servers have implemented the features of the most recently approved standard, SQL-99 or SQL-3. MySQL supports the entry-level SQL92 standard and has some proprietary extensions.

Consider an SQL example. Suppose you want to store information about books in a library. You can create a tableóan object thatís stored in your databaseóusing the following statement:

CREATE TABLE books (
 
title char(50),
 
author char(50),
 
ISBN char(50) NOT NULL,
 
PRIMARY KEY (ISBN)
);

Then, you can add books to the database using statements such as:

INSERT INTO books ("Web Database Apps", "Hugh and Dave", "123-456-N");

Once youíve added data, you can retrieve facts about the books using queries such as the following that finds the author and title of a book with a specific ISBN:

SELECT author, title FROM books WHERE ISBN = "456-789-Q";

These are only some of the features of SQL, and even these features can be used in complex ways. SQL also allows you to update and delete data and databases, and it includes many other features such as security and access management, multiuser transactions that allow many users to access the same database without corrupting the data, tools to import and export data, and powerful undo and redo features.

SQL is discussed in detail in Chapters 5 and 15.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: