Types of Joins in the relational Model (MySQL)

JOIN operation is a main concept in the relational model. If you don’t REALLY understand how it works, using this kind of databases is going to be difficult. If you’re looking for a simple definition “a join is a combination of tables that  links common information”.

But threre’s a problem, there are several join types each one with a different meaning. To make thing easier we’re going to focus the post  on MySQL. Other DBMS (DataBase Management Systems) might differ slightly so we’ll need to review documentation.

We’re going to  use this model:

Model Relacional - Relación 1:N

Relational Model – 1:N (One to Many) Relation

The previous image show a One to Many relation between clients and vehicles. Each client owns one or more vechiles and vehicles can be owned by one or zero client (suppose it comes from the scrap)

Table’s initial content:

Contenido inicial de la tabla clientes.

Clients

As we see, 9 clients from“00001” to “00010” (“00007” not included está)

Contenido inicial de la tabla vehículos.

Vehicles

16 vehicles but 2  with no owner and only  8 clients own a vehicles, client “00008”  doesn’t have any vehicle.

With this date we’ll review the following kind of joins:

  • INNER JOIN aka “normal join”
  • LEFT JOIN
  • LEFT JOIN with null values
  • RIGHT JOIN
  • RIGHT JOIN with null values
  • NATURAL JOIN
  • FULL OUTER JOIN
  • FULL OUTER JOIN with null values

INNER JOIN aka “normal join”

The result is:

Resultado del Inner Join o join normal

Resultado del Inner Join o join normal

In this case tables’ order around the JOIN clause doesn’t matter and the INNER is optional. What do we obtain?  14 rows, one for each vehicle with an owner. Both clients without a car and vehicles without an owner are not shown up.

I’m only showing codClient and plate fields beacuse a better post presentation reason.

LEFT JOIN

Let’s practice with two queries in order to explain it:

First query’s result:

Left Join de Clientes

Left Join with clients

Show all the clients, even those without a car and for those that own a car one row for car. As you can see client “00008”  doesn’t have any car.

Second query’s result:

Left Join con Vehículos

Left Join with vehicles

All the vehicles are shown up, even those without owner “0011 ABC”  and “5566 ABC”.

LEFT JOIN with NULL VALUES

First query’s result:

Left Join con Clientes y valores nulos

Left Join with clients and null values

Only cients with no vehicle. It also can be donde with a subquery.

Second query’s result:

Left Join con Vehículos y valores nulos

Left Join with vehicles and null values

Only cars with no owner.It also can be donde with a subquery.

RIGHT JOIN

It’s  LEFT JOIN analogous but in this case we get every row from the table on the right side of the join.

First query’s result:

Right Join con Vehículos

Right Join with vehicles

As we see we get every vehicle,even those with no owner. For those with an ownner, owner’s data if shown.

Second query’s result:

Right Join con Clientes

Right Join with clients

Every client (once for each vehicle owned) even those clients with no vehicles (“00008”).

RIGHT JOIN with null values

First query’s result:

Right Join con vehículos y valores nulos

Right Join with vehicles and null values

Only vehicles without owner (null values for client related fileds)

Second query’s result:

Right Join con clientes y valores nulos

Right Join with clients and null values

Only client with no car. (null values for vehicle related fields)

COMPARING LEFT JOIN AND RIGHT JOIN RESULTS IS VERY IMPORTANT. WE NEED TO SEE THE ANALOGY BETWEEN THEM.

NATURAL JOIN

Result:

Natural Join

Natural Join

It’s the same result as when INNER JOIN is used. NATURAL JOIN USES COLUMNS WITH THE SAME NAME IN BOTH TABLES. If not we may get unwanted results.  LEFT or RIGHT can also be added after NATURAL and before  JOIN.

OUTER JOIN

MySQL doesn’t support  FULL OUTER JOIN but it can be done using another type of query:

Result:

Outer Join

Outer Join

All the client even those with no vehicle, all the vehicles even those with no owner and one for row for each client-vehicle relation.

This alternative query can fail.Please read this post for further information.

FULL OUTER JOIN with NULL values

As in the previous case:

Result:

Full Outer Join con valores nulos

Full Outer Join with null values

Vehicles without owner and clients without vehicle.

From  WikiMedia Commons

For further info MySQL docs.

 

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

Understaning Foreign keys with MySQL

From WikiMedia Commons

Foreign Keys are a basic concept in the relational database model. In an easy way a foreign key is one or more fields that references fields from other table allowing to set relationships between them.

If we are looking for a formal definition we can use Wikipedia but for now we’ll be using an example:

Relational Model - One to Many RelationShip 1:N

Relational Model – One to Many RelationShip 1:N

This is a One To Many RelationShip 1:N among “clients” and “vehicles”. A client has one or many vehicles and a vehicle is owned by 0 or 1 clients (0 if taken from junk)

“ClientCod” it’s the foreign key in the table “vehicles” and references  “clientCod”  field in the table “clients”, being  this field the primary key in this table. Both fields, primary and foreign keys are used to establish the relationship between those two tables. We must take into account that every “clientCod” value in the “vehicles” table will be present in the “clients” table. This is called referencial integrity.

But, What happnes if we delete a client or if we update the “clientCod” field in the clients table? How does it affect “vehicles”?. In MySQL we have 5 options:

  • RESTRICT : The Default option. Neither delete nor update will be allowed if the a client’s  “clientCod”  already exists in the “vehicles” table.
  • NO ACTION: Like RESTRICT. Allowed for compatibility with the SQL standard.
  • CASCADE : If a client is removed all the client’s vehicles will also be removed and if I update a client’s “clientCod” this update will be propagetd to “vechicles” table.
  • SET NULL: If a client is deleted the  “clientCod” of this client’s vehicles will be set to NULL (this field must allow NULL values) the same if I update the “clientCod” field in the “clients” table
  • SET DEFAULT: Fo both queries, delete and update, a default value is placed in the “clientCod” field for the “vehicles” table.Not valid for InnoDB or  NDB tables.

Let’s see an example with  RESTRICT, NO ACTION , CASCADE y SET NULL:

SQL code for table creation:

We must choose one of the options showed between brackets.

Queries for the example:

Initial content for the tables:

Contenido de la tabla clientes

Clients

Contenido de la tabla vehículos

Vehicles

If I use  ON DELETE RESTRICT and  ON UPDATE RESTRICT (default options) or ON DELETE NO ACTION y ON UPDATE NO ACTION  I will get (in both cases) the following error:

If I use  ON DELETE CASCADE and  ON UPDATE CASCADE every vehicle owned by the client  “00007” will be deleted and in those vechiles with “clientCod” equal to “00008” this value will be updated to “11111”.

Tabla clientes con operaciones en cascada

Clients with CASCADE

Tabla vehículos con operaciones en cascada

Vehicles with CASCADE

If I use ON DELETE SET NULL and  ON UPDATE SET NULL the clients table will remain as the previous case but,  in the vehicles table the  “codClient” will be set to  NULL for those vechicles owned by the clients “00007” or “00008”.

Tabla vehículos con la opción SET NULL

Vehicles with SET NULL

Choosing one or other option is a design decision that must be carefully made  before the creation of the database.

Ihe following post we wll talk about another basic concept in the relational database model: “Different types of Join”

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

Do networking!!!!

Image from WikiMedia Commons

Computer guys are frecuently blamed for being unsociable. There are, of course, people from every kind, but I totally disagree with the previous sentence. We, as a community, plan a lot of events.

In person events, online events, paid events, free events, for sharing, for learning, competitions and challenges and many more. At the end, computer related communities are many and very active.

Taking part in one of these (at  least) should be a must and probably it will help you with your traning and (I’ve seen some cases) it can also help you to find a job.

You don’t have to search a lot. Sign in websites like  Eventbrite, Meetup or BetaBeers and look for groups closed to you and your interests. If you don’t find any… start one, every community had its beggining.

Do networking!!!

Let’s see if everyone takes note.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

