The database development lifecycle defines the route that should be taken to develop a robust database that meets the requirements of an organization. It guides the development from planning through construction.
- [Male Instructor] If you wanted to build a house you'd probably wouldn't just drive over to the site, and start hammering together pieces of lumber. Instead, you'd want to first get the assistance of an architect to help you determine your needs, desires, and budget for your new home, and then draw up a detailed set of construction documents, or blueprints. Once that process is complete, you can take the blueprints to the construction site and begin building the house with the confidence of having a solid plan in place first. The same process applies to a database development project. But all to often, the complex database project starts with someone driving to the site and hammering together random bits of wood.
It's not a good approach when building a house, and it's not a good approach when building a database. Instead, we need a detailed set of construction documents that outline how our database should function, what pieces we'll need, and how to assemble them. We're going to follow a process called the database development life cycle. The database development life cycle includes eight steps that help guide us through the creation of a new database. The steps are planning, requirement gathering, conceptual design, logical design, physical design, construction, implementation and rollout, and finally ongoing support.
Let's take a look at each one of these steps and what they include. Planning is the initial phase, where an organization determines whether they actually have a need for a database, what its goals should be, and the feasibility of successfully accomplishing those goals. High-level studies are performed to determine how long the project might take to accomplish, and whether the cost, and time, and payroll, are offset by the expected return on the investment. In this course, we're going to assume that the planning phase has already been completed and that our database project has been given the green light to proceed. Requirement gathering comes next.
And it places the focus on what the database is to accomplish, but not how to go about doing it. Here we're interested in determining all of the tasks that the database is expected to perform. This goes beyond simply the information that's to be stored, and involves identifying the types of reports that are expected to be produced, as well as, any special needs relating to those rare exceptions, or anomalies, that will cause havoc with your system if you're not prepared for them. In the conceptual design phase, we'll begin translating the requirements into a structure required for relational database. That means organizing all the bits of data that we want to store into themed data tables.
And deciding how best to relate those tables to one another. Logical design is all about taking our plan and making it more specific to the structures of our relational database system. This is probably best described with an example. Let's suppose that in the previous phases, we've determined that we want to store information for a book store. Including the title, author, and publisher of the books that we carry. Let's suppose that we also want to store the customer written book reviews in our database. One of the things that we need to determine as part of the logical design phase, is how much space do we allot for these reviews. 100 characters probably isn't enough unless we're trying to integrate the system to automatically tweet reviews through Twitter.
But is 1,000 characters enough? Or 10,000? This is just one example of the kinds of considerations that we make during the logical design phase. Where we determine constraints and rules that the data must meet, before we allow it to be stored. The physical design phase is up next and it's where you translate the logical design plan into the language of your specific database management system. In fact, you may have realized that the physical design phase is actually the first phase where we're even concerned with the specific DBMS that we're developing in. To illustrate what this phase is about, let's return to our book review data.
There are differences in the data type that must be assigned to that piece of data depending on the chosen DBMS. If we're developing within Microsoft Access, we'll assign that data to a memo, or a long text data type, depending on the version of the software that we're using. If we're developing in SQL Server, then we've likely assigned the TEXT data type. And in Oracle, we'd use something called CLOB data type, which stands for character large objects. Once the physical design is completed, construction is up next. Construction is where you finally get into the database management system.
With your fully developed blueprints in hand, the construction phase simply becomes a process of building what you've outlined. At this point, the needs to make decisions about how things should be put together should be at a minimum, or even non-existent. Implementation and rollout is next. And it involves installing the newly developed database on the client's machines, deploying it to the end users, and providing training on how to use the system. And finally, there will always be a requirement to provide ongoing support. No matter how careful you are in the development process, there's always going to be bug fixes and security patches, as well as performance enhancements and implementing the occasional new feature requests.
It's important to stress that the database development life cycle is not necessarily a linear process. Sometimes information is obtained that alters the previous assumption, or perspective on the project. And you must travel back up the process to accommodate this new understanding, or requirement. In this course, we'll be concerned with the three initial steps, the gathering requirements, conceptual design, and logical design phases. This is a process that's largely software agnostic. Requiring not much more then a whiteboard, or a notepad, and a little bit of thinking. The tasks and methods required for this specific physical design in the construction phases, will be largely dependent on the relational database management system that the database is being developed within.
But at this point, it really doesn't matter which RDBMS you've decided to use. In fact, you might not even know yet which system you'll eventually choose. For now, we're simply considering the initial steps in the development process. And it turns out that it's a pretty universal set of considerations that's applicable to every relational database management system on the market. In other words, we're solely interested in formally discovering our database needs in drawing up the blueprint for our database's construction.
Adam Wilbert covers the basics of relational database design, regardless of whether you use Access, FileMaker, Open Office, or SQL Server. Learn how to prevent data anomalies, gather requirements to plan your design, and develop a conceptual data model—translating your ideas into components like tables, relationships, queries, and views. Plus, learn about logical design considerations that can help you construct a database that is easy to maintain.
- Identify the three rules of relations.
- Summarize the four stages of developing a relational database.
- Describe a strategy one might use to ensure a database remains flexible in terms of the questions a user can ask.
- Explain how to avoid scope creep.
- Recall the characteristics of a Lookup Table.
- Recognize situations in which denormalization would be beneficial.
- Understand the types of relationships modeled by junction tables.
- Define referential integrity.