Database is very important for any application in the real world. In modern days companies are looking for cost cutting, if they apply cost cutting process in the database design stages then there will be modifications or rebuilding the database after implementation. They will leave with high budget for the company. To create an efficient and appropriate database for an application it should be a good idea for any developer to follow certain database design steps. Bad database design will lead company to losses as well as client un-satisfaction. In present days many of the database designs are failed because of wrong user requirements. Collection of user requirements is main and first step in the database design process. If a developer took wrong assumption on the requirements then it results a bad database design. So the developer should be well experienced in database design.
In this report I am going to explain about the importance of standard database modelling tools such as ER, EER and Use case modelling, quality of the database design and the factors affecting quality of a database design. Many of the examples which I am going discuss in this report are from the given scenario to me namely "The Stanwell Parish Register".
In any application building there are two main steps first one is database design and other is front end design. First step is to build database, based on this design developers will build front end design. So the database design is pillar for any application. In these days we can hardly see the applications without databases. Now I am going to discuss about the concepts in database design process. For the successful creation of a database there are three concepts to follow. They are
- Conceptual data model: This is the initial and important step in the database design process. This will represent all the business rules applied for the particular database. Conceptual data model is a diagram which shows the flow of business among all the important entities involved. It doesn't show any relationships and constraints.
- Logical data model: Logical data model is the other step in database design which comes after conceptual data model. According to Shelley Doll (2002) "The logical design of a database involves taking the business information discovered in a conceptual data model and translating it into an empirical layout of the data, usually in a relational style". It deals with the relations among the entities and attributes as well as the constraints. It doesn't bother about the implementation of the database design.
- Physical data model: This is the final step in database design process. Physical data model deals with the implementation of the logical data model and also about the data in it.
Each concept stated above is important for any database design. Some experts may skip one or more steps in design process; they will repent about it later. My intention here is not to explain and show the process of database design whole. Before going in detail about my task I would like to say about the scenario given to me.
The given scenario is about the villages of Great and Little Stanwell in Derbyshire Peak District. They are small communities which have about fifty houses each but they have lot of voluntary activities. Recently they gained broadband access to the internet. Now they planned to implement Parish plan in a way to develop specialized services. They formed some communities to organize these implementations. They don't bother about the logical and physical data model because they are too technical but committee members are non-technical so they need conceptual data model only. In those my scenario is to produce a plan for Stanwell Parish Register. This is a database which stores all information about the residents and the assistances they many provide or avail.
Coming to database design there are some standard tools for this purpose. They are ER, EER and use case diagram. In the remaining part of this report I am going to write about the contribution and importance of each of these standard designing tools in database design.
Entity Relationship model:
As the name implies Entity Relationship Model is a diagram which shows the relationships between the entities involved in the business. An entity is a real world thing it may be a person or car or house etc. Relationship tells us how one or more entities are related to each other. ER model is a part of Conceptual data model. In the views of Peter Pin-Shan Chen (1975) "The Entity Relationship Model adopts the more natural view that the real world consists of entities and relationships. It incorporates some of the important semantic information about the real world. The model can achieve a high degree of data independence and is based on set theory and relation theory". In ER model entities are represented with rectangle boxes, attributes are represented with ovals and relationships are represented with rhombus type boxes. For the given scenario residents and assistance are the main entities. Following diagram will clearly explain the ER model for given scenario.
From the above diagram it is clear that Resident and Assistance are the main entities. R_name and A_name are the attributes where as provides/avail tells us how those two entities are related. One to many and zero to many are the relations between resident and assistance. It means one resident can provide or avail zero or many assistances and one assistance can be provided or avail by one or many residents. There is nothing technical in this diagram but it represents all the business flow. So any non technical member can also understand the business flow by having a look at this diagram. As I said earlier here committee members are non technical so for any scenario ER diagram will help them to understand the flow of design.
Enhanced Entity Relationship Model:
There is no prize money for guessing Enhanced Entity Relationship Model is the extension for ER model. Each model will have some limitations. In the same way ER model also has some limitations. In a way to overcome those limitations EER model was found. It is very difficult to represent complex applications through ER model and it is hard to represent the relationships which are more than binary. According to Connolly (2004) "Semantic concepts are incorporated into the original ER model and called the Enhanced Entity Relationship (EER) model. Examples of additional concept of EER model are called specialization/ generalization". Those concepts are also called as abstract concepts. In the words of Irwin Levinstein (2001) "Specialization defines SETS of subclasses on a class. Each set called a specialization. Generalization unites several entities into a set of sub-classes, making a new class". This model works mainly with super class and sub-class concepts as well as with is a kind of and is a part of relationships. To explain this model clearly I would like to give an example from the given scenario.
The above figure is just an example for the semantic concept specialization. From that diagram it is clear that permanent resident and contract resident are a kind of residents. All the rules applied for the resident are applied for those two also. This is is-a relationship example. If we consider assistance then shopping or teaching are a part of assistance.
Use case modelling:
Use case modelling is one more standard tool in database design. The main components of use case modelling are use cases and actors. This model was invented by Ivar Jacobson in early 1990's. According to an Oracle white paper (2005) "A use cases are fundamentally a text form and are used to specify the functional requirements of a system, primarily as a scenario. The scenario describes how the system should respond to a request of a primary actor to deliver a specific goal of that actor". In the given scenario resident is an actor and providing or availing an assistance is a use case. The following diagram shows how use case modelling works in the given scenario.
Each model stated above has advantages and disadvantages. So the choice of model is fully dependent on the type of scenario as well on the organization members and business requirements.
Quality of a database design:
As we all know database is an important asset for any application in the real world. So it is important for any developer to aim at efficient database design. Here the word quality plays a vital role because by measuring the quality of a database one can say that the database design is efficient. With an immediate affect one question will comes in to our minds is "How can we measure the quality of a database design?" There are many ways to measure the quality of a database design. Some authors followed some metrics and semantics to measure the quality of a database design. John A. Hoxmeier (1998) proposed a framework for database quality. He classified all the database quality factors into four categories. They are Process, Data, Model and Behaviour. Total Quality Management (TQM), Quality Function Deployment (QFD) and Capability Maturity Model (CMM) are used to measure the process quality. Data quality depends on the factors such as redundancy, integrity and age of the data. These two measurements and behaviour quality are not important here because I am asked to write about quality of a database design.
In the words of John A. Hoxmeier (1998) "Data quality is usually associated with the quality of the data values. However, even data that meets all other quality criteria is of little use if it is based on a deficient data model". It means Data model quality has the highest priority when comparing with other quality criteria. Let us consider an example, from the given scenario one resident can avail or provide zero or more assistances. If the developer put this as wrong then however the data, process and behaviour is good there is nothing use with that database. Check points in data mode quality are Representation (flexibility and Interpretability), Semantics (content, scope and understandability), Syntactic (presentation and documentation) and Rules (ease of use and normalization). Apart from all these points client creates boundaries and set rules for the application. So it is very important to follow them.
Answer for the question "Is a correct database design necessarily a quality design"? Is no. Because even the quality of a database design is good if there are any mistakes with data model then it will be wrong. In the same way correct database design also not a quality one. Development process of a quality application is not only depends on the type of application but also on the database design principles. Database design principles are applicable for all type of applications. But the choice is totally depends on the developers and users. In the given scenario it is not important to show all the models to the committee members only ER is sufficient. May be in other scenario EER is suitable.
Producing an efficient database design is the ultimate goal for any database developer. Stanwell Parish Plan is not an exceptional here. So they formed certain groups to implement their plan on the given scenarios. Here my task is to explain them about producing a good quality database design. First and important step for any application is to create a database design. In creating a database design there are some standard modelling tools such as ER, EER and use case modelling. Each model has its own advantages and disadvantages. Depending on the type of application database developer wants to choose a particular model. Not only creating the database design is important but also maintaining the quality of a database design is important. I explained some quality criteria for this purpose. Finally it is a good idea for Stanwell Parish Plan to take the report from the different people for different scenarios. At the end of the day they will have many choices to pick up one.
- Shelley Doll: Create an effective data model for your database: July 2002 online available at: http://articles.techrepublic.com.com/5100-10878_11-5035291.html last accessed at 30/12/2009
- Peter Pin-Shan Chen: The Entity Relationship Model-Toward a unified view of data: International conference on very large databases, September 1975, pp: 22-24
- Thomas M. Connolly, Carolyn E. Begg: Database Systems: A practical approach to design, implementation and management, 4th ed., 2004
- Irwin Levinstein: Enhanced Entity Relationship Modelling, 2001 online available at http://www.cs.odu.edu/~ibl/450/pdf/view-on-line/entrel/eer-all.pdf last accessed at 30/12/2009
- An Oracle White paper: Getting started with Use case modelling, March 2005 online available at http://www.oracle.com/technology/products/jdev/collateral/papers/10g/gswUseCaseModeling.pdf last accessed at 29/12/2009
- John A. Hoxmeier, Typology of database quality factors, Software Quality Journal 7, 1998, pp: 179-193