Database Fundamentals

Database Fundamentals, Data Types, Database Structures, and Database Models

What Is a Database?

A database is an organized collection of related data that is stored electronically and designed for efficient access, management, modification, and retrieval.

Unlike ordinary files, databases organize information so that it can be searched, updated, and shared quickly by multiple users.

Examples of information commonly stored in databases include:

Nearly every modern organization relies on databases to manage important information.

Why Databases Are Important

Databases provide several advantages over storing data in ordinary files.

Benefits include:

Databases allow organizations to manage very large amounts of information efficiently.

Database Management System (DBMS)

A Database Management System (DBMS) is software used to create, organize, maintain, secure, and access databases.

The DBMS acts as the interface between users, applications, and the database itself.

Common DBMS responsibilities include:

Without a DBMS, managing large amounts of structured data would be extremely difficult.

Examples of DBMS Software

Common database management systems include:

Each DBMS provides similar core functionality while supporting different features and environments.

Flat Files vs Databases

One of the first concepts in database management is understanding the difference between a flat file and a database.

Flat File

A flat file stores information in a single table or file without relationships between different sets of data.

Examples include:

Example:

IDNamePhone
1Alice555-1234
2Bob555-5678

Flat files work well for small amounts of simple information.

Advantages of Flat Files

Disadvantages of Flat Files

Flat files become inefficient as the amount of data increases.

Database

A database organizes related information into multiple interconnected tables.

Advantages include:

Databases are designed for managing large volumes of related information.

When to Use a Database Instead of a Flat File

A database is generally the better choice when:

Types of Data

Information stored by databases can be classified into three categories.

These categories determine how the data is organized.

Structured Data

Structured data follows a predefined format and is organized into rows and columns.

Characteristics:

Examples:

Structured data is the easiest type of data to search and analyze.

Semi-Structured Data

Semi-structured data does not fit neatly into rows and columns but still contains organizational elements called metadata.

Metadata provides descriptive information about the data.

Examples include:

Semi-structured data provides more flexibility than structured data while remaining partially organized.

Unstructured Data

Unstructured data has no predefined organizational format.

Examples include:

Most digital information created today is unstructured.

Because unstructured data lacks a consistent structure, it is generally more difficult to search and analyze than structured data.

Relational Databases

A relational database stores information in multiple related tables.

Relationships connect data across tables using keys.

Characteristics include:

Relational databases are the most common type of database used in business environments.

Database Schema

A schema defines the overall structure of a relational database.

The schema specifies:

A schema serves as the blueprint for the database and should be planned before creating the database.

Tables

A table stores related information within a database.

Each table focuses on one type of information.

Examples include:

Large databases typically contain many related tables.

Rows (Records)

A row, also called a record, represents one complete item in a table.

Example:

Customer IDNameCity
101Alice SmithDallas

The entire row represents one customer record.

Columns (Fields)

A column, also called a field, stores one specific type of information.

Examples:

Every record contains values for these fields.

Primary Key

A primary key uniquely identifies each record within a table.

Characteristics:

Examples:

The primary key ensures every record can be uniquely identified.

Foreign Key

A foreign key links one table to another.

Unlike a primary key:

Example:

Customers Table

CustomerIDName
1001Alice

Orders Table

OrderIDCustomerID
5011001

The CustomerID in the Orders table is a foreign key linking each order to the correct customer.

Relationships

Relationships connect tables to eliminate duplicate information.

Benefits include:

Relationships are one of the defining features of relational databases.

Relational Database Example

Consider an online store.

Instead of storing customer information repeatedly with every order:

Customers Table

Customer IDName
101Alice

Orders Table

Order IDCustomer ID
5001101

The order references the customer using a foreign key, reducing duplicate data.

Nonrelational Databases (NoSQL)

A nonrelational database, often called a NoSQL database, stores information without using traditional relational tables.

NoSQL databases provide greater flexibility for certain types of applications.

Advantages include:

NoSQL databases are commonly used by cloud services, social media platforms, and big data applications.

Document Databases

A document database stores information as complete documents rather than rows and columns.

Documents may contain:

Common document formats include:

Document databases are well suited for applications where records may have different structures.

Key-Value Databases

A key-value database stores information as pairs consisting of:

Example:

KeyValue
UsernameAlice
ThemeDark
LanguageEnglish

Key-value databases are extremely fast and are commonly used for:

Relational vs Nonrelational Databases
Relational DatabaseNonrelational Database
Uses tablesUses flexible structures
Uses SQLOften uses NoSQL technologies
Fixed schemaFlexible schema
Structured dataStructured, semi-structured, or unstructured data
Strong relationshipsMinimal or no relationships
Excellent for transactional systemsExcellent for large-scale and flexible applications
Choosing the Right Database

The choice depends on application requirements.

Choose a relational database when:

Choose a NoSQL database when:

Best Practices

Key Terms

Exam Tips

Database Access, SQL Operations, Permissions, and Database Security

Accessing a Database

A database is useful only if authorized users and applications can retrieve, update, and manage its data.

There are several methods of accessing a database, depending on the type of application and the user’s role.

The four primary access methods covered in CompTIA Tech+ are:

Each method provides different levels of interaction with the database.

Manual Database Access

Manual access occurs when a user enters database commands directly.

This method is commonly used by:

Manual access provides complete control over database operations.

Typical tasks include:

Manual access usually requires knowledge of SQL.

Advantages of Manual Access

Disadvantages of Manual Access

Direct Database Access

Direct access means an application or authorized user connects directly to the database server.

Examples include:

Direct access is commonly used by database administrators and trusted applications.

Programmatic Database Access

Programmatic access occurs when software communicates with a database through application code.

Instead of users entering SQL commands manually, the application sends database requests automatically.

Examples include:

When a customer logs into a website or searches for products, the application retrieves information from the database automatically.

User Interface (UI) Access

Most users interact with databases through a graphical user interface (GUI).

Examples include:

Users interact with buttons, menus, forms, and search boxes while the application communicates with the database in the background.

This approach eliminates the need for users to understand SQL.

Comparing Database Access Methods
Access MethodTypical UserSQL Knowledge Required
ManualDBA, DeveloperYes
DirectAdministrator, Trusted ApplicationUsually
ProgrammaticSoftware ApplicationNo (for end user)
User InterfaceGeneral UserNo
SQL (Structured Query Language)

Structured Query Language (SQL) is the standard language used to communicate with relational databases.

SQL enables users to:

Most relational database management systems support SQL.

SQL Categories

SQL commands are grouped into several categories.

The CompTIA Tech+ exam commonly focuses on:

Data Definition Language (DDL)

DDL commands define the structure of a database.

Common DDL commands include:

CommandPurpose
CREATECreates databases or tables
ALTERModifies existing structures
DROPDeletes databases or tables
TRUNCATERemoves all rows while keeping the table

DDL changes the database structure rather than the data itself.

CREATE Command

The CREATE command creates new database objects.

Examples include:

Creating a table involves defining:

ALTER Command

The ALTER command modifies an existing database object.

Common modifications include:

ALTER is used when the database structure needs to change without recreating the table.

DROP Command

The DROP command permanently removes a database object.

Examples include:

Once dropped, the object and its contents are typically unrecoverable unless restored from a backup.

TRUNCATE Command

The TRUNCATE command removes all records from a table while preserving the table’s structure.

Unlike DROP, the table remains available for future use.

Data Manipulation Language (DML)

DML commands modify the data stored inside tables.

Common commands include:

CommandPurpose
INSERTAdds new records
UPDATEModifies existing records
DELETERemoves records

INSERT Command

Adds new information to a table.

Typical examples:

UPDATE Command

Changes existing information.

Examples include:

DELETE Command

Removes selected records from a table.

Examples include:

DELETE removes specific rows rather than the entire table.

Data Query Language (DQL)

The primary DQL command is:

SELECT retrieves information from one or more database tables.

Examples include:

SELECT is one of the most frequently used SQL commands.

Database Users

A database user is an account authorized to access database resources.

Different users require different permission levels.

Examples include:

Not every user should have full administrative privileges.

Roles

A role is a collection of permissions assigned to users with similar responsibilities.

Benefits include:

Example roles:

Principle of Least Privilege

The Principle of Least Privilege (PoLP) states that users should receive only the minimum permissions necessary to perform their job.

Benefits include:

PoLP is one of the most important database security principles.

Permission Levels

Database permissions may exist at multiple levels.

Server-Level Permissions

Apply to the entire database server.

Examples:

Typically reserved for database administrators.

Database-Level Permissions

Apply to one database.

Examples:

Object-Level Permissions

Apply to individual database objects.

Examples:

Object-level permissions provide the greatest level of control.

Authentication

Authentication verifies a user’s identity before granting database access.

Common authentication methods include:

Strong authentication reduces unauthorized access.

Authorization

After authentication, authorization determines what actions the authenticated user is allowed to perform.

Authorization controls:

Authentication answers “Who are you?” while authorization answers “What are you allowed to do?”

Database Security Best Practices

Protecting a database requires multiple security measures.

Recommended practices include:

Common Database Threats

Databases may be targeted by:

Organizations should regularly review security settings to reduce these risks.

SQL Injection

SQL injection is an attack in which malicious SQL commands are inserted into application input fields.

If the application does not properly validate input, attackers may:

Proper input validation and parameterized queries help prevent SQL injection attacks.

Best Practices

Key Terms

Exam Tips

CRUD Operations, Constraints, Normalization, Transactions, Backup, and Performance

CRUD Operations

Every database application performs four fundamental operations known collectively as CRUD.

CRUD stands for:

These operations allow users and applications to manage information stored in a database.

Create

The Create operation adds new information to the database.

Examples include:

In SQL, the INSERT statement performs the Create operation.

Read

The Read operation retrieves information from the database.

Examples include:

The SELECT statement performs the Read operation.

Read operations are the most frequently executed database tasks.

Update

The Update operation modifies existing records.

Examples include:

The SQL UPDATE statement modifies existing information.

Delete

The Delete operation removes records from the database.

Examples include:

The SQL DELETE statement performs this operation.

Deleting records should be done carefully because important information may be permanently lost if backups are unavailable.

CRUD Summary
CRUD OperationSQL CommandPurpose
CreateINSERTAdd new records
ReadSELECTRetrieve data
UpdateUPDATEModify records
DeleteDELETERemove records

Understanding CRUD is essential because nearly every database application relies on these four operations.

Searching Data

Databases are designed to quickly locate specific information.

Searches may be based on:

Searching large databases manually would be impractical, making database search features essential.

Filtering Data

Filtering displays only records that meet specified conditions.

Examples include:

Filtering allows users to narrow large datasets to only the information they need.

Sorting Data

Sorting arranges records into a meaningful order.

Common sorting options include:

Sorting improves readability and simplifies data analysis.

Indexes

An index is a special database structure that improves the speed of data retrieval.

An index functions similarly to the index in a textbook.

Instead of searching every page, the index directs the reader to the correct location quickly.

Likewise, database indexes allow the DBMS to locate records more efficiently without scanning every row.

Advantages of Indexes

Indexes provide several benefits:

Indexes are especially valuable for large tables containing thousands or millions of records.

Disadvantages of Indexes

Although indexes improve search performance, they also have some trade-offs:

For this reason, indexes should be created only where they provide meaningful performance benefits.

Views

A view is a virtual table created from one or more database tables.

Unlike a regular table, a view does not usually store data itself. Instead, it displays data retrieved from underlying tables.

