“Fundamentals of Database Systems” by Ramez Elmasri covers this topic extensively, I’ve gone through it and seemingly the rules mentioned aim at converting the ER-Model to a relational schema without any redundancy. I’m preparing for an entrance exam which has asked questions like “What will be the minimum number of tables for the following ER-Diagram” (I don’t want an explanation for this question, including it only to illustrate my problem)
I’m very confused about how to approach these problems since almost everyone’s opinion on this varies, some prioritize redundancy, some prioritize minimization. Like here, the top voted answer says we can merge a M:N relationship into one table if both sides have total participation, using the primary keys of both entities as a composite key, Ramez Elmasri/other sources on the other hand says you need three tables in a M:N relationship no matter what. I’m not sure if we can ignore redundancy, because I might as well put everything in a single table with an artificial key, and I’m not sure if I can ignore the task to minimize as the question asks me either, because maybe some fields being null is fine given that the question is focusing on minimization.
To rephrase my question, if I’m given an ER-Diagram, with a binary relationship, participation constraints, and some cardinality ratio, how should I proceed if I’m asked to do the job with minimum number of tables.
you are confusing some parts,
the linked post, says in bold letters self-refenrencng
But it is possible for a column in a table to relate to another column in the same table. This is called a self-referencing relationship.
they are a special case in relationships and are used for example in hierachical data model.
But in normal case you would use a bridge table for R1 and r2