SQL Relationships - Reading Assignment

Blockchain Business Course
#584

  1. Kinds of relationships: One to one, one to many, many to one, many to many, self referencing.

  2. Use a one to one relationship when something belongs (relates) to something else. For example, a house can only have one address.

  3. The column customer_id in the customers table uniquely identifies a customer. It connects a customer to an address if you obtain the address_id for the customer and look it up in the addresses table.

  4. A real world example of a many-to-one or one-to-many relationship is cities in a country. A country can have many cities, but each city has only one country.

  5. An example of a many-to-many relationship is a product for sale. A product can be purchased by many customers, and each customer can purchase many products.

  6. The items_orders table is an intermediate table, sometimes called a pivot table, which is used in a many-to-many relationship. In this case, it tracks the items in each order.

1 Like
#585

  1. What kinds of relationships can we represent in a database?
    We can represent varios kinds of relationships in a database including:
    • 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.

  • For example: for a National Insurance card number which is unique to one person.
  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” column is used to identify the customer with a unique number. We can then have the client address details in a separate address table but tied to the “client ID” by using a “foreign key”.
  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • This could be a car showroom and the database used where a customer buy a car. Other customers may also buy the same make, model and colour, so this is a many to one relationship.
  1. Give an example from the real world when would need to use a “Many to Many” relationship.
  • This is where many different customers can buy many different ítems, and many different ítems could be in the order numbers for may different customers. A good example of this is a online supermarket.
  1. What does items_orders table do?
  • This creates and maintains the relationship details between the tables of ítems and orders.
1 Like
#586
  1. ** What kinds of relationships can we represent in a database? **
  • There are several types of relationships that we can represent in a database, these consist of:
  • 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.
  • We use a one to one relationship when there is one direct link between store and consumer. Example would be Costco membership or country club membership based off customer 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?
  • The “customer id” column is referred to as address_id in the example and this allows for the customer to be identified with a unique address id. This is called a foreign key and is used to ensure the integrity of the data for all relationships.
    1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • Nfts
  1. Give an example from the real world when would need to use a “Many to Many” relationship.
  • Amazon, many people can purchase many products.
  1. What does items_orders table do?
  • The items-orders table is sometimes called a pivot table, which is used for a many to many relationship.
1 Like
#587

  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. Only one relationship can be tied to an entry. Eg: No entries (customers) can have the same cellphone 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 customer id is tied to a customer’s name, address, and address id. By having a customer’s id, you can have create a separate address table using the address id, because an address id is linked to a customer’s information like the address. This is called a foreign key, which is used to create relationships between tables

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship . A customer can make many orders and order many items each time they make an order. A FB user can have several FB friends

  5. Give an example from the real world when would need to use a “Many to Many” relationship . Each order can contain multiple items, and each item can be in multiple orders (eg: Many people can purchase the same item on Amazon, and customers can order multiple items at a time)

  6. What does items_orders table do? Create a many to many relationship between items and orders

1 Like
#588
  1. What kinds of relationships can we represent in a database?
  • one to one
  • one to many
  • many to many
  • many to one
  • self referencing

  1. When do we use “One to One” relationships? Give an example from the real world.
    This would be when a relationship 1 item is exclusive to another.
    Example being: a voting reference and person

  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 to minimise the need to repetitive information in a particular table, “customer_id” could be tagged as a CHAR(3) column, making querying the database faster.
    By introducing a “Customer_id” a long with the “Address_id” allows information from the 2 tables to be joined.
    2 tables with specific uses allows for less sparsity within each table and better structure for querying.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When facebook does relationship maps of people you know.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Supermarket database of customers and products.

  5. What does items_orders table do?
    This provides a more granular relationship of what makes up each order.

1 Like
#589
  1. One to one, one to many, many to many, and self referencing.
  2. One to one relationships are used to link a single subject on a table to a single subject on another table, like a customer linked to their address.
  3. This is called a foreign key and is a quick way to identify and link a subject on another table. On the “customers” table customer’s names are listed with a customer ID, this ID is used on the “addresses” table with the customer’s address to link the two subjects.
  4. Many to one: multiple customers making and order for 1 item. One to many: 1 customer making an order for multiple items.
  5. Multiple customers making orders for multiples of the same items.
  6. it links the items ordered to the orders made.
1 Like
#590

  1. One to One, One to Many, Many to One, Many to Many and Self Referencing relationships.

  2. A person can have only one social security number.

  3. Customer_id is a reference number that can be used as a foreign key to link data and create relationships between different tables.

  4. One mom can have many children / many children can have the same mom.

  5. Different people can be friends with different people.

  6. It connects an order made by an individual customer to items in that order. In other words, it creates relationships between ORDERS and ITEMS lists.

