T4L1

Databases

Introduction

This lesson is designed to expose you to the concept of database technology and it’s use on the Internet today. By the end of this lesson you should be able to:

  • Define what a database is.
  • Define what a database management system is.
  • Recognize the difference between an enterprise database management system and a personal database management system.
  • Define what SQL is.
  • Define benefits of using a database with your web site.
  • Define what a static web site is.
  • Define what a dynamic web site is.

The concept of databases has been around for quite some time. With the advent of the web, it is a natural progression for the database to extend itself from the client-server model to a web-enabled model. Web sites have been using databases to produce their content for a number of years.

Databases

  • Database Definition
  • Database Management System Definition
  • Personal DBMS
  • Enterprise DBMS
  • Databases and SQL

Web Enabled Databases

  • Benefits of Using a Database
  • Static Web Sites
  • Dynamic Web Sites
  • Trends in DBMS for the Web

Additional Resources

CNET Builder.com

[[ link to ]]

ahref.com

[[ link to ]]

Web Developers Virtual Library

[[ link to ]]

Database Definition

A Database Defined

A database stores information in a planned format for future retrieval. This section will explain the basic concepts and terminology behind most databases.

File

[[these graphics were created by Brett Bixler. IST has permission to use them if they want.]]

A database file is a collection of related information. Each file stores information about one topic.

Record

A database consists of one or more records of information. Each record is a collection of information about one person, place, or thing. A record contains all the pieces of information about a particular subject. For example, if you had a database of people you correspond with, each record would contain information on one person. If your database contained 20 records, you would have information on 20 people.

Field

Each record in a database contains different pieces of information called fields. A field is one item contained in the record. It is one component of the record.

In the example of a database of people you correspond with, each record might have a name field, an address field, and a telephone number field.

Field Types

Fields can hold different types of information. The most common types are listed below.

  • TEXT (or character): may contain any character.
  • NUMERIC: A numeric field can legally contain ONLY contain a number and may be used in calculations.
  • DATE: a user may choose different data formats to suit specific information needs. A date may be used in day/date calculations.
  • LOGICAL (sometimes called Boolean): can have one of two values. For example, true or false. Another example is a Yes or No question.
  • OTHER types: picture for graphics, calculation for mathematical manipulation of one or more other fields in the same record, and summary for mathematical manipulation of fields from different records, portal to link to other databases.

Databases contain large groupings of records or data such as product information, sales transactions, statistics, multimedia, and customer profiles. Once data is in a relational or object-oriented database one can do just about anything one wants with it.

Examples of databases encountered in everyday life include:

  • TV listings
  • sports statistics
  • airline reservation information systems
  • census information
  • real estate listings

Database Management System Definition

A Database Management System (DBMS) is a complex software tool that allows you to store, manage, retrieve and organize your data. This tool allow one to organize and store this data in a way that allows us to easily update it, delete it or retrieve it. Many types of DBMS software exist. They range from personal database software such as Microsoft Access or Borland’s Intrabuilder to enterprise software packages such as Oracle, IBM’s DB2, and Informix.

Initially Database Management Systems were designed for large corporations also known as the enterprise. These corporations had the requirement to hold millions of records and have a large number of users access this data concurrently. Additionally, these systems had to be powerful enough to perform complex queries on the data. This traditionally meant that only a very expensive, high-speed computer can do the job without running into problems. Enterprise DBMS software is adept at handling these jobs but come with a high price tag.

Additional Resources
Oracle
[[link to ]]
IBM – DB2 websphere
[[link to ]]
Informix
[[ link to ]]Personal DBMS

With the advent of cheaper and more powerful computing power, software has been designed to closely mimic these enterprise DBMSs capabilities. The term “personal database” software refers to a software package that can be installed on a personal computer and require little storage space to accomplish it’s tasks.

The latest versions of personal database software do not require a database administrator to oversee it’s proper functioning. A powerful personal database package allows for the following:

  • Scripting
  • Storage of procedures
  • Creation of a graphical user interface
  • Reporting capabilities

Personal database software does have several drawbacks. One of them being they still do not support a large number of concurrent users. Essentially, this means if you have a database on an intranet or the Internet and you have many users accessing your database, it may become corrupted and crash.

Before utilizing a database of this nature in a large environment, a proper requirements analysis should be performed in order to determine the number of users you expect to be using the database at any given time.

