SQL Relationships - Reading Assignment

Blockchain Business Course
#404

SQL Relationships :people_holding_hands: :two_men_holding_hands: :two_women_holding_hands:

  1. What kinds of relationships can we represent in a database?
  • One to One
  • One to Many / Many to One
  • Many to Many
  • Self-Referencing

  1. When do we use “One to One” relationships?
    It’s not used very frequently because it can only represent very simple and straight forward relations that can usually be conflated within a single record of a table. A particular attribute value in such a One to One relationship can only be connected to a single entry, e.g. the address that belongs to a customer.

  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?
    It serves as a Foreign Key through which records in one table can be uniquely identified within another table.

  3. Give an example from the real world where we would need to use a “Many to One/One to Many” relationship
    If we want to store family relationships in a database each person can have multiple children or siblings. Conversely, multiple children will have exactly one mother.

  4. Give an example from the real world where we would need to use a “Many to Many” relationship
    In a database that tracks orders and products each order may be associated with multiple products and any given product can be part of different orders.

  5. What does items_orders table do?
    In this case it creates a relationship (Many to Many) between the entries of two distinct tables

1 Like
#405

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

  2. an example is when I make a table if all my customers and their unique ID number. Each unique ID number can only belong to each customer so this represent a “One to One” relationship.

  3. The customer id is unique to each and every customer so this allow the database to identify customer by their id rather than their name since it is common for people to have the same name. “address_id” in the Customers table, refers to the matching record in the Address table. This is called a “Foreign Key” and it is used for all kinds of database relationships.

  4. An example will be a table to record a list of hair products that a customer ordered form anonline store.

  5. An example will be trying to retrieve a customer’s order in the past 3 months.

  6. 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
#406

Reading Assignment: Relationships Databases. (Answers)

  1. Customer orders and item orders.
  2. In customer tables and address tables.
  3. Repeats customer_Id relationship data. In a One to One relationship.
  4. Amazon.com / e-commerce/websites.
  5. Hyperledger and Facebook.
  6. Contains data query of both items and orders.
#407
  1. 1.1 1…n, n…m, 0.n
  2. Person -> National ID
  3. To identify all the addresses that belong to a customer, 1…n
  4. A person has multiple fiat currencies in the wallet and that money can buy multiple items in a store.
  5. A course can be taught by different professors in differents languages
  6. Store the items of multiple orders
1 Like
#408

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    When there is a single relationship between 2 items such as a Customers Name and their address.

  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 unique id to the customer. It is then referenced in the order table to provide the link back to the customer for orders. It can also then be used as the Foreign Key for join queries.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    One Customer can have many orders.
    Many Orders can buy the same one item.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many Customers can buy many items just as many items can be on many orders.

  6. What does items_orders table do?
    It creates the relationship between the Items and Order tables.

1 Like
#409
  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.

If each address can belong to only one customer, this relationship is “One to One”

  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?

This is called a “Foreign Key” and it is used for all kinds of database relationships. We will cover this subject later in the article.

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

You can order feed at DoorDash with multiple meals, but it has to belong only to one customer who ordering
5. Give an example from the real world when would need to use a “Many to Many” relationship.
Multiple customers ordering multiple orders

  1. 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.
2 Likes
#410

  1. What kinds of relationships can we represent in a database?
    One to one
    One to many
    Many to one
    Many to many
    Self referencing

  2. When do we use “One to One” relationships? Give an example from the real world.
    When an information is linkd to another in single way e,G. Customer Address Table.
    Customer 1 – Address Customer 1
    Customer 2 – Address Customer 2
    ….

  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?
    Due to referential integrity between data in two tables.
    Because it is a unique ID which is stored as an identifier in both tables these table rows with matching customer ID are referring to each other.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many to one: Article – Article Groups – an article group can have many articles but an article just belongs to one group
    One to many: customer – order ID – an order is linked to a single customer but a customer can be linked to many order IDs

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many to many: Customer – Article relation. A customer ID can be assigned to many articles and article can be assigned to many customers

  6. What does items_orders table do?
    The purpose of items_Orders is creating a many to many relationship.

2 Likes
#411

Type of relationships:

  • One to One Relationships = for example: if you store the place of birth in a separate table you have a one on one relationship with the person table
  • One to Many and Many to One Relationships= for example the table person related to his redidential address whihc could be many
  • Many to Many Relationships = for example if you have two tables one with make persons and one with female, when you have the friendship relationships as a relations you can have many to many
  • Self Referencing Relationships = when you refer a person to another person and when you store all these persons in one table.

The item_orders table creates a “Many to Many” relationship between the items and the orders.

2 Likes
#412

  1. One to One relationships
    One to Many and Many to One relationships
    Many to Many relationships
    Self-Referencing relationships

  2. When unique to a user, like an address, phone or bank account no.

  3. It acts as the Foreign-Key to build a relationship between (in this case 2) tables.

  4. One to Many and Many To One are the most used type of relationship. For Instance an E-commerce website has many tables to one customer, for example:

  • Customers can make multiple orders.
  • Orders can contain several items.
  • An Items can have descriptions in variable languages.

  1. Many to Many relationship can be the customer having a different delivery address as the billing address, or a username that is different from his/hers birth name.

  2. It builds a Many to Many relationship between the items table and the orders table.

