Why do the pipes make a very loud vibrating noise when I turn on sink?

This is called water hammer. It is caused by rattling pipes or by the jumpers inside the tap which rattle.