SQL Relationships - Reading Assignment

Blockchain Business Course
#303
  1. We can represent these types of relationships in a database:
  • One to one
  • One to many
  • Many to one
  • Many to many
  • Self referencing

  1. We use one to one relationships when the values from each category match to only one other value. An example would be customers and addresses, in which each address matches to only one customer, and each customer matches to only one address.

  2. The customer_id is used along with the address id to create a relationship between customers and addresses.

  3. A Many to One/ One to Many relationship could happen with parents and children. A parent can have multiple children, while each of those children would have the same parent.

  4. A Many to Many relationship could be for subscriptions. A person could sign up for many subscriptions, and a subscription can have many different people subscribed to it.

  5. The items_orders table creates a many to many relationship between the items and the orders.

1 Like
#304

  1. What kinds of relationships can we represent in a database?
    There are one to one, one to many and many to one, many to many, and self referencing relationships.

  2. When do we use “One to One” relationships? Give an example from the real world.
    Where there is only single point of relationship to an entry. Real world would be like a person’s ID number, or social security number.

  3. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?
    The customer_id is a field with foreign keys assigned to every customer used to create unique relationships from the customer to other tables such as the addresses table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    For instance, many people may have the same club membership, but that same club may have many members.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Mcdonalds has many restaurants, although same in name, but have many customers also.

  6. What does items_orders table do?
    It links customers to their purchased items. It also allows for a many to many relationship to be established between items and orders.

2 Likes
#305
  1. What kinds of relationships can we represent in a database?
    • One to one, One to Many and Many to One, Many to Many and Self referencing Relationships
  2. When do we use “One to One” relationships? Give an example from the real world.
    • One to one relationship are use when linking a person to more information, for example address or phone number…etc.
    This type is not very common.
  3. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?
    • The address id refers to a matching record in the address table in this case, which is call “Foreign Key”, used for all kinds of database relationships.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    • One to Many / Many to one – for ecommerce or even social media. Customer – things bought or Profile – friends
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    • Many to Many – for ecommerce when you have bought the same items in different orders or perhaps when you have common friends in social media.
  6. What does items_orders table do?
    • It shows you in a quick way what orders are being placed for certain items, even if they are different orders.
1 Like
#306

One to one – one to many and many to one – many to many – self referencing

My name is Stallone and my id number belongs to only me

Each address belongs to only one customer (One to One) by adding address id to the costumer table that match the address table
enables it to have all kinds of data relationship (Foreign key)

Football team. The coach has 11 players and the 11 players have 1 coach

Orders are both connected (show relationship) to items and items order. One order can have many items and one item can have many orders
I order 11 pairs of shoes of different sizes but 1 type of shoe was ordered by 11 people

many order id to many item id , connects order to many items or connects item to many orders

1 Like
#307

What kinds of relationships can we represent in a database?

One to One
One to many
Many to many

When do we use “One to One” relationships? Give an example from the real world.

Signing up to ivanontech.com I can only use 1 e-mail adress to sign up, this e-mail adress is unique to my userID.

In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?

The customer_id is a foreign key which is used for all kinds of relationships in a database. Definition of a foreign key: Establish relationship between two tables.

So we got two tables, table1 = customer, table 2 = customer_details. In table 2 we got a foreign key this is the “customer_id” and the id_101 in table 1 we can find “customer_id 101” and information about customer in this case the address. So the foreign key in table 2 REFERENCE to primary key in table 1 to uniquely identify relationship between the two tables.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.

Customers can make many orders.
Orders can contain many items.
Items can have descriptions in many languages.

Give an example from the real world when would need to use a “Many to Many” relationship.

The definition of many to many relationship is when you’ve got multiple instances of both side of the relationship this can occurs conflicting data. The solution is to add a new table named intermediary table. So the users order can contain multiple items, and each item can also be multiple orders. Real world example would be to buy online database courses.

What does items_orders table do?

Items_orders table is the intermediary table and the only purpose this table has is to protect conflicting data situations and the solution is to create many-to-many relationships between the items and the orders

1 Like
#308

