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

Gorgees , Ammar, Lubna

What is the difference between SQL and NoSQL?

• SQL databases are table-based, while NoSQL databases can be document-oriented, key-value pairs, or graph structures.
• SQL databases scale vertically, usually on a single server, and require users to increase physical hardware to increase their storage capacities. NoSQL databases offer horizontal scalability, meaning that more servers simply need to be added to increase their data load.
• SQL databases use SQL (Structured Query Language). NoSQL databases use JSON (JavaScript Object Notation), XML, YAML, or binary schema, facilitating unstructured data.
• SQL has a fixed-defined schema, while NoSQL databases are more flexible.

What is referencing and what is embedding in MongoDB?

• Manual references save the _id field of one document in another document as a reference. Your application runs a second query to return the related data. These references are simple and sufficient for most use cases.
• MongoDB provides you a cool feature which is known as Embedded or Nested Document. Embedded document or nested documents are those types of documents which contain a document inside another document.

Why should we embed more than referencing when we can in MongoDB?

Embedded documents are an efficient and clean way to store related data, especially data that’s regularly accessed together. In general, when designing schemas for MongoDB, you should prefer embedding by default, and use references and application-side or database-side joins only when they’re worthwhile. The more often a given workload can retrieve a single document and have all the data it needs, the more consistently high-performance your application will be.

When should we prefer referencing over nesting in MongoDB?

MongoDB applications use one of two methods to relate documents: Manual references save the _id field of one document in another document as a reference. Your application runs a second query to return the related data. These references are simple and sufficient for most use cases.
Referencing is how you acknowledge the source of the information you have used (referred to) in your work. It helps to make clear to the reader how you have used the work of others to develop your own ideas and arguments.

What are ORMs? Why we use them? Give an example of an SQL request with and without using ORM.

Object Relational Mapping a library that makes it easier to communicate with a database in code. It is basically a technique to query or perform CRUD (Create, Read, Update, Delete) operations to the database, mainly RDBMS (Relational Databases), using an object-oriented paradigm. With the help of ORM, you don"t actually need to use SQL at all. You can directly interact with the database and perform queries in the same language you are using for your back-end code

What is the difference between a table and a collection?

tables have a fixed schema and adding new columns requires modifying the entire table, collections can contain documents with different structures.

Example:

const mysql = require("mysql");
const conn = mysql.createConnection({
	host: "localhost",
	user: "your_username",
	password: "you_password",
	database: "mydb",
});

conn.connect(function (error) {
	if (error) {
		console.log(error);
	} else {
		console.log("Connected!");
    let sql = "INSERT INTO Users (username, password) VALUES ('john-doe', 'randompassword')";
    conn.query(sql, function (error, result) {
      if (error) {
        console.log(error);
      } else {
        console.log(result);
      }
    });
  }
});

Using Sequelize, the same code would become:

const Sequelize = require("sequelize");
const sequelize = new Sequelize("mydb1", "your_username", "your_password", {
	dialect: "mysql",
});

//Defining User model

const User = sequelize.define("User", {
	username: Sequelize.STRING,
	password: Sequelize.STRING,
});

sequelize.sync();

User.create({
	username: "john-doe",
	password: "randompassword",
}).then(function (user) {
	console.log(user);
});

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