Databases Querying Relational Databases Joining Table Data with SQL Review and Practice

Type of relationships among three different table in Tack #3

Hi,

When we are solving Tack 3,

we find and build relationships among three different tables, books, lonas and patrons by uing Inner Join in the following way:

id from books table and book_id from loans table are values in common AND patron_id from loans table and id from patrons table are values in common.

If we remind the theory of Inner join, all values from different table should have in common so the Venn digram given in teachers note will make sense.

What I am get confused in this is we can find common values between books table and loans table respective between loans table and patrons table, but not between books table and patrons table. Then how can we say that they all three tables have 'common' values?

One more thing is what kind of relationship those three tables have?

Is it one to many in all three or can we say one to many between books table and loans table respective between books table and patrons table?

1 Answer

ivana kantnerova
ivana kantnerova
12,580 Points

this is the relationship M:N between books and patrons and always the M:N relationship is divided to the two 1:N relationships and a common table (in this case loans), one 1:N relationship is between books and loans and the second 1:N is between loans and patrons (always primary key (book.id, patron.id) and foreign key (loans.book_id,loans.patron_id)) and in the common table is the historical data .. who and what is borrowed and should be also when ...