Durham have been relegated to Division Two of the County Championship after a remarkable collapse saw them slump to defeat to Yorkshire on the final day of the season.