IT Fundamentals/Database Concepts

This lesson introduces database concepts.

Objectives and Skills

Objectives and skills for the database fundamentals portion of IT Fundamentals certification include:

• Explain database concepts and the purpose of a database.
  • Usage of database
    • Create
    • Import/input
    • Query
    • Reports
  • Flat file vs. database
    • Multiple concurrent users
    • Scalability
    • Speed
    • Variety of data
  • Records
  • Storage
    • Data persistence
• Compare and contrast various database structures.
  • Structured vs. semi-structured vs. non-structured
  • Relational databases
    • Schema
    • Tables
      • Rows/records
      • Fields/columns
      • Primary key
      • Foreign key
      • Constraints
  • Non-relational databases
    • Key/value databases
    • Document databases

Readings

1. Wikipedia: Database
2. Wikipedia: Flat-file database

Multimedia

1. YouTube: Purpose of a Database
2. YouTube: Various Database Structures
3. YouTube: SQL vs NoSQL or MySQL vs MongoDB
4. YouTube: Chapter 14 - IT Fundamentals+ (FC0-U61) Understanding Databases

Activities

1. Research desktop databases. Include:
   • hardware requirements
   • operating system requirements
   • maximum database size
   • maximum number of concurrent users
   • license (proprietary or open source)
   • price
   • market share
2. Select a desktop database to use for the following activities. Download and install the database.
   • LibreOffice Base is available free for anyone from LibreOffice.org. It requires a Java runtime environment (JRE), available free from java.com.
   • Microsoft Access is available free for students through Microsoft: Azure for Education.
3. Complete a tutorial for your selected desktop database.
   • LibreOffice: Using Databases in LibreOffice Base
   • GCF Global: Microsoft Access
4. Use a desktop database.
   • Using a text editor, copy the following list of names and scores and save it as a flat comma-separated values file named scores.csv:
     Name,Score
Joe Besser,70
Curly Joe DeRita,0
Larry Fine,80
Curly Howard,65
Moe Howard,100
Shemp Howard,85
   • Using your desktop database, import scores.csv.
   • Open the imported table and view the results.
   • Use a wizard to create a query for the imported table that displays the table in order by score from highest to lowest.
   • Use a wizard to create a form for the imported table that allows you to view and modify names and scores.
   • Use a wizard to create a report for the imported table using the query created above that displays the table in order by score from highest to lowest.
5. Use relational and non-relational databases to compare and contrast what they do and how they work.
   • SQLite
   • MongoDB
   • Redis

Lesson Summary

Database Concepts

Database

LibreOffice Base

Microsoft Access

OpenOffice Base

MongoDB

Redis

Related tables

• A database is an organized collection of data.
• A database is a collection of schemas, tables, queries, reports, views and other objects.
• A query language is a computer language used to make queries (or questions about data) in databases and information systems.
• A database server is a computer program that provides database services to other computer programs or computers as defined by the client–server model.
• A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
• A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete data from a database.

Relational Databases

• A relational database presents data to the user as a collection of related tables with each table consisting of a set of rows and columns.
• The schema of a database is its structure described in a formal language supported by the database management system (DBMS). The schema describes the tables, records (rows), fields (columns), and other database objects.
• A database table is a collection of related data held in a structured format within a database consisting of fields (columns), and records (rows).
• A database row or record represents a single, implicitly structured data item in a table.
• A database column or field is a set of data values of a particular simple type, one value for each row of the database.
• A primary key uniquely specifies a record within a table. In order for an attribute to be a good primary key it must not repeat.
• A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. Foreign keys do not need to have unique values in the referencing relation.
• A database constraint restricts the data that can be stored in relations. The two principal rules for the relational model are known as entity integrity and referential integrity.

Non-Relational Databases

• Semi-structured data does not obey the formal structure of data models associated with relational databases or other forms of data tables. Instead, tags are used to separate data elements indicating records and fields. Examples include XML and JSON.
• A NoSQL (originally referring to "non SQL" or "non relational") database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases.
• NoSQL database benefits include simplicity and scalability, but lack enforced consistency across the data.
• NoSQL data structures include key-value pairs and document databases.

Database Storage

• Database tables and indexes may be stored on disk in one of a number of forms, including ordered/unordered flat files and a variety of structured binary files.
• Unordered files offer good insertion performance but inefficient retrieval times. Ordered files provide more efficient retrieval but lower insertion efficiency.
• A database index is a data structure that improves the speed of data retrieval operations on a database table. Indexes are used to quickly locate data without searching every row in a database table every time a database table is accessed.
• A hierarchical database is a data model in which the data is organized into a tree-like structure. This model mandates that each child record has only one parent, whereas each parent record can have one or more child records.
• A flat-file database is a database which is stored on its host computer system as an ordinary file.
• File-based database engines have limited performance compared to process-based database engines in regard to:
  • Multiple concurrent users
  • Scalability
  • Speed
  • Variety of data
• Databases may employ storage redundancy through replication (with one or more copies) to increase data availability (both to improve performance of simultaneous multiple end-user accesses to a database object, and to provide resiliency in a case of partial failure of a database).

Database Usage

• Desktop database applications often support different user interface options such as:
  • Tables to hold or access stored data
  • Queries to select and modify table data for forms and reports
  • Forms to display and modify data on screen
  • Reports to display data on paper or in digital documents.
• Desktop database applications often support import and export of a wide variety of data file formats.

Key Terms

export

The automated or semi-automated output of data sets between different software applications.

field

Each column in a table represents a set of data values of a particular simple type, one value for each row of the database.

import

The automated or semi-automated input of data sets between different software applications.

record

Each row in a table represents a set of related data, and every row in the table has the same structure.

scalability

The property of a system to handle a growing amount of work by adding resources to the system.

schema

The structure of a database described in a formal language supported by the database management system (DBMS).

Assessments

• Flashcards: Quizlet: IT Fundamentals - Database Concepts
• Quiz: Quizlet: IT Fundamentals - Database Concepts

See Also

• Database Fundamentals

References

← Applications

IT Fundamentals

Database Use →