School of Education

Internet Technologies At Work

Chapter 12: Database Programming

After completing Chapter 12, you will know how to:

End of Chapter Labs

Lab Project 12.1: Database Analysis and Design

Imagine that you work for a school or company that needs to create a Web-based discussion forum that will contain confidential discussions by different working groups within your organization. Your employer has assigned you the task of analyzing the needs, specifying the features, and designing the relational database that will contain these discussions. Based on your design, a programming team will write the scripts that power the discussions. Use your word processor to write an essay in which you present your database analysis and design. In formulating this design, consider the following issues:

  1. Needs analysis. Before you can design the data tables that the discussion database will comprise, you need to make an outline of the discussion features your co-workers will require. Make a preliminary list, and consult with your co-workers to make sure the list includes all the discussion features they think they will need.
  2. Data table design. Make a list of the tables that the database will comprise. In each table, indicate which column is the primary key, and tell what columns in other tables, if any, have a foreign key relationship. In designing these tables, remember that you will need columns to identify the author of each message.
  3. Data type definition. Specify the type of data that will reside in each column, such as integer, text, or date/timestamp. Most databases can handle text fields up to 5,000 characters long, which is plenty long for storing discussion messages. If your design permits users to upload file attachments, however, you should plan to store the uploaded files in the server’s file system instead of in the database. It is more efficient to store in the database the path/filename of the uploaded file, rather than the file itself.
  4. Normalization. Beginners tend to create one or two data tables that are very wide, that is, contain many columns. Relational database engines normally work better when you store data in smaller tables that use primary and foreign key relationships. Tables that are too wide tend to store redundant data. Especially when you find a table repeating data, such as storing the user’s first and last name in each discussion record, you should create a secondary table that stores the redundant information more efficiently.
  5. Indexing. For optimal database performance, you should specify the sort order for each table and have the database programmers create indexes based on the sort order. Make the sort order be the order in which the discussions will most often retrieve the data. An example of a sort order is DiscussionID, MessageID, ResponseID.
  6. Walkthrough. Write a walkthrough to make sure you haven’t missed something important. Describe how the typical user will enter the discussion forum and use its options. Tell what will happen in the database as the user posts and responds to messages. If your design permits users to upload file attachments, describe how the database will store them. If the walkthrough uncovers anything missing from your database design, correct the design by supplying the missing elements.
  7. Search engines. To help ensure you have not missed something important, use Google or Yahoo to search for these keywords: discussion forum database design. Perusing other database designs can give you ideas for features you may wish to include, and help guard against omitting something important.

If your instructor asked you to hand in the database analysis and design, make sure you put your name at the top of the essay, then copy it onto a disk or follow the other instructions you may have been given for submitting this assignment.

Lab Project 12.2: Database Backup and Recovery Planning

Without proper database backup and recovery planning, all the hard work and effort you put in to creating a data driven Web site can go down the drain in the event of a hard disk crash, destructive virus attack, or other catastrophic data loss. Imagine that your employer has tasked you with developing a database backup and recovery plan. Use your word processor to write an essay in which you present this plan. In formulating the backup and recovery strategy, consider the following issues:

  1. Backup schedule. In the event of catastrophic data loss, you restore the database from the most recent backup. Every transaction recorded since the timestamp of the backup is lost. In deciding how often to backup the database (e.g., hourly, daily, weekly), take into account how frequently the data tends to change and the financial or operational impact of data loss.
  2. Off site backups. Planning where to keep the backup is just as important as deciding how often to make it. A catastrophic fire or explosion, for example, could destroy all of the data in your building, including all of the backups residing there. It is very important, therefore, to keep extra copies of the backup in secure places off site. Large companies, for example, can keep backups in different buildings, cities, states, or countries. Small businesses can keep backups at home as well as in the office.
  3. Recovery methods. Create a written procedure explaining how your organization will go about recovering data from the backups in the event of different kinds of failures. Include a range of scenarios from a hard disk crash on the server to total destruction of the site housing the server. Thinking about disasters can be depressing. Remember Murphy’s Law: if you have a disaster recovery plan, you will never need it.
  4. Emergency hosting. In the event of a catastrophic disaster that destroys the machine room, you will need a plan for hosting the site at a different location on an emergency basis. Regardless of how you feel about Murphy’s Law, you must include recovery from catastrophic disaster in your planning.
  5. Recovery testing. Unless the recovery procedure works, the backup is of no use. You should recommend that your organization conduct a test in which you format the drives on a spare server, and then rebuild that machine to perform the same function as your production system. Perform tests to make sure the rebuilt system is functioning properly with all the data intact.
  6. Best practices. Consider best practice advice you can discover via Google or Yahoo by searching for the keywords: database backup recovery planning.

If your instructor asked you to hand in the database backup and recovery plan, make sure you put your name at the top of the essay, then copy it onto a disk or follow the other instructions you may have been given for submitting this assignment.