Today we are going to talk about most important part of the Database. As a fresher, we all have a dream to do something different and we choose DBA as a profession. As my personal experience as a fresher I wanted to be Oracle DBA and today I feel proud to work as DBA. Let’s have look at Database Design part of the database. This phase of database base is my favorite one. If you are fresher then it will most helpful for your future with the help Database Design we will have a depth knowledge of the database and if you are experienced than you may add some good knowledge in knowledge treasure.
Before I did lots of R&D about Database Design. While we design database being DBA we have to think about all aspects of projects. We are going to learn that what are these.
As we all know we can not design without the help of software. While we design we must choose the best software on which we can keep our projects first step to words our organization’s growth. While we choose database software we must keep these point in our mind.
SOFTWARE
Following reviews of the data that would be entered into the database, and the requirements of retrieving the data several criteria were identified that need to be met by the database software. The minimum requirements for the software were:
- Must support the relational database model and some version of the SQL language. This is an industry standard, and as a program that will be spanning several countries and many years adhering to this standard will ensure the longevity and portability of the database. In addition, most database administrators are familiar with some form of SQL and relational databases, so training of an administrator will be relatively easy.
- Need to allow multiple users to access tables simultaneously.
- SQL scripts are the most important while database works. While we go to buy software we must know that it allows running of stored SQL scripts. There are many processes that can be automated with stored scripts, to facility management, updates, editing, and querying of the database. This is especially important if users are accessing the data from the internet. Being able to call and run a stored script is far easier and more efficient than trying to code all the information into a web form.
- Allow restrictions on the data values entered in columns within a table. Being able to restrict the data to certain ranges or values will reduce the possible errors in data entry.
- Creation is the most important part of the database being a project manager we must have to keep this point in our mind that it Allow creation of multiple indexes on a table, as well as unique indexes within a table. Also must be able to create one index on multiple columns. Multiple indexes on a table allow faster sorts and queries based on various parameters. Creating a unique index across multiple columns will prevent entering duplicate data.
- Day by day data is increasing we must think about security as we know how much important is view in any database. Software must allow the creation of views on the data. This allows a minimum amount of data to be stored and a virtually unlimited number of outputs to be created. Views allow display of calculated values, without having to create additional columns in the data tables and have them populated with the calculated values. Having the additional columns can lead to conflicting data within one record of a data table. In addition, views allow multiple tables to be joined together to provide a customized view of the data in the data table.
- Allow inner joins, left outer joins, right outer joins, full outer joins, and multiple joins within a query. The joins are different ways of selecting items from one or more tables, in either a query or a view. The inner join selects only the records that exist in both tables and matches them up. The left outer join selects all of the records from the left table and only the matching records from the right table. The right outer join selects all of the records from the right table and only the matching records from the left table. The full outer join selects all records from both the left and right table and joins the records that match. The non-matching records are joined with null values.
- In the fast technology, world replication proves itself being helped for DBA while choosing software keep this point as a plus point. Have some method of replication between two servers. Since the data is going to be housed on two servers some sort of replication is necessary.
- Software must allow triggers on the data tables. Triggers will allow predetermined actions to be taken when information is entered, edited, or deleted from a data table. Column data checks are an intrinsic form of triggers.
- Without internet is life is impossible . We must know that it Allow data entry from the internet. Most of the data will be entered into the forms from the internet.
- The software runs on different OS like Windows, Linux, Unix etc. As per business budget and requirements, we must choose OS and software. We have multiple options available in the market.
Note:-Based on these requirements the qualified software was examined was Oracle, Informix, Ingres, and PostgreSQL. All of these products met the requirements outlined above.
This is the first step for Database Design after this we will look forward to its hardware requirements in next post.
For more details http://www.mbrs.doe.gov.bz/dbdocs/tech/Design.pdf
