VIII

Flight Reservation Systems

Members
1.
2.

TEACHER - TUTOR:

GP
VIII
1. INTRODUCTION

The Internet is information in abundance. We can do anything,

any part of the world, with just a few clicks of the mouse and a card
of credit, regardless of the time. We, as users, have the power to do
price and quality comparisons of products, making reservations for
flight in a matter of minutes. When the Internet is coupled with strategies
in business, the results are positive.
There are several ways to do business online:

Business to Consumer (B2C):

This category includes all those websites that sell
Any type of product to the general public are quick solutions.

2)DEVELOPMENT OF THE TASK

PROJECT:

FLIGHT RESERVATION SYSTEM

2.1. PHASE I: SOFTWARE DEVELOPMENT PLAN

The proposal and scope of the system to be developed are presented.Attached annexof
in such a way that it is approved to start the development.

2.2. PHASE II and PHASE III: REQUIREMENTS

2.2.1. GENERAL DESCRIPTION OF THE SYSTEM

We will use a flight reservation system with access as an example.

via Internet. This is a system that allows the client user to make inquiries and
flight reservation (up to a certain time), in addition to being able to purchase
the airline tickets remotely, without the need to resort to an agent
of human travel. It is desired that the reservation system be accessible.
through the Internet (World Wide Web). As a basis for these systems
Currently, there are multiple reservation databases.
flights that travel agencies use to provide services to customers,
for example about: apollo, worldpan, parse, amadeus, sahara, sita-sahara,
panorama, system-one, gemini, galileo, acxess, etc., many of these databases
 data and corresponding systems are the basis for the systems of
online flight reservations, such as: travelocity,
expedia, etc.

The scope of the system can be summarized as follows:

1. Points of sale: These points of sale will provide purchasing services and
ticket reservations, and member management. There will be between 5 and 20 offices of the
Airline in at least 200 cities around the world.

2. Online services: From a Web application, services will be provided

purchase and reservation of tickets.

3. Management at airports: In each airport, there will be between 10 and 50

computers located at the check-in points and at the gates
boarding that will be used by airline employees.

The description of the problem for our flight reservation system

is the following:

The flight reservation system is a system that allows the user

make inquiries and reservations for flights, in addition to being able to purchase tickets
air tickets remotely, without the need to resort to a travel agent
humans. It is desired that the reservation system be accessible through
from the Internet.

The system presents a welcome message on its main page.

describing the services offered along with the option to register for
first time, or if already registered, to be able to use the system of
flight reservations. This access is provided through the insertion of a
Previously specified login and a previously chosen password
what must be validated. Once the user is registered, and after having
validated the user's registration and password, the options can be selected
following activities:

Consulta de vuelos, Reserva de Vuelos, Pago de boletos.

Flight inquiries can be made in three different ways:

Flight schedules

The consultation according to schedule shows the timetables of the different airlines.
providing service between two cities.
The consultation according to the rates shows the different flights between two cities.
giving priority to its cost. The flight status is mainly used for
check the status of a flight including information on whether there are any
available seats, and in the case of a flight for the same day, or if it is
on the hour. Preferences can be included in searches, such as date of
desired schedule
only direct flights.

The flight reservation allows the customer to make a reservation for a flight.
particular, specifying the date and time, under a set rate. It is
possible to book an itinerary made up of multiple flights, for one or more
passengers, as well as being able to reserve seats. The payment allows the customer, given
a previous flight reservation and a valid credit card, acquire the
air tickets. The tickets will be subsequently sent to the customer, or
they will be ready to be picked up at the airport counter prior to the
departure of the first flight.

It is necessary to be previously registered with a card number of

credit valid to be able to make ticket purchases, or otherwise
provide it at the time of purchase.

In addition to flight services, the user will be able at any time

access, modify or cancel your own registration, all of this after having
The user has been validated in the system.

2.2.2. FUNCTIONAL AND NON-FUNCTIONAL REQUIREMENTS

2.2.2.1 FUNCTIONAL REQUIREMENTS

Points of Sale
Functional Requirement 1 User Registration (Sign Up).
The user's data will be registered: ID number, code, first names, last names, number
from Card, email and its place of origin such as district,
province, department.

Functional Requirement 2 Modification of user personal data.

