SQL Relationships - Reading Assignment

Blockchain Business Course
#624

:raising_hand_woman: :left_right_arrow: :raising_hand_man: (one to one)
:raising_hand_woman: :left_right_arrow: :family_woman_woman_girl_girl: (one to many)
:family_man_man_boy_boy: :left_right_arrow: :family_man_woman_girl_boy: (many to many)

employee :two_men_holding_hands: :couple: table vs compensation :dollar: table
(an employee :woman_red_haired: is uniquely related to a compensation amount :moneybag: he/she is getting)

To reference :loudspeaker: a column in another table.
In the other table, the column customer_id is a Foreign key :fishing_pole_and_fish: to relate customers to addresses

:raising_hand_woman: writes a blog :left_right_arrow: followers will read the blog :family_woman_woman_girl_girl: (one to many)
:family_woman_woman_girl_girl: many followers send a comment :left_right_arrow: to the blogger :raising_hand_woman: (many to one)

:family_woman_woman_girl_girl: 2 or more followers have posted comments :speaking_head:, likes :+1:t3:, emoticons :heart: :left_right_arrow: to 2 or more bloggers :family_man_woman_girl_boy: on the social platform (many to many)

create :m:any to :m:any relations
(in my example: items posted in a blog relates followers with bloggers on the platform)

1 Like
#625

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    When there is a consistent description or common thread - i.e. Customer Names and Customer Addresses, cell phones, home phones, - contact & personal information.

  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? Information is lumped together in the first table and the second table defines a breakout of information for another type of query. The two tables are joined with a “Foreign Key”, which is the same information conveyed on both tables.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. repeat orders from the same client / customers make many orders, Orders can contain many items, and Items can have descriptions in many languages.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    When you have multiple instances on both sides of the relationship. We solve this with a third table (I.E. Orders —> Items —> Items_Orders)

  6. What does items_orders table do? It serves as a point of connection between the first two tables as shown above —> Table known as “Items” and the Table known as 'Orders"

rjr
2021-03-23T04:00:00Z

1 Like
#626
  1. What kinds of relationships can we represent in a database?
    There are many types of relationships each referencing how one or many items tie to different items or groups of things.
  2. When do we use “One to One” relationships? Give an example from the real world.
    Usually when there is one item that uniquely ties to another item or thing. Real world example is a person can only have one social security number or one birth certificate 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 one to one relationship cross-references each customer _id to the customer_address line by identifying a key reference point like the id# to each customer.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    You could have many items that are tied to one person ordering those things in the shopping basket. Or similarly Many customers could order one product or thing.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many customer can or may order several things from the example above.
  6. What does items_orders table do?
    It is a table place holding all items that can be referenced for many purposes
1 Like
#627
  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?

1)One to one, one to many, many to one, many to many, self-referencing relationships
2)In the article, there is a fine example one-to-one relationship. Each address can only be related to one customer. Another example is that in school, each student can only have one student ID.
3)It is called a Foreign key. Its role is to enforce referential integrity between data in two tables.
4)Each customer can have many orders, but the same order can not have more than one customer. Orders can contain many items.
5)Many-to-many relationships can be between students and classes. Each student can attend many classes and classes have many students.
6)It has only one purpose and to create Many-to-many relationship between the orders and the items.

1 Like
#628
  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 you need for example one user to have only one email address registered on a website.

  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 associate the customer with a primary key so that you can fetch customer data easily. You can then get very easily the address of that customer if you have the customer _id.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    One to many: One driver uses more than one car.
    Many to one: One taxi cab is used by several taxi drivers(not at the same time)

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    A car contains several parts, some common some not. Many car models contain many car parts.

  5. What does items_orders table do?
    Combines the items and orders data. Referencing what items are in what order and vice versa.

1 Like
#629

Thank you! I was having a little trouble trying to “get” #3. I understand your point for sure but didn’t connect that’s what was being asked :slight_smile: Thanks for spelling it out so nicely!

