Its my scribble for understanding clearly what i learned from googling.
When you install the WSS 3.0 or Microsoft Office Sharepoint server 2007,WSS configures the IIS server to forward all requests regardless of file[such as HTML,ASPX or other web supported files]and content types[ such as .doc,.docx,.xls,ppt or.pdf or video streaming files] using the "Virtual Path Provider component".
In traditional web application ASP.NET framework process the file system[.aspx] alone.But in WSS or MOSS handles this in different way using Virtual Path Provider.
All site details and file name will be stored in MS-SQL Database or Windows Internal Database.WSS or MOSS fetch these details from the SQL Database.
We can deal with the database using SPContentDatabase object from the Microsoft.Sharepoint namespace to manipulate.
When you create the site in WSS 3.0,all site details,file name,contents such as webparts and context [Admin or user] will be stored in SQL Database.When accessing this site WSS serves from the SQL DB.