- 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
User tracking; that is, session management techniques that add state to HTTP
User authentication and management
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.)