SQL Relationships - Reading Assignment

Blockchain Business Course
#61

1.one to one, one to many/many to one, many to many, self referencing
2.when ther is only one unique value for one unique entry in a table. Unique DNA for a unique person
3.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.
4. I can subsribe with my unique email to many newsletters and many newsletters can arrive to my unique email
5.I can subscribe with my unique email to many newsletters and those many newsletters can arrive to many different subsribers (including me)
6. This new table creats a relationships “many to many” between orders and items from items and orders tables

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

1-to-1; 1-to-many; Many-to-Many

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

One to One is when the entity from the first table maps exactly and only to one entity within a second table. An example of this is ‘people to home 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?

It is the primary key (unique identifier for customers) and is ‘joined’ with the address_id (foreign key) to map customers to their addresses

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

Many to one - many students can take the same class
One to many - one student can take many classes

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

A sales order can have multiple items and an item can be in multiple orders

  1. What does items_orders table do?

It joins the ITEMS and ORDERS table (a junction table)

#63

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    An individual car and licence plate

  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 connected to the id in the address table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Cars can have many drivers, many drivers can have more cars.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A person can have many devices and a device can have many users.

  6. What does items_orders table do?
    Record the relations of the items which are placed in an order.

#64

  1. What kinds of relationships can we represent in a database?
    one 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.
    one name and their unique social security 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?
    it’s a way to create a unique value for that customer

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    one social security account number would be linked to many bank accounts as unique identifier

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    an ecommerce website one individual can order many of the same item as well as many customers ordering that same item.

  6. What does items_orders table do?
    it creates the link/relationship between the order table and the item table

#65
  • What kinds of relationships can we represent in a database?
    One to one / one to many / many to one / many to many / self referencing
  • When do we use “One to One” relationships? Give an example from the real world. when an entry is solely to one customer eg driving license or id no
  • 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? I How is it used in order to connect customers to addresses? customer_id is the foreign key in the customer table. It is used to refer to the data in the customer table and customer-id as reference
  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship. Many to one eg one person many products
  • **Give an example from the real world when would need to use a “Many to Many” relationship.**relation of a person to other family members
  • What does items_orders table do? To track orders and items
    [/quote]
#66
  1. One - One, One - Many, Many - Many and Self referencing.
  2. For instance when one person only can have one account. Uniqueness.
  3. This is the foreign key. Meaning that the information is referencing to the other table and is an identifier relating to that specific user.

One to Many: (1) One mother can have many children. (2) One book can have many authors.
Many to One: Many people can have the same workplace.
5) Many to Many: Students can take ”Many” courses. Courses can have ”Many” students.
6) Creates the ”Many to Many” relationship. Many items can be in ”Many” orders. Orders can have ”Many” items.

#67

  1. Relationships: One to One / One to Many /
    Many to One / Many to Many / Self referencing

  2. One to One: Only if the relation is fixed and only
    one option possible. Eg: driver-Id, taxes-ID,

  3. Each customer get an ID, which will refer to the
    customer table, which has all the names and the
    address_ID for each cumstomer, which will refer
    to the address than. It looks like a lot of work in
    the beginning, but it will save a lot of time in the
    long term, cause you only have to change one
    line and not all references, if your database gets
    bigger.

  4. One to Many: Ordering a lot of stuff from amazon.
    Many to One: All the packages are sent to one
    address.

  5. Buying stuff in a store, but paying one part with
    cash and the other part with different credit
    cards.

  6. To track, who order what.

#68

*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

*When do we use “One to One” relationships? Give an example from the real world.
Let’s say you have a table for customers,
We can put the customer address information on a separate table,
Now we have a relationship between the Customers table and the Addresses
table. If each address can belong to only one customer, this relationship is
“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?
This is called a “Foreign Key” and it is used for all kinds of database
relationships. Giving data a code number, is more efficient and and less
memory consuming (cheaper).

*Give an example from the real world when would need to use a “Many to One/One to Many” 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.