Views simplify complex queries and can restrict users to seeing only the data they need.

Benefits of Views

Views provide several advantages:

For example, a sales representative might use a view that displays customer names and contact information without exposing salary or payroll data.

Data Integrity

Data integrity refers to the accuracy, consistency, and reliability of data throughout its lifecycle.

Maintaining data integrity ensures that stored information remains trustworthy and free from unintended changes.

Poor data integrity can lead to incorrect reports, duplicate records, and unreliable decision-making.

Maintaining Data Integrity

Organizations maintain data integrity by:

Constraints

A constraint is a rule that restricts the type of data that can be stored in a table.

Constraints help enforce business rules and maintain data integrity.

Common Constraints

Primary Key Constraint

Ensures that each record has a unique identifier.

Characteristics:

Foreign Key Constraint

Ensures that relationships between tables remain valid.

A foreign key value must correspond to an existing primary key in the related table.

NOT NULL Constraint

Prevents a field from being left empty.

Example:

An employee identification number should never be NULL.

UNIQUE Constraint

Ensures that every value in a column is different.

Example:

Two employees should not share the same employee ID or username.

CHECK Constraint

Restricts acceptable values.

Examples:

DEFAULT Constraint

Automatically supplies a value if none is entered.

Example:

If a new employee’s status is not specified, the database might automatically assign Active.

Normalization

Normalization is the process of organizing data to reduce redundancy and improve consistency.

Normalization divides large tables into smaller, related tables while maintaining relationships between them.

This improves storage efficiency and reduces data duplication.

Benefits of Normalization

Normalization helps:

Well-normalized databases are easier to manage and less prone to errors.

Transactions

A transaction is a group of database operations treated as a single unit of work.

Either all operations in the transaction succeed, or none of them are applied.

This prevents the database from being left in an inconsistent state.

Example of a Transaction

When transferring money between bank accounts:

  1. Withdraw funds from Account A.
  2. Deposit funds into Account B.

If either step fails, both operations are canceled to preserve data consistency.

ACID Properties

Reliable database transactions follow the ACID principles.

Atomicity

All parts of a transaction complete successfully, or none are applied.

No partial transactions are allowed.

Consistency

Every transaction leaves the database in a valid state while preserving all defined rules and constraints.

Isolation

Multiple transactions can occur simultaneously without interfering with one another.

Each transaction behaves as though it is the only transaction executing.

Durability

Once a transaction is committed, it remains permanent even if the system crashes or loses power.

Backup

A backup is a copy of database data created to protect against data loss.

Backups help recover information after:

Regular backups are essential for business continuity.

Types of Backups

Full Backup

Copies the entire database.

Advantages:

Disadvantages:

Incremental Backup

Copies only data that has changed since the last backup.

Advantages:

Disadvantages:

Differential Backup

Copies all changes made since the last full backup.

Advantages:

Database Recovery

Recovery restores a database after data loss or system failure.

Recovery may involve:

Organizations should regularly test recovery procedures to ensure backups are usable.

Database Performance

Several factors influence database performance.

These include:

Well-designed databases provide faster response times and a better user experience.

Improving Performance

Common optimization techniques include:

Best Practices

Key Terms

Exam Tips

Database Maintenance, Cloud Databases, Big Data, and Business Intelligence

Database Maintenance

A database requires ongoing maintenance to remain reliable, secure, and efficient throughout its lifecycle.

Database maintenance consists of routine administrative tasks that improve performance, protect data, and ensure the database continues operating correctly.

Without regular maintenance, databases may become slower, consume excessive storage, or experience data integrity and security issues.

Goals of Database Maintenance

The primary goals include:

Database maintenance is a continuous process rather than a one-time activity.

Common Database Maintenance Tasks

Routine maintenance activities include:

These activities help ensure that the database remains healthy and performs efficiently.

Monitoring Database Performance

Database administrators continuously monitor database performance.

