Database Essentials: Concepts, Normalization, SQL Functions & Joins

Posted on May 29, 2025 in Computers

Database Applications: Uses, Advantages, and Disadvantages

A database application is software used to store, manage, and access data easily.

Common Database Applications

  • Banking: Stores customer and transaction details.
  • E-commerce: Manages products, orders, and user information.
  • Hospitals: Keeps patient records and doctor schedules.
  • Schools: Stores student marks and attendance.
  • Airlines: Handles bookings and flight details.

Advantages of Database Systems

  • Reduces data redundancy (repetition).
  • Provides secure and fast access to data.
  • Allows multiple users to access data concurrently.
  • Ensures data consistency and accuracy.
  • Offers robust backup and recovery options.

Disadvantages of Database Systems

  • Can be expensive to set up and maintain.
  • Requires trained personnel for management.
  • System crashes can halt operations.
  • Can be complex to manage, especially large systems.

Understanding Database Attributes

In a database, attributes are the columns or fields of a table. They describe properties of an entity, such as a student or an employee.

Types of Attributes

  • Simple Attribute

    Cannot be divided further.

    Example: Age, Roll Number.

  • Composite Attribute

    Can be divided into smaller, meaningful parts.

    Example: Full Name can be split into First Name and Last Name.

  • Derived Attribute

    Value can be calculated or derived from other attributes.

    Example: Age can be derived from Date of Birth.

  • Multi-valued Attribute

    Can have more than one value for a single entity.

    Example: Phone Numbers, Skills.

  • Key Attribute

    Uniquely identifies each record (often used as a primary key).

    Example: StudentID, EmployeeID.

  • Single-valued Attribute

    Holds only one value for each entity.

    Example: Email, Date of Birth.

Database Normalization: 1NF, 2NF, and 3NF

Normalization is the process of organizing data in a database to:

  • Remove duplicate data (redundancy).
  • Ensure data consistency.
  • Make the database more efficient.

It is done in stages called Normal Forms (1NF, 2NF, 3NF, etc.).

First Normal Form (1NF)

A table is in 1NF if:

  • All values are atomic (single-valued).
  • There are no repeating groups or arrays.

Example:

  • Bad: Phones = 1234, 5678 (multi-valued in one cell)
  • Good: Separate rows or a separate table for each phone number.

Second Normal Form (2NF)

A table is in 2NF if:

  • It is already in 1NF.
  • And there is no partial dependency (a non-key attribute depends on only part of a composite key).

Third Normal Form (3NF)

A table is in 3NF if:

  • It is already in 2NF.
  • And there is no transitive dependency (a non-key attribute depends on another non-key attribute).

SQL Functions: String and Math Operations

SQL provides various built-in functions to perform operations on data.

String Functions in SQL

These functions are used to manipulate text (string) data.

  • UPPER()

    Converts text to uppercase.

    Example: SELECT UPPER('hello'); → HELLO

  • LOWER()

    Converts text to lowercase.

    Example: SELECT LOWER('HELLO'); → hello

  • LENGTH()

    Returns the number of characters in a string.

    Example: SELECT LENGTH('SQL'); → 3

  • SUBSTRING() or SUBSTR()

    Extracts a part of a string.

    Example: SELECT SUBSTRING('Database', 1, 4); → Data

  • CONCAT()

    Joins two or more strings together.

    Example: SELECT CONCAT('Hello', 'World'); → HelloWorld

Math Functions in SQL

These functions are used to perform mathematical operations on numeric data.

  • ABS()

    Returns the absolute (positive) value of a number.

    Example: SELECT ABS(-5); → 5

  • ROUND()

    Rounds a number to the nearest integer or specified decimal places.

    Example: SELECT ROUND(12.345, 2); → 12.35

  • CEIL() or CEILING()

    Rounds a number up to the next whole integer.

    Example: SELECT CEIL(4.2); → 5

  • FLOOR()

    Rounds a number down to the previous whole integer.

    Example: SELECT FLOOR(4.8); → 4

  • POWER(x, y)

    Returns x raised to the power of y.

    Example: SELECT POWER(2, 3); → 8

  • MOD()

    Returns the remainder of a division operation.

    Example: SELECT MOD(10, 3); → 1

The Relational Model in Databases

The relational model is a way of organizing data into tables (relations) that are linked to each other through keys. It is based on mathematical concepts of sets and relations, introduced by E.F. Codd in 1970.

Key Concepts in the Relational Model

  • Tables (Relations)

    Data is stored in tables, which consist of rows and columns. Each table represents an entity type (e.g., Students, Employees, Products).

  • Rows (Tuples)

    A row represents a single record in the table, also known as a tuple.

    Example: A row in a Students table might represent one student’s details.

  • Columns (Attributes)

    A column represents a field or attribute of an entity.

    Example: In a Students table, columns could be StudentID, Name, Age.

  • Primary Key

    A primary key is an attribute (or set of attributes) that uniquely identifies each row in the table.

    Example: StudentID can be the primary key in the Students table.

  • Foreign Key

    A foreign key is an attribute that links one table to another, pointing to the primary key of another table.

SQL JOIN Operations: Inner and Outer Joins

A JOIN in SQL is used to combine rows from two or more tables based on a related column between them. The JOIN operation is essential when you need to retrieve data from multiple tables in a single query.

Types of SQL JOINs

  • INNER JOIN
  • LEFT JOIN (Outer Join)
  • RIGHT JOIN (Outer Join)
  • FULL JOIN (Outer Join)

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables based on the specified condition.

  • If there is no match between the tables, those rows are not included in the result.
  • It is the most commonly used JOIN when retrieving matching records from multiple tables.

OUTER JOINs

An OUTER JOIN differs from an INNER JOIN because it returns rows even if there is no match between the two tables. There are three main types of outer joins:

  • LEFT OUTER JOIN (or LEFT JOIN)

    Returns all rows from the left table and the matched rows from the right table. If there is no match, the result will contain NULL values for the right table’s columns.

  • RIGHT OUTER JOIN (or RIGHT JOIN)

    Returns all rows from the right table and the matched rows from the left table. If there is no match, the result will contain NULL values for the left table’s columns.

  • FULL OUTER JOIN

    Returns all rows when there is a match in one of the tables. It returns NULL for non-matching rows from either table, effectively combining the results of both LEFT and RIGHT outer joins.