5.1 Database Basics

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 Databases

A 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 tables

Databases 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 database

5.1.2 Database Terminology

Database

A repository to store data. For example, a database might store all of the data associated with finance in a large company, information about your CD and DVD collection, or the records of an online store.

Table

A part of a database that stores data related to an object, thing, or activity. For example, a table might store data about customers. A table has columns, fields, or attributes. The data is stored as rows or records.

Attributes

The columns in a table. All rows in a table have the same attributes. For example, a customer table might have the attributes name, address, and city. Each attribute has a data type such as string, integer, or date.

Rows

The data entries stored in a table. Rows contain values for each attribute. For example, a row in a customer table might contain the values "Matthew Richardson," "Punt Road," and "Richmond." Rows are also known as records.

Relational model

A formal model that uses database, tables, and attributes to store data and manages the relationship between tables.

(Relational) database management system (DBMS)

A software application that manages data in a database and is based on the relational model. Also known as a database server.

SQL

A standard query language that interacts with a database server. SQL is a set of statements to manage databases, tables, and data. Despite popular belief, SQL does not stand for Structured Query Language and isn't pronounced Sequel: it's pronounced as the three-letter acronym S-Q-L and it doesn't stand for anything.

Constraints

Restrictions or limitations on tables and attributes. A database typically has many constraints: for example, a wine can be produced only by one winery, an order can't exist if it isn't associated with a customer, and having a name attribute is mandatory for a customer.

Primary key

One or more attributes that contain values that uniquely identify each row. For example, a customer table might have the primary key named cust ID. The cust ID attribute is then assigned a unique value for each customer. A primary key is a constraint of most tables.

Index

A data structure used for fast access to rows in a table. An index is usually built for the primary key of each table and can then be used to quickly find a particular row. Indexes are also defined and built for other attributes when those attributes are frequently used in queries.

Entity-relationship (ER) modeling

A technique used to describe the real-world data in terms of entities, attributes, and relationships. This is discussed in Appendix E.

Normalized database

A correctly designed database that is created from an ER model. There are different types or levels of normalization, and a third-normal form database is generally regarded as being an acceptably designed relational database. We discuss normalization in Appendix E.

5.1.3 The Winestore Database

This 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 model

Figure 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 model

The winestore model can be summarized as follows:

• A customer at the online winestore purchases wines by placing one or more orders.

• Each customer has exactly one set of user details.

• Each customer has a title (such as "Mr" or "Dr") and lives in a country.

• Each order contains one or more items.

• Each item is a specific quantity of wine at a specific price.

• A wine is of a type such as "Red," "White," or "Sparkling."

• A wine has a vintage year; if the same wine has two or more vintages from different years, these are treated as two or more distinct wines.

• Each wine is made by one winery.

• Each winery is located in one region.

• Each wine has one or more grape_variety entries. For example, a wine of wine_name "Archibald" might be made of the grape_variety entries "Sauvignon" and "Cabernet." The order of the entries is important. For example, a "Cabernet Sauvignon" is different from a "Sauvignon Cabernet."

• Each inventory for a wine represents the on-hand stock of a wine. If a wine is available at two prices, there are two inventories. Similarly, if the stock arrived at the warehouse at two different times, there are two inventories.

• Each wine may have one or more inventories.