Home > Articles > Data > SQL

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Examples of Tables

Up to now, we have discussed the theory of tables, but you have not seen any real ones. In the following sections you will see some actual tables. We look at a table to see how it looks in both Oracle and Access. We discuss some of the design decisions that are used in constructing many tables. We also examine the tables of the Lunches database, which is used in many of the examples throughout this book.

1-12 An example of a table in Oracle and Access

This section shows the same table in both Oracle and Access. This is our first opportunity to examine how Oracle and Access compare.

You will have to decide for yourself how similar they are and how different they are. To me, this example shows that they are about 90 percent similar and about 10 percent different. Of course, this is just one example. You might ask yourself which percentages you would use to describe this.

Oracle tables can be shown in two formats that are very similar, but have a few slight differences. To keep things simple here, I am only showing you one of those formats. The following Oracle table was obtained using the “SQL Command Line” environment. The other Oracle format occurs in the “Database Home Page” environment. I will discuss it briefly in the notes at the end of this section.

l_employees table: Oracle format

EMPLOYEE                      DEPT               CREDIT PHONE  MANAGER
      ID FIRST_NAME LAST_NAME CODE HIRE_DATE      LIMIT NUMBER      ID
-------- ---------- --------- ---- ------------ ------- ------ -------
     201 SUSAN      BROWN     EXE  01-JUN-1998   $30.00 3484    (null)
     202 JIM        KERN      SAL  16-AUG-1999   $25.00 8722       201
     203 MARTHA     WOODS     SHP  02-FEB-2009   $25.00 7591       201
     204 ELLEN      OWENS     SAL  01-JUL-2008   $15.00 6830       202
     205 HENRY      PERKINS   SAL  01-MAR-2006   $25.00 5286       202
     206 CAROL      ROSE      ACT  (null)        (null) (null)  (null)
     207 DAN        SMITH     SHP  01-DEC-2008   $25.00 2259       203
     208 FRED       CAMPBELL  SHP  01-APR-2008   $25.00 1752       203
     209 PAULA      JACOBS    MKT  17-MAR-1999   $15.00 3357       201
     210 NANCY      HOFFMAN   SAL  16-FEB-2007   $25.00 2974       203

Similarities between Oracle and Access

  • Column names are printed at the top of the column. The column names are part of the structure of the table, not part of the data in the table.
  • Sometimes the column names shown in the column headings are truncated. This is a slight problem. You are given tools to deal with it.
  • Columns containing text data are justified to the left.
  • Columns containing numbers are justified to the right.
  • Columns containing dates often display only the date. The format for displaying the date is not part of the data. The value of the date is stored in the table, but the format of the date is specified separately. The date actually contains both a date and a time, but the time is often not displayed.
  • Columns displaying currency amounts are actually stored as numbers, and use a format to put in the dollar signs and decimal points.

Differences between Oracle and Access

  • Display framework: Oracle displays lines of character data. Access uses graphical techniques to display the data in a grid and color the borders of the grid.

  • Case: The Oracle table is shown all in uppercase. The Access table uses uppercase only for the first letter. It is a common convention to set the databases up this way. Mixed-case data can be put into an Oracle table, but this makes the data more difficult to handle, so Oracle data is usually either all uppercase or all lowercase. Access data is handled as if it were all uppercase, although it is displayed in mixed case. This makes it look nicer, but sometimes it can also be deceiving. In Access, the data appears to be mixed case, but the data behaves as if it were in uppercase. For instance, John and jOhn appear different in Access, but they are handled as if they are the same.

  • Column headings: Oracle can use several lines for a column heading. Access displays the heading on a single line.

  • Date formats: The dates above show Oracle and Access using the same date format. I made that happen here because I wanted Oracle and Access to look similar. However, on your computer the dates will probably use different formats.

    Oracle and Access can both display dates in a variety of formats. Each has a default format to use for dates when no other format is specified. However, Oracle uses one method to specify this default format for dates and Access uses a different method.

  • Date alignment: Oracle aligns dates to the left, whereas Access aligns them to the right.

  • Nulls: In this book, I have set up Oracle to always display nulls as (null) in all the columns of every table. This cannot easily be done in Access.

  • Position pointer: The Access table contains a record selector and a pointer to a particular field within that record, which allows you to modify the data. The Oracle table does not contain these.

  • Ability to add data: In Access, a blank row at the bottom of a table indicates that new rows of data can be entered into the table. Also an extra column is displayed called “Add New Field”. This is not done in Oracle.

1-13 Some design decisions in the l_employees table

