System Analysis & Design – Week – 10 (Designing Databases)

Designing Databases

Introduction

Database design has five purposes:

1. Structure the data in stable structures, called normalized tables, that are not likely to change
over time and that have minimal redundancy.
2. Develop a logical database design that reflects the actual data requirements that exist in the
forms and reports of an information system.
3. Develop a logical database design from which we can do physical database design.
4. Translate a relational database model into a technical file and database design that balances
several performance factors.
5. Choose data storage technologies (such as Read/ Write DVD or optical disc) that will
efficiently, accurately and securely process database activities.

Database Design: File and database design occurs in two steps. (Logical Database Model and
Physical Database Design).

Logical Database Model

Describes data using a notation that corresponds to a data organization used by a database
management system.

This is the system software responsible for storing, retrieving, and protecting data (such as
Microsoft Access, Oracle, or SQL Server).

The most common style for a logical database model is the relational database model.

Once you develop a clear and precise logical database model, you are ready to prescribe the
technical specifications for computer files and databases in which to store the data.

In logical database design, you use a process called normalization, which is a way to build a data
model that has the properties of simplicity, non-redundancy, and minimal maintenance.

Four key steps in logical database modeling and design:

1. Develop a logical data model for each known user interface (form and report) for the
application using normalization principles.
 System Analysis & Design – Week – 10 (Designing Databases)

2. Combine normalized data requirements from all user interfaces into one consolidated logical
database model; this step is called view integration.
3. Translate the conceptual E-R data model (developed without explicit consideration of specific
user interfaces) into normalized data requirements.
4. Compare the consolidated logical database design with the translated E-R model and produce,
through view integration, one final logical database model for the application.
Physical Database Design

Physical data basedesign includes following steps:

 Choosing the storage format (Data Type) for each attribute from the logical database model.
 Grouping attributes from the logical database model into physical records.
 Arranging related records in secondary memory (hard disks and magnetic tapes) so that
individual records and groups of records can be stored, retrieved, and updated rapidly (called
file organization).
 Selecting media and structures for storing data to make access more efficient. The choice of
media affects the utility of different file organizations. The primary structure used today to
make access to data more rapid is key indexes on unique and non-unique keys.

The Relational Database Model

The relational database model represents data in the form of related tables, or relations.

A relation is a named, two dimensional table of data.

Each relation (or table) consists of a set of named columns and an arbitrary number of unnamed
rows.

Each column in a relation corresponds to an attribute of that relation. Each row of a relation
corresponds to a record that contains data values for an entity.
 System Analysis & Design – Week – 10 (Designing Databases)

Refer to relation: - EMPLOYEE1. This relation contains the following attributes describing
employees: Emp_ID, Name, Dept, and Salary. This table has five sample rows,
corresponding to five employees.

The identifier attribute (called the primary key of the relation) is underlined. For example, you
would express EMPLOYEE1 as follows: EMPLOYEE1(Emp_ID,Name,Dept,Salary)

Note: - Not all tables are relations. Relations have several properties that distinguish them from
non-relational tables:

1. Entries in cells are simple.

2. Entries in a given column are from the same set of values.
3. Each row is unique. Uniqueness is guaranteed because the relation has a nonempty primary
key value.
4. The sequence of columns can be interchanged without changing the meaning or use of the
relation.
5. The rows may be interchanged or stored in any sequences.

Well-structured Relations

What constitutes a well-structured relation (also known as a table)?

A well-structured relation contains a minimum amount of redundancy and allows users to insert,
modify, and delete the rows in a table without errors or inconsistencies.

Table EMPLOYEE2 contains data about employees and the courses they have completed. This is
not a well-structured relation.
 System Analysis & Design – Week – 10 (Designing Databases)

If you examine the sample data in the table, you notice a considerable amount of redundancy.

E.g., the Emp_ID, Name, Dept, and Salary values appear in two separate rows for employees
100, 110, and 150. Consequently, if the salary for employee 100 changes, we must record this
fact in two rows (or more, for some employees).

Physical File and Database Design

Designing physical files and databases requires certain information that should have been
collected and produced during prior SDLC phases. This information includes the following:

 Normalized relations, including volume estimates

 Definitions of each attribute
 Descriptions of where and when data are used: entered, retrieved, deleted, and updated
(including frequencies)
 Expectations or requirements for response time and data integrity
 Descriptions of the technologies used for implementing the files and database so that the range
of required decisions and choices for each is known.

Normalized relations are, of course, the result of logical database design.

Number of rows in each table as well as the other information listed above may have been
collected during requirements determination in systems analysis. If not, these items need to be
discovered to proceed with database design.
Designing Fields

A field is the smallest unit of application data recognized by system software, such as a
programming language or database management system.

An attribute from a logical database model may be represented by several fields.

E.g, a student name attribute in a normalized student relation might be represented as three fields:
last name, first name, and middle initial.

In general, you will represent each attribute from each normalized relation as one or more fields.
 System Analysis & Design – Week – 10 (Designing Databases)

Choosing Data types:

You want to choose a data type for a field that minimizes space, represents every possible
legitimate value for the associated attribute, and allows the data to be manipulated as needed.

You would select a length for this field that would handle the maximum value.

Designing Physical tables:

A relational database is a set of related tables (tables are related by foreign keys referencing
primary keys).

In logical database design, you grouped into a relation those attributes that concern some unifying,
normalized business concept, such as a customer, product, or employee.

Physical table is a named set of rows and columns that specifies the fields in each row of the table.

The design of a physical table has two goals: efficient use of secondary storage and data processing
speed.