Integrating UserVoice (Help Desk software) in your Android app.

NOTE: You need basic Android programming knowledge and a UserVoice account.

Soporte al usuario fácil para tu StartUp

Easy Help-Desk for your StartUp

When you are in the beginning of an StartUp adventure you have limited resources and, if possible, you always try to find low cost and effective tools

At Smartfarmr we used lot of these tools.One of the easier and more useful is UserVoice service.

UserVoice is a Help Desk Management app. The Web tool is easy and simple and they offer and SDK that allows a fast integration of the  support features into your Android App.

Firstly we must add jcenter to the Maven repo list.  File build.gradle in your app’s root dir (working with Android Studio).

Then add UserVoice SDK as a project dependency. File build.gradle in the app/ dir( working with Android Studio).

Once we’re done with the previous activities it’s easy to add the different support actions  to our App. We decided to create a main Support Activity with four buttons: Help, Forum, Contact and Suggestions.

If you click on a button, the UserVoice SDK shows a native UserVoice screen. You don’t need to care about anything else, the support is already integrated in your App.

Screenshot_2015-09-28-15-58-14 Screenshot_2015-09-28-15-58-04 Screenshot_2015-09-28-15-58-00

Further Info: https://github.com/uservoice/uservoice-android-sdk

 

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

GitHub at classroom

githublogoThis course my students MUST CREATE a GitHub account.

It’s not something new, GitHub is used in a lot of schools as a learning tool.

It’s not on a whim. GitHub is a standard and recruiters ask for your account url for coding positions.

It’s not because I want,  during the last few year most our students finally found a job and in most cases, although our degree is a Systems Administrations degree, the positions were web and code related. A public and  personal portfolio could motivate the students to make their best during the course and, for sure, will increase their chance to get a job.

We will be using this tool, at least, in the following subjects, MarkUp Languages and Web Apps Deployment

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

Bilingual Professional Training – Let’s see what we get

Bilingual programs in Education generate a full range of opinions, for and against.

Don’t want to enter any debate or analysis in this post, although in my personal opinion the “Bilingual” adjective is oversized, mostly because the programs are partial programs dedicated only to one part of the full course.

At my workplace, IES Triana, with a long a successful  tradition in this kind of programs, we are starting a bilingual professional degree in Computer Systems Administration during the 2015-2016 course.

We decided to join this project due to several causes:

  • Students, worried at first, finally recognized how much they needed the program and how much useful it would be to their training process and their carreer.
  • There are tons of free and great materials. It’s very easy to find what you want and it helps the teachers to stay 100% focused in the learning and training process.
  • In the IT world vocabulary, webs, forums, reference manual are, mostly, already in english.
  • Despite of the unemplyment rate in Spain we’re lucky. Globally speaking IT professionals are highly demanded. Being fluent in english increases a lot the chance of getting hired, here in Spain and abroad.
  • And, of course, the teachers are skilled enough, we hope 😉

The target is not to get “bilingual” students. We want to maximize the students exposition to the english language in order to achieve a professional fluency. We want them to be able to use almost any english reference manual or videotutorial. In the speaking level we want them to be able to hold an IT support position, a very demanded position.

There will be, of course, problems during the course, at least these two:

  • Conversation assistants are expected only once a month and with little IT related knowledge.
  • Students’ english level is unknown. We must be careful with the balance between the english level and the IT level. We don’t want an empty classroom by November.

At the end of the course I promise to post again explaining what happened. I will share materials and reflection at twitter hashtag twitter #asirtrianabilingue and at Diigo Groups Markup Languages and DataBases. 

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

Using CodeAcademy

Usando CodeAcademy

http://www.codeacademy.com

Every day I’m increasingly for changing teachers’ role at the XXIst century classroom. It’s very difficult, you have to change minds, including the the students’ ones, but step by step we must realize that the current classroom model has its own limits, both phisical and knowledge limits. There’s life and knowledge outside and we, as teachers, should push our students to find and walk through new paths.