2 Likes
#413

  1. One to One, One to Many, Many to One, Self Referencing

  2. When we want to link two unique things together we use One to One. Customer and address. Address and phone number.

  3. Customer id stores the customer information. In this case, Name and Address. Both the Name and address tables will reference this number and be linked together.

  4. An e commerce site with customers who have invoices with many items on them. Conversely, any item can appear on multiple invoices.

  5. In college, where there are many students and many classes

  6. Creates the many to many relationship for us.

1 Like
#414
  1. One to one, Many to one & one to many, many to many and self-referencing
  2. When there is a unique relationship of one entity connected to another one, such as a person living who lives in one unique place.
  3. The customer_id is a unique identifier to find that customer also in other tables.
  4. One customer can make many orders.
  5. Each order can contain multiple items, but each item can also be part of multiple orders.
  6. To create a many to many relationship between entities, in this case items and orders.
1 Like
#415
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

When for example a email address can only be linked to one person. People can use that to stop making unlimited accounts on your website with the same email address

With a for Foreign Key.

When you want to buy something at amazone Your order can have multiple items in your order butt your order can only belong to one costumer(you)

Renting a movie. you can rent movies and many people can rent the same movies

It connects items and the orders which make it a many to many relationship

1 Like
#416
  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.

When there is one relationship between tables. Eg, table “citizens” and table “ID register”.

  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?

Each customer_id has a corresponding address_id which refers to the “address” table.

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

A travel agency. Tables Customers and Destinations.

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

Student groups. Different students can participate in different groups.

  1. What does items_orders table do?

It creates a “Many to Many” relationship between the items and the orders.

1 Like
#417

  1. What kinds of relationships can we represent in a database?
    There are several types of database relationships- “One to One”, “One to Many and Many to One”, “Many to Many”, “Self-Referencing”.

  2. When do we use “One to One” relationships? Give an example from the real world.
    An ideal scenario would be the use of a customer database. When utilizing a table for customers and another for their address-this is a “One to One” relationship"

  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 provides creates a Foreign-Key to connect the two tables.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    An e-commerce website with several customers, orders, and items would requires “One to Many” relationship.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Type of database relationship used when there are multiple instances on both sides of the relationship between the tables.

  6. What does items_orders table do?
    This establishes a "Many to Many relationship between the items and the orders.

1 Like
#418
  1. One to one - one to many - many to many - self referral.
  2. You may want to match a cutomer name in one table with the cutomer address in a different table.
  3. This is the key. A unique identifier for that entry that can be referenced by other tables.
  4. My private keys for different types of internet gold (many) in relation to my wallet (one)
  5. I’ll have to use the one from the article, one customer but different orders with overlapping stock items.
  6. It creates the many to many relationship.
1 Like
#419
  • one to one relationships
  • one to many and many to one relationships
  • Many to many relationships
  • Self-referencing relationships

a).When an entry/field such as an address can belong to only one customer, some identifier which solely belongs to that customer, this relationship is “One to One”.
b).Individual people with there own tax file number.

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

1 Like
#420

1- What kinds of relationships can we represent in a database?
One-to-one relationships, One-to-many & Many-to-one relationships, Many-to-many relationships and self-referencing relationships.

2- When do we use “One to One” relationships? Give an example from the real world.
When each item of one type is matched with one and only one item of another type. For example: names / driver’s license numbers.

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?
Customers and their real-world residential address could be stored in two separate tables rather than one: done via one table with columns for name and a reference code or id (also called a “foreign key”) and then another table with their customer reference code and their address . (Each address belongs to only one customer).

4- Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
How about on e.g. an Amazon account in which many different addresses are stored i.e. address(es) for the user plus also addresses for friends / family the user often sends gifts to?

5- Give an example from the real world when would need to use a “Many to Many” relationship.
A retail operator would use this, they would have many products in a warehouse ready to ship – and they would also be shipped to many different customer addresses over a period of time.

6- What does an items_orders table do?
It creates a many-to-many relationship between items and orders.

1 Like
#421
  1. One to one, one to many, many to many, and self-referencing.
  2. A person and their social security number.
  3. It is a foreign key used to connect each address with a specific customer.
  4. A person and their family tree.
  5. A group and people working at different locations.
  6. To create a “Many to Many” relationship between the items and the orders.
1 Like
#422

  1. One to One
    One to Many
    Many to Many
    Many to One
    Self Referencing

  2. Car -> Car oweners

3)This is known as a Foreign Key and refers to a primary key in another table.

4)A person can own multiple items of clothing so you would have:

“Person” table — “clothing tables” (shirt, pants, shoes, etc)

  1. Things can have multiple relationships:

person – cloths – shirts
– pants
etc

  1. Creates the many - many relationships between items and orders
1 Like
#423

  1. What kinds of relationships can we represent in a database?
    One to One
    One to Many
    Many to Many
    Many to One
    Self Referencing

  2. When do we use “One to One” relationships? Give an example from the real world.
    A person and their Age

  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 provides a Foreign-Key to connect the two tables.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    An online cloth store with different items , orders and customers.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Uber pickup location where it can be from any address to any address for one user or several if he shares the ride.

  6. What does items_orders table do?
    It creates “many to many” relationships between the entries of 2 distinct tables.

1 Like