I'm a web developer looking to leverage more of the power of my RDBMS. I'm currently learning about normalization of data and database design.
In the book that I am reading, we started off with one table that contained information about students, teachers, tests, grades, etc, and broke that down into separate tables for each entity.
However, I'm looking at the test table (see picture below), and I am wondering if this table also violates a few design principles.
In particular, I think that the
Test column violates the "no redundant data" principle, and the "no partial dependencies principle". (On this second point, I am assuming that the primary key for this table is a composite key of TestID and TeacherID, in which case the Test column depends only on the TestID part of the primary key).
What I would do in this case is further break up the test table into a
Tests table containing the a
TestID as a primary key,
Name as the name of the test, and
TotalPoints. Then, I would create a
AdministeredTests table that contains a
TeacherId, and a
Date, and the primary key would be a composite of
Would I be on the correct track here? I'm asking because the author also shows the following table:
Again, this suffers from redundant data (although it has no partial or transitive dependencies). Based on my current understanding, I think the author simply has not introduced join tables yet, hence the reason why normalization "stops" at a certain point. Also, the author is quick to point out: "...we presented one possible database design as an illustration of how to normalize data. In truth, many possibilities exist for designing this database." Just wondering the possibility I presented here is reasonable.