| Home > Computer Science > CS 17 |
| |
|
|
| Relational
Database Management Systems |
| |
| Airline
Reservation System |
| |
DATA DICTIONARY :
FLIGHT DETAILS : |
| AR_FLIGHT_MST |
| FL_NO |
VARCHAR2(7) |
PRIMARY KEY |
| FL_NAME |
VARCHAR2(25) |
NOT NULL |
| ORG_CD |
NUMBER(3) |
NOT NULL |
| DES_CD |
NUMBER(3) |
NOT NULL |
| CAPACITY |
NUMBER(3) |
NOT NULL |
|
| AR_FLIGHT_DET |
| FL_NO |
VARCHAR2(7) |
PRIMARY KEY (REFERENCE AR_FLIGHT_MST.FL_NO) |
| HALT_CD |
NUMBER(3) |
PRIMARY KEY |
| DIST_FRM_ORG |
NUMBER(3) |
|
| NO_QUOTA |
NUMBER(3) |
|
| NO_BOOKED |
NUMBER(3) |
|
| JOUR_DATE |
|
NOT NULL |
|
| AR_HALT_MST |
| HALT_CD |
NUMBER(3) |
PRIMARY KEY |
| DESCRIPTION |
VARCHAR2(30) |
NOT NULL |
|
| |
| CUSTOMER DETAILS : |
| AR_CUST_MST |
| CITE_CODE |
VARCHAR2(3) |
|
| CUST_CODE |
VARCHAR2(6) |
PRIMARY KEY |
| NAME |
VARCHAR2(25) |
- |
| ADDRESS1 |
VARCHAR2(30) |
- |
| ADDRESS2 |
VARCHAR2(30) |
- |
| CITY |
VARCHAR2(25) |
- |
| PIN |
VARCHAR2(6) |
- |
| PH_MOBL_NO |
VARCHAR2(15) |
- |
| DIST_TRAVEL |
NUMBER(4) |
- |
| CON_PER_NAME |
VARCHAR2(25) |
NOT NULL |
| ADDRESS |
VARCHAR2(50) |
- |
| CITY |
VARCHAR2(25) |
- |
|
| |
| RESERVATION DETAILS : |
| AR_RESERV_DET |
| CITE_CODE |
VARCHAR2(3) |
NOT NULL |
| CUST_CODE |
VARCHAR2(6) |
NOT NULL |
| FL_NO |
VARCHAR2(7) |
NOT NULL |
| ORG_CD |
NUMBER(3) |
NOT NULL |
| DES_CD |
NUMBER(3) |
NOT NULL |
| DIST_TRAVEL |
NUMBER(4) |
|
| BOOK_DATE |
DATE |
|
| TRAVEL_DATE |
DATE |
|
| STATUS |
CHAR(1) |
|
|
| AR_CITE_MST |
| CITE_CODE |
VARCHAR2(3) |
PRIMARY KEY |
| CUST_NAME |
VARCHAR2(30) |
NOT NULL |
|
| |
| SQL COMMANDS : |
| |
| To develop the given application the below activities are followed in an order: |
- Creation of Database
- Creation of Tables
- Creation of Database Triggers
- Creation of Stores Procedures
- Creation of Server Enforced validation checks
- Create of Indexes
- Creation of view
- Creation of synonyms
|
| The following are some of the SQL commands used to implement this application : |
|
|
| CREATION OF TABLES : |
- CREATE TABLE AR_FLIGHT_MST (FL_NO VARCHAR2(7), FL_NAME VARCHAR2(25), ORG_CD NUMBER(3),DES_CD NUMBER(3),CAPACITY NUMBER(3));
- CREATE TABLE AR_FLIGHT_DET (FL_NO VARCHAR2(7),HALT_CD NUMBER(3), DIST_FRM_ORG NUMBER(3),NO_QUOTA NUMBER(3),NO_BOOKED NUMBER(3),JOUR_DATE DATE);
- CREATE TABLE AR_HALT_MST (HALT_CD NUMBER(3),DESCRIPTION VARCHAR2(30));
- CREATE TABLE AR_CUST_MST (CITE_CODE VARCHAR(3),CUST_CD VARCHAR2(6),NAME VARCHAR2(25), ADDRESS1 VARCHAR2(30),ADDRESS2 VARCHAR2(30),CITY VARCHAR2(25),PIN VARCHAR2(6),PH_MOBL_NO ARCHAR2(15),DIST_TRAVEL NUMBER(4),CON_PER_NAME VARCHAR2(25),ADDRESS VARCHAR2(50), CITY VARCHAR2(25));
- CREATE TABLE AR_RESERV_DET (CITE_CODE VARCHAR2(3), CUST_CODE VARCHAR2(6), FL_NO VARCHAR2(7), ORG_CD NUMBER(3), DES_CD NUMBER(3),DIST_TRAVEL NUMBER(4),BOOK_DATE DATE,TRAVEL_DATE DATE, STATUS CHAR(1));
- CREATE TABLE AR_CITE_DET (CITE CODE VARCHAR2(3),CITE_NAME VARCHAR2(30));
|
| SELECT COMMANDS : |
- To know the information of flight-wise originating and destination terminals
SELECT FL_NO,FL_NAME, ORG_CD,DES_CD FROM AR_FLIGHT_MST ;
- To get the flight, date wise available seats
SELECT FL_NO,FL_NAME,JOUR_DATE (NO_QUOTA-NO_BOOKED)
AVAILABLE FROM AR_FLIGHT_DET ORDER BY FL_NO;
- To count the number of flights
SELECT COUNT(FL_NO) FROM AR_FLIGHT_MST;
- To find the halt code and names in alphabetic order
SELECT HALT_CD,DESCRIPTION FROM AR_HALT_MAST ORDER BY DESCRIPTION;
- To find out the details of seats available by filght,halt wise
SELECT FL_NO,HALT_CD,JOUR_DATE,NO_QUOTA,NVL(NO_QUOTA-NO_BOOKED) FROM AR_FLIGHT_DET GROUP BY FL_NO,HALT_CD;
- To find out the details of seats available by halt wise,filght
SELECT HALT_CD,FL_NO, JOUR_DATE,NO_QUOTA,NVL(NO_QUOTA-NO_BOOKED)
FROM AR_FLIGHT_DET GROUP BY HALT_CD, FL_NO
- To list the customers and their information
SELECT CUST_CODE,NAME,CITY,PH_MOBL_NO FROM AR_CUSTOMER;
- city wise reservation(transaction) details
SELECT E.CITE_NAME,D.NAME,B.FL_NAME,A.ORG_CD,A.DESC_CD,C.DESCRIPTION, A.DIST_TRAVLE,A.BOOK_DATE,A.TRAVEL_DATE FROM AR_RESRV_DET A,AR_FLIGHT_MST B,AR_HALT_MST C,AR_CUST_MST D,AR_CITE_MST E WHERE A.CITE_CODE=E.CITE_CODE AND A.CUST_CODE=D.CUST_CODE AND (A.ORG_CD=C.HALT_CD OR A.DES_CD=C.HALT_CD) AND A.FL_NO=B.FL_NO
|
| GROUP BY E.CITE_NAME,B.FL_NAME; |
| TOP |
| |
|
