Database management systems are widely used in businesses, educational institutions and other organizations to manage the information critical to their operations. Today many databases are used on the world wide web to make information available to millions of web users at the click of a mouse. Examples include online stores, government information sites, and the personal information stored by your school, employer or university.
Many of the online database systems used today share two traits in common: they are relational databases and they use some variant of a common query language called SQL (Structured Query Language, usually pronounced ‘sequel’) to access and manage the data they contain.
There are many web sites that give basic information about relational databases. Here are a few:
Wikipedia's article on relational databases.
How Stuff Works: What are relational databases?
Good Overview of Relational Database Design by Michael J. Hernandez of Kansas State University
Of course, a Google or YouTube search for Relational Databases will yield many more sources for learning more about this topic.
Nearly everyone has had experience putting together a list of some kind—an phone list of classmates, a club member address list, or a customer list. Efficient record keeping is fundamental to operating any successful organization. Often such lists might take the form of a simple table, especially for smaller groups. Consider the following list of employees for a fictional company, Acme Co.
|Acme Co. Personnel|
|First Name||Last Name||Phone||Office||Department||Supervisor||Dept Phone||Current Project||Project Lead|
|Joe||Jones||123-4567||Room 119||Accounting||Jane Doe||4-4321||Anderson Lumber||Mike Nelson|
|Jane||Doe||234-5678||Room 128||Accounting||Mark Farkle||4-4321||Goodwin Realty||Dory Michaels|
|Mike||Nelson||345-6789||Room 127||Accounting||Jane Doe||4-4321||Anderson Lumber||Mike Nelson|
|René||Johnson||456-7890||Room 210||Sales||Jerry Ball||4-9876||Goodwin Realty||Dory Michaels|
|Altman||Carmen||567-8901||Room 212||Marketing||William Brawley||4-6543||Anderson Lumber||Mike Nelson|
|Virginia||Campa||678-9012||Room 301||Design||Stuart Lizotte||4-4455||Hatfield Construction||Bob Bower|
|Harold||Reedy||678-0123||Room 307||Design||Stuart Lizotte||4-4455||Goodwin Realty||Dory Michaels|
|Denise||Stalker||345-8765||Room 122||Accounting||Jane Doe||4-4321||Hatfield Construction||Bob Bower|
|Stuart||Lizotte||456-9988||Room 305||Design||Stuart Lizotte||4-4455|
|Dory||Michaels||456-7889||Room 101||Accounting||Jane Doe||4-4321|
|Bower||Bob||345-0908||Room 203||Design||Stuart Lizotte||4-4455||Hatfield Construction||Bob Bower|
|William||Brawley||234-5431||Room 214||Marketing||William Brawley||4-6543|
|Jerry||Ball||322-5684||Room 220||Sales||Jerry Ball||4-9876||Anderson Lumber||Mike Nelson|
At first glance it might seem fine, but there are some major problems that will only become worse as the list grows. For one, there is a lot of duplication of data within the list, which gives rise to at least three big problems:
Of course there are many other problems with this format that we could list (e.g., ease of searching, sorting, creating custom reports, etc.) This type of database is also called a "flat file" database, referring to the two-dimensional layout of the data. Fortunately, smart people have thought about these issues for years. The most widely-used solution is the application of powerful computerized databases. Most large database management systems today use what are called relational databases a concept first developed in a paper written in 1970 by E. F. Codd, a researcher at IBM.
In a relational data model, instead of putting all of the information into a single huge table, the data instead is grouped into smaller tables, each of which describes a single type of object or entity. For example, for Acme Co. we might have one data table for employees, another one for departments, and a third for projects.
Our new tables might be organized something like this:
The data in the tables might look like this:
In this model, you create relationships between classes of objects by including a reference to a unique identifier, or key, for a record in a different table. This unique identifier is known as a foreign key. In the example above you see that in the dept column of the employee table, the department is denoted by a foreign key—the unique identifier for the department from the department table. You can see, for example, that in this database if you changed the phone number for Marketing you would only have to change it once, in the record for the Marketing department in the Department table. The unique identifier would not change, so the employee record would always stay linked to the current data in the department record.
Relationships like those between an employee and a department, above, are what is called a one-to-many relationship, which is the most common type of relationship. They are fairly simple to manage simply by using a foreign key to establish a relationship between one table and another.
What about the project table, and the relationships between employees and projects? It might look something like this:
Note that each project has a one-to-many relationship with the employees table by means of a foreign key in the lead column.
Remember, however, that each project might have more than one employee assigned to it, and each employee might be assigned to more than one project. How do we represent that? This is what is known as a many-to-many relationship. Such relationships can be handled with a relationship table, i.e., a table that has no extra data, but whose only function is to define relationships between other tables. It might look like this:
Finally, there is also a one-to-one relationship, in which each record in the first table is related to one and only one record in the second table. These relationships are also maintained by the use of keys (unique identifiers), that are used to relate records in one table to records in another table. For instance, Acme Co might choose to maintain compensation data in a table separate from the employee records table. Since each compensation package might be unique to each employee, a one-to-one relationship between these tables would be established.