WHAT ARE THE PHYSICAL DESIGN PREREQUISITES

FOR A DATABASE?

 

Good physical database design is essential!!

But.... what is physical database design??

"Physical database design is the process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures". [1]

The prerequisites for the physical design of a database can be summarized as follows: [5]

- Global logical data model (entities and attributes are well defined, the relationships between them and special rules that define relational integrity)

- Relations are normalized

- Descriptions of where and when data are used
(entered, retrieved, deleted, updated)

- Expectations/requirements for response times, data security, backup, recovery, retention, integrity.

- Description of technologies (DBMS) used for implementing the system

- GUI standard ideas

 

But what does this mean? Let's go over each one of them....

 

Entities and Attributes definition:

Tables are the heart of a database, therefore special care should be placed in designing and creating them. Obviously, one of the most important features of a table is its name. A table name should relate to some logical entity represented in the table, so that in general, the organization of the database will be better understood by the people using it. Another essential feature involves table contents. Fields should be accurately defined. Whenever possible, fields in a table that are only populated in some circumstances should be avoided, since this can lead to confusion regarding whether or not a row in a table is valid. It is also important to watch for tables that contain data that does not belong there.

In a database relationships are as important as tables. A relationship is implemented as a rule which defines Data Integrity. Constraints involve a special kind of validation rule and  typically defined at the field level or at the table level. A table-level constraint is typically used to maintain relational integrity - such as ensuring that the value of a foreign key exists in a particular table. [2]

 

Database normalization:

The goal of Normalization is to produce a Database that is a flexible -- an accurate data container that will cope with future expansion. 

Also refers to the optimization of the tables that constitute a database. Basically, the rules of normalization are enforced by eliminating redundancy and inconsistent dependency in your table designs, making the database more functional and efficient.

Generally Data Normalization is where you start. It is very easy to maintain and add data to a properly normalized database. There are five main progressive steps for normalization:

- First Normal Form

- Second Normal Form

- Third Normal Form

- Fourth Normal Form

- Fifth Normal Form [3]

 

 

When physically implementing the database, there are several considerations to be taken into account:

Indexing is useful in allowing for efficient data access, as well as in enforcing relational integrity by preventing duplicate values. Indexes are ideal for retrieving a few rows at a time from a large table. However, there are some drawbacks in index use, for example they can make updates quite slow. [2]

When entering the data into the database, it is very important to keep records of where and when data are entered, retrieved, deleted, or updated. This provides more control over the data and makes its use more comprehensible.

 

Implementing the database....

There are several RDBMS options in the market to implement a database, and it is important to select the technology that better suits your needs. This includes considering versatility, efficiency or cost-benefit relationship, platform in which to be installed, etc. Among the most commonly used software for implementing databases we can find:

- Microsoft Access

- Oracle

- SQL server

- IBM DB2

- Sybase ASE

 

And last but not least.... how about GUI design?

Graphical User Interface (GUI) design may not be directly related with how the database works or how efficient it is, however it concerns a very important issued regarding databases: the USER!  The user must feel comfortable while working with the database. Now, GUI design should have specific set standards so that all of the GUI looks and feels the same. This is essential for ease of use.

Some of the areas to be considered for a GUI design guide are:

- Fonts

- Default color schemes

- Choice of Controls (there are several Tab, Grid and 3D controls)

- Form density & Layout

- Buttons or Menus or Both

- Feedback - Hourglass, Progress bars, Status bars

- Responsiveness [2]
 

 

And now... let's review some of the concepts.....

Short answer questions:

1. Why is normalization important for a database?

Because it makes the database flexible and well organized by eliminating
redundancy and inconsistent dependency in your table designs.


2. What are some of the important considerations when physically implementing a
database?

Efficiency and speed, expandability, security, and data synchronization, among
others.

3. What are some of the factors to be considered when implementing GUI design?

Fonts, color schemes, controls, buttons and menus, and responsiveness, among
others.

4. What is the difference between logical design and physical design of a
database?

Logical database design is concerned with what entities the database will
include and what relationships it will support. Physical database design
relates to how the database entities and their relationships are to be actually
implemented.
 

Multiple choice questions:

1. Physical database design is concerned with:

a) implementation of the database on secondary storage
b) integrity constraints
c) file organizations
d) all of the above
e) none of the above

The answer is d).

2. Indexing in a database is useful because:

a) it makes the database updating process faster
b) it enforces relational integrity
c) it helps define constraints and validation rules
d) it allows for efficient data access
e) both b) and d)

The answer is e).
 

 

TEXT REFERENCES:

[1] Physical Database design for Relational Databases. http://www.cs.ucc.ie/~abf/CS507-8/L43.pdf

[2] Orange&Black Consultancy LTD. http://www.orangeandblack.co.uk/index.htm

[3] Database Normalization and Design Techniques. Barry Wise.

 http://www.phpbuilder.com/columns/barry20000731.php3

[4] Databases: Physical Design. http://bioinfo.mbb.yale.edu/course/projects/talk-1/db11.html

[5] MIT 5314 website, Dr. Kirs. http://calc.utep.edu/pkirs/mit5314/index.htm

 

PICTURE REFERENCES:

[1] http://www.gannettoffset.com/assets/images/telweb2.gif

[2] http://www.stratigraphy.net/Database.gif

[3] http://www.samoore.com/teach/cis320/fall2001/documents/stocks-fields.png

[4] http://www.texas-broadband.com/images/monitor_animation.gif

[5] http://www.indiaeye.com/swat.jpg

 

This tutorial created by: Lucia Alvarado