My questions are:
1) Does this mSATA drive become my primary (C) drive where I would install the operating system, etc?
No, the mSATA drive is the cache drive for a HDD with an operating system installed on it.
So your HDD would be your C: drive.
2) If it does, is there a way to use the 240GB SSD as the OS drive and still get the benefit of the SMART and such?
No. Just use the SSD as your O/S drive and the HDD as a data/storage/backup drive.
3) Does it really speed up the other drives by a significant amount?
Using an mSATA drive to cache a HDD does speed up performance but it does not compare to a large SSD (240GB) with an O/S installed on it.
4) Is it worth the extra money?
No, forget about mSATA and just go for the 240GB SSD and 2.5TB HDD.