SQL Relationships - Reading Assignment

Blockchain Business Course
#665
  1. one to one realtionships, one to many, many to many, self referencing
  2. it is used whenever an entry is only linked to another single entry e.g. name and telephone number
  3. as it can occur that persons have the same name, a relation between name and address could have more than one outcome. The customer_id helps assosiating a single address to each customer.
  4. taking the Facebook example: a person has multiple friends
  5. More than one student can be in a class and students can take several classes
  6. reducing the amount of data to be stored for matching orders and items
1 Like
#666

  1. What kinds of relationships can we represent in a database? - One to One, One to Many & 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. - We use a One to One relationship when a property or value is unique to single entry ie; bank card and pin 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’ column simplifies the customer information for faster recall and data management. The ‘customer_id’ is the primary key which is used to link the customer to an ‘address_id’. That ‘address_id’ links to a separate table that contains the customers full address information.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. - Many to One: EVO Carshare rentals. ‘Many’ cars can be associated to ‘One’ customers rental record. The inverse is true for the customer, ‘One’, and cars rented, ‘Many’.

  5. Give an example from the real world when would need to use a “Many to Many” relationship. - A recipe can contain multiple ingredients and each ingredient can be in multiple recipes.

  6. What does items_orders table do? - Creates a many to many relationship allowing someone to query what items have been included in a specified orders and what orders contain specified items.

1 Like
#667

  1. One to one relationships; One to many and Many to one relationships; Many to many relationships; Self referencing relationships.

  2. When each record in a table can belong to only one record in another table, it is “One to One” relationship. An example would be a relationship between student names table and student IDs table.

  3. The customer_id column serves as the primary key of the table. It can also serve as the foreign key column to be reference for the respective record in the address table.

  4. Contacts table to phone number table

  5. Movie watchers table to movie title table

  6. Items_orders table creates “Many to Many” relationship between the items and orders.

1 Like
#668

  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 Relationships.

  2. When do we use “One to One” relationships? Give an example from the real world.
    When we want the object to have only one relationship with another type of object in another table. Person should have only one passport and passport should have only one person.

  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?
    Because we can can use an id to create a relationship between different table rows.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    We would use this type of relationship in an e-commerce website, where customers can make many orders, but orders have only one customer.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    We would use this type of relationship in an e-commerce website, where orders have many items and items are in many orders.

  6. What does items_orders table do?
    It creates a relationship between the items and the orders.

1 Like
#669

  1. A database can represent the following relationships.
    -One to One Relationships
    -One to Many/Many to One Relationships
    -Many to Many Relationships
    -Self Referencing Relationships

  2. We use One to One Relationships whenever one record is uniquely associated with one record such as someone and their cell phone number.

  3. The author uses a column called customer_id as a foreign key to reference the table of customer ID’s.

  4. Many to Many Relationships can be used where there is more than one record associated with more than one record, such as customers and products.

  5. Many to One/One to Many Relationships is when one record is associated to many others like one one person owning multiple vehicles/multiple vehicles belonging to one person.

  6. The items_orders table keeps a Many to Many Relationship to keep track of items to orders.

1 Like
#670
  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.
    -When something is only connected to something else. A person and their current 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?
    -Is a key to connect to other datatypes in relation to one another.

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

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

  • Tracking and fulfilling orders. Each order can contain multiple items and each item can be in multiple orders.
  1. What does items_orders table do?
  • Determines a many to many relationship between the order id and item id tables
1 Like
#671

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

  2. When two things share a unique relationship on both sides like a name and an address.

  3. This is a foreign key and it is used as a way to link a table to a brand new table.

  4. One person may have multiple orders they have placed over time and one item could be a part of many different orders.

  5. Friends on Facebook. I have many friends and each of my friends also have many friends.

  6. Create a many to many relationship

2 Likes
#672
  1. One to One Relationships, One to Many and Many to One Relationships, Many to Many Relationships, Self Referencing Relationships
  2. only 1 relationship with an entry. It could be ID number and age
  3. It simplifies data. It is a reference to be used across all relationship tables.
  4. same customers can make many orders with many different items. Or… 1 customer, 1 orders, 1 item.
  5. Several stores receiving several different kinds of products to be sold.
  6. relationship between items and orders
2 Likes
#673
  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.
    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”.Individual people and their personal cell phone 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 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.
    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.
  3. 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.
    In these cases’ we would need to create “One to Many” relationships.
  1. Give an example from the real world when would 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
  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
#674
  • What kinds of relationships can we represent in a database?

One to one, one to many, many to one, many to many, self referencing

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

For example one customer ID linked to one customer address

  • 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 foreign key. It identifies underlying data as it is used as a matching record for the table to be identified.

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

For example if I buy multiple items on a clothing site

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

A specific item can be in many different orders, different orders can have the same item

  • What does items_orders table do?

Creates a many to many relationship between items and orders

2 Likes
#675
    • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships
  1. One to one relationship is when there is only 1 item that belongs to 1 customer eg. customer no. customer address.
  2. This is called a foreign key, correct me if I’m wrong, my understanding is a number (customer id) is easier to form relationships with other rows and columns then customer names with no structure.
    • Customers can make many orders.
  • Orders can contain many items.
  • Items can have descriptions in many languages
  • Many item in 1 order can only belong to 1 customer.
  1. 1 order can contain many items and 1item can be in many orders.
  2. items_orders table is to create a many to many relationship between items and orders.
