For database development you can choose to have as many of the same environments you'd normally use for application software development. Most people probably don't have all six separate environments you've listed above (even with regular application software development) but it's pretty common to minimally have a DEV, STAGING, and PROD environment. Some people opt for provisioning a UAT environment as well.
- Am I correct on the above database development stages?
More or less that's correct. The only minor thing I'll correct is the version in DEV is always greater than or equal to the version in PROD. Naturally if a developer makes a change to the database in DEV, it is now a newer version than the PROD database. Sure, that change can be undone in DEV, but DEV should never be a version older than PROD, just by the very fact that changes should only be made in DEV first, then deployed to any intermediary environments, and finally deployed to PROD last.
- Is there an equivalent of UAT/Staging/Demo in database development?
Yes, those are the equivalents. Any of those are acceptable environments to have, if you decide on them in your deployment workflow.
- If point 1 is correct, how do people create/work on the dev/test database and ultimately pushing it to prod database?
There's various ways to manually and automatically manage deployments and refreshes between environments. It just depends on your business requirements and the database system you're using.
Currently I support a DEV, STAGING, and PROD environment. My workflow is to make changes in DEV and track which objects I've changed. When I'm ready to move the current version of DEV up to STAGING, I run a set of scripts that refresh STAGING from the latest backup of the PROD database. Those scripts also make appropriate data obfuscations and changes that are specific to the STAGING environment. (The scripts are all part of a database job I've created so I only have to push one button to refresh STAGING from PROD.)
After STAGING is refreshed from PROD, I then use a schema migration tool (called SQL Examiner) to compare the DEV and STAGING database for differences and automatically generate the migration scripts from DEV to STAGING.
I then run these scripts in my STAGING environment and hand off the correlating STAGING application to the appropriate users for testing. Once they accept the changes are PROD ready, I lock those scripts down in source control so they are ready for the day we deploy to PROD. At this point, further changes can be made in DEV without worry of them colliding with the locked version that's ready for PROD. They'll all be part of future release candidates.
Then when it's time to deploy to PROD, we run the exact same scripts in the same manner that we ran in our STAGING environment. Now PROD is caught up to that version from DEV.
The key in all of this is the STAGING environment which is a handshake between DEV and PROD. It is one that can be updated from either direction with the least amount of care. It is continually maintained by either a refresh from the PROD environment and / or a deployment from the DEV environment. This creates a good workflow of ensuring proper version migration and testing across all environments.
