SQL Relationships – Reading Assignment
1. What kinds of relationships can we represent in a database?
- One to One Relationships
- One to Many and Many to One Relationships
- Many to Many Relationships
- Self-Referencing Relationships
2 a. When do we use “One to One” relationships?
When an entry/field such as an address can belong to only one customer, some identifier which soley belongs to that customer, this relationship is "One to One".
b. Give an example from the real world.
Individual people and their personal cell phone number.
3. In the article in the “One to One” section, the author has a column called customer_id in the customers table.
This is called a " Foreign Key " and it is used for all kinds of database relationships. Its primary role is enforcing referential integrity between data in two tables.
b. How is it used in order to connect customers to addresses?
It is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table. For example, a table called Customers has a primary key called customer_id . Another table called Customer Details has a foreign key which references customer_id in order to uniquely identify the relationship between the two tables.
4. Give an example from the real world when we would need to use a “Many to One/One to Many” relationship.
This is the most commonly used type of relationship. Consider an e-commerce website, with the following:
- Customers can make many orders.
- Orders can contain many items.
- Items can have descriptions in many languages.
In these cases’ we would need to create "One to Many" relationships.
5. Give an example from the real world when would we need to use a “Many to Many” relationship.
In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.
An example would be: Ordering a pizza with different toppings
6. What does items_orders table do?
The Items_Orders table has only one purpose, and that is to create a "Many to Many" relationship between the items and the orders.