Results 1 to 13 of 13
  1. #1
    JamesFredericks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Location
    Liverpool
    Posts
    5

    Help setting up tables

    Hi there ...I am in trouble. I am a business studies teacher that said he could use databases, and I can. But they expect me to teach SETTING UP DATABASES to computer students, which I have never done before. It is for a task that I have to give students, and I need an accurate answer in order to guide them through it better.

    The task says "A travel agent needs to create a database that contains information about flights that arrive at Barcelona Airport on a Saturday between 09.00 and 18.00, departing from Paris and Milan. The information to be contained in the database should include the point of departure, expected time of arrival and flight number. The database should contain at least three tables."

    There are various other tasks that need doing, but ultimately it just needs to be able to be queried on flights, such as "a report that shows all of the flights arriving from Birmingham on Saturday", and I think I'll be fine with them.

    I cannot for the life of me work out how to make this limited amount of fields into three tables, or more importantly, why.



    Any help would be appreciated.

    James

  2. #2
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    I cannot for the life of me work out how to make this limited amount of fields into three tables, or more importantly, why.
    I agree. Why three tables specifically? I suppose you could make two more tables for flight carriers and locations (destinations/arrivals) to be used as sources for lookup fields in your forms, but it's not really a good case study of database design...

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think this is a case where a student is expected to think about what would be entailed given the users of the database, not just what would be required to satisfy the requirements of the parameters that are given. So one needs to look at the larger picture and not focus on the trees. Such a database would required client identification, payments made/owing against the account, destination, departure and arrival points, itinerary numbers, flight numbers, packages purchased and so on. Here is a link to a data model site that I think you will find yourself perusing more than once if you're expected to meet other challenges as you go forward. There you will find several links to air travel data models. Here's one for travel agencies.

    If I were to restrict my answer to the parameters given, even then one has to think about where some of the data is coming from. For example, flight numbers might be from a linked service, and to get them into a local db would require a flight info table, complete with departure/arrival times AND carrier since a flight number might be the same for more than one carrier. Then a table for cities, one for countries (since a city name can apply to more than one country) and a linking table to assemble the flight data along with the departure/arrival dates, cities, countries and times (that's 4 tables just to satisfy the parameters of the challenge).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    James,

    You have been given/identified a specific problem, but the fundamentals of database design are the real concern in my view, and I think that is also what Micron said.
    Here are some sample tutorials that you should work through to understand and experience the database design process.
    Each tutorial comes with a problem statement, the process and a solution. Do work through some of the samples, follow the procedure and compare your answer with the solution provided. You will learn the basics that can be used with any database.

    Tutorials:
    Entity Relationship Diagramming
    Class information
    Catering Business
    Consolidated Widgets

    These are from Rogers Access Library other links on this page serve as excellent references. Especially Normalization and the 12 steps/nutshell article.

    Each tutorial should take about 30-60 minutes---less after you understand the process.

    Also, here are some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

    Intro to Database
    The Relational Model
    Data Modelling and the ER Model

    Here is a different set of videos by a different presenter to complement those above.
    These are free youtube videos from Mark Serva that deal with entity relationships, modelling, cardinality, ....
    The playlist is listed here.


    Good luck with your project.

    In addition to the data models suggested by Micron, I think this one Flight Schedules at the same site may provide some ideas.
    Last edited by orange; 10-24-2016 at 09:37 AM.

  5. #5
    JamesFredericks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Location
    Liverpool
    Posts
    5

    Thanks, but I'm still stuck (mostly for time)

    Quote Originally Posted by Micron View Post
    I think this is a case where a student is expected to think about what would be entailed given the users of the database, not just what would be required to satisfy the requirements of the parameters that are given.
    I know this is a bit cheeky, and I really WILL read the links that you sent, but I don;t suppose I could get some direct help on this one could I? ...I need to have a solution for Thursday in order to start them off in the right direction. What table/fields would I need? ...can anyone help me out? (I WILL learn it, as I have no choice, but I need to be good to go right now).

  6. #6
    JamesFredericks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Location
    Liverpool
    Posts
    5
    Quote Originally Posted by orange View Post
    James,

    You have been given/identified a specific problem, but the fundamentals of database design are the real concern in my view, and I think that is also what Micron said.
    Here are some sample tutorials that you should work through to understand and experience the database design process.
    Each tutorial comes with a problem statement, the process and a solution. .
    Don't get me wrong ...I am not looking for the world to do my job for me, but I am REALLY pushed for time here. Even if/when I look through the tutorials I am not going to have the confidence to say to the students, "right, this is what you do" or anything like it.

    So I know it's a bit cheeky, and I really WILL read the links that you sent, but I need something this week so I can start them off n the right direction, while simultaneously developing my own skills as you suggest. I don't suppose I could get some direct help on this one could I? ...I need to have a solution for Thursday in order to start them off in the right direction. What table/fields would I need? ...can anyone help me out? (I WILL learn it, as I have no choice, but I need to be good to go right now).

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I need to have a solution for Thursday in order to start them off in the right direction.
    In plain English what is the specific issue that you must find a "solution" to? What level/grade/class are you talking about?
    In very general terms: Is this
    -an Intro to Data management?
    -an Intro to database design?
    -an intro to systems analysis?
    -an intro to business systems requirements gathering?

    If you only have time for 1 tutorial, then do the Entity Relationship Diagramming.
    Here is the one page view for reference.

    The Hernandez Process In A Nutshell:
    One of the best database design books of our time is: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design By Michael J. Hernandez.

    Unfortunately, Hernandez goes into such great detail at times that the overall picture is lost. The following is a list of the major steps in the process. This list should not be use in place of reading the book. It is meant only as an adjunct to it.


    1. Create a narrative that accurately and in some detail describes the business
    2. Double underline each Subject and Direct Object in each sentence
    3. Single underline all the rest of the nouns that describe the subject or direct object
    4. Make a list of the Subjects. Try to determine which are duplicates or are not pertinent. This will be your Subject List
    5. Make a list of the other nouns. Try to determine which are duplicates or are not pertinent. This will be your Preliminary Field List (PFL)
    6. Group the fields in the PFL into logical groupings. Do each of these groupings have a corresponding value in the Subject list? If not, you missed a subject so you should add it.
    7. Go through the revised Subject list to see if there is any data that you should be storing about that subject that you are not. If so, add it to the field list.
    8. Place all the Subjects across the top of a sheet of paper and write the corresponding fields below them, check them off the list as you do.
    9. Now look at your tables and fields and compare them to the Ideal Table and Ideal Field:


    Elements of the Ideal Field
    --------------------------------------
    It represents a characteristic of the subject of the table.
    It contains only a single value
    It cannot be deconstructed into smaller components
    It does not contain a calculated or concatenated value
    It is unique within the entire database structure
    It retains all of its characteristics if it appears in more than one table.

    Elements of the Ideal Table
    ----------------------------------------
    It represents a single subject
    It has a primary key
    It does not contain multipart fields
    It does not contain mulivalued fields
    It does not contain calculated fields
    It does not contain unnecessary duplicate fields
    It contains only the minimum redundant data


    1. Create Primary keys and identify any Alternate Keys (see Hernandez)
      1. Note: Most tables will have Artificial Primary keys (Autonumber in MS Access).
      2. Alternate keys are used for Unique Indexes.

    2. Identify relationships between the tables. Be sure you can identify it in both directions:

    Each Customer can have One or More Orders
    Each Order can pertain to One and Only One Customer


    1. Resolve any Many-to-Many relationships with the addition of a Linking table and two One-to-Many Relationships.


    Order >----------------------< Products
    (Each Order can have Many Products)
    (Each Product can be on Many Orders)

    Order ----------< OrderProducts >---------Products


    1. Create your relationships, put the Primary Key of the table on the "One" side into the table on the "Many" side.


    One-To-Many
    Customer Order
    --------------- ----------------
    CustomerID (pk)------| OrderID (pk)
    CustomerName |-------< CustomerID (fk)
    ...other fields OrderDate
    ...other fields

    Many-To-Many
    Order OrderDetails Products
    --------------- ----------------- ----------------
    OrderID (pk)--------<OrderID (cpk)(fk) |---- ProductID
    OrderDate ProductID (cpk)(fk)>--| ProductName
    ...other fields ...other fields

    Good luck.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by JamesFredericks View Post
    What table/fields would I need? ...can anyone help me out? (I WILL learn it, as I have no choice, but I need to be good to go right now).
    If the data models at the recommended web site don't fit your immediate needs (or a mixture of two or more of them), I think I've already made table suggestions, but will elaborate. While Orange has good suggestions, I feel your need for expediency so I'll provide my quick initial thoughts on fields and hope that other knowledgeable folks can add to or improve those suggestions. Here's what I threw together. It's based on the assumption that all the information will be input by the user into tblFlightInfo and no table data is to be downloaded from some external source. Hopefully, it can provide the type of information specified in your challenge. The way I see it, on frmFlightInfo user types some data (flight number, dates, times) and picks others from combo boxes (country, city, airport, terminal) to build flight info records. I've likely missed something, but my quick evaluation tells me I should be able to record the required info of your OP. I don't for one minute suggest that this is a complete (or even accurate) data model for a travel agency - there's no way I can test my hypothesis.
    tblCities
    CityID autonumber
    City text
    CountryFK long tblCountry id
    tblCountry
    CountryID autonumber
    Country text
    tblCarrier
    CarrierID autonumber
    CarrierName text
    tblAirports
    AirportID autonumber
    CountryFK long tblCountry id
    CityFK long tblCity id
    AirportCode text
    AirportName text
    tblFlightInfo
    FlightInfID autonumber
    FlightNum long
    CarrierFK long tblCarrier id
    ArriveAPFK long tblAirports id
    DepartAPFK long tblAirports id
    DepartDate date/time
    DepartTrmnl text
    ArriveDate date/time
    ArriveTrmnl text

  9. #9
    JamesFredericks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Location
    Liverpool
    Posts
    5

    what if their all in the same country

    Thanks for that ...I think I can see hat you men, but I'm still struggling a bit. It's not that I'm thick (I hope), but more that this is for a level 3 course, and it needs to be done well. I threw in different destinations on my thread so it wouldn't be too recognisable, but they are all in this country (sorry about that, but I got nervous in case people figured out my problem, especially the awarding body).

    Does that change the structure of your solution? ...I imagine it does.

    I have looked at one of the tutorials recommended above, but the pressure of the situation is getting to me a bit, so I cant make head nor tale of it at present. But it has allowed me to follow your solution and at least understand what you are saying.

    If all the airports are in this country, what amendments would I need to make on your structure?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Which tutorial did you try? What was the problem?
    What exactly is a level 3 course?

  11. #11
    JamesFredericks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Location
    Liverpool
    Posts
    5

    Access course

    Quote Originally Posted by orange View Post
    Which tutorial did you try? What was the problem?
    What exactly is a level 3 course?
    It is an "Access To Higher Education" course, and so is just below level 3 (UK A' Level) standard cos it is 2 A' Levels split over six modules.

    I tried the Flight one that you recommended, but I am basically lacking confidence AND skills at this stage ...I need to be good to go and I am currently not, with a capital N.O. and T.

    The level isn't really the thing here, cos there are other things they have to do on the coursework, including other pieces of software, a report, plus other database things (reports, forms, etc), so the database component can be as simple as possible to satisfy the criteria (at least 3 tables, and report flights to Newcastle from Faro and Birmingham (the real cities from the task).

    I kinda get what the tutorial is saying, but without significant practice (actually carried out in Access to see the problems/solutions), then I am stumped.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I tried the Flight one that you recommended, but I am basically lacking confidence AND skills at this stage ...I need to be good to go and I am currently not, with a capital N.O. and T.
    ??? The Flight one I gave you
    http://www.databaseanswers.org/data_...utes/index.htm
    is a generic data model, not a tutorial.

    As I said in post #7

    If you only have time for 1 tutorial, then do the Entity Relationship Diagramming.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I threw in different destinations on my thread so it wouldn't be too recognisable, but they are all in this country (sorry about that, but I got nervous in case people figured out my problem, especially the awarding body).
    Well, if they're monitoring your activities, they already know you seem to be in over your head with little time to bring yourself up to the level you seem to need to be. If they're not, why lead us down some path if it doesn't reflect the issue?

    Anyway, I don't see any reason to limit the db to one country. The idea would be to make it easily expandable but I guess there wouldn't be a whole lot of country entries in your case. But really, maybe your best approach would be to come clean about your apparent inability to come through. IMHO, administrators don't like to have the wool pulled over their eyes, and even if you manage to scrape something together that will work, my guess is that you won't be able to answer questions since you don't really understand what's going on. Hard to earn the respect of a class if they realize you don't understand what you're teaching and expecting them to understand.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-28-2014, 02:42 PM
  2. Replies: 7
    Last Post: 01-10-2014, 08:11 AM
  3. setting up
    By jimmy321go in forum Access
    Replies: 2
    Last Post: 05-02-2012, 12:44 PM
  4. Setting Up Tables - On Right Track??
    By lspelman in forum Database Design
    Replies: 4
    Last Post: 05-08-2011, 06:31 PM
  5. Please Help with Setting up Fact & Dimension Tables
    By oPEEPINGTOMo in forum Database Design
    Replies: 1
    Last Post: 02-09-2011, 12:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums