SQL Relationships - Reading Assignment

Blockchain Business Course
#544

Reading Assignment: SQL Relationships.

  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 we want to link a unique entity in one table with another unique entity in another table.
    F.ex. instead of have the Address information in a Customer Table, we would only use Address ID that will refer the customer address entity in an Address Table.
    Each address would only belong to one 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 will refer to the matching record in the Customer table.
    We only need to call for these ID numbers in other tables.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    F.ex. e-commerce store, where customer can take many orders that contain many items, where each item can have many different descriptions in many languages.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    When order can contain multiple items. And each item can also be in multiple orders.

  5. What does items_orders table do?
    It creates relationship between Orders and Items.
    Different Orders can include same Items.

1 Like
#545
    • One to One Relationships
    • One to Many and Many to One Relationships
    • Many to Many Relationships
    • Self Referencing Relationships
  2. When one record in a table is associated with a specific record in another table. A passport id number is associated with a specific person.
  3. To make a connection between a specific customer and his address. It is called foreign key.
  4. When i purchase many items from a web store my personal customer id is linked to those items(one to many). Those items i purchased are linked to my personal customer id(many to one).
  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.
  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
#546

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.
A bank account
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 id is a foreign key. It connects other tables without having to repeat the entry of the redundant data related to the id. Saves time, money and effort

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

5. Give an example from the real world when would need to use a “Many to Many” relationship.
Loyalty schemes where users have access to the same promotions

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

  1. a) One to One Relationships
    b) One to Many and Many to One Relationships
    c) Many to Many Relationships
    d) Self Referencing Relationships

  2. We use One to One relationships when we want to link one piece of data to another single one. For example a customer in our online shop would ideally have only one address, which would then be updated if the customer changes location. Another example would be the customer’s age, sex, telephone number and so on.

  3. The customer_id in the customers table gives each individual customer an ID number which then may be used as a so called ‘‘Foreign Key’’. Using the customer_id, we can then easily link the particular customer’s ID number to other tables containing additional information or attributes about him. For example we can add another row called number_id in the table and create a relationship between the particular customer and his telephone number.

  4. For example if a person wants to order food online he will have to create an account and enter his name, address, telephone number and so on. Let’s give an ID to this account and call it customer_id1. Customer_id1 will always represent this particular person and contain all of his information. (One-to-One Relationship). But lets say we want to document every food order this customer puts. By doing that the customer_id1 could have order_id1, order_id2, order_id3 and so on, each order may represent a different meal from a different restaurant. This is an example of a One-To-Many and Many-to-One Relationship. He may put thousands of different orders, but his ID will always be customer_id1.

  5. Lets use the example from above from a random restaurant’s point of view. Each order that comes in may contain one, but it may also contain two or more meals that the restaurant offers. In the same way, one or more meals can be ordered by many customers in different orders. This would be an example of a Many-to-Many relationship.

6.The items_orders table is used to create this Many-To-Many relationship. As we can see in the article item_id 201 and 202 are parts of the same order and item_id 202 is used in two different orders.

1 Like
#548
  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 each identity has a unique criteria, particular or characteristic that can only belong to that one identity.
  • Our phone numbers, passport or driver’s licence numbers, or various government identification codes.
  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?
  • Computers operate with greater efficiency in terms of numbers, and of the same number of characters, that can be used to reference lists of random size and character entries and match entries in those columns according to that one id.
  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • When a shop sells multiple items to multiple people, there will be many different items being purchased by one person, and many people buying the same product.
  1. Give an example from the real world when would need to use a “Many to Many” relationship.
  • In any situation where there is multiple products, and many people are purchasing varying amounts of those products. There is many people, and many items, relating to many orders. It would be necessary to know how many orders contain a particular item, what items are in each order, and who is purchasing any particular item.
  1. What does items_orders table do?
  • Creates a relationship between items and orders.