The user's data is modified: ID number, code, first names, last names, number
from Card, email and its place of origin such as district,
province, department.

Functional Requirement 3 Register Card.

 The card number is recorded as long as the payment is credit for
Hello, you can access via PayPal or Visa.

Functional Requirement 4 Flight Reservations (cash sale and credit)

User will be able to make a flight reservation at the point of sale.
this can be cash or credit.

Functional Requirement 5 Purchase and redemption of miles or kilometers.

The customer will be able to make the purchase, the purchase will be recorded with the
following data: date, total amount, total miles according to the flight that
will be added to the accumulated total.

Functional Requirement 6: Mileage report for users.

At any time, the client user can make an inquiry about their
miles or kilometers of accumulated travel.

Functional Requirement 7 Payment Reservation.

The client will make the payment, resulting in a payment record with the following
code, date, time, total amount.

Functional Requirement 8 Consult Information.

The client user will be able to search for relevant information prior to access.
and verification of your data.

Online Service
Functional Requirement 1 Validate User
Users will be able to identify themselves in the system by registering their name.
user and their password login and password and the user will become a customer.

Functional Requirement 2 Flight Inquiry

The customer will be able to check their itineraries, rates, seat availability, etc.
Prior identification in the system.

Functional Requirement 3 Reserve and purchase ticket credit

User will be able to make a flight reservation or purchase it, this can be to the
Credit or installment, the credit details must be confirmed with your card.
of credit.
Functional Requirement 4 Purchase and redemption of miles (purchase ticket with miles).

The customer will be able to purchase their ticket, the purchase will be recorded with
los siguientes datos: fecha, importe total, el total de millas según viaje se
will be added to the total accumulated miles or kilometers.

Functional Requirement 5 User Registration

The user's data will be registered: ID number, code, first names, last names, phone number.
Card, email, and its place of origin such as district, province,
department.

Functional Requirement 6 User Data Modification.

The user's data is modified: ID number, code, names, surnames, number of

Card, email and its place of origin such as district, province,
department.

Functional Requirement 7: Consult Information.

The client user will be able to search for relevant information for prior access and
verification of your data at any time.

Airport Management

Functional Requirement 1 Validate User

Users will be able to identify themselves in the system by registering their name.
user and their password login and password and the user will become a customer.

Functional Requirement 2: Passenger reservation inquiry.

Customer user can access the reservation through an inquiry

passengers

Functional Requirement 3Passenger Baggage Check-in

The passenger's luggage inspection is verified in the system using a
data record

Functional Requirement 4: Inquiry about the status and location of baggage.

The client is registered, their trip is confirmed, and they passed through check-in okay.
You can check the location of your luggage as well as the status of your trip.
2.2.2.2 NON-FUNCTIONAL REQUIREMENTS

Non-Functional Requirements (Specific)

Point of Sale
Non-Functional Requirement 1Travel agents must identify themselves in the
system based on a username and password.

Non-Functional Requirement 2 In case of connection failures with the system

central, the system must allow normal operation, synchronizing
subsequently the information with the central system.

Non-Functional Requirement 3: The system must communicate with the system.

financial authorizer of credit cards for credit sales. In
in case it is not available, the system must
request manual authorization.

Non-Functional Requirement 4: The information about flight reservations.

must be available to other branches and online services in a
maximum of 30 minutes.

Non-Functional Requirement 5 The information of the partners must be

available to other branches and online services within a maximum of 4
hours.

Non-Functional Requirement 6 The replication connections must

to operate on ADSL lines of 512 MBits.

Non-Functional Requirement 7: The system must run on PCs with

Intel Pentium 4 processors, with 512 Mb of RAM and 100 Gb of
disk and Windows XP or higher operating system.

Online Service
The system must be able to authenticate users.

Non-Functional Requirement 2 The system must provide encryption for

communications.

Non-Functional Requirement 3 The system must be available 24/7

hours of the day, the 7 days of the week.

Non-Functional Requirement 4: The system must have an interface that

allow users to quickly become familiar with the system
online.
 Non-Functional Requirement 5 The website must be highly efficient and
visually appealing to users.

Airport Management
Non-Functional Requirement 1: The system must run on PC with
Pentium D processors at 3.0GHz, with 2GB of RAM and 200GB
of disk and will use a Linux operating system.