*Give an example from the real world when would need to use a “Many to Many” relationship.
For example, each order can contain multiple items. And each item can also
be in multiple orders.
For these relationships, we need to create an extra table.

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

#69
  1. One to one, one to many, many to many, self referencing
  2. When one record in a table only belong to one record in the other table ex: Name to Age
  3. Every table must have id which is unique in that table. In the article it is connected in a so called foreign key
  4. This can be for example a person can join to more groups on FB
  5. Well this can be a situation when we have Restaurants table and Customer Table and also have Meal table. One person may visit 2 different restaurant, but he eats fish and chips in both of them.
  6. Matches order id with item id
#70
  1. One to one, One to Many & Many to One, Many to Many, Self Referencing
  2. Where there are a set of specific information captured for only one unique entity in another table. Identification, drivers license and passport documents.
  3. To link a specific order to that specific customer.
    4.To track a how often specific customers visits a gym, when attendance are incentivized with a reward scheme.
  4. You have multiple customers that can order from multiple different products that you supply.
  5. It keeps tracks of the every customer and the products ordered by each customer according to each order.
#71

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    When one record in a table can only be linked to one record in another table. E.g. Name to 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?
    Customer_id is a unique identifier number for each row (customer in this case) in the table. It can be used as a foreign key to link the information in the “customers” table with information in another table.

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

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    An e-commerce shop

  6. What does items_orders table do?
    It links the items table with the orders table. ie. it details what items each order contains

#72
  1. What kinds of relationships can we represent in a database?
  • 1-1
  • 1-many
  • Many-1
  • Many-Many
  • Self Referencing
  1. When do we use “One to One” relationships? Give an example from the real world.

We use a 1-1 relationship if each attribute can belong to only one relvar tuple. A social security number in the states is an example of a"One to One" relationship.

  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?

“Customer_id” is the “foreign key”. A “Foreign Key” is a set of attributes that references a related table (data set) often referenced as the primary key. In the article the “customer_id” attribute is the “Primary Key” of the customer table. In the related table Customer Details, “customer_id” is used as the “Foreign Key” to link the two tables.

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

A traditional families home address is an example of a “Many to one/ One to many relationship”. The Father, the Mother, the Child/children all share the same mailing address and the single address shares them.

  1. Give an example from the real world when would need to use a “Many to Many” relationship.
    Cataloging students at a university. The Many different students can have many different classes ( and vice-versa ) Create a “class_enrolled” table. The “Class_enrolled” table has only one purpose, and that is to create a “Many to Many” relationship between the “student_id” and the “class_id”.

  2. What does items_orders table do?
    The “Items_orders” table creates the many to many relationship between the “items_id” and the “orders_id”.

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

Students being tied to a student ID

  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?

It is faster to read when all of the IDs are the same length. Used to associate them

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

If one company sells one product to someone, they can have multiple sales orders.

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

Amazon. Different people, different products, different orders, different addresses

  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.

#74

1

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

2
In the example of a customer database we can have a customer 1 assigned to an address 1, customer 2 to address 2, etc…

38%20PM
38%20PM1056×494 40.1 KB

3
we can connect customers and addresses. some customers live at the same address than others which is why it is better to assign an ID to both customers and addresses.

4
One to many
When I order from Zalando I ( = customer 1234) can order something new every day (= several order IDs). I still remain customer number 1234.

5
Many to many
In order 5454 I order 1 x t-shirt in size S and 1 x t-shirt in size M. The t-shirt can have many sizes, I can have several orders in place at the same time.

6
creates a “Many to Many” relationship between the items and the orders.

#75

1-What kind of relationships can we represent in a database?

We can make relationships from: one to one, one to many, many to one, many to many and self referencing relationships.

2-When do we use “one to one” relationships? Give an example of the real world.

We have the user table with the field user_id and name and a second table with the name identification where we have the column user_id and personal_id and we associate the values ​​through personal_id, since a personal_id can only belong to a single person.

