Chapter 1
System Development Overview
IN THIS CHAPTER
The components of any database system
The System Development Life Cycle
SQL is the international standard language used by practically everybody to communicate with relational databases. This book is about SQL, but in order for you to truly understand SQL, it must be placed in the proper context — in the world of relational databases. In this minibook, I cover the ground necessary to prepare you to exercise the full power of SQL.
Databases don’t exist in isolation. They are part of a system designed to perform some needed function. To create a useful and reliable database system, you must be aware of all the parts of the system and how they work together. You must also follow a disciplined approach to system development if you’re to have any hope at all of delivering an effective and reliable product on time and on budget. In this chapter, I lay out the component parts of such a system, and then break down the steps you must go through to successfully complete a database system development project.
The Components of a Database System
A database containing absolutely critical information would not be of much use if there was no way to operate on the data or retrieve the particular information that you wanted. That’s why several intermediate components (the database engine, DBMS front end, and database application) take their place between the database and the user in order to do these two things:
- Translate the user’s requests into a form that the database understands.
- Return the requested information to the user in a form that the user understands.
Figure 1-1 shows the information flow from the user to the database and back again, through the intermediate components.
FIGURE 1-1: Information flow in a database system.
I examine each of these components one by one, starting with the database itself.
The database
The core component of a database system is — no surprise here — the database itself. The salient features of a database are as follows:
- The database is the place where data is stored.
- Data is stored there in a structured way, which is what makes it a database rather than a random pile of data items.
- The structure of a database enables the efficient retrieval of specific items.
- A database may be stored in one place or it could be distributed across multiple locations.
- Regardless of its physical form, logically a database behaves as a single, unified repository of data.
The database engine
The database engine, also called the back end of a database management system (DBMS), is where the processing power of the database system resides. The database engine is that part of the system that acts upon the database. It responds to commands in the form of SQL statements and performs the requested operations on the database.
In addition to its processing functions, the database engine functions as a two-way communications channel, accepting commands from the DBMS front end (see the next section) and translating them into actions on the database. Results of those actions are then passed back to the front end for further processing by the database application and ultimate presentation to the user.
The DBMS front end
Whereas the back end is that portion of a DBMS that interfaces directly with the database, the front end is the portion that communicates with the database application or directly with the user. It translates instructions it receives from the user or the user’s application into a form that the back end can understand. On the return path, it translates the results it receives from the back end into a form the user or the user’s application can understand.
The front end is what you see after you click an icon to launch a DBMS such as Access, SQL Server, or Oracle. Despite appearances, what you see is not the database. It is not even the database management system. It is just a translator, designed to make it easier for you to communicate with the database.
The database application
Although it is possible for a person to interact directly with the DBMS front end, this is not the way database systems are normally used. Most people deal with databases indirectly through an application. An application is a program, written in a combination of a host language such as C or Java, and SQL, which performs actions that are required on a repeating basis. The database application provides a friendly environment for the user, with helpful screens, menus, command buttons, and instructive text, to make the job of dealing with the database more understandable and easier.
Although it may take significant time and effort to build a database application, after it’s built, it can be used multiple times. It also makes the user’s job much easier, so that high-level understanding of the database is not needed in order to effectively maintain and use it.
The user
The user is a human being, but one who is typically not you, dear reader. Because you are reading this book, I assume that your goal is to learn to use SQL effectively. The user in a database system typically does not use SQL at all and may be unaware that it even exists. The user deals with the screens, menus, and command buttons of the database applications that you write. Your applications shield the user from the complexities of SQL. The user may interact directly with the application you write or, if your application is web-based, may deal with it through a browser.
It is possible for a user, in interactive SQL mode, to enter SQL statements directly into a DBMS and receive result sets or other feedback from the DBMS. This, however, is not the normal case. Usually a database application developer such as you operates in this manner, rather than the typical user.
The System Development Life Cycle
Producing both a reliable database and an easy-to-use application that fills a real need is a complex task. If you take the task too lightly and build a system without careful preparation, you’re likely to produce something that is neither reliable nor adequately functional.
The best way to accomplish a large, complex task is to break it down into steps, each one of which you can do and do well. To develop a robust and reliable database system, you must go through the seven phases of the System Development Life Cycle (SDLC):
- Definition
- Requirements
- Evaluation
- Design
- Implementation
- Final Documentation and Testing
- Maintenance
Each one of these phases is important. Sometimes schedule pressure may tempt you to shortchange or even skip one of the phases. To do so invites costly errors or a final product that does not meet the needs of the users.
With that last word to the wise out of the way, read on to find out more about each phase of the System Development Life Cycle.
Definition phase
At the beginning of a project, the person who assigns 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 phase, but this phase also includes the following tasks:
- Define the task to be performed. 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’s scope. How big a job will it be? What will it require in terms of systems analyst time, programmer time, equipment, and other cost items? What is the deadline?
- Perform a feasibility analysis. Ask yourself, “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 — a determination of the time and resources it will take to do the job. After you complete the analysis, you may decide that the project is not feasible as currently defined, and you must either decline it or convince the client to reduce the scope to something more manageable.
- Form a project team. 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 just as challenging as any other part of the total development effort.
- Document the task definition, the project scope, the feasibility analysis, and the membership of the project team. Carefully document the project definition, its scope, the feasibility analysis, and the development team membership. This documentation will be a valuable guide for everything that follows.
- Get the client to approve the Definition phase document. Make sure the client sees and agrees with everything recorded in the Definition phase document. It is best to have her sign the document, signifying that she understands and approves of your plan for the development effort.
Requirements phase
In the Definition phase, you talk with the client. This is the person who has the authority to hire you or, if you are already an employee, assign you to this development task. This person is not, however, the only one with an interest in the project. Chances are, someone other than the client will use the system on a daily basis. Even more people may depend on the results generated by the system. It is important to find out what these people need and what they prefer because your primary client may not have a complete understanding of what would serve them best.
The amount of work you must do in the Requirements phase depends on the client. It can be quick and easy if you are dealing with a client who has prior experience with similar database development projects. Such a client has a clear idea of what he wants and, equally important, what is feasible within the time and budget constraints that apply.
On the other hand, this phase can be difficult and drawn-out if the client has no experience with this kind of development, only a vague idea of what he wants, and an even vaguer idea of what can reasonably be done within the allotted time and budget.
As I mention previously, aside from your primary client — the one who hired you — other stakeholders in the project, such as various users, managers, executives, and board members, also have ideas of what they need. These ideas often conflict with each other. Your job at this point is to come up with a set of requirements that everyone can agree on. This will probably not meet everyone’s needs completely. It will represent a compromise between conflicting desires, but will be the solution that gives the most important functions to the people who need them.
The users’ data model
After you have consensus among the stakeholders, you can use their requirements to construct a users’ data model, which includes all the items of interest and how they relate to each other. It also incorporates any business rules that you may have been able to infer from people’s comments. Business rules place restrictions on the items that can be included in a database and on what can be done with those items. See Chapter 2 of Book 1 for a fuller description of the users’ data model.
Statement of Requirements
After you have constructed the users’ data model and verified its accuracy with your client, you can write a formal Statement of Requirements, which is an explicit statement of the database application’s display, update, and control mechanisms. It will answer such questions as
- What will the display look like? What arrangement of items? What color scheme?
- What items will need to be updated, and how will that be done?
- How will users navigate between screens?
- Will selections be made by key depressions? If so, which keys will do what? If not, how will users make selections?
- Will operations be initiated by mouse clicks? If so, which operations? If not, how will users initiate operations?
- What will the maximum acceptable response time to a query be?
The Statement of Requirements must be as detailed as possible because it is 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’ll 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.
Here’s a summary of what you must do in the Requirements phase:
- Interview typical members of all classes of stakeholders in the project.
- Provide leadership in getting stakeholders to agree on what is needed.
- Create a users’ data model of the proposed system.
- Create the Statement of Requirements, which describes in detail what the system will look like and what it will do.
- Obtain client approval of the Statement of Requirements, indicated by a signature and date.
Evaluation phase
Upon completion of the Requirements phase (see the preceding section), it’s a good idea to do some serious thinking about what you’ll need to do in order to meet the requirements. This thinking is the main task of the Evaluation phase, in which you address the issues of scope and feasibility more carefully than you have up to this point.
Here are some important considerations for the Evaluation phase:
- Determine the project’s scope. This step includes several tasks, including
- Selecting the best DBMS for the job, based on all relevant considerations.
- Selecting the best host language.
- Writing job descriptions for all team members.
- Reassess the feasibility of the project and adjust project scope, deadlines, or budget if needed.
- Document all the decisions made in this phase and the reasoning for them.
Determining project scope
Now that you know what you need to do, it’s time to decide on exactly how you’re going to do it. First and foremost, you’ll have to choose what development tools you’ll use. In other words, decide on the best DBMS to accomplish this particular project. To determine this, you need to consider these several factors:
- All DBMS products have limitations in terms of number of tables and records they’ll support, supported data types, and number of users. Considering the size and complexity of the task, which DBMS products will support the current project and any reasonable extensions to it that might be required in the years to come? (Chapter 3 of Book 1 provides some information on the capabilities of several of the most popular DBMS products currently available.)
- Does the client have an institutional standard DBMS that is used for all development? If so, will it work for the current project?
- Is your development team proficient with the selected DBMS? If not, what will it take for them to climb the learning curve and become proficient?
- Is the DBMS you choose supported by a strong company or developer community that will be able to provide upgrades and other services in the coming years?
- Is the best DBMS, from a performance standpoint, affordable to the client from a financial standpoint?
- Does the DBMS have a track record of reliable operation in applications similar to the one you’re planning?
Another consideration is the language that you’ll use to develop the application. You can develop some database applications without writing a single line of program code. These tend to be simple applications that are useful in small organizations. More complex applications require at least some programming. For those more complex applications, you must choose the computer language in which you’ll write it. Some of the same considerations that apply to the selection of a DBMS apply here, including the following:
- Languages have limitations. Choose one that has all the functionality you need.
- Clients sometimes have a language standard. Is their standard language adequate?
- Is your development team familiar with the chosen language?
- Is the language popular enough to have a large number of practitioners? Ongoing maintenance of your code depends on the availability of people who understand it.
With a clear idea of your task and the tools you’ll use to perform it, you can now write detailed job descriptions for everyone who will have a part in the development effort. This important step eliminates any confusion and finger-pointing about who is responsible for what.
Reassessing feasibility
At this stage in the process, you probably have a clearer idea than ever of the assigned task and what it will take to accomplish it. This is a good time to reassess the feasibility of the project. Is it really doable, or are both you and your client too optimistic in thinking that you can achieve everything in the Statement of Requirements, given the DBMS, language, team, budget, and time that you have decided upon?
If the job is not really feasible, it is much better to speak up now than to plunge ahead, burn through your budget and your scheduled time, only to fail to deliver a satisfactory product. At this point, when not much has been invested, you still have some flexibility. You may be able to reduce the scope of the project by deferring until later or even eliminating elements of the project that are not crucial. You may be able to negotiate for a schedule that is not quite so tight, or for a larger budget. You may even decide that the best course for all concerned would be to abandon the project.
At this point, you can bow out relatively gracefully. It will not cost either you or the client very much. If instead, you push ahead with a project that is doomed from the start, you could both suffer substantial loss, both monetarily and in terms of reputation. Making the correct decision here is of critical importance.
Documenting the Evaluation phase
As you should do for every phase, document the steps you took in evaluating development tools such as DBMSs and languages. Place the job descriptions you wrote up with the documentation. Document the feasibility analysis, the conclusions you came to, and the adjustments to the task scope, budget, and schedule that you made, if any.
Design phase
Up until this point, the project has primarily been analysis. Now you can make the transition from analysis to design. You most likely know everything you need to know about the problem and can now start designing the solution.
Here’s an overview of what you do in the Design phase:
- Translate the users’ data model into an ER model. (Remember, the ER model is described in Chapter 2 of Book 1.)
- Convert the ER model into a relational model.
- Design the user interface.
- Design the logic that performs the database application’s functions.
- Determine what might go wrong and include safeguards in the design to avoid problems.
- Document the database design and the database application design thoroughly.
- Obtain client signoff of the complete design.
Designing the database
Database design is all about models. Right now, 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, as well as the characteristics of those objects, and how the objects are related to one another. This is great as far as it goes. However, it’s 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 ER model, which I introduced in Book 1, Chapter 2. In the next chapter of this minibook, I describe the ER model in greater detail. 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.
As you convert the users’ data model into an ER model, you need to make decisions that affect how that conversion is made. Make sure you document your reasoning for why you do things the way you do. At some later time, someone is going to have to modify, update, or add to the database you’re building. That person will need all possible information about why the system is designed the way it is. Take the time to document your reasoning as well as documenting the model itself.
After you have the system in the form of an ER 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.
The database application
After you have designed the database, the design task is only half done. You have a structure that you can now fill with data, but you do not 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 time the user presses a key or uses the mouse. 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, and the user must easily grasp how to perform all the functions that the application provides.
What functions must the application perform, pray tell? Using the DBMS and language that you chose — or that was chosen for you by the client — how will you implement those functions? At this point, you must conceive of and map out the logical flow of the application. Make sure you know exactly how each function will be performed.
Aside from mapping out all the functions that the application will perform, you must also think about protecting the database from inadvertent or intentional harm. People make mistakes. Sometimes they press the wrong key and perform an operation they really didn’t want to perform. Sometimes they enter incorrect data. Sometimes they even want to mess up the database on purpose. You need to design the application in such a way that minimizes the damage that such actions cause. Anticipate that someone might make an inappropriate keystroke, or enter a wrong value, or delete something that should not be deleted. If you anticipate such problems, you can incorporate recovery procedures that will restore things to their proper state.
Documenting the Design phase
The final part of the Design phase is — you guessed it — to document everything carefully and completely. The documentation should be so complete that a new development team could come in and implement the system without asking you a single question about the analysis and design efforts that you have just completed. Take the completed design document to the client and get him to sign it, signifying that he understands your design and authorizes you to build it.
It is critically important to keep the client informed of what you are doing, every step of the way. This gives the client a sense of ownership in the decisions that are made, and makes it less likely that the client will experience an unpleasant surprise when you deliver the final product. The client will know in advance what you intend to deliver and will feel as if he had a major part in shaping its development and its final form.
Implementation phase
Many nondevelopers believe that developing a database and application is synonymous with writing the code to implement them. By now, you should realize that there is much more to developing a database system than that. In fact, writing the code is only a minor fraction of the total effort. However, it is a very important minor fraction! The best planning and design in the world would not be of much use if they did not lead to the building of an actual database and its associated application.
In the Implementation phase, you
- Build the database structure. In the following chapters of Book 2, I describe how to create a relational model, based on the ER model that you derive from the users’ data model. The relational model consists of major elements called relations, which have properties called attributes and are linked to other relations in the model. You build the structure of your database by converting the model’s relations to tables in the database, whose columns correspond to the relation’s attributes. You implement the links between tables that correspond to the links between the model’s relations. Ultimately, those tables and the links between them are constructed with SQL.
- Build the database application. Building the database application consists of constructing the screens that the user will see and interact with. It also involves creating the formats for any printed reports and writing program code to make any calculations or perform database operations such as adding data to a table, changing the data in a table, deleting data from a table, or retrieving data from a table.
- Generate user documentation and maintenance programmer documentation. I’m repeating myself, but I can’t emphasize enough the importance of creating and updating documentation at each phase.
Final Documentation and Testing phase
Documenting the database is relatively easy because most DBMS products do it for you. You can retrieve the documentation that the DBMS creates at any time, or print it out to add to the project records. You definitely need to print at least one copy for that purpose.
Documenting a database application calls for some real work on your part. Application documentation comes in two forms, aimed at two potential audiences:
- You must create user documentation that describes all the functions the application is capable of and how to perform them.
- You must create maintenance documentation aimed at the developers who will be supporting the system in the future. Typically, those maintenance programmers will be people other than the members of your team. You must make your documentation so complete that a person completely unfamiliar with the development effort will be able to understand what you did and why you did it that way. Program code must be heavily documented with comments in addition to the descriptions and instructions that you write in documents separate from the program code.
The testing and documentation phase includes the following tasks:
- Giving your completed system to an independent testing entity to test it for functionality, ease of use, bugs, and compatibility with all the platforms it’s supposed to run on.
- Generating final documentation.
- Delivering the completed (and tested) system to the client and receiving signed acceptance.
- Celebrating!
Testing the system with sample data
After you have built and documented a database system, it may seem like you are finished and you can enjoy a well-deserved vacation. I’m all in favor of vacations, but you’re not quite finished yet. The system needs to be rigorously tested, and that testing needs to be done by someone who does not think the same way you do. After the system becomes operational, users are sure to do things to it that you never imagined, including making combinations of selections that you didn’t foresee, entering values into fields that make no sense, and doing things backward and upside down. There is no telling what they will do. Whatever unexpected thing the user does, you want the system to respond in a way that protects the database and guides the user into making appropriate input actions.
It is hard to build into a system protections against problems that you can’t foresee. For that reason, before you turn the system over to your client, you must have an independent tester try to make it fail. The tester performs a functional test to see that the system does everything it is supposed to do. Also, the tester runs it on all the types of computers and all the operating systems that it is supposed to run on. If it is a web-based application, it needs to be tested for compatibility with all popular browsers. In addition, the tester needs to do illogical things that a user might do to see how the system reacts. If it crashes, or responds in some other unhelpful way, you’ll have to modify your implementation so it will prompt the user with helpful responses.
Quite often, when you modify a database or application to fix a problem, the modification will cause another problem. So after such a modification, the entire system must be retested to make sure that no new problems have been introduced. You might have to go through several iterations of testing and modification before you have a system that you can be very confident will operate properly under all possible conditions.
Finalizing the documentation
While the independent tester is trying everything conceivable (and several things inconceivable) to make your product fail, you and your team still aren’t ready to take that well-deserved vacation. Now is the time for you to put your documentation into final form. You have been carefully documenting every step along the way of every phase. At this time, you need to organize all that documentation because it is an important part of what you’ll deliver to the client.
User documentation will probably consist of both context-sensitive help that is part of the application and a printed user’s manual. The context-sensitive help is best for answers to quick questions that arise when a person is in the middle of trying to perform a function. The printed manual is best as a general reference and as an overview of the entire system. Both are important and deserve your full attention.
Delivering the results (and celebrating)
When the testing and documentation phase is complete, all that is left to do is formally deliver the system, complete with full documentation, to your client. This usually triggers the client’s final payment to you if you are an independent contractor. If you are an employee, it will most likely result in a favorable entry in your personnel file that may help you get a raise at your next review.
Now you and your team can celebrate!
Maintenance phase
Just because you’ve delivered the system on time and on budget, have celebrated, and have collected your final payment for the job does not mean that your responsibilities are 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 do. As time goes on, your client’s needs will change. Perhaps she’ll need additional functions. Perhaps she’ll want to migrate to newer, more powerful hardware. Perhaps she’ll want to upgrade to a newer operating system. All of 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 kind of maintenance can be good because it is revenue that you don’t have to go out hunting for. It can also be bad because it ties you down to technology that, over time, you may consider obsolete and no longer of interest. Be aware that you may have at least an ethical obligation to provide this kind of ongoing support.
Every software development project that gets delivered has a Maintenance phase. You may be required to provide the following services during that phase:
- Fix latent bugs discovered after the client has accepted the system. Often the client doesn’t pay extra for this work, on the assumption that the bugs are your responsibility. However, if you write your contract correctly, their signoff at acceptance protects you from perpetual bug fixing.
- Provide enhancements and updates requested by the client. This is a good, recurring income source.