Database Modeling: Relationships

Question 1

What are the three kinds of relationships in a RDBMS?

Answer 1

1. One-to-one (not used much)
2. One-to-many (dwarfts them all)
3. Many-to-many (used a bit)

Question 2

What is another name for a relationship in a RDBMS?

Answer 2

Cardinality

Question 3

Describe the linear steps for modeling a DB?

Answer 3

1. Sketch your entities (which will become your tables)
2. Define the data (attributes) for those entities. These become the columns in the tables.
3. Determine which columns will be the primary keys and if they need to be generated (surrogate keys).
4. Define your relationships.

Question 4

What is a foreign key?

Answer 4

A foreign key is a key to a row in a different table. It is a primary key used in a table other than its own.

Question 5

What’s a good way to make sure you have a true one-to-one cardinality?

Answer 5

Look at the relationship from both ways. One-to-one relationships are rare and are usually one-to-many or many-to one.
Usually the two tables with a one-to-one relationship can be merged into the same table, negating the need for cardinality.

Question 6

Can a relational DB directly represent many-to-many relationships? If not, how are many-to-many relationships handled?

Answer 6

No. Many-to-many relationships require a linking (junction, joining, cross-reference, etc) table. This joining table only has two columns, one for each foreign key from each of the tables it joins.
Usually linking tables do not have a primary key.

Question 7

What is referential integrity?

Answer 7

Enforcing the rules of a relationship; maintain the validity of data between tables. For example, if an Order table requires a customer ID, referential integrity will not let you create an order without a customer. Same thing with delete: the DBMS can be configured to prevent deleting any customers who are linked to orders.