Trebas College - Welcome to Ed2Ti Blog.

Introduction to Databases [3/5]

25 January 2024 - Posted in Trebas College by edward

Concurrency and Transactions in MySQL

Introduction: Concurrency and transactions are fundamental concepts in database management systems, and MySQL, as a widely used relational database, provides robust mechanisms to handle these aspects efficiently. In this article, we will explore the concepts of concurrency and transactions in the context of MySQL, understanding their importance and how they contribute to the reliability and consistency of database operations.

Concurrency in MySQL:

Concurrency in MySQL refers to the ability of the database to handle multiple transactions or queries simultaneously. In a multi-user environment, where multiple clients may be accessing and modifying data concurrently, it is crucial to ensure that the operations are executed in a manner that maintains data integrity.

Isolation Levels: MySQL supports different isolation levels, such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable. These levels define the visibility of changes made by one transaction to other transactions. Choosing the appropriate isolation level depends on the application's requirements for consistency and performance.
Locking Mechanisms: MySQL employs various locking mechanisms to control access to data. Two main types of locks are used: shared locks and exclusive locks. Shared locks allow multiple transactions to read a resource simultaneously, while exclusive locks ensure that only one transaction can modify a resource at a time. Understanding when to use each type of lock is crucial for managing concurrency effectively.

Transactions in MySQL: A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions in MySQL adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability), ensuring the reliability of database operations.

Atomicity: Atomicity ensures that a transaction is treated as a single, indivisible unit. Either all the operations within the transaction are executed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, maintaining the consistency of the database.
Consistency: Consistency ensures that a transaction brings the database from one valid state to another. Constraints and rules defined on the database schema must not be violated during the execution of a transaction.
Isolation: Isolation ensures that the execution of one transaction is independent of the execution of other transactions. Isolation levels, as mentioned earlier, determine the visibility of changes made by one transaction to other concurrently executing transactions.
Durability: Durability guarantees that once a transaction is committed, its changes are permanent and will survive subsequent system failures. MySQL achieves durability by ensuring that the transaction log is written to disk.

Conclusion: Concurrency and transactions play a pivotal role in ensuring the reliability and performance of database systems, and MySQL provides a robust framework for managing these aspects. By understanding the mechanisms of concurrency control, isolation levels, and transaction management, developers can build scalable and consistent applications on top of MySQL databases. Implementing best practices, such as proper indexing and monitoring, further enhances the overall efficiency of database operations.

Create an Ubuntu Server 22.04 virtual machine using VirtualBox

07 January 2024 - Posted in Trebas College by edward

Step 1: Install VirtualBox Download and install VirtualBox from the official website

Step 2: Download Ubuntu Server 22.04 ISO Download the Ubuntu Server 22.04 LTS ISO image from the official Ubuntu website

Step 3: Create a New Virtual Machine

Open VirtualBox.
Click on "New" to create a new virtual machine. Name and Operating System:
Name: Enter a name for your virtual machine (e.g., "Ubuntu Server 22.04").
Type: Linux. Version: Ubuntu (64-bit).
Memory Size: Choose an appropriate amount of RAM for your virtual machine. 2 GB is a reasonable starting point.
Hard Disk: Select "Create a virtual hard disk now" and click "Create."

Step 4: Configure Virtual Hard Disk Choose the hard disk file type. The default is usually fine (VDI).

Choose the storage on the physical hard disk. The default "Dynamically allocated" is recommended as it allows the virtual hard disk to grow as needed.

Set the size of the virtual hard disk. At least 25 GB is recommended for a basic installation.

Click "Create" to create the virtual hard disk.

Step 5: Attach Ubuntu Server 22.04 ISO With the newly created virtual machine selected, click on "Settings."

In the Settings window, go to "System" and uncheck the Floppy disk option.

In the same window, go to "Storage."

Under the "Controller: IDE," click on the empty disk icon under "Attributes."

Click on the disk icon next to "Optical Drive" and choose "Choose a disk file."

Locate and select the Ubuntu Server 22.04 ISO you downloaded earlier.

Click "OK" to close the Settings window.

Step 6: Install Ubuntu Server Start the virtual machine.

The system will boot from the ISO image, and the Ubuntu Server installer will load.

Follow the on-screen instructions to install Ubuntu Server. You'll need to choose the language, keyboard layout, and provide basic system information.

When prompted, choose the installation type. For simplicity, you can choose the "Guided - use entire disk" option.

Complete the installation by following the remaining prompts.

When the installation is complete, remove the ISO from the virtual optical drive to prevent booting from it again.

Reboot the virtual machine.

Step 7: Configure Ubuntu Server Log in with the username and password you created during the installation.

Update the system:

sudo apt update && sudo apt upgrade

You can now configure your Ubuntu Server according to your needs.

Congratulations! You've successfully created and installed Ubuntu Server 22.04 on VirtualBox.

Introduction to Databases [2/5]

04 January 2024 - Posted in Trebas College by edward

DDL (Data Definition Language): Used for defining and managing database objects.

Key Commands:
- CREATE: Used to create database objects (e.g., tables, indexes).
- ALTER: Used to modify the structure of existing database objects.
- DROP: Used to delete database objects.

DML (Data Manipulation Language): Used for managing data within database objects.

Key Commands:
- SELECT: Retrieves data from one or more tables.
- INSERT: Adds new rows of data into a table.
- UPDATE: Modifies existing data in a table.
- DELETE: Removes rows from a table.

DCL (Data Control Language): Manages access to data within the database.

Key Commands:
- GRANT: Provides specific privileges to database users.
- REVOKE: Removes specific privileges from database users.

TCL (Transaction Control Language): Manages transactions within a database.

Key Commands:
- COMMIT: Saves changes made during the current transaction.
- ROLLBACK: Undoes changes made during the current transaction.
- SAVEPOINT: Sets a point within a transaction to which you can later roll back.

Introduction to Databases [1/5]

03 January 2024 - Posted in Trebas College by edward

Objective: Equip participants with a comprehensive understanding of database fundamentals, relational and non-relational models, and essential database management skills. By the end of the course, participants should confidently navigate database systems, design simple databases, and appreciate the strategic role databases play in modern information management. The course aims to empower individuals with practical knowledge applicable to various domains, fostering a strong foundation for further database exploration.

Necessaires Downloads

https://www.virtualbox.org/wiki/Downloads
https://www.mysql.com/downloads/
https://notepad-plus-plus.org/download/
https://sourceforge.net/projects/brmodelo30/
Http://staruml.io/download
https://www.putty.org/
https://dev.mysql.com/downloads/workbench/

Operating Systems

Ubuntu 22.04 : https://ubuntu.com/download/server
Windows 10 : https://www.microsoft.com/en-gb/software-download/windows10

Install Ubuntu on VirtualBox
https://www.ed2ti.com/2024/01/create-an-ubuntu-server-2204-virtual-machine-using-virtualbox

Install Mysql (Linux)

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql.service

GitHub Project

https://github.com/ed2ti/introduction_databases

How to creat your Elevator Pitch presentation

22 March 2023 - Posted in Trebas College by edward

An elevator pitch is a brief, persuasive speech that is used to create interest in a project, business, or idea. It's called an elevator pitch because it should be short enough to be delivered in the time it takes to ride an elevator, usually between 30 seconds to 2 minutes.

Here are some steps to help you create your elevator pitch presentation:

Identify your goal: What do you want to achieve with your elevator pitch? Do you want to get funding for your project, land a new job, or simply introduce yourself and your business?

Define your audience: Who are you speaking to? Understanding your audience will help you tailor your message and make it more relevant and engaging.

Outline your pitch: Start by introducing yourself and your business or idea. Explain what problem you are trying to solve and how your product or service can help. Make sure to include your unique selling proposition (USP) - what makes your product or service different from others in the market.

Keep it simple and concise: Your pitch should be easy to understand and remember. Avoid using technical jargon or buzzwords that may confuse your audience. Focus on the benefits and results that your product or service can deliver.

Practice and refine: Once you have your pitch outlined, practice delivering it in front of a mirror or with a friend. Refine your pitch until it flows smoothly and you feel confident delivering it.

Remember, your elevator pitch should be tailored to your audience and the context in which you are delivering it. With practice, you can create a compelling and effective pitch that will help you achieve your goals.

A litle bit about Artificial Intelligence(AI), Machine Learn(ML), and Deep Learning

07 March 2023 - Posted in Trebas College by edward

On 2023-03-06 I shared with my class colleagues some concepts and examples about AI, Machine Learn, and Deep Learn. It was amazing and we discussed how a machine can identify a Tiger, a common animal from India. (Thank you Ravi Rawat for your comments).

Artificial Intelligence (AI) is a broad field that involves the development of machines that can perform tasks that typically require human intelligence, such as visual perception, speech recognition, decision-making, and language translation. AI systems use algorithms to analyze and process data, learn from patterns and experiences, and make predictions or decisions based on that learning.

Deep learning is a subset of machine learning that is inspired by the structure and function of the human brain. It involves the use of artificial neural networks, which are composed of layers of interconnected nodes that process information and learn from examples. Deep learning algorithms are particularly effective for tasks that involve large amounts of data, such as image or speech recognition, natural language processing, and autonomous driving.

