To deal with this problem, Codd did something more than simply invent the relational database. He also developed the concept of normalization for a relational database, a process that can help us to achieve at least some of the above goals.

A database is said to be in a particular normal form if the relationships between the attributes of the entities represented by the records in the tables of the database are rigorously defined in a certain way. Originally, in 1970, Codd specified three normal forms. These have the somewhat unimaginative, but easy-to-remember, names First Normal Form, Second Normal Form, and Third Normal Form, which are often abbreviated 1NF, 2NF, and 3NF.

Since Codd’s original proposal, several additional normal forms have been described, including the Boyce-Codd Normal Form (BCNF), as well as 4NF and 5NF, and a 6NF for “temporal” databases, for example. Clearly you can take the normalization process some distance, but going beyond the second or third normal form is often unnecessary, and may even, in some cases, be counterproductive. There are even “denormalization” procedures that can be applied if it is discovered that more normalization than is desirable has been performed, and some needs to be “undone”! In any case, we will have no need to discuss any of these “higher-level” normal forms. For further information see the end-of-chapter References.

One important aspect of these normal forms to remember is that they are cumulative in the sense that to be in 2NF, a database must first be in 1NF, and to be in 3NF it must first be in 2NF, and so on. Also, a database is said to be in 1NF if each table in the database is in 1NF, and similarly for the higher normal forms.

The first normal form essentially deals with the “shape” of a record type. For a database to be in 1NF, each row of a table must have the same number of columns of information (i.e., each record must have the same number of attributes) and each attribute value must contain a single piece of information. A short way of describing the latter requirement is to say that each attribute value must be atomic.

You need to keep in mind, however, that just what atomic means in any given database can depend significantly on the nature of the data and the viewpoint of the database designer. For example, in one database the name of a customer might have the customer’s first name, middle initial, and last name all together in one column and be regarded as being a single “atomic” value because there is no perceived need to have a name broken down into its constituent pieces. In another database, each of those three pieces of a name might be placed in its own column because there might well be a need for each part of the name to be accessible at one time or another, and having each name part in its own column makes that access much easier.

As a simple example, assume that we want to keep a list of products bought by a customer by using just two attributes (two fields per table row): Customer and Product. If a customer, say Pawan, buys two products, say Vitamin B and Vitamin C, the corresponding row of our table would look like the row shown in TABLE 9.1. The problem here is that the Product attribute value is not atomic (we cannot have a single record with Customer = Pawan and Product = Vitamin B, Vitamin C). Instead, we need two records with the same Customer value, but different Product values, as shown in TABLE 9.2. In other words, from the point of view of what’s in a table, each column must contain a single value (whatever that means within the given context) in each row.

Before discussing 2NF we need to say something about keys for our tables, and the functional dependency that may or may not exist between key and non-key record attributes in our tables.

Placing appropriate keys in our database tables is an important step in setting up our database. A table column in which the attribute value is used to uniquely identify the record in its row, and serve, in effect, as a “lookup” value for that row, contains what we call the primary key for that table. Such a primary key must satisfy the following properties:

• It must be unique for every record in the table, and here it is important to remember that the uniqueness must apply to every possible entry that might be put into the table, not just the entries in the table at some particular time.

• It must always have a value and, moreover, a value that will never change.

A primary key column from Table A, say, may also appear in a second table, say Table B, and thus serve to “connect”, or establish a “relationship” between those two tables. In this case, in Table B the primary key from Table A is called a foreign key.

Sometimes we have a “natural” choice for a primary key, such as the social security number, which will be unique for each person in a table, for example. Other times we may simply want to generate an artificial (or surrogate) identifier to use for the primary key. For example, we could use something as simple as a sequence of positive integers. This has the dual advantage of being totally under the control of the database designer, and many database management systems can generate such values automatically as data is entered into the database.

A simple key value that appears in a single column is perhaps the most frequent and convenient kind of primary key, but sometimes the concatenation of the attribute values in two (or more) columns can be also used as the primary key, in which case we refer to it as a compound (or composite) primary key.

The second and third normal forms (2NF and 3NF) deal with the relationship (or the functional dependency) between key and non-key attributes in a table.

