Modeling and Designing Databases - Learning MySQL - page 128

operate on the same flight number over time; our model would need to be extended to
support this.
The system also assumes that each leg of a multihop flight has a different
FlightNumber . This means that a flight from Dubai to Christchurch via Singapore and
Melbourne would need a different FlightNumber for the Dubai-Singapore, Singapore-
Melbourne, and Melbourne-Christchurch legs.
Our database also has limited ability to describe airports. In practice, each airport has
a name, such as “Melbourne Regional Airport,” “Mehrabad,” or “Tullamarine.” The
name can be used to differentiate between airports, but most passengers will just use
the name of the town or city. This can lead to confusion, when, for example, a passenger
could book a flight to Melbourne, Florida, USA, instead of Melbourne, Victoria, Aus-
tralia. To avoid such problems, the International Air Transport Association (IATA)
assigns a unique airport code to each airport; the airport code for Melbourne, Florida,
USA is MLB, while the code for Melbourne, Victoria, Australia is MEL. If we were to
model the airport as a separate entity, we could use the IATA-assigned airport code as
the primary key. Incidentally, there's an alternative set of airport codes assigned by the
International Civil Aviation Organization (ICAO); under this code, Melbourne, Florida
is KMLB, and Melbourne, Australia is YMML.
Using the Entity Relationship Model
In this section, we'll look at the steps required to manually translate an ER model into
database tables. We'll then perform these steps using the music database as an example.
In “Using Tools for Database Design,” we'll see how we can automate this process with
the MySQL Workbench tool.
Mapping Entities and Relationships to Database Tables
When converting an ER model to a database schema, we work through each entity and
then through each relationship according to the following rules to end up with a set of
database tables.
Map the entities to database tables
For each strong entity, create a table comprising its attributes and designate the primary
key. The parts of any composite attributes are also included here.
For each weak entity, create a table comprising its attributes and including the primary
key of its owning entity. The primary key of the owning entity is known as a foreign
key here, because it's a key not of this table, but of another table. The primary key of
the table for the weak entity is the combination of the foreign key and the partial key
of the weak entity. If the relationship with the owning entity has any attributes, add
them to this table.