Understanding Database Documentation

To successfully link tables together, you need to become familiar with your database and how your database accurately links tables and fields. You need to familiarize yourself with the relationships within your database.

The best place to find this information is to obtain two invaluable reference materials that are provided (usually) with every database system. These reference documents are:

• A data dictionary

• An entity-relationship diagram (also known as an ERD)

Your database administrator should know the locations of these documents for your system. Let's look briefly at how to understand and use these types of reference material.

What Is a Data Dictionary?

A data dictionary is a file that displays the basic organization of a database. In other words, a data dictionary tells you the relationships between the tables of a database. Data dictionaries usually contain information such as:

• All tables in the database

• The number of records in each table

• The data type of each field in the tables

Data dictionaries are usually text, Word, HTML, Excel, or PDF files and usually list information in alphabetical order.

Your system administrator should have a copy of the data dictionary. Most DBMSs provide a data dictionary upon delivery or installation of the system.

Note

Data dictionaries do not contain actual data from the data-base. A data dictionary's purpose is to provide information to aid in managing a database (such as understanding the relationships of database fields).

Let's take a look at the type of information contained in data dictionaries and how to decipher this information. The following looks at information from the xtreme.mdb data dictionary.

Tip

Appendix B, "The Xtreme.mdb Data Dictionary," contains a complete data dictionary of this database.

Tables 6-4 and 6-5 display information about the Customer and Orders tables, as found in a data dictionary. Table 6-4 pertains to the global customers of Xtreme Mountain Bikes, and Table 6-5 contains customer order information.

Table 6-4: The Customer table information (found within a data dictionary)

Field Name	Data Type	Field Size/Format	Description
Customer ID	Number	Long Integer	Customer unique identification number.
Customer Credit ID	Number	Long Integer	Customer unique credit identification number.
Customer Name	Text	40	Name of the customer.
Contact First Name	Text	30	First name of the contact individual.
Contact Last Name	Text	30	Last name of the contact individual.
Contact Title	Text	5	Title of the contact individual.
Contact Position	Text	30	Position of the contact individual.
Last Year's Sales	Currency	Currency	Last year's total sales for the customer.
Address1	Text	60	The primary street address information for the customer.
Address2	Text	20	A line for secondary address information for the customer.
City	Text	20	The city of the customer's address.
Region	Text	30	The region with which the customer is associated.
Country	Text	30	The country of the customer's address.
Postal Code	Text	10	The postal code of the customer's address.
E-mail	Text	50	The customer's contact e-mail address.
Web Site	Text	50	The customer's web site.
Phone	Text	20	The customer's phone number.
Fax	Text	20	The customer's fax number.

Table 6-5: The Orders table information (found within a data dictionary)

Field Name	Data Type	Length	Description
Order ID	AutoNumber	Long Integer	Unique identification number assigned to each order.
Order Amount	Currency	Currency	Total amount of the order.
Customer ID	Number	Long Integer	Customer unique identification number.
Employee ID	Number	Long Integer	Employee unique identification number.
Order Date	Date/Time	General Date (MM/DD/YYYY HH:MM:SS AM/PM)	Date on which order was placed.
Required Date	Date/Time	Short Date (MM/DD/YYYY)	Date on which order is required to be received.
Ship Date	Date/Time	General Date (MM/DD/YYYY HH:MM:SS AM/PM)	Date on which order was shipped.
Courier Web Site	Hyperlink	h"ttp"://www."up"s\.c\om	The courier's web site address.
Ship Via	Text	20	Comment regarding how product was shipped.
Shipped	Logical	Yes/No	Boolean that states "yes" or "no" to whether the product was shipped.
PO#	Text	50	The purchase order number.
Payment Received	Logical	Yes/No	Boolean that states "yes" or "no" to whether payment was received for the order.

Data dictionaries provide a lot of good information and are especially informative in looking at the detailed structure of a database.

In looking at these two tables, you might notice the Customer ID field within both. Data dictionaries are useful in finding common fields within different database tables.

However, as useful as a data dictionary might be, there is another type of file that better provides information about the relationship between tables. This type of file is known as an entity-relationship diagram (ERD).

ERDs are also usually provided with your database system. Check with your system administrator on obtaining a copy of your system's ERD.

Tip

Most databases (such as Access, Oracle, SQL Server) contain tools that can generate an ERD for you.

What Is an Entity-Relationship Diagram?

An entity-relationship diagram (ERD) is a graphical representation of the entities and the relationships between entities of a database. You're probably asking, "What are 'entities' and 'relationships'?" Let's break that definition down a little:

• An entity is the database object. In an ERD, entities are represented by a rectangle. For example, a database table is an entity.

• A relationship is the interaction between the entities or how the entities are connected. In an ERD, relationships are represented by lines, which join the entities.

Figure 6-1 displays an ERD for the xtreme.mdb database.

Figure 6-1: The ERD for the xtreme.mdb database

Understanding Table Relationships

Looking at the ERD in Figure 6-1, you may notice the database tables linked together by lines. In an ERD, the lines describe the matching fields within two tables. The lines also describe the type of relationship between two tables.

Although the xtreme.mdb file only uses one type of relationship, there are actually four types of relationships that may exist between database objects. These four types of relationships, known in database terminology as join types, define how two database objects match together. They are summarized in Table 6-6.

Table 6-6: The four join types

Join Type	Description
Inner	The most common type of join (also known as a one-to-one relationship). An inner join designates that a record in the parent table (the table you're linking from) is related to one and only one record in the child table (the table you're linking to). For example, you can use an inner join to view all customers and the orders placed. Only customers with placed orders will display within the report.
Left outer	A left outer join (also known as a one-to-many relationship) includes all records where the linked field value in both tables is an exact match (this being the same as an inner join). However, a left outer join also includes a row for every record in the primary (left) table for which the linked field value has no match in the secondary (right) table. For example, you can use a left outer join to view all customers and the orders they have placed. The report will display the customers with placed orders (as was the case with the inner join). However, you will also see a row of records for every customer who has not placed an order.
Right outer	A right outer join (also known as a one-to-many relationship) includes all the records where the linked field value in both tables is an exact match (this being the same as an inner join). However, a right outer join also includes a row for every record in the secondary (right) table for which the linked field value has no match in the primary (left) table. For example, you can use a right outer join to view all customers and the orders they have placed. The report will display the customers with placed orders (as was the case with the inner join). However, you will also see a row of records for every order without a customer.
Full outer	A full outer join (also known as a many-to-many relationship) includes all records in your linked tables. The result set includes all the records in which the linked field value in both tables is an exact match (this being the same as an inner join). It also includes a row for every record in the primary (left) table for which the linked field value has no match in the secondary (right) table and a row for every record in the secondary (right) table for which the linked field value has no match in the primary (left) table.

Note

In an ERD, a single line indicates a one-to-one relationship (as is the case in the xtreme.mdb ERD). A crow's foot (not displayed in the xtreme.mdb ERD) indicates a many (as in one-to-many or many-to-many relationship).

A crow's foot looks like this:

Luckily for us, report writing does not involve designing relationships. Some poor database designer has already gone through the trouble of defining these table relationships.

Most databases utilize an inner (or one-to-one) join type, which is defined by the database designer when the database is first created. An inner join type means that two tables contain referential integrity.

Now that you're armed with some knowledge on database relationships, let's put this new-found knowledge to work by creating a report that utilizes two or more tables. Crystal Reports makes linking tables a breeze through the use of the Database Expert.