SQL Relationships - Reading Assignment

Blockchain Business Course
#785
  1. One2one; one2many; many2many (last two may be self-referencing)
  2. When there is exactly one unique attribute corresponding to each entry in an association. For example your email address and your social security number.
  3. The customer_id is one2one mapped to the customer name and address. This allows unambigous referencing of customers in other tables.
  4. Many 2 One: Multiple orders may reference the same item.
    One 2 Many: A single customer may order multiple items, or have multiple orders.
  5. Many to Many: An order may have multiple items, but the same item may be a part of multiple orders as well.
  6. It establishes the relation between two foreign keys: the order_id and item_id. It is a key-value pair representation of orders and items. Using other tables It could be flattened to a human readable list of order dates, associated customeres and full list of ordered items for example.
1 Like
#786
  1. What kinds of relationships can we represent in a database? 1-to-1, 1-to-many, many-to-1, many-to-many, and self referencing.
  2. When do we use “One to One” relationships? Give an example from the real world. When the same data repeats multiple times. A customer ID representing the customer’s unique information (name, address, etc) is the most common example). Or an employee ID and their government ID like SSN in the US
  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 unique to a particular customer, and can be placed into various tables (like customer addresses, customer orders, customer billing info, etc) so that info in each of the tables can be tied together for a given customer using that “Foreign Key”
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. Employees to the department they work in. Departments to the employees in them.
  5. Give an example from the real world when would need to use a “Many to Many” relationship. People and the parks they have visited.
  6. What does items_orders table do? Associates the items to each order. This would allow, for example, one to list all the items that a particular customer ordered, or conversely, to list all customers who ordered a particular item.
1 Like
#787

q1, One to one, one to many, many to one, many to many and self referencing.
q2, When we have customer and address tables and the address only belongs to one customer.
q3, It matches data in the address table and is a known as a Foreign key.
q4, E-commerce website where each customer may have zero, one or multiple orders. But an order can belong to only one customer.
q5, When each order can contain multiple items and multiple items can be in each order.
q6, One purpose, and that is to create a “Many to Many” relationship between the items and the orders.

1 Like
#788
  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.
    We use one to one relationships when there is one record that can only be related to one other record. For example, a student’s ID is only related to each student separately.
  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?
    There is a customer_id column in the customers table as it is a unique number or Foreign key which then links the particular customer with particular address_id.
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    This is the most commonly used type of relationship. A common example is someone ordering items on an ecommerce sight. Associated with their id, the customer could have multiple purchases.
  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Students to Classes is a real world example of many to many relationship as a student can register for many classes, and a class can have many students.
  5. What does items_orders table do?
    The Items Orders table associates the order info to items info so that it creates a many to many relationship.
1 Like
#789
  1. one to one relationships, one to many and many to one relationships,many to many relationships, and self-referencing relationships.

2). signing up on a website usually requires assigning a user ID to the customer and customer selects a username. The user ID and User name has a one to one relationship.

3). The customer_id in the customer table is used to create a relationship with the customer and the address.

4). A customer buys a variety of products and a product that has been purchased by many customers.

5). At a restaurant… Many people on the table ordering different food choices.

6). The items-orders table creates a many to many relationship between the items and orders tables.

1 Like
#790
  1. 1 to 1. 1 to many and many to 1. Many to many. Self referencing.
  2. When there can’t be more than 1 entry. One user one address
  3. The id is used as a foreign key to connect to a unique address.
  4. One ID can have many different devices in Apple store system.
  5. Each device has multiple attributes like a case, charger and etc.
  6. It creates the relationship between item and order tables.
1 Like
#791

  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.
    We use a One to one relationship is used when a data item is used to reference another table and ID relational Data. One example would be like using Social Security number in relation to which years taxes have been filed.

  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?
    Customer id was created to identify customer information across customer information tables. The number is used when a customer used, ordered products or services and id the customer with a unique number assigned to the customer also called a foreign Key

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When a single customer places multiple orders, this can also be reversed.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    When several auto parts are ordered several times over different orders or the same.

  6. What does items orders table do?
    Item orders table contains items ordered to a specific order.

