Car Rental System Oracle Application

A Brief Description

The aim of my Car Rental System project is to achieve an Oracle Application able to store and manipulate data. The idea is to let the manager of a car rental to control all money transactions and keep track on of available cars, clients, employees and suppliers. A main issue is to take into consideration security matters. Here Oracle appears to be satisfying from this side.

My Car Rental System project consists of a main page that has six buttons connecting it to different parts of the main database. The cars button takes us to a form that shows the car ID, brand, type, supplier ID, price per day, insurance company and most important whether the car is rented or not.

The Clients button takes us to the client’s form where the manager can view the available clients, add a client or search for a specific client. Each client has ID, first name , last name , age, country, city, road, building, stage, sex, civil state, telephone, portable, fax, email, and the most important, the cars ID that was rented by the client. The employee button takes us to the employee form that gives the Employee ID, first name, last name, age, country, city, road, building, stage, sex, salary, civil state, job and department.

The suppliers button takes us to the supplier form where information like the supplier ID, telephone number and address are viewed. Then the transactions form shows the transaction ID, some information about the client, and the master credit card ID and pin code. Here we should note that, the data concerning credit cards should be connected to banks or to online services supported by banks to provide validation to the input information.

The next button named “search by picture” takes us to a form with a set of pictures each having two buttons underneath it. One button named “See” and the other “Rent”. If “See” was clicked, it will call the cars form so directly we can get other information about the selected car. If “Rent” was clicked, the client form will appear and collecting the information from the client can start directly.

All what we were talking about was the main page. But for security issues, a “Log in” page was added with a username and password that must be with the manager only. The application creator, or simply I, is the only one who is able to change the password.

Tables Creation

I created a list of tables to handle this database.

The tables are: SUPPLIER, CARS, Clients, Employee, Transactions and Authentication.

Each table has a primary key. The primary keys are the first column of each table and named by the table name adding ID. I connected the clients table with the cars table through a foreign key. An illustrating picture is shown below:

And for more information about table creation using sql language refer to the text file included with the project on the CD.

Procedure of Work

After creating the tables, we enter the form builder which is part of the Developer Suite 10g. An important thing here to be mentioned is one should start OC4J service before running a form.

We create a new form and start dropping Text Items in the layout editor. Then In the Object Navigator, select Data Blocks and then go to menu and select edit > create. I usually used the manual way to create a new item. After the new item is created, double click it to open the properties. In the Database panel chose the suitable table that will be connected to this Data Block Item. After that, start adding new child items to this item that will represent the columns of the table.

Here comes the step concerning connection between Text Items and Data Blocks. In fact, this is an easy step to accomplish. Just double click the Text Item and adjust the database connection in the properties panel. Specifically input in the column name, the name of the column that should appear.

A good thing to mention here is that each button has a pl-sql statement behind it to execute the command. To call other forms I used CALL_FORM(‘C:\ahmad\main’) for example. That is why placing the project in the C drive is a must for the application to run.

As mentioned above, security of such a program is a must. That’s why I added a page to log in. The code behind the log in button is the following:

[sql]

DECLARE

USERNAME VARCHAR2(20);

PASSWORD1 VARCHAR2(20);

alert11 number(10);

BEGIN

SELECT UserNameww,passwordww INTO USERNAME,PASSWORD1

FROM Authentification

WHERE UserNameww = :USER1 AND passwordww = :PASS1;

IF (USERNAME = :USER1 AND PASSWORD1 = :PASS1) THEN

alert11 := SHOW_ALERT(‘Valid’);

CALL_FORM(‘C:\ahmad\MAIN’);

END IF;

EXCEPTION

WHEN OTHERS THEN

alert11 := SHOW_ALERT(‘INVALID’);

END;

[/sql]

The code simply declares three variables username, password and alert. It queries the authentification table for all usernames and passwords specified. If found, the access is verified and an alert is called saying that the entrance is valid. If not, then the access is denied and an Invalid message is thrown.

Each time a form is called, I added in the triggers an item called when the form is loaded. I wrote “Execute_Query;”