Question1:
1- One to One relationships;
One to many and many to one Relationships,
Many to many Relationships;
Self Referencing Relationships.
2- In one to one relationship for example each address belongs to only one costumer. A table that specifies my name and address.
3- In order establish relationships each person has to have a unique ID in that particular table so it can be connected to other data in other tables.
4- Many to one relationships - When I make various orders or various items on the amazon website.
One to many relationships - When I order a few items from amazon, all those items will be associated to one order in my account.
5- Many to many - When various people make multiple orders of various items.
6- Items_orders create many to many relationships.
1. What kinds of relationships can we represent in a database?
The following relationships can be represented 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.
A “One to One” relationships is used when there is no more than one relationship to an entry. For example a telephone directory.
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 column named customer_id in a customer table is called foreign key. Foreign keys represents a field which points to the primary key of another table. The goal of the foreign key is to ensure referential integrity of the data.
4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
A “Many to One/One to Many” relationship can be used for employer/employees. An employer has many employees but each employees has only one employer.
5. Give an example from the real world when would need to use a “Many to Many” relationship.
A “Many to Many” relationship can be used for students/classes. Many students follow many classes.
6. What does items_orders table do?
An items_orders table establishes a many to many relationship between items and orders.
Hi,
1, One on One, Many to One, One to Many, Many to Many
2, Anytime we don’t want duplicates. If you want to have a bank account you will need to provide very specific data which can distinguish you from others… ID card number, health insurance number, phone number, mail address, etc.
3, Because the customer name can be duplicated. Customer _ID is a nonduplicate number that can be used as a reference to other tables to “connect” data together. customer_ID connects the customer with relevant address ( which has customer_id attached to it)
4, I can go shopping and have many items on the shopping list. There are more shopping lists connected to me.
5, Girls/boys can have fun with any number of different girls/boys, and these boys/girls can have fun with any number of different boys/girls.
6, Sets connections/relation
There are one to one, one to many, many to many and self referencing relationships.
We use one to one relationships when one thing/attribute/object can only belongs to one thing/attribute/object. For example we live in China and they say the families are only allowed to have one child: A child can only have one genetic father which means that this is a one to one relationship because the child can only have one father and the father can also have only one child.
–> @ivan I think the example (one to one) in the reading assignment is wrong, because they say one adress belongs to one customer. But think of a large house where different families live, then one adress belongs to different customers. So the relationship should be one to many or I am wrong?
3.The authors used it, because he wants to make the database redundancy free and connect the data of the customer to a unique identifying number. The identifying number also make the updating of the database easier. Just imagine, the customer moved to a new address, then the database only needs to update the entry in the customers table. Without this connection every entry in the table orders has to be updated. When someone ordered 1000 things you have to update 1000 entries instead of just one. With this connection the database also ensures that one customer can only have one adress.
One to Many: A child belongs to one father but a father can have many children.
Many to many: A company can be owned by many owners (e. g. via shares) and the owners could also own further companies. Then you have a many to many relationship
It has the purpose to enable a many to many relationship. It ensures that orders can have many items but an item could also belong to many orders.
1 to 1 relationships
1 to many and many to 1 relationships
many to many relationships
Self referencing relationships
2 a When a unique data set can only belong to one person it is called one to one.
2 b For example a driving licence number linked to a name would be “one to one”
It provides an easy reference to that customers information on other pages in the database. It saves space and time re-entering that customers details on other pages. Each time customer_id appears on any subsequent pages, it refers back to the customers details sheet.
An example would be an e-commerce website.
A social networking site like Facebook would be a many to many example as many different users and groups would need to be cross-referenced.
It creates a unique reference to a specific order by a customer that can be easily referenced by other database pages, a many to many relationship.
4
What kinds of relationships can we represent in a database?
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.
A One to One relationship would be a relationship between users. An example would be a sole proprietor getting an EIN number assigned to them based on their Social Security number to use on their tax forms.
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?
The customer_id is a foreign key that represents data that can be used between multiple tables. How is it used in order to connect customers to addresses? It is used in other tables since it is a relationship.
Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
Amazon would use a Many to One relationship to show how many items I order each week.
Give an example from the real world when would need to use a “Many to Many” relationship.
Amazon would also use Many to Many relationships for the multiple amounts of the same item that I order and re-order.
What does items_orders table do?
The items_orders table creates Many to Many relationships between items and orders.
One to One, One to many or Many to one, Many to many, and Self-referencing relationships.
2.
If there is an user, and for example only an address attached to it.
3.
In MySQL, there are two ways to create foreign keys columns:
Defining the Foreign key explicitly, or without explicit declaration.
4.
For example if there is a database which has a user, and this user has different values attached to it like address, birthdate, ID number, phone number, etc.
5.
In a factory for example employees can work on different projects, and a project can have many employees working on it.
6.
It creates a “Many to many” relationship between the items and the orders.
2.When there is a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this relationship is “One to One”. Between people and their drivers licence.
3.Its a Foreign Key that is used. It is used for database relationships. This means that for every row in a Child table that has a foreign key, there will be a matching row in the Parent table.
4.When someone order something fra a e-commerce website. The customer can make multiple orders.
5.The many-to-many relationship is usually a mirror of the real-life relationship between the objects the two tables represent. example, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors.
table stores the line items of a sales order. Each line item belongs to a sales order specified by the order_id column.• One-to-one (1:1)
• One to many and Many to one (1:m - m:1)
• Many to many (m:m)
• Self Referencing
For example Customer name - Address. This couple is unique.
To indentify the correspondent address_id in the one-to-one relationship
Many customers can make many orders; many orders can have many elements; many elements may have description in several languages
To establish a relationship “Many to many” between items and orders
We can represent on a database:
‘One to One Relationships’
‘One to Many & Many to One Relationships’
‘Many to Many Relationships’
and
‘Self Referencing Relationships’
We use One to One Relationships when a piece of data relates to another. Neither of these peices of data would be related to any other pieces of data of the same kind. An example of this is in the UK each individual and the relationship they have to their National insurance number.
3)The customer_id field was used as a foreign key to link the customer with another table which may represent the orders or items that customer has bought. For example when a customer makes an order the customer ID can be used to link the customer ID to the customer table and then use the customers data from the address column to dispatch the order. It creates a One to One relationship between the customer and their address.
An example of this would be Youtube keeping a database of Ivans youtube content. They would use a Many to One/One to Many relationship to to track all of the Youtube content Ivan has created and link those to his Account on their database. They’d have a hard time doing a One to One database every day at 8am central european time and it would be inefficient to then connect all of these together at a later date.
An example of using a many to many relationship would be if we had a list of buyers and sellers on a marketplace. Buyer A would purchase from Seller 1, but also Buyer B might purchase from Seller 1, equally Buyer A might also purchase from Seller 2. These relationships would need to be represented with a Many to Many relationship on a database in order to follow buyer to seller activity.
An Items_orders table represents and tracks the relationships between the items sold ie ‘item 1, item 2 and item 3’ and which orders those items appear in ie ‘order x, order y *3 and order q’. This could be important as these relationships may show that certain items are bought together which would allow for a business to capitalise on this by promoting the sale of the 2nd item whenever a customer goes to checkout on purchasing the 1st item.
We have : One to one, One to Many, Many to One, Many to Many, and Self refering Relationships
We use one to one when there is only one relationship between a data entry on a table.
The Foreign key defined in the second table and refers to the primary key in the first table thus lining the two. In this case, Customer_id on the Customers table is the primary key, and this key refers to the foreign key Customer_id on the Customer Details table.
Using the previous example. Orders on a website contain many items, and these items can also be on multiple orders, for different customers. each customer is only allow to make one order at a time, but items can be in multiple different orders at the same time.
This table create Many to Many relationships between Items and Orders.
SQL Relationships – Reading Assignment
1. What kinds of relationships can we represent in a database?
2 a. When do we use “One to One” relationships?
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”.
b. Give an example from the real world.
Individual people and their personal cell phone number.
3. In the article in the “One to One” section, the author has a column called customer_id in the customers table.
a. 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.
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. 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 we would need to use a “Many to One/One to Many” relationship.
This is the most commonly used type of relationship. Consider an e-commerce website, with the following:
In these cases’ we would need to create “One to Many” relationships.
5. Give an example from the real world when would we 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.
An example would be: Ordering a pizza with different toppings
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.
Onhockey puck in the database of a sports shop. The puck is related to the sport of ice hockey and this spowhereas cleats could be used in different field sports
One to one, One to many, Many to many and self-referencing
When we want an value from one table to correspond to only one value in another table. It could be a name and an address.
Since the customer id is a way to identify the customer in the first table, the author linked the customer id as a foreign key in the address table
This would be needed if customers can make many orders.
Here customers can make many orders and these orders can have many items.
It shows the relationship between the orders and the items within those orders.
One-one, many-one, one-many, many-many, self-referencing
Like i have a creditcard that i own and the creditcard is related to me. So i have one on one relationshipt with the creditcard and the creditcard has one on one relationship with me.
The customer_id column identifies the customer who has the relationship with the address_i
I have lots of keys to diffren places, its lots of places and they all belongs to me.
Schools have lots of students ho goes to lots of classes, and one student goes to lots of classes.
It allows for a many to many relationship to be established between items and orders