Database Management System Essay Example | Topics and Well Written Essays - 1500 words - 2

Database Management System - Essay Example ata 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. 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 ); 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); 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) ); 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)); 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 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. 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