The field of databases has its own terminology. Terms such as database, table, attribute, row, primary key, and relational model have specific meanings and are used throughout this chapter. In this section, we present an example of a simple database to introduce the basic components of relational databases, and we list and define selected terms used in the chapter. We then show you our winestore database that we use throughout our examples in this chapter, and as the basis of our sample application in Chapter 16 through Chapter 20. More detail on the database can be found in Appendix E. 5.1.1 Introducing Relational DatabasesA simple example relational database is shown in Figure 5-1. This database stores data about wineries and the wine regions they are located in. A relational database is organized into tables, and there are two tables in this example: a winery table that stores information about wineries, and a region table that has information about wine regions. Tables collect together information that is about one object. Figure 5-1. An example relational database containing two related tablesDatabases are managed by a database management system (DBMS) or database server . A database server supports a database language to create and delete databases and to manage and search data. The database language used by almost all database servers is SQL , a set of statements that define and manipulate data. After creating a database, the most common SQL statements used are INSERT, UPDATE, DELETE, and SELECT, which add, change, remove, and search data in a database, respectively. In this book, we use the MySQL database server to manage databases. MySQL runs as a server (daemon) process or service, like Apache or IIS, and supports several different clients including a command-line interpreter (that we use in this chapter) and a PHP function library (that we use throughout later chapters). One MySQL server can manage multiple databases for you for multiple applications, and each can store different data organized in different ways. A database table may have multiple attributes , each of which has a name. For example, the winery table in Figure 5-1 has four attributes, winery ID, winery name, address, and region ID. A table contains the data as rows , and a row contains values for each attribute that together represent one related object. (Attributes are also known as fields or columns , while rows are also known as records . We use attribute and row throughout this book.) Consider an example. The winery table has five rows, one for each winery, and each row has a value for each attribute. For example, in the first winery row, the attribute winery ID has a value of 1, the winery name attribute has a value of Moss Brothers, the attribute address has a value of Smith Rd., and the region ID attribute has a value of 3. There is a row for region 3 in the region table and it corresponds to Margaret River in Western Australia. Together this data forms the information about an object, the Moss Brothers Winery in Western Australia. In our example, the relationship between wineries and regions is maintained by assigning a region ID to each winery row. The region ID value for each region is unique, and this allows you to unambiguously discover which region each winery is located in. Managing relationships using unique values is fundamental to relational databases. Indeed, good database design requires that you can make the right choice of which objects are represented as tables and which relationships exist between the tables. We discuss good database design in Appendix E. In our example of the relationship between wineries and regions, there's a one-to-many mapping between regions and wineries: more than one winery can be situated in a region (three wineries in the example are situated in the Barossa Valley) but a winery can be situated in only one region. It's also possible to have two other types of relationship between tables: a one-to-one relationship where, for example, each bottle of wine has one label design, and a many-to-many relationship where, for example, many wines are delivered by many couriers. As we show you later, unique values or primary keys allow these relationships to be managed and they're essential to relational databases. Attributes have data types . For example, in the winery table, the winery ID is an integer, the winery name and address are strings, and the region ID is an integer. Data types are assigned when a database is designed. Tables usually have a primary key, which is formed by one or more values that uniquely identify each row in a table. The primary key of the winery table is the winery ID, and the primary key of the region table is the region ID. The values of these attributes aren't usually meaningful to the user, they're just unique ordinal numbers that are used to uniquely identify a row of data and to maintain relationships. Figure 5-2 shows our example database modeled using entity-relationship (ER) modeling . An ER model is a standard method for visualizing a database and for understanding the relationships between the tables. It's particularly useful for more complex databases where relationships of different types exist and you need to understand how to keep these up-to-date and use them in querying. As we show you later, our winestore database needs a moderately complex ER model. In the ER model in Figure 5-2, the winery and region tables or entities are shown as rectangles. An entity is often a real-world object and each one has attributes , where those that are part of the primary key are shown underlined. The relationship between the tables is shown as a diamond that connects the two tables, and in this example the relationship is annotated with an M at the winery-end of the relationship. The M indicates that there are potentially many winery rows associated with each region. Because the relationship isn't annotated at the other end, this means that there is only one region associated with each winery. We discuss ER modeling in more detail in Appendix E. Figure 5-2. An example relational model of the winery database5.1.2 Database Terminology
5.1.3 The Winestore DatabaseThis section is a summary of the entity-relationship model of the winestore database. It's included for easy reference, and you'll find it useful to have at hand as you work through this chapter. 5.1.3.1 The winestore entity-relationship modelFigure 5-3 shows the complete entity-relationship model for our example winestore database; this model is derived from the system requirements listed in Chapter 16, and is derived following the process described in Appendix E. Appendix E also includes a description of the meaning of each shape and line type used in the figure. Figure 5-3. The winestore ER modelThe winestore model can be summarized as follows:
|