In particular, 2NF is violated when a non-key attribute is a fact about a subset of the primary key (i.e., there is a functional dependency from an attribute that is only part of the primary key to the non-key attribute). Thus this is only relevant when the primary key is composite (consisting of more than one attribute). Another way of saying this is that for 2NF we want each non-key attribute in a table to be information “about the primary key, about the whole primary key, and about nothing but the whole primary key”.

Let us illustrate what we mean by taking a simple example of a database that violates 2NF and converting it into one that satisfies 2NF.

Compare what you see in TABLE 9.3 with what you see in TABLE 9.4. Table 9.3 shows a single-table database with four attributes for purchases made in a store: Customer, email, Product, and Price. Suppose we are using the combination of the customer name attribute (the Customer field) and the product name attribute (the Product field) as the primary key. There are four records in the database. Clearly there is a functional relationship between the attribute Customer, which is part of the primary key, and the non-key attribute email, since a customer’s email address provides a fact about that customer. However, the Customer attribute is only part of the primary key and the customer’s email has nothing to do with any product the customer may be buying (the other part of the primary key).

A similar functional dependency also applies from the attribute Product, which is part of the primary key, to the non-key attribute Price.

Now suppose we split the single table of the original database of Table 9.3 into three tables, as shown in Table 9.4. The first thing we achieve by doing this is that we save some storage. This may not be obvious in this example, because we have so few records, but note, for instance, that in the single-table database of Table 9.3 the product Vitamin C and its price appeared twice, but in the new arrangement this pairing occurs only once, so think about the implications of this for a much larger data set.

We are also moved toward our goal of maintaining database integrity. For example, if we had to change the price of Vitamin C to $29.99, then in the configuration of Table 9.4 we would need to make the change for only one record (the one in the table containing the record with the product Vitamin C and its price). That means we could not create an “update anomaly” by making the mistake of changing the price of Vitamin C in one place and not in the other (Vitamin C and its price appeared twice in the original table).

Note that these three tables can be “joined” together to recover the original table, and hence the decomposition shown in Table 9.4 is lossless. You will see how two or more tables can be joined in various ways using MySQL queries later in this chapter.

Though we will not pursue the discussion any further, let us say here simply that 3NF is violated when a non-key attribute provides a fact about another non-key attribute. The creation of further tables from the existing ones may be necessary to achieve 3NF.

Another important aspect of a database design to think about during setup is the nature of the relationships that the tables in the database will have to one another, as the above discussion suggests. There are essentially three possibilities:

• one-to-one: There is a one-to-one relationship between Table A and Table B if each record in Table A corresponds to one and only one record in Table B, and vice versa. For example, in your company there might be a one-to-one relationship between each employee and his or her desktop computer, if in fact each employee has a single desktop computer.

• one-to-many: A one-to-many relationship from Table A to Table B means that each record in Table A will correspond (potentially at least) to several and perhaps many records in Table B, and several records in Table B can correspond to a single record in Table A. For example, a single customer may have placed several orders, so a perfectly “natural” relationship between a Customer table and an Order table might be one-to-many.

• many-to-many: A many-to-many relationship from Table A to Table B means that at least one record in Table A corresponds to several (or many) records in Table B and at least one record in Table B corresponds to several (or many) records in Table A. Without going into detail, we should say that such relationships often create redundant data in your database and lead to problems in other ways, and so are best avoided.

In all cases, the relationships between tables are established with the help of table keys, the primary key of one table becoming a foreign key in a second table, for example. Sometimes it may prove to be convenient to set up a table containing nothing but keys, just to help “connect” other tables, particularly when you are trying to simplify a many-to-many table relationship.

So, the first step in creating a database application for a business, or for any other endeavor, is to create a data model that contains all relevant attributes that need to be stored. The next step is to use the functional dependencies among the attributes to create an appropriate set of tables, and then establish the relationships between these tables using keys. The resulting set of tables should minimize data redundancy and preserve data integrity as various operations are performed on the database.

As a best practice, you should ensure that the resulting database model is at least in 2NF. But, as we have pointed out, 2NF is not a concern unless you are using a composite primary key. A normalized database tends to make general-purpose querying easier and faster, while a non-normalized database can favor some queries over others. However, keep in mind that normal forms represent only guidelines for the design of the records in the tables of your database. Though generally a good thing, they are just that—guidelines and not rules—and sometimes business requirements become the overriding factor in how a database is actually constructed. Nevertheless, some attempt at normalization is always a good starting point.

