Databases Reference
In-Depth Information
Creating a link table in the Airline Operations
document
Now that we've described the creation of link tables and its uses, let's put it into
practice in our Airline Operations data model.
Remember that, in the previous section, we already added the
Employment
Statistics
table to the Airline Operations data model by using the
Concatenation
method. We'll now do the same, but this time using the link table method. You can
compare each of them and see for yourself their pros and cons.
Follow these steps:
1. Make sure the
T_F41SCHEDULE_P1A_EMP.qvd
file we used previously is
already in the
Airline Operations\Data Files\QVDs
folder.
2. Open the
Airline Operations.qvw
document.
3. Save the file with another name. Let's call it
Chapter 8_Link tables.qvw
.
4. Head on to the script editor and add a new tab to the right-side of the
Main Data
tab. Name the new tab as
Employment Data
.
5. Using the
File Wizard
(click on the
Table Files…
button), create the
Load
statement for the
T_F41SCHEDULE_P1A_EMP.qvd
file. Name the table as
Employment Statistics
.
6. Identify the common fields between the
Employment Statistics
table and
the already loaded
Main Data
table. The shared fields are:
°
Year
,
Period
,
Month
,
Month (#)
,
%Airline ID
,
%Unique Carrier
Code
,
Unique Carrier
,
Carrier Code
,
Carrier Name
, and
%Carrier Group ID
.
7. For all of the shared fields listed above, identify those that will form a unique
key. In this case, the fields that must be included in the unique key are:
°
Period
,
%Airline ID
,
%Unique Carrier Code
, and
%Carrier Group ID
.
8. The fields we will leave out of the key will be:
°
Year
,
Month
,
Month (#)
,
Unique Carrier
,
Carrier Code
, and
Carrier Name
.