Converting an ER diagram with 2 relationships between 2 entities to a RM Schema

Question

I want to convert an ER diagram to RM Schema but I have a problem. I have 2 entities which have 2 relationships with each other (team and player entities). what shall I do? The ER diagram is like this:

Should I create a separate table for each relationship?

I tried to approach to the problem in a way that a player can be a member of a team or a team can have only one leader or captain.

Considering what is said above, I did this:

create table team (
    team_name varchar(30) 
        primary key, 
    captain_name varchar(50) 
        foreign key references player(name)
    );

create table player (
    name varchar(50) 
        primary key, 
    team_name varchar(30) 
        foreign key references team(team_name)
    );

Is this approach a correct one or I had to create more than two tables?

Answer 1

Is this approach a correct one or I had to create more than two tables?

For the specific question that you ask, that approach is correct, but it is not Relational (noting the tag), and therefore it lacks proper or expected constraints.

I want to convert an ER diagram to RM Schema

The short answer is, you can't. Of course there will be problems.

• When modelling at the Entity-Relation level, which is early stages, there is a limit to what can be done. Eventually you have to progress to modelling at the Table-Key-Attribute level, where far more properties for each item can be determined and defined. Although that first step might be called "conversion", it is by no means complete enough to implement (implied by "schema").

• ER modelling is really primitive, it is not used for Relational Modelling in the real world.

  • The proper method for Relational Modelling is IDEF1X, available since 1984, and as a Standard since 1993. One can progress through the same stages of Table; Table-Key; Table-Key-Attribute. Relational Modelling has the full set of Relational articles, such as Independent vs Dependent tables; proper handling of composite Keys; Identifying vs Non-Identifying relations; etc, which ER modelling is totally ignorant of.

  • It is pretty sad that Relational modelling is not being taught, that the primitive pre-Relational method ER modelling is taught instead.

  • One cannot go from the ER level to the resolved Relational modelling level (implied by "RM Schema"), because it has not the properties that Relational tables have, that can be defined in Relational Modelling.

  • One has to go from final ER level to early Relational Modelling level, and then progress to fully resolved, in order to achieve Relational Schema level.

  • If Relational Modelling is used from the outset, the "conversion" step from one modelling method to the other is eliminated, and the limitations of ER modelling are not encountered. The end result of that, the fully resolved model, is a Relational Schema.

Entity Relation • Unconstrained

First let me say, it is excellent that you are using meaningful Keys, which are logical and therefore Relational.

• That is above and beyond the 1960's Record Filing Systems that are promoted and marketed as "relational" by the "theoreticians". Such primitive systems are typified by a Record ID on each File, declared as a "key", which confuses the hell out of anyone because it has none of the properties of a Key, let alone a Relational Key.

However, you do not yet have the full Relational context, you are not using composite Keys, and thus your model lacks the Relational Integrity; Relational Power; and Relational Speed that models that conform to Dr E F Codd's Relational Model have. Stated otherwise, you may not know what is possible in a Relational database, or what the essential requirements of one are.

You have this:

That has various problems, which are typical of the primitive RFS that you are being taught. Such as:

• The Captain assigned to a Team is not constrained to that Team (it allows any Player to be a Captain of any team)

• Player is definitely Dependent, a Player does not exist except for his participation in a Team (it allows Independent Players).

• All the relationships are Non-Identifying (dashed line), typical of the fragmented systems in which every table is falsely declared as "independent".

Entity Relation • Relational

If we elevate that to Relational:

• Team is Independent

  • Team PK is ( Team )

• Player is Dependent on Team

  • Player PK is ( Team, Player )
  • the relation
    Team consists of 0-to-n Players
    is Identifying (solid line)

• Now, the FK in Team for the Captain is the referenced PK in Player ( Team, Player )

  • in which the Team is the Team PK

• The relation
  Player captains 0-or-1 Team (as opposed to the unconstrained 0-to-n)
  is now affected, because the Payer belongs to just one Team

DDL

Again, it is too early to create tables, but since you are contemplating that:

CREATE TABLE team (
    team     CHAR(30)  NOT NULL, 
    captain  CHAR(50)  NOT NULL, 
    CONSTRAINT pk
        PRIMARY KEY ( name ),
    CONSTRAINT  captains 
        FOREIGN KEY     ( team, captain )
        REFERENCES team ( team, player )
    )

CREATE TABLE player (
    team    CHAR(30)  NOT NULL,
    player  CHAR(50)  NOT NULL,
    CONSTRAINT pk 
        PRIMARY KEY ( team, player ),
    CONSTRAINT consists_of 
        FOREIGN KEY     ( team )
        REFERENCES team ( team )
    )

• While a column name such as name is correct for an attribute, it is not correct for an Identifier, which should be named for the thing it Identifies, such as team, player, etc.
  • if table team has an attribute name, separate to the short Identifier team, certainly that would be named name.

Notation

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

• My IDEF1X Introduction is essential reading for beginners

---

Comments

_{The basic idea is that ... each diamond gets a table.}

Nonsense.

At that stage of ER modelling, each diamond represents an unresolved relationship, not a table. There is no "conversion", it is not a defined step (it is simply the point when the person promoting ER modelling has to switch to a real modelling method, precisely because ER modelling is severely limited). The purpose of modelling, regardless of whether one uses ERD; or IDEF1X; or coloured beads, is progressive understanding of the articles, ie. to progress from unresolved to resolved.

It is an intellectual process, not a clerical one.

Therefore, at that stage of ER modelling (your diagram), in order to progress to the next stage, each diamond needs to be resolved. That is, the ER diagram is not even complete within the ER modelling context (again, nowhere near ready for implementation, or "conversion" to a Relational Modelling method).

• The first thing is, each diamond needs to be determined and named (an unnamed diamond proves it is not resolved). That would be a good start re what the heck it actually is.

  • the unresolved relationship could be confirmed as a relationship, resolved. The name would be a VerbPhrase, read from the parent (1) to the child (n).
  • the unresolved relationship could become a table. The name would be a Noun because it has become a thing, and things are named by nouns.
  • Here is what your ERD looks like, when remaining at that stage, and progressing it (just the diamonds named, not yet resolved), check it out for yourself Progressed ERD.

• Each many-to-many relationship, eg:
  a Fan follows 0-to-n Players, and
  a Player is followed by 0-to-n Fans
  remains an n-to-n relationship

  • it is implemented (meaning later, at the point where the model is rock solid and ready for implementation) as an Associative Table PlayerFan.
  • At the point you are at in the modelling, it remains an n-to-n relationship and the diamond is removed, signifying resolution.

• Each 1-to-many relationship, eg:
  Each Team consists of 0-to-n Players
  always remains a relationship, not a table. The diamond is removed.

• Sure, a 1-to-many relationship may progress to a table ... but that is a result of further modelling, several iterations later, taking the other items in the model into account, not an automatic "conversion" at this point.

_{Which is why we do not use ER modelling in the real world, we use something that is designed for Relational modelling, that handles Relational features such as Keys with ease, eliminates "conversion" and the attendant nonsense.}