Wednesday, September 4, 2013

What is a Derived Attribute

What is a derived attribute? Give an example.

Derived attributes are those whose values are created from other attributes. 

These values are generated with the help of algorithms, calculations and other relevant procedures. The specifications established for calculating these derived attributes is a concern with respect to the processing aspects of the particular information system. These attributes may be integrated with the data model only in the condition that the attribute value calculation rules would be lost in the absence of the derived attributes.

Database designers preferring to maintain the elegance of their designs prefer to avoid storing the derived attributes in their databases. They try to execute these derived attributes through appropriate algorithms so that they are called only when a specific query for them is made. In this manner the design elegance of the database is maintained.

The most appropriate example of the usage of derived attributes is the calculation of a person’s age using Julian dating system. Using this system the age of a person can be calculated by subtracting the date of birth of the employee from the current date and then dividing the result by 365. This can be understood better with the help of the following notation:
PERSON_AGE = (PERSON_DOB   DATE())/365

The main issue with non storage of derived attributes in the database is that large databases provide very slow queries when the values of the derived attributes are processed during the execution of the query.

Elad Shalom,
Senior Consultant at SwiftRadius
Co-Founder of Saint John Developer User Group

Action When MultiValued Attributes Encountered

What two courses of action are available to a designer when a multivalued attribute is encountered?

When a multivalued attribute is encountered, the designer has two alternatives which can be followed.
  1. The multivalued components can be split into its components and kept in the same entity. But the only condition with the usage of this approach is that only single entries are stored at each level. For example, CUSTOMER_TELEPHONE can be decomposed into CUST_HOMEPHN, CUST_MOBILE AND CUST_FAX_NUMBER. If the customer has more than one mobile number, then this structure will not be able to store more than a single value. Hence for each component, only a single value will be stored.
  1. The second approach involves the creation of a new entity which would comprise of the components of the multivalued components. This new entity can be linked to the entity in which the multivalued attributes were originally present. This method is most favorable and desirable when the total number of results in the multivalued attributes is unlimited. This holds significance for practical purposes also. The most appropriate example of this approach is the classification employees as “technical” which enables them to possess certifications in various areas as well as levels.

Elad Shalom,
Senior Consultant at SwiftRadius

Database Design and Normalization Principles

The “right” database design can rarely be decided on the basis of normalization principles alone. Do you agree or disagree with this assertion?

The Right Database and Normalization
A database is a collection of information formatted into a table, chart, or file. Data tables are generally collections of information inputted into columns, rows and fields.

Columns in each table can be selected through a primary sorting key and there may be unique keys to assist in data retrieval and input.
You may have columns that are fixed in length or vary depending on the type of data that is being input. At the same time, records can also be fixed or varied.
You can restrict column names and keep your column and table names case sensitive.
You can develop a database in any form you desire as long as it is "normal."

Normalization Characteristics
There are many way to construct a database which includes the rational database plus the principles of normalization. One example includes database normalization techniques constructed by mathematicians.

These types of data bases are difficult to understand and program unless you have a math background.
To make life easier on those who do not understand all the techniques in mathematical equations, normalized relationship databases can be summarized into these benefits:
  • Eliminating redundant data storage. Rather, not allowing the same data to be stored over and over again creating an infinite number of columns.  Data can be overwritten, but there will not be multiple entries through normalization.
  • Modeling of real world objects or entities and their relation to one another.
  • Structuring the data to enable a model to be flexible and adaptable. 
The "real" definition of normalization is the procedure of forming data, shaping it into workable tables and columns and providing data that is easy to manage.  If your data is normalized, there are no more redundancies or no more inputting the same data over and over.
  • Identify relations between attributes.
  • Combining the attributes to the relations of the forms.
  • Combine relations or attributes to form a complete database.
There are different form of databases and normalization, and it is good for database designers and programmers to understand what their specific form of normalization is. This will help in finding "broken" entries and taking the right actions to fix those broken entities.

Every database has normal attributes and designers need to define the attributes, group each related attribute into relations, select primary and candidate keys for every relation plus remove repeating groups. Functional dependencies must be identified and all transitive dependencies need to be identified.

Is Normalization Normal?
When all the theories have been listed, stated and argued the result is: is normalization normal?

Normalizing your data bases makes sense to the company, does provide great performance, prevents duplication, avoids synchronization problems, and allows programmers to write simpler activities and codes. Using set templates for developing databases provides ease of inputting and use.
Yet what is normal for one department is definitely not normal for another department. Normalization does not fix any problems; it may create more problems.

Measure the data you need and how you will input and retrieve the data contingent on the type of information you are inputting and retrieving. Let your normal be controlled, but also make provisions to customize your data base. 

A normalized database is great if you have template data to input and retrieved, but if you have complex data that needs to be retrieved in a specific manner, you need to customize and "denormalize" your database.
"As the old adage goes, normalize until it hurts, denormalize until it works" (Atwood, 2008).

References:
Atwood, Jeff (2008). Coding Horror: Maybe Normalizing Isn't Normal. Available: http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html. Last accessed: 3 April 2013.
Marston, Tony (2004). Relations Data Model, Normalization and Effective Database Design. Available: http://www.tonymarston.net/php-mysql/database-design.html#what.is.it. Last accessed: 3 April 2013.
Melton, Beth (2009). Databases, Normalizing Access Data. Available: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88. Last accessed: 3 April 2013.



Elad Shalom,
Senior Consultant at SwiftRadius