Non-Functional Requirement 2: The system must be 100% available.

Non-Functional Requirement 3 The system must support mechanisms that

allow fundamentally modifying its graphical interface and logic
corresponding.

Non-Functional Requirements (General)

Response Time:
When a user registers, the data and queries will appear in
a second (boundary restriction).
interface metaphor

Windows oriented towards the metaphor of a form and boxes of

dialogue. Maximizing easy navigation with keyboard and mouse
(detail).

fault tolerance

You must record customer payments made by credit card still.

when power or equipment failures occur (restriction of
border).

Performance:

The system must be able to respond to access of

all users and processes with acceptable response time
and uniform, to the extent of technological possibilities, in periods
high, medium, and low demand for system use.

Scalability:
The system must be able to allow in the future the
development of new functionalities, modify or delete
 functionalities after its construction and commissioning
initial.

Ease of use and data entry:

The entry of information into the system must be designed with
transactions that allow the input of data in a way
partial; that the size of the information registration pages
they are adjusted according to the stability of the network.
The system must present error messages that allow for
User identify the type of error and communicate with it.
system administrator

Ease of testing.
The system must have facilities for the identification of the
localization of errors during the testing phase.

Flexibility:
The system must be designed and built with the utmost
levels of flexibility regarding the parameterization of types
data, so that the system management is
carried out by a functional system administrator.

Installation:
The system must be easy to install on all platforms.
hardware and base software, defined by the Systems area
company, as well as allowing its installation in different sizes
of configurations.

Maintenance:
The entire system must be fully documented, each
one of the software components that are part of the
proposed solution must be properly documented
both in the source code and in the manuals of
administration and user.

Security:
 The implemented access control must allow assigning
the profiles for each of the identified roles.

The system must be capable of rejecting access.

or unauthorized modifications to the information and
provide the services required by legitimate users
of the system
Validation of information:
The system must automatically validate the information
contained in the admission forms. In the process of
validation of the information, aspects must be taken into account
such as mandatory fields, character length
allowed by field, data type management, etc.

2.2.3. USE CASES

miles_kms exchange mile purchase_kims

(from USE CASES) (from USE CASES)

luggage inquiry reservation inquiry

(from USE CASES) (from USE CASES)

reserved tickets ticket purchase

(from USE CASES) (from USE CASES)

check in luggage flight inquiry

(from USE CASES) (from USE CASES)

flight list miles_kms of a client

(from USE CASES) (from USE CASES)
 registration of Client discharge modify Client
(from UseCases) (from UseCases)

System access Validate Client

(from Use Cases) (from Use Cases)

2.2.3.1 Use Case Specification

ECU - Miles Exchange

ECU - Purchase of Miles kilometers
ECU - Luggage Inquiry
ECU - Reservation Inquiry
ECU- Ticket reservation
ECU - Ticket purchase
ECU- Check-in baggage
ECU - Flight Inquiry
ECU- Flight list
ECU - Client kilometers in miles
ECU - Client Registration
ECU - Modify client
ECU - System Access
ECU- Validate Client

3 Actor Identification
 Airline Executive credit Employee
(from Actors)
(from Actors)
(from Actors)

Operator Passenger Client

(from Actors) (from Actors) (from Actors)

4 Use Case Diagrams

General use case diagram

Access to the system check-inluggage Airline

(from Use Cases) (from Use Cases) (from Actors)

Operator
(from Actors)

Reservation Maintenance Passenger

(from Use Cases) reservation inquiries (from Actores)
list of flights (from UseCases)
(from Use Cases) baggage inquiry
System maintenance
(from Use Cases)
Use Cases

Client ticket reservation

(fromActors) (fromUseCases)
Register Card
(from Use Cases)
flight inquiry
(from Use Cases)

purchase of miles_kims Customer maintenance

ticket purchase
(from Use Cases) (from Use Cases)
(from Use Cases)
Employee
(fromActors)
<<extend>>

miles_kmsexchange
Credit Executive (from UseCases)

(from Actors) credit card

(from Use Cases)

3. PHASE IV: Analysis Model

 Entities:

Reservation Card Registration Miles Client

Seat Rate

Passenger Frequent traveler Flights Checked luggage

Airline

Clients
Schedule Airport User registration Ticket purchases

