When you are in a big electronics store buying the latest edition of the iPod, how does that store’s inventory tracking system know you just bought an iPod and not, for example, a car stereo or a television?

Let’s walk through the process of buying an iPod and consider all the implications this has on the inventory database that sits far underneath all the shiny, new gadgets on the sales floor.

When you hand the iPod box to the cashier, a barcode scanner reads the label on the box, which has a product identification number. In barcode language, this number might be something like 885909054336. The barcode representing this number can be seen in Figure 1.

Figure 1. A sample barcode

The barcode acts as a unique identifier for the product; in this case, all iPods that are the same model as the one passing across the barcode reader have the same exact barcode.

The barcode scanner relays the number represented by the barcode to the register at the cashier’s station, which sends a request (or a query) to the store’s inventory database. This database could be in the same store as the register or somewhere across the country or even around the world, thanks to the speed and reliability of the Internet.

The register asks the database, “What are the name and price of the product that has this barcode?” To which the database responds, “That product is an iPod, and it costs $200.”

You, the customer, pay your $200 and head home with a new toy. Your work in the store is finished, but the inventory management system still needs to reconcile your purchase with the database!

When the sale is complete, the register needs to tell the database that the iPod was sold. The ensuing conversation goes something like the following.

Register: “How many products with this barcode are in our inventory?” Database: “1,472.”

Register: “Now, 1,471 products with this barcode are in our inventory.”

Database: “OK.”

What Did the Database Do?

Data Retrieval

Of course, this is not the whole story. Much more happens behind the scenes than simple conversational requests and acknowledgements.

The first interaction the register had with the database occurred when the request for the product name and price was processed. Let’s take a look at how that request was really handled.

If the database is an SQL database, like MySQL or PostgreSQL or many others, then the request would be transmitted in the standard Structured Query Language (SQL). The software running on the register would send a query to the database that looks similar to the following.

SELECT name, price FROM products WHERE id = 885909054336;

This query instructs the database to look in the products table for a row (also called a record) in which the id column exactly equals 885909054336.

Every database may contain multiple tables, and every table may contain multiple rows, so specifying the name of the table and the row’s unique identifier is very important to this query. To illustrate this, an example of a small products table is shown in Figure 2.

When the database has successfully found the table and the row with the specified id, it looks for the values in the name and price columns in that row. In our example, those values would be “iPod” and “200.00”, as seen in Figure 2. The execution of the previous SELECT statement, which extracts those values from the table, is shown in Figure 3.

The database then sends a message back to the register containing the product’s name and price, which the register interprets and displays on the screen for the cashier to see.

{qbapagebreak title=Data Modification}

Data Modification

The second time the register interacts with the database, when the inventory number is updated, requires a little more work than simply asking the database for a couple numbers. Now, in addition to requesting the inventory number with a SELECT statement, an UPDATE statement is used to change the value of the number.

First, the register asks the database how many iPods are in the inventory (or “on hand”).

SELECT onhand FROM products WHERE id = 885909054336;

The database returns the number of products on hand, the register decrements that number by one to represent the iPod that was just sold, and then the register updates the database with the new inventory number.

UPDATE products SET onhand = 1471 WHERE id = 885909054336;

This sequence is presented in Figure 4.

In Figure 4, the database responds to the UPDATE query with UPDATE 1, which simply means one record was updated successfully.

Now that the number of iPods on hand has been changed, how does one verify the new number? With another SELECT query, of course! This is shown in Figure 5.

Now, the register has updated the database to reflect the iPod you just purchased and verified the new number of iPods on hand. That was pretty simple, wasn’t it?

More on Databases

You now know databases are made of tables, which are, in turn, made of records. Each record has values for specific columns, and in many cases, a record can be uniquely identified by the value contained in at least one column.

In our example, the barcode number uniquely identified the iPod, which cost $200, in the products table. You have also seen that values in a database can be modified. In this case, the number of iPods on hand was changed from 1,472 to 1,471.