SQL Relationships - Reading Assignment

[Itzamna]

1. one to one, one to many or many to one, and many to many. 2. When one entry can only belong to a certain entry, one on one which is less common. Peoples names and social s ecurity numbers. 3. It is the foreign key, which i used for database relationships. The foreign key enforces referential integrity between data in two tables. One table : “customers” has the primary key “customer_id”, while the other table “customer details” which uses a foriegn key to reference “customer_id” in order to uniquely identifiy the relationship between the two tables preventing duplicate entries. 4. For example, amazon. Customers can make many orders, orders can contain many items and items can have descriptions in may languages. This is a one to many relationship. 5. A student would take 4 courses and each course would have many students. 6. Connects orders_id with the Items_id creating a many to many relationship betweent the two tables.

[Jugging_Crypto]

@ivan

• 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 a specific record can belong to only one individual in the data set. The article uses the example of name and address. Another example could be name and phone number/email.

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

Every customer has a customer_id because this id is what links the customers to their corresponding address and information. By using a customer_id you are able to generate all the information that is linked to them in order to more efficiently decipher the data and keep its integrity. The address_id is known as a foreign key, many foreign keys can be generated in order to link the customer_id to more relational data and ensure integrity.

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

This is the most common type of relationship. The article references the example of e-commerce. Every customer can have many orders but orders can belong to only one customer.

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

Many to many relationships are used when multiple instances are needed on both sides of the relationship. For example, many orders can be connected to many items.

• What does items_orders table do?

The only purpose of the items_orders is to create the many to many relationship. This is where you would use the order_id to link the item_id foreign keys. By linking the orders and the items tables you now have a many to many relationship because there are many orders containing many of these various items.

