Database Management System - Statistics Project Example

Database Management Systems The table schema for the flight booking company are given below in the table format. The constrains for a specific fieldis also given. In most of the fields the not null integrity constraint is enforced. The relevant primary key and foreign key are also enforced properly. The data type for a particular field is choose with respect to the context of the field and most of the fields the data length are specified in par with the real time data. For example the number of flights is limited to 6 digit and number of seats is limited to 4 digit. The varchar2 data type is used in many cases which is advantageous in terms of memory management and also to make changes in the size of a field in future as required. Table Name: CUSTOMER Attribute Name Data Type Constrains Customer_no number(12) Primary Key Customer_town varchar2(30) Not Null Customer_name varhchar2(45) Not Null Customer_address varchar2(80) Not Null SQL query: create table Customer(Customer_no number(12) constraint CONST_PK_CUST primary key not null, Customer_town varchar2(30) not null, Customer_name varchar2(45) not null, Customer_address varchar2(80) not null ); Table Name: SALES_REP Attribute Name Data Type Constrains Sales_rep_id number(1) Primary key, Range in (1 to 5) Sales_rep_name varchar2(45) Not null SQL query: create table Sales_rep(Sales_rep_id number(1) constraint CONST_PK_CUST primary key constraint check_id check(Sales_rep_id between 1 to 5), Sales_rep_name varchar2(45) not null); Table Name: FLIGHT Attribute Name Data Type Constrains Flight_code varhchar2(6) Primary key Departure_day_time date Not null Departure_airport varhcar2(30) Not null Arrival_time date Not null Arrival_airport varchar2(30) Not null SQL query: create table Flight(Flight_code varchar2(6) constraint CONST_PK_FLIGHT primary key, Departure_day_time date not null, Departure_airport varchar2(30) not null, Arrival_time date not null, Arrival_airport varchar2(30) not null); Table Name: BOOKING Attribute Name Data Type Constrains Booking_ref_no number(10) Primary key Booking_date Date Not null Customer_no number(10) References Customer_no of Customer Deposit_paid varchar2(1) Not null, check in Y,N Sales_rep_id Number(1) References Sales_rep_id of Sales_rep SQL Query: create table Booking(Booking_ref_no number(10) constraint CONST_PK_BOOKING primary key, Booking_date date, Customer_no number(10) constraint CONST_FK_BOOKING references Customer(Customer_no), Deposit_paid varchar2(1) not null constraint CHECK_YN check(Deposit_paid in(Y,N)), Sales_rep_id number(1) constraint CONST_FK2_BOOKING references Sales_rep(Sales_rep_id) ); Table Name: SEAT Attribute Name Data Type Constrains Seat_no number(5) Primary key Class_code bool References class_code of Class SQL Query create table Seat(Seat_no number(5) constraint CONST_PK_SEAT primary key, Class_code bool constraint CONST_FK_SEAT references Class(Class_code)); Table Name: CLASS Attribute Name Data Type Constrains Class_code bool Primary key Class varchar2(7) Not null SQL query: create table Class(Class_code bool, Class varchar2(7) not null); Table Name: FLIGHT_SEAT Attribute Name Data Type Constrains Flight_code varchar2(6) Primary key, References Flight_code of Flight Seat_no Number(4) Primary key, References Seat_no of Seat Price number(5) Not null SQL Query create table FLIGHT_SEAT(Flight_code varchar2(6) constraint CONST_FK1_FLIHTSEAT references Flight(Flight_code), Seat_no number(4) constraint CONST_FK1_FLIGHTSEAT references Seat(Seat_no), price number(5) not null,constraint CONST_PK1_FLIGHTSEAT primary key(Flight_code,Seat_no)); Table Name: BOOKING_LINE Attribute Name Data Type Constrains Booking_ref_no number(10) References Booking_ref_no of BOOKING Flight_code varchar2(6) Primary key, References flight code and Seat_no of FLIGHTSEAT Seat_no Number(5) Flight_date Date Primary key Passenger_surname Varchar2(20) Not null SQL query: create table Booking_line(Booking_ref_no number(10) constraints CONST_FK1_BOOKINGLINE references Booking_line(Booking_ref_no), Flight_code varchar2(6), Flight_date date,Passenger_surname varhcar2(20), constraints CONST_PK_BOOKINGLINE primary key (Flight_code, Seat_no, Flight_date),constraints CONST_FK2_BOOKINGLINE foreign key (Flight_code, Seat_no) references Flight_seat(Flight_code, seat_no) ); The entity SEAT seems to be a over head in data entry and also it has some effect in database operation. The SEATNO and CLASS_CODE field in the SEAT entity can be moved into the FLIGHT_SEAT entity. In the FLIGHTSEAT entity we can have the CLASS_CODE which will solve the above issues. The updated FLIGHT_SEAT entity is given below. Table Name: FLIGHT_SEAT Attribute Name Data Type Constrains Flight_code varchar2(6) Joined Primary key Seat_no Number(4) Price number(5) not null Class_code bool References class_code of CLASS SQL query: Create table Flight_seat(Flight_code number(6), Seat_no number(4), Price number(5), Class_code bool); When the entity FLIGHT_SEAT is inserted a new row or updated any existing row the CLASS field should be first class if the seat no is less than 21 otherwise it should be set to economy class. To handle this business rule a trigger is written to check if the CLASS filed in the FLIGHT_SEAT entity whenever an insert or update happens to the entity. The trigger checks for the rule and it automatically assigns the right class code based on the seat no thus achieving efficiency in the data entry. The given below is the required trigger. Trigger1.sql Create or replace trigger set_class after insert or update of Seat_no,Class_code on Flight_seat for each row declare seatno number(5); classcode bool; classname varchar2(7); begin select f.Seat_no, f.Class_code, c.Class into seatno,classcode,classname from Flight_seat f,Class c where Flight_code = :new.Flight_code and f.Class_code = c.Class_code; if (:new.Seat_no < 21 and :new.Seat_no >0 and :new.Class_code = FALSE) then raise_application_error(-10000,Wrong Class Code || classname); :new.Class_code = TRUE; elsif(:new.Seat_no>20 and :new.Class_code = TRUE) raise_application_error(-10001,Wrong Class Code || classname); :new.Class_code = FALSE; end if; end; Inserting the given data: insert into Customer values(1, Windsor, RHM PLC, Windsor House Ascot Road); insert into Sales_rep values (1, john smith); insert into Flight values(KN456, to_date(Wed 12:00,dy:hh:mi),Gatwick, to_date(hh:mi,14:00), Athens); insert into Flight values(KN457, to_date(Fri 11:00,dy:hh:mi),Athens, to_date(hh:mi,12:00), Gatwick); insert into Booking values(1,to_date(dd:mon:yyyy,14:Jan:2010),1,Y,1); insert into Flight_seat values(KN456, 112, 110, 0); insert into Flight_seat values(KN456, 2, 150, 1); insert into Flight_seat values(KN457, 120, 110, 0); insert into Flight_seat values(KN457, 2, 150, 1); insert into Booking_line values(1,KN456,112,to_date(dd:mm:yyyy,14:07:2010), Patel); insert into Booking_line values(1,KN456,2,to_date(dd:mm:yyyy,14:07:2010), Khan); insert into Booking_line values(1,KN456,112,to_date(dd:mm:yyyy,21:07:2010), Smith); insert into Booking_line values(1,KN457,120,to_date(dd:mm:yyyy,16:07:2010), Jones); insert into Booking_line values(1,KN456,2,to_date(dd:mm:yyyy,16:07:2010), Khan); Report to list all booking made by a sales representative: The report can be achieved by using procedure so that we can have a formatted output. We will get the Sales representative id and the we will display the report. The required procedure is as follows: Create or replace procedure booking_report (Salesrepid IN number(2)) IS Cursor BookingLine IS select flight_code, passenger_surname,Sales_rep_id from Booking_Line; CountPass, CountPassFlight number(7); booking_line_rec BookingLine%rowtype; Salesrepname varchar2(30); BEGIN select count(passenger_surname) into CountPass from booking_line where sales_rep_id = Salesrepid; select sales_rep_name from sales_rep into Salesrepname where sales_rep_id=salesrepid; dbms_output.put_line( Sales Rep Name || Salesrepname||Passenger Count || CountPass); IF NOT BookingLine%ISOPEN then open BookingLine END IF dbms_output.put_line( FLIGHT_CODE || PERSON_PER_FLIGHT || PASSENGER_SURNAME ); FETCH BookingLine INTO booking_line_rec; EXIT WHEN booking_line_rec%NOTFOUND; select count(passenger_surname) from booking_line group by passenger_surname having flight_code = booking_line_rec.flight_code; select count(passenger_surname) into CountPassFlight from booking_line where sales_rep_id = Salesrepid and flight_code=BookingLine_rec.flight_code; IF( booking_line_rec.Sales_rep_id = Salesrepid) then dbms_output.put_line( booking_line_rec.flight_code || || || CountPassFlight || booking_line_rec.passenger_surname ); END IF END LOOP; END; The Output of the above report: SQL>exec booking_report (1) Sales Rep Name john smith Passenger Count 5 FLIGHT_CODE PERSON_PER_FLIGHT PASSENGER_SURNAME KN456 3 Patel KN456 3 Khan KN456 3 Smith KN457 2 Jones KN457 2 Khan Work Cited Connor McDonald, Chaim Katz, Christopher Beck, Joel R.Kallaman. “Mastering Oracle: PL/SQL: practical Solutions”. Apress, 2004 Oracle® Database. Data Warehousing Guide 10g Release 2 (10.2) B14223-02, December 2005 http://download.oracle.com/docs/cd/B19306_01/server.102/b14223.pdf Read More