SQL Relationships - Reading Assignment

Blockchain Business Course
#464
  1. One to One Relationships, One to Many Relationships and Many to One Relationships, Many to Many Relationships and Self-Referencing Relationships.
  2. When one attribute of a table can only belong to one other attribute of the table. Let’s say we have on person that has one driverlicense.
  3. The customer_id is used as a key (Foreign Key). Every key allows the referencing of data across tables. Since there is only one adress that matches on customer the customer id also refers to other attributes.
  4. Different insurances can only belong to one insured person.
  5. You have C02 emissions from a lot of entities and CO2 sequestration from a lot of forests. If you want to create an overview on the global CO2 balance you need to track emissions and sequestrations in “Many to Many” relationships.
  6. It creates a "Many to Many "relationship between items and orders.
1 Like
#465
  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.

It is used to connect one record with exactly one record in another table. Technically the data could also have been included in the first table. However it could save space because if foreing key is null than there is no row created in the other table. The example used is that of customer address. It’s hard to think of something that only has a one to one relationship. Even something as unique as a passport has exceptions like people with multiple nationalities. I would mostly consider using this where there is a large subset of information that would be optional. For instance you have a user in a detabase that may or may not have an extended bio with a photograph and large pieces of text.

  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?

The customer_id identifies the customer record and can be used in other tables to reference these records. It is not used in this section cause it would create a one to many relationship with the addresses if addresses would have a foreign key to customer_id. Which in the real world would make sense. It is used in the next section to connect multiple orders to a customer.

  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Well a customer could have many orders is a pretty good one.

  2. Give an example from the real world when would need to use a “Many to Many” relationship.
    Students can take many courses, each course can be taken bij many students.

  3. What does items_orders table do?
    It holds the data for the many to many relationship between items and orders.

1 Like
#466
  1. A database can represent many relationships: one to one, one to many, many to one, many to many, as well as self referencing relationships.
  2. “One to One” relationships can be used for records that only relate to one other record on a table in the database. For instance, my name would have a relationship with my home address in a different table of the same database.
  3. The “customer_id” entry serves as a identifier key for that table. And further serves as a foreign key in another table. The foreign key points to the record on the customer table which identifies a specific customer and therefore links the address to that unique customer.
  4. “Many to One/One to Many” relationships can exist between parents and children. Many parents (2) to one child. One Child to many parents. One parent to many children.
  5. “Many to Many” relationships could be used to catalogue a mechanic’s inventory of many oil filters to many different types of cars.
  6. items_orders table in this example describes where many items can be in many orders, and each order can contain multiple items.
1 Like
#467
  1. one-to-one, one-to-many or many-to-one, many-to-many, self referencing.
  2. A person can only have one father or mother related to him/her.
  3. A foreign key connects data between tables to create manageable tables that relate to one another.
  4. A project manager handling different mobile dapp projects each with different frontend developers using ios and android applications.
  5. A blockbuster movie production with the actors, directors, producers, etc working together to create the movie.
  6. A many-to-many relationship between items and orders.
1 Like
#468

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    One to one relationships is used to associate 1 record in a table to 1 record in another table. An example of a One to One relationship is when the “Name” of a person and the person’s “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 column is the unique identifier (called a “Foreign Key”) used to create a relationship between the “CUSTOMERS” table and “ADDRESSES” table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    In an online shop database where one customer may have multiple orders but an order may only belong to one customer.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    In an online shop database where one order may have multiple items and each item may belong to multiple orders.

  6. What does items_orders table do?
    The items_orders table creates a “Many to Many” relationship between items and orders.

1 Like
#469

1 Customers make orders, Orders contain items, Items contain products
2 Child—Father, Child --Mother
3 Addresses is a separate table. Connect address_id to Customer_id
4 Father-Child1, Father–Child2
Child1, Child2, child3 to Mother
5 All bycicles off a family can be used by all fam.members
6 Connect an order to an item

1 Like
#470

