Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Help with Flighr reservation Database Design

    Hi everyone !

    I hope everyone is alright

    well, it's my first post. I need your help designing my database.

    I'm developing flight reservation system. When I started developig the database i got stuck.

    The problem is that each passenger should be assigned a seat. the system should assign a specific seat number. it could be asile / window/ fronst / back.

    The problem is that each aircraft has three cabins First / business/ economy. and each cabin in each craft has different layout. Some 3-3 , 2-3-2 , 2-4-2 etc...

    How can I desing my db to be able to reserve seat as the passenger wishes ?

    I tried for long , my best idea is to have each cabin with different layout to be an entity. but still I don't think this the solution. ERD looks like complicated and messy.

    Any help regarding this problem ?

    Thank you in Advance

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    If you are a student it is best to say this at the start. Then it's best to give your current table design and then explain what the issues are. Then we feel like we're helping rather than just doing your homework.

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Hi mike_BIKE_KITE

    Thank you for your replay

    Well, If i were a student i would ask my doctor for help

    I studied computer science three years ago but I had not learnt much because i was a lazy student. the best way to learn is to do projects and learn by trying to do something real! I chose online reservation system. I think it's simple and good start.

  4. #4
    Join Date
    Mar 2009
    Posts
    5
    let me start from the begining.

    My project is : online flight reservation. I assume it's for an airline.

    I thought about it and collected the requirements and objectives and I hope they are complete if some requirments not mentioned and you see it's important please let me know.

    Should i start with the requirements ? briefly , and they may not be consistent and clear :
    1- user selects origin/ destination , date of travel, number of passengers ( adult/child/infant).
    2-view the available flights
    3- selects flights
    4-get the fare/ taxes/rules of the ticket/total price
    5-fill in the form of names/contact number/email.
    6-reserve a seat / meal/ request special requirements like wheel chair.
    7-credit card payment
    8-get the tickets.
    9-view bookings and check statues/ changes/ print tickets or receipts.

    I may add the function of changing the dates of flights if it's simple after tickets issued. now it's optional.

    Since i start from scratch and it's not for real airline, I have to develop the databse myself to run my project! I also have to add other functions for the administration and other staff just to start our project so we can play with it.

    Control functions :
    10- create flights and publish schedules. includes add destinations/ taxes/ timings etc..
    11-edit flights ( change time / cancel )
    Marketing functions:
    12-publish fares ,rules,promotions.
    13-edit fares and ticket rules
    Admintration function:
    14-create accounts to allow these functions
    15-generate reports from the databse. Not yet specified

  5. #5
    Join Date
    Mar 2009
    Posts
    5
    I was trying to design my ERD. while i was designing it I eleciate many requiremnets and thought about database design issues and I'm still confused !

    Here is the initial design as a jpeg file. we should discuss it to come with a good database desing before mapping and normalization .. I'm not sure i'm right

    http://i41.tinypic.com/2cql0uh.jpg



    and here is brief explanation of the database so that you judge and try to improve my design.

    --------------------------------------------------
    Each flight is operated by an aircraft.

    A town may have more than one airport.

    An agent makes a booking for one or more passengers.

    Each booking should have an itinerary (one or more flights to get to destination). It has passengers' names , contact details , the total cost would be calculated and saved within the booking based on the ticket type ( class , fare basis ).

    Booking should save the fare basis to know the rules and fares.

    Taxes based on the airports would also be calculated and added to the fare of tickets to get the total cost.

    Each passenger will be issued a ticket .tickets should have a unique ticket number , reference to the booking , passenger name , itinerary , fare basis , taxes and fare amount , total price mentioned.

    A passenger may also request special services, special meal, a desired seat.
    --------------------------------------------------



    I put an X on two relationships ( governs). I think I don't need them since we can find the farebasis associated with ticket or booking by searching fare basis table by keys already kept in ticket or booking.

    I also omit the relationship between ticket and airports. i thought relating them makes no use other than refrential integrity!


    Sorry for writing much.. I really appreciate your help.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have looked at your ERD and it is good enough to get started

    what is your next step?

    also, when is this project due?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    5
    Hi r937 !

    Thank for your fast response !

    well, you didn't tell me your opinion about governs relationships. is it better to omit them or keep them ?

    Project has no due date since i do it for myself but it should not take long time or i'll get bored and close it lol

    next step should be mapping and normialization.

    after that drawing DFD

    Thank you dear.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i did not understand the governs relationships, so yeah, better leave them out

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •