What you need to know

The first thing you should do when you have a relational “Database”, is knowing what you want, meaning, what should be in the “Database” and what do you want to achieve?
As an example, I create a “Database” of inventory in my cabinets. The inventory consists of songs on CD or casette. These are spread across different cabinets. My mother would always say to me “Clean up your cabinets and sort them neatly alongside each other.” I would have to redo this often. But, If I was to stack it all in a “Database,” I would only need to do it once.

The purpose of my “Database” for me is to find a song in a cabinet and add songs easily. This applies when I search by title, by singer, or by genre.

Define your fields

Once you know what you want, you determine the various fields. Each song has a title, a singer, and a genre, so you need a field with the name “Title,” “Singer” and “Genre.”

What we need to know

A “Location” field is useful for knowing which cabinet a song can be found in. Also since the song can be on a CD or cassette, so a “Type” field can also be useful. You can even add in the date the song was published and give the song a rating.

The next thing you do is place these fields in different tables.

Note that the “Singer” is associated with the “Album” table and also with the “Song” table. This is because our album can contain several songs with different singers.

Determine your primary keys

Now that we have all the fields that we need in our “Database”, write them on a paper. We simplify this by determining what the “Primary keys” and “Relationships” are.

In the “Album” table, we have a “Primary key”, each album is indeed unique. So here we add a field with a “Primary key”, eg “AlbumID”. Because we want to connect this to the “Song” table, we add a related key to this field to this table.

The “Genre” field from the “Genre” table, can be placed in the “Song” table. Each song has a genre. Same goes for the “Type” field from the “Type” table and for the “Location” field from the “Location” table. Each album may have only one type and can only be in one location. So these two fields can be moved to the “Album” table.

In the “Album” table and the “Songs” table, we have the same field, namely “Genre”. How do we solve this? Some albums may consist of more than one genre, so this field is variable. However, because a song can have only one lead singer, it is not a variable, so we create a “Lead Singers” table with “SingerID” as the primary key. Then because we need to connect the “Lead Singers” table to the “Song” table, we add this field to the “Songs” table as related key.

What we have now is, the unique field “AlbumID*” that occurs only once in the “Album” table but, several times in the “Songs” table. We also have the unique field ” SingerID*” that occurs only once in the “Lead Singers” table but, several times in the ” Songs ” table. In short, we have two “one-to-many” “Relationships”.
Now that our format is correct, we can start creating our “Database”. If at a later point, you feel the need to make more changes, it can be done at any time.

Create a new database

Before we make our tables, we must first create a “Database”.
Open ” Access”.

This opens Access in the “Backstage” view where the “New” tab (A) is selected.

Click the “Blank “Database” button (B).

Click the “Browse” button (1) to navigate to a folder on your computer where you want to save the “Database”.

Give the file a name (2), and click the “Create” (3) button.

This opens up the “Database” in “Technical Information Display” (1), with a table (2) and a field in this table, namely, the ID-field (3).

In “Access”, we have a number of display options to set for the structure of our table. “Datasheet View”, is the display option shown above. By clicking on the “View” tab at the top of the “Ribbon”, we can navigate between the different display options.

We will add necessary tables and fields in the next lesson.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats