A relation is a two-dimensional table with no repeating groups. That means that if you look at the intersection of a column and a row, there will be only one value. What you see in Figure 1-2is certainly a table, but it isn't a relation. Why? Because there are multiple values in some of the rows in the Children column. In contrast, Figure 1-1 is a legal relation.

A relation has a name that is unique within its schema. Each column (or attribute) in a relation also has a name, but in this case, the name needs to be unique only within the table. In fact, as you will see shortly, there are times when you actually want to have columns with the same names in multiple tables.

In a well-designed relational database, each table represents an entity. We often document entities (and, as you will see, the relationships among them) in a diagram known as an entity-relationship diagram (ERD). There are many ways to draw ERDs, each of which can convey just about the same information. The particular style we'll be using in this book is known as the information engineering (IE) style. An entity is represented as a rectangle with its name in the top section and its attributes in the bottom, as you see in Figure 1-3.

A relation is both column-order independent and row-order independent. This mean that we can view the columns in any order and the rows in any order without losing the meaning of the data. The assumption is, however, that all the data in one row remain in that row.

Each column in a relation has a domain, an expression of the legal values for that column. In some cases, a domain is very specific. For example, if you are working with a column that stores the sizes of T-shirts, the entire domain might consist of the values S, M, L, XL, and XXL. Domains are more commonly, however, general data types, such as integer or date. ³

Once you assign a domain to a column, the DBMS will enforce that domain, rejecting any command that attempts to enter a value into the column that isn't from the domain. This is an example of a constraint on a relation, a rule to which the relation must adhere.

Each row in a relation must have a unique value that identifies the row. This primary key is made up of the values in one or more columns (the smallest number of columns needed to enforce uniqueness). A table that stores information about an order, for example, would probably use the order number as its primary key.

People are particularly difficult to identify uniquely, so we often assign each person in a table an arbitrary number. If you look back at Figure 1-3, you will see that there is a customer_numb attribute, representing a number that will be simply given to each customer when a row for a new customer is entered into the table. The IE diagramming method places an asterisk in front of the column or columns that make up a primary key, just as is done in Figure 1-3.

Sometimes there is no single column that will uniquely identify each row in a table. As an example, consider the table in Figure 1-4 (dependents), which lists employees' dependent children. We can't use the employee number as the primary key because customer numbers repeat for each child an employee has, and many employees have more than one child. By the same token, the children's names and birthdates aren't unique. The solution is to consider the values in two columns as the primary key. In this case, the employee number and the child's name make the best primary key. Taken as a unit, the two values are unique in every row. A primary key made up of more than one column is known as a concatenated key.

Why are unique primary keys so important? Because they ensure that you can retrieve every piece of data that you put into a database. If primary keys aren't unique, a query will retrieve one or more rows with a value you specify, but you can't be certain which is the exact row you want unless you know something that identifies just that one row. In fact, you should be able to retrieve any single data value knowing three things: the name of the table, the name of the column, and the primary key of the row.

As you will see later in this book, you specify a table's primary key when you define the table to the DBMS. The DBMS will then enforce a constraint that requires unique primary key values.

Sometimes you don't put data in some columns of some rows because you don't know the appropriate data values. The empty columns don't contain a zero or a blank. Instead, they contain a special indicator known as null, which means “unknown.”

There are two important implications of the presence of nulls in a table. First, we can't allow nulls as all or part of a primary key. If there is only one row with null for a primary key, then the property of unique primary key values is preserved. The minute we introduce a second row with a null primary key, however, the primary keys are no longer unique. A DBMS will therefore ensure that all primary keys have values, a constraint known as entity integrity.

Secondly, nulls can affect the result of queries. Assume, for example, that you want to retrieve the names of all employees who have a salary of more than $100,000. For all employees that have a value in the salary column, the answer to “Is the salary more than $100,000” will be either “yes” or “no.” But if the salary column contains null, the DBMS doesn't know the answer to the question; the result is “maybe.”

We say that a DBMS operates using three-valued logic: yes, no, or maybe. The question that remains is what a DBMS should do when the answer to the question it is asking is “may-be.” Should it retrieve rows with null or leave them out? The relational data model doesn't specify exactly what a DBMS should do, but does require the DBMS to act consistently— either always retrieve rows with nulls or always leave them out—and that the user be aware of what is happening. We'll deal with effect of nulls at various places throughout this book.

There are two primary types of tables with which you will be working when you use SQL. The tables that contain data that are stored in the database are known as base tables. However, the DBMS also uses several types of temporary tables that only exist in main memory. These are virtual tables and by definition they are not stored in the database. Most modern DBMS use several types of virtual tables, including views, temporary tables, and query result tables. If you want to keep the data in a virtual table, then those data must be inserted into a base table.

There are three types of relationships between entities that we encounter in our database environments: one-to-one, one-to-many, and many-to-many.

If you look back at Figure 1-8, you'll notice that some attributes appear in more than one entity. For example, you can see that the engine entity contains the VIN of the car into which it is inserted. This is how a relational database shows data relationships. When VIN in the engine entity has a value, it represents the relationship with a specific occurrence of car. Any attribute in an entity that is the same as the entire primary key of another entity is known as a foreign key. Table 1-1 lists all the foreign keys in the car database we have been developing.

A foreign key can be null. For example, if an engine isn't installed in a car, then the VIN attribute in the Engine entity will be null. However, foreign keys that are part of a primary key, such as the URL and VIN attributes in the listing entity, must have values to satisfy entity integrity.

When foreign keys are non-null, a matching primary key value must exist in the table referenced by the foreign key. When a car has an owner, for example, a row with the matching owner_numb must exist in the Owner table. Otherwise, it will be impossible to find information about that owner. This property is known as referential integrity: Every non-null foreign key value must reference an existing primary key value. As you will see throughout this book, much of what you do with SQL involves retrieving matching data using primary key-foreign key relationships.

Foreign keys are not limited to single columns; they can be concatenated, just like primary keys. As an example, consider a part of the database design for a very small accounting firm in Figure 1-9. Because the firm is so small, the database designer decides that employee numbers aren't necessary and instead uses the accountants' first and last names as the primary key of the accountant table. The project table, used to gather data about one accountant preparing one year's tax returns for one customer, uses the tax year and the customer number as its primary key. However, it has three foreign keys. (We'll get to those in a moment.) The form table that stores data about the forms that are part of a specific tax return uses the concatenation of the form's ID and the primary key of the project table for its primary key.

A foreign key is the same as the complete primary key of another table. Therefore, the acct_first_name attribute by itself in the project table is not a foreign key; neither is the acc_ last_name attribute. If you concatenate them, however, then they are the same as the primary key of the accountant table and, in fact, this is the unit with which referential integrity should be enforced.

Assume that “Jane Johnson” is working on customer 10100's 2014 tax return. It's not enough to ensure that “Jane” appears somewhere in the first name column in the accountant table and “Johnson” appears anywhere in the last name column in the accountant table. There could be many people named “Jane” and many with the last name of “Johnson.” What we need to ensure is that there is one person named “Jane Johnson” in the accountant table, the concatenation of the two attributes that make up the primary key.

The same holds true for the concatenated foreign key in the form table: the tax year and the customer number. A row with a matching pair must exist in the project table before referential integrity is satisfied.