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.
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.
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 )
12-publish fares ,rules,promotions.
13-edit fares and ticket rules
14-create accounts to allow these functions
15-generate reports from the databse. Not yet specified
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.