SQL Relationships - Reading Assignment


#21
  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.
    a passport can only belong 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?
    One adress can belong to several customer. So with the id in the table you can write SQL questions to collect specific data from each table and show the data together in one report. You can for eample list all customer that have a specific address id or all adresses that have a specific customer id.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    When you buy somehting on a webshop. You as a customer can make many transactions. But a transaction can only apply to one costumer. And a transaction can contain several products.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    One book in a library can relate to several readers. One reader can relate to several books.

  6. What does items_orders table do?
    That table contain every item id from the ITEMS table that are related to a specifik order id.


#22
    • one to one relationship
  • one to many and many to one relationship
  • many to many
  • self referencing relationships
  1. assigning a user ID to a username in order to login to a website

  2. customer_id is a “foreign” assigned ID to the customer - creating a relationship between the customer name and the artificially generated cusomer_id. It is called a “Foreign Key”

  3. e-commerce is the most predominant case of “many to one/one to many” relationships
    as 1 customers can order many products that can be found in many warehouses but at any point we need to know where those many products from what warehouse was shipped to this unique customer

  4. ordering a kit car for example - “many to many” relationship is visible as you order many parts of that kit but each parts can maybe part of different assembly kits on 5 different models of kit cars. The manufacture has to be able to keep track them in order to supply

  5. items_order it creates “many to many” relationships between order_id and item_id


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

One-to-one/One-to-many/many-to-many/self-referencing relationships

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

Customer to address, customer to email, etc.

  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?

To make a connection between the ‘customers table’ and the ‘address table.’

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

Orders w/ many items, a repeat customer who has made several orders, an item that has been ordered by many customers, etc.

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

Many customers that live in the same city or in different cities

  1. What does items_orders table do?

It creates a many-to-many relationship


#24
  1. One to one
    One to many - many to one
    Many to Many
    Self Referencing
  2. One to one would be used where on a table, we have for example a customer, and we needed to associate perhaps a date of birth to that customer, or some attribute directly relating to that customer in this example.

An example would be if I completed a bike race and wanted to ensure the results did indeed have me in first place. I could querry the web page that has claimed to have the results posted. Perhaps there would be a database that would have both my name and my finish time and my finishing position.
Let’s say there was an variable text entry window where I could type the name or race number of the participant, and the result would be posted for me to see. Likely, this could be done using a 1 to 1 relationship quite easily.

  1. The customer_id is used as a sort of ‘tag’ to allow something to be associated with it. Now, data can be quarried by using attributes under a specific tag.

  2. An example would be if I wanted to see the names of everyone that liked my post, because I’m so popular and all I can see is a three to seven digit number that represents how many people liked something I posted, then I can make a querry to show the names of everyone that liked my post. This could be accomplished using a one to many relationship, such as wanting to see the names of all customer_id’s for example that liked a very specific post.

…ok, I just realized that my example may not be that great of a ‘practical’ example, because as I was typing, I realised that perhaps NoSQL would be better for this type of database, because I am so popular, and have so many likes that in order to handle ‘Large’ volumes of people that like my stuff, SQL may reach it’s upper limits…but I suspect for ‘ordinary’ people, maybe SQL would work.

  1. Here’s a real world example, lets say I’m the google god, and have access to the whole google database and would like to find out who liked Dideridoo’s, but of those people, I would like to know if they had any friends who were into tree hugging. And when these two conditions were met, I would then like to know out of these results, pairs of people within the same geographic region, but because we are only using SQL, this could be determined by Postal Code for example.

  2. It shows us a list based on combining data from two separate ‘tags’ whereby we can now see information more specific to our querry.
    :wink:
    YC


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

One to one
One to many
Many to many

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

Users
Join dates

  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?

Using foreign key

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

Time, customer, location to item purchased

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

Multiple businesses to multiple products compairing for commonalities

  1. What does items_orders table do?

List items that can be purchased