At Computer Science field there are tons of free and quality sites and resources and I just had to show the students a starting point. I finally chose one of my favourites CodeAcademy.com, a website where you can learn how to code.

I decided to leave the students alone, no feedback, no questions, no problem solving if it was CodeAcademy related.There was only one rule “You must finish the full course before the given deadline”

And, how did the experiment go?:

On the positive side:

  • The students learnt by themselves.No teacher help nor interaction.
  • They finished  a long time before the deadline. The Badge Reward System seems very motivating.
  • Collaboration has ocurred naturally.
  • Thanks to the course later lessons have been easier for the students.

On the negative side:

  • Very easy courses. I’ve to go deeper at some points..
  • Content is not always curriculum related. But I don’t give a shit.
  • I decided to test CodeAcademy once I saw  the spanish translation but spanish translation is awful. I’ve offered CodeAcademy myself as a translator (no answer yet)

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

VoidBrains (SmartFarmr) winner of #HackathonEBE

Superganadero ganador del HackatonEBE

SmartFarmr  #HackathonEBE’s winner

As a @Voibrains’ member I had the chance to take part at #HackatonEBE , organized by @BetaBeersSVQ.

For those who don’t know what a #Hackathon is we can briefly describe it as a “meeting where people with different profiles (coders, marketing and business)  get together in order to create a product (usually a software product) about a fixed topic”

This time the starting point was a problem presented by the NGO @ayudaenaccion. They wanted to improve their members engagement with their sponsored childs. Don’t know for the rest of the people, but for us, working for a NGO makes things easier and inspiring

After the initial meeting teams were created. I was very lucky to be part of the same team as @manoloruiz @jescobeitor  and @Alejandro_Hall . We got to work soon and during friday’s evening we developed our idea “An semi-instant messagin application between NGO’s members and sponsored childs”.

Presentación de la Aplicación

Presentation

Saturday was a very busy day, from dawn to late night we spent the whole day coding and designing our proposal. We aimed to make the best of our skills to get the best possible product for the next day.

Everthing went fine on Sunday, @manoloruiz had created an amazing presentation and finally the juror decided that our project was the best. We won!!!!, and the level was really high.

You can get the project’s code from gitHub (remember that is just a working prototype)

Thanks again to everybody and thanks to the sponsors @comalis and  @ayudaenaccion

Thanks to the other team members again. Here’s a link to @maloruiz’s post (from Geekia) about the event.

Fotos con los organizadores del #HackatonEBE @BetabeersSVQ

#HackathonEBE’s organizers @BetabeersSVQ

Foto de los participantes, organizadores y patrocinadores de #HackatonEBE

Everybody at  #HackhatonEBE

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

Projects Launched

In the previous post I told you about my recently started adventure, with @marcis20,  of creating our own company. After a lot of work and debate we’re in the market wit our two  firsts products:

smarty_farmer_logo

SmartFarmr is a web app which main target is make livestock farms’ management easy. We are using  Zend Framework 2 and Doctrine 2. As soon as posible we will launch an Android mobile version.

logo_vinoshopping

Vinoshopping is an horizontal portal for selling wine online. With our experience background (@marcis20 is the master here) we give the client turn-key solutions. Anyone who wants to sell wine online must only care about their product, the rest is our job. This is a Prestashop based product.

Both projects have been developed  using agile methodolgies and with a Lean Start-Up approach.

 I’ll keep you updated explaining our hits and our mistakes.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

The adventure starts – VoidBrains

VoidBrains. Improving your business performance

VoidBrains. Improving your business performance

People must think I’m loosing my mind. “With lifelong job  and he has decided to become one of this fashionable things, an “enterpreneur”. I’m going to be part of VoidBrains.

The decision was easy. At the beggining it’s not a full time job, I’m going to be side by side with one of my friends who, at the same time, it’s one of the smartest guys I’ve eve met and, very important,at my current position there’s no possibility for a professional growth. All of these are very powerful reasons.

I’m coding again, researching again, reading a lot about start-ups and recovering the pleasue for almost forgotten activities.

I will try to blog about this adventure. If I have time. Wish me luck.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS