No SQl vs SQL discussion

nosqlvssql.md

Discuss the answers for these questions with you teammates and write your answers in the comments.

1. What is the difference between SQL and NoSQL?
2. What is referencing and what is embedding in MongoDB?
3. Why should we embed more than referencing when we can in MongoDB?
4. When should we prefer referencing over nesting in MongoDB?
5. What are ORMs? Why we use them? Give an example of an SQL request with and without using ORM.
6. What is the difference between a table and a collection?

Some helpful resources:

• https://joekarlsson.dev/MongoDBSchemaDesign
• https://blog.bitsrc.io/what-is-an-orm-and-why-you-should-use-it-b2b6f75f5e2a

Jana AbuHaltam, Raneem AlHamarneh, Mohamad Alchehabi, Hala Qitouqa

Q1
SQL are relational databases and NoSQL are non-relational databases
SQL databases are vertically scalable, NoSQL databases are horizontally scalable
SQL are table-based, NoSQL could be key-value pairs, document-based, graph databases or wide-column stores
Examples of SQL databases: Oracle, MySQL
Examples of noSQL databases: MongoDB and CouchDB

Q2 Embedded Documents:

Used for one-to-many relationships with a "contains" or "has a" association.
Example: Blog posts containing multiple comments. All comments are embedded within the blog post document.
Pros: Better performance for read and update operations as everything is in a single document.
Cons: Risk of data duplication and limited by the maximum document size of 16MB.
References (Manual References):

Used for many-to-many relationships and complex hierarchical data.
Example: Blog posts and comments stored in separate collections; comments reference the blog post’s _id.
Pros: Avoids data duplication and is effective for complex relationships.
Cons: Slower read performance due to the need for multiple queries to join related data.

Q3 Referencing saves space and allows for more scalability, but it can be slower due to multiple queries and can be complex to maintain data consistency. Choosing between them depends on your specific needs regarding performance, data size, and complexity.

Q4 use referencing when dealing with large, complex, frequently changing, or interrelated datasets where embedding would be impractical or inefficient.

Q5 1-Simplifies Code
2-Enhances Maintainability
3-Database Abstraction
4-Reduces Boilerplate

Without ORM (Using Raw SQL in Node.js):

the code in JS

const mysql = require('mysql');

// Create connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'your_database'
});

// Connect to MySQL
connection.connect();

// Execute query
connection.query('SELECT * FROM users WHERE age > 30', (error, results, fields) => {
    if (error) throw error;
    // results is an array of records
    const users_over_30 = results;
    console.log(users_over_30);
});

// Close connection
connection.end();

With ORM (Using an ORM in Node.js):
const { Sequelize, Model, DataTypes } = require('sequelize');

// Connect to a database
const sequelize = new Sequelize('your_database', 'your_username', 'your_password', {
    host: 'localhost',
    dialect: 'mysql' // or 'sqlite', 'postgres', 'mssql'
});

class User extends Model {}

User.init({
    // Assuming a model structure, modify as needed
    age: DataTypes.INTEGER
}, { sequelize, modelName: 'user' });

async function getUsersOver30() {
    const users_over_30 = await User.findAll({
        where: {
            age: {
                [Sequelize.Op.gt]: 30
            }
        }
    });
    return users_over_30;
}

getUsersOver30().then(users => console.log(users));

Q6 table is structured collection of data organized in rows and columns
in collections, data is organized in documents