SQL Relationships - Reading Assignment

Blockchain Business Course
#242

  1. Relationships that can be represented in a database are: One to One, One to Many, Many to One, Many to Many and Self Referencing.

  2. It is used when certain data only belongs or is related uniquely to another data. For example, one person’s ID number or voting reference number could be recorded in a simple database for a particular purpose.

  3. It is to give this particular customer a unique identifier. There can be 50 John Does and this ensures the right John is selected while seeking for the right information. This particular id can be used to check if it is equal to the correct address_id (integrity check), thus whether a particular address belongs to the particular customer.

  4. Online shopping - one customer can order multiple items (one to many);
    Project funding - many investors sending money to the same company or person (many to one).

  5. Supermarkets - many customers can order many, same or not, items.

  6. It is to create a Many to Many relationship between the items and the orders.

1 Like
#243

  1. One-to-one; One-to-many/Many-to-one, Many-to-many, Self-referencing

  2. One-to-one example: Every citizen has one tax number or social security number. No one else has the exact same number.

  3. The customer_id helps to connect with the customers table. The ID is used to identify the customer attributes in the customers table. So once you know which customer_id you want to check, you can look up all the information you need in the customers table.

  4. One-to-many/many-to-one example: Father of multiple kids. One father could potentially have multiple (many) kids

  5. Many-to-many example: Using hashtags in social media. Many hashtags are being used in multiple (many) posts/tweets

  6. This is the transaction data stripped down to keys/IDs. The most efficient way of storing the transaction data. When linking with the other tables you can get the order details, customer details and product details

2 Likes
#244
  1. One to one; one to many; many to one; many to many.
  2. One to one relationships are used to store a single record against a user, for instance name and address.
  3. The customer id is a foreign key which links the relationship to both a customer name and customer address.
  4. If I make a a purchase of different items online my one order will contain many items.
  5. If I regularly purchase item from a website their database will link me, one of many customers with the many items in my purchase history.
  6. This links the data contained in two or more tables together in a separate table.
1 Like
#245

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.
For example when I have a customer and I need to link his/her personal data (birth-gender-personal taste) to his/her name.

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 point at the customer who has a connection with that address_id

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
For example on Amazon website where a customer can buy multiple items all collected and packed in a single order.

Give an example from the real world when would need to use a “Many to Many” relationship.
Taking again the Amazon example: one order contains multiple items and each item can also be in multiple orders from multiple persons.

What does items_orders table do?
It supports the -Many to Many- relationship between the “order_id” table and “item_id” table.

2 Likes
#246
  1. What kinds of relationships can we represent in a database?
  • One to one relationship
  • One to many relationship
  • Many to many relationship
  • Self referencing relationship
  1. When do we use “One to One” relationships? Give an example from the real world.

when there is one unique match for each item in the table

  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 represent the Foreign key and it is used to connect the two tables.
    It is used by matching address ID to the costumer ID

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

  • Customers can make many orders.
  • Orders can contain many items.
  • Items can have descriptions in many languages.
  1. Give an example from the real world when would need to use a “Many to Many” relationship.
    each order can contain multiple items. And each item can also be in multiple orders.
  2. 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
#247
  1. we can represent one to one , one to many, many to one and many to many relationships in a database.

2.one to one example is to store the name and customer name and address. the information is about one individuals or thing that has different categories of information.

  1. this customer ID allows this data to be used in queries between database. It is as foreign key in different tables.

  2. One to many & many to one example is a school courses and students. students may have one course or many courses.
    Student1 - English, philosophy , chemistry
    Student 2 -Spanish, Englsih

  3. Many to many example is if we use the same example as 4. at school , many students made many subscription to many different courses.

6.items_oders table creates a many to many relationship between items table and orders table.

1 Like
#248

  1. What kinds of relationships can we represent in a database?
    There are several types of database relationships. A few examples are: One to One Relationships, One to Many & 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.
    When a specific entry such as an email address can belong to only one person/customer, a One to One relationship is present.

  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 used to execute a “One to One” relationship between a referencing number and a specific customer/person. It also refers to a matching record in another table, “Addresses”. This type of entry is also called a “Foreign Key”.

  4. Give an example from the real world when you would need to use a “Many to One/One to Many” relationship.
    The most commonly used type of relationship. For instance, if you were running your own e-commerce store, and had to keep track of single customers placing several orders with several items, you would need to have the proper tables in your database to draw the right relationships between the single customer, their orders, and the items contained with their orders

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Using the E-Commerce store example, each order can contains multiple items, and each item can also be in multiple orders.

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