Additional Resources

Three Easy Desktop Databases for the Web

[[ link to ]]

Enterprise DBMS

The “enterprise” has become to be known as the collection of computers that support the business functions of a large organization. The enterprise DBMS is complex database software package installed and operating from a centrally located server that supports the data needs of this enterprise. In certain network configurations, this server often ties into several computers utilizing large storage devices such as RAID (Redundant Array of Independent Disks) devices. Spanning the data across multiple storage devices in multiple locations is done to improve performance and availability of data. Additionally, this is done in order to avoid having an environmental or man-made catastrophe destroy the data. Today’s “e” businesses are “data centric” which means they rely a great deal upon their organizational data. Denial of accessing this data can negatively impact a business relying on this system.

To ensure this performance is not degraded is the responsibility of a Database Administrator (DBA). This individual is responsible for a variety of corporate critical functions with database availability and survivability being the top two. At any given time a DBA must be able to access the network and restore database activity to an acceptable level. Traditionally, small and medium sized business rarely can afford to keep on staff a DBA professional. In order to alleviate this cost, they often outsource this function to a company that can provide the same level of service but at a lesser cost. This is a major issue to contend with when determining your database solution.

Additional Resources:

Data Survivability White Paper

[[link to ]]

RAID Devices

[[link to ]]

Disaster Recovery

[[ link to ]]

Databases and SQL

The wide varieties of DBMSs today are relational databases that use the SQL for creating database queries. Structured Query Language (SQL) is a relatively simple language that you can use to access most relational databases. Even though many vendors have developed their own variations of SQL, most of the language is standard regardless of what DBMS you use such as Oracle, Sybase, Informix, Microsoft SQL Server, or MS Access. IBM released the first commercial version of SQL in the early 1980s, and almost every relational database vendor has adopted it since then.

A relational database system stores information within tables. These table structures are comprised of rows and intersecting columns of data and are related to one another. These columns are typically called fields. An example of a database table would be one that would hold customer information. You can possibly have fields in your table labeled “name”, “address”, “phone number” etc. All these fields would be related customer information.

Using various SQL commands you are able to retrieve the data you are targeting. SQL commands can range in complexity. Below are some examples of data being selected from a relational database:

SELECT

customer_name

FROM

Customer

This particular SQL query would retrieve all the customer names from a relational database table called “Customer” and display them as a list of names. Using additional SQL command words you can make your queries return data in various formats in a certain order and even calculated.

Additional Resources

SQL Sites

[[link to ]]

Benefits of Using a Database

As stated earlier in this module, relational database technologies have been around for quite some time. The client server model, where a central database (server) serves out data to clients (PCs), is a reliable architecture, which was a natural candidate for evolving to the internet. Once data is stored in a database that content is ready to be conveyed across any medium whether it be a corporate intranet, World Wide Web or even the wireless devices available today such as PDAs and digital cell phones.

However, benefits do exist when we use a database maintain our data. First, once we have data stored in our database it stands separate from the presentation. This allows the various teams responsible for gathering data to not have to worry about how all the data being collected is going to be conveyed. Secondly, and probably the most significant, is reusability. Once your data is stored in a database you can retrieve it time and time again. This is the primary reason we use dynamically driven web pages. Last is the ability to update the information. Once centrally stored, we can update that information with relative ease and all other sources pulling data from our database receive the updates.

Database technology has been getting more reliable and robust through the years. Security features can be put into place to ensure data integrity. When these types of features along with solid network security are put into place it also makes it difficult for hackers or crackers to pry into your data.

Static Web Sites

During the late 1980’s and early 1990’s the internet was made up of a large number of web sites that were “static” in nature. These were “static” because they were primarily created using HTML or similar markup language code. These “static” web sites had to be updated manually any time the content changed.

Aside from animated GIFs or JAVA applets displaying some form of interactivity or animation, these pages did not convey dynamic content. Static web sites are often difficult to maintain, especially if you have content that changes on a regular basis. For example, you may be required to manage a web site for a local sports team. On this site you may have statistics on each of the players. These statistics may also appear on several of the web pages. So each time the team plays, the statistics will change. This will require you to change each page.

Managing a “static” site has the benefit of not requiring knowledge about databases or the cost associated with having an Internet Service Provider (ISP) that supports database technology. But it becomes a timely chore of making simple updates and may create the chance for error when updating multiple pages with the same information.

Dynamic Web Sites

From the mid to late 1990’s to today, the number database driven web sites, also known as “dynamic” web sites have been increasing dramatically. This is primarily due to the ease of utilizing this technology through more stable software.

“Dynamic” web sites are not just sites created using Dynamic HTML (DHTML). They are sites that pull their content from databases and convey this content wrapped within one of the primary markup languages available today such as HTML or XML or programming language such as JAVA. When a visitor comes to your web site the URL they are accessing is basically a “call” or request that goes through the web server to the database. The database retrieves the appropriate data and returns it on the web page. Any information stored in the database becomes a candidate for being viewed on a web page. The benefit being all information that is updated, deleted and inserted can cascade throughout the web site wherever it is being used.

These sites have gained in popularity now that a larger number of users are on the web today and demand current information as know as “real time”. News sites such as USAToday [[link to ESPN [[link to or portals such as Excite [[link to and Yahoo [[link to utilize database technology since visitors use them for up to the minute (or pretty close) information.

These sites are using software such as Oracle’s WebDB or Oracle Application Server, IBM’s Websphere, or Microsoft’s Active Server Pages (ASP). However, these software packages require familiarity with programming and an understanding of how databases interact with a web server. This raises the complexity of managing a web site and often requires professionals with various technical backgrounds to make it all work.

Trends In DBMS for the Web

Due to the value of these “dynamically” driven web sites developers now have access to various tools that are making it possible to create database driven web sites. However, these tools are often designed to fit certain requirements and almost always require some sort of “tailoring.”

There are software packages available that alleviate the developer of having to know in-depth programming, such as Macromedia’s Drumbeat and Allaire’s Cold Fusion. Additionally MS Access 2000 now offers the ability to directly publish database reports to a web server utilizing the new, interactive Data Access Pages. All of these software packages allow you to retrieve data from personal databases or enterprise databases.

Before utilizing any of these packages it is important to determine what are the server side requirements for each of them. Certain software packages interact better when all the components are uniform. For example, at times Microsoft products require a complete Microsoft solution. When a developer uses NT, SQL Server, and Microsoft Internet Explorer to provide a web-based solution, the results tend to be more stable than if you were running SQL server on Unix or using Netscape Server components using Internet Explorer.

Databases Summary

This lesson is designed for you to gain some basic information about databases and their role with the internet. When you are finished with the lesson, you should be able to do the following:

  • Define what a database is.
  • Define what a database management system is.
  • Recognize the difference between an enterprise database management system and a personal database management system.
  • Define what SQL is.
  • Define benefits of using a database with your web site.
  • Define what a static web site is.
  • Define what a dynamic web site is.

A short summary of these topics is listed below. If you do not understand these things, you should review the lesson at least once. If you are still having difficulty, you should consider other sources of information that compliment this lesson, such as textbooks, tutors, and instructors.

Database Definition

A database is an integrated collection data. When we store data in a related form using tables this is typically a relational database.

Database Management System Definition

In order to efficiently create, store, manage and maintain a relational database, database management systems (DBMSs) are used. These are complex software systems available on the enterprise level and personal PC level.

Personal DBMS

The term “personal database” software refers to a database software package that can be installed on a personal computer and require little storage space to accomplish it’s tasks.

Enterprise DBMS

The “enterprise” has become to be known as the collection of computers that support the business functions of a large organization. The enterprise DBMS is complex database software package installed and operating from a centrally located server that supports the data needs of this enterprise.

Databases and SQL

Structured Query Language (SQL) is a relatively simple language that you can use to access most relational databases.

Benefits of Using a Database

  • Data stored in a database is separate from the presentation.
  • Reusability.
  • Easy updating of information.
  • Ability to add security to ensure data integrity.

Static Web Sites

“Static” web sites are primarily created using HTML or similar markup language code. They must be updated manually any time the content changes.

Dynamic Web Sites

Dynamic web sites contain web pages that are generated upon demand from a database.

Trends in DBMS for the Web

New tools are appearing that make it easier to integrate a DBMS with the delivery of web pages. There are software packages available that alleviate the developer of having to know in-depth programming, such as Macromedia’s Drumbeat and Allaire’s Cold Fusion.

1