#26
  1. What kinds of relationships can we represent in a database?
    There are various types of relationships that can be represented in a database- One to one relationships, one to many and many to one relationships, many to many relationships, and self referencing relationships.
  2. When do we use “One to One” relationships? Give an example from the real world.
    An example of a one to one relationship is the relationship between my car and I. Because I am the current owner of the car, I have a one to one relationship with it and it has a one to one relationship with me.
  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 column identifies the customer who has the relationship with the address_id
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    My mother has 4 children. We all(many) have a relationship with my mother(to one) and she(one) has a relationship to all 4 of us(to many).
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    A group of friends who all have various relationships with each other is a many to many relationship. Jack is friends with Bill who is friends with Karen who is also friends with Jack.
  6. What does items_orders table do?
    The items_orders table creates a many to many relationship between the items and orders.

#27
  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 a unique attribute in a table matches an unique attribute in another table.

  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 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.
    When placing online orders and many users want the same product, or one user wants many products. The store may want a record of the purchases that a client has purchased previously

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Online Book store. Many people orderng many books

  6. What does items_orders table do?
    Links customers to correct items purchased.It allows for a many to many relationship to be established between items and orders.


#28
  1. What kinds of relationships can we represent in a database?
  • One to One
  • One to many or many to one
  • Many to many
  • Self-referencing (but not part of readings)
  1. When do we use “One to One” relationships? Give an example from the real world.
    You would use one to one if the two data points can only belong to each other. An example would be a birth certificate, which belongs to one person and that person’s name is on that document.

  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?
    This is the primary key that can be referenced elsewhere, if needed (in the table it would be referenced to, it would be considered the foreign key)

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A list of all my passwords. All those passwords belong to me, but I own many passwords.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Customers of all restaurants in Montreal. A customer can frequent many restaurants and a restaurant will have many customers.

  5. What does items_orders table do?
    This is what makes the many to many relationship. It takes two foreign keys to link the two other tables together.


#29
  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 there cannot be more then one relationship between an entry.
    Example: social security number provided to a unique individual

  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?
    It’s the Foreign Keys which are used for all kinds of database relationships. It is used to ensure referential integrity of the data.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    very common for e-commerce website: Ebay, Amazon …

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Also common for e-commerce website but with detailed orders.

  5. What does items_orders table do?
    to create a Many to Many relationship


#30

1. What kinds of relationships can we represent in a database?
Relationships which relate the entities. These are:
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 two entities can only be related to each other by one of each. Country and Capital. Other example could be Person and Passport.

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 primary key in customer table to identify each customer uniquely. address_id is used as foreign key to relate customers table to adresses table.

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

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

6. What does items_orders table do?
It is a Join table that joins items and orders tables to simplify many to many relationship to one to many relationship


#31

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

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

We use a one to one relationship when we are linking a user id with the given name of the user.

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 because that is the unique record in that field in both tables that links the customer name to his/her address.

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

In accounting software, a one to many example would be one customer to all his invoices.

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

A many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.

What does items_orders table do?

The Items_Orders table creates a many to many relations as links the Item purchased with the with the order number for the customer who purchased the products.


#32

What kinds of relationships can we represent in a database?
One to One Relationships
One to Many & 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.
You can’t use the same email to open two different Facebook accounts, each account must have its own 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?
That it is a Primary key used to identify the customer. This let’s the customer change other information in that row, such as their address, without affecting the relationships built around the primary key. It enforces referential integrity between data in two tables.

Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
For example facebook, a user will have many friends (One to many), and some of those friends like a particular resturant (Many to one).

Give an example from the real world when would need to use a “Many to Many” relationship.
Facebook example again, ”your friends list”, ”people who liked Thai restaurants”, ”your friends who like eating at Thai restaurants”, the result an advertiser in Facebook that is a Thai restaurant has access to your friends that like eating at Thai restaurants, directly advertising to interested dinners.

What does items_orders table do?
This is only used to determine the Many to Many relationship between the “order_id” table and the “item_id” table”.


#33
  1. One to one, one to many, many to one, and many to many
  2. When we want to present an unique characteristic to the object we preset, date of birth of a specific person
  3. So he can use this Id in other references/tables. By creating and address ID tight to an customers ID, created a step earlier.
  4. Data base of the employees in an organization, oner person has a specific title, and also many people can have the same title.
  5. Company’s employees social events participation,
  6. list different orders to the specific customer.

