Does turning on MySQL server turn my local computer into a server and stores databases in my computer when I create them using MySQL ?
Basically yes. Please consider that you are using server with two distinct meanings here. When you talk of your MySQL server which you turn on, you mean a process running in background, listening and fulfilling requests. When you say turn my local computer into a server you mean a computer on which such a process runs. However, you can decide which other computers can talk to your server process. Your local MySQL clients will access it regardless, but it is possible that your MySQL server is configured by default not to listen to external requests, and it is the safest configuration. Opening up to other machines involves security concerns which will have to be addressed when the system goes into production on a real server, not now.
If so can I locate the database ?
Sure. But where it is depends on the package you have chosen to install MySQL. Have a look at this SO answer for different options.
/usr/local/var/mysql/ are usual locations.
Can I make a offline database using MySQL which can be located through 'Finder'?
Not clear what you are asking. You can locate your DB files through
Finder in the locations above, but they will be of little use without a running server process, so they are not what is usually meant by offline database such as
SQLite. There are several utilities capable of accessing your data in the files without a running server, but they are meant for backup or emergency recover and they won’t give you a fully functional RDBMS.