Database table in design view alongside the feature image for Michael Jackson's Album: Thriller on Wikipedia. In this module, you'll learn the fundamentals of storing structured data in a "desktop" database application.

Database 1: A relational database of music albums

Browser note: This particular page is best viewed in Chrome/Chromium

CIT-100: Computer Fundamentals Exploration Module

Since you are familiar now with spreadsheets, we can think of a database as a collection of carefully created spreadsheets that are linked to one another to be able to access any single piece of data very quickly.

A primary goal of a database is to avoid duplicating data while still allowing retrieval of data that may be printed multiple times (The instructor's email address appears on every course overview but we only want to store the precise spelling in one single little spreadsheet cell).

Start Here: Jump to a section

This module was engineered to be worked through sequentially: from top to bottom

check_boxLearning Objectives
bookDatabase learning resources & links
webDownload & install LibreOffce Base
webDownload & open our sample album database

highlightCore Concept : Spreadsheets vs. Databases
motorcycleStep 1: Album table design in "edit" mode
motorcycleStep 2: Entering album data to the table in "open" mode
motorcycleStep 3: Adding a related artist table to the DB
motorcycleStep 4: Entering data in related tables
motorcycleStep 5: Using queries to retrieve data stored in related tables

buildMini Project: Add your favorite albums to the database

prepare yourself mentally

To successfully learn the basics of databases, plan to devote 3-6 hours to studying both this module AND the external documentation links. As an online course, you are the captain of your own ship. If you leave port, make it a good trip, eh?

The maxim: "you get what you pay for" holds true in the computer data world, too. Databases are tricky because when designed correctly, they allow us to maintain highly accurate, easily accessible digital data to many users. This is a very complicated task to undertake and requires care and study to get right.

the rewards, though, are many

The good news is that if you apply your brain to this module by studying the documentation and tutorials, you are adding a powerful tool to your set of employment-worthy skills. Organizations pay folks to help organize and access their precious digital data! You could become that person.

check_boxLearning Objectives

  1. Explain the concept of a database and its relationship to spreadsheets in terms of data structure, manipulation, and use cases
  2. Enter data into a simple database in Libre Office Base containing related tables
  3. Extract information from a database from related tables in a query
  4. Understand how to use external resources to design queries and database commands to accomplish useful tasks

arrow_upward back up to contents

highlightCore Concept : Databases Rock (and they're everywhere)

Perhaps you have never dug around in the mud with a relational database, but you are sure to have looked up some useful tid-bit of data from one within the last day or so. This introductory video steps through some examples of relational databases in action to whet your appetite for designing one of your own!

arrow_upward back up to contents

bookExternal Resources

Extensive video tutorial series on Libre Office Base

A prolific YouTube screen caster named TheFrugalComputerGuy has compiled the most comprehensive tutorial set on Libre Office Base that your instructor could find

Libre Office Base Handbook

Published by the open document foundation, this FANTASTIC and COMPREHENSIVE PDF handbook explores all the essential elements of the Base software package.

Libre office official documentation

While open source software communities generally pride themselves on their excellent free and open documentation, the Libre Office Base documentation decent but is very text-heavy and lacks of screen clippings. This document is most useful for consulting specific tools or functions in the system you know you want to use. It's not a tutorial.

arrow_upward back up to contents

webDownload & install LibreOffce Base (And probably Java 8)

LibreOffice and its Base program are free software!

Libre Office Suite, including Base at no extra charge, is free for download to all major platforms (operating systems). The entire office suite was designed mostly by volunteers working without pay with one another to create a useful alternative to the Microsoft Corporation's MS Office suite (a single license for which Microsoft continues to charge upwards of $100 a pop!)

What is open source software?

Because those who worked on the project care about regular folks having access to high-quality computing tools for free, the entire Libre Office project is open source, and available for sharing and improvement by anybody with a computer without fear of legal destruction by money-hungry tech companies whose business model is based on selling license fees.

What does this software do?

Libre Office is a set of office-related applications that mirror those in the Microsoft Corporation's Office set:

We'll only be using Base for this course, but you are invited to use Libre Office for all your office computing needs since each program is very compatible with all major MS Office applications.

Where can I download this?

Link image to youtube tutorial

Perhaps install Java 8, too

Do I need Java?

Try installing Libre Office, download the sample program (next section), and try opening one of the tables. You'll be prompted with a helpful error message that the computer isn't doing what you asked because it doesn't have a Java Runtime Environment.

If needed, follow this link to Oracle's Java download page and install the appropriate version of the Java Runtime Environment. For Windows 10 users, you want the "Windows X64" version (NOT the x86, since your processor computes with 64-bits per cycle).

Java Standard Edition Runtime Environment Download

The best file to download for Microsoft Corporation's Windows 10 users

databaes table relationships

Mac OSX and Linux users should obviously not download the windows executable :)

Why do I need two programs?

databaes table relationships

Databases are programs that don't have any buttons or boxes that pop up on the screen. One must talk to them only through the keyboard. Handily, LibreOffice Base is software that gives you buttons and menus all wired up to talk to that mysterious "headless" program.

Java is a programming language. It is one of the most successful langauges ever created because it works on every mainstream computing device. Java programs are written in one program, called the Java development kit (JDK). You, however, just need to run Java programs (not compile them), so you just need the Java Runtime Environment (JRE) that was made for your operating system.

arrow_upward back up to contents

webDownload & open our sample album database

This module builds and queries a database about music albums and their lead artists. Once you have LibreOffice Base installed and running download the sample database to a known location on your computer's hard drive. Probably your CIT-100 directory, or a sub-directory you could call databases.

Right-click this link >> Save Link As... >> then select your chosen directory.

collectedAlbums.odb for download (Right-click >> Save Link As..)

To open the practice database, open your file explorer ("Windows Explorer"), locate the directory into which you downloaded this sample database, and double click the file name. This should open the database using LibreOffice Base. You may have to select "Libre Office Base" from an application selection menu, but don't be confused.

Get the spreadsheet version of the album table

You don't "need" this file, but the tutorials reference it and it's nice to have what is being manipulated in the tutorial in front of you. Download and open this in the same way as your database file: except use a spreadsheet program of your choice.

albumTableInSpreadsheet.ods for download (Right-click >> Save Link As..)

arrow_upward back up to contents

motorcycleStep 1: Conceptual overview (Thinking like a database)

Join the CIT-115 class from Spring 2018 as they collectively design a database table structure for storing information about our favorite vinyl records. The core difference between a spreadsheet and a database are explored: databases require data-type-specific columns for storing information to protect the integrity of the table's contents.

Table relationships visualized

Study the following diagram based on the concepts explained in the video.

databaes table relationships

arrow_upward back up to contents

highlightStep 2: Populating our album table with data

With our table structure designed, we're ready to populate each row with data about a single album. Join the class as they choose their favorite albums and Eric adds the data to the hand-written table with commentary.

The second half of the film jumps into cyber land to see the same operation performed digitally inside LibreOffice Base.

arrow_upward back up to contents

highlightStep 3: Don't duplicate data!

We've got duplicated data in our album table since storing data about two albums by the same artist means we have the same artist's name and biographical info stored twice--once for each album. Imagine a database at scale: we might have hundreds of duplicate pieces of data if we only used a single table to store it.

This video segment explores how we can break our large album table into two smaller tables: album and artist. The magic of databases connects these two tables so they can work together to produce any combination of the data fields (columns) we seek.

arrow_upward back up to contents

highlightStep 4: Inserting your own favorite albums int our database

This module step asks you to show off your musical prowess and enter some of your own data about favorite musical albums. Hint: Choose a variety of album types such as current pop, oldies, etc. You can even enter ones you don't like and give them a low rating. This will make your own explorations of queries much richer.

arrow_upward back up to contents

highlightStep 5: Using queries to retrieve data stored in related tables

This is the central step: getting data out of the database with a query. Explore along with Eric how to create a query of the data using the design view in Base.

The SQL database language

Databases speak the SQL language to one another, and to every other computer program that needs to store or retrieve data inside the fancy box. Snobby folks insist on saying: "Ess Que El", but in the real world, folks say "I'm working in see-quel." The query design tool and the query wizard are both handy ways for writing statements in SQL that the database knows how to respond to.

Filter conditions reference

Eric references this table of query operators from the online guide.

Expand a screen shot of the online documentation for Base that lists the various operators and symbols for creating query filters.

Tutorial Video

arrow_upward back up to contents

buildMini Project: Build out our albumTracker database!

project objective

Practice the core skills of this module by designing a few queries to retrieve data that you've entered into the system. Practice preparing that data for sharing in a spreadsheet.

Your product will be a customized version of our sample database developed through this module with your additional queries and data included.

Project guide video

Xander walks through the essential components of this mini-project and even shows a step-by-step for generating your first query of the specification below!

Mini-Project steps

These steps are assuming that you've diligently worked through each step above, tinkering along with Eric in your own copy of the sample database. Specifically, you must have already entered your own album data into the system as laid out in step 4 and be familiar with the query process as Eric explained in step 5

  1. Study relevant sections of the Queries chapter in the LibreOffice Base handbook (also linked in the module resources). This should feel like a review of step 5. Soak up the possibilities of adding criteria for more than one column of data in your query.
  2. Study the Queries page in the official Base documentation online, particularly the section called Formulating filter conditions which shows how to specify which rows you want returned by your query. Tinker as you go! Don't be afraid to make mistakes!
  3. Design queries to retrieve the following information from your database (Eric goes over the first one in the tutorial video below). Include the album's title, rating, release year, artist name, and birth date in each query. Save each query with a logical name so it shows up when you click "Queries" in your database launcher window (the main DB window)

Filter conditions reference

Expand a screen shot of the online documentation for Base that lists the various operators and symbols for creating query filters.

Query Tasks

Query 1: Any albums whose average rating is 4 or higher.

Query 2: Any albums whose title starts with the letter 'T' (Hint: use the keyword LIKE with a * as described in the official documentation)

Query 3: Any album published in the 1990s (Hint: Think of 1990s as greater than or equal to 1990 and less than or equal to 2000)

Query 4: An album by any artist born before 1960

Query 5: Compound Query (a criteria in more than one column): Retrieve any album whose release year was before 2000 and whose average rating is higher than 3.5

Query 6: Tricky one: Find any albums whose average rating an even 5 written by an artist who was born before 1960.

Query 7: Make your own: Based on a review of the data in the system, create a query that meets a criteria of your choosing. Be ambitious! Tinker!

Submission instructions

Document your query output in a spreadsheet

Create a new spreadsheet. Your job is to copy the results of each query into its own tab in the spreadsheet. Rename the tabs so their name tells the user what the query is returning. You'll submit both your spreadsheet and database file to the cloud drive.

module photo

Submit your updated database file and spreadsheet

You have two files to submit: your schnazzy improved Base database and the spreadsheet containing the results of your queries. You only need to make one entry in the index.

Use the universal work submission guide for upload links

Extension ideas:

  1. Add another table to the database just like we created the album and artist table. Perhaps a "hit song" for each album in a hitSong table? Perhaps a record company table that stores information about which label the album was released under? This will require you to add new column to the album or artist table so that the two tables can be related to one another. Check out the handbook's chapter 3, section: "Relationships between tables" for some ideas.
  2. Explore the query wizard and the SQL view for creating queries. Try to reproduce a few of the queries you used in the mini-project using one of these other two query tools.
  3. Create a form for entering data into our album or artist table. Use chapter 4 in the handbook as a guide. This view gives users nice boxes for each column value and allows them to avoid manipulating the spreadsheet-like view for entering data.
  4. Create a report for displaying the results of one of your queries. Use chapter 6 in the Base handbook as a guide. Reports are nicely formatted documents that contain a query's results. If you didn't want to transfer the query results to a spreadsheet, a report allows you to share and publish your database's information inside the LibreOffice Base program itself.

arrow_upwardback up to contents