#34
  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.
    A case where each unique Customer is represented with each IP 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?
    Customer_id is intended to be used as a foreign key in a different table. This relationship will be used to JOIN the tables.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A customer can have multiple adresses or an adress can be assigned to multiple customers.

  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    Facebook may show university you and your friends attended. It can as well show the multiple univeristies you attended.

  6. What does items_orders table do?
    It is used to create the relationship between the items and orders tables.


#35
  1. What kinds of relationships can we represent in a database?
    We can represent One to One, One to Many and Many to One, Many to Many, and Self Referencing relationships.
  2. When do we use “One to One” relationships? Give an example from the real world.
    We use One to One relationships when the items we are referencing or relating to in one table can only refer to one other item in another table. A real world example is a database that has a customer table and the customer table has customer id’s which reference their address which no one else shares.
  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 used in the customers table to separate each customer from the next. It is being used a the “Foreign Key” in the customers table.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    There are Many students who attend one class/There is one student who attends many classes.
  5. Give an example from the real world when would need to use a “Many to Many” relationship. There are many customers at a restaurant and they all have the ability to choose from multiple courses.
  6. What does items_orders table do?
    It creates a “Many to Many” relationship between the items and the orders.

#36
  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 „One to One“ relationship is used if there is only an exact pair to the relevant object/subject. For instance a Persons full Name and his/her ID Identification 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? – The customer-id functions as a reference in the other table. This is called foreign key.

  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship. – A person can have blue eyes. And there are many other persons who have also blue eyes.

  5. Give an example from the real world when would need to use a “Many to Many” relationship. – A person can use many colored pencils. The same colored pencils can be used by another person.

  6. What does items_orders table do? – The ITEMS_ORDERS table show which types of items are associated to an order. Its purpose here is to create a many-to-many relationship between the 2 preceding tables ORDERS & ITEMS.


#37
  1. What kinds of relationships can we represent in a database?
    • 1 to 1, 1 to many, many to 1, many to many
  2. When do we use “One to One” relationships? Give an example from the real world.
    • It creates an unique link between two data elements (Name and SSN for example)
  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 creates a direct relationship between the customer and the address. The foreign key is used to create relationships between users and other tables.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    • A way of tracking online transactions, both multiple purchases and multiple items with a single purchase.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    • Many different users login at many different times
  6. What does items_orders table do?
    • It creates a “many to many” relationship between items and orders.

#38
  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 and Self Referencing Relationships
  2. When do we use “One to One” relationships? Give an example from the real world.
    When an item can belong to one only client. For example I have one national ID
  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?
    Because the ID is unique so every ID number is related to one and only one customers
    he used it as a Foreign Key, so it creat a relation 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.
    My clothes have one owner and its me “many to one” and I have many clothes “one to many”
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    My and My wife have two cars, I can use her’s and she can use mine’s
  6. What does items_orders table do?
    It create relation between Items and orders “many to many”

#39
  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 a customer in an online store has to have his birthday entered in a column .

  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?
    This is called a foreign key. The thing is that a customer can have two adresses. In this way the adresses are stored on a different table. Now relationships are build between the tables.

  3. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A customer at an online store can have many orders.

  4. Give an example from the real world when would need to use a “Many to Many” relationship.
    Many orders can be booked by many people

  5. What does items_orders table do?
    The Items_Orders table creates a many to many relations as links the Item purchased with the with the order number for the customer who purchased the products.


#40
  1. What kinds of relationships can we represent in a database?
    1 to 1; 1 to many, many to many & self-referencing.
  2. **When do we use “One to One” relationships? Give an example from the real world.**t
    when a piece of data has only one unique identifier eg Car number plates for cars.
  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 custumer_id is the attribute of a customer in the customers’ table that connects or relates to the addresses in the address table. It is a foreign key.
  4. Give an example from the real world when would need to use a “Many to One/One to Many” relationship.
    A father and children relationship in a family.
  5. Give an example from the real world when would need to use a “Many to Many” relationship.
    The bus stops on a route can be 'stops for many buses going different routes.
  6. What does items_orders table do?
    it creates the relationship between the Items table and the Orders table.