The CREATE command is most often used to create a table within a database, but it can also be used to create the database itself. For example, the command

CREATE DATABASE webbook;

could be used to create our webbook database, provided we have the necessary MySQL permissions to do so. These permissions are under the control of the MySQL administrator. That administrator will be you if you have installed MySQL on your own machine. However, many users in an academic environment may not have the authority to create their own databases. If this is the case for you, your system administrator would probably have created a database for you, based on information provided by a course instructor.

As for creating a table in an already existing database, the general syntax of the required command looks like this:

CREATE TABLE table_name (col_name col_type col_constraints[,...]);

Figure 9.5 shows the CREATE command used to create our Customers table. This command, as shown, could be typed in directly if we were using the command-line interface to MySQL, a topic for later discussion in this chapter. For the moment, we are happy to have phpMyAdmin “construct” this command for us “behind the scenes”, based on information we enter into the form it displays for us. This particular command illustrates almost all of the useful features of the CREATE command. In the first line, 'webbook'.'Customers' tells that we are creating a table called Customers in the database webbook. The use of single quotes is not always necessary, but phpMyAdmin has put them in for us. Within the parentheses we specify the list of attributes, along with their types, and their lengths and other constraints wherever appropriate (such as NOT NULL, which means that the attribute must have a value).

At the end of the list of attributes, we specify that customer_id is the primary key, that the table should be indexed based on phone_number, and that the attribute values of email_ address and login_name should be unique. Outside the parentheses, the “storage engine” is specified to be MYISAM, a specification that was not really necessary since MYISAM was the default storage engine at the time of this writing, though this may have changed by the time you read this and is likely to be irrelevant for our purposes in any case.

Now that we have created the Customers table using the CREATE command, let us move on to our next data definition command of interest, ALTER, and see how to make changes in the structure of our table.

The ALTER command allows us to modify an existing object in our database. For example, we can use it to add a column to, or delete a column from, an existing table. This is different from, and should not be confused with, the UPDATE command, which is used to change the values of attributes in the rows of our tables, and which we discuss later.

Recall that we had made a mistake in creating the Customers table by omitting the gender attribute. We will now add this attribute. Let us say that we realized our error as soon as we looked at the results returned by the CREATE command in Figure 9.4. Notice at the bottom of the figure that we have the option of adding an attribute. We can indicate that we want to add an attribute after customer_last_name and click on the Go button.

FIGURE 9.6 shows the page that will come up for specifying the properties of the new attribute. We indicate that the name of the attribute is gender, and it will be of type VARCHAR with a length of 1, and then click the Save button.

FIGURE 9.7 shows the structure of the altered Customers table, as well as the SQL ALTER command that was used to add the gender attribute. This command is also shown, more clearly, in FIGURE 9.8. ALTER is a very versatile command, which allows us to add, change, or delete attributes (as opposed to values of attributes, done with UPDATE, as we mentioned above). In our case, we have simply used the ADD “subcommand” to add gender after customer_last_name.

The last SQL data definition command we want to look at is the DROP command. The DROP command can be used to remove an attribute from a table, a table from your database, or even to remove the complete database. Caution should be exercised when using this command, because the deletion is irreversible and the “dropped” data is therefore irretrievable. A GUI interface to MySQL, like phpMyAdmin, will generally warn you and ask you to confirm deletions, but remember later on when you are using the command-line interface to MySQL that you will not get any such warnings, the idea being that if you are at the command-line you know what you are doing, and you are doing it carefully.

We will study this command with the help of a table called temp. We have created such a table in our database with two attributes called dummy1 and dummy2, as shown in FIGURE 9.9. If you click on the red X icon in the row containing the dummy1 attribute, a dialog box will appear. This is also shown in Figure 9.9. The corresponding SQL query uses this ALTER command with a DROP subcommand:

ALTER TABLE 'temp' DROP 'dummy1';

As soon as you click on OK, the attribute dummy1 will disappear from the table. If we want to delete the entire table temp from the database, we can click on the database and then click on the red X icon in the row corresponding to that table, as shown in FIGURE 9.10. The required SQL query is:

DROP TABLE 'temp';

