Essential Database Concepts and Administration

Posted on Mar 17, 2026 in Computers

Database Normalization

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It divides large tables into smaller tables and establishes relationships between them.

Objectives of Normalization

  • Remove data redundancy (duplicate data).
  • Improve data consistency.
  • Make database structure efficient.
  • Avoid update, insert, and delete anomalies.

Types of Normal Forms

  1. First Normal Form (1NF)
    • Each field contains atomic (indivisible) values.
    • No repeating groups.
  2. Second Normal Form (2NF)
    • Table must be in 1NF.
    • All non-key attributes must depend on the entire primary key.
  3. Third Normal Form (3NF)
    • Table must be in 2NF.
    • No transitive dependency (non-key attributes depend only on the primary key).

ACID Properties

ACID ensures reliable database transactions:

  • Atomicity – Transaction happens completely or not at all.
  • Consistency – Database remains valid before and after transaction.
  • Isolation – Transactions do not interfere with each other.
  • Durability – Changes remain even after system failure.

Three-Tier Architecture

The Three-Tier Architecture divides an application into three layers:

  1. Presentation Layer – User interface (client).
  2. Application Layer – Business logic and processing.
  3. Data Layer – Database server.

Advantages:

  • Better security
  • Easy maintenance
  • Scalability

DBMS Languages

DBMS languages are used to define, manipulate, and control databases.

Types:

  • DDL (Data Definition Language) – CREATE, ALTER, DROP
  • DML (Data Manipulation Language) – INSERT, UPDATE, DELETE
  • DCL (Data Control Language) – GRANT, REVOKE
  • TCL (Transaction Control Language) – COMMIT, ROLLBACK

Concurrency Control

Two-Phase Locking is a concurrency control method used to maintain database consistency.

Two phases:

  1. Growing Phase – Locks are acquired.
  2. Shrinking Phase – Locks are released.

Concurrency control manages simultaneous execution of transactions to avoid conflicts. Methods:

  • Locking
  • Timestamp ordering
  • Optimistic concurrency control
FeatureDBMSFile System
RedundancyReduces redundancyHigh redundancy
SecurityBetter securityLess security
ConcurrencySupports concurrencyLimited concurrency
IntegrityData integrity maintainedIntegrity difficult

Data Abstraction

Data abstraction is the process of hiding complex database details from the users and showing only the necessary information.

Levels of Data Abstraction:

  1. Physical Level – Describes how data is actually stored (files, indexes, storage details).
  2. Logical Level – Describes what data is stored and the relationships among the data.
  3. View Level – The highest level, which shows only a part of the database to the user according to their needs.

The Database Administrator (DBA)

A Database Administrator (DBA) is responsible for managing, maintaining, and controlling the database system to ensure its efficient operation.

Main Functions of a DBA

  1. Database Design: The DBA designs the structure of the database, including tables, relationships, and constraints.
  2. Installation and Configuration: The DBA installs the DBMS software and configures it for proper functioning.
  3. Security Management: The DBA controls user access and permissions to protect the database.
  4. Backup and Recovery: The DBA regularly backs up the database and restores it in case of failure.
  5. Performance Monitoring: The DBA monitors performance and optimizes queries and storage.
  6. Data Integrity: The DBA ensures that the data remains accurate and consistent.
  7. Concurrency Control: The DBA manages simultaneous access to avoid conflicts.
  8. Database Maintenance: Regular updates, patches, and troubleshooting are handled by the DBA.