Chapter 10: Databases

What We’ll Cover >>>

• Database Concepts
• Microsoft Access
• Tables
• Queries
• Forms
• Reports
• Review and Distribution
• Special Concerns

This textbook does not train on database work. Why? Databases are intense, complex, and very detail-oriented repositories of information that requires time, review, and practice that should be offered in a databases-specific course.

At the same time, MS Office provides Access Database as one of the key applications for relational database work. And, much of work in the computing / digital word is reliant on database data, analysis, connection, and data entry. Therefore, this chapter is an overview of MS Access database concepts so that you can understand the breadth of now information/data means in the scheme of computing and information access. Other instructors may choose to add practical activity examples and homework.

Database Concepts

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Data stored in databases might include facts about people, events, things or ideas. This provides information, which generates usable knowledge and actions.

In a way, a database is like a huge warehouse of information that is necessary for data acquisition, analysis, reporting and computations, but the database itself does not do that work. It holds the data for exporting to other applications that can do the work, like Excel for computations, data analysis query tools, etc.

For a database to be useful for basic work, the information in tables of data have to have some relationship to each other, or else one would be comparing apples to giraffes in a futuristic world in which dinosaurs roam a starship. We will focus on relational databases which have ways to link seeming unrelated information from multiple tables/sources. Nonrelational databases also have an important place in the computing workplace but are beyond the scope of this book.

The following are common relational Database Management Systems, (DBMS):

• AWS for Data
• Microsoft Access
• MySQL
• Oracle

Databases are part of our lives in every way, even when we don’t recognize it. If you ever have to ask for a massive amount of information to sort/filter itself and give you a specific response, chances are that you ad dealing in someway with a database or database output. Examples:

For my birthday, I treat myself to 3-4 new (or missing from my Kindle library) books by favorite authors. I go to Amazon’s Kindle section and look for the newest Stephen King book so I can buy it.

• Amazon’s entire collection about Kindles, books, authors, book titles available books, prices, and capability for purchase of a book, is part of their database. I query Amazon in the Kindle Search field and the database reports back (in the list of Stephen King Kindle books) that Stephen King doesn’t yet have a newly published book, and also lists other books. I recognize one I am missing from my Kindle library, and I treat myself to a purchase of it.

I am drafting this OER textbook about Business Technology, and I need more information about accessibility issues for populations with differing accessibility needs.

• I use Google as a search engine and in the search field look for “assistive technology for cognitive disabilities” and Google returns a list of linked web pages that might give me some information I need. Google’s search engine has queried their massive database of websites and topics and reported out search results.

Database preparation

Effective database design is critical, and must be planned out before the database is built. One of the first steps in designing a database is to ask ‘What questions should this database be able to answer?’ The goals of the database should be to store data in a way that makes it easier to answer these questions.

The database should be user friendly, easy to input data into, and able to prevent duplicate records from being created. Redundancy in the data can cause the integrity of the database to be compromised (questions can’t be answered accurately).

Practical database design also has to focus on accuracy and consistency of the data – reliable and standardized formats, with data input controls that ensure consistency. Spelling and naming conventions must be precise and uniform.

Database objects

Databases contain objects to store or reference data. A data object is a structure used to either store or reference data. The database objects we will focus (in order of use) on are:

• Tables: storage of information
• Queries: questions about information
• Forms: input of information
• Reports: output of information

Microsoft Access

Since Microsoft® Access® (MS Access / Access) is widely used in industry, and we are covering Microsoft® Windows® and Microsoft® Office® in this textbook, we will focus primarily on Access as an example of a relational database management system. There are many similarities across relational database management system standards, so the skills we are learning are translatable.

MedAttrib: author-generated. MS Access user interface.

Tables

Tables contain data. Columns and rows of tabular data can be indexed, sorted, filtered, and later queried for finding information in the database. Tables are read by columns (column head for table field data identification) and rows (each row is a separate instance, like a customer or product).

• Table Record: A table’s row of information about a particular item the table is about, like a customer record.
• Table Field: Like a cell in Excel, a field contains one piece of data about one part of the table subject, such as first name of a customer. The item inside that field is its value.
• Table Field property: The format that a table’s field takes, like text for the customer’s first name, or currency for an entry about salary.
• Data Types: Like Excel a database will have types of data, like text, currency, email addresses, a hyperlink, a date/time, a number, and so on.

Connecting tables of data

Relational databases have many tables of information in them. A common standard is that if an item in a table can be broken down into another table, then linked to the main table in a query, it needs to happen. For instance, in a Customer table:

