In the movie the death of Boromir is indeed one a very very tragic moment, perhaps the most tragic I ever seen in a movie (I don't often see tragic movies
). Yet I didn't experience it like that while reading the book.
I think the story of Túrin is the most tragic story Tolkien ever wrote, and the most tragic moment in it is probaly when Glaurung dies and the spell is destroyed, and Túrin and his sister discover they are married to eachother, which ultimately leeds to the suicide of Túrin..