PHP —

SQL components

SQL has four major parts, and we discuss two of them-the Data Definition Language (DDL) and the Data Manipulation Language (DML)-in detail in Chapter 3. The four major components of SQL are:

Data Definition Language

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.

Data Manipulation Language

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.

Transaction management

SQL includes commands for treating a set of commands as a unit, or transaction. Using these tools, transactions can be undone, or rolled back.

Advanced features

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 maintained correctly.

Transaction management and advanced features of SQL are discussed briefly in Chapter 3 and Chapter 6, and in Appendix C. Pointers to references on SQL can be found in Appendix E.

Our Case Study

The principles of web database applications are illustrated in practice throughout this tutorial with the running example of Alexa and Dave's Online Wines. We refer to it as the winestore throughout the tutorial.

The winestore application has many components of a typical web database application, including:

  • Web pages populated with data from a database

  • User-driven querying and browsing, in which the user provides the parameters that limit the searching or browsing of the database

  • Data entry and validation. HTML <form> widgets collect data, and JavaScript client-side scripts and PHP server-side scripts perform validation.

  • User tracking; that is, session management techniques that add state to HTTP

  • User authentication and management

  • Reporting

Let's take a look at the scope of the winestore and the system functional requirements. (The process of modeling these requirements with relational database entity-relationship (ER) modeling and converting this model to SQL statements is the subject of Appendix C. The completed winestore ER model and the SQL statements to create the database can be found in Chapter 3. We use the winestore components as examples beginning in Chapter 4. Completed components of the winestore application are discussed in Chapter 10 to Chapter 13.)