I need to build a database from scratch. Without hiding anything, i am a teacher teaching databases to students. I have been
given some pre release materials containg a scenario. I have an ok level of access however i have problems interprating
scenarios into databases. Please don't get me wrong i don't want anyone to do the work for me but Would anyone be able to
assist me in doing the following:
The company wishes to use its website to advertise and to take bookings for the “Deer Valley Experience”. A database is
required which allows users of the website to book seats on these special trains. You have been asked to produce this
database. In total there will be six special trains, three on each day. The trains will leave from Linfield station at 09:00,
11:30 and 13:00. Each train will be made up of an engine and four identical 32-seater carriages labelled A, B, C and D. Each
carriage has eight sections, four on either side of an aisle. Each section
contains two pairs of seats, one pair facing forwards and one pair facing back. Each pair of seats has a window seat and an
aisle seat. Individual seats will have a code which consists of the carriage label
(A, B, C or D) followed by a two digit number.
The customer must enter at least their last name, the first line of their address and their post code. There will be the
option on this form to confirm or cancel the booking. If the cancel button is pressed then nothing is stored. If the confirm
button is pressed then the customer is assigned a unique identification (Customer ID), which consists of the first three
characters of their last name followed by a four digit number. The customer’s data is then saved and the seat is reserved by
linking their identification to the relevant seat booking and the system is updated. If a seat is still available then it
will contain the word“Free” instead of a Customer ID.
So far the tables i have are as follows:
tbl_Trip (Trip_no, date, time)
tbl_Seats (Seat_no, forward/backward, window/aisle)
tbl_Customer (Customer_no, fname, sname, address1.......)
tbl_Booking (Booking_Ref_No, Trip_no, Seat_no, custno, Date_Booking_Made)
I would really appreciate peoples help on this.