Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access relationships

Discussion in 'Information Technology' started by John W, Jul 28, 2006.

  1. John W

    John W
    Expand Collapse
    Guest

    I have to explain to some students why tables need to be related. I thought
    they had to be related in order to run a query using 2 tables. But when i
    tried running a query using 2 tables not related i still got results. So
    what is the benefit from relating 2 tables
     
  2. Loading...


  3. Relating tables in access allows for cascading deletes and updates it is
    also fundamental for enforcing referential integrity which is it's primary
    purpose. Referential integrity simply states that 2 related tables, when
    properly done, will contain the following:

    o Both should have a primary key
    o One of the tables, the "child" table will have a foregin key to the
    "parent" table this key must match the primary key of the "parent" table OR
    be null.

    My database teacher drove that into our heads until our ears bled. :)

    The or part is critical too. So many times people forget that a foreign key
    can be null.

    http://en.wikipedia.org/wiki/Referential_integrity

    Gary Townsend
    Spatial Mapping Ltd.

    "John W" <John W@discussions.microsoft.com> wrote in message
    news:8158F955-F269-4F2D-B810-CDA3F1FF2630@microsoft.com...
    > I have to explain to some students why tables need to be related. I

    thought
    > they had to be related in order to run a query using 2 tables. But when i
    > tried running a query using 2 tables not related i still got results. So
    > what is the benefit from relating 2 tables
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi John

    If you have set up a relationship between your two tables and then you
    create a new query involving those two tables, then the tables will
    automatically be joined in the query. Otherwise, you would need to manually
    join the tables every time you create a new query. (Actually, later
    versions of Access try to pre-empt by creating a join if one of the tables
    has a field with the same name as the primary key field of the other table,
    but this is by no means foolproof).

    However, the biggest benefit of relationships is referential integrity (RI).
    This is a mechanism that allows the database engine to automatically take
    care of your data by preventing "orphan links".

    Let's say you have an order entry database with hundreds of customers and
    products and thousands of orders. The customers and products are identified
    in the order records by ID fields such as numbers, which match the primary
    keys in the Customer and Product tables respectively.

    Now, let's say a particular product with ProductID=123 has been sold
    thousands of times to many different customers - in fact there might be some
    unfulfilled orders for that product in the system. Without a relationship
    it is perfectly possible for the corresponding record to be deleted from the
    Product table. That would leave you with thousands of order records
    referring to an unknown product.

    RI prevents this from happening. You CANNOT have a foreign key value in the
    related table that does not exist in the primary table.

    Hope that makes it all clear for you :)
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "John W" <John W@discussions.microsoft.com> wrote in message
    news:8158F955-F269-4F2D-B810-CDA3F1FF2630@microsoft.com...
    >I have to explain to some students why tables need to be related. I
    >thought
    > they had to be related in order to run a query using 2 tables. But when i
    > tried running a query using 2 tables not related i still got results. So
    > what is the benefit from relating 2 tables
     
  5. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

    Relational Database Management Systems (that's what Access/Jet is) are
    designed along the lines of "Set Theory" from math. Tables are where
    like entities (sets) are gathered. Only items belonging to the same
    set can be stored in a given table and all entities within your
    application that are of that set must appear in that table.

    The most common relation that appears in an RDBMS application is a
    one-to-many relationship. The one side, also called the Parent side,
    usually has fewer records than the many side, also called the Child
    side. The means of marking the relationship is to store some piece of
    information that appears in the Parent record into the Child record.
    That's counter to how most people would assume things work. They
    assume that the parent would keep a list of its children. Not so,
    each child identifies its parent.

    When Referential Integrity (the strongest form of relation) is
    enforced the element of data from the Parent is its Primary Key. The
    Parents Primary Key is stored as a Foreign Key in each child record.
    Referential Integrity will block the creation of a key in the Child
    table unless a record for the Parent is already identified. That
    prevents the creation of orphan records in the child table.

    For example consider a very simplistic application for a school:
    There would be high level table for people. Common data about very
    person would be in that table. There would be an category indicator
    that would indicate whether this person is an Employee or a Student
    We've identified a couple of one-to-many relationships: from People
    to Employee and from People to Student. The Employee table records
    would record employee kinds of things and could further child
    tables/relationships for Administrator and Teacher. The Student table
    might have child tables for AcademicTrack and TechnicalTrack.

    Where does all of this lead? If you were trying to realistically
    track all of the above information in a "flat file" such as Excel,
    every record in the spreadsheet should have every element about every
    person. When you need to track tome unforeseen attribute you simply
    add a column ... Before you're very far along in the flat file
    parading you are storing and managing more redundant data than unique
    data.

    With properly designed entities/tables and relationships it is only
    necessary to include unique data. The only redundant data is the Key
    from the parent which is stored in the child. That's a very tiny part
    of the data.

    E.F. Codd of IBM was the creator of DB2 the powerful relational
    database product in the 1070s. He and C.J. Date created and published
    the "Rules for Relational Debases". You can find a paraphrase of
    those rules at www.mvps.org/access Look for "Ten Commandments". You
    might want to print it for your students.

    HTH
    --
    -Larry-
    --

    "John W" <John W@discussions.microsoft.com> wrote in message
    news:8158F955-F269-4F2D-B810-CDA3F1FF2630@microsoft.com...
    > I have to explain to some students why tables need to be related. I

    thought
    > they had to be related in order to run a query using 2 tables. But

    when i
    > tried running a query using 2 tables not related i still got

    results. So
    > what is the benefit from relating 2 tables
     
  6. Larry and Graham have hit it right on the nail only thing i would dissemble
    a bit about is that it isn't so much about not creating orphans as it is in
    creating proper links between the two. A true referential integrity
    relationship will allow the foreign keys to be null which would mean the
    child table records are orphaned. That being said though it is a very minor
    point but can be useful in situations where you want a category of unknowns
    then a null foreign key will do jus that.
    "Larry Daugherty" <Larry.NoSpam.Daugherty@verizon.net> wrote in message
    news:OrM607HeGHA.5040@TK2MSFTNGP03.phx.gbl...
    > Relational Database Management Systems (that's what Access/Jet is) are
    > designed along the lines of "Set Theory" from math. Tables are where
    > like entities (sets) are gathered. Only items belonging to the same
    > set can be stored in a given table and all entities within your
    > application that are of that set must appear in that table.
    >
    > The most common relation that appears in an RDBMS application is a
    > one-to-many relationship. The one side, also called the Parent side,
    > usually has fewer records than the many side, also called the Child
    > side. The means of marking the relationship is to store some piece of
    > information that appears in the Parent record into the Child record.
    > That's counter to how most people would assume things work. They
    > assume that the parent would keep a list of its children. Not so,
    > each child identifies its parent.
    >
    > When Referential Integrity (the strongest form of relation) is
    > enforced the element of data from the Parent is its Primary Key. The
    > Parents Primary Key is stored as a Foreign Key in each child record.
    > Referential Integrity will block the creation of a key in the Child
    > table unless a record for the Parent is already identified. That
    > prevents the creation of orphan records in the child table.
    >
    > For example consider a very simplistic application for a school:
    > There would be high level table for people. Common data about very
    > person would be in that table. There would be an category indicator
    > that would indicate whether this person is an Employee or a Student
    > We've identified a couple of one-to-many relationships: from People
    > to Employee and from People to Student. The Employee table records
    > would record employee kinds of things and could further child
    > tables/relationships for Administrator and Teacher. The Student table
    > might have child tables for AcademicTrack and TechnicalTrack.
    >
    > Where does all of this lead? If you were trying to realistically
    > track all of the above information in a "flat file" such as Excel,
    > every record in the spreadsheet should have every element about every
    > person. When you need to track tome unforeseen attribute you simply
    > add a column ... Before you're very far along in the flat file
    > parading you are storing and managing more redundant data than unique
    > data.
    >
    > With properly designed entities/tables and relationships it is only
    > necessary to include unique data. The only redundant data is the Key
    > from the parent which is stored in the child. That's a very tiny part
    > of the data.
    >
    > E.F. Codd of IBM was the creator of DB2 the powerful relational
    > database product in the 1070s. He and C.J. Date created and published
    > the "Rules for Relational Debases". You can find a paraphrase of
    > those rules at www.mvps.org/access Look for "Ten Commandments". You
    > might want to print it for your students.
    >
    > HTH
    > --
    > -Larry-
    > --
    >
    > "John W" <John W@discussions.microsoft.com> wrote in message
    > news:8158F955-F269-4F2D-B810-CDA3F1FF2630@microsoft.com...
    > > I have to explain to some students why tables need to be related. I

    > thought
    > > they had to be related in order to run a query using 2 tables. But

    > when i
    > > tried running a query using 2 tables not related i still got

    > results. So
    > > what is the benefit from relating 2 tables

    >
    >
     
  7. John W

    John W
    Expand Collapse
    Guest

    Thanks for the replies, they have all helped I should be able to answer my
    students questions now without embarassing myself.
    John W

    "Gary Townsend (Spatial Mapping Ltd.)" wrote:

    > Larry and Graham have hit it right on the nail only thing i would dissemble
    > a bit about is that it isn't so much about not creating orphans as it is in
    > creating proper links between the two. A true referential integrity
    > relationship will allow the foreign keys to be null which would mean the
    > child table records are orphaned. That being said though it is a very minor
    > point but can be useful in situations where you want a category of unknowns
    > then a null foreign key will do jus that.
    > "Larry Daugherty" <Larry.NoSpam.Daugherty@verizon.net> wrote in message
    > news:OrM607HeGHA.5040@TK2MSFTNGP03.phx.gbl...
    > > Relational Database Management Systems (that's what Access/Jet is) are
    > > designed along the lines of "Set Theory" from math. Tables are where
    > > like entities (sets) are gathered. Only items belonging to the same
    > > set can be stored in a given table and all entities within your
    > > application that are of that set must appear in that table.
    > >
    > > The most common relation that appears in an RDBMS application is a
    > > one-to-many relationship. The one side, also called the Parent side,
    > > usually has fewer records than the many side, also called the Child
    > > side. The means of marking the relationship is to store some piece of
    > > information that appears in the Parent record into the Child record.
    > > That's counter to how most people would assume things work. They
    > > assume that the parent would keep a list of its children. Not so,
    > > each child identifies its parent.
    > >
    > > When Referential Integrity (the strongest form of relation) is
    > > enforced the element of data from the Parent is its Primary Key. The
    > > Parents Primary Key is stored as a Foreign Key in each child record.
    > > Referential Integrity will block the creation of a key in the Child
    > > table unless a record for the Parent is already identified. That
    > > prevents the creation of orphan records in the child table.
    > >
    > > For example consider a very simplistic application for a school:
    > > There would be high level table for people. Common data about very
    > > person would be in that table. There would be an category indicator
    > > that would indicate whether this person is an Employee or a Student
    > > We've identified a couple of one-to-many relationships: from People
    > > to Employee and from People to Student. The Employee table records
    > > would record employee kinds of things and could further child
    > > tables/relationships for Administrator and Teacher. The Student table
    > > might have child tables for AcademicTrack and TechnicalTrack.
    > >
    > > Where does all of this lead? If you were trying to realistically
    > > track all of the above information in a "flat file" such as Excel,
    > > every record in the spreadsheet should have every element about every
    > > person. When you need to track tome unforeseen attribute you simply
    > > add a column ... Before you're very far along in the flat file
    > > parading you are storing and managing more redundant data than unique
    > > data.
    > >
    > > With properly designed entities/tables and relationships it is only
    > > necessary to include unique data. The only redundant data is the Key
    > > from the parent which is stored in the child. That's a very tiny part
    > > of the data.
    > >
    > > E.F. Codd of IBM was the creator of DB2 the powerful relational
    > > database product in the 1070s. He and C.J. Date created and published
    > > the "Rules for Relational Debases". You can find a paraphrase of
    > > those rules at www.mvps.org/access Look for "Ten Commandments". You
    > > might want to print it for your students.
    > >
    > > HTH
    > > --
    > > -Larry-
    > > --
    > >
    > > "John W" <John W@discussions.microsoft.com> wrote in message
    > > news:8158F955-F269-4F2D-B810-CDA3F1FF2630@microsoft.com...
    > > > I have to explain to some students why tables need to be related. I

    > > thought
    > > > they had to be related in order to run a query using 2 tables. But

    > > when i
    > > > tried running a query using 2 tables not related i still got

    > > results. So
    > > > what is the benefit from relating 2 tables

    > >
    > >


    >
    >
     

Share This Page