You can achieve a tilt shift lens effect with any view camera, but it is not limited to doing stuff like this. You can do weird stuff with it like for example take a picture in front of a mirror without the camera operator reflecting in a mirror. This is achieved by a horizontal shift of lens and adjusting for perspective correction. The whole camera is basically a projection manipulation device. Unlike in a regular camera where all the planes are fixed and parallel to each other, in a view camera you can 'select' parts of the image you want to be in focus, that are not restricted to be planes parallel to the imaging plane. There are many applications for this in architecture photography for example, to make parallel lines parallel, instead of converging at infinity (effectively making it ,I think, an orthographic projection), etc. but there is also a huge quality step up over a small format cameras purely due to the size of the lens and the size of the imaging plane. It takes a bit of a time to get used to all the weird effects and to visualize the planes in 3D and their intersection points to get the effects you want but it's not that difficult.
The sinar cameras are just well made cameras with extremely precise knobs and hinges, etc. so that it feels more like operating a microscope and not a camera. Once set in place, things stay in place and don't move, etc.