SQL Relationships - Reading Assignment

Blockchain Business Course
#101

Read this article: https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships–net-8561

503 Service Temporarily Unavailable

#102

From: https://www.techrepublic.com/article/relational-databases-defining-relationships-between-database-tables/
There are three types of relationships:
One-to-one: Both tables can have only one record on either side of the relationship. Each primary key value relates to only one (or no) record in the related table. They’re like spouses—you may or may not be married, but if you are, both you and your spouse have only one spouse. Most one-to-one relationships are forced by business rules and don’t flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.
One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and a parent. You have only one mother, but your mother may have several children.
Many-to-many: Each record in both tables can relate to any number of records (or no records) in the other table. For instance, if you have several siblings, so do your siblings (have many siblings). Many-to-many relationships require a third table, known as an associate or linking table, because relational systems can’t directly accommodate the relationship.
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
#104
  1. One to one, one to many, many to one, many to many and self referencing.
  2. A list Social Security Numbers that corresponds to a list of the people who go with those numbers.
  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 list of parents and a list of their kids.
  5. A list of siblings and a list of their siblings.
  6. This table relates an item to an order by the id number of each.
1 Like
#105

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 there cannot be more than one relationship between an entry. For example, asocial number isonly tied to one 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?
Foreign keys are used for all kinds of database relationships. It is used to ensure referential integrity of the data.
Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
When doing a register for houses for example: Many persons can live in one house. (Many to one)
Or when doing a register for owning car/cars: One person can own many cars. (one to many)
Give an example from the real world when would need to use a “Many to Many” relationship.
For example on a web store. Each order can contain multiple items, and each item can also be in multiple orders.
What does items_orders table do?
It is used to determine the many to many relationship between the order_id and item_id table

1 Like
#106

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.
Name and address is very common example. or possibly something like driver and car.

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 unique. while even names could be the same. more than one person could share an address. by making the customer id field it ensures customer data is kept separate from each other. An address field could be used either as an attribute (field) in a single table or stored in a separate (foreign) table.

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

A list of friends could be used in both senses. I have a group of friends. Some have cars. a friend could have 1 or more cars or none at all.

Give an example from the real world when would need to use a “Many to Many” relationship.
Multiple users and multiple items. Such as maybe students and assignments.

What does items_orders table do?

It relates the items to the orders. This would allow you to keep track of the items being ordered and the orders pending that contain those items.

1 Like
#107
  1. one-one, one-many, many-many, self
  2. parent has 0 or 1 child (saves space if no child); customer - address
  3. customer_id is the parent primary key that links to the child foreign key named customer_id; SELECT * from customer LEFT OUTER JOIN order ON customer.customer_id = order.customer_id
  4. One-Many relationships where a parent can have 0 or more children and the child must have exactly one parent. e.g. customer - order
  5. Many orders can reference many products
  6. The items_orders table is a referential table that resolves a many-many relationship.
1 Like
#108 
What kinds of relationships can we represent in a database?

one to one, one to many, many to many.SR if you want to includ it.

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

one person got one address

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 key to connect the person to the address by pointing the correct customer record to the correct address record.

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

One person got one address/one address can have mulitple persons tide to the address
one supply er to one company/ the specific supplyer have multiple customers

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

Family relationship db would be many to many relationships

What does items_orders table do?

Creates Many to Many realtionsships between items and orders.

1 Like
#109

  1. Databases are used to store multiple tables that can have multiple interrelations. These include;
    One to One Relationships / One to Many and Many to One Relationships / Many to Many Relationships / Self Referencing Relationships

  2. In a table of names and addresses, each name can only have one address associated with it. This is a ‘one to one’ relationship.

  3. The value created in the customer_id field is called a ‘Foreign Key’. Foreign Keys enable relations to be created and connected or interlinked between many different tables and the information they contain. This is all stored within the database

  4. "Many to One/One to Many”. If I have a list of clients, I would be invoicing them for different jobs on different occasions. This would be a ‘one to many’ relationship, because although there is one value for the individual client in the invoice database, there would be multiple values across different invoices, which in turn could also contain different itemised services.

  5. "Many to Many”. In my invoicing database, I would hope to have more than one client, as well as more than one service that I was offering. This would be a ‘many to many’ relationship, because there will be many clients as well as many services that I need to track.

  6. In the example, the items_orders table would allow the interrelation, if any, between the many clients and the many services to be known and tracked by looking at the database.

1 Like
#110
  1. 1:1, 1:Many, Many:Many, Self-referencing
  2. When we want to link only one row in a table to only one row in another table. An example would be a table of students at a school and linking each row in this table to a table of student birth dates. Each student can only have one birth date.
  3. This is done to provide a unique identifier for the customer. This identifier can then be placed in any other table to link to all available customer information in the customer table. Customers are connected to addresses by the address_id column (called the “Foreign Key”). With both the customer_id and address_id in the same row a connection is made.
  4. Many:1 example - an example would be the many students that have completed this specific Ivan on Tech Academy course. A 1:Many example is that Ivan on Tech Academy has many courses.
  5. Many:Many example. A table of students and a table of courses - Many students completed many courses in Ivan on Tech Academy
  6. It creates a Many:Many relationship between the items and orders. In the example table of the article there are two unique orders an each has two items in it.
1 Like
#111

  1. One to one
    one to many and many to one
    many to many
    self-referencing

  2. When one entry belongs to one customer.

  3. Customer id is a foreign key in customers table. It creates a relationship between databases of customers and their address

  4. Student has many classes to attend. Class has many students

  5. Each order can contain multiple items. And each item can also be in multiple orders

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

1 Like
#112
  1. One to one relationships - One to many - Many to one - Many to Many - Self reference relationships
  2. When an unique attribute in a table matches an unique attribute in another table. – An extra data for a certain type of clients, client with benefits that you would store extra info.
  3. It has it or represent an unique client. It didne use it to connect to the adresses, it uses the address_id column
  4. A client having one or more telefone numbers
  5. In a gym, a client can assist to many classes, and in a class could be many clients
  6. It creates a many to many relationship between items and orders
1 Like
#113
  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 an unique attribute in a table is used has reference in other table, meaning they share a relation between tables.
    Example:
    Table Users contain an ID unique attribute, next attribute is Name, next is Social ID.
TABLE USER
[id:0, name: ivan, social id: 0101]

Lets say an Store hold this data in a database, each time ivan buy something, another tables work each other to generate a result based on relations they got, meaning: TABLE USER point Social ID for TABLE ORDER, a one to one relation between TABLE USER and ORDER is created by the same Social ID attribute.

TABLE ORDER
[id: 0, customer: social id, items: {' ',' '}, total cost: amount$]

Store inventory table

TABLE ITEMS (store inventory)
[id: 15, name: coffee, cost: 15$, store units: 50]
[id: 20, name: water, cost: 25$, store units: 100]
[id: 25, name: milk, cost: 15$, store units: 40]
[id: 30, name: sugar, cost: 10$, store units: 70]

ONE TO ONE
TABLE ORDER (when ivan go buy in store)
[id: 845, customer: 0101, items: {'15', '20'}, total cost: 40$]

Table ORDER contain a relation with Table USER for social ID has a “foreign key” to access that attribute.

  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?
    I think i describe this in question 2.

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

ONE TO MANY/ MANY TO ONE
TABLE ORDER (ivan's orders)
[id: 845, customer: 0101, items: {'15', '20'}, total cost: 40$]
[id: 846, customer: 0101, items: {'25', '30'}, total cost: 35$]

Table ORDER contain a relation with Table USER for social ID has a “foreign key” to access that attribute.
, but in Table ORDER the customer with social ID 0101 can have many items that share relation between that social ID.

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

Table ITEMS contain a relation with Table ORDER for item ID has a “foreign key” to access that attribute.
, but in Table USER the customer with social ID 0101 can have many items that share relation between that social ID through the Table ORDER.

  1. What does items_orders table do?
    In this case, used to determine the Many to Many relationship between the other tables.
2 Likes
#114

Nice answer … :wink:

#115

well thanks @ivga80 , man after i finish the post i said to my self “yeah…this is 100% inaccurate or nobody will understand it, probably im using the worst example to describe it”

#116

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.
    Eg, Customer name / Customer 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? This is referred to as a Foreign Key, which is a key used to link to tables together.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Eg; A customer / Ordered Items

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Eg. Items in one order/ those items in various orders

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

1 Like
#117

  1. What kinds of relationships can we represent in a database?
    One to One,
    One to Many/ Many to One
    Many to Many
    Self refering.

  2. When do we use “One to One” relationships? Give an example from the real world.
    Social security nbr can be associated to one person, one key value

  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 is a way to create unique value/id for that customer. A foregin key.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many customers that can order many different items that your service supply.
    Items can have descriptions in many languges.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    An E-commerse shop,like Wish or Amazon. Many different customers and orders that comes from many different retailers/addresses

  6. What does items_orders table do?
    It tracks who ordered what, link between customer table and item table.

2 Likes
#118
  1. One to one, one to many, many to one, many to many, self referencing
  2. For example, a customer table and an address table. If a customer can have only one address and one address can have only one customer, then this relation is one to one.
  3. The customer_id is in the address table a foreign key that comes out of the customer table. This is used as the relation between the customer table and the address table.
  4. In a ground school, there are groups which can have many kids. A single group can have many kids, but kids only have a single group. This is a one to many/many to one relationship.
  5. In university, there are many courses and many students. Students can be rolled in in many courses and many courses can have the same and multiple students. This is a many to many relationship between a Courses table and a Students table.
  6. It creates the many to many relationship by listing all the items that there are in a single order (while a single customer may order multiple items and one item can be ordered by multiple customers).
2 Likes
#119

[quote=“ivan, post:1, topic:6431”]
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.**We use one on one relationships to connect 2 pieces of information soley related to each other. A real world use case might be a particular user and an email 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 used in all kinds of data base relationships.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship** A many to one example might be many Amazon user-id’s are ordering a particular face mask (one item), and a one to many example might be, one user-id buying many items such as masks, wipes, cleaners, food, etc.

5. Give an example from the real world when would need to use a “Many to Many” relationship.An example of a many to many relationship might be, In some cases, multiple user-id’s are ordering multiple items.

  1. What does items_orders table do?**
    [/quote]
    Every order can have multiple items and every item can have multiple orders, or a “many to many” relationship that must be resolved by a Join or intersection entity, wiht 2 foreign keys distinguishing items and orders.
1 Like
#120
  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
  1. When do we use “One to One” relationships? Give an example from the real world.
    Name and your personal ID 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 used to associate the name to the specific ID. It is a one-to-ne relationship. When you input the customer ID, it gives the address id.
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Name and gender
  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Students Name and Subjects they taken in this semester
  5. What does items_orders table do?
    To associate the order with the items inside it.
1 Like
#121
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

When comparing two tables and one row has a relationship with only one other row from a different table
Ex. from real life: keys from the home belongs to one lock

It is usefull to create an ID for the items in your table, if for example 2 customers have the same name

Many to one: many different students belong to one class
One to many: in one wardrobe are many different clothings

Each class can contain multiple students and each student can also be in multiple classes

Its only purpose is to create a many to many relationship between items and orders

1 Like