SQL Relationships - Reading Assignment

Blockchain Business Course
#504

  1. relationships we can represent in a database are

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

  2. we use one to one relationships from the real world for example, ordering a milkshake from from mcdonalds through an app which has your name and your order of that milkshake.

  3. refers to the matching record in the Address table. This is called a “Foreign Key” and it is used for all kinds of database relationships.

  4. an example of real life is i work in an online grocery department in a grocery store, where customers have there name, order number, and list of groceries along with many other things. this would be where we would use many to one/one to one many relationships because, the customer keeps coming back and you as the server provide their last order details and you have to use their information over to identify between customers. also sometimes customers could have 2 orders in same day they pick them both up.

  5. when someone for example makes multiple orders on items at a vape shop.

  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
#505
  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 there is only one relationship. For example the Name of the customer and his adress.
  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 called a foreign Key. It is used to link 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.
    One customer (me). Can purchase many different alts, but they belong to me(If I put it on a private key :slight_smile: )
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Each dog have 2 parents. Each parents(dogs) can have several puppies.
  6. What does items_orders table do?
    items- orders links two tables, the customer and the order.
2 Likes
#506
  1. Self-referencing, One-to-One, One-to-Many, Many-to-Many
  2. Sometimes we want to separate basic user information (used for multiple apps) from user profile information for the particular app. In this case basic user information (username, login, password) can be stored in one table and user profile in another (avatar, bio, wallet address, karma score). In this case we have One-to-One relation between a user_id and user_profile_id, where each unique user can only have one user_profile_id (given that user profle for this particular app has parameters that are not used in other apps) and for each user_profile_id can only be one user.
  3. customer_id identifies a unique user, because the name field in the given example does not require unique values. This enables to relate a unique user with a unique address.
  4. I would use One-to-Many relationship for transactions. One customer can have many transactions but each transaction belongs to only one customer. In this case we store user_id in the transaction. Another example will be the country of birth of the user. One customer can have only one country of birth, but each country has a lot of users. In this case we store country_id in users table.
  5. Each movie can have multiple genres and there are many movies corresponding to each genre. We will need a separate table to match movies and genres.
  6. Relates items to orders, so that we can find out: what items are in a specific order, or to which orders does belong a particular item.
1 Like
#507

1 to 1
1 to many (and vice versa)
self-referencing

If a certain entity can only belong to an individual entity. For example, someone’s Facebook account can only belong to one person. However, the person might have multiple accounts.

This is an example of a Key:value pair. The number represents the customer and can be applied to associate the customer with the address.

A person can enroll in multiple online courses at an online academy. (one to many)
Multiple courses can be enrolled by different people. (many to one)

Multiple planes can land at multiple airports over the course of a week.
The airports can host multiple planes over the course of a week.
I might be wrong on this one.

It represents “many-to-many” relationship.

1 Like
#508

What kinds of relationships can we represent in a database?
one to one; one to many; many to many; self referencing

When do we use “One to One” relationships? Give an example from the real world.
We use one to one when the relationship when there is only one pair of items like customer_id and address. Now a person could have more than one address but if you have a standard that each address gets a unique id then that is 1: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 is the key that connects the two tables as they both have it - is a common field which can link data together.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
A customer_id can place many different orders.

Give an example from the real world when would need to use a “Many to Many” relationship.
An item may be in many different orders and orders can contain my different items.

What does items_orders table do?
It is a lookup table that shows the relationship between the orders and items.

1 Like
#509
  1. One to one relationships. One to many, many to one. Many to many. Self referencing.
  2. One customer and his payment option.
  3. Because there will be an individual address in the address table who holds the same ID and that’s how you will find the customer’s address.
  4. People who go to the same school or who was born on a certain day.
  5. It structures what orders hold which items.
1 Like
#510
  1. One to One, One to Many & Many to One, Many to Many

2.Maybe a database that displays a “user” and their “crypto wallet”

3.A customer ID can be used as a FOREIGN KEY for another table. The Customers can be just the names which isnt necessary to find search and communicate with other tables.It could be used to link multiple ID’s to a given address as their name would be different.

4.Perhaps a store that sells construction building materials to a big construction company. We could have a database that links all the employees of that company to what items they order and as a company can see all the “grouped” items theyve ordered.

5.For a Many to Many, we can say a database for a Merchant to organize what items have gone to certain orders numbers; and because each order number can have multiple items, this info could be retrieved in more complexity.

6.It acts as a Foreign Key between ORDERS and ITEMS

1 Like
#511
  1. One to One, One to Many, Many to Many
  2. When a customer needs to be linked to an address but they will only ever have one address.
  3. The customer_id was the primary key which is used to uniquely identify each entry.
  4. E-Commerce when people can have multiple orders or multiple items.
  5. E-Commerce when people can have multiple orders or multiple items.
  6. It shows all of the items sold and which order they were in. This allows for a person to see who ordered it, when they ordered it, and various other serches.
1 Like
#512
  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.
    In a customer DB of a trading company, for expl., there is a “one-to-one” relationship between the AddressID and the Address data, assuming that every possible customer will have a unique invoice or delivery address.
  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? Answered above in (2.).
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Thinking of Amazon, for expl.:
  • Customers can make one or many orders.
  • Orders can contain one or 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.
    A student can attend different classes, and these classes are given by different professors
  2. What does items_orders table do? This table establishes the relation between the orders (and its content) and the customers and its characteristics, like name, address, bank account, etc.
1 Like
#513
  1. What kinds of relationships can we represent in a database?

One to one, one to many, many to one and many to many

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

When you have two data fields linked with a foreign key and the second value can only ever relate to the first value. Someone signs up to a newsletter and the form captures email address and first name, the email address would only ever reference that person’s first name and no other data field (as the person would only ever have one first name).

  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?

This is the “foreign key.” It is required to look up value on another table. In this example Customer_id is the key that links the two tables of Customers and Addresses.

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

In a library - someone may check out multiple books. One user, many different books on loan to them.

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

There may be 10 copies of each of the books in the library. Some or all may be checked out, some or all may be available. 1 book may be checked out by many users and each user may check out many books.

  1. What does items_orders table do?

This creates a many to many link or relationship between the two tables - the product catalogue (items) and the orders. An order may contain many products and a product may exist in many orders. Without the items_orders table this link would not exist.

1 Like
#514

Answers

  1. There are 4 different kinds of relationship in a DB:
  • One to One
  • One to Many (and Many to One)
  • Many to Many
  • Self Referencing

  1. One to One relationships are used for managing unique dependencies.
    For instance, every person has a unique fiscal code. If we put these two informations into two different tables, we must have 1to1 relationship.

  2. In that example, customer_id is the foreign key. It is essential in order to communicate the relationship between the two tables. It’s not necessary to declare, we can retrieve it using a join query.

  3. One to Many / Many to One relationship is very common. For example, a book is typical written by an author; but the same author could have written other books.

  4. Example of Many to Many relationship: some companies product some different taste of ice-creams.

  5. In that example, items_orders provides Many to Many relationship between items and orders.

1 Like
#515
  1. types of relationships:
  • one to one - (ie - customer to address)
  • one to many, many to one (ie - doctor to patients, patients to doctor)
  • many to many (ie - items to order history)
  • self referencing ( ie - customer referral, when initial source is also still a customer)

  1. 1 to 1 - is a unique relationship between two entities not duplicated in the table. an example: my name to my address on a mortgage loan.

  2. customer_id is referenced as a ‘foreign key’. by assigning a numerical value to the record (row) you create an organizational flow for the data. Customer_id is an attribute, just as the other record fields. therefore, the numerical value of a record allows you to interact with all the data to build relationships, without creating a higher value for one item over the others.

  3. a real world example of one to many relationships: When a doctor wants to analyze the patient data for his/her practice. those patients all have unique attributes, but all have an underlying connection to the doctor as they are all patients.

  4. many to many: perhaps a good example is a sports arena. you have two sets of fans, rooting for two separate outcomes. yet they all come together because of their passion for the sport itself.

  5. an items_orders table has only one function - to create a many to many relationship.

1 Like
#516
  1. What kinds of relationships can we represent in a database?
    one to one, one to many, many to one, and self-referencing
  2. When do we use “One to One” relationships? Give an example from the real world.
    time stamp and temperature from a sensor
  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 connect a relationship between the customers table and the address table. In this case, the customer_id is the Foreign Key.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. Customers can make many orders; orders contain many items; 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. Ordering a new car with multiple options
  6. What does items_orders table do?
    It creates a “many to many relationship” between the items and the orders.
1 Like
#517

1.)

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

2.)
In a one-to-one relationship, one record in a table is associated with one and only one record in another table.
For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.

3.)
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

4.)
In a e-commerce website.

Customers can make many orders.
Orders can contain many items.
Items can have descriptions in many languages.

5.)
a Students table, which contains a record for each student, and a Classes table, which contains a record for each class.

6.)
it’s purpose is to create a many to many relationship between the items and orders.

1 Like
#518
  1. one to one, one to many, many to many, self-referencing
  2. a row in 1 table can only correspond to a row in another, e.g. a single person (a row in one table) can only have one unique token of some kind (another row in another table)
  3. Because customer_id can easily be shared accross tables to do one to one relations.
  4. I, as a person, could own different lambo’s, but a lambo can only be registered to 1 person.
  5. Multiple people could be allowed to drive muliple cars.
  6. It connects two other tables to make a meaningful many to many relationship.
1 Like
#519
  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.
    Name of a customer and his/her address

  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?
    A column called address_Id is inserted in the customers table to link the addresses from the address table to the customers in the customers table.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Customers can make several orders
    Orders can have several items
    Items can have several models

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Orders having several items and those same items could be found in several orders

  5. What does items_orders table do?
    Link items to orders.

1 Like
#520
  1. What kinds of relationships can we represent in a database?
    One to one relationships
    One to many relationships 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.
    Name and phone number when person has only a one 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 author wanted to link two tables together. In this case customer_id is the foreign key
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    For example ordering products from the webshop. Customer can have many orders, orders can have many Items.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Investor can have different tokens in his portfolio and different tokens can be part of different investor portfolios
  6. What does items_orders table do?
    It relates items to orders with many to many relationship
1 Like
#521
  1. **What kinds of relationships can we represent in a database?
  • one on 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.
  • Person name and height without Any other variables
  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 ”customer_id” We can define the actual Customer without always using the whole Customer information
  • to collect and distribute the information easier and faster
  1. **Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • me Personally refuelling my car in a different gas stations and Then I could make different additional car services at the same time
  1. **Give an example from the real world when would need to use a “Many to Many” relationship.
  • relationship.
  • if I would like to track and/or analyze our family Daily purchase at grosery shop.
  1. **What does items_orders table do?
  • it defines all different items ordered
    Under one order id.
1 Like
#522

  1. one to one relationship
    one to many and many to one.
    many to many and self referencing relationships

  2. when its a unique user with one address.

  3. The author calls it a “foreign key”

  4. When one customer makes many orders, or a website can be translated into many languages.

  5. One person can go and se many movies, and the cinema can hold many persons.

  6. It creates a many to many relationship between items and the orders.

1 Like
#523

1- One to one, one to many, many to one, many to many, and self-referencing.
2- It’s used when an entry field can only belong to one object. For example a person and their social security number
3- That represents the " Foreign Key", and it is used to create relationships between different tables. Its used, for example, when there is a user that is given a foreign key in order to connect to different objects, like address, order, and so on, therefor creating relationships between different tables
4- For example, a user may have many houses, this is one type of one to many relationships
5- It’s used in e-commerce when many orders have many items
6- Is used to create the many to many relationships between items and the orders

1 Like