Join Adam Wilbert for an in-depth discussion in this video Moving through the database-development life cycle, part of Learning Relational Databases (2014).
If you wanted to build a house, you probably wouldn't just drive over to the site and start hammering together pieces of lumber. Instead, you would first want to 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 could 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 database development, but all too often, a complex database project starts with someone driving to the site and hammering together random bits of wood.
That'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 lifecycle. The database development lifecycle 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 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 it's 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 do it.
Here we're interested in determining all of the tasks that a database is expected to perform. This goes beyond simply the information that is 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 begin translating the requirements into the structure required for a relational database. That means organizing all of 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 relational database system we'll be utilizing. This is probably best described with an example. Let's suppose in the previous phases, we've determined that we want to store information for a bookstore, including the title, author, and publisher of the books that we carry. Let's suppose that we also need to store customer-written book reviews in our database. One of the things that we'll need to determine as part of the logical design phase is, how much space do we want to allow for the reviews. 100 characters probably isn't enough unless we were trying to integrate the system to automatically tweet reviews out through Twitter.
But is 1000 characters enough, or 10000? This is just one example of the kinds of considerations we must make during the logical design phase, where we must determine the constraints and rules 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 the book review data.
There are differences in the data type that must be assigned to the piece of data depending on our 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 we are using. If we're developing in SQL Server, then we'd likely assign the text data type. And in Oracle we'd use something called the CLOB data type, which stands for character large object. 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 need to make decisions about how things should be put together should be at a minimum or even nonexistent. 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 will always 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 a previous assumption or perspective on the project. And you must travel back in the process to accommodate this new understanding or requirement. Now in this course, we'll be concerned with three of the initial steps, the gathering requirements, conceptual design, and logical design phases. It's a process that is largely software-agnostic, requiring not much more than a whiteboard or a notepad and a little thinking. The tasks and methods required for the specific physical design and construction phases will be largely dependent on the relational database management system 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 and drawing up the blueprint for our database's construction.
- What is a database management system (DBMS)?
- Moving through the database development cycle
- Preventing duplicate, inconsistent, and conflicting data entries
- Gathering requirements
- Developing relationships
- Identifying key fields
- Following a naming convention
- Developing the actual database