Intro To Databases

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 online 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;
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 lots of 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 Introduction:A Quick-Start Tutorial on Relational Database Design by Chua Hock-Chuan of Nanyang Technological University

Of course, a Google or YouTube search for Relational Databases will yield hundreds of 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 customer list, or a catalog of their rock collection. 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
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:

Identical data must be entered multiple times, increasing the potential for typographical errors.
Maintaining the list becomes difficult. What if the Accounting department gets a new phone number? The number has to be changed in at least five different places. Any similar change will require changes in several places.
As the company grows the room needed to store the data mushrooms because of all the duplicated data.
(And what if an employee needs to be assigned to more than one project?)

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, monolithic 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 the following. Note that each row, or record, in each table is identified by an ID number. This number must be unique for each row:

employee
person_id	fname	lname	phone	office	dept
1	Joe	Jones	123-4567	Room 119	2
2	Jane	Doe	234-5678	Room 128	2
3	Mike	Nelson	345-6789	Room 127	1
4	René	Johnson	456-7890	Room 210	3
5	Jerry	Ball	322-5684	Room 220	1



← foreign
key →

department
dept_id	name	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_id	client	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
employee	project
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_id	fname	lname	phone	office	dept
1	Joe	Jones	123-4567	Room 119	2
2	Jane	Doe	234-5678	Room 128	2
3	Mike	Nelson	345-6789	Room 127	1
4	René	Johnson	456-7890	Room 210	3
5	Jerry	Ball	322-5684	Room 220	1

compensation
person_id	hourly_rate	commission_rate
1	25.00	.20
2	19.75	.065
3	30.00	.10
4	34.00	.15
5	40.00	.15

Back BYU LiveCode Lessons Gateway

DigHT 310 Introduction to Online Relational Databases

Discussion

Relationships between tables

DigHT 310
Introduction to Online Relational Databases