Clicking on the OK button will permanently delete the table temp from our database.

Now that we have dealt with the SQL data definition commands that are of interest to us, we turn our attention to data manipulation commands, beginning with the INSERT command. Suppose we want to add two records to the table Ref_Invoice_Status. Clicking on that table in the database, and then clicking on the Insert link will bring up the web page shown in FIGURE 9.11. We then enter the values for the two records and click the Go button at the bottom right. The resulting web page, seen in FIGURE 9.12, shows the insertion as well as the corresponding SQL command. The SQL INSERT command is also reproduced in FIGURE 9.13.

Here is the general format of the INSERT command:

INSERT INTO table_name
(comma-separated list of column names)
VALUES
(comma-separated list of corresponding values for those columns)

In our command shown in Figure 9.13 we are indicating we want to enter the values of attributes invoice_status_code and invoice_status_description. The first record has the value 'IS' for invoice_status_code and 'Issued' for invoice_status_description. The second record has the value 'PD' for invoice_status_code and 'Paid' for invoice_ status_description.

The INSERT command is quite useful for loading a small number of records into our database. But what if we want to insert a large number of records from a file? In this case we can run the INSERT command for each record automatically using SQL programming. This can also be achieved using a GUI interface such as that provided by phpMyAdmin, a process we now describe.

In the ch09 subdirectory of the text website (or text files) you will find a data file called products870.csv, which contains 870 product records. Note that these are not real products; they are generated by changing the names of some of the products that one usually finds in a health products store. A typical store would carry many more products than this. The first 20 records from the file are shown (partially) in FIGURE 9.14, with the lines truncated so the display will fit on the page. The value for each attribute is enclosed in a pair of single quotes and values are separated by commas. That is why we use the (conventional) file extension.csv, which stands for “comma-separated values”.

We can import these records into our Products table using phpMyAdmin. If we click on the Products table, we will see a link called Import. Clicking on the Import link brings up a web page similar to the one shown in FIGURE 9.15, where we have entered the name of our data file and clicked on the CSV radio button. We have also indicated that the fields are enclosed in single quotes and separated by commas. Finally, we use the default value for the escape character. This means that any occurrences of tab, newline, or that are preceded by should be treated as literal characters. For example, \ will be treated as a single . Clicking on the Go button will add all 870 records to the table, using 870 INSERT queries, as confirmed by the message shown in FIGURE 9.16.

There is a more efficient way to import a large number of records, by using the LOAD command. Let us explore this option with the help of the same Import interface, but this time we will use the Customers table. The data file we will import contains 10,000 customers, is called customers10000.csv, and a copy is also provided in the ch09 subdirectory of the text files. As with our products data file, a truncated version is shown in FIGURE 9.17.

This time we choose the CSV using the LOAD DATA radio button, but the rest of the selections are the same as before. Clicking on the Go button in FIGURE 9.18 will add all 10,000 records using a single query based on the LOAD command in SQL, as shown in FIGURE 9.19. Caution should be exercised when loading large files through the phpMyAdmin interface. There may be a limit of 2MB. Even if your file is smaller than 2MB, the LOAD command may not be successful. We recommend that you upload the file through other means to the server that hosts your MySQL server. Then execute the SQL query preferably through the command-line interface that we will look at shortly.

In the meantime, look at the SQL query shown in FIGURE 9.20, which uses the SQL LOAD command. Here is the abbreviated (and generic) format of the LOAD command that we used for our example:

LOAD DATA LOCAL INFILE 'file_name'
    INTO TABLE tbl_name
    FIELDS TERMINATED BY 'string'
    ENCLOSED BY 'char'
    ESCAPED BY 'char'
    LINES TERMINATED BY 'string';

Here we see only the options that we actually used in loading our customer records. See the MySQL manual for details about many other options.

First, the keyword LOCAL means that the file needs to be copied to the server that hosts MySQL. That is why we have a funny filename in the command shown in Figure 9.20. Our file customers10000.csv was copied to a temporary file on the server, and the filename used in the command is that of the temporary file. We indicate that the fields are enclosed in single quotes and separated by commas. We use the default value for the escape character. As before, that means all occurrences of tab, newline, or that are preceded by should be treated as literal characters.

