SQL Relationships - Reading Assignment

Blockchain Business Course
#282
    • One to One Relationships, * One to Many and Many to One Relationships, * Many to Many Relationships, * Self Referencing Relationships
  2. If each entry can belong to only one other entry. One address can only belong to another entry called customer for example
  3. It is because of “Foreign Key.” The data is used as a reference for two different entries
  4. One to many: The customer ID can be associated with many sales orders. Many to one works the other way.
  5. A many to many relationship occurs when multiple records are associated with multiple records in another table. For example, a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.
  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
#283
  1. Relations possible: 1-to-1, 1-to-many, many-to-1, many-to-many.
  2. A medical test (blood sample) and a specific person is 1-to-1.
  3. The customer_id field is a key-field appearing in the customer & address tables.
  4. Mathteacher_of is a 1-to-many and Mathstudent_of is a many-to-1
  5. Roads and travellers are many-to-many
  6. It is a way of grouping orders and items in a many-to-many way as multiple orders may contain the same item*, and any order may be of different items.

*the items must be interchangeable.

3 Likes
#284
  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.

If data in one table is related to, but does not ‘belong’ to the entity described by the other, then that’s a candidate to keep it separate.

Example: Customer can have an optional 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?

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
It will be used to connect the customer and address data.

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

Customers can have many orders

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

An product can be part of multiple orders. And an order can have multiple products.

  1. What does items_orders table do?

The table is used to build the many-to-may relationship (intermediate table).

2 Likes
#285
    • One to One Relationships* One to Many and Many to One Relationships* Many to Many Relationships* Self Referencing Relationships
  2. When only one entry can belong to only one customers. Individual people and their credit card number.
  3. Aka a Foreign Key. Used to cross reference data between data base tables. You choose to do this if it from cost/Efficiency perspective makes sense to have addresses in separate tables.
  4. For example, each order can contain multiple items. And each item can also be in multiple orders. Ex. e-commerce
  5. Drive thru at McDonalds
  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.
1 Like
#286

  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 list of Customers for a company

  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?

    So he can later reference the customer on another table if one is needed with this information. He creates a field named “address_id” in the Customers Table,that refers to the matching record in the Address Table. This is called a “Foreign Key” and it is used for all kinds of database relationships.

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

    • E-Commerce

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

    • Creating orders on a e-commerce site

  6. What does items_orders table do?

    Link an order id to an item id when an order is placed.

1 Like
#287
  1. Relationships:
  • one to one
  • one to many/ many to one
  • many to many
  • self referencing
  1. When an entry can only have single relationship with another entry.

Example: one person can only have one passport number of a particular nationality

3.It refers to the matching record in the customer table.

  1. Example: if multiple people are register to vote at one address.

  2. Example: Students and classes. One student goes to more than one class; also each class is attended by multiple students.

  3. It creates a ‘Many to Many’ relationship between the items and the orders.

1 Like
#288

  1. One to One Relationships
    One to Many and Many to One Relationships
    Many to Many Relationships
    Self-Referencing Relationships

  2. The key creates a value thats specific like Name and address

  3. A relationship was created that customers have addresses

  4. Job can have one department but have many programmers in that department and many programmers working on different parts of a project

5.Ordering from a fast food menu

6.It creates many to many relationships between an item and orders

1 Like
#289
  • What kinds of relationships can we represent in a database?
  1. One to One
    One to Many
    Many to Many
    Self-Referencing
  • When do we use “One to One” relationships? Give an example from the real world.
  1. A little out dated, but phone books serve as a physical manual database with a one to one relationship of person (potential customer) to 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?
    3.Customers are represented with a space saving number(id) that can appear on other tables to be referenced back to that customer. It’s a way to compact data.

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

  1. 10 people buy a new xbox one online, multiple payments come through to the retailer. The retailer must then process invoices and shipments for many customers. Could also be one customer buying 25 different items from 6 retailers.
  • Give an example from the real world when would need to use a “Many to Many” relationship.
  1. Retail warehouses purchase from hundreds/thousands of manufacturers and deliver those goods to or through dozens to hundreds of their physical/online retail stores.
  • What does items_orders table do?
  1. Creates a relationship between items and orders which have a relation ship with customers and addresses.
