SQL Relationships - Reading Assignment

Blockchain Business Course
#162

  1. What kinds of relationships can we represent in a database?
    One to One, Many to Many, One to Many & Many to One, and self referencing

  2. When do we use “One to One” relationships?
    Given the example in the article a customer would have ONLY a single 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?
    It ensure that each customer row is unique. It allows for the customer and address data to be joined together. Note the foreign key customer_id on the address table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    Multiple person attending a single sporting event. In this example a there will be multiple person entities tied to a single event or event_ID

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Products to Purchases . Each purchase can have many products. Purchase can have many product.

  6. What does items_orders table do?
    The items_order table is an order manifest. Per the book. It creates an many to many relationship between items and orders.

1 Like
#163
  1. One to one, one to many/many to one, many to many, and self referencing.
  2. A passport number can only be issued uniquely to one person.
  3. This is done to uniquely identify the customer (a key). It is done so another table can use the key as a column to create a relationship with the two tables. In the case, of connecting customers to addresses the customer_id can be referenced in the addresses table.
  4. A group of people that share the same interest.
  5. Web page sessions in relation to web pages visited.
  6. It establishes the item ids that are related to an order id.
1 Like
#164

  1. One to one, one to many, many to many, many to one, self referencing

  2. A simple address book

  3. Both tables share a common field, address_id. Customer_id is associated with the address_id in each record within the customers table. To find the related address for a customer, you use the common key, address_id

  4. An ecommerce website. A customer may have many orders. But an order with many items can only be related to one customer

  5. An ecommerce site where many items are being sold to many customers.

  6. The items_orders table has the key fields to relate the items and orders tables together

1 Like
#165
  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.
    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?
    It is faster to read when all of the IDs are the same length. Used to associate them
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A mother might have a bunch of children attending school. Each child would have the same mother in the school records.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A social network user who has multiple entries, multiple friends, and multiple entry comments.
  6. What does items_orders table do?
    Item Orders take many table inputs that are id’s and place them in a table which have id’s only
1 Like
#166
  1. 1-1, 1-many and many-1, many-many, self referencing
  2. when we combine one piece of information with another, like linking an email to a user.
  3. Because the customer_id is linked to the address, foreign key
  4. When a customer makes an order for many items
  5. When many customers makes orders for many same items
  6. To make many to many relationships possible
1 Like
#167
  1. One to One Relationships, One to Many and Many to One Relationships, Many to Many Relationships and Self Referencing Relationships
  2. Customer name and Phone number
  3. It is a Foreign key and guarantees a connection between customers and address by referencing other records in another table
  4. Many to one: Multiple children of the same father, One to many: One person having multiple E-Mail addresses
  5. Customers and T-shirts: Multiple customers can buy multiple different types of T-shirts, and multiple T-shirt type can be in multiple customers possession
  6. It creates a “Many to Many” relationship between items and orders.
1 Like
#168
  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.
    When one entry is linked exclusively to another entry. Student number - Student
  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 link a customer with an exclusive number in order to make an exclusive link. It is used as a foreign key.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    One user, many facebook pictures.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Human relationships. Bob has many friends wich have friends that have friends and might also be Bob’s friends.
  6. What does items_orders table do?
    It links two tables and create a many to many relationship
1 Like
#169

  1. One-to-one relationships, one-to-many relationships, many-to-one relationships, many-to-many relationships, self-referencing relationships.

  2. One row in a table is linked to only one row in another table. E.g. a country has only one capital city, and the capital city is linked to only this country.

  3. It is used to abstract the process. Customer id can directly reference to the address id.

  4. One book can have many pages, one specific page can only be in one book.

  5. An author can write several books, and a book can be written by several authors.

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

