|only for RuBoard - do not distribute or recompile|
The database tier is the base of a web database application. Understanding system requirements, choosing database-tier software, designing databases, and building the tier are the first steps in successful web database application development. We discuss techniques for modeling system requirements, converting a model into a database, and the principles of database technology in Appendix C. In this section, we focus on the
In a three-tier architecture application, the database tier
Managing relational data in the third tier requires complex RDBMS software. Fortunately, most DBMSs are designed so that the software complexities are hidden. To effectively use a DBMS, skills are required to design a database and
In this book, we use the
RDBMS to manage data. Much like choosing a middle-tier scripting language, there are often arguments about which DBMS is most suited to an application. MySQL has a well-deserved reputation for speed, and it is particularly well designed for applications where retrieval of data is more common than updates and where small, simple updates are the general class of modifications. These are characteristics typical of most web database applications. Also, like PHP and Apache, MySQL is
There are other, nonrelational DBMS software choices for storing data in the database tier. These include search engines, document management systems, and simple gateway services such as email software. Our discussions in this book focus on relational database technology in the database tier.
A database management system stores, searches, and manages data.
is a collection of
A DBMS is a set of components for defining, constructing, and manipulating a database. When we refer to a database management system, we
Figure 1-4 shows the simplified architecture of a typical DBMS.
A DBMS consists of several components:
Libraries for communicating with the DBMS. Most DBMSs have a simple command-line interpreter that often uses these libraries to relay
A parser that checks the syntax of incoming query statements and
Generates different plans for evaluating a query by considering database statistics and properties, selects one of these plans, and translates the plan into low-level actions that are executed.
The modules that manage access to the data stored on disk, including a transaction manager, a recovery manager, the main-memory buffer manager, data security manager, and the file and access method manager.
The physical data itself stored in data files. The data also contains index files for fast access to data, and database and system summary statistics primarily used for query plan generation and optimization.
The important components for web database application developers are the database and applications interface. For all but large-scale applications, understanding and configuring the other components of a DBMS is usually unnecessary.
A question that is often asked is: why use a complex DBMS to manage data? There are several reasons that can be explained by contrasting a database with a spreadsheet, a simple text file, or a custom-built method of storing data. A few example situations where a DBMS should and should not be used are discussed later in this section.
Take spreadsheets as an example. Spreadsheet worksheets are typically designed for a specific application. If two users store names and addresses, they are likely to organize data in a different way—depending on their needs—and develop custom
Managing complex relationships is difficult in a spreadsheet or text file. For example, consider our online winestore: if we want to store information about customers, we might allocate a few spreadsheet columns to store each customer's residential address. If we were to add business addresses and postal addresses, we'd need more columns and complex processing to, for example, process a mail-out to customers. If we want to store information about the purchases by our customers, the spreadsheet becomes wider still, and problems start to emerge. For example, it is difficult to determine the maximum number of
Spreadsheets or text files don't work well when there are associations or relationships between stored data items. In contrast, DBMSs are designed to manage complex
data. DBMSs are also a complete solution: if you use a DBMS, you don't need to design a custom spreadsheet or file solution. The methods that access the data—most often the query language SQL—are independent of how the data is physically stored and actually
A DBMS usually permits multiuser transactions. Medium- and large-scale DBMSs include features that control the writing of data by multiple users in a methodical way. In contrast, a spreadsheet should be opened and written only by one user; if another user opens the spreadsheet, she won't see any updates being made at the same time by the first
An additional benefit of a DBMS is its speed. It isn't totally true to say that a database provides faster searching of data than a spreadsheet or a custom filesystem. In many cases, searching a spreadsheet or a special-purpose file might be
There are also other advantages of DBMSs, including
In any of these situations, a DBMS should probably be used to manage data:
There is more than one user who needs to access the data at the same time.
There is at least a moderate amount of data. For example, we may need to maintain information about a few hundred customers.
There are relationships between the stored data items. For example, customers may have any number of related purchase orders.
There is more than one kind of data record. For example, there might be information about customers, orders, inventory, and other data in an online store.
There are constraints that must be rigidly enforced on the data, such as field lengths, field types, uniqueness of customer numbers, and so on.
New or consolidated information must be produced from basic, related information; that is, the data must be queried to produce
There is a large amount of data that must be searched quickly.
Security is important. There is a need to enforce rules as to who can access the data.
Adding, deleting, or modifying data is a complex process.
There are some situations where a relational DBMS is probably unnecessary or unsuitable. Here are some examples:
There is one type of data item, and the data isn't searched. For example, if a log entry is written when a user logs in and logs out, appending the entry to the end of a simple text file may be sufficient.
The data-management task is trivial. In this case, the data might be coded into a web script in the middle tier, rather than adding the overhead of a database access each time the data is needed.
The data requires complex analysis. For analysis, a spreadsheet package or statistical software may be more appropriate.
MySQL is a medium-scale DBMS, with most of the features of a large-scale system and the ability to manage very large
The difference between MySQL and some other systems is that MySQL is missing some querying support and has limited concurrency-handling
The limitations of MySQL usually have a very minor impact on web database application development. However, for high-throughput systems, large numbers of concurrent users, or applications that modify the database frequently, other DBMSs may be
At the time of writing, the current version of MySQL is 3.23, and the current release is 3.23.38. MySQL resources are listed in Appendix E.
SQL is the standard relational database interaction language. Almost all relational database systems, including MySQL, support SQL as the tool to create, manage, secure, and query databases. Indeed, this is an important point about SQL: it is much more than just a query language; it is a fully fledged tool for all aspects of database management.
SQL has had a complicated life. It
Since the mid-1980s, three
We focus on features found in the MySQL DBMS. MySQL supports the entry-level SQL-92 standard.
SQL has four major
DDL is the set of SQL commands that create and delete a database, add and remove tables, create indexes, and modify each of these. DDL commands are generally used only during the construction of the database. Indexes are structures for fast access and updates of data.
DML is the set of commands that work with a DBMS and a database. DML commands include those to search, insert, and delete data. These commands are the tools that interact with a database during its normal use.
SQL includes commands for
DML and DDL include advanced features for embedding SQL into general-purpose programming languages (in much the same way you can see SQL commands embedded in PHP in Chapter 4) and defining special-purpose views of the underlying data, and granting and removing access rights to the DBMS and databases. They also include commands for ensuring the integrity of the system; that is, ensuring the data is correct and that relational constraints are
Transaction management and advanced features of SQL are discussed
|only for RuBoard - do not distribute or recompile|