1 Like
#549
    • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships
  1. is a relationship between the Customers table and the Addresses table. We can see that in a eshop
    3.Is called " Foreign Key" . Is used in order to connect data between two tables.
  2. E- commerce site that have all this functions.
  3. Restaurant . Each menu contains different kind of plates.
  4. Relates an item to an order
1 Like
#550
  1. one to one, one to many, many to many and self-referencing
  2. by relating the costumer to an addres
  3. its used as a foreign key refering to another table. its used as a relationship key
  4. when ordering multiples different things
  5. when multiple costumers order the same thing.
  6. it creates the many to many relationships
1 Like
#551
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

One-to-one relationships occur when there is a one-to-one relationship between data. For example, a social security number that is associated with a person would be a one-to-one relationship because each person has a unique social security number.
3.
Each customer has a unique customer id that can be referenced using a unique address id.
4.
A many-to-one relationship may occur when there are multiple items associated with one order.
5.
This could be when multiple items are associated with many different orders.
6.
It creates a many-to-many relationship between items and orders.

1 Like
#552

  1. What kinds of relationships can we represent in a database?
    We can represent i) one-to-one, ii) one-to-many, iii) many-to-many and self-referencing relationships.

  2. When do we use “One to One” relationships? Give an example from the real world.
    E.g. when we have lets say citizens and their birth date stored in a government database.

  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?
    That is the key to reference the particular customer in that table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    E.g. if there is a database that stores people and their car registration, you can have the same car being owned by different people at different time.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    In a supermarket supply database you can have a number of items of the same product being delivered to many supermarkets.

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

1 Like
#553
  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
  1. When do we use “One to One” relationships? Give an example from the real world.

whenever one record in a table is associated with one record in another table. An example from the real world can be an individual person and their social security number.

  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 author identified it as a “foreign key”, which is intended to be used as a reference between data in two table.

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

A one to many could be where one person can have multiple emails. Many emails can belong to one person.

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

Many students and many classes. A class can contain many students and a student may be enrolled in many classes.

  1. What does items_orders table do?

The items_orders table resolves the many to many relationship between orders and items.

1 Like
#554

SQL Relationships - Reading Assignment:
Q1: What kinds of relationships can we represent in a database?
A: One to one, One to many and many to one, Many to many, and Self-referencing relationships can be represented in a database.

Q2: When do we use “One to One” relationships? Give an example from the real world.
A: We use one to one relationships when there exists relatively unique data associated with another type of unique or individualised data, such as employee name and N.I Number.

Q3: 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 column customer_id in the customers table is used to refer to the customers name in the customer_name column, and is used to connect the customer to the related address by a connection with the address_id.

Q4: Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
A: Shopping on Amazon is one such real-world example of where a one to many /many to one relationship would be used.

Q5: Give an example from the real world when would need to use a “Many to Many” relationship.
A: In the real world a many to many relationship could be used to keep track of Garden center supplies and suppliers.

Q6: What does the items_orders table do?
A: The items_orders table connects the item/s ordered to the order made by a particular customer.

1 Like
#555

What kinds of relationships can we represent in a database?
We can do One on One relationships, or One to many, or Many to Many, or Self Referencing relationships.
When do we use “One to One” relationships? Give an example from the real world.
People buy tickets to a concert. the 1st table has the Customer, Ticket #, and on the other table, you have Ticket #, and assigned seat #.
In the article in the “One to One”-section, the author of the article has a column called customer_id in the customer’s table. Why? How is it used in order to connect customers to addresses?
Customer_id is the relationship identifier that links the customer table to the addresses table.
Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
Each Nike profile is linked to all orders made by a customer using that profile

Give an example from the real world when would need to use a “Many to Many” relationship.
Nike using Case numbers for each call a Nike member makes to separate each reason/time a Customer calls with a case #. Each case is assigned to a Nike rep who took the call and each order that the Nike rep takes from a given caller is separated in its own unique case number. A customer may have many cases liked to their Nike profile email.
What does the items_orders table do?
It links the order id with the item id with the Customer id identifying the buyer in this many many relationships.

