SQL Relationships - Reading Assignment

[travis_bazile]

1. We can represent attachments to customer information such as address, order,amount to store on a database.
2. If i’m ordering off of amazon. when i sign up to there website application they store my name and address for future reference to know. One who is making the order and two where will they send the order once i make my purchase. They also keep track of the amount and my history as well on the amazon DB.
3. This Customer ID is also a foreign key within the SQL DB the name and item are kept seperate from customer information. When you query SQL to link address with name it creates a relational relationship to update that entry.
4. a many to one/one to many relationship should be used and is the most commonly used code side customers will make more than one order. this can assist with tracking, how many orders the customer has made and updates the entry when customer makes purchase.
5. a many to many relations should be used with a customer makes a purchase of the an item you capture the order id and the item id.
6. Items_Orders table has only one purpose, and that is to create a “Many to Many” relationship between the items and the orders.

[homiejohn]

1. One to one, one to many/many to one, many to many, self referencing
2. A one to one relationship can be used when you are relating one customer to one address.
3. Using a customer_id can be helpful by simplifying names of different lengths to 3 digits representations like in this example. These _ids can now have relationships to specific addresses and can be referenced as foreign keys in other tables.
4. When you have one customer_id which has a relationship to many orders or many products.
5. When you need to reference many order_ids to many Item_ids
6. A items_orders table would show the relationships between orders and the items contained in each order.

[CryptoDev]

1. There are several some being one to one ; many to one ; many to many and Self-referencing relationships.

2. When we only have a limited unique data set a one to one relationship would be used like in a school with a student and there student id card.

3.The customer_id is the primary key of the Table and is used to link the data to other tables with a foreign key creating a data set that can be more easily queried.

4. A one to many relationship would be used in a bank where one user can open many accounts.

5.A many to many relationship would be used in eCommerce where a user could have an account that has multiple orders each containing multiple items.

6.The items_orders table links the orders and items tables together.

[Glenn_CostaRica]

Glenn_CostaRica

1. What kinds of relationships can we represent in a database?
In a database, relationships can be of the following kinds: 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.
A one to one relation is used to link two pieces of data in a fixed way, where one piece of information characterizes the other, or is associated to the other in an exclusive way. In the One to One type, you can have only two items referring to each other. Examples are Person & Id, Car % NumberPlate, House & Address or Object & Weight.

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 example, one ID refers to one customer only in the customer table, and, in the same way, refers to an address in another table. By having one table where each customer is tied to one and only one particular ID, subsequently, the ID can be used to tie the person with other information indirectly, like to addresses. This method makes the whole storing system efficient and easy to update.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
When a single item needs to be associated to more than one element of an attribute, the Many to One/One to Many is ideal. For example, in the case we need to store information like Student & RegisteredCourses or Person & FavoriteSongs.

5. Give an example from the real world when would need to use a “Many to Many” relationship.
In Many to Many type of association, more than one items is tied to more than one attribute; for example ShareHolders & Shares or FormsOfPayment & Bills.

6. What does items_orders table do?
This is a type of table specialized in tracking clients that make orders and the products or services each client(s) order(s).

[CSquared]

1. a.One to One
   b. One To Man and Many To One
   c.Many To Many
   d, Self Referencing
2. This is used when a piece of information is distinctly unique to another in their relationship e.g. a person’s name details and their fingerprint
3. This is because their is only one Customer ID per customer and it uniquely identifies them
   and as a result it can be used as an effect Foreign key linking the information in the Customer table with any other table to in which data is contained relating to that customer
4. One To Many : A Person can speak multiple languages
   Many To One : Many Students can attend the same course
   Many To Many : Multiple customers can order similar multiple products from Amazon
5. To create a “Many to Many” relationship between the items and the orders.

[Maximus]

@ivan

• What kinds of relationships can we represent in a database?
• When do we use “One to One” relationships? Give an example from the real world.
• 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?
• Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
• Give an example from the real world when would need to use a “Many to Many” relationship.
• What does items_orders table do?

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

2. Name and Surname to identification number in your country.

3. Each entry in a table has a unique ID and therefore one can subdivide and create more tables. Due to the data being divided into more detailed tables it is easy to create relationships data and a database that is organised in such a way that it processes fast search results.

4. Ivan on Tech,where you have thousands of students in One Block Chain Academy.

5. Amazon …where many clients order several products and can have several purchases.

6. It creates an relationship of Many to Many between orders(ID) and items(ID).

[RailGame]

• 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.
  We use one to one when there is a unique relationship of data. eg Name and Passport 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?
  This is a use case for foreign keys. Different tables are used to optimize the efficiency of the database navigation by breaking up the data and connecting them with a join function.

• Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  A Shipping Dock might have many ships docking. Each Ship might dock at many different shipping docks.

• Give an example from the real world when would need to use a “Many to Many” relationship.
  A many to many relationship could represent a product that many different customers would buy, And each customer in turn could buy many different products.

• What does items_orders table do?
  It creates or mediates the “Many to Many” relationship between entities.

[Bunbo_Hue]

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

One to one, one to many, many to one, many to many.

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

Customer > Address
Customer > Language
Customer > 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 value makes it easy to connect a customer to their address, while having separate tables. So that way the address table doesn’t have to contain all customer info, it can just identify the customer based on their ID number and keep the tables separate.

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

One to many:
Customer > Order
Customer > Item purchased

Many to one:
College Class A, B, C, D > Professor A

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

Orders > Items. Each order could contain multiple items, and each item could appear on multiple different orders.

Or Groups > Members. Each group can contain multiple members, and each member can be a member of multiple groups.

6. What does items_orders table do?

The items_orders table connects the items with individual orders by creating a “many to many” relationship between them. On items_orders we can see which items were included on a particular order (and technically vice versa).

[Staniis]

1. One to One Relationships
   One to Many & Many to One Relationships
   Many to Many Relationships
   Self Referencing relationships

2. One to One relationship is used when there is more than one table, And the different data is on both tables for that one same person. For example, Customer is on one table and Phone Number is on another table, it’ll have a One to One Relationship.

3. customer id is there to give it a foreign key for use and communication with other tables for reference.

4. Dominos Pizza for example, Customer can call and order a Pizza and 2 Drinks and 1 Ice Cream. It’s one Customer Making 1 order (But can make more) and inside the order he orders a few different items and that order is only His.

5. Ok for some reason I can’t wrap my head around it…

6. It’s purpose is to create “Many to Many” Relationship between orders

[thecil]

@Staniis

Ok for some reason I can’t wrap my head around it…

Here, take a look to this more detailed example, just to help you clear your head!

My answer to SQL Relationships

Hope this gives you a clear view of the subject, keep learning!

If you have any doubt, please let us know so we can help you!

Carlos Z.

[Von]

1. Different kinds: one-to-one / one-to-many / many-to-many …
2. When you have one unique set of information for a given data, ex: Name + Phone Number relationship
3. That means that he has a table with ID + Customer Name for instance, so that he does not need to bring back the full name each time and can use the ID only instead
4. ex: persons // shoes they wear, you can have many perso who wear the same kind of shoes
5. ex: persons // an IOS application, many persons can have app A + B + C, and some others might have App A + D + F …
6. It shows the relationship between orders and items. So we can see in how many different order were Item A for instance. And not just how many times Item A was sold. It gives extra information.

[Eva_Santiago]

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 and Self-Referencing Relationships
When do we use “One to One” relationships? Give an example from the real world. When one field can only be connected to another - for example . one client has one unique email 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? The customer id is called a foreign key and it relates to the customer table where the customer id is the primary key and connects the records related to that customer.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship. One customer may place many orders and many itmes may be ordered by that one customer.
Give an example from the real world when would need to use a “Many to Many” relationship.
Many people have placed an order of many items, and many items are being ordered by diffrent people.
What does items_orders table do?
The items_orders table creates a many to many relationship between orders and items.

[John_Okoye]

1:1, 1 to many, many to 1, many to many and self referencing

the two entries are specific to one possible occurence. my name and my passport number

to query the primary key

my name related to my contacts in a given country

I’ve never done one of those maps where you place the pin on the country that you have traveled to. but no worries, since I have had my cell phone with location active, google has done that for me! it can pinpoint every country I have been to…‘the many’ are the countries and ‘the one’ is me.

My primary nationality is Canadian. But I am also British and Nigerian. I am sure that I am not the only person in the world with these three nationalities, there are many. Now how many of these Canadian/British/Nigerian passport holders have been to Hawaii, Switzerland, Egypt and Singapore? That is a many to many query that a government agency might be able to perform.

Order Tables are a MASTER table that are able to query many-many subset tables. If you are familiar with simply accounting, your balance sheet and income statements are order tables in which you can ‘drill down’ many layers to get to the details eventually getting to the general ledger line entry…I never fully understood this but getting clearer now,

[Nickpuna1]

1. 'One to One", “One to Many and Many to One”
   “Many to Many”
   “self Referencing”

2. Customer Name and address example Unique account/ id number to customer

3. To make a connection between the “Customers Table” and “address Table”

4. Students and classes . One student has many classes that they enrolled on. One class has many students that are enrolled to it.

5. Online marketplace may have multiple customers all containing the same or different items.

6. It allows for “Many to Many” relationship to be established between items and orders.

[Kapo]

SQL Relationships - Homework - Reading Assignment

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.

• One record in a Row on one table matches another record in a Row on another table and vice versa.

• Example – In a school database, each student has only one student ID, and each student id is assigned to only 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?

• Customer id is also known as the foreign key;

• A foreign Key is the linking pin between to tables.

Given a value from a row in one table you can access another table to find the right row with related data. So, one table gives the key for the other, ‘foreign’ table. This represents a one to one relationship.

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

Most relations between tables are One-to-Many.

Example:

• One area can be the habitat of many students registered at the Ivan on Tech Academy.

• One student can have many course subscriptions.

• One Course can have many Students.

A Many to One relation is the same as One-to-Many, but from a different viewpoint.

Example:

• Many Students live in one area.

• Many course subscriptions can be of one and the same Student.

• Many subscriptions are for one and the same Student.

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 is one between students and courses at the Ivan on Tech Academy. A student can register for Many courses, and a course can include Many students. This includes a Students table, which contains a record for each student, and a Course table, which contains a record for each course.

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

Example in the e-commerce Enterprises.

• Customers
Contains a list of customers. One row per Customer. Would contain all the customers information - their contact details, etc…

• Orders
Contains a list of orders. One row per order. Each order is placed by a customer and has a Customer_ID - which can be used to link back to the customer record. Might also store the delivery address, if different from the customers address from their record - or store addresses in separate tables.

• Items_Order
Contains a list of order items. One row for each item on an order - so each Order can generate multiple rows in this table. Each item ordered is a product from your inventory, so each row has a product_id, which links to the products table.

• Products
Contains a list of products. One row per product. Similar to the customers table, but for products - contains all the product details.

[blue]

1. 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
2. When do we use “One to One” relationships? Give an example from the real world.
   when compiling your customer database, you can label each customer with a unique id/customer number whereas that number can then be used to reference the customer with all their pertinent information
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?
   where every customer id is unique, the info/details associated with that customer also follows their unique customer id
4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
   my customer #1 orders bread May1 (order #5) and then again May15 (order #10)
   my customer #2 orders bread only on May1 (order #7)
   one customer ordered twice, same customer number but unique order numbers
5. Give an example from the real world when would need to use a “Many to Many” relationship.
   when a single customer order more than one item, each item has it’s own ID, both items will be on the same order ID and with the same customer ID
6. What does items_orders table do?
   creates a relationship between items and orders

[thecil]

Excellent answer sir! really well documented! keep it like that please!

Carlos Z.

[arno_nabuurs]

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

2. When to database entries belong only to each other, like i.e. customer name & bank account

3. It is used for matching two records, in this case name and address. It is called a foreign key and is added in an extra tabel refering to the main table, i.e. “customer name” and link it with another table like i.e. address

4. For e-commerce websites this is the most used form.

5. Handling of orders. One item can be in many orders, and many orders can contain the same item

6. This creates the many to many relationship described in 5

[coinarura]

1. What kinds of relationships can we represent in a database?
   There are several types of database relationships:

• 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.
   In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a school database, each student has only one student ID, and each student ID is assigned to only 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?
   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.
   It is a field 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.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
   In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders.
   Because relationships work both ways, there are also many-to-one relationships.

5. Give an example from the real world when would need to use a “Many to Many” relationship.
   A typical example of a many-to many relationship is one between students and classes. A student can register for many classes, and a class can include many students.

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

[Drew_Mackey]

1. Databases represent many different relationships. Some common representations are:

• One to One Relationships
• One to Many and Many to One Relationships
• Many to Many Relationships
• Self Referencing Relationships

2. A “One to One” relationship is a relationship between two tables in a database where one record in one table is related to only one record in another table. This type of relationship is unusual, but there are some useful times to use the 1to1:

• If there are many optional attributes that relate to only some records. A table with a lot of columns, with a few being optional and only applying to some records, could be better suited by separating those few optional columns creating a second table, and relating the rows to the first table.

• If your data is being loaded from different places. Using data available in a different schema or different database would make getting all of the data on only one table very difficult. Instead, using one table for the data you control and another coming from the other databases or schema.

3. The customer_id column relates the Customers table and the Addresses table by showing only one customer (customer_id) having only one address (address_id) in the system.

4. An example of a “Many to One/One to Many” relationship is an e-commerce website, with the following:

• Customers can make many orders.
• Orders can contain many items.
• Items can have descriptions in many languages.

5. An example of a “Many to Many” relationship is when many orders can contain many different items

6. The items_orders table creates a “Many to Many” relationship between order_id and item_id.