1 Like
#249

  1. The kinds of relationships we can represent in a database are; a) “One to One” relationships, b) “One to Many” and “Many to One” relationships, c) “Many to Many” relationships, and d) “Self-referencing” relationships.

  2. We use “One to One” relationships where we have two items which can only belong to each other. A pair. e.g. A car model being made by only one company.

  3. The author has a column called “customer_id” in the customer table in order to connect the customer information to the relevant “address” data in the in the addresses table.This is done by giving each customer an id number and connecting this number to a corresponding address in the addresses table.

  4. A real world example of when I would need to use a “Many to One/One to Many” relationship is where “many people live in one area/an area is habitat to many people”.

  5. A real world example of when I would need to use a “Many to Many” relationship is where at a company “many different employees can work on many different tasks”.

  6. The items_orders table connects the items with the orders.

1 Like
#250

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

  2. when one entity can only belong to another single entity. For example in a table containing the relationship product <-> barcode. A barcode can only belong to one product, and a product has only one unique barcode.

  3. customer_id is used as a sort of key value that appears in both tables. With the customer_id we can take a look at the address table. There we find the same customer_id and the customer’s address connected to his ID. The value customer_id has the role of a unique identifier.

  4. School class: one teacher has n children in his class. N children have this one teacher.

  5. Call center: many customers may have been talking to many different operators to solve a problem, while each operator talks to many customers a day.

  6. This table (let’s call it table 3) shows the many to many relationship matching together the key values of table 1 and the key values of table 2. (order_id plus item_id, or in case of my example in question 5: customer_id with operator_id

1 Like
#251

  1. What kinds of relationships can we represent in a database?
    a) We can represent the following types of relationships as:
    • 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.
    a) We would want to use a One to One relationship to link possible redundant data in another table.
    For example, if you have an e-com store, you would have customers that potentially order your products; more than once – but need the item shipping to the same address.
    In this case, a One to One Relationship we could create would look something like this:
    Cust_ID Cust_Name Cust_Address Order_Nu Item_Ord
    101 John Smith 21 Bullish Ave Rekt City 21 BTC T-Shirt
    102 Jane Doe 15 Bearish Blvd Rekt City 22 BTC T Shirt
    101 John Smith 21 Bullish Ave Rekt City 23 ETH Socks

Customer_ID Customer_Address
101 21 Bullish Ave., Rekt City
102 15 Bearish Blvd., Rekt City
101 21 Bullish Ave., Rekt City

Customer_ID Customer_Name Order_Number Item_Ordered
101 John Smith 21 BTC T-Shirt
102 Jane Doe 22 BTC T Shirt
101 John Smith 23 ETH Socks

  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?
    a) The “customer_id” was used to create a “Foreign Key”. This is how tables are connected or linked to each other in a DB.

  2. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    a) A person’s email address, or phone number could be considered as a “Many to One/ One to Many” relationship.

  3. Give an example from the real world when would need to use a “Many to Many” relationship.
    a) As in the example above, picture an e com store who will take orders and ship their products to each customer.
    Cust_ID Cust_Name Cust_Address Order_Nu Item_Ord
    101 John Smith 21 Bullish Ave Rekt City 21 BTC T-Shirt
    102 Jane Doe 15 Bearish Blvd Rekt City 22 BTC T Shirt
    101 John Smith 21 Bullish Ave Rekt City 23 ETH Socks

Customer_ID Customer_Address
101 21 Bullish Ave., Rekt City
102 15 Bearish Blvd., Rekt City
101 21 Bullish Ave., Rekt City

Customer_ID Customer_Name
101 John Smith
102 Jane Doe

Customer_ID Customer_Order
101 21
102 22
101 23

Order_Number Item_Ordered
21 BTC T-Shirt
22 BTC T Shirt
23 ETH Socks

Item_Ordered Item_Number
BTC T-Shirt 1
BTC T Shirt 1
ETH Socks 2

Customer_ID Order_Number Item_Number
101 21 1
102 22 1
101 23 2

  1. What does items_orders table do?
    a) The “item_orders” table essentially depicts a “Many to Many” relationship between the item purchased and the order.
2 Likes
#252

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