SQL Relationships -Reading Assignment:

  1. What kinds of relationships can we represent in a database?
    We can represent following relationships:
  • 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.
    a) One to One relationship would be used for data that is unique to an individual entry. For example customer would typically have only one address for his name.
    b) an example of “One to One” relationship would be a cell phone number of a person.

  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?
    Customer_id assigns an ID to each customer, this now can be used to create relationships with address table by use of a “Foreign Key”.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When multiple clients purchase same type of an item in the online store “Many to Item Nr1.”
    Each order for every customer can also have Many items on each (One) order (One to Many).
    Also, items listed on website could have descriptions of specs listed in multiple languages.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    In any online store to track purchasing history of various items by different customers.

  5. What does items_orders table do?
    Its sole purpose is to create “Many to Many” relationship between items and orders.

G.

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

One to one relationship can be used when some information needs to be unique to a costumer. For example, a address can only have one single costumer ID.

  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?

When you have a costumer ID it secures that you can’t repeat costumer on the code. It is called Foreign Key. Each costumer will have a unique ID. It will ensure integrity of data.

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

On an e-commerce for example costumer can make different orders and orders can have different items.

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

On an e-commerce, each order can have multiple items and multiple items can be in different orders.

  1. What does items_orders table do?
    It creates a relationship between tables and orders.
1 Like
#472

  1. Relationships: One to one, One to many & many to one, many to many, self referencing

  2. We use one to one relationships when a field belongs to a single entity. For example, a one to one relationship example is an individual and their phone number.

  3. The column called “customer_id” is a foreign key that is primarily used to enforce referential integrity between the data in the separate tables. This is used as a field in one table that uniquely identifies a row of another table of the same table.

  4. A many to one / one to many relationship is the most commonly used. 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.

  1. An example of a many to many relationship is ordering a pizza or sandwich with multiple toppings.

  2. The items_orders table has a sole purpose to create a many to many relationship between the item and orders.

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

Height and weight charts

  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 a foreign key