The l_employees table contains some design decisions that I want to point out to you because they reflect some common practices within relational databases. Like all design decisions, they could have been made in other ways. This is not the only way to design the table. It might not even be the best way. But you may often encounter these design decisions and you need to be aware of them.

l_employees table

EMPLOYEE                      DEPT               CREDIT PHONE   MANAGER
      ID FIRST_NAME LAST_NAME CODE HIRE_DATE      LIMIT NUMBER       ID
-------- ---------- --------- ---- ------------ ------- ------  -------
     201 SUSAN      BROWN     EXE  01-JUN-1998   $30.00 3484     (null)
     202 JIM        KERN      SAL  16-AUG-1999   $25.00 8722        201
     203 MARTHA     WOODS     SHP  02-FEB-2009   $25.00 7591        201
     204 ELLEN      OWENS     SAL  01-JUL-2008   $15.00 6830        202
     205 HENRY      PERKINS   SAL  01-MAR-2006   $25.00 5286        202
     206 CAROL      ROSE      ACT  (null)        (null) (null)   (null)
     207 DAN        SMITH     SHP  01-DEC-2008   $25.00 2259        203
     208 FRED       CAMPBELL  SHP  01-APR-2008   $25.00 1752        203
     209 PAULA      JACOBS    MKT  17-MAR-1999   $15.00 3357        201
     210 NANCY      HOFFMAN   SAL  16-FEB-2007   $25.00 2974        203

Design decisions to be aware of

  • The phone_number column contains text data, not numbers. Although the data look like numbers, and the column name says number, it actually has a text datatype. You can tell this by its alignment, which is to the left. The reason the table is set up this way is that the phone number data will never be used for arithmetic. You never add two phone numbers together or multiply them. You only use them the way they are, as a text field. So this table stores them as text.
  • The employee_id column contains numbers. You can tell this by its alignment, which is to the right. Now, we do not do arithmetic with employee IDs, we never add them together, so why isn’t this a text field, too? The answer is that numbers are often used for primary key columns even when no arithmetic will be performed on them. This can allow the computer to handle the table more quickly.
  • The manager_id column contains numbers, but it is not a primary key column. So why doesn’t it contain text? This column is intended to match with the employee_id column, so it has been given the same datatype as that column. This improves the speed of matching the two columns.
  • The name of the table, l_employees, might seem strange. The l indicates that this table is part of a group of tables. The names of all the tables in the group start with the same letter(s). In this case it shows that the table is part of the Lunches database. (Here I use the term database to mean a collection of related tables.)
  • The people who design databases put a considerable amount of work into the consistent naming of objects, using standard prefixes, suffixes, abbreviations, and column names. This makes the whole model easier to understand and more usable for the code that is developed for each database.

1-14 The Lunches database

Most of the examples of SQL code in this book are based on the Lunches database. You can get a complete listing of this database from the Web site. To read this book, you will need to understand the story and the data, so here is the basic story.

There is a small company with ten employees. This company will serve lunch to its employees on three occasions. Each employee can attend as many of these lunches as his or her schedule permits. When employees register to attend a lunch, they get to pick what they want to eat. They may choose from among the ten foods available to them. They can decide to have a single portion or a double portion of any of these foods. The Lunches database keeps track of all this information.

That is the story. Now let’s look at the data. When I call this a database, I mean that it is a collection of related tables. The set of tables, taken together, tell the story. There are seven tables in this database:

  • Employees (l_employees)
  • Departments (l_departments)
  • Constants (l_constants)
  • Lunches (l_lunches)
  • Foods (l_foods)
  • Suppliers (l_suppliers)
  • Lunch Items (l_lunch_items)

To show that these tables are all related to each other and to distinguish them from other tables we may use, the names of these tables are all prefixed with the letter l. When there are multiple words, such as lunch_items, the spaces are replaced with underscore characters. This helps the computer understand that the two words together are a single name.

l_employees table

EMPLOYEE                      DEPT               CREDIT PHONE  MANAGER
      ID FIRST_NAME LAST_NAME CODE HIRE_DATE      LIMIT NUMBER      ID
-------- ---------- --------- ---- ------------ ------- ------ -------
     201 SUSAN      BROWN     EXE  01-JUN-1998   $30.00 3484    (null)
     202 JIM        KERN      SAL  16-AUG-1999   $25.00 8722       201
     203 MARTHA     WOODS     SHP  02-FEB-2009   $25.00 7591       201
     204 ELLEN      OWENS     SAL  01-JUL-2008   $15.00 6830       202
     205 HENRY      PERKINS   SAL  01-MAR-2006   $25.00 5286       202
     206 CAROL      ROSE      ACT  (null)        (null) (null)  (null)
     207 DAN        SMITH     SHP  01-DEC-2008   $25.00 2259       203
     208 FRED       CAMPBELL  SHP  01-APR-2008   $25.00 1752       203
     209 PAULA      JACOBS    MKT  17-MAR-1999   $15.00 3357       201
     210 NANCY      HOFFMAN   SAL  16-FEB-2007   $25.00 2974       203