1 Like
#556

  1. What kinds of relationships can we represent in a database?

    One to One; Many to One; One to Many; Many to Many; Self Referencing Relationships

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

    A One to One relationships occur when a set of data has a unique relationship. Name and Drivers
    license number for example.

  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 creates a one to one relationship the address_id. 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.

    Ecommerce websites use Many to One/One to Many relationships. It is important to know who your
    customers are, where they are located, What they ordered and when they ordered.

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

    Ecommerce websites may use these relationships as well. If you have multiple items for sell with
    descriptions and pictures that can be ordered multiple times by multiple people you have a many to
    many relationship.

  6. What does items_orders table do?

    It creates a “Many to Many” relationship that links Orders to Items.

1 Like
#557
    • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships
  1. For example: on an order, an address can only be used by 1 customer
  2. Customer id # can belong to only 1 customer and by querying a customer id all their info, orders, address, etc. can be brought up
  3. I order regularly from a certain department store. When I want, I can check all my orders and the date I ordered can be brought up as well as the items I ordered in that particular order.
  4. There are times when ordering that an item may say, 7 in stock and 3 people have this in their carts. Though we are different customers, some of us are ordering the some of the same items and some different.
  5. 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
#558
  1. one to one, one to many and many to many, many to many and self-referencing
    2)one to one is when specific data is linkeD to a specific user i.e. telephone number T to user X
    3)the customer_id is the identifier linked to the specific address and it is used to be matched with the corresponding address. It is a foreign key in the address table as well
    4)one to many - a single customer orders number of LPs from a catalog in an online record store. Many to one - number of customers order the number one hit single of the hottest band from an online record store
    5)orders in online stores can contain multiple items and multiple users can order multiple items some of which will be the same
    6)create the relationship b/w items and orders - many to many
1 Like
#559

image
image936×722 210 KB

1 Like
#560
  1. We can capture and represent 1:1, 1:M (1 to 0, 1 or many) and M:M relationships in a relational DB
  2. When time is taken into account true 1:1’s are very rare, but one example would be Person : UK National Insurance Number
  3. The custimer_id is a unique key used to retrieve customer details and a non-unique key, used to retrieve their address details
  4. (M:1 - The addresses I’ve lived at, over time : Me), and (1:M - Me : the addresses I’ve lived at, over time)
  5. When time is taken into account, many-to-many’s are very frequent. For example Employer : Employee
  6. The items_orders table ‘joins’ items and orders, so you can identify (1) all the items in a specific Order and (2) all the Orders for a specific Item
1 Like
#561

1.) One to one, one to many/many to one, many to many, and self-referencing
2.) We use one to one when each item only has a single relationship (i.e. customer - address [a customer only has one address])
3.) It is possible that some people share the same exact name. The ids are unique to each person and their address.
4.) Online e-commerce store, customers can have multiple orders, and those many orders only belong to one customer.
5.) Online e-commerce store, each order can contain multiple items, and each item can belong to different orders.
6.) items_orders sets the itemid to the orderid, therefore the same item can belong to different orders.

1 Like
#562
  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.
    One to One is given where only 1 record on a table corresponds to only another record on another table, ie customer names and addresses
  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 the Foreign Key. It´s used to create a reference to a primary key
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    names and cars / components of 1 tool
  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    In an CRM, an order could contain many items and each item can be contained in several orders
  5. What does items_orders table do?
    create a “Many to Many” relationship between 2 tables
1 Like
#563

1/ one to one, one to many, many to one, many to many
2/ Lio and Ivan speak together
3/ customer-id is the foreign key of customers table. Usefull to connect table customers to table addresses

4/ I have several cars - at least, each car must have an insurance covering any damage done to rest of world => I have to subscript such an insurance for any of my cars: One to many

I have several brothers and sisters : each one will celebrate my birthday : Many to one

5/ For electing the president of Republic, each citizen is first given identical list of all candidates. Voting consist in tapping on one name one time, during a given interval.

6/ put relationship bitween orders and orderable items?

1 Like