1.) Does having both a Derived Key (“id”) and Natural Keys (“order_no”, “product_no”) invalidate 3NF?
Not really, because 3NF is not about how you create the PK, it is about how the rest of the data depends on the PK.
Or as Codd put it: “Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.”
Your surrogate key would be generated to be unique in the table so this table itself makes the id dependant on the candidate key of order_no+product_no.
In fact, your id would be an orderline_no, which is something you want as way for your customers to reference in their order.
2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?
Composite keys are just keys, they change nothing about the first three NFs.
3.) Is there anything that would prevent this table from being in 3NF?
Not as long as you put your constraints in place properly.
In fact, without the id column joining requires fun queries like:
SELECT * FROM ORDER_DETAILS INNER JOIN othertable ON (ORDER_DETAILS.order_no, ORDER_DETAILS.product_no) = (othertable.product_no, othertable.product_no);
As a sidenote to make your life easier:
Don’t use the nondescriptive name “id” for the surrogate key. The reason is that many tables will get this ‘id’ column and when you join them, the result will get several ‘id’ columns that you cannot tell apart in your application. You can work around that by aliassing the columns in the queries (SELECT orderdetails.id AS orderdetail_id), but you can imagine how that’s going to work; you’ll forget aliases, you’ll alias the wrong id’s, you’ll use different aliasses, poop will fly. But the simplest argument is that if you are going to provide a different name for the id colujmns in just about every query, why not just name the column so that you don’t have to alias it in the first place?