How do databases physically store data on a filesystem?

The answer to this question is both database dependent and implementation dependent. Here are some examples of how data can be stored:

• As a single file per database. (This is the default for SQL Server.)
• Using a separate file system manager, which could be the operating system. (MySQL has several options, with names like InnoDB.)
• Using separate files for each table. (If we consider Access a database.)
• As multiple physical files, spread across multiple file systems, but represented as a single "file". (HIVE, for instance, that uses a parallel file system to store the data.)

However, these are the default configurations. Real databases typically let you split the data among multiple physical devices. SQL Server and MySQL call this partitions. Oracle calls this table spaces. These are typically set up by knowledgeable DBAs who understand the performance requirements of the system.

The final questions are easy to answer, though. Most databases give you the option of either growing the databases as space is needed or giving the database a fixed (or fixed maximum) size. I have not encountered a database engine that will split the underlying data into multiple files automatically, although it is possible that newer column oriented databases (such as Vertica) do something similar.