1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded education professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Applied ICT Unit 7 Databases pre-release

Discussion in 'Computing and ICT' started by jambocraig, Mar 25, 2017.

  1. jambocraig

    jambocraig New commenter

    The prerelease scenario is available in the Edexcel website.

    Has anyone read it, and have any ideas for tables etc?
     
  2. teach_r

    teach_r New commenter

    Here we are for the last time. Again a fairly vague scenario and there could be a number of tables. For me one of the steers is that owners can book more than one dog in on the same booking. This is the first time this has been specified and I take it to mean that there would need to be a booking table linked to a bookingDog table in order to allow this to happen. Having looked at it there are many tables that could include: Staff, Dogs, Boarding package, Booking, BookingDog, Supplies and Owners. Too many here for one exam?
    So far I have got:
    Tbl Owner: OwnerID, (Name and contact details)
    Tbl Staff: StaffID, Firstname, Surname, contact details
    Tbl Dogs: DogID, Name, Breed, Size, FoodType
    Tbl Package: PackageID, Package name, Package description, package price
    Tbl Booking: BookingID, Date of booking, arrival date, departure date, OwnerID*
    TblBookindDog: BookingID*, DogID*, PackageID, StaffID, Kennel Number

    I realise this doesn't include supplies so any ideas welcome. Also confused about how to offer the extras on the packages.

    In the absence of any output requested I am assuming that they may ask for a report for members of staff showing which dogs they are looking after during a particular time period. They could also ask for an invoice.

    For practice data seems like most kennels charge on size of dog and package type. Extras can include longer walks, pick up/drop off, larger kennel and treatments.

    These are my initial ideas please develop and add.
     
    jambocraig likes this.
  3. hricketts

    hricketts New commenter

    Will there be a need for a Table for the daily activities?

    Also in the dog table there will need to be a field for age and a validation for this field as the kennel cannot take dogs under 3 months.
     
  4. teach_r

    teach_r New commenter

    Yep agree with the validation. If we include activities table it could be linked to the package and done using combo boxes on the booking form, but this seems like far too many tables. Pondering if could use option boxes on the booking form that show depending on which package selected?
     
  5. belch

    belch New commenter

    Perhaps you can add the additional activities on the tblbookingdog or tblbooking (depends if these extra activites are for all dogs booked by the same owner or the owner might want different activities for each dog ) as yes/no fields. On the managing boarding visit form some of these activites will need to be greyed out depending on the type of package chosen.
     
    Last edited: Mar 29, 2017
  6. mike125

    mike125 New commenter

    Hi,

    I've been looking at this too - I originally had a separate Package table, but I took it out and just put the information into the bookings table because if they ran a special offer for half-price luxury packages, changing the price in the packages table would mean all previous bookings would have their prices changed! Adding a 'special offer' type would make it hard to do a mailshot to all previous luxury customers with the offer for next time (for example, I try to keep things real!)

    The activities part is worrying me, because it seems to really over complicate things potentially, but I suppose there just could be *another* table:

    VisitActivities: dogID, visitID, activityID
    Activities: activityID, activity

    or maybe just do away with the activities table and put the activity in the VisitActivities table and validate it as a drop-down?

    Still seems like a lot of tables though.

    Also, I had staff members attached to the booking rather than individual dogs (as they'd probably be walked together anyway?) but that should be easy enough to spot once they get to see the data files!
     
  7. teach_r

    teach_r New commenter

    Yep I agree package and extras need to be in the bookingDog table as owners should be able to pick different packages and activities for different dogs.
     
  8. mike125

    mike125 New commenter

    I haven't done this exam for a few years and the bookingDog table is worrying me because I can't remember how to make a form to populate a 'New Booking' that allows the person booking to choose multiple dogs to add.
     
  9. Mantiega2

    Mantiega2 New commenter

    It states "Owners can choose additional activities depending on the package."

    This could be read as;
    • After the owner has chosen a package, additional activities can be selected.
    • The packages have different activities selected, the owner chooses the additional activities by selecting a different package.
    I am assuming the latter for now.
     
  10. belch

    belch New commenter

    Yep you are right hadn't thought about it in that way.
     
  11. teach_r

    teach_r New commenter

    if it was After the owner has chosen a package, additional activities can be selected and the activities were check box fields in the booking table then you can disable them all before update of the package and after update enable the correct ones depending on which package is chosen, so they can only chose activities available with the package.

    If it is the activities are set with the package then it would be a lot easier.
     
  12. amerlwd

    amerlwd New commenter

    Have you actually read the 2017 pre release?, if so i would appreciate it if you could provide it's details.
     
  13. amerlwd

    amerlwd New commenter

    Have you read the pre-release?
     
  14. jom202

    jom202 New commenter

    Hi all. Lots of interesting chat going on. Thought I'd share my thoughts so far. Attached is my database so far and a sort of 'mock' paper to help my students out. Feel free to use and share as appropriate. Appreciate any comments about the relationships etc.
     

    Attached Files:

  15. shaila12

    shaila12 New commenter

    I read the mock paper and it looks good . Do you have the answers to it? I am going go through it tonight .
     
  16. Jennie_reeve

    Jennie_reeve New commenter

    I agree with your relationships, but would have only put bookingID as the primary key of the booking table. do you have the text file that you are intending on giving the students?
     
  17. Charlie W

    Charlie W New commenter

    Your Bookings table needs sorting out, it would be inefficient for a booking containing many dogs (this data should be moved to a "BookedDogs" table).
     
  18. david_gale

    david_gale New commenter

    Hello everyone again. May I interject a few thoughts on what has gone before:
    • Looking at the ERD I can't see the need for separate tables tblBooking and tblBookingDog. These can surely all be located in one tblBooking table?
    • I can see that a printout to give to staff members might be useful, I suspect the report may well be the reorder for supplies as this is specifically mentioned in the pre-release and I can see no other reason for its inclusion.
    • The idea of the activities being rolled up in the package seems to me to fit the bill and avoid unnecessary complication. However it may be wise to allow for the possibility of other forms. Assuming that packages will be priced, this also opens the possibility of the report being the production of customer bills.
    Thanks for all your thoughts so far. I'll upload some files as I make them.
     
  19. david_gale

    david_gale New commenter

    Ah. I can see your point about the booking of many dogs simultaneously. I shall give this more thought.
     
  20. jambocraig

    jambocraig New commenter

    As said previously (in part):
    for tblBooking:
    - the PK is BookingID ONLY
    - there is no need for OwnerID in the table (its relationship s implied through tblDog)

    Many thanks to jom202 for the work on this so far...
     

    Attached Files:

    • Test.zip
      File size:
      85.8 KB
      Views:
      235
    annahalya likes this.

Share This Page