The l_employees table lists all the employees. Each employee can be identified by an employee ID, which is a number assigned to him or her. This allows the company to hire two people with the same name. The primary key is the employee_id column.

Each employee has a manager, who is also an employee of the company. The manager is identified by his or her employee ID. For instance, the manager_id column shows that Jim Kern is managed by employee 201. Employee 201 is Susan Brown.

Susan Brown and Carol Rose are the only employees without a manager. You can tell this because there is a null in the manager_id columns. However, these nulls mean different things.

Susan Brown is the head of the company. The null in this case does not mean that we do not know who her manager is. Rather, it means that she does not have a manager.

Carol Rose is a new hire. The null in her manager_id column could mean that she has not yet been assigned to a manager or it could mean that the information has not yet been entered into the database.

l_departments table

DEPT
CODE DEPARTMENT_NAME
---- ------------------------------
ACT  ACCOUNTING
EXE  EXECUTIVE
MKT  MARKETING
PER  PERSONNEL
SAL  SALES
SHP  SHIPPING

Each employee works for one department. The department code is shown in the l_employees table. The full name of each department is shown in the l_departments table. The primary key of this table is dept_code.

These tables can be linked together by matching the dept_code columns. For example, the l_employees table shows us that employee 202, Jim Kern, has a department code of SAL. The l_departments table says that the sales department uses the department code SAL. This tells us that Jim Kern works in the sales department.

l_constants table

                               BUSINESS
BUSINESS_NAME                  START_DATE  LUNCH_BUDGET OWNER_NAME
------------------------------ ----------- ------------ --------------
CITYWIDE UNIFORMS              01-JUN-1998      $200.00 SUSAN BROWN

The l_constants table contains some constant values and has only one row. We use these values with the other tables of the database. These values are expected to change infrequently, if at all. Storing them in a separate table keeps the SQL code flexible by providing an alternative to hard-coding these values into SQL. Because the table of constants has only one row, it does not need a primary key.

l_lunches table

LUNCH_ID LUNCH_DATE  EMPLOYEE_ID DATE_ENTERE
--------- ----------- ----------- -----------
        1 16-NOV-2011         201 13-OCT-2011
        2 16-NOV-2011         207 13-OCT-2011
        3 16-NOV-2011         203 13-OCT-2011
        4 16-NOV-2011         204 13-OCT-2011
        6 16-NOV-2011         202 13-OCT-2011
        7 16-NOV-2011         210 13-OCT-2011
        8 25-NOV-2011         201 14-OCT-2011
        9 25-NOV-2011         208 14-OCT-2011
       12 25-NOV-2011         204 14-OCT-2011
       13 25-NOV-2011         207 18-OCT-2011
       15 25-NOV-2011         205 21-OCT-2011
       16 05-DEC-2011         201 21-OCT-2011
       17 05-DEC-2011         210 21-OCT-2011
       20 05-DEC-2011         205 24-OCT-2011
       21 05-DEC-2011         203 24-OCT-2011
       22 05-DEC-2011         208 24-OCT-2011

The l_lunches table registers an employee to attend a lunch. It assigns a lunch ID to each lunch that will be served. For example, employee 207, Dan Smith, will attend a lunch on November 16, 2011. His lunch is identified as lunch_id = 2.

The lunch_id column is the primary key of this table. This is an example of a surrogate key, which is also called a meaningless primary key. Each row is assigned a unique number, but there is no intrinsic meaning to that number. It is just a convenient name to use for the row, or the object that the row represents — in this case, a lunch.

The l_lunches table shows the most common way to use a surrogate key. Usually a single column is the primary key. That column has a different value in every row.

Some database designers like to use surrogate keys because they can improve the efficiency of queries within the database. Surrogate keys are used especially to replace a primary key that would have many columns, and when a table is often joined to many other tables.

Other designers do not like surrogate keys because they prefer to have each column contain meaningful data. This is an area of debate among database designers, with many pros and cons on each side. People who use databases need only be aware that these columns are meaningless numbers used to join one table to another.

l_foods table