Department Province District

Border:

Login Type of Service Customer Registration General inquiries Flight Inquiry

Schedule inquiry check rates Schedule inquiry

Ticket reservation Customer Modification

Luggage Control Baggage Inquiry

Ticket purchase Mile Exchange

Manager:
 Ticket reservation management Client registration management System access Consultation Gesture

Service type manager RegisterCard Reservation Maintenance

Rates

Luggage Control Manager Modify Client

Buy ticket Miles redemption manager

Domain Model of Analysis

Entity Border
(from Analysis Model) (from Analysis Model)

Manager
(from Analysis Model)

Analysis Architecture

Registration of Validate Client Card of Reservation of

Client Credit Passages

Flight List Modify Maintenance Maintenance

Client of the System of Reserve

Consultation of Consultation of
Purchase of Consultation of
Flights Reservations Luggage
passages

Check In Exchange of Access to

Luggage Miles_Kms client
 I

Implementation Access to the Client

System access RA_System Access

(from Use Cases)

Collaboration Diagram Customer Access

User,
password

Data Entry

4: Record data User Registration

5: correct registration
Client Login
Send information
check user

Message from
Acc...

System access

II

Execution of Miles_Kms Exchange

miles exchange_kms RA_Miles Exchange_kms

(from Use Cases)
 Collaboration Diagram for Mile-Km Exchange
Select exchange product product_code
General data on the exchange
Product_redeem_code

1:

Mile Exchange
Client
Miles Client
3: Data Sending
4: Data confirmation
5: Sending of redeemed record

6: Insufficient Miles

Miles redemption manager

III

Baggage Check-In

check in luggage RA Check In Baggage

(from Use Cases)

Collaboration Diagram Check in Luggage

Number of luggage,
Luggage Weight
Passenger ticket check-in number

1: register information

5: Shipping Document Baggage Control 6: Airline Data

Checked luggage
Operator

4: Recorded data
2: sending of information

3: Surcharge for excess weight Airline

Luggage Control

IV
 Ticket Purchase

Purchase of tickets

purchase of tickets RA Ticket purchase

(from Use Cases)

Collaboration Diagram for Ticket Purchase

Basic Flow of Ticket Purchase

Enter Data

6: Obtain Passage
Client Ticket purchase

Send Information
5: Correct validation

3: Verify data Ticket purchases

4: Register data

Buy ticket

Baggage Inquiry Processing

luggage inquiry RA Luggage Inquiry

(from Use Cases)
 Collaboration Diagram Baggage Inquiry
4: Check Ticket

Enter information

Ticket Number 3: Ticket entry error

2: Consultation data

6: Luggage location
Passenger Baggage Inquiry Consultation Gesture

5: Data sent

Checked baggage

VI

Reservation Inquiry

reservation inquiry Reservation Query Execution

(from Use Cases)

Collaboration Diagram of Reservation Inquiry

3: Check reservations
Register:
City, origin and destination
Number of Adults
cabin
Passenger

1: Access the Reservations interface

Ticket reservation management
Data transmission

5: Reservations Made

4: Reservation Inquiry

Reserve a ticket

Reservation

VII
 Flight Inquiry

flight query Flight Inquiry

from Use Cases

Collaboration Diagram Flight Inquiry

validates the client, type of inquiry:

2: Parameters Itineraries, flights, city, origin...
Flights

Data entry

5: Sending information
Flights
7: Data on Screen
Client
Flight Inquiry
3: Information shipment
4: query ok

6: Resolved inquiry

Consultation Gesture

VIII

Flight List Realization

list of flights Flight List Implementation

(from Use Cases)
 Collaboration Diagram Flight List
enter user and
password
Schedules

Enter information

Client 6: Return query Flights

Flight Inquiry 3: data Ok

Send information

5: Verify customer

Returns queried data

Consultation Gesture

IX

Maintenance of Reservation

Reservation Maintenance Reserve Maintenance

(from Use Cases)

Collaboration Diagram Reservation Maintenance

user Update dtos:
password Flight
rate, orig...

Enter information

5: Exit
Client Ticket reservation 3: modified data Reservation

Send Data

4: Registration ok

Reservation Maintenance

X
 System Maintenance Implementation

System maintenance RA System Maintenance

(from Use Cases)

