5.2 Compare and contrast various database structures.
📘CompTIA ITF+ (FC0-U61)
A relational database is a type of database that organizes data into tables. Each table contains rows and columns, and the tables are connected to each other through relationships.
This structure allows data to be stored in an organized way, retrieved quickly, and linked between tables without duplication.
Relational databases are the most commonly used type of database in IT systems such as:
- Web applications
- Enterprise software
- User account systems
- Inventory management systems
- Customer databases
Common relational database management systems (RDBMS) include:
- MySQL
- PostgreSQL
- Microsoft SQL Server
- Oracle Database
These systems typically use SQL (Structured Query Language) to manage and query data.
Schema
A schema defines the structure and design of a relational database.
It describes:
- What tables exist in the database
- What columns each table contains
- The data type of each column
- Relationships between tables
- Rules and constraints applied to the data
In simple terms, a schema is the blueprint of the database.
Example (IT System Schema)
A user management database may include tables such as:
- Users
- Departments
- Roles
The schema would define:
- Columns inside each table
- Data types (integer, text, date, etc.)
- Primary keys
- Foreign keys
- Constraints
Why Schema Is Important
A schema helps:
- Maintain data consistency
- Prevent incorrect data storage
- Ensure tables can connect properly
- Provide clear organization of data
Without a schema, the database structure would be unclear and difficult to manage.
Tables
A table is the main structure used to store data in a relational database.
A table is organized as a grid made of rows and columns.
Each table stores data about one specific type of entity.
Example Tables in an IT System
A system managing employee access might contain tables such as:
| Table Name | Purpose |
|---|---|
| Users | Stores user account information |
| Roles | Stores permission levels |
| LoginLogs | Stores login activity |
Each table stores related data grouped together.
Rows (Records)
A row (also called a record) represents one complete entry in a table.
Each row contains data for all fields in that table.
Example Table: Users
| UserID | Username | |
|---|---|---|
| 1001 | admin01 | admin@company.com |
| 1002 | user02 | user02@company.com |
Each row represents one user account.
Key Points
- One row = one data entry
- Rows store the actual records
- Every row usually has a unique identifier
Fields (Columns)
A field (also called a column) represents one specific attribute of the data.
Each column stores a particular type of information.
Example Table: Users
| UserID | Username |
|---|
Fields in this table:
- UserID – unique user identifier
- Username – account name
- Email – user email address
Key Points
- Columns define what type of data is stored
- Every column has a data type
Examples of data types:
- Integer
- Text
- Date
- Boolean
Primary Key
A primary key is a column (or group of columns) that uniquely identifies each row in a table.
Each table normally has one primary key.
Characteristics of a Primary Key
A primary key must be:
- Unique (no duplicate values)
- Not null (every row must have a value)
- Stable (should not change often)
Example
Users Table
| UserID | Username | |
|---|---|---|
| 1001 | admin01 | admin@company.com |
| 1002 | user02 | user02@company.com |
Here:
UserID is the primary key.
Why?
- Each user has a different UserID
- It uniquely identifies each row.
Why Primary Keys Are Important
Primary keys help:
- Identify records uniquely
- Prevent duplicate entries
- Enable relationships between tables
Foreign Key
A foreign key is a column that links one table to another table.
It stores the primary key value from another table.
This creates a relationship between tables.
Example IT System
Table 1: Users
| UserID | Username |
|---|---|
| 1001 | admin01 |
| 1002 | user02 |
Table 2: LoginLogs
| LogID | UserID | LoginTime |
|---|---|---|
| 501 | 1001 | 09:00 |
| 502 | 1002 | 09:05 |
Here:
- UserID in LoginLogs is a foreign key
- It refers to UserID in the Users table
This allows the database to know which user created each login log.
Why Foreign Keys Are Important
Foreign keys help:
- Connect tables together
- Maintain data relationships
- Ensure referenced data exists
This is called referential integrity.
Constraints
A constraint is a rule that controls what type of data can be stored in a database table.
Constraints help ensure that data remains accurate and valid.
They are defined when creating the database schema.
Common Types of Constraints
NOT NULL
Prevents a column from having empty values.
Example:
Username NOT NULL
This means:
- Every record must have a username
UNIQUE
Ensures that all values in a column are different.
Example:
Email UNIQUE
This ensures:
- No two users can have the same email address.
PRIMARY KEY
Ensures:
- Unique value
- No null values
- Identifies each record
Example:
UserID PRIMARY KEY
FOREIGN KEY
Ensures that the value exists in another table.
Example:
UserID FOREIGN KEY REFERENCES Users(UserID)
This ensures:
- A login log cannot exist without a valid user.
CHECK Constraint
Ensures that values follow a specific rule.
Example:
Age >= 18
This prevents invalid values.
How All Components Work Together
A relational database works by combining the following:
| Component | Role |
|---|---|
| Schema | Defines the database structure |
| Tables | Store groups of related data |
| Rows | Individual records |
| Columns | Data attributes |
| Primary Key | Unique identifier |
| Foreign Key | Links tables together |
| Constraints | Maintain data accuracy |
Together, these elements allow databases to:
- Store large amounts of structured data
- Maintain relationships between data
- Ensure data integrity
- Support efficient searching and reporting
Example Relational Database Structure (IT System)
Table: Users
| UserID (PK) | Username | DepartmentID |
|---|---|---|
| 1001 | admin01 | 10 |
| 1002 | user02 | 20 |
Table: Departments
| DepartmentID (PK) | DepartmentName |
|---|---|
| 10 | IT |
| 20 | Security |
Relationship:
- DepartmentID in Users is a foreign key
- It references DepartmentID in Departments
This structure allows:
- One department to have many users
- Each user to belong to one department
Advantages of Relational Databases
Relational databases provide several important benefits:
Structured Organization
Data is stored in clear tables with defined relationships.
Data Integrity
Constraints and keys ensure accurate data.
Reduced Data Duplication
Relationships prevent repeated data storage.
Powerful Queries
SQL allows complex data searches and reports.
Scalability
Can handle large enterprise systems.
Key Exam Points to Remember (CompTIA ITF+)
You should understand:
- What a relational database is
- What a schema defines
- The structure of tables
- The meaning of rows (records) and columns (fields)
- The role of a primary key
- The role of a foreign key
- What constraints do
- How relational databases connect data across tables
