Relational Schema to ER Diagram /Cardinalities difference

Question

If we have an ER diagram and we want to convert to a relational schema we follow a specific procedure (eg Elmashri& Navathe book). What is not clear to me is if there is a difference when there is a cardinality of 1:1 vs 1:N. How is this difference represented in the relational schema?

For example in the following figure from Elmashri if we focus on the relation between Department-Project that has a cardinality of 1:N we take the following relational schema. If the cardinality was 1:1 would there be a difference?

And to ask more directly: In the following figure, if somebody was giving me only the left part of it with the relational schema, how would I say if the 2 relations (in black and red circle) are 1:1 or 1:N?

Answer 1

Re mapping from ER & pseudo-ER to relational

That is a Chen style of ER diagram. Diamonds denote relation(ship)s/associations & have corresponding tables/relations. Lines denote participations & have corresponding FKs. Cardinalites are about diamonds ie relation(ship)s/associations ie tables/relations. They tell you certain things about what combinations of entities can participate in a relation(ship)/association.

Your textbook is Fundamentals of Database Systems 7th edition by Elmashri & Navathe. It follows the above usage & explains how to map X:Y:etc & explains how it mapped your example in particular. That DDL can arise from mapping the ERD to obvious "cross reference"/"relationship" tables then combining them. But your textbook just gives multiple ways to map X:Ys without clearly explaining how the shortcut ways arise from the obvious ways plus combining.

Eg: The table that is the value of query select Dnum, Pnum from PROJECT represents the binary relationship "for some Pname & Plocation, department Dnum controls project Pnum"--which is presumably true exactly when "department Dnum controls project Pnum"--which is relationship CONTROLS on a DEPARTMENT & a PROJECT. The ER diagram says that the corresponding binary relation is 1:N in Dnum:Pnum & N:1 in Pnum:Dnum. Because CONTROLS has that particular 1 in its cardinality, its obvious table can be combined with the PROJECT entity table into table PROJECT. If it were M:N then combining would have certain problems so your text gives a different mapping--but under the previous presumption it still could be. And if you did that or just used the obvious mapping then your relational design would be the same for both 1:N & M:N. 1:1 allows yet other combinations.

Different design & diagramming methods have different conventions for cardinalities & mapping to relations. Many so-called ER methods are not really ER in that they are trivially ER--everything is a (possibly associative) entity. All lines involve a 1 or 1-or-0 at one or both ends--because they are about certain implicit binary relationships & tables associated with FKs--like when obvious Chen tables are combined as above. Also there are look-here & look-away cardinalities & other conventions like explicit null FKs. All depends on what textbook/reference/method are you using.

Your DDL diagram "relation" is a FK--wrongly but ubiquitously called a "relationship" by pseudo-ER methods. You are not using "X:Y" meaningfully in the DDL diagram. You are using it more or less how a pseudo-ER diagram would label a FK. But the cardinality is about the relation(ship)/association represented by a certain projection of the FK's referencing table.

Re mapping from relational to ER & pseudo-ER

If you started from a Chen ER design & used only the obvious mappings then you would have tables 1:1 with entities & relationships & the entity tables would be the ones with no FKs. But your textbook has multiple mapping choices that involve combining ER diagram entity & relationship tables into other tables, in multiple ways, so the tables in the database don't tell you the diagram entities & relationships that they arose from.

Relational designs are more general than Chen ER designs--tables represent relations on 0 or more values. (Every superkey of every base table & query result identifies some entity.) So not all reasonable relational designs correspond to Chen ER designs. Whereas one benefit of the pseudo-ER methods is that they are really recording DDL and not distinguishing between entities & relationships. But if they do arise from entities & relationships that's not recorded in the design. So you can't map from such a relational/pseudo-ER design back to those entities & relationships.

You wouldn't know the cardinality from DDL constraints unless you put them in --which you should, preferably declaratively but otherwise triggers. FKs & CKs (via PKs & UNIQUE NOT NULL) are enough to express Chen cardinality constraints for binary relation(ship)s/associations, but not n-ary. Pseudo-ER methods may or may not address constraints beyond PKs & CKs. And Chen ER designs can have problem constraints that must be addressed through general relational design principles--so really they are only provisional. (And--unnecessary.)

What is the difference between an entity relationship model and a relational model?