Transforming Data Models into Database Designs - Database Processing: Fundamentals, Design, and Implementation - page 235

EMPLOYEE

EmployeeNumber: Int

EmployeeName: Varchar(50)
Phone: Char(15)
Email: Nvarchar(100) (AK1.1)
HireDate: Date
ReviewDate: Date
EmpCode: Char(18)

Figure 6-5
Table Display Showing Data
Types

In fact, with many data modeling products, such as CA Technologies' ERwin, you can
specify the DBMS you will use and the data modeling product will supply the appropriate set
of data types. Other products are DBMS specific. For example, Oracle's MySQL Workbench is
intended to design databases for MySQL and therefore uses MySQL-specific data types.
If you do not know which DBMS product you will be using, or if you want to preserve in-
dependence from a particular DBMS, you can specify the data types in a generic way. Typical
generic data types are CHAR(n) for a fixed-length character string of length n ; Varchar(n) for
a variable-length character string having a maximum length of n ; Nvarchar(n) for a variable-
length Unicode character string having a maximum length of n; Date; Time; Money; Integer (or
Int ); and Decimal . If you work for a larger organization, that company probably has its own
generic data standards. If so, you should use those data standards.
Figure 6-6 shows the EMPLOYEE table showing both data type and null status. The
display becomes crowded, however, and from now on we will show tables with just column
names. With most products, you can turn such displays on or off depending on the work you
are doing.

By The Way The fact that a design tool is dedicated to one DBMS product does not
mean that it cannot be used to design databases for other DBMSs. For
example, an SQL Server database can be designed in MySQL Workbench and most
of the design will be correct. You will, however, have to understand the relevant dif-
ferences in the DBMS products and make adjustments when creating the actual
database.

Default Value
A default value is a value supplied by the DBMS when a new row is created. The value can be
a constant, such as the string 'New Hire' for the EmpCode column in EMPLOYEE, or it can be
the result of a function, such as the date value of the computer's clock for the HireDate column.
In some cases, default values are computed using more complicated logic. The default
value for a price, for example, might be computed by applying a markup to a default cost and
then reducing that marked-up price by a customer's discount. In such a case, an application
component or a trigger (discussed in Chapter 7) will be written to supply such a value.
It is possible to use the data modeling tool to record default values, but such values often
are shown in separate design documentation. Figure 6-7, for example, shows one way that de-
fault values are documented.

EMPLOYEE

Figure 6-6
Table Display Showing Null
Status and Data Types

EmployeeNumber: Int NOT NULL

EmployeeName: Varchar(50) NOT NULL
Phone: Char(15) NULL
Email: Nvarchar(100) NULL (AK1.1)
HireDate: Date NOT NULL
ReviewDate: Date NULL
EmpCode: Char(18) NULL