SQL Relationships - Reading Assignment

Blockchain Business Course
#81

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, and self-referencing relationships.

When do we use “One to One” relationships? Give an example from the real world.
When each element can relate to only one other element, it is one to one. For example, an address belongs to only one customer or an email belongs to only one user.

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 that helps with relationships.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
A unique customer at an ecommerce site who has many attributes, such as username, address, payment information, orders, etc.

Give an example from the real world when would need to use a “Many to Many” relationship.
A social network user who has multiple entries, multiple friends, and multiple entry comments.

What does items_orders table do?
Creates a many to many relationship between items and orders (ex. orders each with different items).

#82
  1. The kinds of relationships we can represent in a database are: one to one, one to many and many to one, many to many, and self-referencing.
  2. An example of a “One to One” relationships from the real world could be a person and their drivers license number from their home state.
  3. The customer_id in the customers table, and the same customer_id in the addresses table is what links each customer with his one address.
  4. An example from the real world of a “Many to One/One to Many” relationship could be a person and his variety of pets.
  5. An example from the real world of a “Many to Many” relationship could be the above example, except now that person is married and both spouses share the variety of pets.
  6. The items_orders table contains all of the items within all of the orders.
#83

  1. In a database we can represent several kinds of relationships : one to one, one to many, many to many, self referencing.

  2. We use “One to One” relationships when there is only one possible characteristic for each entry and when there is only one entry for a given characteristic (no repetition). For exemple each client has only one address and each address has only one 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. It gives a link from one table to another table. In this example it links the address to the customer, even if addresses and customers are in different tables.

  4. A Many to one relationship is needed between customers and orders for example, because each customer can have several orders, but a specific order belongs to only one customer.

  5. An exemple that would need to use a “Many to Many” relationship is the orders and items tables, because an order can have several items and an item can be in several orders.

  6. The items_orders table creates the relationsip between the tables orders and items.

#84

  1. There are “one to one”, “one to many” (and vice versa), “many to many” and self referencing relationships.

  2. A real world example of a one-to-one relationship is a Customer’s ID and his email address. There is only one ID belonging to only one email address.
    Another example would be a phone book, where one person has only one phone number.

  3. The customer ID is a single key to identify the exact customer without having to include names, addresses and such. Just having the name wouldn’t work as there can be several people with the same name, hence we have an ID. The author puts the addresses in a separate table and gives each a unique ID as well. This could be an efficient way to store data, because several people can live at the same address. This way the address has to be stored only once and can then, using the ID, be linked to each individual customer.

  4. Sticking to the customer database theme, a customer can have multiple orders. We could create an orders table and give each order a unique ID and link that to the customer. This would result in a one-to-many relationship (one customer, many orders).
    In a social network, a user can have only one birthday, but several people can share that date.

  5. Many-to-many relationships are a bit confusing, but an example is products within orders. An order can contain a product multiple times, and a a product can be ordered on several occasions.
    Friends in a social network would be another example of a many-to-many relationship.

  6. The items_orders table links the item IDs from the items table to the order IDs from the orders table, thus creating a many-to-many relationship.

#85
  1. kinds of relations in a db: One to one relationships, One to many and many to one relationships, Many to many relationships and Self-referencing relationships
  2. They are used when forming unique relationships between table data entries. For example, connecting a unique account/identification number to a customer.
  3. 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.
  4. When you buy something on a webshop you can make many transactions. But a transaction can only apply to one costumer. And a transaction can contain several products.
  5. Many customers that live in many cities
  6. The items_orders table creates a many to many relationship between the items and the orders.
#86
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

  1. One to One relations are if only one item can be related to one single other item.
    Per example a TIN (tax identification number) can only be related to one citizen.

  2. This is called a foreign key. This key does create the relation to an item in a different table. Each row in a table has such a primary key in order to create these relations.

  3. One to Many: A stock management can have one storage location but hold many items. Or ingredients of food. One specific food contains many ingredients.
    Many to one: Many students can be enrolled in one specific course

  4. Food: Many ingredient can be in one food product but these ingredients can also be in multiple products

  5. The items_orders table has the sole purpose to create the many to many relation