1 Like
#170

  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.
    A person and his email address. (assuming he only has one 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.

3.1 Why?
It identifies the row of the table. It is the primary key.

3.2 How is it used in order to connect customers to addresses?
A foreign key is defined on that row called address_id. The foreign is used to identify a row of another table.

  1. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    – Many to one: Several family members having the same address.
    – One to many: A person having many friends.

  2. Give an example from the real world when we would need to use a “Many-to-Many” relationship.
    Several family members having mutual friends.

  3. What does items_orders table do?
    It creates a many-to-many relationship by linking a table ID to another table ID.

1 Like
#171
  • What kinds of relationships can we represent in a database?
    One to one, one to many, many to one, many to many and self referencing.
  • When do we use “One to One” relationships? Give an example from the real world.
    When only one item in table a can relate to one item in table b. For example, each user can only have one email to login.
  • 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 gives each customer a number so that query is easier and faster for computer to run. Each id is connected to address so they can be linked and information is not repeated.
  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    If user ordered many products or if product existed in multiple colors.
  • Give an example from the real world when would need to use a “Many to Many” relationship.
    You can have many orders and each can have many items. So if I wanted to see how many orders I got in the last week and what items were ordered, that would be many to many.
  • What does items_orders table do?
    It gives what items were ordered, when, by whom, how many, revenue, maybe even order status, etc
1 Like
#172

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

2
Name, work mobile phone in the address book db.

3
It’s used as a “foreign key”: He created another table with addresses and defined each row with an id, so each table has the related id in the key column.

4
Passport number, many visas numbers

5
Different orders contain many items but a single product is choosen during many orders.

6
It defines all the orders during which an item was picked up. Example of many to many relationship.

1 Like
#173

1 One to one relationships; one to many and many to one relationships; many to many relationships; and self referencing relationships.

2a When there can only be one relationship between other entries. 2b like a social security number for a customer can only be for that one customer.

3 it’s used as a foreign key for another table to help you locate the remaining data that pertains to that entry.

4 like when one customer can have many different items in their cart.

5 when there are many houses owned by multiple subsidiaries that own partial shares in the various properties.

6 to create many to many relationships for items and orders.

1 Like
#174

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

  2. When do we use “One to One” relationships? Give an example from the real world.
    A email address to a user. An email address cannot reference more than one user.

  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 then referenced in the addresses table. This creates a reference between the two tables where the customer_id is refered to as a foreign key in the addresses table.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A user can make many different purchases. So this would be one user and many orders. One order can have many items included in it.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    If a user makes an order of many items from an online store, and many users purchase the different item.

  6. What does items_orders table do?
    This creates connections between the items sold and the orders made. This is an example of a many to many relationship.

1 Like
#175
  1. One to One Relationships, One to Many and Many to One Relationships, Many to Many Relationships, Self Referencing Relationships.
  2. if my database stores businesses phone numbers and each business has only 1 number my database only needs to connect the business with the phone number using Foreign Key, this would be one to one.
  3. the customer id is programmed to connect to the address id which then revealed the customer address.
  4. if the data belongs to a car spares dealer, the database will store car models, series and spare part numbers, each part can fit on many cars.
  5. if the database belongs to ebay, it needs to store buyers, sellers, items, shipping addresses and payment info, each buyer can buy from many sellers, each seller has different payment info, each buyer has different address, many buyers buy same and different items.
  6. it connects the same and multiple items to orders.
1 Like
#176
  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.
    Social number can only be tied to one person.
  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 called foreign key, makeing sure that it cant be duplicated.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When a customer makes many orders at webshop.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many customers, many orders, one item, that many customers order
  6. What does items_orders table do?
    it connect to orders id with the items id
1 Like
#177
  1. Types of relationships:
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships
  1. We use 121 relationships in cases where there is only one direct connection between one row in one table and another row in another table. E.g. UserName and Phone No.
  2. The customer_ID is the Primary Key of the customer table that serves to identify each entry without ambiguity. customer_ID is actually not used to connect the two tables in the example. The primary key of the address table, called address_ID is used in the customer table as a foreign key. In this way address_ID connects the two tables.
  3. One customer, many orders.
  4. Orders and items in orders.
  5. The items_orders table creates the n2n relationships between the orders and the items tables.
1 Like
#178
  1. one to one, one to many, many to many, many to one, self referencing
  2. when an entry belongs to only one related entry elsewhere. social security number has a one to one relationship to a person
  3. It sets up a unique id for the customer. I don’t believe it is. The address_id is used as a foreign key to connect the unique customer to a unique address.
  4. A forum thread’s relation to it’s comments. One forum thread has many comments
  5. A song playlist. A song can belong to many playlists, while a playlist can contain many songs
  6. Creates the many to many relationship between an order and an item
1 Like
#179
  1. one to one, one to many - many to one, many to many
  2. Customer and customer id, usually a customer has just certain id, while one id just serve one customer for identification purposes
  3. adding one certain unique number makes it easier to avoid confusion since customers can have the same name
  4. one customer can do many order, many items can be in one order
  5. many students can register for many classes and in many classes are many students
  6. create many to many relationship
1 Like
#180

one to one
one to many
many to one
many to many

if an entry can only have one corresponding property. E.g. the passport number of one person.

It is there to make the customer unique and to allow referencing from other tables to him. Referencing by name is not good, because there could be more than one customer with the same name.
In the address table the only connection between the person and the address is the customer_id.

many items in one order

A facebook group can include many members and each member could be part of many groups

It is there to create the many-to-many relation

1 Like
#181
  1. one to one, one to many, many to one, many to many, self referencing.
  2. assuming that a customer can have ONLY ONE residence, then a one to one relationship can be customer/address relationship
  3. the customer_id is the primary key to the customer table and it uniquely identifies a customer record from all other customers.
    address_id uniquely identifies an address record in the address table, and when added as a foreign key in the customer table,
    the customer record can now reference the ddress associated with that customer to a uniques address record in the address table.
  4. assuming that a customer can have MORE THAN ONE residence, then a one to many relationship can be customer/address; FL residence and NJ residense
  5. a customer order can consist of many items, and an item can belong to many orders
  6. it creates a table that contains the primary key from the orders table with the primary key from the items table
1 Like