Database Terms

Many sites on the World Wide Web, as well as many applications on PCs, Macs, smart phones, and tablets use databases. The purpose of this blog post is to define some basic database terms which need to be understood by anyone who wants to create a database.

Database:

A database is an organized collection of information. By that definition even a phone book is a database. But a shoebox of receipts is not a database. Why? Because it’s not organized.

Databases are everywhere. When you use your bank’s ATM you are using a database. When you book a plane ticket you are using a database; in fact, when you buy anything online you are most likely getting information from a database.

Electronic Database:

An Electronic Database is a database stored on a computer or smart phone/ tablet.

Value:

A value is a piece of information stored in a database.

Field:

A field is like a column of values in a spreadsheet. It contains one type of data. The screen shot below has seven fields (ID, Store, First, Last, Phone, Credit Limit and Region).

Record:

A record is like a row of values in a spreadsheet. It contains information about one thing. In the screen shot below each record contains information about a customer.

Table:

A table is a collection of related fields and records and is similar to a worksheet (tab) in a spreadsheet.

Atomic Value:

This term comes from chemistry. Recall that mankind used to think that the atom could not be split. So an atomic value is a value that cannot be split into smaller meaningful values. For example, you could create a field to store a person’s full name (not atomic) or you could create three fields: first name, middle name, and last name. Good database design suggests you create fields that contain atomic values.

Key Field:

Key fields are used to connect the records in one table with the records in another table. The different types of key fields are explained below.

Candidate Key:

A candidate key is a field that uniquely identifies a record. In order to be a candidate, a field must never be blank and must be unique. Its values can’t be duplicated in another record. Each table may have one or many candidate keys. In the screen shot above the ID field and the Store Name field are candidate keys. The phone number field might be a candidate key if the database doesn’t allow blanks phone numbers.

Primary Key:

When setting up the tables, the database designer needs to choose one candidate key that will be used to link to records in other tables. The chosen candidate is “elected” as the primary key. Every table should have one, and only one, primary key. Stated again, the primary key is the chosen field that uniquely identifies a record and is used to create relationships between tables and records.

Foreign Key:

When the president of the United States visits another country he is not the president of that country, but he is the foreign president. Similarly, when the primary key of one table is used in another table it’s a foreign key. Unlike the primary key, the foreign key does not uniquely identify the record, but it is used to create the relationship to the original table. See the screen shot below.

Relational Database:

A relational database is a database that uses key fields to maintain relationships.

Flat File Database:

Flat file databases are databases with only one table. They are non-relational databases. Spreadsheet programs like Excel and Google Docs are great tools for creating flat file databases.

Relational Database Management System:

Relational Database Management Systems are used to create and manage relational databases. Microsoft Access, Oracle, MySQL, and Microsoft SQL Server are Relational Database Management Systems.

Server-Based Database:

Some databases, especially large ones like those used by airlines and big internet retailers, need to be fast and ultra-reliable. They are therefore run on a dedicated database servers. A database server is like a file server, but instead of serving files it delivers database information. Therefore, a server-based database is a database that runs on a dedicated database server. Databases created with Oracle, MySQL, and Microsoft SQL Server are server-based databases.  Conversely, databases created with Microsoft Access are not server-based because they don’t run on a dedicated database server.