Add keys to the telephone directory diagram

The following figure shows the initial choices of primary and foreign keys. This diagram reflects some important decisions.

For the name table, the primary key rec_num is selected. The data type for rec_num is SERIAL. The values for rec_num are system generated. If you look at the other columns (or attributes) in the name table, you see that the data types that are associated with the columns are mostly character-based. None of these columns alone is a good candidate for a primary key. If you combine elements of the table into a composite key, you create a cumbersome key. The SERIAL data type gives you a key that you can also use to join other tables to the name table.

The voice, fax, modem, and address tables are each joined to name through the rec_num key.

For the voice, fax, and modem tables the telephone numbers are used as primary keys. The address table contains a special column (id_num) that serves no other purpose than to act as a primary key. This is done because if id_num did not exist then all of the other columns would have to be used together as a composite key in order to guarantee that no duplicate primary keys existed. Using all of the columns as a primary key would be very inefficient and confusing.

Figure 1. Telephone directory diagram with primary and foreign keys added

There are five entities in the diagram. Each entity is represented by a rectangle. The name of each entity is above its rectangle. The attributes of each entity are inside its rectangle. The names of the five entities are: name, voice, fax, modem, and address. A legend in the lower right of the diagram shows that the letters PK after an attribute indicates that it is a primary key (or is part of a composite primary key) and the letter FK after an attribute indicates that it is a foreign key. The PK and FK are not part of the attribute name. The attributes of the "name" entity are: rec_num PK, lname, fname, bdate, anniv, email, child1, child2, and child3. The attributes of the "address" entity are: id_num PK, rec_num FK, street, city, state, and zipcode. The attributes of the "voice" entity are: vce_num PK, rec_num FK, and vce_type. The attributes of the "fax" entity are: fax_num PK, rec_num FK, oper_num, and oper_till. The attributes of the "modem" entity are: mdm_num PK, rec_num FK, b9600, b14400, and b28800. The "name" entity is connected to each of the other four entities by relationships. None of the other four entities are connected to each other. The relationship between "name" and "voice" has no special symbols on the end near "name". The end near "voice" has the symbol for "optional" and the symbol for "many". The relationship between "name" and "fax" has the symbol for "many" on the end near "name". The end near "fax" has the symbols for "optional" and "many". The relationship between "name" and "modem" has the symbol for "exactly one" on the end near "name". The end near "modem" has the symbols for "optional" and "many". The relationship between "name" and "address" has the symbols for "optional" and "many" on the end near "name". The end near "address" has the symbols for "optional" and "exactly one".