The database is a fundamental component of an organisational information system, Connolly and Begg (2005), so the quality of a database system that full meets the user requirements largely depends on the correctness of the design of the database given that "database design is a process of creating a design that models an enterprise in the actual world" Tom Jewett (2006).
Database design is a vital part in database system development lifecycle which is the very next stage after proper requirements collection and analysis had been carried out, since it is a key component of organisational information system. There are three stage processes of database design, Curtis and Houldcroft (2009) namely:
- Conceptual database design
- Logical database design
- Physical database design
Conceptual database design
This is the formal representation of the real world, capturing information about the business and aids understanding of the system states Curtis and Houldcroft (2009), the most widely used model for conceptual design is the Entity Relationship (E-R) model.
Entity Relationship (E-R) model
The Entity Relationship model helps to identify the entities and relationships that will be represented in the model. In the case of Scenario C - The Stanwell Theatre Group, for example, the entities identified are 'Customer', 'Performance', 'Booking', 'Ticket' and 'TicketType', so also the type of relationships (one-to-many and many-to-many relationships) between the entities were also represented.
The Entity Relationship (E-R) modelling is a top-down approach to database design and this really communicate nontechnical and free of ambiguities
Enhanced-Entity Relationship model (EER)
The inherent problems of ERD in representing complex data is addressed in Enhanced-Entity Relationship model (EER) which have additional 'semantic' modelling concept of specialization/generalization and composition. Connolly and Begg (2005) wrote that "specialization is a top-down approach process of minimizing the differences between members of an entity by identifying the distinguishing characteristics while generalization opposes this process by identifying common characteristics between entities".
Hoxmeier (1997) categorised the quality of database design into four sub-headings:
- Database process quality: end user requirements and needs are fundamental in the design process of a quality database. Proper requirement gathering and analysis process must be in place to reduce the rework if need be or significant uncertainty in the requirement becomes clearer. A continuous quality improvement process approach is good as this help lower cost of solving problem of poor design which can give serious problems like data integrity, structure and redundancy and also performance.
- Database data quality: the quality of data from the database is very key in the design of a quality database. For the user to make informed decisions and have confidence in it, the data must be accurate, the age of the data must reflect the enterprise need and the enterprise should be able to gain value from it. In the case of Scenario C - The Stanwell Theatre Group, for example, primary keys and the concept of referential integrity were implemented in the model to manage and avoid double booking to a particular event making the tables to remain consistent.
- Database semantic quality: According to Hoxmeier (1996); Levitin and Redman (1995), "Content, scope, consistency, conformity to generally accepted design principals, and flexibility are all characteristics of model quality". Semantic quality of a database design helps in relating to meaning the problem and solution area, thereby improving the process of defining the problem and solution area. In the case of Scenario C - The Stanwell Theatre Group, for example, the entity relationship diagram helps describe the expectation in a visual format covering scope of task and very consistent.
- Database behaviour quality: this is more of implementation of the database design and usage of the database." Because of the difficulties associated with the definition of a fixed set of current requirements and the determination of future utilization the database problem domain is typically a moving target or resembles an "amoebae". When one corner gets squeezed, the problem expands in another area. In addition, insufficient identification of appropriate database 'behaviors', poor communication, and inexperience in the problem domain leads to inferior solutions." Hoxmeier (1997)
How to identify the characteristics of a good database design
The desire of end-user is to make informed decisions and have absolute confidence in the enterprise database which must reflect the need of the enterprise. A poor database design will defeat this aim or desire resulting in lost of confidence and misinformation of the management. The followings are some of the characteristics a good database design should exhibit:
- A good database design allows only relevant and required data to be stored.
- The structure of a good database design must allow data to be consistent by implementing of primary keys and unique key constraints.
- A good database design should be well normalised thereby eliminatingdata redundancy
- A good database design allows for fast execution queries since it reduces complexities in queries.
- A good database design keeps into consideration the business rules as defined in the requirement gathering and analysis stage.
- A good database design should be efficient to allow for overall performance of the application which depends on it.
- A good database design should be easy to maintain to allow for easy modification as information requirements of the enterprise grow or change.
Given the above list of what characterised a good database design, i strongly agreed to the fact that a correct database design is a quality design as also "database quality must be measured in terms of a combination of factors including process and behaviour quality, data quality, semantic quality, and value" Hoxmeier (1997). The above principles applied in the design of Scenario C - The Stanwell Theatre Group can be applied for all the application proposed.
- Hoxmeier, J . A Framework for Assessing Database Quality, Available from: http://osm7.cs.byu.edu/ER97/workshop4/jh.html, (Accessed 10 January 2010)
- Hoxmeier, J. and D. Monachi, "An assessment of database quality: design it right or the right design?", Proceedings of the Association for Information Systems Annual Meeting, Phoenix, AZ, August, 1996.
- Levitin, A., and T. Redman, "Quality dimensions of a conceptual view", Information Processing and Management, 1995, Vol. 31, No 1.
- Susan Curtis and Alan Houldcroft (2009). Logical Database Development - Lecture Notes and Exercise, Sheffield Hallam University
- Thomas Connolly and Carolyn Begg (2005). Database System: A practical Approach to Design, Implementation, and Management. 5th ed.
- Tom Jewett (2006) Database design with UML and SQL, Available from http://www.tomjewett.com/dbdesign/dbdesign.php?page=models.html (Accessed 10 January 2010)