Chapter 1: Relational Database Concepts
Every organization has data that needs to be collected, managed, and analyzed. A relational database fulfills these needs. Along with the powerful features of a relational database come requirements for developing and maintaining the database. Data analysts, database designers, and database administrators (DBAs) need to be able to translate the data in a database into useful information for both day-to-day operations and long-term planning.
Relational databases can be a bit intimidating at first, even if you’re a specialist in some other informational technology area, such as networking, web development, or programming. This chapter will give you a good overview of current relational and object-relational database concepts. It begins by comparing a database with another tool that most everyone has used—a spreadsheet (also known as the “poor man’s” database). Then you’ll learn about the basic components of a relational database, the data modeling process, and object-relational database features.
Are Spreadsheets Like Databases?
Most people are familiar with some kind of spreadsheet, such as Microsoft Excel. Spreadsheets are easy and convenient to use, and they may be employed by an individual much like a database is used in the enterprise. Let’s look at the features of spreadsheets to see how good of a database tool they actually are.
Similar to databases, spreadsheets are commonly used to store information in a tabular format. A spreadsheet can store data in rows and columns, it can link cells on one sheet to those on another sheet, and it can force data to be entered in a specific cell in a specific format. It’s easy to calculate formulas from groups of cells on the spreadsheet, create charts, and work with data in other ways. But there are many ways in which a spreadsheet is not like a traditional database table:
| Spreadsheet | Database |
|---|---|
| More than one datatype can be stored in a spreadsheet column. | Usually, only one datatype can be stored in a database table column. |
| Cells in a spreadsheet can be defined as a formula, making the contents variable depending on other cells. | Columns in a database table have a fixed value. |
| A spreadsheet has only the physical row number to make it unique, and no built-in way to enforce uniqueness of a given spreadsheet row. | Single rows of a database table are uniquely identified by a unique value (typically a primary key, as described later in this chapter). |
| Usually, only one user can have write access to the spreadsheet at any given time; anyone else is locked out, even if the second user is on a different part of the spreadsheet. | Multiple users can access a database table at the same time, with various combinations of read and write capabilities in different parts of the database. |
| A spreadsheet does not have any built-in transaction-control capabilities, such as ensuring that a group of changes to the sheet is completely applied or not applied at all. The Save button is about the best a spreadsheet can do to simulate transaction control. | A database usually has transaction-control capabilities, making it possible to “roll back” a change if something happened to prevent it from completing successfully (such as a power failure). |
| A corrupt spreadsheet cannot usually be repaired; the entire spreadsheet must be restored from a backup, which may have occurred yesterday, last week, or never! | There are many tools for repairing and recovering databases. |
This is not to say that a spreadsheet isn’t a valuable tool in the enterprise for ad-hoc and “what-if” analyses. Furthermore, most spreadsheet products have some way to connect to an external database as the data source for analysis.
