1

"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)

GATE CS 2008

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.

Improve this question
3
  • You can build a model of arbitrary complexity using a single table. The EAV model does precisely this. Unfortunately, this answer is not in the spirit of the question. The question explicitly says to build a relational model, which an EAV model is not. It becomes ambiguous, however, how far you can depart from normalization bwefore your model can no longer be considered a relational model. For example, is a star schema relational?
    – Walter Mitty
    Commented Aug 14, 2021 at 10:16
  • @WalterMitty I'm sorry but I haven't gotten to normalization in my course yet, so my knowledge on that is limited. My instructor emphasized that every attribute must be single valued when teaching mapping, and a quick google search tells me that's 1NF. Ramez's textbook covers normalization way after instructions on how to map an ER-diagram, so I guess his method only guarantees up to 1NF too. So if the goal is to minimize, should I only be mindful of not breaking 1NF?
    – Yueor
    Commented Aug 14, 2021 at 12:03
  • Normalization pertains to relations and tables, but not to entities and relationships. Sorry to throw that term at you.
    – Walter Mitty
    Commented Aug 14, 2021 at 15:41

1 Answer 1

Reset to default
0

you are confusing some parts,

a bridge table between to tables is needed to represent a m:n. see also here

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

Improve this answer
2
  • Thank you for your answer. I think the answer to the linked post is still with respect to a general relationship (judging from the diagrams that guy has made). Here is another guy saying many to many relationships can be handled with a single table, my teacher says so too, although I feel more inclined to believe we need 3 tables since a lot of reputable sources say so.
    – Yueor
    Commented Aug 14, 2021 at 9:24
  • in the video he uses first a comma seprated column and please see stackoverflow.com/questions/3653462/… what the consens is about that and second he uses a combined key tio circumvent the primary key restiction. the next thing that is implied is that we use normalized table. so that both possibilities are not used when discussiong general about design. So my point still stands five tables(goo thing i know what he uis talking about, as i don't speak
    – nbk
    Commented Aug 14, 2021 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.