Deep learning has been used in a variety of applications, such as medical diagnosis, fraud detection, image and speech recognition, and language translation. However, it also has some limitations, such as the need for large amounts of training data and computational resources, and the difficulty of interpreting the decision-making processes of deep learning models.

Overall, IA and deep learning are rapidly evolving fields with numerous applications in various industries. As technology continues to develop, we can expect to see further advancements and improvements in AI systems and their ability to perform complex tasks.

Thank you, Professor Iyad Koteich for this opportunity.

RESTful API vs GraphQL

26 January 2023 - Posted in Trebas College by edward

When it comes to building modern web applications, there are two popular approaches for designing and consuming APIs: RESTful APIs and GraphQL. Both have their advantages and disadvantages, and it's important to understand the differences between the two before deciding which one to use.

REST (Representational State Transfer) is a set of guidelines for building web services that use HTTP methods such as GET, POST, PUT, and DELETE to manipulate resources. RESTful APIs are designed around resources and their representations, and they typically use JSON or XML to transfer data between the client and server.

GraphQL, on the other hand, is a query language for APIs that was developed by Facebook. It allows clients to request specific data from the server using a single endpoint, and the server responds with only the requested data. GraphQL uses a type system to define the data that can be queried, and it supports real-time updates and subscriptions.

So, which approach is better? Let's take a closer look at the pros and cons of each.

RESTful APIs

Pros:

Widely adopted: RESTful APIs are a well-established standard and are used by many companies, including Twitter, Google, and Amazon. Caching: Because RESTful APIs use HTTP caching, they can improve performance by reducing the number of requests to the server. Simplicity: RESTful APIs are simple and easy to understand. They use HTTP methods and URLs to manipulate resources, which makes them easy to work with.

Cons:

Overfetching and underfetching: Because RESTful APIs return the entire resource by default, clients may end up receiving more data than they need (overfetching). On the other hand, if a client needs data from multiple resources, it may need to make multiple requests to retrieve all the necessary data (underfetching). Versioning: If the API changes, clients may need to be updated to work with the new version. This can be challenging if the API has a large number of clients. Complexity with hierarchical data: If the data is hierarchical, such as a tree structure, clients may need to make multiple requests to retrieve all the necessary data.

GraphQL

Pros:

Flexibility: Because clients can request only the data they need, GraphQL can reduce the amount of data transferred over the network, resulting in improved performance. Strongly typed: GraphQL's type system makes it easy to understand the structure of the data being queried and reduces the risk of errors. Real-time updates: GraphQL supports real-time updates and subscriptions, which makes it ideal for building real-time applications.

Cons:

Learning curve: Because GraphQL is a new technology, it may take some time to learn and understand. Caching: Because GraphQL queries can be very specific, caching can be more difficult to implement than with RESTful APIs. Server complexity: Because the server needs to process and validate queries, it can be more complex to implement than a RESTful API.

Conclusion

Both RESTful APIs and GraphQL have their advantages and disadvantages, and the choice between them will depend on the specific requirements of the project. RESTful APIs are a well-established standard and are simple to work with, but they can be limited in terms of flexibility and performance. GraphQL, on the other hand, offers greater flexibility and can improve performance by reducing the amount of data transferred over the network, but it requires more effort to implement and may not be suitable for all use cases.

Ultimately, the choice between RESTful APIs and GraphQL will depend on factors such as the size and complexity of the application, the types of data being queried, and the performance requirements of the project.

PostgreSql x MariaDB(Mysql) - As Service

26 October 2022 - Posted in Trebas College by edward

Today I don't want to compare MariaDB(Mysql) with PostgreSQL. I just want to share with you some ways to fastly start these databases as a Service for free.

** Just remember that MariaDB is not the same as Mysql. MariaDB is a community fork from Mysql.

I am talking about a study or test proposal.

The ElephantSql, the name refers to an animal that represents PostgreSql, and you can do your register at https://www.elephantsql.com/

The Skysql, you will receive 500$ to spend with your MariaDB databases. To register an account you can just click on the link https://mariadb.com/products/skysql/get-started/ [Remember to stop the service after you use]

But you can ask me: what is your proposal with this share?

Between 2022-10-31 and 2022-11-04, professor Iyad Koteich asks me to present some examples of using ETL with Pentaho(PDI). We will do an ETL process with Python, and then, we will do the same with Pentaho(PDI).

The main purpose is that you can understand the ETL pipeline at Pentaho.

All the files and requirements will be here, and you can download them on GitHub (https://github.com/ed2ti/Pentaho_Data_Integration)

Thank you Professor Iyad Koteich for this new challenger.

Business Intelligence e Analytics: From Abyss to Collision

25 October 2022 - Posted in Trebas College by edward

Today I was reading and organizing some files from my computer and I found my first article from Trebas.

It was interesting because after reading again this file I found a lot of necessary modifications and corrections. Of course, I can't do that on the original document, but I'm planning to do another with a similar title.

If you can read It, I'll really appreciate your feedback by mail. Thank you!

Professor: Ghazal G.Fard
College: Trebas Institute

Link to download https://github.com/ed2ti/articles/blob/main/trebas_assignment_2_english.pdf

Python & Pandas & SQLite

13 October 2022 - Posted in Trebas College by edward

As requested by Professor Iyad Koteich, now the application saves the data on the SQLite database.

In the first post (here) we did a python program to join three spreadsheets in just one. This work involves knowledge of pandas to organize the data.

Now we create a database and a table to store all data. This version is so simple and does not check the user store option.

I'll create a new project on GitHub because I'll show how to merge these steps in one project. (god to understand git commands line)

GitHub: https://github.com/ed2ti/exercise04

[CODE]

# *************************** #
# College: Trebas Institute 
# Professor: Iyad Koteich
# Class: Edward
# Day: 2022-10-13
# *************************** #

#Importing Libres
## Pandas ##
import pandas as pd
from pandas import DataFrame 

## sqlite3 ##
import sqlite3
from sqlite3 import Error

###
### INFORMATIONS FROM Concordia ###
###

##Loading Data
concordia = pd.read_excel('Concordia.xlsx')

## Informing that this informations are form Concordia
concordia["College"] = 'Concordia'

#show Concordia Result
print(concordia.head())
print('n')

###
### INFORMATIONS FROM McGill ###
###

##Loading Data
mcgill    = pd.read_excel('McGill.xlsx')

## Informing that this informations are form McGill
mcgill["College"] = 'McGill'

## Organizing the DataFrame
mcgill.rename(columns={'id': 'Student_Code'}, inplace = True)
mcgill.rename(columns={'name': 'Full_Name'}, inplace = True)
mcgill.rename(columns={'Course': 'Program'}, inplace = True)
mcgill.rename(columns={'country': 'Nationality'}, inplace = True)
mcgill.drop(columns=['city'], inplace = True)

#show McGill Result
print(mcgill.head())
print('n')

###
### INFORMATIONS FROM Trebas ###
###

##Loading Data And Print The Colums
trebas    = pd.read_excel('TREBAS.xlsx')

## Informing that this informations are form TREBAS
trebas["College"] = 'TREBAS'

## Organizing the DataFrame
trebas.rename(columns={'Country': 'Nationality'}, inplace = True)
trebas.rename(columns={'Student_ID': 'Student_Code'}, inplace = True)
trebas["Full_Name"] = trebas["First_Name"] + " " + trebas["Last_Name"]
trebas.drop(columns=['City'], inplace = True)
trebas.drop(columns=['First_Name'], inplace = True)
trebas.drop(columns=['Last_Name'], inplace = True)

#Ajusting the sequence of the columns
trebas = trebas[["Student_Code","Full_Name","Nationality","Program", "College"]]

#show Trebas Result
print(trebas.head())
print('n')

final = pd.concat([trebas, concordia,mcgill], ignore_index=True, sort=False)
print(final)


# Exporting to excel (to_excel)
final.to_excel ('final.xlsx', index = True, header=True)


## 
## Try to conect database (memory)
## This database is on the Memory (no file) for a best performance.
## If you want, you can change :memory: for a name file.
##

#target = ':memory:';
target = 'ex-04.db';

conn = None;
try:
    conn    = sqlite3.connect(target)
    print("connect on Database sqlite3 "+sqlite3.version)
    cur     = conn.cursor()
except Error as e:
    print(e)

# Create a final table (IF NOT EXISTS)#
## id = AUTOINCREMENT ##
cur.execute("CREATE TABLE IF NOT EXISTS final(id INTEGER PRIMARY KEY AUTOINCREMENT, Student_Code TEXT, Full_Name TEXT, Nationality TEXT, Program TEXT, College TEXT)")

## Trucate the final table ##
cur.execute("DELETE FROM final")

#
data = final.values

#print(len(data))

for i in range(len(data)):
    #datal = Data Line 
    datal = data[i]
    # writing on Database
    cur.execute("INSERT INTO final(Student_Code, Full_Name, Nationality, Program, College) VALUES (?,?,?,?,?)", datal)
    conn.commit()
conn.close()