The physical counterpart of an attribute is a column within a table. When you map entities to tables, map the attributes of each entity to the columns of each respective table. At least initially, do not change the basic definition of the columns. For example, do not group attributes together into a composite column.
The attributes of each entity should be mapped to the columns of each respective table.
Try to maintain the same naming convention for physical columns as was used for logical attribute names. However, you must understand that the physical constraints of the DBMS being used may limit your ability to do so. Always take into account the capabilities and limitations of the DBMS when creating the physical database from the logical data model.
Transform Domains to Data Types
To support the mapping of attributes to table columns you will need to map each logical domain of the attribute to a physical data type and perhaps additional constraints. Each column must be assigned a data type. Certain data types require you to specify a maximum length. For example, you could specify a character data type as CHAR(20), indicating that up to 20 characters can be stored for the column. You may need to apply a length to other data types as well, such as graphic, floating point, and decimal (which also require a scale).
Map each logical domain to a physical data type, perhaps coupled with additional constraints.
Commercial DBMS products do not support domains, so the domain assigned in the logical data model must be mapped to a data type supported by the DBMS. You may need to adjust the data type based on the DBMS you use. For example, what data type and length will be used for monetary values if no built-in currency data type exists? Many of the major DBMS products support user- defined data types, so you might want to consider creating a data type to support the logical domain if no built-in data type is acceptable.
There may be multiple physical data types that can be used successfully for a domain. Consider a logical domain whose valid values are integers between 1 and 10. You could choose an integer data type, of which there may be several (e.g., INTEGER, SMALLINT, TINYINT). Alternatively, you could choose a decimal data type with a zero scale. You might even choose to store the data in a 2- byte character column if no mathematical operations are required. Any of these can work. As the DBA you will need to determine which data type can be most efficiently accessed, stored,
maintained, and processed by the applications accessing the data. To make such a decision requires in-depth technical knowledge of the way in which your DBMS physically stores each type of data, as well as knowledge of application processing details.
In addition to a data type and length, you may also need to apply a constraint to the column. Consider, once again, the domain of integers 1 through 10. Simply assigning the physical column to an integer data type is insufficient to match the domain. A constraint must be added to restrict the values that can be stored for the column to the specified range of 1 through 10. Without a constraint, negative numbers, zero, and values greater than ten could be stored.Using check constraints, you can place limits on the data values that can be stored in a column or set of columns. Check constraints are covered in detail in Chapter 13.
The nullability of each column in the table must also be specified. The logical data model should contain information on the nullability of each attribute, and this information can be copied for each requisite column in the physical database. Some DBMS software enables you to assign a default value to be used when a row is to be inserted and no value has been provided for the column. You must assign column default values when you create the table.
For text or character data, you need to make an additional decision: Should the column be fixed length or variable length? A fixed-length column occupies a preset and unchanging amount of storage for each row. A variable-length column specifies a maximum size, but the actual length used by the column can vary for each row. Variable-length columns can save storage space, but usually require additional manipulation by application code to insert and maintain them. Another negative aspect is that variable-length columns can cause the table to require more frequent attention. Changes made to row size will have the effect of moving rows within the database. If the DBMS offers a compression option, you may be able to save more space by compressing the database than by implementing variable-length columns.
Primary Keys
Specification of a primary key is an integral part of the physical design of entities and attributes. When designing the logical data model, you assigned a primary key for every entity, and as a first course of action you should try to use that primary key. However, multiple candidate keys are often uncovered during the data modeling process. For physical implementation you may decide to choose a primary key other than the one selected during logical design 뾢 ither one of the candidate keys or another surrogate key.
If the primary key is unwieldy, you may need to choose another primary key. Perhaps the key is composed of several columns or is a nonstandard data type. In either of these cases it may be better to choose a surrogate primary key. Most DBMS products provide built-in features that can assist in the definition of primary keys. Some examples include support for default values, ROWID data types, and the identity property (see "The Identity Property" sidebar).
Most DBMS products provide built-in features to help define primary keys.
As a rule of thumb, though, be sure to identify a primary key for each physical table you create. Failure to do so will make processing the data in that table more difficult. If there is no key to uniquely identify rows of the table, it will be difficult for programs to specifically select, modify, and delete individual rows. Furthermore, without a primary key, dependent relationships cannot be defined for that table.
Be sure to identify a primary key for each physical table you create.