Free Tutorials, Linux Command, Source Code Architecture, Software Engineering, Intelligent Systems, RDBMS, Computer Accounting, Operations Research, Discrete Mathematics, Network, SAD Lay Networks Lay Networks
Computer Science Networking Operating Systems Linux and Unix Source Code Script & Languages Protocols Glossary
Web laynetworks.com
Google
 


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

CITE_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 : 

(a)   CREATE TABLE AR_FLIGHT_MST (FL_NO VARCHAR2(7), FL_NAME VARCHAR2(25), 
ORG_CD NUMBER(3),DES_CD NUMBER(3),CAPACITY NUMBER(3));

(b)  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);

(c)   CREATE TABLE AR_HALT_MST (HALT_CD NUMBER(3),DESCRIPTION VARCHAR2(30));

(d)  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 VARCHAR2(15),DIST_TRAVEL NUMBER(4),CON_PER_NAME VARCHAR2(25),ADDRESS VARCHAR2(50),
CITY VARCHAR2(25));

(e)   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));

(f)     CREATE TABLE AR_CITE_DET (CITE CODE VARCHAR2(3),CITE_NAME VARCHAR2(30));

SELECT COMMANDS :

(a) To know the information of flight-wise originating and destination terminals

SELECT  FL_NO,FL_NAME, ORG_CD,DES_CD FROM AR_FLIGHT_MST ;

 

(b) 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;

(c ) To count the number of flights

SELECT COUNT(FL_NO) FROM AR_FLIGHT_MST; 

a) To find the halt code and names in alphabetic order

SELECT HALT_CD,DESCRIPTION FROM AR_HALT_MAST
ORDER BY DESCRIPTION;

(e) 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; 

(f)  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 

(g) To list the customers and their information

     SELECT CUST_CODE,NAME,CITY,PH_MOBL_NO FROM AR_CUSTOMER; 

(h) 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
Back
Next
FDDI Frequently Asked Questions (FAQ), The function and frame format of FDDI,Aloha,Comparative analysis between two types of ATM Switches,Knockout Switch,Barcher-Banyan Switch,Various popular standards for compressing multimedia data,Distributed Multimedia Survey: Standards, ASCII to hex value chart,Comparative analysis - TCP - UDP, Addressing Formats and QoS parameters, Bellman Ford's Algorithm Lay networks, free, java, java script, asp, vb, linux, ignou, tutorial, Unix commands, System Analysis, System Design, Ipv6, quiz, download, free, Computer Architecture, Object Oriented System, Relational Database Management Systems, Object Oriented System, Operating Systems, Software Engineering, Communications and Networks, Discrete Mathematics, Intelligent Systems, Operations Research, Accounting and Finance on Computersmca, networking, protocols, glossary, assignment, project, tma, programming source code, programming, source code, unix, free
 
Book Mark/Share this site at BlinkBits BlinkList Blogmarks co.mments Delicious Digg Fark Furl it! Google Ma.gnolia Netvouz NewsVine RawSugar Reddit Shadows Simpy Stumble Technorati YahooMyWeb

Copyright © 2000- 2007 Lay Networks All rights reserved. 
This website is best viewed in Firefox 1.0.1 above.
Web Hosting sponsored by Customized Software Company India
Web Site Designed by Web Designing, Flash Animation, Multimedia Presentations, Broacher/catalogue designing, Web Promotion 
Refer to your freind About Us Legal IGNOU Contact Us Feedback Donate to laynetworks.com Download Management Tutorials Tutorials History Search here