XI

Client Modification

modify Client RA Modify Client

(from Use Cases)

Collaboration Diagram Modify Client

User,
password
Modify: name,
nro_asiento_origen
destination.
enter data Customer Modification

Sending information
5: registered and updated data
3: Verify data
Passenger Passenger
4: Updated data

Manager Modify Client

XII

Customer Registration Implementation

client registration RA Customer Registration

(from Use Cases)

Collaboration Diagram Register New Client

User,
password

Access to the system

Customer Registration
3: new data
Clients
genra customer number

Client Send information

4: complete record

Customer registration management

XII
 BOOK TICKETS

reserve tickets RA_reserve tickets

(from Use Cases)

Collaboration Diagram Reservation of tickets (Basic Flow)

Enter data

Consultation code
Accepts parameters

Reservation
Customer Ticket reservation

3: Visualize changes

5: Update Record

4: View record

Reserved management
passages

XIII

Credit Card Issuance

credit card RA Credit Card

(from Use Cases)

XIV

Validate Customer

Validate Client Validate client

(from Use Cases)

Collaboration Diagram Validate Client (Basic Flow)

 VALIDATE CLIENT

1: Register data
System access
Date, time 4: Request access parameters
Client

Returns query 5: Consultation Reg.

3: Visualize data

Customer Registration 6: Update

Passenger

4. PHASE V: Logical Model of the Flight Reservation System

4.1 Only entities

 Logical Model of the Flight Reservation System
Has They arrive
1
1 0..*
Clients * Airline
Checked baggage 1
(from Entity) (from Entity) +Exit from Department
1 is assigned (from Entity)
0..* 1 It has (from Entity)
+Has
1
0..* 0..* 0..*
0..* +Has
1
1 +Belongs A 1..*
Reservation
(from Entity)
Card Registration Flights
Has
(from Entity) (from Entity)
* Province
+It is used in
Has
* (from Entity)
1
It is used
Seat
(from Entity)
1..*
+Belongs to

Ticket purchases
(from Entity)
Rate District
(from Entity) (from Entity)

4.2 Entities and Attributes

 Logical Model of the Flight Reservation System
1 Has

0..*
They arriveDepartment
Clients Checked luggage Airline 1
1..*
(from Entity) (from Entity) (from Entity)
(from Entity)
Name Location 0..* Name Name of dep
iDni assigned to State Telephone
Address Reservation Customer Registration()
address 1
Telephone (from Entity) Register luggage() email Modify Client()
email +Exit from Delete Customer()
Fec_reserva Modify luggage() 1
state Remove luggage() RegisterAirline()
Register customer() payment ModifyAirline()
Modify client() payment date DeleteAirline() +Petenece A 1..*
Delete client() number_of_seats Has 1 +Has
record2() Cancel reservation 0..* 0..*
1 0..*
Has 1
Province
1
0..* +Has (from Entity)

1..* +Has Name Prov

Flights

* (from Entity) Register Province()

Card Registration +It is used in
Flight number Modify Province()
(from Entity) Seat Salt Time Remove Province()
1
Card Type (from Entity)
Time Arrives
It is used
Expiration date Duration
Seat_state
Price 1..*
Rate Belongs To
Register Card() Ticket purchases City_origin
Register status()
Modify Card() (from Entity) City_Destination
(from Entity) Modify state()
Delete card() Description frequency
Passenger name Check status()
Start_Date class flight
shipping_address Available_seats District
Fec_Venc
application_season (from Entity)
Register Purchase() Validation RegisterFlight() Name Dist
Modify Purchase() National_children ModifyFlight()
Elima Purchase() International_children Update Flight() Register District()
Value FlightQuery() Modify District()
Remove district()
Register Rate()
Modify rate()
Delete rate()
Consult Rates()

5. PHASE VI: Physical Model of the Flight Reservation System

 SR_Airline SR_Flights
