We are now going to explain the conceptual data model in relation to our Airline System example.

Identifying Entities and Relationships

Firstly, you will need to identify the entities using our nouns and verbs to map the relationships:

Image of a diagram that shows the relationships between entities in our airline example

| Image: Identifying entities and relationships |

A Passenger buys a Ticket
The Ticket is for a Flight
The Flight applies to an Aircraft
The Aircraft travels to or from an Airport

Note that the directions may be described both ways. They need to be when we consider cardinality.

Identifying Cardinality

Then, you will use the nouns and verbs to map the cardinality:

Image of a diagram that shows the cardinality in our airline example

| Image: Identifying Cardinality |

A Passenger buys one or more Tickets. He isn’t a ‘passenger’ without one (one-to-many type of cardinality).
A single ticket is for a single passenger (one-to-one type of cardinality).
The flight may have many tickets applying (one-to-many type of cardinality).
The Flight applies to a single Aircraft (one-to-one type of cardinality).
An Aircraft may make many flights (one-to-many type of cardinality).
The Aircraft travels to or from a minimum of two Airports on its flight (one-to-many type of cardinality).
An Airport can deal with many Aircrafts (one-to-many type of cardinality).

Extending the conceptual model

|Image: Extending the conceptual model |

Our model assumes that the passenger buys all the tickets for their party. We also have no way of showing how the ticket was paid for.

Suppose that a group of passengers are all going to a meeting somewhere and the tickets are all bought by the travel department of a company on the company credit card. Our model breaks at this point so we need to change it. This is quite normal. A model tends to evolve rather than be absolutely right first time.

With our updated model we can record all of the details of the credit card and tickets don’t have to be bought by a passenger.

Diagram showing the extended conceptual model based on the airline example

| Image: Extended conceptual model |

Model and significant properties

The below diagram is from an open-source diagramming tool and may look slightly different from others you may see. Actually, there is no absolute set of rules for how diagrams should look; it is the content that matters.

| Image: model diagram |

It is common that these diagrams are used to plan with customers who are not database wizards. They are interested in the concepts, not the detail. However, it is common to include key properties in the diagram, usually ones that the client is familiar with. How far in terms of detail the diagram goes is custom rather than a standard.

In this diagram the entities have been expanded to include some key properties. The ‘+’ sign is optional and indicates that the value of the property is public, i.e., it is visible. It makes sense that, for example, the name and passport number of a passenger should be easily available. If a property has a ‘-’ sign it will be private to the entity and only available within the system being modelled.

However, look at the diagram and decide whether it goes far enough. Ask yourself…

Is the Agent name unique?

Flights have the same number on different days so do we need a date?

An airline surely has more than one plane of a certain type, so which one is it on this flight?

These items aren’t essential to understanding the relationship between the entities, but they often help to pick up anomalies.

The really important thing about conceptual diagrams is that they are technology independent. It is only the entities that are being mapped. How they would be linked within a database is unknown and no database specific features should be included.

These significant properties are usually used to identify occurrences of the entity.

AgentId makes agent unique.

Flight number and date now specifies the occurrence exactly.

Tail number specifies the aircraft uniquely.

Note the change in entity relationship between Aircraft and Airport. The destinations depend really on the Flight rather than the Aircraft. If the aircraft was changed would the flight’s destination change?

Image showing the same model diagram in relation to the airline example with changes in the significant properties and entity relationships

|Image: Model diagram properties |

This iterative process of review and change is common and helps ensure that the modelling process is thorough.

When you’re ready, select Next to continue.

Conceptual data models