Objectives

Students will be able to:

  • import data from a csv file into a common database application
  • understand that a database table is composed of rows (records) and columns (fields)
  • undertand that fields can have different datatypes eg number, text
  • insert and delete records from an XAMPP database table

Database Tables

A database table has records (rows) and fields (columns).

One of the fields should make every record unique. The field which makes every record unique is called the primary key field.

Look at this database table. Is every record unique? Which field makes each record unique?

id username twitterHandle tweetContent Date
1 JamesBrolin @james01 Yo Yo Yo! Bro! 12/1/2024
2 MaceyT @maceyyy Feeling gorgeous 12/1/2024
3 LowBro @thelowbro Anyone in town for Hula? 12/5/2024
4 LowBro @thelowbro Anyone at all???? 12/6/2024
5 JamesBrolin @james01 Bro Bro Bro! Yo! 12/1/2024
6 MaxStorm @lilmista Coffeeeeeeeee!! 12/1/2024
7 JamesBrolin @james01 Dog ate ma homework! 12/1/2018
8 MollyAnne @mollyanne21 Hello! 12/1/2018
9 MollyAnne @mollyanne21 Hello! 12/1/2018

.csv files

A csv file is a very, very simple database!

It is just a text file which stores lots of data.

Data is arranged in rows and each item is separated by a comma. Here is an example:

Note: in this csv file the first row contains the field/column names!

Creating a Database with a Table

Let's create a database in XAMPP!

Once we have a database, we can create a and add records to it.

As we will see later, a single database can have many tables.

To add records, we will need to do a lot of typing so...

Let's take a shortcut!

We will:

  • create a database in XAMPP
  • import a to help us create a table.
  • learn how to insert, edit and delete records in a database table.

Task1

data file

Download the file and save it in a project folder - don't leave it in your downloads folder!

Task2

Now let's create a database in XAMPP and import the csv file into a database table. The following video will help.

Complete the worksheet as you work through the task.

Observations

Each record in a database table must be unique.

The field that makes every record unique is called the primary key field.

In some tables, a primary key field may already exist. For example, if one of the fields in a table is email address then this can be used as a primary key because everyone has a unique email address.

However, if a table does not have a field which makes each record unique then a common strategy is to:

  1. make a new field called id, or something like that
  2. set it to be of type integer
  3. make it auto-increment

Auto-incrementing means that when you add a record to the table, the database software will take care of the primary key field's value.

Task3

Can you figure out how to:

  1. delete all records that do not have title_year data
  2. insert a new record into the database table - do you need to enter the movieID?
  3. edit an existing record into the database table
  4. delete a field from the database table

Food For Thought

This short course is going to give you an appreciation of databases! Probably without realising it, you interact with databases everyday. If you're a Facebook user, or a Gmail user, or an Instagram user, or a.... web user - you deal with databases!

Homework

essential vocabulary

database import evidence table integer screenshot record text field double data type

SUBSCRIBE

Join my mailing list to receive updates on the latest blog posts and other things.