Lesson video

Hello, my name is Mrs. Holborow and welcome to Computing.

I'm so pleased that you've decided to join me for the lesson today.

In today's lesson, we are going to be investigating some key database terms, and we'll be comparing flat file and relational databases.

Welcome to today's lesson from the unit databases and SQL.

This lesson is called Database Fundamentals.

And by the end of today's lesson, you'll be able to describe the difference between a flat file and a relational database.

Shall we make a start.

We will be exploring these keywords throughout today's lesson.

Should we take a look at them? Record.

Record, a data structure that stores related fields of data for a single object or thing.

Field.

Field, the part of a record that is the category heading for data in a table, sometimes called an attribute.

Primary key.

Primary key, an attribute that uniquely identifies a record in a database table.

Foreign key.

Foreign key, an attribute in one table that is the primary key in another table, and that is used to link the tables together.

Look out for these key words throughout today's lesson.

Today's lesson is broken down into two parts.

We'll start by defining key database terms and then we'll move on to compare flat file and relational databases.

Let's make a start by defining key database terms. Data is fundamental to any computer system.

With data comes the need for a system that is able to store, sort, and search through it.

When large amounts of data need to be collected and organised, they are often stored in a database.

A database is a structured set of data.

It is structured in tables that are easy to search and update.

Here is an example of a flat file database.

This database holds the information about some song ID tracks.

A flat file database is a database that contains a single table.

So you can see here the data is stored in a table with the category headings, TrackID, title, artist and genre.

A table holds a collection of records for a particular theme.

In this case, it is a collection of music tracks.

The naming convention for a table always begins with a lowercase tbl, which stands for table, followed by the name of the table.

So in this example we have tblTracks.

A table contains a collection of records.

Each record contains the data for one object, person, or place.

In this example, each record is a single song track.

So you can see here the first record has the TrackID one, the title is "Float Away," the artist is The Springs, and the genre is pop.

That's one record.

The second record is TrackID two.

The title "In Denial," the artist Rocketts, and the Genre Rock.

The third record has the TrackID three, the title "With You," the Artist Funzo, and the genre pop.

And then finally, the fourth record in this table has the TrackID four, the title is "Us," the artist is The Feast, and the genre is RnB.

A table has a number of fields.

These are the categories for each piece of data in the table.

This table has four fields, TrackID, title, artist, and genre.

Each record in the table needs a unique identifier, something that is a different piece of data from everything else.

A primary key is used to give each record a unique code.

In this example, the TrackID is the primary key, and you can see that the number is unique for each record.

Those numbers are not repeated.

A primary key can never be repeated.

This ensures that each record in the table is unique.

This is very important when dealing with thousands or even millions of records.

Time to check your understanding.

Select all of the properties of a primary key.

A, each value held must be unique.

B, the same primary key can be used for multiple entries.

C, a primary key allows you to identify each unique record.

And D, a primary key must always be a number.

Pause the video whilst you have a think about your answer.

Did you manage to spot them? Let's have a look at the answers together.

A, each value held must be unique is a property of a primary key, and also C, a primary key allows you to identify each unique record.

Time for another check.

Match the key terms to the letters in the diagram.

So the key terms are table, fields and record.

Pause the video whilst you have a think about your answer.

How did you get on? Did you manage to spot them? The table should have been B 'cause we've got tblTracks there.

Fields are A, so the fields are TrackID, title, artist, and genre.

And then lastly, C, which is highlighted is the record, in this case the TrackID four.

State the field name that is a primary key in this table.

Pause the video whilst you have a think.

Did you spot it? That's right, TrackID is the primary key because it's a unique identifier.

Well done.

Okay, so we've come to our first task of today's lesson and you've done a fantastic job, so well done.

Using the example of the flat file database shown in figure one, I'd like you to explain the terms table, record, field and primary key.

Pause the video whilst you complete the task.

How did you get on with the task? Did you manage to explain the key terms? Let's have a look at a sample answer together.

The database table, tblTracks, is used to store information about songs.

The fields, TrackID, title, artist, and genre are used to store the data.

Each song is stored as a record in the table.

TrackID is the primary key in this database table as it is unique and not repeated.

So we've defined some key database terms, let's now move on to compare flat file and relational databases.

Here is a flat file database that has been used to record data about the downloads of tracks from a music website.

Take a few moments to have a look at the data.

Sofia has a question, "What might be a problem with storing data in this way?" Maybe pause the video here and have a think.

Did you spot the problem? In this example, Sara Bibi has downloaded two tracks.

In a flat file database, her details would need to be entered twice, and this is known as data redundancy.

The track "Float Away" has been downloaded three times.

This means that all of this data would need to be entered three times.

This is another example of data redundancy.

Having to enter data multiple times means that the database is at an increased risk of having inaccurate data.

So you can see here The Springs has been entered without a capital S at the start of the word Springs.

It also makes it much harder to update because you would need to change every occurrence of the data item, and this can lead to inconsistent data.

Time to check your understanding.

Which of these statements best describes a flat file database? Is it a A, database that has a single table, B, a database that has multiple tables, C, a database with a limited number of fields, or D, a database that doesn't have a primary key? Pause the video whilst you think about your answer.

Did you select A? Well done.

A flat file database is a database that has a single table.

The most common model for a database is a relational model.

A relational database contains more than one table.

The data in the table is linked using what we call relationships.

Relational databases can be used to solve some of the issues that occur in flat file databases.

Relationships can be one-to-one, one-to-many, or many-to-many.

A one-to-one relationship means that one record in a table relates to a single record in another table.

A one-to-many relationship means that one record in a table relates to multiple records in another table.

A many-to-many relationship means that multiple records in a table relate to multiple records in another table.

Note in relational databases, you cannot directly implement many-to-many relationships.

However, during the early stages of database design, you'll encounter many examples of this type of relationship.

Time to check your understanding.

One record in a table relating to multiple records in another table is, A, a one-to-one relationship, B, a one-to-many relationship, or C, a many-to-many relationship? Pause the video whilst you have a think.

Did you select B? Well done.

One record in a table relating to multiple records in another table is an example of a one-to-many relationship.

The downloads database needs to be stored in a relational database.

So you can see here we now have three tables.

We have our original table tracks, we have a table called table downloads, and we have a table called table members.

Keeping the database as a flat file database would cause issues with inconsistency and data redundancy.

This is because each track can be downloaded multiple times and each member can have multiple downloads of different tracks.

What relationships are being formed here? Is it a one-to-one, B, one-to-many, or C, many-to-many? Pause the video whilst you have a think.

Did you spot them? Well done.

We are forming one-to-many relationships here.

Look carefully at the arrows and how the tables are connected.

The other benefit of a relational database is that all the data can be updated at the original source.

Data doesn't need to be repeated and relationships can be made using the source table's unique identifier.

So you can see here in table downloads we have DownloadID, TrackID, MemberID.

So these TrackID and MemberIDs link out to other tables.

A primary key is unique to the original source of the data and it cannot be repeated.

When you link to a source table's primary key, you use what's called a foreign key.

A foreign key can be repeated because it is a link back to the primary key in the source table.

Using the foreign key, you can go to the table tracks table and find the linked track.

So here on table downloads, the first download, DownloadID one has the TrackID one.

If we go to table tracks, TrackID One is the title "Float Away" by The Springs.

You can also use the foreign key for the member to find out who downloaded that track at that time on that day.

So you can see that we are looking for MemberID one.

And in the table members, MemberID one is Sara Bibi.

The music database now has three tables, all are linked together using relationships.

Data isn't repeated because it's linked to the source table's primary key using the foreign key.

So you can see here the TrackID, which is a primary key in table tracks is also a foreign key in table downloads.

And MemberID which is a primary key in table members is a foreign key in table downloads.

Okay, we're moving on to our second tasks of today's lesson.

I'd like you to start by explaining why you may decide to use a relational database rather than a flat file database.

Pause the video whilst you think about your answer.

How did you get on? Did you manage to explain why you may decide to use a relational database rather than a flat file database? Well done.

Let's have a look at a sample answer together.

You may decide to use a relational database instead of a flat file because it's better for keeping things organised.

In a flat file database, all of the data is stored in one big table, which can get confusing if there's a lot of it.

You might end up repeating the same information over and over, which leads to data redundancy.

A relational database lets you split data into smaller tables that link together.

This means no repeated data and it's easier to update.

It's also better at stopping mistakes as the data is only entered once.

Remember, if you need to pause your video here and make any corrections, you can do that now.

For part two, I'd like you to look at the structure of the following relational database.

In the table downloads table, state whether the fields DownloadID, which is D-O-W-N-L-I-D, TrackID, and MemberID are primary or foreign keys.

Pause the video whilst you look carefully at the tables and answer the questions.

How did you get on? Did you manage to work out if they were primary or foreign keys? Well done.

So DownloadID is a primary key in the table downloads.

It's unique to this table and it's not repeated.

TrackID is a foreign key, which comes from the table tracks.

And MemberID is also a foreign key, which comes from the table members.

Okay, we've come to the end of today's lesson, database fundamentals, and you've done a fantastic job, so well done.

Let's summarise what we've learned together during today's lesson.

Data is fundamental to any computer system.

With data comes the need for a system that is able to store, sort, and search through it.

A database is an organised collection of data.

Databases can be as simple as a single file and as complex as a set of related tables, each with multiple records, fields, and keys.

I hope you've enjoyed today's lesson and I hope you'll join me again soon.

Bye.