1 Like
#630

  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

  2. When do we use “One to One” relationships? Give an example from the real world.
    When one data type can only relationally belong to only one other data type. A real world example would be your telephone number, maybe your passport number.

  3. In the article in the “One to One”-section, the author of the article has a column called customer_id in the customers table. Why? How is it used in order to connect customers to addresses?
    In order to facilitate the relational aspect of the database by

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Retail online purchasing, Medical coding, Utility operations, Maintenance/Parts, etc.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Resorts - guests can select amenities, etc. So, many (different) guests would have a relationship with many of the same amenity types.

  6. What does items_orders table do?
    Creates a many to many relationship. In the article, the example used is many orders can contain many of the same items.

1 Like
#631
  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 a table element has a unique element attached to it.
    Each person would have a unique fingerprint or 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?
    It is a relationship key - it is used as a foreign key that refers to another table.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    One dog can have many puppies but each puppy has only one dog mother.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many people can order multiple things from a menu and many items can be ordered by many people.

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

2 Likes
#632
  1. one to one, one to many, many to one, many to many, self referencing
  2. In most sales records, a unique identifier would be used with each customer or email address or phone number
  3. It is used as a foreign key to create a relationship between data
  4. My posts on this website can be many but linked to my one id.
  5. A bar may serve many types of drinks to many people
  6. Is usually a pivot table used to maintain many to many relationships
1 Like
#633
  1. one to one, one to many and many to one, many to many, self reflecting.
  2. People and email address. Address can only belong to one person.
  3. Foreign Key in SQL refers to a column or a group of columns that is used to connect two tables from the same database.
  4. Online grocery shopping. One client orders many items, some of them can be more than one (5x yogurt, 3 apples, 5 bananas, orange, juice,… )
  5. when you have a webside let say online store and you have a code for discount that your friend gave you.
  6. Each order can contain multiple items. And each item can also be in multiple orders. 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
#634

  1. There are 4 different types of relationships that can be represented in a database. These include:

  2. One to one relationships

  3. one to many and many to one relationships

  4. many to many relationships

  5. self referencing relationships.

  6. When an entry/field such as an address can belong to only one customer, some identifier which only belongs to that customer, this relationship is “One to One”. There cannot be more than one relationship between an entry.
    Examples: Each Social Security number can be associated to one person. Also, personal phone number.

  7. It is used for all kinds of database relationships. Its primary role is enforcing referential integrity between data in two tables. This is called a foreign key. Its primary role is enforcing referential integrity between data in two tables.
    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.

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

  9. Ecommerce websites such as amazon may use this relationship. 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

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

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

  • 1:1 Relationships
  • 1:N and N:1 Relationships
  • N:N Relationships
  • Self Referencing Relationships

2. When do we use “One to One” relationships? Give an example from the real world.
A: 1:1 relationships are used whenever a record in one table relates with a single record in a different table. Aa example would be a relation between a customer’s country of birth and a table with all the different countries

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?
A: Because it refers to the customer’s unique identifier in the customer’s database. The customer’s table and the addresses table are joined by the address_id which is stored in both tables an ensure a 1:1 relationship between records (in real world, I should stress, the relationship between customers and addresses is N:N)

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
A: A spotify subscriber can play many different playlists; a playlist contains many tracks

5. Give an example from the real world when would need to use a “Many to Many” relationship.
A: The friends relationships in facebook

6. What does items_orders table do?
A: It supports an N:N relationship between the items and the orders

1 Like
#637
  1. One to One Relationships
    One to Many and Many to One Relationships
    Many to Many Relationships
    Self Referencing Relationships
  2. When a customer belongs to only one table like an address - this relationship is one to one
  3. customer_id refers to the matching record in the address table is called a “Foreign Key”
  4. An example of Many to One/One to Many relationship are common in e-commerce - where customers can make many orders, and would require multiple entries in the orders table.
  5. Many to Many relationships would be when you have orders would contain multiple items on multiple orders and the One to Many relationship would not be sufficient to cover this many entries.
  6. items_orders links the order_id to the item_id
