Help with building a database from a specification

[crazyrat25]

Hi, I have been given a brief from an exam board to teach my A-level (16+) class to build a database system. I am not the database specialist though, she is currently off on long term sickness.

I have included the brief from the exam board below. If I can work from a working example I can break it down and teach the separate components to the class.

Megan wishes to handle customer bookings using the same system as the camp site. She would like the new computerised system to handle the checking in and out of guests. The receptionist will receive a copy of the booking form which will include lead guest details, number of guests,accommodation details, date and duration of stay. Megan would like this information to be stored within the database.
Megan has written to previous customers who have used the camping facilities informing them that loyalty points will be valid for Glamping Cymru.
When guests check in they will receive a loyalty card that contains the username and password. Each guest in the party receives a copy of this card.
When guests checkout they will receive an invoice . If loyalty points are to be used then the total value of the loyalty points are deducted from the bill. Loyalty points are updated (click HERE for an explanation).


The system should allow Megan to generate a weekly report


Megan would like a system placed in reception to allow guests to order meals themselves. Guest should access the system using their login details.
Meal bookings can only be placed for the following day. These bookings can only be made at reception during opening times.
Guests may choose to have breakfast delivered between 7.30 am and 10.30 am, lunch from midday until 2.30 pm and dinner from 6.30 pm until 10 pm.

My first job would obviously be to build the tables for this but Other than the customers and accommodation tables I am unsure what to do for the structure of this. There are also some text files included, that I can take screenshots of/attach if necessary?

Thanks in advance for any advice!

[orange]

There are some excellent tutorial(s) at RogersAccessLibrary that start with a business description, and their purpose is to design a database to support that business. The tutorials each come with a Problem definition, a method/procedure guide
to help with each step, and a generic design approach that can be used with any database. Each tutorial also has a proposed solution. My suggestion is that you work through a couple of these tutorials (about 30-45 min each). You will quickly identify the procedure involved and the rationale for the approach. You can use this with any database-- especially the one(s) in your lessons.

I recommend this one and any of the ones in the examples section here.
Good luck.

[Homegrownandy]

Also. Are you using access? All through my education access wasn't used.

[crazyrat25]

Thank you for that link to the tutorials. I have already worked through a number of tutorials that were left behind by the other teacher but I am not sure what table structure to use for the students.

Also, we are using MS Access 2013

[orange]

Did you work through at least one of the tutorials at RogersAccessLibrary?
Use what you learned on your own data.

[HiTechCoach]

Hi, I have been given a brief from an exam board to teach my A-level (16+) class to build a database system. I am not the database specialist though, she is currently off on long term sickness.

I have included the brief from the exam board below. If I can work from a working example I can break it down and teach the separate components to the class.

Megan wishes to handle customer bookings using the same system as the camp site. She would like the new computerised system to handle the checking in and out of guests. The receptionist will receive a copy of the booking form which will include lead guest details, number of guests,accommodation details, date and duration of stay. Megan would like this information to be stored within the database.
Megan has written to previous customers who have used the camping facilities informing them that loyalty points will be valid for Glamping Cymru.
When guests check in they will receive a loyalty card that contains the username and password. Each guest in the party receives a copy of this card.
When guests checkout they will receive an invoice . If loyalty points are to be used then the total value of the loyalty points are deducted from the bill. Loyalty points are updated (click HERE for an explanation).
The system should allow Megan to generate a weekly report


Megan would like a system placed in reception to allow guests to order meals themselves. Guest should access the system using their login details.
Meal bookings can only be placed for the following day. These bookings can only be made at reception during opening times.
Guests may choose to have breakfast delivered between 7.30 am and 10.30 am, lunch from midday until 2.30 pm and dinner from 6.30 pm until 10 pm.

My first job would obviously be to build the tables for this but Other than the customers and accommodation tables I am unsure what to do for the structure of this. There are also some text files included, that I can take screenshots of/attach if necessary?

Thanks in advance for any advice!

FYI: I have built systems for clients that do everything described in the brief. I have also trained programmer how to built it.

WOW. Anyone able to read that brief and build it would definitely be a professional programmer or someone with Advanced Access skills. Not beginner level stuff.

If an expert is teaching this to beginners, it can easily take a year to teach them the skills to be able to read the brief and be able to build it.

If I can work from a working example I can break it down and teach the separate components to the class.

That is not really true with database development. It takes years of practice creating many database and maintaining. It takes years of experience to be able to read the brief you posted and have a clue where to start.

Properly normalizing a database's table structure is not something that you can just look at an example, absorb it, and now how to do it for any other database. It that were true you could just look at the templates available within Access when you create a new database.

None of the templates will teach you how to properly set up a database for multiple users, which you will need. One does have a very simple login screen.

My first job would obviously be to build the tables...

I disagree. That is the obvious first step I hear from Excel people making he leap to database design. A professional database designer would not start within Access. The first step should be to write up design specs and a system model. This is the art of taking the brief, in the real world it would be the customer interview notes, and translating it to documentation for the programmer.


My view is:
Please, Please Please respect the students and other professional trainers and wait until you can build the database yourself without any assistance. Otherwise you really are not prepared to teach it properly.

I believe you must master something before you are qualified to teach it.

You know, you teach. You do not know, you learn.
~ Lao Proverb

[Homegrownandy]

For no reason at all I'll add my comment. I was "taught" databases by someone who was clueless and I hated it. I learnt nothing and I didn't ever want to work with them again. By chance I'm now building one, and with the support on the forums/youtube/reading I love databases. So as Boyd said; you really do need to know such things before you teach them.

Having said that, people may disagree here. The basics of databases aren't that hard.. maybe I did pick some things up with bad teachings, but understanding relationships I seemed to pick up really quickly. If you're only teaching at A level and to that spec its possible you can learn such things.