Seat SR_Department
VARCHAR(30) Flight Number
CHAR(1) Name dep : VARCHAR(20)
CHAR(10) Salt Time : CHAR(8)
INTEGER INTEGER
address Arrival Time: CHAR(8)
INTEGER VARCHAR(40) Duration : CHAR(3)
SMALLINT Non-Identifying PK_SR_Department45()
INTEGER Non-Identifying FLOAT(10) 1
INTEGER 1
PK_SR_Seat36() 1 0..*
0..* PK_SR_Airline41()
Identifying INTEGER
FK_SR_Seat49() SMALLINT Identifying
FK_SR_Seat54() CHAR(3) 0..*
<<Index>> TC_SR_Seat109() 10..* Available_seats 1
<<Index>> TC_SR_Seat108() INTEGER
INTEGER
INTEGER
1 INTEGER SR_Province
Identifying
INTEGER NameProv:VARCHAR(30)
SR_Registered Luggage INTEGER
1 0..*
Location PK_SR_Flights40() INTEGER
0..* 1 Non-Identifying
State : CHAR(1) FK_SR_Flights47()
INTEGER FK_SR_Flights57() PK_SR_Province46()
INTEGER FK_SR_Flights58() FK_SR_Province56()
SR_Reserve
INTEGER FK_SR_Flights50() Index TC_SR_Province120()
DATE
<<Index>> TC_SR_Flights115()
CHAR(1) 1
Registered luggage 43() Index TC_SR_Vuelos116()
FLOAT(12) Identifying
Registered luggage53() Index TC_SR_Flights113()
DATE Identifying
Registered luggage48() <<Index>> TC_SR_Flights114() 1
SMALLINT
Index TC_SR_Registered Luggage 118()
DATE
Registered Luggage Index TC_SR_119()
INTEGER 0..* SR_District
INTEGER 0..*
Name Dist : VARCHAR(30)
INTEGER 0..* Non-Identifying INTEGER
SMALLINT
INTEGER
1 SR_Customers INTEGER
PK_SR_Reserva38()
VARCHAR(20)
FK_SR_Reserva60()
Days : CHAR(11) PK_SR_District47()
FK_SR_Reserva61()
1 Address : VARCHAR(70) FK_SR_District59()
Phone <<Index>> TC_SR_District121()
0..* VARCHAR(40)
Identifying INTEGER
1 0..1
SR_Purchaseoftickets PK_SR_Customers44()
1
PassengerName:VARCHAR(20)
<<Non-Identifying>> SR_Tariff
shipping_address
INTEGER 0..* VARCHAR(30)
INTEGER 0..* DATE
SMALLINT <<Non-Identifying>> SR_Card Registration DATE
INTEGER CHAR(15)
Card Type: CHAR(20)
SMALLINT Validity: CHAR(3)
Expiration date: DATE
SMALLINT
0..* INTEGER
Ticket purchase 1 SMALLINT
INTEGER
FK_SR_Purchase of tickets45() Value: FLOAT(10)
FK_SR_Ticket purchases46() INTEGER
PK_SR_Card Registration39()
Index TC_SR_Purchase of tickets94() FK_SR_Card Registration52()
Index TC_SR_Ticket Purchases117() PK_SR_Tariff37()
<<Index>> TC_SR_Card Registration 111()
FK_SR_Ticket Purchases62()

6. PHASE VII: SQL Script of the Flight Reservation System (Migration)

