MySQL – Database Transaction

MySQL Transaction tutorial for Beginners | CreativeDev
image source:

A transaction is a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data). Transactions guarantee validity of the data even in the event of errors, power failures etc.

MySQL fully satisfies the ACID properties for transaction operations in the RDBMS.

ACID is an acronym that stands for:

  • A – Atomicity
  • C – Consistency
  • I – Isolation
  • D – Durability


A transaction is an atomic unit, all instructions within a transaction will successfully execute or none of them will execute. MySQL handles this by storing the results of transactional statements in a memory buffer and writes to disc only when the transaction is committed.


A database is initially in a consistent state before a transaction starts, in the event of an error/failure during the transaction, the database must return to its consistent state. This is primarily handled by MySQL logging mechanisms which record all changes to the database and provide an audit trail for transaction recovery.


If two transactions are running concurrently, they should not be affected by each other. i.e, the result should be the same as the result obtained if the transactions ran sequentially. For example if transaction A and transaction B starts concurrently, A accesses property X, modifies it and then transaction B accesses property X, transaction B should get the modified value done by transaction A. The implementation detail depends on the MySQL engine, for example, InnoDB engine uses fine-grained row-level locking for this purpose.


Changes that have been committed should remain even in the case of software and hardware failure. MySQL implements durability by maintaining a transaction log file that tracks changes to the system during the course of a transaction.


DataStructures Series #15 – The Graph ADT

The Graph ADT is meant to implement the directed and undirected graph concepts from the field of graph theory within mathematics.

A graph data structure consists of vertices (nodes) and edges connecting the vertices. A graph with direction on the edges are called directed graphs while edges without direction are called undirected graphs as shown in the image below.

Image source:


The graph ADT can be represented in the different data structures shown below:

  • Adjacency matrix
Representing Graphs -
image source:

The matrix above is a 6 by 6 matrix representing the vertices (nodes) in the labelled graph.

The ones represent nodes that have edges while zeros shows that there are no edges connecting the nodes.

For example, position 1,1 on the matrix is 1, and we can see on the graph that there is indeed an edge connecting 1 to itself. Position 2,3 or 3,2 also has 1 indicating a connection between node 2 and 3. Position 3,5 or 5,3 for example is 0 and can be seen on the graph that there isn’t indeed an edge connecting node 3 and 5.

Please Note: We can pick both directions for an undirected graph, this of course will change if the graph is directed for example, 3,5 can be 1 while 5,3 is 0.

  • Adjacency list
image source:

The graph above can be represented in a list form as shown below:


The column on the left are the nodes in the graph and the column on the right contains all the connected nodes.

Please Note: We can pick both directions for an undirected graph, this of course will change if the graph is directed for example, a can point to vertix b and not vice versa.

  • Incidence matrix

image source:

In the incidence matrix, the edges are labelled and then a matrix is created from the labelled edges and the nodes as shown in the table and matrix below.

{\displaystyle {\begin{pmatrix}1&1&1&0\\1&0&0&0\\0&1&0&1\\0&0&1&1\\\end{pmatrix}}.}
image source:


  • Adjacent (x, y) – tests if there is a direct edge linking x and y
  • Neighbours (x) – gets all vertices linked directly to x
  • AddVertex (x) – add vertex x if it doesn’t already exist
  • RemoveVertex (x) – removes vertex x if it exists
  • AddEdge (x, y) – adds vertex x to y if it doesn’t exist
  • RemoveEdge (x, y) – removes vertex x to y if it doesn’t exist
  • GetVertexValue (x) – returns vertex x value
  • Graph Traversal

The graph traversal is the most important operation as all other operations depend on it.

The Depth First Traversal

Similar to tree traversal, the DFS (depth first traversal) visits the child vertices before visiting the sibling vertices, the difference being that visited nodes are marked during the traversal.

The Breadth First Traversal

Similar to tree traversal, the BFs (breadth first traversal) visits the sibling vertices first before the child vertices, the difference being that visited nodes are marked during the traversal.

Real life Applications

  • Relationships between people on social media (facebook, instagram etc)