Great answers sir, well documented! Please keep them like that :muscle:

Carlos Z.

1 Like
#309
  1. One to one, one to many and many to one, many to many, self referencing relationships
  2. When one cell or entry can belong only to one other cell or entry. Example: customer and customers address
  3. Customer_id identifies a certain customer with a number, which can then be used to to connect him to his address.
  4. One to many: In a restaurant. A guest orders starter, main and a dessert.
    Many to one: a restaurant receives a multiple orders from different guests, which some or all can be the same
  5. Many to many: in a restaurant each guest can order multiple dishes and each dish can be in multiple orders
  6. To create many to many relationship
1 Like
#310

1 one on one relationship many to many relationship
2 an individual and his personal computer
3 Also called " Foreign Key " and it is used in all database relationships.
4 is when you are reading a book and has many chapters
5 is like reading several book with several chapters
6 it is used to determined the relationship many to many

1 Like
#311

  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. When do we use “One to One” relationships? Give an example from the real world.
    – We use “One to One” relationships when two things can only be related to each other. For example, a person and their finger print info or image

  3. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?
    – The Customer ID is the key that is used to indentify each customer and it is needed in the address table as a foreign key to tie the customer and address.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. Give an example from the real world when would need to use a “Many to Many” relationship.
    For example, a student can be enrolled in zero, one, or many classes

  5. What does items_orders table do?
    – The item order shows possible ways in which an order can have many items and how these items can be in several orders

1 Like
#312
  1. What kinds of relationships can we represent in a database?
  • 1 to 1
  • 1 to Many and Many to 1
  • Many to Many
  • Self Referencing Relationships
  1. When do we use “One to One” relationships? Give an example from the real world.
  • user name -> user ID
  • user name -> adress
  1. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?

it "translates various length of text (customer name) into standardised integers which are easier to reference to

  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • many order -> one customer ID
  • many order dates -> customer ID
  • many FB connections/friends -> to one user
  1. Give an example from the real world when would need to use a “Many to Many” relationship.
  • many customer ID -> mandy product IDs
  1. What does items_orders table do?

it records all orders without having all detailed data about customers and orders (only the ID) which can be looked up in the order/customer table

1 Like
#313

  1. One to One Relationships
    One to Many and Many to One Relationships
    Many to Many Relationships
    Self Referencing Relationships

  2. Where one table id can only belong to one table id. ie, one person can only have one distinct social security number. That number can’t be shared across many people. Conversely, that person can’t have multiple SSI numbers.

  3. to make a one to one relationship

  4. One grocery store could have many of the same types of syrup for example. That one syrup could also be sold at many different grocery stores.

  5. A series of items can contain many orders.

  6. creates a many to many relationship.

1 Like
#314
  1. What kinds of relationships can we represent in a database?
  • One to One
  • One to Many
  • Many to Many
  1. When do we use “One to One” relationships? Give an example from the real world.
    An example of a one to one relationship would be between a customer and a billing address. It would be impractical for a given customer to have more than one billing address.
  2. In the article in the “One to One” section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?
    The customer_id field is used to uniquely identify a given customer, e.g. in the case that there was more than one customer in the database with the same first and last name. The customer_id field is the primary key field in the customers table and the customer_id field in the addresses table is the foreign key field. This field will uniquely associate a customer to an address.
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    An example of a One-To-Many relationship would be a customer having multiple email addresses.
  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    A example of a Many-To-Many relationship would be that a sales order can be related to a multiple product SKUs, while a given product SKU can be related to multiple sales orders.
  5. What does items_orders table do?
    The items-orders table simply relates an order to an item in this many-to-many scenario. This table allows the database to be “normalized” so that the data is stored in the most space efficient manner possible.
1 Like
#315

  1. one to one, one to many, many to one, many to many, and self referencing.

  2. When there is only one record associated with another, such as person and an address. One person has one address and one address belongs to that one person. That was the example given although multiple addresses could belong to a person and multiple people could share an address so maybe not the best example.

  3. The customer_id is used as a reference to a particular customer, this is what’s referenced as a foreign key from other tables when they are referring to a row in the customer table.

  4. A many to one example would be a customer has a single order which contains many items.

  5. A many to many relationship might be used for a group of books and authors. A book may have multiple authors and and author may have written multiple books.

  6. Creates a many to many relationship between the items and the orders.

