10 Steps to SQL Success - dummies

By Allen G. Taylor

The purpose of SQL is to enable developers to create useful and robust databases and database applications. In order to be successful at this, your development effort should pass through a series of stages, each one building on the previous, until you can rightfully celebrate a successful project. Here are ten essential stages that will result in a successful database development effort.

Define the task

At the beginning of a project, the person who is assigning you the task of building a system (the client) has some idea of what is needed. That idea may be very specific, sharp, and concise, or it may be vague, nebulous, and ill-defined. Your first task is to generate and put into writing a detailed description of exactly what the end result of the project, called the deliverables should be. This is the primary task of the definition stage.

In the definition stage, you define the problem to be solved by your database and associated application as accurately as possible. Do this by listening carefully to your client as she describes what she envisions the system to be. Ask questions to clarify vague points. Often, the client will not have thought things through completely. She will have a general idea of what she wants, but no clear idea of the specifics. You must come to an agreement with her on the specifics before you can proceed.

Determine the project scope

The next step in the definition phase is to determine the project scope. How big a job will it be? What will it require in systems analyst time, programmer time, equipment, and other cost items? Is there a deadline?

Determine what it will take to do the job:

Once you’ve determined the project scope, the next question to ask yourself is, ‘Is it possible to do this job within the time and cost constraints placed on it by the client?’ To answer this question, you must do a feasibility analysis. After you complete the analysis, you may decide that the project is not feasible as currently defined. You must either decline it or convince the client to reduce the scope to something more manageable.

Once you’ve determined that the project is doable, you know what kind of staffing you’ll need to do the job. At this point you need to decide who will work on the project. You may be able to do a small job all by yourself, but most development efforts require a team of several individuals. Finding people who have the requisite skills (and who are also available to work on the project when you need them) can be as challenging as any part of the total development effort.

Generate a Statement of Requirements

Once you’ve come to an agreement with your client on what exactly the project will include, you can write a formal Statement of Requirements. The Statement of Requirements is an explicit statement of the database application’s display, update, and control mechanisms.

The Statement of Requirements must be as detailed as possible. It’s essentially a contract between you and your client. You are agreeing on exactly what will be delivered and when it will be delivered. To seal the arrangement, both you and your client should sign the Statement of Requirements, signifying agreement on what you will be responsible for delivering. This step may seem rather formal, but it protects both parties. There can never be any question later as to what was agreed upon.

Create a formal database model

Up until now, the project has primarily been analysis. At this point, you can enter the design phase and make the transition from analysis to design. You most likely know everything you need to know about the problem, so now you can start designing the solution.

Database design is all about models. At this point, you have the users’ data model, which captures the users’ concept of the structure of the database. It includes all the major types of objects, the characteristics of those objects, and how the objects are related to one another. However, it is not sufficiently structured to be the basis for a database design. For that, you need to convert the users’ data model into a model that conforms to one of the formal database modeling systems that have been developed over the past few decades.

The most popular of the formal modeling systems is the entity-relationship model, commonly referred to as the E-R model. With this model, you can capture what the users have told you into a well-defined form that you can then easily translate into a relational database.

Once you have the system in the form of an E-R model, it’s easy to convert into a relational model. The relational model is something that your DBMS understands, and you can create the database directly from it.

Design the database application

Once you’ve designed the database, the design task is only half done. You have a structure that you can now fill with data, but you don’t yet have a tool for operating on that data. The tool you must design now is the database application.

The database application is the part of the total system that interacts with the user. It creates everything that the user sees on the screen. It senses and responds to every key depression that the user makes and every mouse action that the user performs. It prints every report that is read by the user’s coworkers. From the standpoint of the user, the database application is the system.

In designing the database application, you must ensure that it enables the users to do everything that the Statement of Requirements promises that they’ll be able to do. It must also present a user interface that is understandable and easy to use. The functions of the system must appear in logical positions on the screen. The user must easily grasp how to perform all the functions that the application provides.

Build it

Now that you have a database design, you can create the tables, the relationships between them, and the constraints on that data that can be entered into them.

Document it

Everything you’ve done and the reasons for all the decisions you’ve made must be meticulously documented. Hopefully, you’ve been doing this all along. At this stage, all you need to do is put the documentation into its final form. A competent developer who is unfamiliar with the project should be able to pick it up after you have moved on to bigger and better things.

Test everything

Once you’ve built and documented a database system, it may seem like you’re finished and you can enjoy a well-deserved vacation, but you aren’t quite finished yet — the system needs to be rigorously tested. That testing needs to be done by someone who doesn’t think the same way you do. Once the system becomes operational, users will do things to it that you never imagined. They’ll make combinations of selections that you didn’t foresee, enter values into fields that make no sense, and do things backward and upside down. There’s no telling what they’ll do. Whatever unexpected thing the user does, you want the system to respond in a way that protects the database and that guides the user into making appropriate input actions.

Maintain the finished product

After you’ve delivered the system on time and on budget, celebrated, and collected your final payment for the job, your responsibilities aren’t over. Even if the independent tester has done a fantastic job of trying to make the system fail, after delivery it may still harbor latent bugs that show up weeks, months, or even years later. You may be obligated to fix those bugs at no charge, depending on your contractual agreement with the client.

Even if no bugs are found, you may still have some ongoing responsibility. After all, no one understands the system as well as you. As time goes on, your client’s needs will change. Perhaps she’ll need additional functions, or want to migrate to newer, more powerful hardware. These possibilities may require modifications to the database application, and you’re in the best position to do those modifications, based on your prior knowledge. This additional work could mean some nice additional revenue for you.