We have now seen two commands (INSERT and LOAD) for getting data into our database. But what if we inadvertently entered some wrong data, or some piece of data already in the database has to be updated? Clearly we also need an UPDATE command and, of course, there is one. Here is its general syntax:

UPDATE 'table_name'
    SET column1_name=expression [column2_name=expression2,...]
    [WHERE where_expression]
    [LIMIT n]

The command works as follows. The columns in the table that are modified are those specified in the WHERE clause, and each of those rows has each column named in the SET clause set to the value of the corresponding expression. If there is no WHERE clause, be careful to note that all rows of the table are modified. If the LIMIT clause is given, the value n specifies the maximum number of rows to be modified.

See the end-of-chapter Short Exercises section for an exercise giving you a chance to get familiar with this command.

We now know how to “populate” our database using two data manipulation commands, INSERT and LOAD. Two other commands, DELETE and TRUNCATE, will allow us to “de-populate” our database. Before looking at these two destructive commands, we will take a brief look at the most important data retrieval command, SELECT. It will be discussed in more detail later in the chapter when we discuss the command-line interface. However, in order to delete records, we will have to browse our tables and find what to delete. So a simple introduction to data retrieval is in order.

The records we have in our database allow us to experiment with the SELECT command. We can simply click on the Browse link when we are in the Customers table, and we will get (by default) the first 30 records in the table, as shown in FIGURE 9.21. The corresponding SQL query is shown in FIGURE 9.22 and you can see that these records have been retrieved from the database using a SELECT command.

In Figure 9.22 we show the use of the SELECT command in its simplest form to select the complete record by specifying '*' after the keyword SELECT. The word Customers after FROM indicates the table, and the range of 0 to 30 records appears after the keyword LIMIT. We will be experimenting with more sophisticated SELECT statements later on in this chapter, but for the moment we just look at how to delete a few, or all, of the records from a database.

The Customers table shown in Figure 9.21 has one of the records highlighted with a different color, because we are hovering our mouse over that record. We are going to pick the record for deletion by clicking on the red X icon that appears just before the beginning of the record. FIGURE 9.23 shows the window that will pop up. It also shows the corresponding SQL query. The query uses the DELETE command. Once again, there are more options than we show, but the typical (generic) syntax for deleting from a single table looks like this:

DELETE FROM table_name
    [WHERE where_condition]
    [ORDER BY...]
    [LIMIT row_count]

The WHERE clause allows you to specify a condition that a record must satisfy for it to be deleted. In our case, we specified that the customer_id must match the specified value. The ORDER BY option allows us to specify the attributes that should be used to order the records for deletion. The ordering is especially relevant if we were going to limit the number of records that should be deleted using the option LIMIT. In our example query, we are limiting the number of deletions to a single record. This is redundant, since customer_id is unique, and there will be only one record that will match the WHERE clause in any case.

Since we have started deleting records, why stop at one record? Let us delete all the records and then rebuild our data—we now have the power. The command TRUNCATE TABLE allows us to delete all the records in a table. We can invoke it from phpMyAdmin by clicking on the database and then clicking on the red X next to the table name. A warning window including the SQL query will pop up as shown in FIGURE 9.24. Click the OK button and all the data in the table will be deleted. Most database engines implement the TRUNCATE TABLE command by dropping the table and recreating an empty table. It is much faster than deleting individual records when the tables are large. Should we spare you the obvious warning about exercising caution while using DELETE and TRUNCATE TABLE commands?

Most retail businesses will have some kind of inventory management system that will be part of the “back office” for the use of store management. It will handle the wholesale purchase of items from suppliers and maintain the data necessary to keep track of inventory.

Systems like this are not unique to e-commerce businesses. Our system can work with such an inventory management system simply by interacting with the product-related tables. We have chosen to limit the scope of our system, since the inclusion of many additional tables would not help us learn any new features of web programming. However, for the business you are working on in the “parallel project”, you may find it convenient to add a number of additional tables to deal with the specific activities in which your business is involved. Be thinking about the possibilities as you work on the next iterations of the parallel project at the end of this chapter and the next.

In the absence of a complete inventory management system, we have filled as many tables as necessary for experimenting with the database. Readers are encouraged to browse through the populated tables from the book’s website.

Later on in this chapter we will also describe how you can import our entire database into your own MySQL system so that you can experiment with it. In the next chapter, we will also see how to populate other tables using simulated e-commerce, when we process online “purchases” of our products by our customers.

However, our limited discussion of database management is not yet complete. We need to look at the command-line interface to MySQL. The command-line interface is generally less user-friendly than phpMyAdmin, but much faster to use for those who know what they are doing, especially when queries are more complicated. It is also less forgiving than the GUI counterpart, and is therefore meant for more knowledgeable users. To become comfortable with database management, it is necessary for all web programmers to learn how SQL queries are executed via the command-line interface, since when such queries are sent through a programming language API the commands have essentially the same form as when they are entered directly by typing them in at a command-line prompt.

FIGURE 9.25 shows our first command-line interaction with MySQL. In line 1 we use the Linux command¹

mysql –u webbook –p

to log in to our MySQL system. Note that the command to access the MySQL system under Linux is an all-lowercase mysql. The name following the –u option is the username by which MySQL identifies the user. The –p option indicates that the username requires a password, which will be entered in response to the prompt from MySQL on line 2.

As is always the case in these situations, when the password is typed, it does not show up on the screen. Once the password is verified and accepted, the prompt

mysql>

appears, and we are then ready to enter our commands at the command-line interface.

The first thing we have to do is tell MySQL which database we would like to use. That is the purpose of the USE command in line 9:

USE webbook;

We continue to capitalize MySQL commands, for emphasis, in our discussions of them, but we have saved time when entering them at the command line by using lowercase. Note that most commands in MySQL have to be terminated with a semicolon (;). Some commands do not actually require the semicolon, but having a semicolon at the end of those commands does not cause an error, so it is much safer, and a recommended “best practice”, simply to put a semicolon at the end of every command.

With the command

SHOW tables;

in line 13 we can find out what tables are currently in the database we have said we want to use. In this case, we see the 12 tables of our own Nature’s Source database that we have been discussing.

Next, we execute a simple SELECT command (line 31) to retrieve all the records from the table Ref_Invoice_Status:

SELECT * FROM Ref_Invoice_Status;

There are only two such records, as the display shows (lines 32–38). The use of * in this context means that we want to see all the fields from each record. This SELECT command is similar to the one we saw earlier. Note the format of the output for each of the queries we have seen here, which is typical.

In fact, all the SQL commands we saw in the previous section can be executed using the command-line interface, but in some cases—such as creating a table or adding one or two records—it may be easier to use the phpMyAdmin GUI.

Unfortunately, the phpMyAdmin GUI may be of limited use when we are specifying more complex data retrieval queries using SELECT. Let’s now investigate some more sophisticated retrievals using the command-line interface. In a SELECT query, the user describes only the desired result set. The following are keyword modifiers commonly used with a SELECT command:

• FROM, which is followed by a comma-separated list of the names of the tables from which the data is to be taken

• WHERE, which is followed by a comma-separated list of the conditions that specify which rows are of interest for the retrieval

• GROUP BY, which is followed by information indicating how the data in rows with related values is to be combined

• ORDER BY, which is used to identify which columns are used to sort the retrieved data

• LIMIT, which specifies a range of records for which the data is to be retrieved

We will explore these keywords with the help of some queries. Note that in the text discussion we continue to use all uppercase for the names of MySQL commands and their modifiers to distinguish them from the (user-chosen) names of other entities. When typing them into the command-line interface, however, it is easier to take advantage of the case-insensitivity of MySQL and use all lowercase. Keep in mind, however, that the user-chosen names (for tables and attributes, for example) will be case-sensitive, at least on Linux and other Unix-based systems.

Calling Built-In MySQL Functions and Performing Simple Arithmetic During Data Retrieval

MySQL has some built-in functions that can be very useful in data retrieval. Let us begin by illustrating COUNT( ), a function that retrieves just the total number of records, instead of the records themselves. So in FIGURE 9.26 our first query (line 1) is

SELECT COUNT(*) from Customers;

Use of the COUNT( ) function in SQL.

and the result shows the total number of records in the Customers table. You may recall that phpMyAdmin generated queries that used single quotes around table and attribute names. We are not using single quotes around the table names here, since they are unnecessary if the names do not contain any spaces. But if we had a table with a name like Our Customers, for example, we would have to enclose it in single quotes. However, best practice would dictate that we avoid such names.²

The second query in Figure 9.26 is

SELECT country, COUNT(*) FROM Customers GROUP BY country;

and uses the GROUP BY option (line 9), where the records are grouped by the values in the specified list of attributes. We are grouping by the country, so there will be a separate record for each country. That is why we get two records for two countries in our table, one for Canada and the other for the USA (we only have customers in those two countries).

Another useful MySQL function is SUM( ). FIGURE 9.27 shows its use with the following query from line 1:

SELECT SUM(product_inventory) FROM Products;

This query sums up the values of the attribute product_inventory for every record in the Products table. So we see that there are 43,097 items in our inventory (line 5).

Use of the SUM( ) function in SQL.

The second query in Figure 9.27 also shows that we can do simple arithmetic during data retrieval using SQL (line 8):

SELECT SUM(product_inventory)/COUNT(*) FROM Products;

This query gives us the average number of items per product (line 12).

So far each of our queries has dealt with all of the records in a given table. We can restrict the scope of our retrieval by using a WHERE clause similar to the one we used for DELETE. Let us say we wanted to find out how many products in the Products table have fewer than 10 items in stock. As FIGURE 9.28 shows, we can use the query (line 1)

SELECT COUNT(*) FROM Products WHERE product_inventory < 10;

which gives a result of 96 (line 5).

If we want to sort the records, we can use the ORDER BY option, as shown in FIGURE 9.29 (line 4). In this case we want to retrieve a list of the products with inventory size greater than 90 and have it sorted based on the value of the attribute product_inventory. The DESC option indicates that we want the sorting performed in descending order. We also used the LIMIT option here so that we only see the top 10 records in the list. The LIMIT option has two (comma-separated) values in this case, 0 and 10. The first indicates how many records to skip before starting the retrieval, and the second tells how many records to retrieve. Since the first value is 0, it is actually redundant in this case, and we could have used the single value 10.

Note that this (somewhat longer than usual) query is entered over several lines, and the prompt from MySQL changes to -> from the second line on, until we enter the terminating semicolon.

So far, in making our queries, we have only dealt with a single table. Our retrievals can also collect information from multiple tables at the same time, which is referred to as a join. We illustrate this in FIGURE 9.30. The query in this figure is our most complex query to date.

In this example, we want to list the number of products in each product category as well as the total number of items in each category. We want to use the category description as the first column in our retrieved data. The category description is in the Ref_Product_Categories table, while the rest of the information is in the Products table.

This query also employs another useful feature of SQL that allows us to assign temporary aliases to certain entities, which provide more meaningful or concise names to be used within the query itself. Here, for example, we are indicating that the product_category_description will also be known as category, by using the keyword AS (line 1). Similarly, the count of products will also be known as products, and the sum of the product inventory will be known as product_inventory. We further abbreviate the Products table simply as P and the table Ref_ Product_Categories as R (line 4).

These two tables are “joined” by requiring the following condition, which connects them via their common keys, to be satisfied (line 5):

P.product_category_code = R.product_category_code

The records are then grouped by category (line 6), and finally they are sorted in descending order based on the value of products (line 7), and we are also limiting ourselves to viewing the first 10 records (line 8). The join we have seen in this case is the simplest form of join. SQL allows for more sophisticated joining of tables. See the MySQL manual or the References section at the end of this chapter for more information.

The data from any of the SQL queries we might make can be stored in new tables. We will look into that facility in the next section, along with general importing and exporting of both individual tables and entire databases.

Let’s illustrate this process by copying just part of a table. We can achieve this by combining the CREATE and SELECT commands, as shown in FIGURE 9.31. Suppose we want to make a copy of just a portion of our Customers table. We will only copy the customer_id, customer_first_name, customer_last_name, and login_name to another table called Customers2. First, we have to retrieve the required information, and here is the SELECT command we need:

SELECT customer_id,customer_first_name,
       customer_last_name,login_name
       FROM Customers;

Now all we have to do is precede this command with

CREATE Customers2 AS

and the output from the SELECT statement will be stored in the table Customers2. Figure 9.31 shows the complete MySQL session that performs this action. The SHOW tables; command (line 6) shows that the table Customers2 does not exist. We then create it using the above described combination of CREATE and SELECT. We then verify the creation of the table by looking at the first five records in the newly created Customers2 table.

Later, we delete the table with a

DROP TABLE Customers2;

command (not shown in the session), since we do not want it to be part of our database.

Now we will show you how to create a copy of our entire database. First, we will use the phpMyAdmin interface, and then the command-line option.

In phpMyAdmin, you click on the database you want to “export” (webbook in our case) and then click on the Export link at the top. FIGURE 9.32 shows the web page that will pop up. You can select the tables you want to export, as well as the format of the exported file. We will use the default options of exporting all the files and exporting them as an SQL file. Clicking on the Go button will bring up the dialog box that tells you that the file will be saved as webbook.sql, as shown in FIGURE 9.33. You can save the file in an appropriate location.

The exporting and importing of large databases using phpMyAdmin can be a problem, as it may result in an unusually high volume of traffic over the Internet. For this reason, the command-line option is the preferred method for performing these tasks. You need a related command-line utility called mysqldump on your system, which should be available with any installation of MySQL.

FIGURE 9.34 illustrates how to export our entire database using a mysqldump command on a Linux system. The command works much like the mysql command. After the –u option, we specify that the user is webbook, and the –p option indicates that we will be entering a password.

The name webbook after the –p option says that we want to dump the webbook database. The > sign redirects the output to a file called backupbookSep09.sql.

We confirm the existence of this file simply by doing a directory listing (line 3). The file is in place and has approximately 1.8MB worth of data. The format of the file created by exporting from phpMyAdmin and mysqldump is identical. We can take a quick peek at the file created by mysqldump, as shown in FIGURE 9.35.

Note that the most recent version of this file (which you should now use) is called

webbook2e_backup20150504.sql

and is available from the ch09 subdirectory of the text website.

As we can see, it is really just a file containing SQL commands that can be used to create a database, and you can run these SQL commands to recreate the database. In fact, you can import the entire Nature’s Source database (that is, the webbook database) to your own system using the commands in this file. First, you need to have an empty database with complete manipulation privileges. It can have any name. To illustrate, we will leave the webbook database unchanged and import its contents from the most recent version of the backup file into another database called webbook1 using this backup file. Our system administrator has created this database for us and granted us all the necessary privileges. All we need to do is run the following command:

mysql –u webbook –p webbook1 < webbook2e_backup20150504.sql

After the –u option, we are specifying that the user is webbook. The –p option again indicates that we will be entering a password. The name webbook1 after the –p option tells us that we want to work with the webbook1 database. The < sign redirects the input from our previously created file webbook2e_backup20150504.sql, which contains all the commands necessary to replicate the original database.

All of this activity, including the results of the command-line import, is shown in FIGURE 9.36 and its continuation, FIGURE 9.37 (except in the figures we are using the original backup file backupbookSep09.sql rather than the latest webbook2e_backup20150504.sql). We refer to the line numbers in those two figures to help you follow the action. First, we verify that we do in fact have a database called webbook1 (lines 9–10 of Figure 9.36). There are no tables in the database, since the command SHOW tables; comes up empty (lines 11–12 of Figure 9.36). Now we exit from MySQL and run the MySQL command that imports the database from the Linux command-line interface (lines 14–17 of Figure 9.36). As Figure 9.37 now shows, by going back into the database we can see that all the tables are in place. We also perform two SELECT queries (lines 50 and 62 of Figure 9.37) to do a “spot check” to convince ourselves that the records have been imported and we have the right number of customers. All of this takes place in lines 18–72 of Figure 9.37.

As we cautioned before, using phpMyAdmin over the Internet can be problematic for importing large databases. Our database is of moderate size—less than 2MB. Let us see what happens if we try to import it using phpMyAdmin. FIGURE 9.38 shows what will happen if we click on a database such as webbook3, which is currently empty. We indicate that we want to import from the file webbook.sql that we had saved earlier by exporting through phpMyAdmin. Clicking on the Go button starts the importing process.

Unfortunately, as shown in FIGURE 9.39, phpMyAdmin returns an error saying that it does not have the resources to carry out the importing task. Given what we have said, this is not entirely surprising. It follows that your best option may well be to work at the command line when you are importing a database, as shown in Figures 9.36 and 9.37.