After you define the basic columns and primary keys in your tables, you can start defining relationships between the tables. Once the relationships are defined, you can write SQL statements in UltraDev to combine data from two tables.

For example, each plane managed by Arrow Aircraft has several shareholders. You would like to establish a similar "one-to-many" relationship between each plane in your aircraft table and the shareholders in the shareholders table. This would save you from entering and tracking redundant aircraft data in the shareholders table.

In a one-to-many database relationship, a single row in one table is related to several rows in the another table. You can define this kind of relationship by including a foreign key in the table providing the many rows—in the above example, the shareholders table. A foreign key is a column containing values matching those in the primary key column of another table. Your aircraft table's primary key is called ac_serial . Therefore, including a foreign key called ac_serial in the shareholders table would define a "one-aircraft-to-many-shareholders" relationship.

With your knowledge of the client's feature request and of the company's business rules and policies, you decide to define the following one-to-many relationships in your database:

	each aircraft can have many shareholders
	each aircraft has many bookings
	each aircraft has many itineraries
	each shareholder has many itineraries
	each pilot has many bookings
	each itinerary can have many flights (or legs)

In addition, each pilot has many itineraries, and each itinerary has many pilots (at least one captain and a co-pilot). This is a "many-to-many" relationship and can be represented in the database by creating a special table consisting of two foreign keys—in this case, pilot_ID and itin_ID :

You will use this table later in UltraDev to connect the pilots table to the itineraries table.

Here are the revised table diagrams after you add the foreign keys defining these relationships: