Your second example is indeed denormalized, and the original tables are now redundant – but this is not the only way to denormalize data, and I would venture to say that cases like this are rare. Denormalization is a performance optimization, which means that you do it because you have performance issues. These issues are different from case to case, so the ways to solve them also differ.
Keep in mind that denormalization has a cost. Updating the name of a school is trivial in your first example, but costly in the second one.
I don't think there's much to be gained by putting the
school_name column in
School_has_Student - how often do you look up the name of a school, given a student, in bulk? What kind of volume will that type of query reasonably reach? The opposite (looking up the names of all students at a school) is more likely to be taxing, so keeping
student_name might make sense. Now
Student is redundant, but
School is not.
Another example of denormalizing the same original database is to add a
number_of_students column to the
School table. This optimizes for a specific type of questions ("How many students does this school have?", "Which school has the most students?", etc.) by removing the need to look at multiple rows in the
School_has_Student table, but all tables are still necessary for other queries.
If students also had genders, you would have to keep the
Student table since those are not included in the
School_has_Student table. You might also keep the gender ratio in the
School table (another denormalization), and now you have no redundant tables.
You can remove redundant tables, but you're not likely to have them.