1 Like
#290

1 One to One Relationships; One to Many and Many to One Relationships; Many to Many Relationships; Self Referencing Relationships

2 An unique attribute in a table matches an unique attribute in another table. Example: Company name and VAT number

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.

4 Teachers to Class; Class to Students

5 An order can have many items and an item can belong to many orders

6 Put in relation itemsID with ordersID

1 Like
#291

  1. Relationships One to One, One to Many and Many to Many.

  2. One to One refers to data that only can link two items, eg: Data1:Person, Data2:His/Her Address
    The address belongs to one person, it cannot be used by another entry in the database.

  3. “Customer_id” helps to link the row data of that customer to another information about this user in a different database with the same “id linked”. It makes the “union”.

  4. One to Many (o viceversa) is when one item is linked to many other items. For example: In Facebook your account is ONE and it is befriend with MANY other users, your real friends.

  5. “Many to Many” is when items of a database can be linked with multiple items or another database and these to more items.
    eg: In a Festival there are STAGES where BANDS can play at different hours and in each stage and play SONGS.

  6. Links an order number (with its data, amount, etc) to the items purchased (title and description).

1 Like
#292
  1. Databases may be used for administering one-to-one, one-to-many, or many-to-many relationships
  2. One-to-one relationships used if an entry is attributable to a unique thing such as where personal details of a customer are attributable to his unique customer ID
  3. The customer ID field is used as a point of reference for the retrieval of the full customer details
  4. An example of an one-to-many relationship may be a customer having multiple orders, while a many-to-one relationship may be items having descriptions in many languages
  5. A many-to-many relationship may be created if orders containing multiple items or an item forming part of multiple orders
  6. The item orders table particularises a many-to-many relationship
1 Like
#293

  1. We can represent these relationships in a DB:
    • One to One Relationships
    • One to Many and Many to One Relationships
    • Many to Many Relationships
    • Self-Referencing Relationships

  2. We use “One to One” relationships when we need to relate a unique row on one table to another unique row on a different table and the other way around, too.
    An example could be: The relationship between the records of one driver and his driving license and vice versa. A unique driver’s row on the table ‘drivers’ links (two ways) to a unique driving license’s row on the table ‘driving license’.

  3. In the article’s example, the ‘customer_id’ in the customers table is known as a foreign key. Because it interconnects the two tables in a one-to-one relationship, in this case. However, a foreign key it is used for all kinds of database relationships. Foreign keys are especially useful to make the tables more space efficient (less and uniform record length); thus, making the DB more time efficient to search.

  4. A “Many to One/One to Many” relationship could be:
    Several customers recorded on the table ‘customers’ have rented, in different periods of time, the same house recorded on the table ‘houses’. This house’s record points back to the customers that rented it.

  5. A " Many to Many " relationship could be:
    A rental car customer, recorded on the table ‘customers’, can rent several cars, recorded on the table ‘cars’. Each car’s record points back to the many customers that have rented it.

  6. The Items_Orders table serves the purpose of creating a “Many to Many” relationship between the items and the orders.

1 Like
#294

Always sharing amazing answers miss! glad to know that you are truly understanding the lessons! :slight_smile:

If you have any more questions, please let us know so we can help you! :slight_smile:

Carlos Z.

1 Like
#295
  1. One to one, one to many, many to many, self referencing
  2. When the attribute from one table belong to only one record from another table. Ex: Social Security Numbers to people -> each person only has one SSN, and each SSN only belongs to one person.
  3. This is a unique identifier to identify the customers. That way in other tables, you can simply say “this order belongs to customer 1” or “this address is for customer 1”. These primary keys (in the customers table) work as foreign keys in other tables to connect other data with the customer without data redundancy in other tables(e.g., avoid having customer names in another table again thus if Mary gets married and changes her last name, you only have to update one table, customer table).
    4 Employee ------> paychecks. Each employee will have many paychecks (over the course of time), but each unique paycheck is only associated to one employee.
  4. Ivan’s Academy Students —> courses enrolled in. One student can be enrolled in many courses, and each course can have many enrolled students.
  5. Creates a many to many relationship between the items and the orders -> a way to link each order to each items ordered in each order.
1 Like
#296

1.-One to one / one to many / many to one / many to many / self-referencing relation.

2.-National identification number. A person can only have one ID.

3.- We need a key for a customer since more than one person can have same first name. Also, by having customer_id enable to refer additional data of same customer in other tables, such as address, because address_id was included in customer table.

4.- A person is a friend from other persons and at same a person have many friends.

5.- When I use public service, such a hospital, school, transportation, and at the same time, those service are offered to many people.

6.- Store information about each order and enable to create different relations, due to the way data was kept.

1 Like
#298
  1. Types of relationships in databases:
  • One to One
  • One to Many and Many to One
  • Many to Many
  • Self Referencing
  1. One to one relationships are used when a single data point is related to another single data point. An example of this would be a User and their Age.
  2. The customer id represents a foreign key in the customer table, this is used to connect different data entries from different tables. The foreign key of the customer id connects the address id to the customer id.
  3. An example on a social media site would be the relationship between one user and many other users that have the same group in common.
  4. A user may order many items and those many items may have many different options and features.
  5. It is used to connect many to many relationships such as order id to item id tables.
1 Like
#299
  1. The following relationships can be represented in a database:
  • one to many and many to one;
  • one to one;
  • many to many;
  • self referencing.

  1. One to one relationships are used when only one record from one dataset relates to only one record from another dataset. E.g. a unique ID to a unique customer name.

  2. customer_id in Customers table is the primary key of unique autonumbers. It may be used as a foreign key in other tables in reference to customer_id stored in Customers table.

  3. An example from the real world with many to one/one to many relationship: a purchase order containing a set of products (one to many); many products related to one purchase order (many to one).

  4. Many to many relationship: a purchase order may contain multiple items while each of these items can be on multiple purchase orders.

  5. Items_orders table creates a many to many relationship between the items and the orders.

1 Like
#300
  • What kinds of relationships can we represent in a database?
    A. One to One, One to Many, Many to Many and Self Referencing Relationship.
  • When do we use “One to One” relationships? Give an example from the real world.
    A. When the relationship is unique is when One to One relationship is use for example a customer table with address, as the address is unique to only 1 customer.
  • 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?
    A. Customer_id column is a unique column that has one to one relationship with address and address_id is used as a Foreign Key for database relationship between 2 tables.
  • Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A. A Customer can have one or multiple Orders but an Order can only have one customer.
  • Give an example from the real world when would need to use a “Many to Many” relationship.
    A. In E-commerce, ther can be multiple orders with multiple items. An Order can have multiple items and an item can be in multiple orders.
  • What does items_orders table do?
    A. Item Orders table is a Many to Many relationship table which records Order_id and
1 Like
#301
  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 we want to retrieve the Social Security Number of a customer.
  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?
    Each customer has it’s own ID that is matched to the addresses id of the addressed table.
  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When using an e-commerce website for ordering products.
  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    In order to establish which items have been ordered by which customers and how many items were ordered.
  5. What does items_orders table do?
    It has only one purpose and that is to create Many to Many relationships between the items and the orders.
1 Like
#302
  1. Relationships:
  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

  1. When you have a unique user with a unique input for example a person and their social security number.

  2. The field named “address_id” in the Customers table, that refers to the matching record in the Address table. This is called a “Foreign Key” and it is used to ensure referential integrity of the data.

  3. 1 customer that has a few orders and vice versa.

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

  5. To create a “Many to Many” relationship between the items and the orders.

1 Like