CSCI A114 / INFO I111 Lecture Notes Seven First semester 2000-2001

A Database System

Let us consider an enterprise, such as an airline, that has a large amount of data kept for long periods of time in a computer. This data might include information about

• passengers
• flights
• aircraft, and
• personnel

Typical RELATIONSHIPS that might be represented include:

• bookings (which passengers will have seats on which flights?)
• flight crews (who is to be the pilot, copilot, etc., on what flights?), and
• service records (when and by whom was each aircraft last serviced?)

The software that allows one or many persons to use and/or modify this data is called a DATABASE MANAGEMENT SYSTEM (DBMS).

Roles of the DBMS

• allow the user to deal with data in abstract terms, rather than as the computer stores data
• security, integrity, synchronization (concurrency), crash protection and recovery, etc.

Levels of Abstraction in a DBMS

• PHYSICAL DATABASE - only this one exists

  ---

• CONCEPTUAL DATABASE - abstract representation of the physical database
• VIEWS - abstractions of portions of the conceptual database

Here's a diagram of this:

Schemes and Instances

When a database is designed, we are interested in plans for the database.

When the database is used, we are interested (and working with) the actual data present in the database.

Definitions

The current contents of a database we term an INSTANCE of the database.

The plans regarding the organization of a database are called a database SCHEME. Plans consist of an enumeration of the

• type of ENTITIES that the database deals with,

• the RELATIONSHIPS among these types of entities.

Observation 2: The term scheme is used to refer to plans so we talk of a

• conceptual scheme (as the plan for the conceptual database), and of a

• physical scheme (physical database plan - the user has nothing to do with it).

The conceptual scheme is an abstraction of the real world pertinent to an enterprise. It is roughly at the level of passengers, flights, and so on which we have discussed in connection withm the enterprise of an airline.

A DBMS provides a DATA DEFINITION LANGUAGE to specify the conceptual scheme and, most likely, some of the details regarding the implementation of the conceptual scheme by the physical scheme.

The data definition language is a high-level language, enabling one to describe the conceptual scheme in terms of a DATA MODEL.

The choice of a data model is a difficult one, since it must be rich enough in structure to define significant aspects of the real world, yet it must be possible to determine fairly automatically an efficient implementation of the conceptual scheme by a physical scheme.

Our choice of a data model is the relational data model.

The Relational Data Model

This model is based on the set theoretic notion of a relation.

A relation is a set of k-tuples, for some fixed k.

For example: bookings could be represented by a set BOOKINGS of triples:

(FLIGHT_NO, DATE, PASSENGER)

BOOKINGS = { (n, d, p) | passenger p has a reservation on flight n on date d}

Let us now undertake the design of a (somewhat) complete database for an airline using the entity relationship model.

First we list the entity sets and their attributes.

Then we describe the relationships.

1. The Entities

Entity set PASSENGERS with attributes:

NAME       CHAR(30)
ADDRESS    CHAR(30)
PHONE      INT(10)

Entity set FLIGHTS with attributes:

NUMBER    INT(3)
SOURCE    CHAR(3)
DEST      CHAR(3)
DEP_TIME  INT(4)
ARR_TIME  INT(4)

The NUMBER of a flight should uniquely identify a flight.

The SOURCE is an airport. All commercial airports have three character letter codes (e.g., ORD, IND, JFK etc.) DEST is also an airport. Departure time and arrival time are in military format.

Entity set DEPARTURES with attribute:

DATE  INT(3)

Entity set PLANES with attributes:

MANUFACTURER  CHAR(10)  
MODEL_NO      CHAR(10) 

Example: Boeing (Manufacturer), 747 (Model_No).

Entity set AIRCRAFT with attribute:

SERIAL_NO  INT(5)

Entity set PERSONNEL with attributes:

EMP_NO   INT(6)
NAME     CHAR(30)
ADDRESS  CHAR(30)
SALARY   INT(6)

Entity set PILOTS (subset of EMPLOYEES) with no additional attributes.

Relationships:

one	PILOTS	[1] IS_A	PERSONNEL	one
many	PILOTS	[2] CAN_FLY	PLANES	many
many	PASSENGERS	[3] BOOKED_ON	DEPARTURES	many
many	DEPARTURES	[4] INSTANCE_OF	FLIGHTS	one
many	PERSONNEL	[5] ASSIGNED_TO	DEPARTURES	many
many	AIRCRAFT	[6] TYPE	FLIGHTS	one

Here's the entity-relationship diagram that describes our model:

Note that in this diagram the relationships appear as columns in the tables and the design is complete.