Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)
|
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.
| 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. |
| | 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. |
| 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.
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.
| 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.
|