SQL Relationships - Reading Assignment

Blockchain Business Course
#1

Welcome to the discussion about this reading assignment.

Leave your answers to the questions below in this thread. If you have any questions or you want to discuss something connected to the assignment feel free to do it in this thread as well, but please everything to the topic.

  1. What kinds of relationships can we represent in a database?
  2. When do we use “One to One” relationships? Give an example from the real world.
  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?
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
  6. What does items_orders table do?
23 Likes
#2

SQL Relationships – Reading Assignment

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 a. When do we use “One to One” relationships?

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

b. Give an example from the real world.

Individual people and their personal cell phone number.

3. In the article in the “One to One” section, the author has a column called customer_id in the customers table.
a. Why?

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.

b. How is it used in order to connect customers to addresses?

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.

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

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.

5. Give an example from the real world when would we 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

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.

16 Likes
#3
  1. One to one, one to many, many to one, many to many and self referencing.
  2. A unique user and their address.
  3. The customer id is a foreign key in the customer table. The customer id relates to the customer table where the customer id is a primary key and shows the record of attributes about that specific customer.
  4. A user could speak many languages. This is a one to many relationship. You can have many languages relating to the attribute of human language. This is many to one.
  5. Many languages use many letters of the Roman alphabet and there are many Roman letters in the many languages.
  6. This table relates an item to an order by the id number of each.
2 Likes
#4

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.

  • When there cannot be more then one relationship between an entry.
  • Each Social Security number can be associated to 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?
Foreign Keys are used for all kinds of database relationships. It is used to ensure referential integrity of the data.

4) Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
In the case of a College. Students may have one course, but one course may have many different classes. Or another case, each course may have many lecturers.

5) Give an example from the real world when would need to use a “Many to Many” relationship.
A student may attend many classes, and each class can host many students.

6) What does items_orders table do?
It is only used to determine the Many to Many relationship between the “order_id” table and “item_id” table.

1 Like
#5

1 Types include cross join, inner, left and right joins - depending on how you want the relationship data output from the query displayed.
2. We use One to One when we want to preserve some unique aspect associated with the data Name national Insurance Number for example.
3 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. A one to many relationship could be Name and E-mail address - A person could have one real name but many e-mail addresses.
5 Items-orders table is an efficient way to track orders and items - for example the changes to stock bought can inform the re-purchase table of items selling.- Unique order reference to different items (Inventory management table) Queried through Key Id and foreign keys to track stock levels, orders processed - tracking too if need be.

#6

1. What kind of relationships can we represent in a database?
• One to One
• One to Many and Many to One
• Many to Many
• Self Referencing
2. When do we use “One to One” relationships? Give an example form the real world.
When an unique attribute in a table matches an unique attribute in another table. For example, a table of the account numbers in a bank with the account information and a client table with the client information. It must have only one bank account per client.
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 there is only one customer_id per client that matches the only one address_id per client.
4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
For example when a bank client of 2. could have some different bank accounts.
5. Give an example form the real world when would need to use a “Many to Many” relationship.
For example a Book_Borrows table in a library that has a relationship with a Book table and a Borrows table
6. What does items_orders table do?
It creates the relationship between the Items table and the Orders table, both sides of this table.

#7
  • What kinds of relationships can we represent in a database?

One to one, One to many, Many to one and many to many.

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

When one entry can only be linked to another entry. For example, people and their passport number.

  • 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 done to create a relationship between the customer and the address.

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

When an order history record is required for a particular customer.

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

When a list of male pupils who attend a school is required.

  • What does items_orders table do?

Its sole purpose is to create a many to many relationship between the items and orders.

2 Likes
#8

  1. kind of relationships;
    one to one, one to many, many to one, many to many, self referencing relations

  2. one to one;
    one 1 item in database can only belong to one entry, for example customer id to customer name.

  3. the author uses many different tables to break up data into many different subsets or parts to make it easier to access and use. It is probably easier to join data, from many different small tables, then break up data from 1 big, complex table. they are connected with a join function. one joins a Foreign key.

4.one to many - example - 1 customers makes many orders
many to one; many items were ordered by 1 client

5.many clients, made many orders, or an item was ordered many times and by many people

  1. it displays a list of orders, and the contents of the orders.or the order id in this case.
1 Like
#9

  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.
    When one key=value
    Name and social security

  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 gives two tables a linking point and way to simplify and organize information

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When one person makes multiples orders at a shop

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Making multiple
    Ordering something from Chipotle

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

#10
  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.

When one row entry in a table can only have one specific relationship value in another table as defined by the foreign key, such as in a DB of users and their usernames.

  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 column in the customers table is used to uniquely identify each customer and is used as a foreign key in the addresses table to identify which customer belongs to each unique address.

  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    In a census situation, one demographic age range will contain many individuals (one to many), and the many individuals will only belong to one age range (many to one).

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

A list of students and a list of classes, where 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 creates a many to many relationship between the the orders and items.

#11 
What kinds of relationships can we represent in a database?

  1. We can represent:
    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.

  2. In one example, a SSN is associated with one prtson. And for every one person, there is one SSN.
    In the text, we have a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this data relationship is described as “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?

  3. This is the unique identifier, or UID, which can be a foreign key in another table, like the address table.

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

  4. For example, in a world of customer and product orders, 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.

  5. In a world of customer and product item orders, there is a need for multiple instances on both sides of the data relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.

