The question is comprised of several related (but ultimately separate) questions.
using it in the URL should be okay (is this considered "exposing"?)
"Exposing" is defined as "letting the outside world know". A URL is specifically to be used by the outside world to access your resource, so that is indeed exposing the value.
The main takeaway from exposing a value is that you require your consumer to know the value, and when your consumer knows this value, you can't just up and change it without having to coordinate that with the consumer. That can be a cumbersome process and thus should be avoided.
But my professor insisted that I should use [tablename]_ID, which I didn't even know why anymore because my professor just keep saying that the people who see the design won't know which ID is for which table. Isn't that the point of ERD drawing?
For an enterprise grade application, you're not going to be able to remember every field and its name and purpose, nor is it going to be easy to fleece through the ERD every time you want to use the name of a field/column.
Think of it this way: just because you have a thesaurus and dictionary by your side doesn't mean that every sentence I write (conveying the same message) will be exactly as easy to understand as every other sentence I could've written. It's still a lot more efficient if I use language that is immediately understandable without requiring you to dig into the documentation (thesaurus/dictionary).
To the same effect, using [table name]_[PK column name]
as the FK column name immediately telegraphs which PK this FK is referencing.
Note that I'm ignoring the underscore. It's a holy war that I don't want to stake a claim in. I tend not to use underscores but your mileage may vary.
That's not a hard and fast rule, but it's a convention that makes developers' lives significantly easier.
using an auto-increment as primary key is a bad practice because something like german-tank problem
You're not wrong, but the german-tank problem isn't particularly relevant for your average application. And it can still be easily avoided using a non-sequential type such as a GUID - which is advisable over an integer for several reasons, e.g. avoiding collisions in distributed systems. You did already touch on this:
I also argued that using auto-increment should be okay for a project at a scale that won't need databases merger (which usually when auto-increment can be a problem)
...but you've glossed over the part where you can't know for a fact that just because an application doesn't run distributed today, it's not going to run distributed tomorrow.
If you already use GUIDs from the get go, you application can be scaled much more efficiently than if you started using ints and now have to refactor the codebase to account for the collisions from your distributed system.
Software development entails making reasonable judgment calls about the possibility of something changing and the cost (effort/time/technical debt) of accounting for it (or ignoring it). The ability to do so comes with experience, and based on you glossing over this I would make an educated guess that you don't have a lot of practical experience in a non-academic environment yet.
My professor insisted that a primary key (natural or surrogate) should not ever be exposed, even in the URL
Yes, though it does bear mentioning that the scope of an application matters a lot here. Effort of implementation is always something that needs to be considered, and whether a high-effort implementation is worth implementing very much depends on the context of the application you're going to be implementing it in.
But it makes sense. Suppose that you have a web shop which has a history of orders. You've told your customer that their order number is 12345. That reference number shouldn't change, ever. Supposing you drop and recreate your database (for whatever reason), then the PK may be different, but the order number still shouldn't have changed (because otherwise your customer can't refer to the order anymore). The only logical conclusion here is that the order number is not the PK (and vice versa).
That being said, it's perfectly possible that in some systems, even though you've separated PK and identifier, that these two will always contain the same value simply because you haven't (yet) hit on an event that would cause these two values to go out of sync when adding new entries.
I argued that the table, for example, USER table, doesn't have natural key by design because it doesn't store unique stuff like email, and using it in the URL should be okay (is this considered "exposing"?) because there is authorization step to check if the user is authorized to access the resource.
Authorization is not the same as authentication!
- Authentication = who is this? It's Bob.
- Authorization = is Bob allowed to see this data? Yes.
Authorization can only occur after authentication, but that doesn't mean that you should lump them into a single blob, which is what your "because there is authorization step" comment is suggesting you're doing right now.
To authenticate a user, you must first identify them, and to identify them, you must be able to refer to the user you claim to identify as. In short: it all starts by referencing a user.
The same logic applies as above with the web shop example: if you refer to a user by their PK, then if you drop and recreate the database, everyone's PK may have changed, and that's going to be a costly affair to fix.
Even big software like Oracle use auto-increment (sequence).
Do not assume that just because something exists, it's therefore good. Especially in the field of enterprise software, backwards compatibility is a main feature (and Oracle especially touts their backward compatibility). This means that you're also going to see outdated features because they are kept in for compatibility reasons.
I'm not saying that int PKs are wholly outdated, but I am countering your suggesting that just because it exists it must mean that it's the right way to go.