BYU Home pageBRIGHAM YOUNG UNIVERSITY
  Office of Digital Humanities
Back     BYU LiveCode Lessons Gateway

DigHT 310
Introduction to Online Relational Databases

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.

Discussion

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 RealtyDory 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
Mark Farkle 555-1222 Room 1 Owner/CEO 4-1111

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:

Table 1: employee
FIELD DESCRIPTION
person_id Unique numeric identifier for each employee
fname Employee's first name
lname Employee's last name
office Employee's office number
phone Employee's phone number
dept Which department the employee works in (dept_id from dept table)
Table 2: department
FIELD DESCRIPTION
dept_id Unique numeric identifier for each dept
name Department name
office Department main office number
phone Department main phone number
supervisor ID of supervisor (person_id from employee table)
Table 3: project
FIELD DESCRIPTION
proj_id Unique numeric identifier for each project
client Short name of client
lead Employee in charge of each project (person_id from employee table)
phone Client contact phone number

The data in the tables might look like this:

employee
person_idfname lname phone office dept
1Joe Jones 123-4567 Room 119 2
2Jane Doe 234-5678 Room 128 2
3Mike Nelson 345-6789 Room 127 1
4René Johnson 456-7890 Room 210 3
5Jerry Ball 322-5684 Room 220 1
 
 
← foreign
key →
department
dept_idname office phone supervisor
1 Sales Room 100 4-9876 5
2 Accounting Room 200 4-4321 2
3 Marketing Room 222 4-6543 6
4 Design Room 300 4-4455 7

Relationships between tables

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:

project
proj_idclient lead phone
1 Anderson Lumber 3 801-445-6677
2 Goodwin Realty 10 801-554-3322
3 Hatfield Construction 11 801-667-4455

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:

project_team
employeeproject
1 1
2 2
4 3
1 3
3 1
7 2
8 3

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.

employee
person_idfname lname phone office dept
1Joe Jones 123-4567 Room 119 2
2Jane Doe 234-5678 Room 128 2
3Mike Nelson 345-6789 Room 127 1
4René Johnson 456-7890 Room 210 3
5Jerry Ball 322-5684 Room 220 1
compensation
person_idhourly_rate commission_rate
1 25.00 .20
2 19.75 .065
3 35.00 .10
4 18.00 .15
5 40.00 .15

Back     BYU LiveCode Lessons Gateway
Maintained by Devin Asay.
Copyright © 2005 Brigham Young University.
This page last updated on March 16, 2017 11:44:42.