SUPPLIER PRODUCT    MENU                                  PRICE
ID       CODE       ITEM DESCRIPTION             PRICE INCREASE
-------- ------- ------- -------------------- -------- --------
ASP      FS            1 FRESH SALAD             $2.00    $0.25
ASP      SP            2 SOUP OF THE DAY         $1.50 (null)
ASP      SW            3 SANDWICH                $3.50    $0.40
CBC      GS            4 GRILLED STEAK           $6.00    $0.70
CBC      SW            5 HAMBURGER               $2.50    $0.30
FRV      BR            6 BROCCOLI                $1.00    $0.05
FRV      FF            7 FRENCH FRIES            $1.50 (null)
JBR      AS            8 SODA                    $1.25    $0.25
JBR      VR            9 COFFEE                  $0.85    $0.15
VSB      AS           10 DESSERT                 $3.00    $0.50

The l_foods table lists the foods an employee can choose for his or her lunch. Each food is identified by a supplier ID and a product code. Together, these two columns form the primary key. The product codes belong to the suppliers. It is possible for two suppliers to use the same product code for different foods. In fact, the product code AS has two different meanings. Supplier JBR uses this product code for soda, but supplier VSB uses it for dessert.

The price increases are proposed, but are not yet in effect. The nulls in the price_increase column mean that there will not be a price increase for those food items.

l_suppliers table

SUPPLIER
ID       SUPPLIER_NAME
-------- ------------------------------
ARR      ALICE & RAY'S RESTAURANT
ASP      A SOUP PLACE
CBC      CERTIFIED BEEF COMPANY
FRV      FRANK REED'S VEGETABLES
FSN      FRANK & SONS
JBR      JUST BEVERAGES
JPS      JIM PARKER'S SHOP
VSB      VIRGINIA STREET BAKERY

The l_suppliers table shows the full names for the suppliers of the foods. For example, the l_foods table shows that french fries will be obtained from supplier ID FRV. The l_suppliers table shows that Frank Reed’s Vegetables is the full name of this supplier. The primary key of these tables is the supplier ID.

l_lunch_items table

                      SUPPLIER PRODUCT
 LUNCH_ID ITEM_NUMBER ID       CODE     QUANTITY
--------- ----------- -------- ------- ---------
        1           1 ASP      FS              1
        1           2 ASP      SW              2
        1           3 JBR      VR              2
        2           1 ASP      SW              2
        2           2 FRV      FF              1
        2           3 JBR      VR              2
        2           4 VSB      AS              1
        3           1 ASP      FS              1
        3           2 CBC      GS              1
        3           3 FRV      FF              1
        3           4 JBR      VR              1
        3           5 JBR      AS              1
(and many more rows)

When you look at the l_lunch_items table you need to be aware that the data in the item_number column is aligned to the right because it is a column of numbers. The data in the supplier_id column is aligned to the left because it is a column of text. So when you look at the first row, 1 ASP is not a single piece of data. Instead, the item_number value is 1 and the supplier_id value is ASP.

The l_lunch_items table shows which foods each employee has chosen for his or her lunch. It also shows whether they want a single or a double portion. For example, look at lunch_id 2, which we already know to be Dan Smith’s lunch on November 16. It consists of four items. The first item is identified as ASP-SW. Here I am putting the supplier_id and the product_code column data together separated by a hyphen. Looking in the l_foods table, we find this is a sandwich. The l_lunch_items table says he wants two of them, which is shown in the quantity column. See if you can figure out all the foods he wants for his lunch.

The correct answer is:

  • 2 sandwiches
  • 1 order of french fries
  • 2 cups of coffee
  • 1 dessert

The primary key of this table consists of the first two columns of the table, lunch_id and item_number. The item_number column is a tie-breaker column, which is another type of meaningless primary key. In this design, I wanted to use the lunch ID to identify each food within a lunch. However, most lunches have several foods. So I cannot use the lunch ID by itself as a primary key, because that would create several rows in the table with the same value in the primary key, which is not allowed. I needed a way for each row to have a different value in the primary key. That is what a tie-breaker column does. The item_number column numbers the items within each lunch. Therefore, the combination of lunch ID and item number provides a unique identity for each row of the table and can serve as the primary key. A primary key of this sort, containing more than one column, is sometimes called a composite key.

Challenging features of the Lunches database

Most SQL books have you work with a database that is tame and contains no challenges. This book is different. I have intentionally put some features in the Lunches database that could cause you to get the wrong result if you do not handle them properly. I show you how to become aware of these situations and how to deal with them. Many real business databases contain similar challenges. Here are a few of them:

  • Two employees are not attending any of the lunches — employee 209, Paula Jacobs, and employee 206, Carol Rose.
  • One food has not been ordered in any of the lunches — broccoli.
  • One of the departments is not yet staffed with any employees — the personnel department.
  • + Share This
  • 🔖 Save To Your Account