Help required please

[roadrage]

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.

Regards

 

[DCrake]

Are you actually wanting to build a web based interface or not?

 

[namliam]

I may be over complicating things, but....

As you basicaly have 4 type of seats, maybe add a type table? This would prevent 'users' from having to enter the same info multiple times

Also right now the train carries four carriages, what about when it starts to run with 5?
Where are you keeping your pricing?
What if driving forward is considered to be more pleasent, so maybe the train company starts charging (extra or other) fee's for that?
What if the train starts running with different Carriages with possibly different seatings and/or fee's?
Perhaps you would need a table to define what carriages there are, as well as a table what carriages are 'going along for the trip'

Edit:
What about the trains, there will be 6 special trains your text says... Whats the difference? Do you need to specify this maybe?

Beeing a teacher,
- I wont tell you to better not use fieldnames as Date or Time, these are preserved words and can better not be used.
- Futher I wouldnt presume to tell you that Access and most databases will store Date/Time fields, thus spliting them is *erm* usually not good.
- I also will not go into the discussion of using an autonumber key vs a meaningfull 'formatted' key of i.e. Customer number and/or Seat number.
And I wont worry about the Uniqueness of the customer number 3 chars + 4 numbers *headache* ! A composit primary key
- Lastly.... Special characters in any names should not be used *&^%$#@ etc but als / and _

Good luck Teach!

 

[roadrage]

Are you actually wanting to build a web based interface or not?

No a web interface is not required.

 

[roadrage]

I may be over complicating things, but....

As you basicaly have 4 type of seats, maybe add a type table? This would prevent 'users' from having to enter the same info multiple times

Also right now the train carries four carriages, what about when it starts to run with 5?
Where are you keeping your pricing?
What if driving forward is considered to be more pleasent, so maybe the train company starts charging (extra or other) fee's for that?
What if the train starts running with different Carriages with possibly different seatings and/or fee's?
Perhaps you would need a table to define what carriages there are, as well as a table what carriages are 'going along for the trip'

Edit:
What about the trains, there will be 6 special trains your text says... Whats the difference? Do you need to specify this maybe?

Beeing a teacher,
- I wont tell you to better not use fieldnames as Date or Time, these are preserved words and can better not be used.
- Futher I wouldnt presume to tell you that Access and most databases will store Date/Time fields, thus spliting them is *erm* usually not good.
- I also will not go into the discussion of using an autonumber key vs a meaningfull 'formatted' key of i.e. Customer number and/or Seat number.
And I wont worry about the Uniqueness of the customer number 3 chars + 4 numbers *headache* ! A composit primary key
- Lastly.... Special characters in any names should not be used *&^%$#@ etc but als / and _

Good luck Teach!

you sound like your are over omplicating things... and thanks!

 

[DCrake]

Well if I was a teacher and I set a question which commenced..

The company wishes to use its website to advertise and to take bookings for the “Deer Valley Experience”.

The key words being "take bookings" implies that a web based user interface is required. And should any of my pupils not come up with one I would have retorted ... "Did you not read the question correctly..?"

either the senerio setting is changed to suit the actual configuration of the expected resolution or start learning about asp.

 

[roadrage]

Well if I was a teacher and I set a question which commenced..



The key words being "take bookings" implies that a web based user interface is required. And should any of my pupils not come up with one I would have retorted ... "Did you not read the question correctly..?"

either the senerio setting is changed to suit the actual configuration of the expected resolution or start learning about asp.

I just took a snippet of the text.

 

[namliam]

you sound like your are over omplicating things... and thanks!

Well these are questions/remarks from real business ...

four identical 32-seater carriages labelled

Why add the identical? Surely an open door for "they have bought new carriages"....

Among other things... I think you need the 'next step' to make it sustainable.
Offcourse I do have a tendancy to make things overly complex

 

[namliam]

The key words being "take bookings" implies that a web based user interface is required. And should any of my pupils not come up with one I would have retorted ... "Did you not read the question correctly..?"

then again:
"You have been asked to produce this database."

Doesnt say anything about complete system, web pages... just asked to do the database/backend.

 

[roadrage]

then again:
"You have been asked to produce this database."

Doesnt say anything about complete system, web pages... just asked to do the database/backend.

OK withou over complicting, how do you assign a unique id that consists of the last three letters of theri name, my guess is a query???

 

[wazz]

you teach databases?

 

[roadrage]

you teach databases?

No I teach ICT 11- to 16 year olds, databases is just an element of it.

 

[wazz]

... i have problems interprating scenarios into databases...

a good place to start when interpreting requirements is to look for nouns; they will help you identify the entities (tables) you need. some of the nouns (you will not need all of them):

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.

you will have guessed that some of the terms in red will describe, or identify the noun: lastname will help to identify the customer. those terms become fields in the table.

So far the tables i have are as follows:​

