SQL Relationships - Reading Assignment

Blockchain Business Course
#444

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    We use one to one if there is a unique relationship. Name and gender datas or date of birth

  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 ID can be used in different tables to connect (relation) them with each other.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    An account on Amazon can have many orders in place. As well the account could have more then one delivery or email addresses

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A car rental: Many orders to many customers

  6. What does items_orders table do?
    to create a “Many to Many” relationship between the items and the orders.

1 Like
#445

  • What kinds of relationships can we represent in a database?
    One to One, One to Many, Many to Many

  • When do we use “One to One” relationships? Give an example from the real world.
    One to One table relationships are used to additional data about a group that is not always appropriate for all records in the table. For example you may have a table that contains a list of people. Some of the people are users and some are users that are also editors. All users are stored in this table, but not all users have editor privilege’s. Users would have a record only in the users tables. Editors have a record in the user table (name, login, password, etc) and a record in the editors table with columns for level of access (create new blog; edit and create blogs; edit, create, delete blog. While all users could read the blogs.

  • 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 id that identifies a specific customer. The address ID column in the customer table provide a unique key to locate a specific address that is associated to the specific customer based on the customer id.

  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A one to many could be used for customers to orders, orders to line items, Product to Product attribute, Business to addresses/locations, business to contacts, Product to Product parts, etc.

  • Give an example from the real world when would need to use a “Many to Many” relationship.
    You could have a Many to Many with business to address. One business may have many locations and one location may have many businesses.

  • What does items_orders table do?
    It stores a list of items that belong to a specific order.

1 Like
#446

  1. One to One, One to Many, Many to Many are examples of database relationships.

  2. One to One relationships are used to relate an a group of objects to another group of objects. An example would be the relationship between a customer list and and an address list of customers.

  3. The customer_ID in the customer table relates the customer to the customer’s address but also enables the customer_ID to used to define a relationship to other tables such as orders as well.

  4. A Many to One and a One to Many relationship can be used to relate between a customer, what the customer is ordering.

  5. A Many to One and a One to Many relationship can be used to relate between a customer, what the customer is ordering and the items that make up the order.

  6. An Items_orders table list the relationship between the order and the items that make up that specific order. The number of items in the items_order are runique for each customer but the list of items than can be ordered represent a many to many relationship between the customer and the items that can be ordered amongst multiple customers.

1 Like
#447
  1. Relationships can be represented as one to one, one to many, many to many or many to one.
    This is also known as cardinality where the “weak” side of the relationship is usually the record that is on the right side of the relationship vs. the “strong” side of a relationship.
  2. I use this with hosted components of a CMDB (Configuration Management DB) where a host “hosts” hardware or software objects that run on that computer system. Usually this would be done to count licenses associated with the host. For example an Oracle database running in a cluster of x number of hosts. The license is then counted on the SID of that DB. License is related to the DB, and DB is related to the host it is running on.
  3. customer_id is used as an index. A db can have several indexes created to store a referende to data in that index. This speeds up the access of the data in the dB. We can measure the “performance” of these indexes and rebuild them if they get too stale.
  4. In my “real world” we use Many to One when a critical server has many subscribers. From this we can calculate “impact” measurement on how many users would be impacted if the recource became unavailable. One to many cardinality is used for hosts that have IP Endpoints or basically host multiple objects of the same type.,
  5. Many to many (for me anyway) is used as a Dependency type relationship. Often used to represent a cluster of databases.
  6. that table just stores the orders per used. it can be used with a ForeignKey for the user table and so on.
2 Likes
#448
  1. one on one; one to many and many to one; many to many; self-referencing
  2. We use it only when there is one relation between entry and output. Example: Person and their Tax number. Only one person to one tax number and vice versa.
  3. It is the primary key and identify the customer. For each address_id, which is a foreign key, there is a relation to the address table to identify that customer.
  4. Student and classes.
  5. shopping products and customers. Many customers could buy many products.
  6. item_orders table are the relation table of order and item to shop. It is a many to many relationship.
1 Like
#449
  1. Relationships we can represent in a database
  • one to one - a unique stamp of an entity: logo of a company (2.)
  • one to many - the list of all the different rooms of a property (4.) and a specific room that is present in different properties.
  • many to many - the different rooms found in different properties (5.)

  1. The customer_id column is created to identify the customer. An address column is created with its id when different customers could have the same address. That is why we would create a second table with customers addresses and link it with a foreign key to the customers table.

  2. The items_orders table creates a relationship of many to many.

1 Like
#450
  1. Relational Databases can represent one to one, one to many, many to one, many to many, and self representing relationships.
  2. One to one relationships when two items can only belong to each other. a great example would be a persons social security number.
  3. The customer ID is used in the customer table to create a primary key, which will add a functionality and cohesiveness for the database among multiple tables. The customer id also eliminates the redundancy of having to add the personal data of the customer in other tables where it may not be necessary. The customer id is used in a different table to store and/or access that specific information and creates greater efficiency.
  4. many different people can own a specific device, like a Samsung Galaxy 10. Also note that the Samsung Galaxy 10 has many owners.
  5. A real world example of Many to many might be that millions of crypto investors own many different assets, and in turn each crypto has many different investors.
  6. the items_orders table creates the relationship between items and orders, which are many to many. many orders have multiple items, and many items are on multiple orders.
1 Like
#451

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.
2 Likes
#452

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    In the case where have a person’s legal passport number and national ID number, there we have a one-to-one relationship because each person only has one passport and one ID number which can correspond to one another.

  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 being used as foreign key in table customers in order to link it to the addresses table since addresses table also contains the same customer_id column.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many-to-one example is tying sales order to a specific customer since one customer can have multiple sales orders.
    A one-to-many example is shopper and the shopping items being purchased since one shopper can buy have multiple items.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    One example would be college classes and students since each student can be enrolled in multiple classes and each class can have multiple students.

  6. What does items_orders table do?
    It relates to the items being ordered by a particular customer, quantity and price paid.

1 Like
#453
  1. One to one, one to many/many to one, many to many and self referencing
  2. We use one to one relationship when for example matching person name and gender
  3. Customer id simplifies the data to it can be a one to one relationship by creating a separate table to hold the data in customer_id
  4. Example of one to many is taking a person’s order for food. Customer wants many items on their meal.
  5. An ordering system that takes multiple orders per client and has multiple items per each order
  6. Create a many to many relationship between the items and the orders
1 Like
#454

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

    If you talk about you bank account or the address where you live.

  • 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 refers to the matching record in the address table of database, but talks about a unique customer.

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

    When accidentally multiplicate an order in a restaurante or when we dupplicate a sale of 2 products with the same code.

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

    When you classify something twice for two people different.

  • What does items_orders table do?

       They create the list fir many-to-many relationship and the orders.
1 Like
#455

Excellent answers, It’s easy to understand. Please keep them like that :muscle:

Carlos Z.

#456
  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 entry/field such as an address can belong to only one customer, some identifier which soley belongs to that customer, this relationship is “One to One”.
2b. A single customer and their 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?
    This is called a " Foreign Key " and it is used for all kinds of database relationships. Its primary role is enforcing referential integrity between data in two tables.
    3b.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. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table. For example, a table called Customers has a primary key called customer_id . Another table called Customer Details has a foreign key which references customer_id in order to uniquely identify the relationship between the two tables.

  2. 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 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.
    A list of students and a list of classes, where a class can contain many students and a student may be enrolled in many classes.

  2. What does items_orders table do?
    The items_orders table creates a many to many relationship between the the orders and items.

1 Like
#457

SQL Relationship - Read

  1. The following are the kind of relationship we can represent in a database:

  • One to One Relationships

  • One to Many/ Many to One Relationships

  • Many to Many Relationships

  • Self Referencing Relationships

  1. We use one to one relationship when one row in a table is linked with only one row in another table and vice versa. For example, each driver has only one id, and each driver’s id is assigned to only one person.

  2. The author referred to customer _id in the customer because it a foreign key(referring to a primary key from another table). It links the two tables ie. customer id and address id.

  3. Example in the real world of Many to One Relationship:
    A Many to one relationship in a Doctor’s Surgery.
    Doctors Table - each doctor has a primary key (ids)
    Patients Table - each patient has a primary key (ids)

  • Both tables are linked. Many patient can see one doctor or one doctor can see many patients vice versa.

  1. Example in the real world of Many to Many Relationship:
    A Many to Many relationships of a Doctors Surgery. Patients, visits and Doctors:
    Doctors Table - each doctor has a primary key (ids)
    Patients Table - each patient has a primary key (ids) Table
    Visits Table - stores the primary keys(ids) as foreign keys showing how many visits.

  2. Item_Orders Table shows a Many to Many Relationship between the items and the orders ie. each order can contain multiple items and each item can also be in multiple orders.

1 Like
#458

  1. The relationships that we can represent in a database are:
    One-to-One
    One-to Many
    Many-to-Many
    Self-Referencing Relationships

  2. We use One-to-One relationships in instances where a value in one table corresponds to only one value on the related table ie. List of SSN

  3. The author has a column called customer_id in the customers table as a means to assign a foreign key to link it to the addresses table, which also contains the customer_id column.

  4. A real world example of an instance where we would need to use a Many-to-One/One-to-Many relationship would be in a shopping application where one or many customer(s) can make multiple orders with multiple items in each order.

  5. An example of a Many-to-Many relationship is a school class scheduling application where there are many students, each with a different course load.

  6. The items_orders table defines a Many-to-Many relationship between the items and orders.

1 Like
#459

  1. The kind of relationship are :
    a. One to one relationships
    b. One to Many and Many to One Relationships
    c. Many to Many Relationships
    d. Self Referencing Relationships

  2. A user and his national id number for example.

  3. The Customer id is a unique data connected to the customer name and is called Primary Key. The Customer ID is related to the Address ID and this Address ID is related to another table through the “Address id” which is called Foreign Key and relate two different tables within a database.

  4. A college where a student may have to complete a course and a course may have many classes to be completed.

  5. Many clients may order many items and many items may be ordered many times by many clients

  6. Create many to many relationships between orders and items.

1 Like
#460

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 one entry can only be linked to another entry. E.g Telephone 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?
To create a relationship between the Customers and the Addresses table.

4.) Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
When one customer makes many orders or orders contain many items.

