- Introduction
- The Parts of a Table
- Examples of Tables
- Key Points
This chapter is from the book
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.