[PapatoniC]

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

   • One to One
   • Many 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.

   • When there is a relationship between one item of a table (entity) and another item from another table(another entity).
     -Example: Driver with car
     Luis owns only 1 car

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?
   That is a called Foreign Key. This customer_id foreign key is used to distinguish one customer from others within the table and to connect to addresses_id foreign key so that both items are connected with a database expression:
   Explicit declaration:
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   Without explicit declaration:
   customer_id INT,`

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
   When a driver can own many cars , this would be a single driver relationship to many items on the car table

5. Give an example from the real world when would need to use a “Many to Many” relationship.
   User and football stadiums. A user could have visited several stadiums , while one stadium could have been visited by many users.

6. What does items_orders table do?
   It is a table with the relation records between two tables , items and orders

[Babsi]

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

2. e.g. user and user phone number - the phonenumber can only belong to one user
3. customer id (foreign key) is needed to store the connection btw the two tables
4. many to one/one to many -> online shop: one customer orders several items in one order
5. many to many -> inventory system: items are ordered by several different customers in different orders - but inventory system has to check if there are enough items left after deducting the orders form the different customers or if the items has to be refilled
   6.it connects the order_ids to the item_ids - so it connects two foreign keys to each other and establishes the many to many relationship

[Leighton]

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

2. If each address can belong to only one customer, such as a personal number.

3. This goes by a Foreign Key which is used for all kinds of database relationships. It is used to enforce referential integrity of data.

4. In a course the student may be studying a subject made up of many modules which consists on many lecturers throughout the duration of the course.

5. A student may attend a class which is also attended by many other students.

6. Used to create a many to many relationship between the order & item id on the table.

[jernejb]

1. One to one, one to many and many to one, many to many, self referencing relationships.
2. Name of a person and his/her ID card number. This can be used when one entry can be matched with only one another entry.
3. Customer_id is a specific number unique for each individual customer. In the same row there is a reference to address_id, this number is a link to address table where each address_id represents one unique address.
4. One person can have many children, this is one to many relation. Also many children can have only this person as a parent, this is many to one relation.
5. Classes and students. One student can visit many classes and each class can enlist many sudents.
6. It creates a many to many relationship between the items and the orders. It connects the order id and item id.

[Naomshka]

1. Relationsships:
   1 to 1
   1 to many
   Many to 1
   Self Referencing

2. A wallet, privat key to a person.

3. It is to distinguish one customer from others within this table and to connect with the other table named addresses_id. Also called foreign key.

4. A person has many bitcoins. Many people share one business.

5. Many NFTs to many owners. A collection of the same NFTs owned by different collectors.
   ( I love my examples! )

6. Many to many relationship between items and orders.

[CryptosaurusRex]

1. What kinds of relationships can we represent in a database?
One to one - one record in a table is associated with only one record in another table, one to many and many to one - one record in a table can be associated with one or more records in another table., many to many - occurs when multiple records in a table are associated with multiple records in another table and self referencing - used when a table needs to define a relationship with itself.

2. When do we use “One to One” relationships? Give an example from the real world.
University database where one student is connected to one student ID 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?
Each customer ID has a corresponding address ID that connects it to the address table.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
Each customer can order a number of different items. So in this case each customer can have multiple orders so it is a one to many relationship.

5. Give an example from the real world when we w ould need to use a “Many to Many” relationship.
For situations where a customer can place orders that contain multiple different items, the orders and the items have a many to many relationship

6. What does items_orders table do?
Creates a many to many relationship between items and orders

[Anidez]

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

When a set of data needs to have an specific record or data, for example, the relation between a customer and the address, a customer and a account number and so on.

-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 author refers to the Foreign Key, this is the link between the tables, this maintain the integrity of the DB

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

In a Hospital, we have a patient, the patient can have one or more desases, and one deseas can have one or more treatments.

with this we can build a huge numbers of relationships between treatment, deseas but only one patient

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

We can build a many to many relationship using the same above example; now the relationship can refer one treatment for different patients with different desases, that means the we can use one treatment to various dessesas (of course if it works), we can use more than one type of medicine, and so on.

[KcDevDude]

1. One to one, one to many, many to one, many to many and self referencing.
2. One to one would be used if you have customers and need to store data for these customers, such as addresses or phone numbers for each of these customers.
3. The customer ID is used to reference other data that is used to identify that customers corresponding data like the customers address.
4. The many to one and one to many would be used if those same customers made multiple orders that could then be reference back to that customer and vice versa.
5. In that same example you would need to use many to many if those customers were making multiple orders or one order had multiple items in it also, many different items could be referenced to multiple customers.
6. The items_orders creates a many to many relationship between the different items and different orders.

[RBIG]

1. One to one, one to many, many to one, many to many and self referencing
2. We use one to one when 2 data fields are related by information that is unique to each other. Some examples of this would be a name and cell phone number, a name and an email address or a nationality and passport number.
3. Each customer is unique and needs to have a primary key to identify them within the database. This primary key can then be be linked to a foreign key to connect the address to the customer.
4. A customer may have several delivery addresses or telephone numbers (home or work) there may also be a need to link multiple orders with many different items contained within each order.
5. A customer could order the same item in a new order that was contained in a previous order, along with other items that were not connected to that previous order.
6. It creates a many to many relationship between items and orders.

[Sergio_Garay]

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.
   You can use it to make a relationship between 2 tables, the particular condition is that 1 data on the first table is related to just one data in the second table.
   Example: personal email.

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?
   You can connect them through a Foreign Key.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
   You can have many children but all these children will have one father or mother.

5. Give an example from the real world when would need to use a “Many to Many” relationship.
   Order something to eat in a restaurant with different ingredients.

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

[FrinaDC]

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 address to one customer or one item in one order.

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 identifying information across different tables in a database. The Foreign key refers to the specific customer and shows all records of information related to that customer in the database.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
   A customer makes many orders, an order contains many items.
   Many email addresses are being accessed on the same one device.

5. Give an example from the real world when would need to use a “Many to Many” relationship.
   Many customers at the same address placing many orders, some of the many items are the same.

6. What does items_orders table do?
   To represent the “Many to many” relationship between orders and items.

[Sgtcrypto]

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 there cannot be more then one relationship between an entry.
   Each Social Security number can be associated 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 done to create a relationship between the customer and the address.

4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
   The many students in this class

5. Give an example from the real world when would need to use a “Many to Many” relationship.
   The Facebook users & groups. I belong to many groups & each group has many users.

6. What does items_orders table do?
   To create a many to many relationship between the items & orders.

[Crypto_Craven]

1. What kinds of relationships can we represent in a database?
   one-to-one
   one-to-many
   many-many
   self
   none (relationships can be optional / lazy)

2. When do we use “One to One” relationships? Give an example from the real world.
   A one to one relationship is a relationship where for a given entity ‘A’ (table and row) there exist entity ‘B’ for example A ‘customer’ entity which has an associated ‘contract details’ entity

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 id column of an table is a unique reference that can be ‘joined’ to another table to create a relationships between rows in each table

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

It’s used to associate a particular entity with a collection. I’ve used many to one relationship in the past when I was building a PDF reporting tool. That system took text inputted by the user and combined it with files they uploaded to output a PDF summary.

5. Give an example from the real world when would need to use a “Many to Many” relationship.
   FOAF or friend-of-a-friend is an example found in Facebook and other social media where my many friends have many friends themselves and we are all connectioned through many-to-many relationship.
6. What does items_orders table do?
   this is an intermediary table used to implement a many to many relationship. between many items and many orders for those items.

[Tomahawk]

1. One-to-one
   One-to-many
   Many-to-many
   Self referencing

2. We can use this when we have one item which has only one record related to it and visa versa. For example name and driving licence ID.

3. Because customer_id in table “Customers” has an address_id, which is a foreign key to the table “Addresses”

4. For example user and bank account (one user can have more bank accounts)

5. For example Suppliers and Customers

6. It connects order_id with item_id in many-to-many relationship. Which can be expressed like which orders contains which products and which products are part of which orders.

[lordkalvin]

• 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.
  • Across 2 diff tables, an item from “Table A” can only relate to an item from “Table B”
  • ie: One original song can only belong to one original author
• 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” column helps to optimize bit-space, thus speedin up searches n query responses
  • “Address_id” is an example of a “foreign key” that links the abbreviated id’s to the fully expressed data
  • This way of storing data can cut down on redundancies
• Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
  • One Spotify user can create many playlists
  • One playlist can list many songs
• Give an example from the real world when would need to use a “Many to Many” relationship.
  • Diagraming sexual partnerships // Person A can “relate” w/ Person B and also Persons C - Q
  • Person B can “relate” w/Person A and also Persons M - Z
• What does items_orders table do?
  • It creates a “Many to Many” relationship between the items and the orders

@LORDKALVIN

[MattL]

1. One to One. One to Many. Many to Many. Self Referencing
2. One User and One Address
3. The address ID in the customer table is called the foreign key which attaches it to the address table and connects the information.
4. A customer orders multiple items and items are ordered by multiple customers
5. Multiple instances on both sides. Orders can contain multiple items and so on…
6. Has only one purpose which is to create a many to many relationship between items and orders.

[Karima]

1. One to one, one to many, many to one, many to many and self referencing
2. When one entry (e.g. a specific individual) is linked to a single specific entry ( e.g. national insurance numer).
3. Customer_id is a unique identifier referred as foreign key which is used as a reference in all kinds of database relationships in order to identify the relationship between different tables.
4. Many to One: All the students attending the same class
   One to Many: One specific student attending many courses
5. Many to many: all the students at uni attending different courses
6. To create a “Many to Many” relationship between the items and the orders

[Denzel]

1.- One to one/One to many/Many to one/Many to many/Self referencing
2.- When only one thing belongs to another one thing. We use this in our daily lives with people on our contacts list, we know certain person is related with a certain phone number.
3.- To reinforce the accuracy of the references, in case of two or more people sharing the same name, these two different people have an extra layer of identification.
4.- In transactions, where we need to keep track of the amount, date, and other factors.
5.- In multisig transactions, where two people have to sign a transaction that can go to multiple addresses.
6.- To create a “Many to many” relationship between the items and the order.