4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
An in store points card where retail can track an individuals purchases
5. Give an example from the real world when would need to use a “Many to Many” relationship.
A list of people buying supermarket products so that shops can track what needs restocking
6. What does items_orders table do? create the many to many relationship between orders
[

2 Likes
#474

[quote=“ivan, post:1, topic:6431”]

  • 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

  • When do we use “One to One” relationships? Give an example from the real world.
    If customer’s address can belongs to only one customer in a restaurant, this relationship is “One to One”.

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

  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Each customer may have zero, one or multiple orders. But an order can belong to only one customer.

  • Give an example from the real world when would need to use a “Many to Many” relationship.
    In an e-commerce website, with the following:

  • Customers can make many orders.

  • Orders can contain many items.

  • Items can have descriptions in many languages.

  • 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
#475

If we were to use table examples directly from
https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in,

  1. What kinds of relationships can we represent in a database?
    There are 4 kinds of ways relationships can be established. They are:
    a. One-to-One - e.g. In Table “Customers”, each “CustomerID” has only one “Address” (if that field were placed in other table as “AddressID”) that doesn’t share between each other (can be verified by typing the following code into the SQL Statement box:)
    (SELECT Address, COUNT(*) FROM [Customers] GROUP BY Address HAVING COUNT(*) > 1)
    (i’ll call this Unique Relationships)
    b. One-to-Many/Many-to-One - e.g. In Table “Order”, “CustomerID”=4 has “OrderID”=10250,10252,10257…etc…
    c. Many-to-Many - e.g. suppose there is a new table called “Categories-Supplier”, there will be many “CategoryID” sharing sporadically between “SupplierID”.
    d. Self-referencing - e.g. suppose in table “Customers” there is a new column called “Referrer”, a couple of “CustomerID” is referred by the same “CustomerID”=1.

  2. When do we use “One to One” relationships? Give an example from the real world.
    When tracking your citizens uniquely, create a database containing Citizen matching to its unique 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 assigned ID schema by the software/admin within its own created table act as a PRIMARY KEY that can then be referenced by other tables as a FOREIGN KEY.
    But care needs to be taken that both “customer_id” PRIMARY KEY & FOREIGN KEY has the same data type.
    This way, the software can process the different KEYs between each tables much faster without bothering about other different data related to “customer_id”.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When there is an Order Book to track Buy/Sell Orders on an asset, create a database of Orders by new Customers and by repeat Customers.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    When you’re linking Mutual Friends to Friends-of-friends, create a separate table to connect the “User” database, “Friends” database, and “Friends” of “Friends” database.

  6. What does items_orders table do?
    Its sole purpose is to establish “Many-to-Many” relationships between the items and the orders.

1 Like
#476
  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 there is a unique attribute such as an employee/customer name and their National Insurance Number which is a unique identifier for that 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?
    The customer_id column is a unique identifier for each customer. The address_id column in the customers table is a foreign key which links each customer to their address in the adresses table.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A student and their class. Each class could have multiple students and each student could be taking multiple classes.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    In commerce with items and orders. An order can consist of multiple items and each item can be in multiple orders.
  6. What does items_orders table do?
    It creates a Many to Many relationship between items and orders.
2 Likes
#477

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    We would use a one to one relationship for when when assigning the name of the customer, seeing that the customer can only have 1 name.

  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 assign the customer to a variable to prevent redundancy and to link the id to the current address .

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

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Orders can have many items and a item can be in many orders

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

1 Like
#478

  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.
    seperate tables for “customers” and “cust addresses”: with each address only belonging to one cust, this 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. Why? How is it used in order to connect customers to addresses?
    Foreign Keys are used for all kinds of database relationships. It is used to ensure referential integrity of the data. The Customer ID allows the same record or data to be used in many queries where we don’t want the information to change - National insurance number as part of Customer ID preserves the "No Duplicates allowed rule when querying tables related to customers and other attributes/fields and data in records.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    e-commerce is the most predominant case of “many to one/one to many” relationships
    as 1 customers can order many products that can be found in many warehouses but at any point we need to know where those many products from what warehouse was shipped to this unique customer

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A group of friends who all have various relationships with each other is a many to many relationship. Jack is friends with Jill who is friends with Sarah who is also friends with Jack.

  6. What does items_orders table do?
    It creates a many to many relations, as links the Item purchased with the order number for the customer who purchased the products.

2 Likes
#479

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

  2. You can use One to One relationships to relate data that are in different tables.

  3. This is a Foreign Key, it is an expression of the relationship of entities in different tables.

  4. Credit card issuers keeping track of credit data from their customers.

  5. Doctors and patients. Either can have many or few of each other.

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.

3 Likes
#480
  1. One to One Relationships
    One to Many and Many to One Relationships
    Many to Many Relationships
    Self Referencing Relationships.
  2. Credit card, home address is used as a customer identifier, also cell phone number is one to one relationship
  3. It has a matching record in the Address table, this is called a Foreign Key and it is used for all kinds of database relationships.
  4. Bulk order off website which sends out parts of shipment as it comes into stock. So same customer receiving multiple orders from original order.
  5. 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. Example would be a regional warehouse supplying a supermarket.
  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.
2 Likes
#481

  1. What kinds of relationships can we represent in a database?
    Cross join, Inner Join, Left outer Join, Right Outer Join, Self join

  2. When do we use “One to One” relationships? Give an example from the real world.
    When one row of the table has an exact one match to the other row of the table. for e.g. Customer “A” can have only one unique account number within that bank

  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 in the customer table is a primary key of the customer record. Whereas in the order table, customer_id is a reference key also called as foreign key to establish relationship between customer and the orders

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many to One example: More than one associate of the company is working in Sales Department.
    One to Many example: A Student can enroll in many training courses at once

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A Medical insurance claim can have many diagnosis code (e.g. bone fracture, neck injury) and each diagnosis can have multiple treatment/conditions (bone fracture will need surgery, physiotherapy, wheelchair etc )

  6. What does items_orders table do?
    Items_order table is maintaining many to many relationship between order and items

2 Likes
#482
  1. One to one, one to many, many to many.
    2.when we want to connect a list o people with their address for example.
    3.both tables have the customer ID.
    5.facebook commun friends comparison.
    4.many people live in the same house/ when one person has a real estate empire
  2. Create a many to many relationship between orders and itens.
1 Like
#483
  1. What kinds of relationships can we represent in a database?

One to One RS
One to Many RS
Many to Many RS
Self-Referencing RS

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

When we want to store the Social Security number of a customer. As it is a unique fit, the RS will be created 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?

It is a Foreign Key which is used in all kind of databases and serves as the link/connection between two or more tables and uses the date to connect them.

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

A customer can make several/many orders but each order can go to only one customer individually.

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

An item can be in several orders and several orders can have the same item

  1. What does items_orders table do?
    Create a “Many to Many” relationship between the items and the orders
2 Likes