Local relations - creating database relationships
|
| Previous Top Next |
Local relations - creating database relationships |
MapForce allows you to extract related database data, even if no such relationships explicitly exist in the source database. You can define the primary/foreign key relations between tables, views and SELECT statements in a component, without affecting the underlying database relationships in any way.
These on-the-fly relationships are called Local Relations in MapForce.
| • | any database fields can be used as primary or foreign keys |
| • | new relations can be created that do not currently exist in the database |
The MS Access altova-no-relation.mdb database used in this example, is a simplified version of the Altova.mdb database supplied with MapForce. The Person and Address tables, as well as all remaining table relationships have been removed in MS Access.
The aim of this example is to display the offices of Altova and show the departments in each.
None of the tables visible in the altova-no-relation tree have any child tables, all tables are on the same "root" level. The content of each table is limited to the fields it contains. We can however, use MapForce to extract related database data, even though relationships have not been explicitly defined.
To create local relations:
Local relations can be defined while inserting a database, or by right clicking an existing database component and selecting the Add/Remove Tables from the context menu.
| 1. | Insert the altova-no-relation database. |
| 2. | In the connection wizard click Microsoft Access, then click Next. |
| 3. | Click Browse, select Altova.mdb then click the User Tables checkbox to select all three tables. |
| 4. | Click the Add/Edit Table Relations button in the icon bar. |
This opens the Add/Edit Table Relations dialog box.
| 5. | Click the Add Relation button. |
The two combo boxes allow you to select the tables you want to create relations for. The left combo box is the Primary/Unique Key Table, the right one, the Foreign Key Table.
| 6. | Click the left combo box and select the Altova table. |
| 7. | Click the "select column" combo box below it, and select the Primary Key entry. |
| 8. | Select the Office table and ForeignKey column for the Foreign Key table. |
| 9. | Click the OK button to complete the local relation defintion, then click the Insert button to insert the database into the mapping area. |
Clicking the expand icon of the Altova table shows that there is a relationship between the Altova and Office tables. The Office table is shown as a related table below the Altova table with its own expand icon.
Use the same method to create a relationship between the Office and Department tables.
| 10. | Right click the database component and select Add/Remove Tables from the context menu, then click the Add/Edit Table Relations button in the icon bar. |
When creating the mapping it is important to remember that to preserve relationships between tables, connectors below one of the "root" tables must be used, i.e. Altova in this case.
Having defined the mapping as shown above, click the Output tab, to preview the result immediately. Database data cannot be previewed if the target language is XSLT, a message will appear and the database component will be greyed out.
The mapping result shows:
| • | "for each Office element, output the office name and then all departments in that office" |
|