5.) Give an example from the real world when would need to use a “Many to Many” relationship.
Many people order the same item_id but get an unique order_id.

6.) What does items_orders table do?
They create a “Many to Many” relationship between the items and the orders.

1 Like
#461
  1. One-to-one, one-to-many/many-to-one, and many-to-many relationships can be represented in a database

2.One-to-one relationships are used when only a single unique entry in a database table can be linked to another table entry. A real world example of this would be linking an individual to an address or birthdate. In the real world, they only have a single version of these attributes.

3.In the referenced table, the customer_id is the unique value to each customer record, which can be referenced by other tables. This is known as a Foreign Key and is used in tandem with the address_id field to create the one-to-one relationship.

  1. An example of the many to one/one to many relationship would be a database used for a social media website. Each user could create many posts but each post could only have one originator.

  2. An example of a many-to-many relationship could be found in the same social media website example in the form of group membership. A user can be a member of one or more groups and a group can have one or more members.

  3. The items_orders table connects the items and orders tables together in a many-to-many relationship via the order_id and item_id fields.

1 Like
#462
  1. One to one; One to many; Many to one; Many to many; Self-referencing
  2. When a unique record in one table can only correspond with only one record in another table. Ex. Name and Social security #
  3. Customer id# is a foreign key in the customer address table.
  4. An e-commerce store with one customer with a variable number of orders but many orders that belong to a single customer
  5. An e-commerce store with many orders with many items and many items going to multiple orders.
  6. Create many to many relationships between items and orders
1 Like
#463
  1. What kinds of relationships can we represent in a database?
    One to one
    One to many & many to one
    Many to many
    Self preferencing
    2.When do we use “One to One” relationships? Give an example from the real world
    When between two tables each information connecting to one other information. For exapmle, one table for the customers’s shoe size and other table for the brand new shoe’s size. Each customer will connect with only one shoe size.
    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 ‘Foreign Key’ which is used in all kind of relationship. It refers to the matching records between tables.
    4.Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Many to one: more people can love the same exact food
    One to many: one person can love more than one food
    5.Give an example from the real world when would need to use a “Many to Many” relationship.
    We have two groups , in each group people know each other but they can know somebody else from the other group
    6 What does items_orders table do?
    Connect an item and an order in a ‘Many to Many’ relationship
1 Like