1 Like
#12

Also
What does items_orders table do?
6. The items_orders table resolves the many to many relationship between orders and items.

#13

  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.
    Buying a second-hand car from an individual

  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 connecting customers name to the addresses

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

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

  6. What does items_orders table do?
    It links customer to the right item bought

#14

  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.

  2. When do we use “One to One” relationships? Give an example from the real world.
    Social number can only be tied to 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?
    It’s called foreign key, makeing sure that it cant be duplicated.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When a customer makes many orders at webshop.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many customers, many orders, one item, that many customers order, many times.

  6. What does items_orders table do?
    it connect to orders id with the items id

1 Like
#15
  1. There are 4 kinds of relationships that can be represented in SQL database: (a) One-to-one, (b) One-to-many & Many-to-one, © Many-to-many, and (d) Self referencing relationships.
  2. If there is only one reference that can be made from each item of one table to another, then One-to-one relationship could be used. An example could be a company’s employee record table that’s kept separate from a table of medical records due to privacy reasons. Someone can update the medical records table of a company, without necessarily knowing the rest of the information about the employees.
  3. In this case, customer_id is not used to reference the customer addresses. However, it is better to have it as it can be used to reference the customers table to another set of data.
  4. It can be used to track orders in e-commerce websites associated to a customer.
  5. It can be used to track orders in e-commerce websites, LOL, but with more detail about the order, i.e. what items belong to it.
  6. The ITEMS_ORDERS table show which types of items are associated to an order. Its purpose here is to create a many-to-many relationship between the 2 preceding tables ORDERS & ITEMS.
#16
  1. What kinds of relationships can we represent in a database?
  • one to one
  • many to one and 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 we want to restrict a key to an attribute that has to be unique. Eg when you sign up an ecommerce account, your email is in relation to a customer ID, and you can only use that particular email address to sign up one account.

  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 is a foreign key assigned to each customer so it can be used to create the relationships between a customer and other tables such as Addresses and Order, while making sure these relationships are accurate because there could be more than one customers with the same name. The customer_id appears in the Addresses table to connect each unique customer account to their address.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    In a stock trading account, a trader has many stocks/tickers. Each ticker has many transactions because the trader has been actively trading it.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Facebook users and groups - I ‘Like’ many groups and each group has many other users.

  5. What does items_orders table do?
    Records all order details such as customer, date and amount. It helps store owner identify which order is made by which customer by using keys: customer_id and order_id.

#17
  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.

They are used when forming unique relationships between table data entries. For example, connecting a unique account/identification number to a customer.

  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?

An id is used to create a unique identifier to factor out common information and eliminate data redundancy. The id is used as a foreign key to connect to a unique address.

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

For example, an online marketplace, whereby a customer may purchase multiple items contained within a single order.

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

An online marketplace may have multiple orders from multiple customers all containing the same or different item.

  1. What does items_orders table do?

It allows for a many to many relationship to be established between items and orders.

#18

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

  • What kinds of relationships can we represent in a database? One to One relationship, 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 there can’t be more than one relationship between an entry. example, each Canadian is assigned a SIN#, that SIN# can only be related to that specific person.

  • 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 keeps data organized or enforces referential integrity between data in two or more tables because more then one user may share the same address or name
    A foreign key is used to create relationships between users and other tables such as the address table

  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship. a company that has 26 employees, 5 of these employees are Supervisors, 5 are Trade persons, 12 are Apprentices, 4 are Labourers, the company has 4 jobs. Each job needs 1 constant supervisor, 1 trade person, 3 apprentices and 1 labourer each day 5 days a week. the trades person apprentices and labourers get moved around from job to job depending on their skill set while the supervisor remains the same until compleation of the job.

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

  • What does items_orders table do? it’s purpose is to create a many to many relationship between the items and orders.

1 Like
#19

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    Citizen -> Driving Licence: a citizen of a country can only have one driving licence in that country.

  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 used to abstract the address from the customer. Customers are linked to an address_id, which can be referenced to determine the actual address. One address could be linked to multiple customers.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Students and Classes -> one student has many classes that they are enrolled to. One class has many students that are enrolled to it.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Orders and Items -> one order contains many items, one item is in many orders. Orders & Items will provide a many to many relationship.

  6. What does items_orders table do?
    This provides a single list of items and order numbers, so essentially a list of every single item in every single order. items and orders can both appear multiple times, but the concatenation of items and orders should produce a unique line in the table.

#20

I’m going to cheat. I didn’t read the material, but I’ve been working with SQL Database for 2 decades. Hopefully, I can get a pass.

  • 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.
    Facebook user has their user account in the USERS table. They might have their collection of photos in the PHOTOS table. But in the USERS table, they have their profile picture linked to one of the entries in the PHTOTOS table.

  • 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’s a Primary key used to identify the customer. This let’s the customer change other information in that row, without affecting the relationships built around the primary key.

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

  • Give an example from the real world when would need to use a “Many to Many” relationship.
    Sticking with facebook. Users have many photos and photo can have many likes and comments and shares linked to other users…

  • What does items_orders table do?
    LInks the items in a given order