Diagram Conventions   «Prev  Next»

Lesson 8 Verbalizing an ER diagram
ObjectiveDescribe the process of verbalizing an ER diagram.

Verbalizing ER diagram

Once an ER diagram is completed and especially if the diagram is huge, as is often the case with a database containing a dozen or more tables, many designers will opt to verbalize the diagram for quick reference and to provide a second source of documentation.
The ER diagram for Stories on CD is translated into relational notation this way, with entity names followed by attributes enclosed in parentheses. Note that the entity name is now plural, reflecting the name you will ultimately give to the table in the database itself.

Customers  (CustID PK, CustLast, CustFirst, CustStreet, CustCity, CustState,
CustZip, CustPhone) Orders (OrderNo PK, CustID FK, OrderDate) 

Line Items (OrderNo CPK/FK, CDNo CPK/FK, Quantity,
SellingPrice, LineCost, Shipped) 

CDs (CDNo PK, CDTitle, DistID FK, RetailPrice, AgeGroup, Description) 

Distributors (DistID PK, DistName, DistStreet, DistCity, DistState, DistZip, DistPhone)

Database Modeling

Relational Notation

The relational notations retain the information necessary to translate them into basic database tables. However, unlike an ER diagram, this type of notation is not a particularly good tool for communicating the structure of a database to end users. It is principally for the benefit of the database designer, and often comes in handy when standardizing on names for entities(tables) and attributes(fields). As with an ER diagram, there is no “standardized” notation for verbalizing a diagram; consistency is the only rule of thumb. The next lesson concludes the module.

Naming fields

When creating a medium-to-large database with a dozen or more tables and dozens of fields, it is especially important to have some sort of naming strategy for fields (attributes) that you apply consistently across the database. Often, you will implement the naming strategy when you verbalize the ER diagram.
One very common practice among database designers is to use the first two, three, or four letters of a table’s name to distinguish between similar fields across tables. You have already seen this strategy applied throughout this course to the Stories on CD case study.
The ID (identification) fields for Customers and Distributors are similar in the case study; these were named CustID and DistID respectively. My own preference is to use capital letters to separate the table portion of the field’s name (Cust) from the descriptive or identification portion (ID). Other designers prefer to use the underscore character (_) to separate the two portions (for example, cust_ID).
The majority of database designers agree on one thing: Do not use a space to separate the two portions (Cust ID, for example, is not acceptable), even if the RDBMS you are using supports multiple words to identify fields. Not all RDBMSs support multiple words, and this fact alone makes it an unwise practice.
Finally, be careful about using abbreviations to name fields. Do not use abbreviations that are not intuitive. In our case study, for example, first and last names of customers are specified as CustFirst and CustLast, as I feel this abbreviated format is intuitive. Other designers are not comfortable using abbreviations at all, and would opt for a format such as CustFirstName and CustLastName (or, cust_first_name and cust_last_name). Here, as elsewhere, consistency combined with standard database design practices will serve you well.