1 Like
#792

  1. We can represent the following kinds of relationships in a database: One to One Relationships; One to Many and Many to One Relationships; Many to Many Relationships; Self Referencing Relationships.

  2. We use “One to One” relationships when the relevant attribute from one table can belong to only one other attribute from another table. For example, if each address (in one table) can belong only to one customer (in another table), this relationship is “One to One”.

  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 in order to uniquely identify each customer (so that said ID (or primary key) can be used to create relationships with other tables). In this instance, it is used in order to connect customers to addresses by referencing the “address_id” (a foreign key) within the “Customers” table. This “address_id” refers to the matching primary key record in the “Address” table (which is also called "address_id).

  4. You would need to use a “Many to One/One to Many” relationship when building an e-commerce website within which customers can make many orders, orders can contain many items, and items can have descriptions in many languages. For example, each customer may have zero, one or multiple orders, whereas an order can belong to only one customer.

  5. You would need to use a “Many to Many” relationship when building an e-commerce website within which each order can contain multiple items and within which each item can also be in multiple orders.

  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
#793

  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.
    A good use-case for a One to One relationship is when you have a person on a website and need to store an address from him. when you call the name of the person in the database the address gets retrieved.

  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?
    this is called a “Foreign Key” This is useful to connect table customers to table addresses.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A Many to One/One to Many can be useful when you have a customer that buys something from a site this costumer can be linked with Many other products.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A good use-case for a Many to Many relationship is when you have 1 item ordered by many people and these people can also have many products in their order.

  6. What does items_orders table do?
    This makes many to many connections between items and orders.

1 Like
#794
  1. In a relational database we can present 3 relationships:
    (a) One to One Relationships
    (b) Many to One and One to Many relationships
    © Many to Many relationships
  2. When a customer only orders one item per order that would be one to one as each order can only have 1 item with 1 customer
  1. It is a foreign key and it is used for referencing to the customer
  2. A customer which has multiple orders but all items are unique and do not get ordered by others
  3. Customers make different orders of the same items
  4. It references the items to their respective orders.
1 Like
#795

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.
    Connecting a user with an address, Customers on a e-commerce website.
    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 foreign key.

4 Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
A customer ordering multiple items

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

  2. What does items_orders table do?
    Creates a many-to-many relationship between items and orders.

1 Like
#796
  1. We can represent One to One, One to Many, Many to One, Many to Many, and Self Referencing Relationships
  2. We use “One to One” relationships when there is one unique user with one unique identifier. For example, each US citizen is uniquely identified with one unique Social Security number.
  3. The customer_id is a “foreign key” and it directly links to a unique customer. It is used to uniquely identify and create links or relationships to other unique data points.
  4. An example of a “One to Many” relationship would be one individual having multiple email addresses.
  5. An example of a “Many to Many” relationship would be an order at a restaurant may have multiple items, and each item could be in multiple orders.
  6. items_orders table creates a “Many to Many” relationship between the items and the orders.
1 Like
#797
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

One customer who makes one order and an order can only be assigned to one customer.

The customers table collects the unique customer_id’s and via foreign key connects the customers table to the address table.

Many orders can be done but only assigned to one customer.

Here there can be multiple orders and they can be containing many different items and vice versa.

It lists the order and item id and serves a many to many relationship.

1 Like
#798
  1. A few different types, such as: one-to-one, one-to-many, many-to-one, many-to-many and self-referencing.
  2. We use them to link to pieces of data that have a linear relationship with each other, such as a customer and their address.
  3. It is a foreign key.
  4. In the example of a customer making an order, “one” customer may place “many” orders.
  5. Each order can contain multiple items and each item can also be in multiple orders.
  6. Creates a many-to-many relationship between items and orders.
1 Like
#799

SQL Relationships – Questions


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.

Example: connecting a customer with his/her unique social security number (SSN) in the U.S.


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?

To uniquely identify each distinct customer with their respective unique ID.

That primary key in the customers table can be used to connect it with a customer with his/her respective address(es) in an address table (either based on a one-to-one or one-to-many relationship, if a customer happens to live at multiple addresses).


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

Many-to-one/One-to-many real-world examples:

  • A unique person/individual owning/living at more than one address in the world
  • Multiple orders being placed by the same unique customer.
  • One or more license plate numbers connected to the same unique driver (if he/she happens to own/drive multiple vehicles).
  • Multiple phone numbers belonging to the same person.
  • Multiple (bank) accounts connected to the same person.
  • Multiple school grades attributed to the same student.
  • One user/person speaking multiple languages


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

Many-to-many real-world example:

  • Multiple Amazon items connected to multiple Prime members


6. What does items_orders table do?

An items_orders table has creates a many-to-many relationship between items and their corresponding orders.

1 Like
#800
  1. What kinds of relationships can we represent in a database? One to One, 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 one id can only belong to one other id. Example – customer name can only match the customer 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? By the use of a Foreign Key. This allows the customer id and the customers to be tracked by a specific linked number.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. Amazon allows for both, where as you have one customer id that allows customers to order multiple items as well as make multiple orders.
  5. Give an example from the real world when would need to use a “Many to Many” relationship. Amazon where multiple people can order the same product
  6. What does items_orders table do? They create Many to Many relationships between items ordered.
1 Like
#801

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    Person and passport 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 foreign key in the customer table. This gives two tables a linking point and way to simplify and organize information

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

One user or person could speak many languages this is a many to one relationship

  1. Give an example from the real world when would need to use a “Many to Many” relationship.
    A list of items purchased by several customers that do partly include the same items.
  2. What does items_orders table do?

The Items_orders table has only one purpose which is to create a “Many to Many” relationship between items and the orders

1 Like
#802
  1. one to one, one to many, many to many, self referencing
  2. Spouses (assuming polygamy isn’t lawfull)
  3. Every entry in a database needs a unique key. This key can then be referenced in other entries which refer to that item.
  4. Biological father - biological children. Only one father per person, but one person can have multiple children.
  5. Siblings. If a person has 2 siblings, then those siblings also have 2 siblings.
  6. Creates the many to many relationships between items & orders
1 Like
#803
  1. What kinds of relationships can we represent in a database?
  • 1 to 1
  • 1 to Many
  • Many to Many
  • Self Referencing

  1. When do we use “One to One” relationships? Give an example from the real world.
    Where 1 piece of data can only reference 1 other piece of data, for example if 1 customer can only live at 1 address this would be One to One relationship.

  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 the Foreign Key and works with the primary key of a table to enforce referential integrity between 2 tables.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A restaurant bill, 1 bill would include Many order items

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    A restaurant table booking, there are Many tables that will have Many customers or bills on the same date

  5. What does items_orders table do?
    To create a Many to Many relationship between Items and Orders tables.

1 Like
#804

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

-One to one relationships are used to maintain information that is specific to one item, a link between two tables. ie. A club or an organisation would have your name as well as contact details.

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 is used for the customer name

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

-For example ordering a pizza it;s possible that the restaurant would have your information stored on a DB.

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

-If you were creating a DB for employees of a firm, where you had data for their, name, position, payscale etc.
What does the 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