Remember you can use the “Preformatted Text” Button to encapsulate any kind of code you want to show.


function formatText(){

let words = “I’m a preformatted Text box, Please use me wisely!”

}

preformatted text
preformatted text957×174 11.9 KB

Just look how awesome your examples looks like with the preformatted text

Customer_ID Order_Number Item_Number
101 21 1
102 22 1
101 23 2

Carlos Z.

#253

Great. Thanks for the tip. I was wondering how to display it correct. I started the tables in Excel but it didnt translate properly. Now I know! TY

1 Like
#254

  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 have a customer table and we have a car table. We can now match the customer to his car. One to one relationship.

  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?

With the customer id, the customer gets a number. So we don’t have to use the whole name of the customer. Instead the customer_id is enough to identify the customer. The customer_id gets connected in the addresse table.

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

Many to One: When you order different items from a store. You are the same customer, but different items are ordered, so they are connected to the same customer.

One to Many: when you have different prices in different currency, they have to be matched to the same object. So one item to different prices.

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

Digital Filmstore: Has many different customers and many different films. The same film can be selected from different customers. And they can chose from many films.

  1. What does items_orders table do?

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

1 Like
#255

  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 relationships

  2. When do we use “One to One” relationships? Give an example from the real world.
    Links one piece of info to another where their can only be one relationship, for example a person to their cell phone number. One person to one cell phone 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 contains integers that represent the customers name. The integer is then used as a foreign key on the addresses table to relate the customers name to their address on the addresses table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    If, over the course of a year I use multiple public car parks for parking my car. My car is the “one” and the “many” are the various car parks.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Books in a library: Many booked lent to many people.

  6. What does items_orders table do?
    Is a table of foreign keys that pulls together the relating data from the other 2 corresponding tables to give the entire info about each order.

1 Like
#256
  1. 4 main types, namely one to one, one to many, many to many and self referencing relationships.
  2. If each persons address can only belong to one customer (not common, what if my brother who lives in the same house want to become a customer).
  3. Customer ID provides the reference to the customer (its a “foreign key” that allows us to interact and communicate with references in other tables.
  4. If one customer say wants to make many orders via amazon we would use the one to many example.
  5. If I want to buy an amazon gift card, I have to “purchase” on Amazon and then another user could spend the money on amazon that I uploaded for them.
  6. Keeps track of the various orders placed by a single user / customer ID.
1 Like
#257
  1. One to one, one to many, many to one, many to many, self referencing
  2. Key value pair eg. ID and person
  3. Customer ID is a unique number, or the key
  4. Eg. when one customer makes many orders, or many items belong to one person
  5. When there are multiple customers and multiple items, and customers order certain items, sometimes overlapping
  6. Creates a ‘many to many’ relationship
1 Like
#258
  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
  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”. Ex. Mailing list
  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? These processes makes the data base easier to query It connects to the separate address table
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. ecommerce site
  4. **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.
  5. **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
#259
  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 ise ”One to one” when one entry can only be linked to another entry. Passport is an ”one to one” example, people and their passport 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?
    To create a relationship between the customer and the address. It connects to the separate address table

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

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    For example Books in a library, where MANY books is lent to MANY people.

  5. 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
#260

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

  2. When do we use “One to One” relationships? Give an example from the real world. When a data element should belong only to one person, such as a Social Security Number or a Passport ID.

  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 primary key which uniquely identifies the customer we want. Then from that customer, we take the address_id which is a foreign key to the address table, where we can fetch the associated address

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. I am a shopper and I have twenty orders (1 to many). Or, twenty shoppers order the same item (many to 1).

  5. Give an example from the real world when would need to use a “Many to Many” relationship. Students involved in a variety of sports

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

1 Like
#261

[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.
    When I write a letter or do a mail drop to someone, one name and one adress
  • 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 column called customer id in the customers table so that an adress ID can be assigned to a customer. A customer may order more than one item that be assigned to the adress ID which is called a foreign key.
  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    example Many to one: when I shop at the supermarket, I may order many different items, to the same adress. Example one to many: Iphone may bring out a new phone and a million different people order the same phone.
  • Give an example from the real world when would need to use a “Many to Many” relationship. An example is Amazon, they are distributing many items daily to many customers daily.
  • What does items_orders table do?
    The items_orders collates the customer ID with the Items ID that has been ordered.
1 Like