Relation between/among Tables/Entities: Definition and Condi

[WWGD]

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.

 

[Borg]

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).

 

[WWGD]

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).

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?

 

[Samy_A]

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.

 

[Borg]

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?

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.

 

[WWGD]

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.

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.

 

[WWGD]

Please ignore my previous, it has been answered.