3-In the article in the “One to one” section, the author of the article has a column called customer_id in the customer table. Why? How do you use it to connect customers with addresses?

This customer_id column is part of the table but as information, since the column that really connects the addresses with the customers is the address_id column.

4-Give an example of the real world when you would need to use a “Many to one / One to many” relationship.

We have the people table with the name and personal_id columns and a second table medical-appointment where we have the personal_id and appointment column. We associate the values ​​through personal_id, in this case a person can have many medical appointments but the appointment of medical appointment belongs to only one person.

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

We have the people table with the name and personal_id field and a second table with name school we have the personal_id columns and name of the school, we associate the tables through personal_id. In this case people could be in many schools and schools can be associated with different people.

6-What does the items_orders table do?

It helps to organize the items sold and then relate to the number of orders in which it was purchased.

#76

1 What kinds of relationships can we represent in a database?
An SQL database can represent the following relational functions: One2One, One2Many, Many2One, Many2Many.

2 When do we use “One to One” relationships? Give an example from the real world.
An example could be a gym membership.

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?
Customer I.D retrieves and displays customers details in a database. It’s used by entering a customers I.D/primary number in the database.

4 Give an example from the real world when would need to use a “Many to One/One to
This could be an amazon online account where a customer has many orders to many items.

5 Give an example from the real world when would need to use a “Many to Many” relationship.
I good example would be a builder’s builder merchant account where there could be multiple items across multiple orders and the same items over multiple orders.

6 What does the “items” order table do?
The"Items" table creates many2many queries between items and orders,

#77

One to one, one to many, many to one, many to many and self referencing.
When an address can belong to only one customer.
: Like a website where you can only have a specific email.
This is done to create a link between the customer and the address.
Customers can make many orders, orders can contain many items, items can have descriptions in many languages.
Online store where many people ordering multiple items.
To create a “Many to Many” relationship between the items and the orders.

#78

1 One to one, one to many, many to one, many to many and self referencing.
2 Social security number with name and last name
3 Customer_id has is a foreign key and in it a unique record in the database.
4 Many to one example many bills need to be pay by one person, and one to many. One guy orders many items.
5 Elections many people votes for many candidates
6 IT use with foreign key to be related with others tables.

#79
  1. One to One; One to Many and Many to One; Many to Many; Self Referencing
  2. One to One relationships are used when a primary key is related to one attribute or field. For example, each person with a social security number has a unique social security number.
  3. Having a customer_id helps clarify information. For example, there can be more than one customer with the same name that might be differentiated by different addresses. Using customer identifiers and address identifiers helps to differentiate customers and addresses.
  4. A mother might have a bunch of children attending school. Each child would have the same mother in the school records.
  5. An example of Many to Many relationships can be items and orders. Orders can have many items and items can be in many orders.
  6. The items_orders table creates a Many to Many relationship between items and orders.
#80
  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.
  • Customer Table and Social Security Table (one-to-one)
  • Note: Where each customer will have only one unique Social Security in database
  1. 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?
  • An ID is assigned to the customer table that contains full contact details about the customer. The ID shortens the lookup and creates better efficiency between relational tables in the database.

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

  • An Address is assigned its own table ID and so for a given row, let’s say customer A= ID:101 and Address for customer A= ID:301; there is a relationship.

Note: The database now does not need to duplicate each time contact details for a given customer and hence will reduce the overhead involved when processing relational data lookup.

  1. Give an example from the real world; when to use a “Many to One One to Many” relationship?
  • Customer A placed an online order on 11/25/2019 and again on 12/25/2020 with ACE Hardware. Each date visited, customer A ordered the same Item ID Type “AAA Duracell Battery”

5.Give an example from the real world; when to use a “Many to Many” relationship?
Customer A ordered online and on the same day (12/26):

  • Customer A Order ID:555;Item:201 = DVD Movie
  • Customer A Order ID:555;Item:202 = DVD Player
  1. What does items_orders table do?
  • Defines the relationship between two separate tables.
    Example: many-to-many relationships.