Flight Test Database in Oracle

Here, the development of a typical DBMS using Oracle 8 (there could be other SW/tools possible [17]) is briefly described [16,18-21]. Its client-server architecture

(say, two PCs connected via LAN, local area network) can be used, leading to a multi­user access system with a centralized data bank over LAN. The data can be loaded directly into the database tables from ASCII file. This can be done either manually using an interactive menu or via the Oracle Loader. Also, SQL * Loader can be used for loading the data from a non-Oracle data source to the Oracle database. Oracle 8 is a component of networking computing architecture and this feature reduces the cost. Some features of Oracle 8 are (1) client-server architecture, (2) object/relational technology, (3) concurrent database users, and (4) SQL * Loader/Oracle Loader facil­ity. These features are briefly described next. A communications and code transfer process in a typical DBMS is depicted in Figure 7.13.

Client-server mode: The database system is divided into (1) a client portion or a front end and (2) a server portion or a back end. The client PC is responsible for controlling the user interface. The server PC is the host to the relational database management system. An application program residing on the client PC interfaces with another SW layer known as SQL * Net. The latter is responsible for communicating requests and their results between the application program and the relational database management system. The client PCs directly communicate with the database server.

Concurrent database users: Oracle supports a large number of users at the enterprise level using Net 8, the new version of SQL * Net. The data that are on Windows NT PCs can be accessed by these users.

Object/relational technology: This helps in the design of an object-oriented database. This extends the traditional Oracle relational database management system to include object-oriented concepts and structures.

SQL * Loader or Oracle Loader. The tool can be used to move the data from a non-Oracle standard source into the Oracle database. The data can be loaded from multiple data files and in fixed/delimited formats. The data file containing the actual data and the control file containing the specifications that drive the SQL * Loader session are provided as inputs to the SQL * Loader. The loader reads text files and places the data in the Oracle database based on the instructions in the control file. The control file provides the information related to (1) names of the input data files,

(2) names of the database tables into which the data are to be uploaded, (3) the mode in which the data are to be loaded (like replace, appends, or insert), (4) the correspondence between the data fields and the table columns, and (5) the selection criteria for loading.

A brief note on Oracle 8: It is an object relational database management system. The objects can be defined as reusable SW codes. These codes are location independent and perform a specific task on any application. The client-server concept segregates the processing of an application between two systems. One system (PC) performs all activities related to the database server and the other (client) performs activities that help the user to interact with the application. The front-end/client database applica­tion also interacts with the database by requesting and receiving information from the database server. The client acts as an interface between the user and the database. It also checks for validation against the data entered by the user. The user front-end tools of Oracle are SQL* Plus V 8, Oracle Forms 5.0, and Reports 3.0 and of course the later versions. The back-end server manages the database tables among the multiple clients. These clients concurrently request the server for the same data. The server also maintains the integrity across all client applications. It controls the database access and security requirements.

PL/SQL and FORM: SQL is the natural language of the database administrator. However, it does not have procedural capabilities of looping and branching. It also does not have any conditional checking capabilities. The Oracle has PL/SQL which can be used to create programs for validation and manipulation of table data. It provides the user with all the facilities of a programming environment. PL/SQL closes the gap between database technology and procedural programming languages. It is a development tool and extends the capability of Oracle’s SQL database language. One can insert, delete, update, and retrieve table data. One can use procedural techniques of writing loops and branching to another block of codes. As it is an extension of the SQL, it allows one to use all the SQL data manipulation statements. It also allows curser control operations and transaction processing, as well as logical grouping of SQL sentences and passing them to the database administrator as a single block. The entire block of statements can be sent to the RDBMS engine. PL/SQL can be used in SQL*FORMS, and its procedural capabilities can be used for writing complex triggers that would validate data before they are placed in the tables. The applications in PL/SQL are portable to any computer hardware (HW) and operating system (OS), where Oracle is operational. The Oracle Forms Builder provides GUI to design forms. All objects, properties, and triggers can be selected by clicking on an appropriate icon. The tool comprises (1) Forms Builder, (2) Forms compiler, and (3) Forms Runtime. An application built using Oracle Forms Builder would contain (1) Form module, (2) Menus, (3) PL/SQL, (4) Object libraries, and (5) database objects. The Form module is a collection of objects such as blocks, canvas, frames, items, and the event-based PL/SQL code blocks (these are called triggers in MS Window). PL/SQL library module is a collection of PL/SQL functions and procedures in a single library file, which is attached to a Form/Menu module. The objects in Form/Menu can access and share the collection of PL/SQL functions and procedures. An Object library is used to create, store, maintain, and distribute standard and reusable objects and create applications by dragging and dropping predefined objects on Form. The Database objects like Stored Procedures, Stored Functions, and Database Triggers are created using appropriate SQL and PL/SQL syntax.

Conversion of ASCII file into tables: This can also be done manually using the built-in option SQL * Loader. This is run on the server machine. A method can be used such that the system will automatically call the subroutines to convert the ASCII file into a text file by copying it from a location to a temporary directory. Then a table is created with all the required fields and a control file. This file contains the path of the file and the width of all fields. The following code reads each line from file using the file operations with the help of text_IO package:

LOAD DATA

INFILE < input file name >

DISCARDFILE<disfile >

[APPEND/REPLACE/INSERT]

INTO TABLE < tablename >

(<columnname >

[POSITION<start : end)

< columnname >

[POSITION< (start : end) . .. ]

)

Data storage / manipulation of associated data: This has options like adding/saving of new data, modification/deletion of old data, and viewing of data from the tables. Queries can be entered to view the required data.

Data storage: The flight data should be stored in such a way that the analyst is able to select a particular section of data/information. The user should be able to select the table name from the main screen developed using a Developer 2000 product (D2K)—Forms 5.0. From here the details of the fields with all options can be viewed. The user can enter the new data and click on the Save button to save it in the specified field. The subroutines will be activated automatically when the specific buttons are pressed. The data will be saved into the table based on the FILEJNDEX. This is created automatically. The user can modify/erase the data via a query mode, if so desired.

Graphical display: This is the user interface menu for plotting graphs and can be implemented with the help of a D2K product—Oracle graphics. One can use the OG. PLL library.

User-defined queries: This can be implemented with the help of a D2K product— Reports Builder. Using this screen the user can select the tables, the field names, and the required conditions, etc.