tbl_Trip (Trip_no, DepDateTime, ArrDateTime (or duration))
(tblTrain (TrainID, not much to go on here ... has four carriages))
tblCarriage (CarriageID, TrainID (train this carriage belongs to, possibly)
tbl_Seats (Seat_no, forward/backward, window/aisle)
tbl_Customer (CustomerID, fname, sname, address1.......)
tbl_Booking (Booking_Ref_No, Trip_no, (TrainID), CarriageID, Seat_no, custno, Date_Booking_Made)

the carriage seems quite important in this scenario. i would draw a diagram of a carriage showing the aisle and eight sections and even draw the seats.

the booking will have to show which train, carriage (a,b,c,d) and specific seat. (Individual seats will have a code which consists of the carriage label ... (A, B, C or D) followed by a two digit number.)

seats as you have them seem to be more like descriptions; you might create tblSeatType (window; aisle), tblFacing (forward; backward) -- how you will know which one is forward and which is backward, i'm not sure; i suppose it depends on going/returning, but the train doesn't turn around does it? do people really have a choice here, or do they face forward (or backward) when they leave, and the opposite when they return? if they really don't have a choice, eliminate this.

this is far from complete. you next have to think in terms of one-to-many and figure out which (single) entities are related to which other entities, and how. a train has many carriages; a carriage has many seats; a booking has many people (or not); and so on.

i think there is a sample around here somewhere for booking a theatre. maybe check that out. there are other booking scenarios around you could check out on this site.

p.s. you can't get the three letters of a person's name with a query because the person doesn't exist yet - you are entering the person. you will have to extract the first three letters from the entered name with a function:
Left("txtLName", 3).

 

[roadrage]

Hey thanks for your help, your ideas and advice was first class and very valuable. I have looked for a theatre booking scenario in these forums and as yet i have not found one, but i'm still looking... Also when you say i need a function, do i assign it to an unbound text box?

 

[gemma-the-husky]

one thing with things like this -

any question for students (any course) is necessarily targeted at an appropriate level

a student wont be expected to produce a commercial quality database - hence all that is required is to produce a database to enable bookings to be taken on 4 similar 32 seater coaches (say), and not necessarily to consider (or allow for) a real world situation where the number of coaches may vary, or the seating arrangement may vary -although the nature of properly designed databases generally includes such flexibility

in particular note that although dealing with a number of coaches is trivial, the seating arrangement itself is quite awkward. you cant just have seats numbered 1-32, since you need to know which seats are contiguous, for family/group bookings.

solving this effeciently is in itself likely to be extraordinarily complex, and probably out of the ability range of the students, and probably an untrainerd teacher.

in this case it may be sufficient for the project merely not to allocate specific seats, just record the toal number of allocations for each carriage.

 

[roadrage]

one thing with things like this -

any question for students (any course) is necessarily targeted at an appropriate level

a student wont be expected to produce a commercial quality database - hence all that is required is to produce a database to enable bookings to be taken on 4 similar 32 seater coaches (say), and not necessarily to consider (or allow for) a real world situation where the number of coaches may vary, or the seating arrangement may vary -although the nature of properly designed databases generally includes such flexibility

in particular note that although dealing with a number of coaches is trivial, the seating arrangement itself is quite awkward. you cant just have seats
numbered 1-32, since you need to know which seats are contiguous, for family/group bookings.
solving this effeciently is in itself likely to be extraordinarily complex, and probably out of the ability range of the students, and probably an untrainerd teacher.
in this case it may be sufficient for the project merely not to allocate specific seats, just record the toal number of allocations for each carriage.

Hi thanks for the advice. I am trying functions to automatically generate id using customers name is this correct:

On the control source I type in left([customer_name]),3) but this doesn't produce anything?

 

[roadrage]

ok i think i might have solved this. If i make a query and make a new field based on an expression Left([CustomerID],3) this works. I should now be able to run a macro to run this query no?? also how can i get four digits automatically at the end of this customer id?

 

[roadrage]

ok i think i might have solved this. If i make a query and make a new field based on an expression Left([CustomerID],3) this works. I should now be able to run a macro to run this query no?? also how can i get four digits automatically at the end of this customer id?

Just tried linikng the query to a text box but it doesn't work? Can some one help, am i along the right lines or am i firing blanks??

 

[namliam]

Firing blanks sorry...

The left is correct, it gets the first 3 characters.

You can put it in a control if you add = to it...

The problem is in trying to make sure your Unique identifier of 3 chars + 4 numbers stays unique. So you need a function that does this...

Also you need a 'definition' of how to build it. What do you do with "Mc Niel" ?? Is the first three "Mc " or "McN"

I will remind you of my earlier statement, please dont use this as your database Primary key! This (in daily real life business) will give you problems, use a meaningless Primary key in your database, always!

 

[roadrage]

Firing blanks sorry...

The left is correct, it gets the first 3 characters.

You can put it in a control if you add = to it...

The problem is in trying to make sure your Unique identifier of 3 chars + 4 numbers stays unique. So you need a function that does this...

Also you need a 'definition' of how to build it. What do you do with "Mc Niel" ?? Is the first three "Mc " or "McN"

I will remind you of my earlier statement, please dont use this as your database Primary key! This (in daily real life business) will give you problems, use a meaningless Primary key in your database, always!

Thanks, would you mind giving me some idea of what function i need and also does it need to be written in a VBA editor?