CREATE TABLE SR_Card_Registry (
Card_Type CHAR (20) NOT NULL
Expiration_date DATE NOT NULL,
INTEGER NOT NULL
Client_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Card_Record39 PRIMARY KEY (ID_Card_Record)
);
CREATE INDEX TC_SR_Card_Registration111 ON SR_Card_Registration (Client_ID);
CREATE TABLE SR_Flights (
Flight_number SMALLINT NOT NULL,
Hour_of_Sal CHAR ( 8 ) NOT NULL,
Hour_Arrives CHAR (8) NOT NULL,
Duration CHAR ( 3 ) NOT NULL
Price FLOAT (10) NOT NULL,
City_origin INTEGER NOT NULL
Destination_City INTEGER NOT NULL,
frequency SMALLINT NOT NULL,
flight_class CHAR ( 3 ) NOT NULL
Available_Seats SMALLINT NOT NULL
Flight_ID INTEGER NOT NULL
INTEGER
AIRLINE_ID INTEGER NOT NULL
Department_Sale_ID INTEGER NOT NULL
Department_Arrival_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Flights40 PRIMARY KEY (ID_Flights)
);
CREATE INDEX TC_SR_Vuelos115 ON SR_Vuelos (ID_Departamento_Llega);
CREATE INDEX TC_SR_Flights116 ON SR_Flights (Airline_ID);
CREATE INDEX TC_SR_Vuelos113 ON SR_Vuelos (ID_Tarifa);
CREATE INDEX TC_SR_Vuelos114 ON SR_Vuelos (ID_Departamento_Sale);
CREATE TABLE SR_Customers (
Name VARCHAR ( 20 ) NOT NULL
DAY CHAR ( 11 ) NOT NULL
Address VARCHAR(70) NOT NULL
Phone CHAR (10) NOT NULL,
email VARCHAR ( 40 ) NOT NULL,
Customer_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Customers44 PRIMARY KEY (Customer_ID)
);
CREATE TABLE SR_Registered_Luggage (
Location VARCHAR (20) NOT NULL,
State CHAR (1) NOT NULL,
Registered_Luggage_ID INTEGER NOT NULL
INTEGER NOT NULL
Customer_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Registered_Luggage43 PRIMARY KEY (ID_Registered_Luggage)
);
CREATE INDEX TC_SR_Registered_Luggage118 ON SR_Registered_Luggage (Client_ID);
CREATE INDEX TC_SR_Registered_Luggage119 ON SR_Registered_Luggage (Flight_ID);
CREATE TABLE SR_Airline (
Name VARCHAR (30) NOT NULL,
Phone CHAR (10) NOT NULL,
address VARCHAR(70) NOT NULL
email VARCHAR ( 40 ) NOT NULL,
ID_Airline INTEGER NOT NULL
CONSTRAINT PK_SR_Airline41 PRIMARY KEY (ID_Airline)
);
CREATE TABLE SR_Ticket_Purchases (
Passenger_Name VARCHAR (20) NOT NULL
shipping_address VARCHAR ( 20 ) NOT NULL,
INTEGER NOT NULL
FLIGHT_ID INTEGER NOT NULL
Seat_ID SMALLINT NOT NULL
Card_Registration_ID INTEGER NOT NULL
SMALLINT NOT NULL
CONSTRAINT PK_SR_Ticket_Purchases42 PRIMARY KEY (Flight_ID, Seat_ID, Client_ID,
Ticket Purchase ID
);
CREATE INDEX TC_SR_Purchase_of_Tickets117 ON SR_Purchase_of_Tickets (Flight_ID );
CREATE INDEX TC_SR_Passenger_Purchase94 ON SR_Passenger_Purchases (Client_ID);
CREATE TABLE SR_Tarifa (
Description VARCHAR (30) NOT NULL
Start_Date NOT NULL
Fec_Venc DATE NOT NULL,
Season_app CHAR ( 15 ) NOT NULL,
Validity CHAR ( 3 ) NOT NULL,
National_children SMALLINT NOT NULL,
International_children SMALLINT NOT NULL,
Value FLOAT (10) NOT NULL,
ID_Tariff INTEGER NOT NULL
CONSTRAINT PK_SR_Tarifa37 PRIMARY KEY (ID_Tarifa)
);
CREATE TABLE SR_Province (
Provider_Name VARCHAR(30) NOT NULL
Department_ID INTEGER NOT NULL
Province_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Provincia46 PRIMARY KEY (ID_Department, ID_Province)
);
CREATE INDEX TC_SR_Provincia120 ON SR_Provincia (ID_Departamento);
CREATE TABLE SR_Reserva (
Reservation date NOT NULL,
state CHAR ( 1 ) NOT NULL,
payment FLOAT ( 12 ) NOT NULL,
payment_date DATE NOT NULL,
SMALLINT NOT NULL
Fec_cancel_reserv DATE NOT NULL,
INTEGER NOT NULL
Customer_ID INTEGER NOT NULL
INTEGER NOT NULL
Seat_ID SMALLINT NOT NULL,
CONSTRAINT PK_SR_Reserva38 PRIMARY KEY (Flight_ID, Seat_ID, Reservation_ID)
);
CREATE TABLE SR_Seat (
Seat_status CHAR (1) NOT NULL,
Flight_ID INTEGER NOT NULL
Customer_ID INTEGER
ID_Seat SMALLINT NOT NULL,
CONSTRAINT PK_SR_Asiento36 PRIMARY KEY (Flight_ID, Seat_ID)
);
CREATE INDEX TC_SR_Asiento109 ON SR_Asiento (ID_Clientes);
CREATE INDEX TC_SR_Asiento108 ON SR_Asiento (ID_Flights);
CREATE TABLE SR_Department (
Name_dep VARCHAR ( 20 ) NOT NULL,
Department_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Departamento45 PRIMARY KEY (ID_Departamento)
);
CREATE TABLE SR_District (
DIST_Name VARCHAR (30) NOT NULL
Department_ID INTEGER NOT NULL,
Province_ID INTEGER NOT NULL
DISTRICT_ID INTEGER NOT NULL
CONSTRAINT PK_SR_Distrito47 PRIMARY KEY (Department_ID, Province_ID, District_ID)
);
CREATE INDEX TC_SR_Distrito121 ON SR_Distrito (ID_Departamento);
ALTER TABLE SR_Ticket_Purchases ADD CONSTRAINT FK_SR_Ticket_Purchases62 FOREIGN
KEY (Card_Registration_ID) REFERENCES Card_Registration (Card_Registration_ID) ON
DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Ticket_Purchases ADD CONSTRAINT FK_SR_Ticket_Purchases46 FOREIGN
KEY (Customer_ID) REFERENCES Customer_Records (Customer_ID) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE SR_Ticket_Purchases ADD CONSTRAINT FK_SR_Ticket_Purchases45 FOREIGN
KEY (Flight_ID, Seat_ID) REFERENCES Seat (Flight_ID, Seat_ID) ON DELETE NO
ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Asiento ADD CONSTRAINT FK_SR_Asiento49 FOREIGN KEY (ID_Flights)
REFERENCES SR_Flights (ID_Flights) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Asiento ADD CONSTRAINT FK_SR_Asiento54 FOREIGN KEY (ID_Clientes)
REFERENCES SR_Customers (ID_Customers) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Distrito ADD CONSTRAINT FK_SR_Distrito59 FOREIGN KEY (ID_Departamento,
ID_Province) REFERENCES SR_Province (ID_Department, ID_Province) ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE SR_Card_Registration ADD CONSTRAINT FK_SR_Card_Registration52 FOREIGN
KEY (Customer_ID) REFERENCES Customers (Customer_ID) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE SR_Reserva ADD CONSTRAINT FK_SR_Reserva61 FOREIGN KEY (ID_Vuelos,
ID_Seat) REFERENCES SR_Seat (ID_Flights, ID_Seat) ON DELETE NO ACTION ON UPDATE
NO ACTION;
ALTER TABLE SR_Reserva ADD CONSTRAINT FK_SR_Reserva60 FOREIGN KEY (ID_Clientes)
REFERENCES SR_Customers (Customer_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Vuelos ADD CONSTRAINT FK_SR_Vuelos47 FOREIGN KEY (ID_Tarifa) REFERENCES
SR_Tarifa (ID_Tarifa) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Flights ADD CONSTRAINT FK_SR_Flights57 FOREIGN KEY
(Department_Sales_ID) REFERENCES SR_Department (Department_ID) ON DELETE NO
ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Flights ADD CONSTRAINT FK_SR_Flights58 FOREIGN KEY
(ID_Department_Arrives) REFERENCES SR_Department (ID_Department) ON DELETE NO
ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Flights ADD CONSTRAINT FK_SR_Flights50 FOREIGN KEY (Airline_ID)
REFERENCES SR_Airline (ID_Airline) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SR_Provincia ADD CONSTRAINT FK_SR_Provincia56 FOREIGN KEY (ID_Departamento)
REFERENCES SR_Department (ID_Department) ON DELETE NO ACTION ON UPDATE NO
ACTION;
ALTER TABLE SR_Registered_Luggage ADD CONSTRAINT FK_SR_Registered_Luggage48
FOREIGN KEY (ID_Flights) REFERENCES SR_Flights (ID_Flights) ON DELETE NO ACTION ON UPDATE
NO ACTION;
ALTER TABLE SR_Equipajes_registrados ADD CONSTRAINT FK_SR_Equipajes_registrados53
FOREIGN KEY (ID_Clientes) REFERENCES SR_Clientes (ID_Clientes) ON DELETE NO ACTION ON
UPDATE NO ACTION;