Creating the Database Schema

The Airlines Reservation application uses the airlines database to store information about the airlines, aircrafts, travelers, and seats. The AIRLINES.DDL file provides the SQL script required to create the airlines database.

Listing 5-1 shows the code in the AIRLINES.DDL file:

Listing 5-1: Creating the Airlines Database

 /* Create airlines database.*/ use master  go create database "airlines"  go use "airlines"  go /* Create new table "Travelers". "Travelers" : Table of Traveler "TravelerId" : TravelerId identifies Traveler "AircraftId" : Traveler rides in Aircraft "TravelerName" : TravelerName is of Traveler */ create table "Travelers" ("TravelerId" int not null, "AircraftId" char(10) not null,  "TravelerName" varchar(20) null) ON 'PRIMARY'  go alter table "Travelers" add constraint "Travelers_PK" primary key clustered ("TravelerId")  go /* Create new table "Seats". "Seats" : Table of Seat "RowNumber" : Seat is in Row  Role two (Row) of fact: Seat is in {Row}. Role two (Number) of fact: Row is identified by {Number}. "LocationCode" : Seat has Location Role two (Location) of fact: Seat has {Location}. Role two (LocationCode) of fact: Location is identified by {LocationCode}. "TravelerId" : Traveler sits on Seat "AircraftId" : Aircraft has Seat */ create table "Seats" ("RowNumber" int not null, "LocationCode" varchar(10) not null,  "TravelerId" int not null, "AircraftId" char(10) not null) ON 'PRIMARY'  go alter table "Seats" add constraint "Seats_PK" primary key clustered ("RowNumber", "LocationCode")  go /* Create new table "Aircrafts". "Aircrafts" : Table of Aircraft  "AircraftId" : AircraftId identifies Aircraft  "AirlineCode" : Airlines has Aircraft */ create table "Aircrafts" ("AircraftId" char(10) not null, "AirlineCode" char(10) not null) ON 'PRIMARY'  go alter table "Aircrafts" add constraint "Aircrafts_PK" primary key clustered ("AircraftId")  go /* Create new table "Airlines". "Airlines" : Table of Airlines  "AirlineName" : AirlineName is of Airlines */  create table "Airlines" ("AirlineCode" char(10) not null, "AirlineName" varchar(20) not null)  go alter table "Airlines" add constraint "PK_Airlines" primary key ("AirlineCode")  go /* Add the remaining keys, constraints, and indexes for the table "Seats".*/ create unique index "Seats_AK1" on "Seats" ("TravelerId") ON 'PRIMARY'  go /* Add the remaining keys, constraints, and indexes for the table "Airlines".*/ alter table "Airlines" add constraint "Airlines_UC1" unique ("AirlineName")  go /* Add foreign key constraints to table "Travelers".*/ alter table "Travelers" add constraint "Aircrafts_Travelers_FK1" foreign key ("AircraftId")  references "Aircrafts" ("AircraftId")  go /* Add foreign key constraints to table "Seats".*/ alter table "Seats" add constraint "Travelers_Seats_FK1" foreign key ("TravelerId")  references "Travelers" ("TravelerId")  go alter table "Seats" add constraint "Aircrafts_Seats_FK1" foreign key ("AircraftId")  references "Aircrafts" ("AircraftId")  go /* Add foreign key constraints to table "Aircrafts".*/ alter table "Aircrafts" add constraint "Airlines_Aircrafts_FK1" foreign key ("AirlineCode")  references "Airlines" ("AirlineCode")  go /* This is the end of the Microsoft Visual Studio SQL DDL script.*/ 

 

Download this Listing .

The code in the above listing creates the airlines database for the Airlines Reservation application. The database consists of the following tables:

• Airlines: Stores information about the airlines. It stores the airline code and airline name .

• Aircrafts: Stores information about the aircrafts that belong to a specified airline. It stores the airline code and aircraft id.

• Seats: Stores information about the seats reserved by an end user . It stores the row number, location code, aircraft id, and traveler id.

• Travelers: Stores information about the travelers. It stores the traveler id, traveler name, and aircraft id.