• BotWa031323, LJ, Bothell, 1234 Main Street, WA
• In this record (row) of information, my first and last name is unique to me. The street address is generally also unique, although other people in the table may actually live at the same condo complex. However, what is definitely not unique is the state abbreviation. Yet, when a data entry operator inputs a couple of hundred new rows/records, they may refer to Washington State as WA or Wash in different records. What a mess!
• Another table for just abbreviations of States should be made
• Then, the Customer table and the States table needs be linked so that Customer table would have a dropdown option for the State field, which links to the list of the State abbreviations in the States table.

This kind of relationship-building, which one really needs to learn, absorb, and practice in a database course, looks like this:

In every single table, there should be one column of information that can never, ever have a duplicate – like a field value of a unique customer number. This unique field value would be given a “Primary Key.” Which is an Access database way of ‘locking’ the unique field value from being duplicated.

The States table would also need to have a column of totally unique information field values, so that each state could be considered a unique entry. Because the state abbreviations ARE unique, the abbreviation field values could also act at the primary key for that table.

Then, in a query, those two primary keys could be referenced, and the tables would be ‘linked’ to pull information from them.

Okay, that’s enough techy detail.

Queries

A query in a relational database is just a way of asking the database a question about data that may reside in part in several tables. The idea is that you can query data in a separate customer table, product table, and sales rep table, and get an answer that identifies which sales rep represented which products for which customers. Why? You might want to know which sales reps have the most sales, or which customers bought which products.

Why a database instead of Excel? Because a relational database is designed to hold tens of thousands of customers, thousands of products, hundreds of sales reps, all their data like addresses, ID numbers, payments, tax amounts, dates, etc.

• Excel would be used after you narrow down a lot of data to just what you need, then export it to Excel so you could use the worksheet to calculate formulas about the money, count of products, average order size, etc.
• Access and other databases don’t do calculations, or graphs/charts.
• Excel can’t efficiently process thousands of multiple columns and rows of seemingly redundant data.

TIP: Queries in databases like Amazon and Google. The Amazon query is the Search bar. The query in Google is also the search bar. The search for a Stephen King book at Amazon queries the database, and the Google search for Assistive Technology queries the Google database.

Forms

Database forms are used for inputting data into the tables of the database.

In Excel, one generally works with the actual raw data ranges or tables of data. In a database, however, working with the native table can lead to costly and messy inconsistency mistakes being entered into the fields.

Instead, an efficient and secure database uses one or more input forms for data entry input into tables. For instance, if we refer back to the separate customer table, product table, and sales rep table, what if you are doing data entry for new customer orders that need to populate fields in all THREE of these tables at once?

The form would be designed – using the relationships between the tables (via the Primary keys and other database magic). Then, the data entry specialist would simply open a dashboard form. Fill in all the customer order information, and the database would place the relevant data for each pf those 3 tables into them.

Reports

Access and other relational databases can also ‘report out’ information from tables and queries. It isn’t an optimal use of reporting, but if no calculations or graphics are needed (hey Excel!) but some basic narrowing of data is, Access can generate a moderately attractive form so you can print it, save it as a PDF, or otherwise distribute the report.

Review and distribution

In Word, Excel, and PowerPoint, one big step before distributing the work product (printing, PDF, etc.) is to do a spell check. You can also do this in Access and other databases, but the concern can be that data could be corrupted if a field of information that a query relies upon suddenly has a spell-checked batch of corrections hit it. Be very careful.

Another standard pre-distribution check for Word, Excel, and PowerPoint is an Accessibility check. This is to be sure that tables, inserts like images and SmartArt, and other document items that may go through a screen reader can pass basic accessibility standards.

The database, however, isn’t so easy to do this with. The tables of data, the queries, and the input forms aren’t designed for a real accessibility check. Instead, Microsoft works to add accessibility tools to the overall Access interface.

However, the output Access database report can and should go through an accessibility check, just to verify that any design/formatting images can get alt text and other accessibility-related hiccups can be caught and corrected.

Special concerns

Future of databases

A future of databases is happening now. The chapter mentioned flat databases, which are not relational, and relational databases (Like MS Access). However, as more data is accumulated, needed, and has to be combined and synthesized for analysis, solitary non-relational databases of some complexity are needing to be accessed and used. This is due to so many database systems now being ‘legacy’ on legacy machines. The data is important, but can’t be easily accessed, merged elsewhere, and analyzed. Some U.S. Government database systems have this problem.

Applications are being developed to assemble multi-model databases, which are database management systems that organizes many NoSQL data models using a single backend, a unified query language, and API. They allow for several non-related databases, from different servers and systems, with different forms of data formatting, to be accessed and queried. The query then creates big data which can then be repurposed into a more central and secure database with more processing power access.

It is also likely that in future quarters, students may find themselves treated to very basic SQL coding for relational database work just to learn how to develop a very simple database.