1 Like
#676
  1. Relationships we can represent in a database are
    One to one/one to many/many to one/Many to many and self referencing relationships
  2. We use one to one relationships where there cant be more than one relationship between an entry. For example a government issuing citizens IDentity card numbers . the ID card is only related to that one person.
  3. The customer ID column is used to match the customer address as a one to one relationship, so that there is no mis -allocation of Data to wrong customer. The customer ID is used as a foreign key in all kinds of relationships, assigning the correct information to the correct customer. The existance of a relationship can be optional since this information can fit in one table.
    4, One to Many/Many to One relationships:
    One to many relationships are common in E-commerce /supermarket websites where :1. Customers can make many orders. 2. Orders can have many items. 3. Items can have multi-language descriptions. Each customer can have zero, 1 or many orders in a 1 to many relationship. But an order can belong to 1 customer.
    Many to one is when many customers may order for 1 item eg, at the start of Covid 19, many customers were ordering for toilet paper. This is a one to many example.
    5, Many to many relationship is when many customers or user ids order for multiple items.
  4. Item orders table creates a many to many relationship between items and orders. eg. when an order contains multiple items and when multiple items are contained in many orders, use of many to many relationships is solved by a join or intersection entity with 2 foreign keys distinguishing items and orders.
1 Like
#677
  1. What kinds of relationships can we represent in a database?

One to one, one to many, many to many, self referencing.

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

When there can only be one address for each customer, or one customer for each order id. This would be evident by an invoice number that was generated from a specific order for a customer who bought something from a company online.

  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?

For efficiency and the possibility of querying a different address table for that customer.

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

Let’s say I have a company called Sleeping Monk, that manufactures and sells herbal tinctures. I have 6 products. Any combination of, or all of those 6 products could be in any one order and any one product could be in many different orders.

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

This could be when many different customers have all purchased many different products

  1. What does items_orders table do?

Creates the ‘many to many’ relationship between items and orders.

1 Like
#678
  1. One to One, One to Many, Many to Many and Self Referencing Relationships.
  2. One to one relationships are typically used in the case of unique identifiers. e.g. the foreign key or a persons fingerprints.
  3. The customer_id field is a foreign key and provides a unique identifier within the database for the data set relating to a particular customer. The address_id (also a foreign key) is used to identify particular addresses and, by utilising a one to many relationship (one address can apply to many customers), reduce data redundancy.
  4. One costumer to their many orders.
  5. Many students to Many courses.
  6. It creates a Many to Many relationship between the items and the orders.
1 Like
#679

What kinds of relationships can we represent in a database?
JION queries such as “One to one”, “One to Many,Many to one”, “Many to Many”, “Self referencing” need to be represented in a SQL database.

When do we use “One to One” relationships? Give an example from the real world.
When the data in one table is in relation to only one other table in the database. Such a set up in uncommon in a SQL database.

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?
Rather then have the data on one table, the data is spread on two tables, one being the customer table and the other being the address table. They are both connected in relation to the address_id.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
When one customer can have only one custumer_id but can have the info of different orders in relation to that one custumer_id.

Give an example from the real world when would need to use a “Many to Many” relationship.
When one customer can have one custumer_id, the custumer_id can have different orders_id in relation to the custumer_id and also can have the items in the orders_id in relation to other custumer_id’s. When a relationship needs to be made across 3 or more tables

What does items_orders table do?
The items_orders table contains the info of each item set up in rows and columns.

1 Like
#680
  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.
    It is not used much. We could use it in case of many columns in one table. Sometimes is better to split such table in two or more tables.
  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?
    We need a primary key in order to establish relationship with other tables.
    Each customer has an address_id in the customer table and is used as a foreign key that is utilized as the common link between the Customer table and Address table. The foreign key will will match the Primary Key on the Address table.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When one customer can have only one custumer_id but can have the info of different orders in relation to that one custumer_id.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A specific item can be in many different orders, different orders can have the same item.
  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.
1 Like
#681
  1. One to One Relationships, One to Many and Many to One Relationships, Many to Many Relationships, Self Referencing Relationships
  2. A person and their address
  3. It’s a foreign key used to enforce referential integrity between data in tables.
  4. A customer ordering products from a website
  5. Each order can contain multiple items and each item can also be in multiple orders.
  6. Create many to many relationships
2 Likes
#682

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; and Self Referencing Relationships

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

When we have several products sold in one store and each product has a unique id.

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 primary key of the customers table that gives a unique number to each customer. We can then have 10 James Smith living in different addresses, but they will all have their unique customer_id which will then send the product to the correct address of whichever James Smith bought it.

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

When a customer buys several items in the same order and we need to send the products to the customer’s address (several items sent to one single address), or when several people live at the same address and we want to send them vouchers (several vouchers sent to one single address).

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

What does items_orders table do?

When we need to know the number of products people usually buy per order. The items_orders table creates a single row per item-order combination.

1 Like
#683

  1. a one to one
    b one to many
    c many to one
    d many to many
    f self referencing

  2. A unique person and its identity card number

  3. It is used to match the customer to a specific address. Cutomer_id and address_id are Foreign Keys that are used to tie the relation between the two data.

  4. Many customers ordering the same item ,and one order having multiple items.

  5. Customers having many orders,and orders having many items.

  6. It is used to create a many to many relationship.

2 Likes
#684
    • One to one
    • One to many and many to one
    • Many to many
    • Self referencing
  2. For instance my National ID and me
  3. The customer_id is called a foreign key in a different table. It’s a relationship “join” the tables.
  4. When we think about online shopping the same item can be bought by many different customers or many items can be bought by one customer
  5. If you take a music Lp , each song might one or more authors and each author might have written multiple songs.
  6. It links items with orders (many to many)
2 Likes