Database Design

Name:

Number:

Course:

Lecturer:

1.0 Introduction

A database is used to store files in the form of tables, queries and even reports. Every file in a database consists of two or more records. A record on the other hand is formed by a collection of fields used to identify table columns. The table rows identify the records. A table having two or more tables can be used to extract a report or query through the help of relationship. A relationship refers to an association that exists between any entities in a relational database. Normalization is also done for various fields to eliminate the aspect of data redundancy within the database. Data redundancy makes it difficult to update records within database files due to the existence of data or information duplication. The duplicated data items within database files on the other hand compromises the integrity of information stored within the database. Therefore, all database elements and their characteristics should be considered in the process of creating a good and reliable database. Good practice of database design should thus be employed in order to come up with the needed structure that meets all the major requirements of database. The use of good practice as discussed in this paper will involve the employment of database design tools such as SQL for server, oracle, class diagrams, entity relationship diagrams among others in the design of either file or server database (Garvey, 2013, VanderLouw & Fisher, 2011).

2.0 The initial database design procedure

The procedure for database design consists of a number of steps. The first step involves the mission statement description together with the objectives of the mission. Second is the examination of the present database followed by the creation of the relevant data structures. The forth step is the determination and setting up of table relationships. Fifth is the determination and definition of rules for business. The next step involves the determination and description of views. The last step is the review of integrity for data (VanderLouw & Fisher, 2011).

2.1 Mission statement and mission objective description

The mission statement description will provide the reason and the focus for the database to be designed. It therefore describes the requirements to be met in addition to offering the course during design. The description in this case encompasses the serious examination of data or the kind of information required to complete the design work. The description of objective for the mission will stand for the common task that database users will be expected to carry out alongside the stored data within the database. The designer in this phase is expected to work with the owners, developers, managers and even users who will each offer insight as well as the description of the project objectives (VanderLouw & Fisher, 2011).

2.2 Database examination

This involves the analysis of the database. This stage can be considered to be optional in the case where by the previous or present database do not exist. The legacy (inherited) or paper based databases may form a number of present databases. The paper based can comprise of forms, folders, and index cards among other things. The analysis of the present database is very significant in providing an insight on how the organization uses information and data. In this case, the developer will pose questions to the stakeholders concerning how they want to use the database,, what they want to be modified or put into action as well as what they like from the existing system. The developer can then proceed to come up with various fields from the answers supplied in response to the questions posed. The generated fields are later evaluated for further improvement by the management team (VanderLouw & Fisher, 2011).

2.3 Creation of relevant data structures

The tables and their associated fields are described in this stage. The various types of keys such as primary keys and others are set together with the description of field requirements. The tables are also examined to ensure there is no data duplication within the fields of data. The definition of primary keys can then be done for unique identification of each record. The specifications of table fields are finally set up followed by interviews with users and the executive team. The structures then become ready for the next stage after review and refinement with users and executive (VanderLouw & Fisher, 2011).

2.4 Relationship determination and establishment

This comprises of database design process where interviews is also carried out with an aim of identifying the exact relationships with data. The connections between tables are also set up by means of primary keys. The possible relationship types include one-to-one, one-to-many (many-to-one and many-to-many relationships which can be shown clearly using entity relationship diagrams (ERDs) (Asha, Kumar & Kumar, 2014, VanderLouw & Fisher, 2011).

2.5 Determination and definition of business rules

The interview is still done here to identify the constraints and set up business rules. The description and implementation of tables for validation are also described in this stage. A good example of specific business rule is the setting of the maximum amount or order not to exceed a given level. Data integrity can be ensured by setting these constraints in place (VanderLouw & Fisher, 2011).

2.6. Determination and description of views

The views are described and determined at this stage. Interviews will also be conducted with the stakeholders in order to know the requirements for viewing and getting the right of entry into information and data. It is advisable to set and generate special views to aid in productivity of every control group given that every group of users will differently use the information. The views are then set in place once the interview process is over (VanderLouw & Fisher, 2011).

2.7 Assess the integrity for data

This will involve assessing every table to ensure that they are correctly designed and with the correct arrangement. Any existing inconsistency is also resolved at this stage with repeated assessment. The assessment and inspection of every field requirements, the review together with improvement are also done. The assessment and validity of associations of tables as well as the confirmation of associations or relationships are also stated. The assessment of business rules as well as the confirmation of constraints is also done here. This stage marks the end of the initial design process (VanderLouw & Fisher, 2011).

3.0. Conceptual, logical and physical database modeling

These forms the three stages in the design process of the database. The design begins with the conceptual modeling for the purpose of appreciating various data entities. The conceptual modeling can make use of unified modeling language (UML) class diagram notation for the design. Next is the logical modeling which includes the insertion for the particulars of the database without the actual implementation. Last is the physical modeling where the interaction of data model with the database is precisely determined. The entity relationship diagrams are used in this case to show the relationship that exists between various entities from different tables (Byrne & Qureshi, 2013, VanderLouw & Fisher, 2011). The entity relationship diagram (ERDs) is used as an abstract approach of database description. The structured query language (SQL) can be used for logical design (Asha, Kumar & Kumar, 2014).

4.0 Conclusion

The design of a functioning database call for strict following of the design procedure described above. The overall design is divided into two categories namely the initial design and the final three step design process consisting of conceptual, logical and physical design. The UML class diagrams, structured query language and entity relationship diagrams are the tools used in the conceptual, logical and physical modeling respectively. The design process should also consider all the database elements such as records, files, entities among others with precise evaluation of each.

Annotated Bibliography

Asha, N., Kumar, M. V., & Kumar, C. B. (2014). Construction of Database Design-A Reverse Engineering Process. International Journal of Computer Science & Information Technologies, 5(2).

This is a very important source for this paper because it explains various aspects of database design process which include the use of logical, conceptual and physical modeling in addition to relationship determination and establishment. The source explains how tools like UML and ERD can be used to accomplish conceptual and physical modeling respectively.

Byrne, B. M., & Qureshi, Y. S. (2013). The Use of UML Class Diagrams to Teach Database Modelling and Database Design. Friday 5th July 2013 University of Sunderland, 11

This source also explains about the various types of modeling in database design. It provides further explanations on the use of SQL statements, UML and ERD tools to achieve logical, conceptual and physical modeling parts of database design.

Garvey, M. (2013). USING ORACLE DEVELOPER TOOLS TO TEACH DATABASE DESIGN. Friday 5th July 2013 University of Sunderland, 25.

The source provides and explains the concepts of database that applies to good practice of database design. The concepts such as files, records, tables, normalization, and data redundancy among many other terms are explained. In addition, a number of tools such as ERD, class diagrams, My SQL are also mentioned.

VanderLouw, T., & Fisher, S. (2011). Highlights: File-based and Server-based Databases, Normalization and Redundancy, Relational Models and Entity Relationship Diagrams (ERD), Database Design, SQL Statements, Reports and Menus (UI).

This source is very crucial since it offers the guide on the steps of database design used in the initial stage. In addition, it also explains a number of database concepts required for good design. The design and modeling tools required for the three stages of the second part of design are explained in this source.