In lesson 1, we will be defining the concept of relational database and all terminology in order to understand what a database is prior to creating the database in FileMaker Pro.
What is a Database?
A database is a software program that is an organized collection of information on a specific topic. The word “data” can be defined as facts or pieces of information. You may have heard the term “raw data”. Raw data is data that is unprocessed and cannot truly be utilized in a meaningful way. So, is data (in and of itself) information? No, not exactly. Data must become information, and databases are a great tool to make that happen.
There is a difference between data and information. Information is the result of data being processed, analyzed, and formatted in a useful way. Thus, the term “database” means a collection of facts that can provide meaningful information.
Databases are powerful reservoirs that store data efficiently and provide information effectively. These powerful systems are utilized by many organizations worldwide to show statistics, analyze trends, and track information. Most importantly, databases can produce valuable reports that are critical in the decision-making process.
Everywhere you go on a daily basis, you will find examples of databases: schools (to store information regarding grades, registration, and attendance), libraries and bookstores (to store authors’ names, publishers, ISBN numbers, book titles, prices, sales, and customers), businesses (to store employees, departments, clients). There are even personal uses of databases, for example, you can create a database to store the addresses of your family and friends, just like a handwritten address book that contains names, addresses, and phone numbers.
In order to understand database software program, we must first define the terminology that all database software applications have in common. The first term to define is a table. In our examples of databases above, a business that uses a database might create a table to store all the customer information.
What is a Database Table?
A table is a collection of data pertaining to an individual topic. Essentially, tables store records – records of sales, grades, employees, customers, orders, etc. Tables provide structure for the data in order to keep the database organized. Each table consists of fields and records. If you have ever worked with spreadsheet software programs, such as Microsoft Excel, you might already be familiar with rows and columns in a datasheet. The information in a database is stored in a similar way, except we refer to the rows as records, and the columns as fields. A record is one set of fields in a database table. Each record contains data about a single activity, individual, subject, or transaction. In other words, an example of a record would be the customer record for an individual client at a company. Similarly, a record in a bookstore’s database would contain information on an individual book for sale. The single book record would contain fields, such as Book Title, Author, Publisher, ISBN number, price, etc. An employee table would contain records of each employee with fields such as employee last names, employee SSN, date of birth, or date of hire. The field is the basic unit of data in a record. A field holds a specific piece of data.
What is a Relational Database?
A relational database is a database file that contains one or more tables, which consist of fields and records. This gives the database its power…the relationships between the tables.
Table relationships are powerful methods for organizing your data. Using a relationship you can join data in one or more tables based on common field values or a comparison of values in two or more fields.
What is a Primary Key?
When creating a database, it is important to create multiple tables with matching key fields in the tables. That brings us to a very important definition: Primary Key. When talking about the term “Primary Key”, think of the word “unique”. The unique ID field used to identify the records in a table is referred to as the Primary Key in that table. Each record must be uniquely identified. That is, the record for one employee is unique in that no other employee’s record will contain exactly the same data. In databases, we identify each record with a special field called a “Primary Key”, such as an employee ID, customer number, order number, ISBN number. These are all unique for each record in that particular table. This primary key is then matched to a related field (also called common field or matching field)
There are four specific characteristics regarding primary keys:
- Existent - Primary key is set to be automatically entered with a successive Serial Number, which we will learn how to do later in Lesson 2.
- Persistent - Primary key cannot change or be touched. We will prohibit modification of value during data entry.
- Unique - Primary key is distinctive for each record.
- Meaningless - Primary key does not have significance elsewhere.
- i.e. don’t use SSN as a primary key, since that has meaning beyond the database.
There is a definite advantage in creating multiple tables within a database. Table relationships avoid data duplication or data redundancy. There’s no sense retyping information about a customer in the order table when the customer information is already in the customer table. So, we create one table to store customer names, and a second table to store order records placed by those customers. These tables will be related with a matching, common field (related field). In this case, it can be the customer number. The primary key is the customer number field in the customer table, and the related field in the order table is called the foreign key.
What is a Database Relationship?
Relationships provide access to data from one table to another. Using a relationship, you can join data in one or more tables based on common field values, different field values, or a comparison of values in two or more fields.
After you create relationships, you can display and print the data from the related tables. Each occurrence of data is stored in only one table at a time but can be accessed and displayed from any related table. You can make modifications to the data, and the changes will appear in all places where that related data is used.
We will be discussing four types of database relationships in this lesson.
One-to-Many relationship: This relationship can join one record in one table to many other records in another table. In other words, it is a correspondence between data in database tables in which one record in the first table is related to more than one record in another table.
One of the best examples is a customer table and an order table. Let’s say the same customer places several orders on consecutive days of the week. Suppose that the customer is defined with a primary key of Customer ID #101. A separate order is placed each day of the week for this customer. In the Order table, each individual order will be distinctively identified with a unique order number, but for each of the order records, the same Customer ID #101 appears, since the same customer placed the order. So, this describes a one-to-many relationship. A visual depiction of the two tables in this example appears below:
Customers table
Order table
One-to-One relationship: This relationship joins one record in one table to one record in another table.
Many-to-Many relationship: This relationship joins all records in one table to all records in another table.
Cartesian Product Relationship (Cartesian Join): This describes a relationship where any record in one table will match all the records in another table.
In Lesson 2, we will learn how to create the relationships between tables within the relationships graph in FileMaker Pro.
To summarize, the power of a relational database is that it contains all the data you need.
Each occurrence of data is stored in only one table at a time, but can be easily updated and accessed. There is no need to open up multiple files to obtain the data when all the information is located within the one database.
You may refer to the Glossary to obtain more definitions and explanations regarding database terminology from FileMaker Pro.
FileMaker Pro Help – This link will provide valuable information on planning a database
Lesson 1 Key terms: Table, Record, Field, Primary Key, Foreign Key, Relationships
This concludes Lesson 1. Please proceed to Lesson 2, where we will open FileMaker Pro for the first time, and create our database!