Food/Fruits Nutrient Matrix

You are what you eat.

BeansAmount Per 100 grams
Total Fat – 0.5g :
(Saturated Fat – 0.2g,
Unsaturated fat – 0.2g)
Potassium – 1,254mg
Total Carbohydrate – 63g :
(Dietary fibre – 13g)
Protein – 20g

Calcium – 6%
Iron – 27%
Vitamin B6 – 20%
Magnesium – 31%

Calories – 347
EggsAmount Per 100 grams
Protein – 13g
Total Fat – 11g :
(Saturated fat – 3.3g,
Unsaturated fat – 5.5g)
Cholesterol -373mg
Sodium – 124mg
Potassium – 126mg
Total Carbohydrates – 1.1g :
(Dietary fibre -0g, Sugar 1.1g)

Vitamin D – 21%
Vitamin B12 – 18%
Vitamin A – 10%
Calcium – 5%
Iron – 6%
Vitamin B6 – 5%
Magnesium – 2%

Calories – 155
OnionsAmount Per 100 grams
Potassium – 146mg
Total Carbohydrate – 9g :
(Dietary fibre -1.7g, Sugar 4.2g)
Protein – 1.1g

Vitamin C – 12%
Calcium – 2%
Iron – 1 %
Vitamin B6 – 5%
Magnesium – 2%
PepperAmount Per 100 grams
Potassium – 340mg
Total Carbohydrate – 9g :
(Dietary fibre -1.5g, Sugar 5g)
Protein – 2g

Vitamin C – 404%
Vitamin A – 23%
Iron – 6%
Calcium – 1 %
Vitamin B6 – 15%
Magnesium – 6%
NutsProtein, Fats
Vegetable Oils
(olive, avocado,
coconut oils)
OrangesAmount Per 100 grams
ApplesAmount Per 100 grams
Total Carbohydrates – 14g :
(Dietary fibre -2.4g, Sugar 10.4g)
Protein – 0.3g
Potassium – 107mg
Sodium – 1mg
Fat – 0.2g

Vitamin C – 7%
Vitamin A – 1%
Magnesium – 1%

Calories – 52
SunlightVitamin D

NutrientsDescription and Function
Protein (Macro)Protein provides the building block of
the body. Every cell from bone to skin
to hair contains protein.

All hormones, antibodies and other
important substances are composed
of protein.

Proteins are made of different
amino acids, while the body can create
some amino acids on its own, there
are some essential amino acids that
come from food.
Carbohydrates (Macro)Carbohydrates fuel your body, especially
your central nervous system and brain
and again protect against disease.

Carbohydrates should make up 45 to 65
percent of your total daily calories
Fat (Macro)Fat supports many of the body function
such as vitamin absorption,
blood clotting, building cells and
muscle movement.

Fat is high in calories but these calories
are an important energy source for the

It is recommended that 20 to 35 percent
of your daily calories come from fat.

Unsaturated fats are omega-3 and
omega-6 fatty acids. Unsaturated fats
are important for the body as they
provide essential fatty acids the body
can’t make.

Please avoid trans fat and limit your
intake of saturated animal-based fats
like butter, cheese, red meat and
Vitamins (Micro)Vitamins are vital for warding off
diseases and staying healthy. The
body needs these nutrients to
support its functions.

There are 13 essential vitamins
that the body needs to function
properly including the vitamins
A, C, B6 and D

A D E and K are fat-soluble while
the rest are water soluble.

Vitamin A – Helps form and maintain
healthy teeth, bones, soft tissue,
mucous membrane and skin.

Vitamin B6 – Helps form red blood cells
and maintain brain function

Vitamin B12 – Important for metabolism,
also helps form red blood cells and
maintain the central nervous system

Vitamin C – An antioxidant that promotes
healthy teeth and gums, it helps
the body absorb iron and maintain
healthy tissue. It is also essential
for wound healing.

Vitamin D – Also known as sunshine
vitamin,It helps the body absorb calcium.

Vitamin E – Helps the body form red blood cells
and use vitamin K

Vitamin K – It helps the blood coagulate (stick

Vitamin Biotin – Helps in the metabolism of
carbohydrates and protein

Vitamin Niacin – is a B vitamin that helps
maintain healthy skin and nerves

Vitamin Folate – works with vitamin B12
to help form red blood cells

Vitamin Pantotenic acid – essential for food

Vitamin B2 (Riboflavin) – works with other B
vitamins, it is essential for body
growth and the production of
red blood cells.

Vitamin B11 – Helps the body change
carbohydrates into energy. Getting
enough carbohydrates during
pregnancy and breastfeeding is very

Vitamin Chlorine – Helps in normal functioning
of brain and nervous system. Lack of
chlorine can also cause swelling in liver.

Vitamin Carnitine – Helps to change fatty acids
into energy.
MineralsMuch like vitamins, minerals help
support the body. They’re essential
for many body functions.

Macro Nutrients:

Sodium – Needed for proper fluid
balance, nerve transmission and
muscle contraction.

Chloride – Needed for proper fluid
balance, stomach acid.

Potassium – Needed for proper fluid
balance, nerve transmission and
muscle contraction.

Calcium – Important for healthy bones
and teeth, helps muscles relax and
contract, important in nerve functioning,
blood clotting, blood pressure regulation,
immune system health.

Phosphorus – Important for healthy
bones and teeth, found in every cell,
part of the system that maintains
acid-base balance.

Magnesium – Found in bones; needed
for making protein, muscle contraction,
nerve transmission, immune system health.

Sulfur – Useful for synthesis proteins in the

Micro Nutrients:

Iron – an essential element in blood
production in the body.

Zinc – Has a function in taste perception,
wound healing, fetal development, sperm
production, normal growth and sexual
maturation, immune system health.

Iodine – Found in thyroid hormone which
helps regulate growth, development and

Selenium – is an antioxidant

Copper – part of many enzymes, needed
for iron metabolism

Manganese – part of many enzymes

Fluoride – Involved in formation of tooth
and bones, helps prevent tooth decay.

Chromium – Works closely with insulin
to regulate blood sugar levels.

Molybdenum – Part of some enzymes for
WaterWater is absolutely crucial for every system
in the body, it is also the main thing we are
mad of. About 62% of the body weight is water.

Water improves your brain function and mood. It acts a shock absorber and a lubricant in the body. It also helps flush out toxins, carry nutrients to cells, hydrate the body, and prevent constipation.


Solid Principles

S.O.L.I.D: The First 5 Principles of Object Oriented Design ...

Solid Principles are a set of principles created by Robert C. Martin to guide software engineers in creating maintainable Object Oriented Applications.

SOLID Principles:

  • S : Single Responsibility
  • O : Open/Closed
  • L : Liskov Substitution
  • I : Interface Segregation
  • D : Dependency Inversion

Single Responsibility

  • Each class should do just one job and should have only one reason to change.


  • A class should be open for extension but closed to modification.
  • Do not modify your classes to add class types.

Liskov Substitution

  • Subclasses can substitute parent classes without breaking functionality. Examples:
    • New exceptions shouldn’t be thrown in the derived classes.
    • Pre conditions cannot be more strict in the derived classes (e.g subclass accepting positive integers only)
    • Post conditions cannot be weakened (e.g opening without closing in subclasses while being closed in parent class)

Interface Segregation

  • Interface should represent one cohesive distinct behaviour
  • All methods in an interface should be implemented.

Dependency Inversion

  • A high level module should not depend on a low level module, both should depend on abstractions.
  • Abstractions should never depend upon details, detail should depend on abstractions.


Using an online Library as a case study, the class below violates the SOLID principles.

The initial class:

Class Book {

    Function getTitle() ;

    Function getAuthor() ;

    Function turnPage() ;

    Function printCurrentPage (String printerType) :
        Switch (printerType) :
            Case “plainTextPrinter” :
                return “plain text print out” ;
            Case “htmlPrinter” :
                return “html print out" ;

    Function savePage (String storeType) :
        Switch (storeType) :
            Case “flatFile” :
                return “save in flat file”  ;
            Case “mysql” :
                return “save in mysql DB” ;

We can apply the SOLID principles as follows:

Single Responsibility says a class should do just one thing. We can look at the class above and see three distinct entities.

  • Book
  • Store
  • Printer

Class Book {

    Function getTitle() ;

    Function getAuthor() ;

    Function turnPage() ;

    Function getCurrentPage() ;

    Function getNextPage() ;

Class Store {

    Function saveBook(String storeType) ;
        Switch (storeType) :
            Case “flatFile” :
                return “save in flat file”  ;
            Case “mysql” :
                return “save in mysql DB” ;

Class Printer {

    Function printBookPage (String printerType) :
        Switch (printerType) :
            Case “plainTextPrinter” :
                return “plain text printout” ;
            Case “htmlPrinter” :
                return “html printout" ;

Open/Closed says a class should be closed to modification but open for extension.

Class Printer {
    Function printBookPage (PrinterInterface printer) :
        printer.print() ;

Interface PrinterInterface {
    Function print() ;

Class HtmlPrinter implements PrinterInterface {
    Function print() :
        return  “html print out” ;

Class PlainTextPrinter implements PrinterInterface{
    Function print() :
        return “plain text print out” ;

This way a new type of printer can be added to the system without directly modifying the Printer class.

Liskov Substitution says the behaviour of subclasses should be same as parent classes, the example below is a violation of this principle because the BrowserPrinter throws an exception while the other subclasses have different behaviours (don’t throw exceptions)

Class Printer {
    Function printBookPage (PrinterInterface printer) :
        printer.print() ;
Interface PrinterInterface {
    Function print() ;

Class BasePrinter implements PrinterInterface {
    Function print() :
        return  “print out” ;

Class HtmlPrinter extends BasePrinter {
    Function print() :
        return  “html print out” ;

Class BrowserPrinter extends BasePrinter {
    Function print() :
        If (internet explorer) {
           Throw IHateInternetExplorerException();
        return  “print browser page” ;

Interface Segregation says that an interface should represent one discrete cohesive behaviour, the interface should be created in a such a way that all methods must be implemented by the concrete class using the interface.

The PrinterInterface below isn’t cohesive enough in this case because not all printers use ink, therefore, it doesn’t make sense to force a POS (thermal) printer to implement this interface. A way to fix this will be to create an InkableInterface and extract methods that printers using ink only will need.

Interface PrinterInterface {

    print() ;


Class POSPrinter implements PrinterInterface {
    Function print() :
        return  “POS print out” ;

    Function removeInk()
        Throw new doesNotHaveInkException;

Interface InkableInterface() {
    Function removeInk();

Dependency Inversion says that a high level module should not depend on a low level module, both should depend on abstractions (interfaces)

We have to change the implementation details of the Library class each time we want to use a different printer in the first implementation. The way to solve this problem will be to depend on an abstraction (interface) in Library class and this allows us to change the printer type without modifying the internal implementation in the Library class as shown in the second implementation.

A WRONG way to do it:

Class Library {
    HtmlPrinter printer = new HtmlPrinter;
    Books[] bookPages;

    Function printBook (Books[] bookPages) :
        Foreach (bookPage) {
            This.printer.print(currentPage) ;

The RIGHT way to do it.

Class Library {
    PrinterInterface printer;
    Books[] bookPages;

    ClassConstructor (PrinterInterface printer) {
        This.printer = printer;

    Function printBook (Books[] bookPages) :
        Foreach (bookPage) {
           This.printer.print(currentPage) ;

DataStructures Series #14 – The B Tree Data-structure

Oak Tree Png, Vector, PSD, and Clipart With Transparent Background ...
Image source:

The B Tree is a self-balancing tree data-structure that maintains sorted data and allows searches, sequential access, insertions and deletions in logarithm time. The B Tree is similar to a binary tree except that a node isn’t restricted to having just two children.

Every non-leaf node in a B-Tree is structured to have all its children keys greater than the key before it and less than the key after it as shown in the sample B-Tree image below.

A B-Tree is characterised by its order. A B-Tree of order m is a tree which satisfies the following properties:

  • Every node has at most m children.
  • Every non-leaf node (except root) has at least m/2 child nodes.
  • The root has at least two children if it is non-leaf node.
  • A non-leaf node with k children contains k-1 keys.
  • All leaves appear in the same level and carry no information.

Illustration with the B-Tree below having a degree 5.

A example of a B-Tree data-structure
The inner circles with numbers represent the node keys while the green dots represent the children nodes.

Every node has at most m children

All nodes in the B-Tree diagram have the capacity to accommodate 5 children nodes (the green dots)

Every non-leaf node (except root) has at least m/2 child nodes

All nodes shown above are either root or leaf nodes, in a case where there are nodes in between, they must all have at least m/2 child nodes, in this case, inserting a node in between in the example above must have at least 2 (5/2) nodes.

The root has at least two children if it is a non-leaf node.

If the root is a non-leaf node as shown in the B-Tree diagram above, then it must have at least two children nodes.

A non-leaf node with k children contains k-1 keys.

As seen in the B-Tree diagram above, the green dots are the children nodes and every node has (no_of_children_nodes – 1) keys e.g the root node having 3 children (green dots) has 2 keys. This means the max number of keys that can be present in any given node in the B-Tree above is 4 (max_no_of_children – 1).

All leaves appear in the same level and carry no information.

All leaves are on the same level. This maintains the balanced property to ensure operations on the B-Tree always happen in O(log n) time complexity.

Operations on a B-Tree

  • Search
  • Insert
  • Delete


Similar to the implementation for the Binary Search Tree. We start from the root node, go through the keys and compare with the search key until we find a node greater than or equal to the key, if the key is found, we return the node attached to it otherwise we . Otherwise we visit the key’s node children and repeat the process until we find the exact key.


  • Find the leaf node where the item should be inserted
  • If the leaf node can accommodate another item (It has no more than m-1 items), insert the item into the correct location in the node.
  • If the leaf node is full, split the node in two, with the smaller half of the items in one node and the larger half in the other. Promote the median item to the parent node. If the parent node is full, split and repeat process

There is a graphical explanation of how this is done in the video below:


Deletion is similar to the insert process.

Real life Applications

  • Databases
  • Filesystems


My Poems

I saw many
but couldn’t choose any
not because I didn’t have a penny
I couldn’t project any would fit for a nanny
not to talk of a mummy
because a mummy builds a home
and if this is not a poem
I don’t know what is

On Finding a partner

To get fit
is no small feat
You have to be ready physically
but most importantly

On keeping fit


Home Is Our Happy Place Wall Quotes™ Decal |
Image source:

It is important to know the difference between a house and a home, a house is a physical place while a home is a psychological place, your home should be your happy place.

How I plan to build my home:

  • Practice Self Love:
    • Practice and master self love, know one will love you if you don’t love yourself. Let it be independent of others.

  • Finding a Partner, Choose from friends:
    • Someone who exhibits self-love also
    • Someone who listens to others worries/challenges without being judgemental.
    • Someone who understands that people don’t remain constant, they change as time passes, and will be there to witness these changes as a support figure.
    • Someone who is empathetic and interacts with others with empathy.
    • Someone who is a patient listener that gives full attention when others are communicating and asks follow up questions that shows full interest. 
    • Someone who tries to understand others beyond verbal communication.
    • Someone who is emotionally attracted to you and vice versa.

  • Making the house a home:
    • Stay to support each other on anything and everything, treat each other as equal, no one should be bigger than doing some part of the house chores.
    • Lay good examples for kids coming. Eating habits, choice of language etc.
    • Set aside time for family and stick to it as much as possible.
    • Raise kids to be independent and confident together.
    • Create memories together, capture some also digitally if possible, at the end this is all that matters. Memories.
    • Enjoy your old age with your partner when your kids are independent and keep the home warm for them to return with their own families.

Fundamental Computer Abstractions.

It is all abstractions built on top of each other.

Gestalt Abstraction
Image source:

First, there was electricity.

And then switch devices were built to receive electric signals and make them exist in two states: on or off

Now, we know a switch device can only represent two states on or off (binary)

More states can be represented by combining multiple switch devices:

- 2 Switch devices together can represent 4 states
- 3 Switch devices together can represent 8 states
and so on...

In summary, n switch devices can represent 2ⁿ states.

Then the single information was termed to be a bit.

A 1 bit machine – can store two pieces of information

A 2 bit machine – can store 4 pieces of information

and so on…

And then the naming system for bits was created:

4 bits – 1 nibble

8 bits – 1 byte

Early computers were 8 bit computers, this meant they could process 8 bits (1 byte) at once.

Total possible states with 8 bits/1 byte is  2⁸ = 256

The next abstraction was to make meaning of these binary format for human interaction and ASCII (American standard code for information interchange) was born, ASCII mapped alphabets, numbers, special and control characters to the last 7 bits of the byte. The first mapping being 00000000 – NULL and the last value being 01111111 was mapped to – DELETE control character.

Characters – cslib
Image source:

More and More Abstractions…

One of many other important abstractions created was the OSI model or IP model that enabled computers communicate easily with each other, this eventually gave birth to what we call the INTERNET today.


MySQL – Implicit Type Conversion in Expressions

SQL's Implicit Type Conversion - Adam Johnson
Image source:

When an operator is used with operands with different types in MySQL queries, a type conversion occurs to make the operands compatible. 

The following rules describe how conversion occurs for comparisons operations:

1. If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.


Table Name – people 

SELECT *  FROM people WHERE lastname = NULL; will return no records as stated above because it returns NULL. The NULL-safe equality comparison operator can be used to compare null operands : SELECT * FROM people WHERE lastname <=> NULL; will return the record having firstname Arthur.  

2. If both arguments in a comparison operation are strings, they are compared as strings.

3. If both arguments in a comparison operation are integers, they are compared as integers.

4. If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. 


Table Name – people

SELECT date_of_birth from people where date_of_birth = 100; returns the record having firstname Alice.

SELECT date_of_birth from people where date_of_birth = 1; returns the record having firstname Bob.

5. If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal, integer or string value. 


Table Name – people

SELECT * from people WHERE account_balance = ?

  • ‘100.0’ or ‘100’ or ‘100.000’
  • 100
  • 100.00

 All return the record having firstname Alice.

6. In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers


Table Name – people

SELECT * from people WHERE bonus_code = 8; returns the record with firstname Alice. The string and integer are both converted to floating numbers before comparisons take place.

In summary, always try to compare type for type in MySQL to avoid unexpected results.


Finances – Monthly Spending

11 Manageable Tips for Healthy Finances | SUCCESS
Image source:

In the past, I spent money how and when I felt like. I had no compass guiding how I spent money. In retrospect, this approach I would say isn’t sustainable especially when you start earning quite a lot of money.

I read a book that changed how I managed my expenses and I am really glad I did.

Here is a break down of how my expenses are being managed

I invest about 25% of my net salary into long/short term projects that will enable grow wealth on the long run. I divide the rest of the money as follows:

  • Savings (15%) : Savings for sudden/emergencies expenses e.g surgery for self or a family member etc.
  • Groceries (7%) : Used to purchase groceries and food stuff for the month.
  • Tfare (10%) : Used in moving around/ fuelling fixing your car.
  • Restaurants (5%) : Used to occasionaly eat out in restaurants.
  • Health and Welfare (5%) : Get drugs, books etc.
  • Entertainment (6%) : Netflx, Saving for Vacations etc fall in this category.
  • Personal Care (4%) : Body/Hair Cream, Spray etc. fall in this category.
  • Clothings (6%) : Clothes, Shoes etc. fall in this category.
  • Bills + Utilities + Housing (30%) : House rents, electric, water bills.
  • Givings + Dependencies (12%) : Support friends and family.

Most times, I don’t get to use all of the money in a month so I save the rest in a reserve space to use for other things in future.