Composite Primary Keys example in MySQL

For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

Yes, you will need to insert all three of the columns to be completely valid. Otherwise the DB won't know what person or language you are trying to tie this record to.

Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

You could do this via an insert trigger, but it might not make any sense. So, let's say that you just entered a new language - say French. You shouldn't need to enter any values at all into the personLanguage table because your existing users might not want to get information in French. The same situation would be for creating a new person. You might have many languages. Most people won't speak most of the languages, so again, you wouldn't want to enter a record into the personLanguage table automatically.

As for updating the records in person and language, the KEYS shouldn't change. This is why you would do something like this. Once Bob or Alice is assigned a personId, they are that Id. Once French is assigned a langaugeId, it should always be that languageId.

How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

Well, this is the tricky question. If you are trying to get ALL the languages personId=1 speaks, the join is pretty easy.

select pl.personId, l.languageId, l.languageName   from personLanguage pl    join language l on l.languageId = pl.languageId    where pl.personId = 1

It gets more complicated if you are trying to figure out which language you should communicate with the person, since there is a chance that the person might not have any personLanguages defined. If you can accept null values, you can use an outer join, but you would want to define the query so that you only return a single language.

1. For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

   You can only insert values into personLanguage if there are existing keys in the referenced tables. This means you must populate person and language prior to inserting values into personLanguage. However, if you have a NULLable field, you could do this, but it would violate the unique composite key.

2. Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

   The constraint that you have specified (ON UPDATE CASCADE) means that when there is a change to a referenced value in either person or language it will automatically update those values in personLanguage. However, there cannot be a violation of of the PRIMARY KEY constraint on personLanguage.

3. How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

   Since this is a basic example, there wouldn't really be a need for this. In an extended form, you could use explicit JOINs to fetch data between the tables.

Just a few more thoughts...

Composite keys are generally used for referencing in tuples (or sets). This means that when you have a composite key (col1, col2) on table1, this references a composite key (col1, col2) on table2.