Understanding Database For Newbies

Database terminology is almost as slippery as the term “object-oriented programming.” The word “database” can be used to describe everything from a single set of data, such as a telephone list, to a complex set of tools, such as SQL Server, and a whole lot in between. This lack of precision isn’t a bad thing, necessarily—it’s just the nature of language—but it’s not particularly useful for our purposes, so I’ll try to be a bit more precise here.

Although relational databases don’t have real-world analogies, most are intended to model some aspect of the real world. I’ll call that bit of the real world the problem space. The problem space, by its nature, is messy and complex—if it weren’t, we wouldn’t need to build a model of it. But it is critical to the success of your project to limit the database system you’re designing to a specific, well-defined set of objects and interactions; only by doing so can you make sensible decisions about the scope of your system.

I’ll use the term data model to mean the conceptual description of the problem space. This includes the definition of entities, their attributes (a Customer, for example, is an entity, and it might have the attributes Name and Address), and the entity constraints (such as, the CustomerName cannot be empty). The data model also includes a description of the relationships between entities and any constraints on those relationships—for example, managers are not allowed to have more than five individuals reporting to them. It does not include any reference to the physical layout of the system.

The definition of the physical layout—the tables and views that will be implemented—is the database schema or just schema. It’s the translation of the conceptual model into a physical representation that can be implemented using a database management system. Note that the schema is still conceptual, not physical. The schema is nothing more than the data model expressed in the terms that you will use to describe it to the database engine—tables and triggers and such creatures. One of the benefits of using a database engine is that you don’t ever have to deal with the physical implementation; you can largely ignore B-trees and leaf nodes.

Once you’ve explained to the database engine what you want the data to look like, using either code or an interactive environment such as Microsoft Access, the engine will create some physical objects (usually, but not always, on a hard disk someplace) and you’ll store data in them. The combination of structure and data is what I’ll refer to as a database. This database includes the physical tables; the defined views, queries, and stored procedures; and the rules the engine will enforce to protect the data.