The relational model was developed by E F Codd at the IBM San Jose Research Laboratory in the late 1960s. This work being published in 1970 under the title:
"A Relational Model of Data For Large Shared Data Banks".
In this paper Codd defines the relational model and its capabilities mathematically.
Following this publication a number of research projects were undertaken in the early 1970s with the aim of implementing a relational database management system. The earliest of these projects included, System R at IBM, San Jose and Ingres at the University of California, Berkeley.
3.1.1 Relational Query Languages.
The relational database model as defined by Codd included a number of alternative relational query languages.
The Ingres project developed a query language called Quel which broadly complies with Codd's definition of a tuple relational calculus query language. Quel is still a part of the Ingres DBMS available today; although in view of current trends SQL is generally chosen.
The System R project developed a series of query languages; the first of these called SQUARE, was later developed into a more convenient form called SEQUEL. SEQUEL was itself further developed into the form of today's SQL.
In 1986 the American National Standards Institute ANSI published an SQL standard the:
"Systems Application Architecture Database Interface (SAA SQL)".
Query languages have two main components:
Data Manipulation Language (DML), and
Data Definition Language (DDL);
where the DML part of the language is used to retrieve, delete and amend instances of data in the database and where the DDL part of the language is used to describe the type of data to be held by the database.
3.2 Relational Database Terminology.
In section 2 you were introduced to some general concepts and
definitions relating to the storage and retrieval of data. In
a Relational Database all data may be viewed in the form of simple
two-dimensional tables and to distinguish this representation
of data from that of other representations we use a separate terminology
to describe the data held in a Relational Database.
There are in fact alternative terms used to describe the data
in a relational database. The first is taken from the formal definition
of the relational model and the second is based on the ability
to view data in the form of simple tables. We will adopt the latter
terminology for the remainder of this booklet.
Where "tuple" is pronounced tup^el (as in couple).
3.3 Database Tables and Relationships.
The table definitions for an "airline" database are
given in detail in Appendix A1 and summarised here.
The data held by these tables do not exist independently. Hence,
there are a number of inter-relationships which must be considered.
The relationships between the tables in the "airline"
database are as follows:
In a relational database, relationships such as these are implemented
via so called foreign keys.
Any column of a given table constitutes a foreign key if
it can contain value which refers to a single rows of another
table; ie if the given column contains the value of the primary
key of another table. In the following example Itinerary.
TicketNo is a foreign key which supports the one-to-many
relationship between "ticket" and "itinerary";
ie a ticket comprises an itinerary of one or more flight
legs.
TICKET
ITINERARY(example data of flight legs on ticket 100012)
In Appendix A1 the primary key column(s) of each table appear
underlined. (If you look closely you will notice that these columns
also appear in other tables not underlined; where they serve as
a foreign key enabling one table to be referenced from another.)
The ability to reference one table from another enables us to
implement relationships between one table and another.
For example if we wanted to find the seating capacity of the aircraft
allocated to a particular flight we would take the value of AircraftType
for that flight, eg DC9, and look for the single row in the Aircraft
table with a key value of DC9 to find the seating capacity for
that aircraft. Conversely if we wanted to find all flights which
have been allocated with a DC9 aircraft we would list from the
Flight table all rows with a value of AircraftType equal to DC9.
The following exercise is designed to help you familiarise yourself
with the tables of the "airline" database and the relationships
between them.
Desk Exercise.
Turn to the database schema and table contents given in Appendix
A1 and attempt the following questions:
1) Each airport is identified by a short code. What are the identification
codes for Heathrow, Leeds/Bradford and Brussels?
2) The airline provides flights on a number of different Routes;
what is the description for RouteNo 9.
3) On any given route the airline offers a number of different
types of fare, "Standby Single", "Eurobudget",
etc. Find and list the complete range of "Return" Fares
offered by the Airline.
4) What is the Tariff (Price) on Route 9 of an "Advanced
Purchase Return"?
5) What is the seating capacity (NoSeats) of the aircraft allocated
to FlightNo BD412?
6) What are the names (AName) of the airports (FromAirport-ToAirport)
used on FlightNo BD80?
7) List the Itinerary (FlightNo, LegNo, FlightDate) for Ticket
No 100001. What is the Fare for this ticket and how much would
the ticket cost?
8) List the names (AName) of the airports which will be visited
by passenger
R H Miller.
Equivalent Terms
Relational Databases Non Database
Relation Table File
Tuple Row Record
Attribute Column Field
Table Name Role
Airport Records all airports from which the Airline
operates.
Route Records all routes flown by the Airline.
Fares Contains details of different classes of Fare
which are available, eg Standby Single, Business
Return, APEX, etc.
Tariff Details the prices of fares as applicable to each
of the operated routes.
Aircraft Records each type of aircraft used by the Airline.
Flight Holds details of all timetabled flights, times of
departure, destination, and service.
Passenger Records all passengers who have flown with the
airline.
Ticket Records tickets currently allocated to passengers.
Itinerary Details for a given ticket, all flights and
flight-dates in order.
TICKETNO TICKETDATE PID
100001 01-07-95 26
100002 25-08-95 28
100010 09-08-95 29
100011 11-08-95 24
100012 01-09-95 21
TICKETNO FLIGHTNO LEGNO FLIGHTDATE FARETYPE
100012 BD776 1 15-09-92 SDR
100012 BD655 2 15-09-92 APR
100012 BD652 3 20-09-92 APR
100012 BD775 4 20-09-92 SDR
Return to Contents
Go on to Chapter 4 SQL Data Manipulation Language
© Copyright 1997 CHL. All rights reserved.