Important performance indicators include:

Monitoring allows administrators to detect potential problems before they affect users.

Database Optimization

Optimization improves the efficiency of a database without changing its overall functionality.

Optimization techniques include:

Proper optimization results in faster searches, quicker reports, and improved application performance.

Database Auditing

A database audit records important database activities for security and compliance purposes.

Audit logs may record:

Auditing helps detect unauthorized access and supports forensic investigations after security incidents.

Database Logging

Database logs maintain records of events occurring within the database.

Logs are commonly used for:

Administrators regularly review logs to identify unusual or suspicious activity.

Database Scalability

Scalability is the ability of a database to handle increasing amounts of data, users, and transactions without significant performance degradation.

A scalable database continues to perform efficiently as organizational needs grow.

Vertical Scaling

Vertical scaling (scaling up) improves performance by upgrading the existing server.

Examples include:

Advantages:

Disadvantages:

Horizontal Scaling

Horizontal scaling (scaling out) increases capacity by adding more servers.

Advantages:

Horizontal scaling is commonly used by cloud providers and large enterprise applications.

Cloud Databases

A cloud database is a database hosted on cloud infrastructure rather than on local servers.

Organizations access cloud databases over a network, typically the internet or a private cloud connection.

Cloud databases are widely used because they reduce the need to purchase and maintain physical database servers.

Advantages of Cloud Databases

Cloud databases provide numerous benefits:

Cloud databases are particularly useful for organizations with changing workloads.

Database as a Service (DBaaS)

Database as a Service (DBaaS) is a cloud service model in which a provider manages the database infrastructure.

The provider typically handles:

Customers focus primarily on managing their data and applications.

Benefits of DBaaS

Advantages include:

Examples of cloud database services include managed SQL and NoSQL database platforms offered by major cloud providers.

Data Warehousing

A data warehouse is a centralized repository designed for reporting, business intelligence, and data analysis.

Unlike operational databases that support daily transactions, a data warehouse stores historical information collected from multiple sources.

Organizations use data warehouses to identify long-term trends and support strategic decision-making.

Characteristics of a Data Warehouse

A data warehouse is typically:

Large organizations often consolidate data from many operational systems into a single warehouse.

Data Mart

A data mart is a smaller, specialized portion of a data warehouse.

It is designed for a specific department or business function.

Examples include:

Data marts provide faster access to department-specific information.

Big Data

Big data refers to extremely large and complex collections of data that traditional database systems may struggle to process efficiently.

Sources of big data include:

Organizations analyze big data to discover patterns and make informed decisions.

The Five Vs of Big Data

Big data is commonly described using five characteristics.

Volume

Large amounts of data generated continuously.

Velocity

The speed at which new data is created and processed.

Variety

Different forms of data, including structured, semi-structured, and unstructured data.

Veracity

The reliability, quality, and accuracy of data.

Value

The usefulness of the data for supporting business decisions.

Business Intelligence (BI)

Business Intelligence (BI) refers to technologies and processes used to analyze data and support decision-making.

BI helps organizations transform raw data into meaningful information.

Common BI Activities

Business intelligence solutions commonly perform:

Managers use BI reports to monitor organizational performance and identify opportunities for improvement.

Data Analytics

Data analytics is the process of examining data to identify patterns, relationships, and trends.

Organizations use analytics to:

Data analytics often combines information from multiple databases and data warehouses.

Database Careers

Many IT careers involve working with databases.

Common positions include:

Database Administrator (DBA)

Responsibilities include:

Database Developer

Responsibilities include:

Data Analyst

Responsibilities include:

Data Engineer

Responsibilities include:

Business Intelligence Analyst

Responsibilities include:

Database Best Practices

Successful database environments typically follow these best practices:

Key Terms

Exam Tips

Database Fundamentals

SQL and CRUD

Data Integrity and Performance

Transactions

Security

Cloud and Analytics