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

Database design occurs in the component design step of the systems
development life cycle (SDLC) in the systems analysis and design process.
For an introduction to systems analysis and design, and to the SDLC, see
Appendix B.

The Purpose of a Database Design

A database design is a set of database specifications that can actually be implemented as a da-
tabase in a DBMS. The data model we discussed in Chapter 5 is a generalized, non-DBMS specific
design. A database design, on the other hand, is a DBMS specific design intended to be imple-
mented in a DBMS product such as Microsoft SQL Server 2012 or Oracle Database 11 g Release 2.
Since each DBMS product has its own way of doing things, even if based on the same re-
lational database model and the same SQL standards, each database design must be created
for a particular DBMS product. The same data model will result in slightly different database
designs depending upon the intended DBMS product.

Topics on systems analysis and design often identify three design stages:

By The Way

● Conceptual design (conceptual schema)

● Logical design (logical schema)

● Physical design (physical schema)

The database design we are discussing is basically equivalent to the logical design ,
which is defined in these topics as the conceptual design implemented in a specific
DBMS product. The physical design deals with aspects of the database encountered
when it is actually implemented in the DBMS (as we will discuss in Chapter 10A for
Microsoft SQL Server 2012, in Chapter 10B for Oracle Database 11 g Release 2, and in
Chapter 10C for MySQL 5.6), such as physical record and file structure and organiza-
tion, indexing, and query optimization. However, our discussion of database design will
include data type specifications, which is often considered a physical design issue in
systems analysis and design.

Create a Table for each entity

We begin the database design by creating a table for each entity using the steps shown in
Figure 6-1. In most cases, the table is assigned the same name as the entity. Each attribute of
the entity becomes a column of the table. The identifier of the entity becomes the primary key
of the table. The example in Figure 6-2 shows the creation of the EMPLOYEE table from the
EMPLOYEE entity. In this text, to differentiate entities from tables, we will show entities with
shadowed boxes and tables with nonshadowed boxes. This notation will help clarify our dis-
cussion, but be aware that it is not standard notation across the industry.
Be certain that you understand the difference between these similar-looking graphics.
The shadowed rectangle in Figure 6-2(a) represents a logical structure that has no physical
existence. It is a blueprint. The nonshadowed rectangle in Figure 6-2(b) represents a database
table. It is the same as the following notation that we used in Chapters 3 and 4:

EMPLOYEE ( EmployeeNumber , EmployeeName, Phone, Email, HireDate,
ReviewDate, EmpCode)

Note, too, the key symbol next to EmployeeNumber. It documents the fact that EmployeeNumber
is the table key, just as the underline does in the notation used in Chapters 3 and 4.