Characteristics of a good database design

1. Introduction

The purpose of this report is to mainly discuss about characteristics of a good database design and a quality design. Main reasons behind writing this report is as part of implementation of Stanwell parish plan the need for a database application was identified by several committees.

Our committee is responsible for the design a database for record the runners participated on the annual Fell race. The new broadband internet access facility is helpful in creating these new facilities.

A database can be identified as a set of records or files which are logically related. Computer based databases are normally organised as one or multiple number of tables. A table consists of series of rows and columns.

Relational model is a powerful and commonly used method for organising data. The table design of the database reflects the quality of the database.

The principles of normalization need to be considered always when designing a effective database. Objectives of normalization are follows.

• Minimising data redundancy
• Minimising data restructuring
• Minimising I/O by redesign transaction sizes
• Enforcing referencing integrity.

When designing an effective database following techniques and concepts are important to consider.

• Identifying an entity as a logical collection of items related to the stated database. And an attribute is a descriptive or quantitative characteristic of the entity.
• The physical counterpart of the entity is a database table and the physical counterpart of the attribute is the column.
• Primary key is identified as an attribute which identifies each instance of an entity uniquely.
• The logical link between two entities is the relationship.
• When the primary key of a parent entity exists in a child entity, it is called as a foreign key.
• There are two types of relationships, Identifying and non Identifying. Identifying relationships are when primary key of the parent entity exists in the primary key of the child entity. In non identifying relationships primary key of the parent key exists in the child entity but not as a part of primary key of the child entity.
• To understand the nature of the relationship between the parent and child entities, cardinality is helpful. Four types of cardinalities are

§ 1 to 0 or more

§ 1 to 1 or more

§ 1 to 0 or 1

§ 1 to many

In designing a correct database ER, EER and Use Case are really useful modelling tools.

2. Importance of data modelling

Data model is a very useful tool in communicating between developers and users of a database. It is also helpful in identifying design issues and other problems before starting the actual design process. A data model is like a blueprint of a newly constructing building. Builders will newer start building a large construction without a proper blueprint. Even though changes are made during the developing process, the basic foundation remains the same.

Importance of ER diagrams in the design

ER diagrams falls into a category of data modelling.ER diagrams show entities in a database and relationships between tables. This graphical representation helps to understand how the database works with the interactions and dataflow. No matter how large or small the database is, the concepts of entity and relationship are the same.

ER diagrams have 3 basic components.

• Entities
• Attributes
• Relationships

For an example, in the Fell Race database, Race can be identified as an entity and year of the Race can be identified as an attribute. An entity corresponds to a table of the database and attributes corresponds to the columns of that particular table.

Most common errors that can happen when designing an ER diagram can be stated as,

• Missing constrains
• Missing keys to any entity type
• Unexpressed constrains due to bad design
• Associating attributes with wrong entry types
• Putting invalid constrains
• Misuse of subclasses
• Using foreign keys instead of relationships

EER Diagrams in modelling

EER diagrams are also a similar kind of tool to ER diagrams except for some little differences. It is identified as enhanced model of ER diagrams.

EER = ER + hierarchical relationships Hierarchical relationship means relationship between Super class and subclass, the IS A relationship.

The next step is transferring the ER diagram or EER diagram into well structured relations. A relational table is made up of one or more data tables or relations. The mapping should be done in a way to avoid data duplication. This is called data redundancy.

Data redundancy can become a problem for several reasons.

• Entering same data over and over again is a waste of time.
• Data redundancy increases the probability of occurring errors
• Errors can occur when retrieving data.
• Disk space is wasted.
• Affect the performance of the overall system
• When updating, deleting data, multiple rows should be considered.

Normalization is the process used to avoid data redundancy.

Importance of Use Case diagrams in the design

Use cases are used in the analysis stage of the design process. Main target of drawing a use case is to identify the system functionality. Use cases separate the system into use cases and actors.

Use cases represent the integration of actors and the system. An actor is defined as a thing with behaviour. An actor could be a person or another system.

According to the Fell Race system actor can be someone who uses the system to enter data (user).

3. Importance of a good database design

Mainly a good, effective database design helps the development team to reduce the costs and time taken for the overall development. By creating a good data model and following the correct process, helps the development team to understand user requirements clearly and accurately.

4. Is a correct database design necessarily a quality design?

The answer is no. A correct database design is not always a quality design. But a quality design should always be a correct design.

In a quality design, instead of putting data over multiple files, data is stored in a single logical unit. This reduces the duplication of data. The non duplication of data helps to maintain data integrity. Also a quality design is helpful in maintain the security of data. Data can be managed properly trough a quality database design. Finally a quality design is a design that helps to increase the performance of the database. A correct database design would work, but a quality design will help increase the performance of the database.

5. Conclusion

An affective and quality database design helps the development team to reduce time and the overall cost of the system development process. By taking a correct approach to database design helps the team to understand the user requirements correctly and deliver a system the user desires. A good database design will also help increase the overall performance of the system.

References

• Mike gahan.2000.An introduction to databases.[online](updated 10 October 2000) Available at: http://www.ucl.ac.uk/archaeology/cisp/database/manual/node1.html [Accessed 10 January 2010].
• SQLTeam.com.2000.Database design and modelling fundamentals.[online](updated 30 June 2000) Available at : http://www.sqlteam.com/article/database-design-and-modeling-fundamentals [Accessed 10 January 2010].
• Farrkoh Alemi.2007.healthcare databases.[online](updated 27 November 2007) Available at : http://gunston.gmu.edu/709/DataModelingERDiagram.asp [Accessed 12 January 2010]
• Development cycles.2004.Entity Relationship Modeling-Experience vs InExperience.[online](updated 05 April 2004) Available at : http://www.devarticles.com/c/a/Development-Cycles/Entity-Relationship-Modeling/13/ [Accessed 12 January 2010]
• Veda c storey. Modelling quality requirements in conceptual database design.[e-book] Available at : http://web.mit.edu/tdqm/www/tdqmpub/QEROct98.pdf [Accessed 12 January 2010]
• John A. Hoxmeier.1997.A framework for accessing database quality.[online]Available at : http://osm7.cs.byu.edu/ER97/workshop4/jh.html [Accessed 12 January 2010]