#87

  1. We can have may relationships in a database
    One to one
    One to many and many to one
    Many to many
    Self referencing

  2. A table, which has one to one relationship, could be a customer linked to their address

  3. Customer id and address id is important as these can be stored in different tables and then liked in any other table

  4. An example of many to one/one to many can be, many customers making an order on a web site/ one customer making many orders on a web site, these are expressed in Customer and Order tables

  5. A real world example of many to many relationship can be, many customers wanting many items say ‘office stationary’, each would require many items

  6. Item Orders take many table inputs that are id’s and place them in a table which have id’s only

#88

  1. The kinds of relationships that can be represented in a database are: 1) one-to-one 2) one-to-many 3) many-to-many 4) self-referencing relationships

  2. One-to-one relationships are used when one and only one piece of data can belong to another one and only one piece of data. An example would be someone’s name and their social security number.

  3. The author has a column called customer_id because it is used as a key to reference another table, i.e. the addresses table. Within the addresses table, their is a customer_id column that connects to the customer_id column in the customers table.

  4. One would use a ‘many-to-one’ relationship, for example, in a school database. A particular class of students would have only one teacher. The teacher’s name would be connected to multiple students and vice versa, creating a one-to-many/many-to-one relationship.

  5. An example of a many-to-many relationship would be, continuing with the school example, would be finding which teachers share in teaching the same students. A mathematics teacher and science teacher may have overlaps in the students that they teach.

  6. The items_orders table creates many-to-many relationships between the items and orders.

#89

SQL Relationships - Reading Assignment

  1. What kinds of relationships can we represent in a database?
  • one to one relationship
  • one to many & many to one relationships
  • many to many relationship
  • self referencing relationship
  1. When do we use “One to One” relationships? Give an example from the real world.
  • when pairing two unique things together
  • it is basically pretty uncommon
  • DNA and individual
  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 id columns are used to connect costumers to their addresses
  • costumers are linked to their addresses through the costumer_id
  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • recording friendship database
  1. Give an example from the real world when would need to use a “Many to Many” relationship.
  • intermediary table
  • orders can have many items and each item can be in different orders
  1. What does items_orders table do?
  • it links the Items to the orders
#90
  1. one to one, one to many, many to one, many to many, self-referencing.
  2. when there can be only one acceptable result. Such as a prime beneficiary when a spouse passes away.
  3. there may be more than one customer for the address, such as Alfred Pennyworth sharing the address with Bruce Wayne.
  4. incarcerated persons- there are many in one prison, one prison has many, but, a prisoner can only be in one prison at a time.
  5. one example is a person signed up for many courses, but not all the courses, here in The Academy. Yet, each course goes to many from The Academy.
  6. it matches an item to an order, this is a “shopping cart”.
#91
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

When each entry in 1 table only has 1 entry in the other table, example user table with contact details.

Customer is a unique identifier to identify the entry, there is no duplicate of this. Eg there

Many to one - Department to students

One to many - Customer to sales

Many to many - students and classes

Items_ Ordered creates a Many to Many" relationship between the items and the orders.

#92
  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 a shop has the name and address of one of its customers. The address can be unique to that customer.
    Peoples passports are unique to them.
  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 be able to search easily customers that live at the same address.
  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 students attending one college. One maths teacher has many classes to teach.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many students attend to many different classes.
  6. What does items_orders table do?
    The Items_Orders table only has one purpose, and that is to create a “Many to Many” relationship between the items and the orders.
#93
  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 it is a unique user. An example would be a user’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” is a Foreign Key and it is used for database relationships.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    One to Many - When one person orders many things from Amazon
    Many to One - When many people orders the same item from Amazon
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many people order many items on Amazon
  6. What does items_orders table do?
    Creates the “Many to Many” relationship between items and orders.
1 Like
#94

  1. One-to-One, One-to-Many, Many-to-Many, Self-referencing relationships.

  2. We use a One-to-One relationship to relate one value with another one value. For example, we could relate one national ID number to only one citizen.

  3. Customer_id is included in the customer table to give each customer a unique identification. In the address table, we can include the customer_id to make a relationship.

  4. An example of a One-to-Many relationship is one project having different assigned tasks. An example of a Many-to-One relationship is that many tasks can be assigned to one specific project.

  5. An example of a Many-to-Many relationship is song collections and songs. One collection can have many songs and one song can belong to many collections.

  6. The purpose of an items-orders table is to give Many-to-Many relationship to items and orders.

1 Like
#95
  1. one to one, one to many, many to one, many to many and self referencing
  2. one to one relationships occur when there cannot be more than one relationship to an entry- i.e. customer i.d. to customer name
  3. the customer i.d. is a foreign key in the customer table and is done to ensure only one address is assigned to each client
  4. Many to one/one to many relationship example would be ordering several clothing items on one shipment from an online retailer
    5.Many to many relationship example could be auto parts since many manufacturers make parts that are compatible with many makes of cars
  5. it creates a many to many relationship between items and orders
1 Like
#96

  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

  2. When do we use “One to One” relationships? Give an example from the real world.
    When there is only one value corresponding to another value. For example, ID number of a 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” helps to identify unique customers and to connect them with their addresses by using their “address ID”. It is done by creating a table for relationships between customer and address IDs.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many children have one mother. One mother has several children.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A list of people and a list of countries where they traveled to. Many people went to many countries.

  6. What does items_orders table do?
    It creates a many-to-many relationships.

1 Like
#97
  1. What kinds of relationships can we represent in a database?
    One to one, one to many, many to one, many to many and self-referencing when using a single table. Also when relating tables you can have Cross Joins, Natural Joins, Inner Joins, Left Joins and Right Joins.
  2. When do we use “One to One” relationships? Give an example from the real world.
    Its when you have a single key relating to strictly, to a single value. The use is not frequent since usually, a table contains more than one attribute that builds other relations, but this relation can be useful to single out a specific query for a particular attribute.
  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 acts as a referral for an Item in another table that is not been described in the current one, maybe due to privacy, as hiding the address or the wallet address its been sent. It’s known as Foreign Key,
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Photos from, Paul One to Many; People tagged in Pauls’s Photo, Many to One.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    In a Game, Charles and Ivan bought a shield; Filip and Ivan and Charles bought a sword and Filip bought an Armour.
  6. What does items_orders table do?
    It Creates a Many to Many relationships between items and orders, using two foreign keys, occurs when both sides of the relationship need multiple records and attributes.
1 Like
#98
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

when a value does not belonf g to anything else or is used onece, e.g. linking an address to a customer.

The customer id is used to link or join with other id’s.

When many customers order the same thing or when many things are ordered by one customer.

If you have many products that are part of many orders.

It can be joined with the other tables to identify what customer has ordered what and how many, and how much.

1 Like
#99
  1. One to One, One to Many, One to Many, Many to Many
  2. Customer name and the mobile number
  3. Customer id is shared with both tables for one to one matching
  4. Customer purchase record (Customer to many purchases)
  5. Product to Purchase records
  6. Item_orders will link customers and items
1 Like
#100

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?

  • A user and their address.
    b. Give an example from the real world.
  • Individual people and their public crypto keys
  1. In the article in the “One to One” section, the author has a column called customer_id in the customers table.
    a. Why?
  • The customer_id is called a " Foreign Key " and it is used for all kinds of database relationships. It is used to ensure referential integrity of the data
    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.
  1. 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. Shopify is a great example of this 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. Give an example from the real world when would we need to use a “Many to Many” relationship.
  • An example would be: Ordering a pizza with different toppings
  1. 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