Defining relationships between the tables

After defining 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 Dreamweaver to combine data from two tables (see Joining 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 itinerary can have many flights (or legs)

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

The diagrams are known as E-R diagrams, or entity-relationship diagrams.