1 Like
#591
  1. What kinds of relationships can we represent in a database?
  • one to one
  • one to many
  • many to many
  • self referencing

  1. When do we use “One to One” relationships? Give an example from the real world.
    Tabble for customers: Customer ID, Name ; 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?
    The so called “Foreign Key” matches records, e.g. between id and address. The “Foreign Key” and it is used for all kinds of database relationships.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    An e-commerce website where:

  • Customers can make many orders.
  • Orders can contain 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.
    In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.
    E.g. Many customers order several different items with different specifications.

  2. 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
#592
  1. one-to-one, one to many, many to many
  2. Name and social security number
  3. To give the customer a number so you can make an easy relationship with another table. it is connected by using the same filed name also called a foreign key.
  4. A customer and its order.
  5. A order can have different items, and an item can be in different orders
  6. Make a many-to many relationship possible
2 Likes
#593
  1. A database can represent one-to-one, one-to-many, many-to-many, and self-referencing relationships.
  2. A representation of a one-to-one relationship, based on business coaching business: Table 1 would include columns for Client ID number, Client Name, Online Store yes or no, and URL of Store if applicable. Table 2 contains columns for URL ID number and URL addresses.
  3. It further simplifies queries, the one-to-one relationship connects one client to one address.
  4. Further to business coaching business scenario, perhaps the coach has a variety of website plugins clients can purchase to enhance their website (only applicable to clients in the database with an online store). Table 3 would then have columns for Plugin ID, Plugin Name, Description, Price.
  5. Further to above scenario, Table 4 would represent orders from businesses. Include columns for Order ID number, Business ID number, Plugin ID number, Quantity, Total, and URL ID number that it will allow download for. Many versions of plugins can be ordered for many websites.
  6. Items_orders table contains all relevant information to complete a transaction, creating a many-to-many relationship.
1 Like
#594

  1. Databases can represent one-to-many/many-to-one (brown hair, the first name Jon (single traits), to many people), many-to-many (people who can see color, to colors people who can see color can see), and one-to-one (individual to their DNA).

  2. “One to One” relationships are rare as they require a single item or person to have an attribute that is absolutely unique to them and not held by any other. Examples from the real world include DNA or fingerprints to a single person (or most biometric data).

  3. The column called customer_id in the customers table allows for a fixed size numeric number to represent (and become the primary key) to each customer record. This primary key can be pointed to as a foreign key from another table, simplifying and marking relationships between the tables (such as orders to customers), in a way that reduces redundancy and thus data sizes. The customer id can also be used on an address table, to segregate where and how information is stored but still allow the data to be joined using keys.

  4. “Many to One/One to Many” relationships include any data about a single entity that repeats in some way, or any single data point that can be applied identically to multiple entities, such as a (single) person making multiple orders, the same (single) item appearing on multiple orders, a given hair color or texture to the number of people who have that trait, or the relationship of a single given definition of soda (sweet carbonated beverage) to a list of all the beverages in a store that meet that criteria.

  5. “Many to Many” relationships occur when both sides of a relationship reaches out to multiple criteria. In the article this is given as multiple orders containing the same item (one item many orders), but also some orders containing multiple items (one order many items), which when combined means both many orders and many items on the relationship of orders to items. For facebook each user account can link to multiple friends (one person to many friends), but each of those friends are also friends to other user accounts (one friend to multiple user accounts), which when combined means many friends to many users (many to many).

  6. The items_orders table cross references order numbers to item numbers, a potential many-to-many relationship.

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

We can represent one-to-one, one-to-many, and many-to-many relationships

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

We use one-to-one relationships where there is a unique relationship between two items. An example would be the engine number of a car. That enables the car to be identified regardless of the owner, location, license plate, and any other components, all of which can be changed over time. One car cannot have multiple engines and each engine can only be in one car

  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 enables each customer to have a unique identifier and enables all of the information or data for one customer to be stored in one row of the customers table without duplication. In the customers table, there can be multiple fields, one of which would be the customer’s address. The same address could apply to more than one customer, but each customer would have one address (as far as the customer database is concerned). Any query that access a particular customer_id would in theory also be able to access the specific address of that (unique) customer_id

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

A many-to-one relationship would be nationalities. Many people have the same nationality depending on what country they come from. A one-to-many relationship could be bank accounts under a customer_id. In most banks a customer can open a savings account, check account, loan, credit card, term deposit, etc,

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

