Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Relation between/among Tables/Entities: Definition and Condi

  1. Dec 6, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi All,
    My apologies, I think I may have asked this question already, but I could not find it. Here it goes:

    I have seen the usage of the word 'relation', specifically a relation between tables but I have not seen a formal definition. From what I understand, tables X,Y are related to each other if some attributes A_X used in X need some of the attributes A_Y in Y in order to be "fully understood" , meaning some attribute A_yi in Y is needed as context to make sense of some attribute A_Xj in X. Formally this is expressed by the fact that an attribute in Y is used as a FK (Foreign Key) in X.

    As an example, I guess we could have:
    X is "Workers": Worker_Num (PK), Worker_FName, Worker_LName, Worker_DOB and

    then Y could be for info on workers:

    Work_Info: Work_ID , Work_HourlyPay, Work_Skill (Assuming each has just one skill), Work_Hrsweek (Assuming these are constant), Worker_Num (FK).

    Then Y's existence and meaning depend on X. So we say X,Y are related? And this is made formal by the fact that Y contains the FK Worker_Num ?

    Is this correct? Is there more/less to it than this?

    Thanks.
     
  2. jcsd
  3. Dec 6, 2015 #2

    Borg

    User Avatar
    Science Advisor
    Gold Member

    I wouldn't say that the other table is always required. Your examples are mainly one-to-one relationships. You could also have a WorkerPhone that may not have any entries for a worker or may have multiple entries (one-to-many).
     
  4. Dec 6, 2015 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, Borg, but then when do I say that two tables are related? I guess I am assuming the tables have been normalized, so if WorkerPhone had multiple entries wouldn't it have become (at least part of ) a new table?
     
  5. Dec 6, 2015 #4

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    The way I use it is that two tables are related when the foreign key in your Y table has to correspond to an existing primary key in your X table.
    As Borg said, the Y table can contain 0,1 or more records with the same value in the foreign key, but that value must correspond to an existing primary key in the X table.

    The X table could be your Customers table, with primary key cust_pk.
    The Y table could be your Contacts table, listing various contacts related to customers. Contacts has a cust_pk foreign key (the name is immaterial), and the value entered in that field must correspond to an existing cust_pk in Customers.
     
  6. Dec 6, 2015 #5

    Borg

    User Avatar
    Science Advisor
    Gold Member

    Yes they would have to be in a separate table. You would then need a foreign key to the main worker table to establish the relationship. When you build the tables you can enforce the relationships so that you can't build a phone record for a worker FK that doesn't exist.
     
  7. Dec 6, 2015 #6

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Yes, thanks, of course I meant that the FK of Y has been exported to Y from X, possibly containing 0,1 or more records as you said. Can you think of a case where we would have 0 records?
    Thanks.
     
  8. Dec 6, 2015 #7

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Please ignore my previous, it has been answered.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Relation between/among Tables/Entities: Definition and Condi
  1. Writing a Table (Replies: 6)

  2. Matlab and tables (Replies: 0)

Loading...