It's a poor design where a site is allowed to throw up a dialog like that (also goes for the "this site wants to know your location! yes/no").
It should work like the popup blocker, just show a red cross somewhere, that some action was blocked, and if the user wants to, he can go and unblock it. The onus should be on the site owner to tell his user "if you want this additional functionality, please go and enable <this or that>".
Depends on your OS. For Chrome on macOS, it'll show a chrome-styled square box with an icon and a piece of text in the upper right hand corner, kinda like macOS notifications (but they look nothing like it). In general, web notifications (can) work the same way they do on your smart phone. They can arrive from an active tab as well as from a background service.
Safari (on macOS) will use the native macOS notification system to display notifications AFAIR. I have no idea what IE on Windows does.
JS service workers allow a JavaScript thread to run in the background while the browser chrome (the window) is closed. So, in theory, the browser doesn't need to be open. Also, notifications can be useful with an open browser too (or, multiple tabs with a tab in the background for example)
Visiting the page for the first time and immediately getting a popup is not one of them.