An example would be a train schedule. For instance, from one station there can be many trains going out, and also many trains coming into that same station from various outside locations.

  1. What does items_orders table do?

The Items_orders table keeps a list of the many-to-many relationships between orders, and items. One order can contain many items, and the same item can appear in multiple orders.

1 Like
#596

  1. What kinds of relationships can we represent in a database?
    One to One; One to Many; Many to Many

  2. When do we use “One to One” relationships? Give an example from the real world.
    One Person can only have one Passport (It’s not true, but it’s in 99,9% the case ;))
    or another one: One Indivdual can drive one car at the same time (but it’s related to time, which is maybe the best limiter for one to one rel. ;))

  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 the table one customer can only have one address because the information is in one table, on one line directly attached to each other. The customer ID refers to the customer name AND the address.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    An Individual can register many cars. But one car can’t be registered by many individuals.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A room can be entered by multiple participants. And each participant can enter multiple rooms.

  6. What does items_orders table do?
    creates many-to-many relationship between items and orders

1 Like
#597

1: One to One Relationships, One to Many and Many to One Relationships, Many to Many Relationships, Self-Referencing Relationships
2: When an entry only has one unique input to it. Eg, each person with their passport number.
3a: Ot’s a foreign key, used for enforcing referential intergrity.
3b: by creating a new table and linking the foreign key to the primary key or a unique key in the first table
4. Customer with many orders
5. a person can take a bus and the bus can have many different passengers.
6. to create a many to many relationship table between items and orders.

1 Like
#598
    • One to One Relationships
    • One to Many and Many to One Relationships
    • Many to Many Relationships
    • Self Referencing Relationships

  2. When we can only assign 1 thing tho 1 customer - your passport number

  3. By using the address id in a column in the costumer table it uses it as a foreign key to connect the two

  4. If some one purchases a lottery ticket through a lottery website, the lottery website (one) has to print out all the tickets for the people (many)

  5. Take for example an estate agent. He/she needs a lot of houses(many) and a lot of people to sell it to(many)

  6. It connects the item and the order

1 Like
#599

  1. One to one relationships
    One to many relationships
    Many to many relationships
    Self reference relationships

  2. One to one is used when unique data belongs only to a unique record on another table.
    A unique user has a unique address on the addresses table

  3. Customer_id is the foreign key, it is used to reference the customer from the addresses table.

  4. Many to one/one to many example: A customer can place many orders and orders can have many items

  5. Many to many example: A user can buy the same items on several orders. many orders to many items

  6. items_orders: It is used to create the many to many relationships between items and orders

1 Like
#600
  1. Types of SQL relationships: one-to-one, one-to-many, many-to-many
  2. One-to-one relationship example: Citizens and their national id number
  3. For one-to-one relationships a unique attribute value is necessary to create the relationship. It’s needed in both tables.
  4. One-to-many relationship example: Customers and orders
  5. Many-to-many relationship example: Orders and items
  6. The only purpose of the items_order table is to create the many-to-many relationships
1 Like
#601
  1. One-to-one, one-to-many, many-to-one, many-to-many, self referencing.
  2. When a record from table A can only reference one other record. For example a Person and Social Insurance Number.
  3. this creates a “foreign Key” which connects that record to other records on different tables. allows for a one-to-many relationship.
  1. One Customer can Have many orders,
  2. from the example in the artile an order can have many items, and an item can be in many different orders.
  3. This allows the the many-to-many relationship to be documented.
2 Likes
#602
  1. One to One; One to Many, Many to One; Many to Many; Self Referencing.
  2. When one entry can be linked to only one other entry. e.g. peoples ID number in their passport.
  3. It allows us to use it in other relations as a foreign key. It connects customer_id with address_id.
  4. Mother to two or more children.
  5. Students and classes or group of friends.
  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.
2 Likes
#603

[quote=“ivan, post:1, topic:6431”]

  • **What kinds of relationships can we represent in a database?
    -1 to 1
    -1 to many, many to 1
    -many to many
    -self referencing
  • When do we use “One to One” relationships? Give an example from the real world.
    -user name and phone number
  • 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?
    -Each entry in a table has a unique ID which is the primary key, that ID pairs it to the unique address of the user
  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    -Facebook, where the direct relation is link to one user, than from each member of the relation other family members are connected
  • Give an example from the real world when would need to use a “Many to Many” relationship.
    -Netflix, multiple users streaming multiple content at the same time
  • What does items_orders table do?
    used to create many relationship between the customer(ID) and the item(ID)
1 Like