1 Like
#316

SQL Relationships

Centralized IT Infrastructure

What kinds of relationships can we represent in a database?

one-to-one, one-to-many, and many-to-many.

When do we use “One to One” relationships? Give an example from the real world.

In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.

In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?

The author has a column called customer_id in the customer table because each customer will have one id. Now this ID column can be used on the address table to connect each customer to one address.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.

An example of Many-to-One is: where one project can have more than one student working on it. A team of five students in a college in assigned a project that they need to complete in let us say one month. This states a relationship between two entities Student and Project

In a One-to-Many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders .

Give an example from the real world when would need to use a “Many to Many” relationship.

Many-to-many relationship : When one row of table A can be linked to one or more rows of table B, and vice-versa. Real Life Example : A user can belong to multiple communities, and a community can have multiple users. A practical example to many-to-many is the courses taken in a college.

What does items_orders table do?

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. The Items_Orders table has only one purpose, and that is to create a “Many to Many” relationship between the items and the orders.

1 Like
#317

Loved your answers. Easy to understand. Great work. :clap:

Carlos Z.

#318

1- One to One, One to Many, Many to One, Many to Many & Self-Referencing
2- An example of one to one in real life might be social security numbers in the US. Each person should only have one of those!
3- We should always have an id # in a table in case we want to connect the relationship of that item to an item in another table. In this case, we want to be able to connect the customer with their address. For example, 101 & 301.
4- We can talk about phone numbers as a one to many situation. So, a customer can have multiple phone numbers, but those phone numbers can only belong to one customer.
5- Here, maybe we could talk about YouTube channels and subscribers to give an example of the “Many to Many” relationship. A subscriber can be subscribed to many channels, and a channel can have many subscribers. Also, a YouTuber can have many channels as well. I hope that’s a good example!
6- It connects the order numbers with the various items in that order.

1 Like
#319
  1. What kinds of relationships can we represent in a database?
  • one to one
  • one to many and many to one
  • many to many
  • self referencing
  1. When do we use “One to One” relationships? Give an example from the real world.
    social security number
  2. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?
    This is introduced as “foreign key” and is used to connect to address_id
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    a resume
  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    A shopping list
  5. What does items_orders table do?
    To create the “many to many” relationship between items and orders
1 Like
#320
  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

  1. When do we use “One to One” relationships? Give an example from the real world.
    Social Security numbers

  2. **In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? To reference the address in another table. How is it used in order to connect customers to addresses? With a relationship between the two tables.

  3. **Give an example from the real world when would need to use a “Many to One/One to Many” relationship. Customers and phone lines

  4. **Give an example from the real world when would need to use a “Many to Many” relationship.
    Customers and phone lines and phone plans

  5. What does items_orders table do?
    It creates the relationship between Items and Orders.

1 Like
#321
  • One to one
  • One to many and many to one
  • Many to many
  • Self referencing

when data from 2 tables can belong to each other. example name and mobile numbers

It is a primary key, it is required to establish a connection between data tables.

In a mechanical product assembly, there can be many different components used to build the whole product. many single components can be used many ex: nut and bolts

many to many can be used in a car assembly database

It creates many to many relationship between the datasets.

1 Like
#322
  1. What kinds of relationships can we represent in a database?
    One to one, many to one and one to many, many to many, Self-Referencing.

2.When do we use “One to One” relationships? Give an example from the real world.
when the entry or field belong to only one example my licenses number.

  1. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses.
    Because it is the foreign key that makes possible the interaction between tables

  2. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When I want to buy different items in a web store I use my client Id for buy multiples items.

5.Give an example from the real world when would need to use a “Many to Many” relationship.
When many items are in multiple orders.

  1. What does items_orders table
    this relate items table with the orders id
1 Like