1 Like
#638
  1. What kinds of relationships can we represent in a database?

A) 1 to 1 , 1 to many, many to 1, many to many , self referencing relationships.

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

A) name and address

  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) by the use of foreign key which refers to customer address

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

A) 1 client purchased many cars.

Many clients purchased the same car

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

Many orders ,many customers ,ordering an item that ,many customers order many times

  1. What does items_orders table do?

Item refers to single item

Orders refer to a group of items in a order

1 Like
#639

  1. One to one, one to many and many to many

  2. A person can only have one place of birth.

  3. It’s the primary key in the customer table and it can be referenced in the adresses table.

  4. One Family can have multiple members.

  5. A Webshop. Many products can be ordered many times by the same or multiple customers.

  6. It is an relationship table to reference the many to many relationship.

1 Like
#640
  1. What kinds of relationships can we represent in a database? One-to-one, one-to-many, many-to-one
  2. When do we use “One to One” relationships? Give an example from the real world. When a table element has a unique element attached to it. A person and their ID number
  3. 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? It is used as a foreign key providing the link between the customer and addresses tables
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many to one: How many people belong to a certain group
    One to many: How many groups does a certain person belong to
  5. Give an example from the real world when would need to use a “Many to Many” relationship. A person likes many posts and posts are liked by many people
  6. What does items_orders table do? It creates a many-to-many relationship between the items and the orders
2 Likes
#641
  • one-to-one
  • one-to-many / many-to-one
  • many-to-many
  • self referencing

We use one-to-one relationship when one attribute belongs to one item. Example would be an email address belonging to the one specified person.

A customer_id becomes a foreign key in some linked tables and that way you can simplify data. Instead duplicating all information about customer such as name, address etc. you just place an id number.

  • Many-to-one example would be three different email addresses related to one person
  • One-to-many example would be one address related to several people
  • Many-to-many example would be a online store with many different items and many different customers buying different items.

items_orders table creates relation between orders and items.

1 Like
#642
  1. We can have relationships between tables, the kinds are:
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships
  1. A one-to-one relationship is a key value relationship, an example is pizza orders. When someone calls Marcos Pizza, in the database you have the customer name/idnumber and the order.
  2. The customer_id or address_id is used to match to corresponding value type using a “Foreign Key” used for all kinds of database relationships. Foreign keys reference an item in another table.
  3. A smaller ecommerce store might have a One-to-Many relationship based on the number of items in a shopping cart.
  4. A large ecommerce store like Amazon would have many-to-many relationship given the breadth of resources that they offer.
    6.items_orders tables create Many-to-Many relationship between items and orders.
1 Like
#643

1:

  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships
    2: When forming a relation between two tables. An example could be a table with students enrolled in a university. The table could have their name and other information like their phone number. Instead of their actual phone number the table would have their phone number ID and then you could have a second table of the phone number ID and their full phone number.
    3: It gives each customer, or entry in the table, a unique ID since its possible for people to have the same name. Each customer has a customer ID and Address ID associted with it.
    4: Single person placing an order on a website when buying multiple products.
    5: Amazon ecommerce site
    6: It creates a Many to Many relationship that lists the order Ids, and each item on the order IDs
1 Like
#644

1: * One to One Relationships

  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self-Referencing Relationships

2: like a contractor saving the information of the job and the materials and the address for the home.

3: It is a foreign key in the customer table. The customer id relates to the customer table where the primary key is and the attributes from it.

4: This is the most common. Like amazon needing all the old customer orders and information including the current address and what the order contains.

5: This is an example of when things are ordered within the order. Like pizzas with multiple toppings.

6: This is meant to create a Manny to Manny relationship between items and the orders.

1 Like