-1

I have to create an ER diagram based on a relational schema.

There is a table of players, and a table of zones. A player can 'live' in many zones, and each zone is owned by one or more players.

I've come up with this simple ER diagram but I'm not sure having relationships going each way is allowed?

http://img149.imageshack.us/i/84754821.png/

Cheers

Oded
  • 489,969
  • 99
  • 883
  • 1,009
Roger
  • 3,411
  • 5
  • 23
  • 22

3 Answers3

7

Yes, that is a perfectly good Entity Relation Diagram. (I am not responding as to whether it makes sense or not: you still need to resolve the Relations and Cardinality.)

Using the correct terms helps people understand exactly what you are discussing, and which level you are discussing. Loose talk results in much more volume in the discussion, and time wasted in clarifying what you meant by which term. Not good for productive technical endeavours.

  1. At this early stage, it is normal to model Entities and Relations (not Attributes), that's why it is called an ER diagram; we are nowhere near modelling the data. The Relations are relevant, and that's why you are detailing and evaluating their nature in the diamonds and Cardinality. The goal is to clarify the true Entities, and their Relations to each other. Many-to-many relations remain as relations. The ERD is purely Logical, there is no Physical.

  2. Once you have some confidence with that, that you have gotten the Entities and Relations right, you move onto a Data Model (which includes Attributes). Still at a Logical level, the n::n relations remain as relations.

    • As you progress, you may show further detail, such as Domain for each Attribute. That's the DataType, but at the Logical level, just as the terms are Entity = Table and Attribute = Column, Domain = DataType.
      .

  3. When you get to the Physical level, the Data Model has Tables; Columns; DataTypes.

    • And n::n Relations are manifested as the Associative Tables.
      .
  4. The idea is, as long as you are working through the prescribed steps, at (1), the content in the diamonds will determine (expose) if they need to be stored, and the diamond is thus promoted to an Entity; otherwise it remains a Relation.

There is a junction table called lives-in in the relational schema I've been given. However, I thought when mapping a relational schema [back] to an ER diagram a junction table becomes a relationship?

  • The Relational term is Associative table.

  • Yes. If it is a pure n::n Table (containing nothing but the two FKs to the PKs of the parent Tables), at the ERD level, which is Logical only, it is a Relation.

  • If it has Columns other than the two FKs, it is an Entity.

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • 1
    Should not perfect УК diagram be in IDEF1X? – Gennady Vanin Геннадий Ванин Dec 08 '10 at 18:22
  • 1
    @vgv8: Well yes. But this is Chen's ER technique (he **is** a giant, of Codd & Date's vintage), and the technique is taught formally at the tertiary level, although it is incomplete. Codd's work plus Chen's work was the basis for LDDT (Robert Brown) which was widely accepted and used in the 1980's; grew into IDEF1X; and became a NIST Standard in 1993. And yes, if an IDEF1X practitioner did this task, they would use that notation, not Chen's. I am answering OP's question in their context, not promoting anything else. This question is specific to modelling the Relation, only. – PerformanceDBA Dec 09 '10 at 22:13
  • 1
    Sorry, my keyboard converter cyriillizied the ER (Entity Relationship) to YK. I asked follow-up question in comments to http://stackoverflow.com/questions/4132044/name-database-design-notation-you-prefer-and-why/4140309#4140309 – Gennady Vanin Геннадий Ванин Dec 10 '10 at 12:11
0

Since there's a many-to-many relationship between [Players] and [Zones] you have to add a junction table (called for ex. [PlayersZones]). The notation itself is correct (Chen notation), though I prefer the Crow's Foot Notation.

Koen Rouwhorst
  • 81
  • 4
  • There is a junction table called lives_in in the relational schema I've been given. However, I thought when mapping a relational schema to an ER diagram a junction table becomes a relationship? – Roger Nov 30 '10 at 19:09
  • I'm not sure if it's required to add junction tables to your entity relationship diagram, it probably depends on level/notation type. I think one junction table would do the job. In that case you're allowed to save relationships in both directions ('lives in' - left to right and 'owned by' - right to left). – Koen Rouwhorst Nov 30 '10 at 19:25
  • Robert, you are correct. Many people use ER diagrams to express a relational model of the data. Strictly speaking, however, Peter Chen devised the ER model as an alternative to the relational model, not as an alternative way of diagramming the same model. I use different diagram tools for ER models and for relational models. – Walter Mitty Nov 30 '10 at 21:49
0

I am not able to see your images (blocked!) so I'll just try to describe the "correct" design. If a player living in a zone doesn't necessarily mean they own it, you should have four tables: 

PLAYER (playerid, <other fields>)
ZONE (zoneid, <other fields>
PLAYER_ZONE(playerid, lives_in_zoneid)
ZONE_OWNER (zoneid, owner_playerid)

Otherwise three tables would suffice.

Axn
  • 356
  • 4
  • 7