3

MIS student, here. Learning about ERDs (entity relationship diagrams). ERDs are supposedly used for the purpose of later constructing a database. My question is this:

  • What will an entity (and, likewise, an entity's attributes) become upon implementing the database? For instance, will entities turn into rows and attributes into columns?
  • In fact, now that I consider it, I'd like to know how one implements associative entities from ERD to database form as well. Is there a difference between what a regular entity and an associative entity represent in terms of database terminology?

migrated from pm.stackexchange.com Oct 27 '18 at 11:29

This question came from our site for project managers.

3

When implementing an ERD as physical database the following mappings apply:

  • An entity becomes a table (set of rows)
  • A relationship becomes a foreign key.
  • Associative (many to many) relationships become a table with a foreign key to each associated table.

Generally the ERD shoud be in at least third normal form.

A few notes about relationships:

  • The (zero or) one side of the relationship is implemented as the primary key of the table.
  • The many side of the relationship is usually implemented with a foreign key index.
  • If the site opposite the many side range is zero to one, the foreign keys column(s) are nullable.
  • If the side opposite the many side range is one to one, the foreign key columns are not nullable.
  • If the side opposite the many side range is also zero or one to many, then a relationship table must be implemented.

Many ERD tools can be used to create the tables and indexes; or the DDL (Data Definition Language) to do so. They may also be able to create an ERD from a database. The best tools can update an existing database to match a modified ERD, or generate the DDL to modify the database.

  • 1
    I don't think the nullable vs. non-nullable part is correct. – ypercubeᵀᴹ Oct 27 '18 at 13:02
  • @ypercubeᵀᴹ Seems correct to me. If the business rules allow orphans, child records not assigned to a parent, then the foreign key value in that child table will be a NULL. Alternatively, you can create a specific parent row whose ID you assign as the foreign key on all the otherwise would-be-orphans. If your business rules forbid orphans, such as every invoice-line-item child must be assigned to an invoice parent, then the foreign key on invoice-line-item should be defined as NOT NULL. – Basil Bourque Oct 27 '18 at 18:00
  • But that is irrelevant to whether the relationship is 1 to 0-to-many or 1 to 1-to-many. – ypercubeᵀᴹ Oct 27 '18 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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