Terminologies in Database

Some of the important terms used in database systems are:

• Table: A collection of related data organized in rows and columns.
• Field: A column in a table that stores specific information about each record.
• Record: A single row in a table containing related data items.
• Tuple: Another term for a record; an ordered set of values.
• Object: In object-oriented databases, it represents real-world entities with attributes and behaviors.
• Keys: Special fields used to uniquely identify records (e.g., Primary Key, Foreign Key).

Data Types

Data types define the kind of values that can be stored in a table field. Common examples include:

• Integer: Whole numbers (e.g., 1, 25, -10).
• Decimal/Float: Numbers with fractional parts (e.g., 3.14, -2.5).
• Character/String: Sequence of characters (e.g., "Hello", "Nepal").
• Date/Time: Represents dates and times (e.g., 2025-09-01, 15:30:00).
• Boolean: Logical values (TRUE/FALSE).

Data Dictionary

A Data Dictionary is a repository that contains information about the structure of the database. It stores metadata such as:

• Names of tables, fields, and relationships
• Data types of each field
• Constraints (e.g., NOT NULL, UNIQUE)
• Indexes and keys

It acts as a reference for database administrators and developers.

3. Types of Database Models

Database models define the logical design and structure of a database. They specify how data is stored, related, and manipulated. The following are the most common types of database models:

1. Hierarchical Model

The hierarchical model organizes data into a tree-like structure, where each parent node can have multiple child nodes, but each child has only one parent. It is suitable for representing data with a clear hierarchy, such as organizational charts or file systems.

• Data is stored in records connected by parent–child relationships.
• Supports one-to-many relationships.
• Navigation is fast but rigid, as relationships are predefined.

2. Network Model

The network model represents data using nodes and set structures. Unlike the hierarchical model, a child (called a member) can have multiple parents, supporting many-to-many relationships. This model is more flexible for complex applications.

• Data is organized as records connected by links (pointers).
• Supports many-to-many relationships.
• Efficient for complex queries but harder to design and maintain.

3. Relational Model

The relational model organizes data into tables (relations) made of rows (tuples) and columns (attributes). Relationships between tables are established using keys. This is the most widely used model in modern database systems.

• Data is stored in two-dimensional tables.
• Supports powerful querying using SQL.
• Offers flexibility, simplicity, and scalability.

4. Entity-Relational Model (ER Model)

The Entity-Relational (ER) model represents the database in terms of entities, attributes, and relationships. It is often used at the design stage to model real-world concepts before implementation in a relational database.

• Entities represent real-world objects (e.g., Student, Course).
• Attributes describe properties of entities (e.g., Name, Age).
• Relationships define associations between entities (e.g., Enrolled-In).

Integrity Constraints and Their Types

Integrity constraints are the rules that ensure the accuracy, validity, and consistency of data in a database. They act as a safeguard to prevent invalid or inconsistent data from being entered or maintained in the system. Without integrity constraints, databases would quickly become unreliable, as users could insert contradictory or incomplete information. These rules are enforced by the DBMS automatically whenever data is inserted, updated, or deleted.

In essence, integrity constraints maintain the quality of the data and preserve the logical relationships between tables. They are one of the key reasons why databases are more reliable than traditional file systems.

Types of Integrity Constraints

1. Domain Integrity Constraint

Domain constraints define the valid set of values for an attribute (column). They restrict the type, format, and permissible range of values that can be stored in a column. For example, the column Age should only accept positive integers, and the column Email should follow the email format.

• Specifies the data type (e.g., integer, char, date).
• Restricts the range (e.g., marks between 0 and 100).
• Ensures values are meaningful and within expected bounds.

    CREATE TABLE Students (
        StudentID INT PRIMARY KEY,
        Name VARCHAR(50) NOT NULL,
        Age INT CHECK (Age > 0 AND Age < 120)
    );
    

Here, the domain constraint ensures that Age must always be between 1 and 119.

2. Entity Integrity Constraint

Entity integrity ensures that every table has a unique identifier (primary key) and that this key value is never NULL. It guarantees that each record (row) can be uniquely identified within the table. Without this, it would be impossible to distinguish between two rows of data.

• Each table must have a primary key.
• The primary key column(s) cannot contain NULL.
• No two rows can have the same primary key value.

    CREATE TABLE Employees (
        EmpID INT PRIMARY KEY,
        Name VARCHAR(50),
        Department VARCHAR(50)
    );
    

The primary key EmpID ensures every employee record is unique and identifiable.

3. Referential Integrity Constraint

Referential integrity maintains consistency between related tables through the use of foreign keys. It ensures that a value in a child table must correspond to an existing value in the parent table or be NULL (if allowed). This prevents the creation of "orphan" records.

• Defined using foreign keys.
• Ensures that relationships between tables remain valid.
• Prevents deletion of a parent record if matching child records exist (unless cascading is defined).

    CREATE TABLE Departments (
        DeptID INT PRIMARY KEY,
        DeptName VARCHAR(50)
    );

    CREATE TABLE Employees (
        EmpID INT PRIMARY KEY,
        Name VARCHAR(50),
        DeptID INT,
        FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
    );
    

Here, every employee must be assigned to a valid department. If DeptID = 5 does not exist in Departments, it cannot be inserted into Employees.

4. Key Integrity Constraint

Key constraints enforce the uniqueness and proper identification of rows within a table. They define which attribute(s) can be used as keys and how they enforce uniqueness across records.

• Primary Key: Uniquely identifies each record, not NULL.
• Unique Key: Ensures uniqueness of values but allows one NULL.
• Candidate Key: A set of attributes that can uniquely identify records. One is chosen as the primary key.
• Foreign Key: Links one table to another, ensuring referential integrity.

    CREATE TABLE Users (
        UserID INT PRIMARY KEY,
        Username VARCHAR(50) UNIQUE,
        Email VARCHAR(100) UNIQUE
    );
    

Here, UserID is the primary key, while Username and Email must be unique for each user.

Why Integrity Constraints Matter?

Integrity constraints form the backbone of reliable databases:

• Prevent invalid, inconsistent, and duplicate data.
• Ensure logical correctness of relationships between tables.
• Protect data from accidental corruption during insert, update, or delete operations.
• Maintain trust in the database for decision-making and operations.

Advantages and Disadvantages of Normalization

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large, complex tables into smaller, well-structured tables connected through relationships. While normalization improves efficiency and accuracy, it also comes with certain trade-offs. Understanding its pros and cons is essential for designing an effective database.

Advantages of Normalization

• Reduces Data Redundancy: Prevents the storage of the same piece of information in multiple places, saving space and ensuring consistency.
• Improves Data Integrity: Changes made in one place are automatically reflected everywhere, minimizing anomalies during updates, insertions, or deletions.
• Easier Maintenance: With smaller, organized tables, modifying, updating, or extending the database is simpler and less error-prone.
• Better Data Consistency: Since each fact is stored only once, data remains consistent across the database.
• Efficient Query Processing: Properly normalized databases often perform faster in read/write operations because there is less redundant data to scan.
• Logical Clarity: Relationships between entities become clear, improving database design and understanding for developers and analysts.

Disadvantages of Normalization

• Complex Queries: Since data is spread across multiple tables, retrieving information often requires complex joins, making queries harder to write and slower in some cases.
• Performance Overhead: Too much normalization can slow down query performance, especially in large databases, due to frequent table joins.
• Initial Design Complexity: Designing a highly normalized database requires deep understanding of entities, relationships, and dependencies, which can be time-consuming.
• Difficult for Beginners: For new learners or non-technical users, normalized designs may appear complicated compared to flat, single tables.
• Not Always Suitable: In analytical databases or systems where speed is more important than consistency (like data warehouses), excessive normalization can be a drawback.

Conclusion

Normalization is a powerful technique for ensuring data accuracy and reducing redundancy, making it an essential step in designing reliable transactional databases. However, it should be applied wisely, balancing between data consistency and system performance. In practice, databases often use partial normalization (up to 3NF or BCNF) while denormalizing selectively for performance optimization.

Centralized and Distributed Database

Introduction

A Centralized Database is stored and maintained in a single location, usually a central computer or server. All users access the database through this central system. In contrast, a Distributed Database is spread across multiple locations (servers or sites), but the data appears as a single logical database to users. Both approaches have their own strengths and weaknesses depending on system requirements.

Advantages of Centralized Database

• Data Consistency: All data is stored in one place, ensuring consistency and reliability.
• Easy Maintenance: Administration, updates, and backups are easier since everything is in one location.
• Cost-Effective: Requires fewer resources compared to distributed systems.
• Better Security Control: Since data is stored centrally, it is easier to implement strict security policies.

Disadvantages of Centralized Database

• Single Point of Failure: If the central server fails, the entire system becomes unavailable.
• Scalability Issues: Handling a growing number of users and data can overwhelm the central system.
• Performance Bottlenecks: High traffic can lead to slower response times.
• Limited Availability: Remote users may face latency due to distance from the central server.

Advantages of Distributed Database

• Reliability: If one site fails, others can continue functioning, improving fault tolerance.
• Improved Performance: Data is stored closer to users, reducing access time.
• Scalability: New sites can be added easily without affecting existing operations.
• Local Autonomy: Each site can manage its own data while still being part of the overall system.

Disadvantages of Distributed Database

• Complex Management: Maintaining synchronization and consistency across multiple sites is difficult.
• High Cost: Setting up and managing multiple servers is more expensive.
• Security Challenges: Multiple access points increase the risk of breaches.
• Data Redundancy: May require replication of data, leading to higher storage needs.

Comparison

Database Security

Introduction

Database Security refers to the collective measures used to protect the database against threats such as unauthorized access, data breaches, corruption, or loss. Since databases often store critical and sensitive information (such as financial records, personal data, and business transactions), ensuring their security is a top priority.

Challenges in Database Security

• Unauthorized Access: Hackers or malicious users may try to gain illegal access to sensitive data.
• SQL Injection Attacks: Attackers exploit poorly written queries to manipulate or steal data.
• Insider Threats: Authorized users misusing their privileges to harm the system.
• Data Corruption: Software bugs, malware, or faulty processes can lead to loss of data integrity.
• Weak Authentication: Poor password management and weak user authentication increase risks.
• Backup Vulnerabilities: Insecure storage of backups may expose sensitive data.

Security Measures

• User Authentication and Authorization: Ensuring that only verified users access the database, with proper role-based permissions.
• Encryption: Encrypting data both at rest and in transit to prevent unauthorized reading of sensitive information.
• Firewalls and Network Security: Protecting the database from external attacks using firewalls, intrusion detection, and VPNs.
• Regular Backups: Maintaining encrypted backups to prevent permanent data loss.
• Audit Trails: Keeping detailed logs of user activities to detect and prevent suspicious behavior.
• SQL Injection Prevention: Using prepared statements and parameterized queries to prevent injection attacks.
• Patch Management: Keeping database software updated to protect against known vulnerabilities.
• Access Control Policies: Defining strict policies to limit who can view, modify, or delete data.

Effective database security is a continuous process that combines technical measures (like encryption and firewalls), administrative policies (like role-based access), and regular monitoring to safeguard valuable data against both internal and external threats.

Entity-Relationship (ER) Diagrams

An Entity-Relationship (ER) Diagram is a visual representation of entities, attributes, and the relationships among them in a database system. ER Diagrams are widely used during the database design phase because they provide a clear and structured way to represent data requirements. They serve as a blueprint for creating relational databases.

Entities

An Entity represents a real-world object or concept that can be identified distinctly in the database. Entities can be physical objects (like Student, Book, Employee) or abstract concepts (like Course, Department). In ER diagrams, entities are usually represented by rectangles.

• Strong Entity: Has a primary key that uniquely identifies its instances (e.g., Student).
• Weak Entity: Cannot be uniquely identified by its attributes alone; depends on a strong entity (e.g., Dependent of Employee).

Attributes

An Attribute describes a property or characteristic of an entity. For example, the entity Student may have attributes like Student_ID, Name, Age, Address. Attributes are represented by ellipses (ovals) in ER diagrams and connected to their entities.

• Simple Attribute: Cannot be divided further (e.g., Age).
• Composite Attribute: Can be broken down into sub-parts (e.g., Name → First Name, Last Name).
• Derived Attribute: Value can be derived from other attributes (e.g., Age derived from Date of Birth).
• Multivalued Attribute: Can have multiple values for a single entity (e.g., Phone Numbers).

Relationships

A Relationship shows how entities are connected to each other. For example, a Student enrolls in a Course. Relationships are represented by diamonds in ER diagrams.

• One-to-One (1:1): A single entity instance in set A is related to a single entity instance in set B.
• One-to-Many (1:N): A single entity instance in set A is related to multiple instances in set B.
• Many-to-Many (M:N): Multiple entity instances in set A are related to multiple instances in set B.

Cardinality and Degree

Cardinality defines the number of instances of one entity that can be associated with instances of another entity (e.g., 1:1, 1:N, M:N). Degree of a relationship represents the number of entities involved in the relationship:

• Unary Relationship: Involves one entity set (e.g., Employee supervises Employee).
• Binary Relationship: Involves two entities (e.g., Student enrolls in Course).
• Ternary Relationship: Involves three entities (e.g., Doctor prescribes Medicine to Patient).

Symbols in ER Diagrams

ER Diagrams use a standard set of symbols to represent entities, attributes, and relationships. Below are the most common symbols:

Rectangle:

Represents an Entity.

Double Rectangle:

Represents a Weak Entity.

Ellipse (Oval):

Represents an Attribute.

Double Ellipse:

Represents a Multivalued Attribute.

Dashed Ellipse:

Represents a Derived Attribute.

Diamond:

Represents a Relationship.

Line:

Connects entities with relationships or attributes.

SQL JOIN

JOIN operations in SQL are used to combine rows from two or more tables based on a related column between them. They are essential for retrieving data that is spread across multiple tables in a relational database.

1. INNER JOIN

The INNER JOIN returns records that have matching values in both tables. Rows without matches are excluded from the result.

-- Example:
SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.CourseID = Courses.CourseID;
    

Explanation: This query returns only students who are enrolled in a course (matching CourseID in both tables).

2. LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN returns all records from the left table, and the matched records from the right table. If no match is found, NULL values are returned for columns from the right table.

-- Example:
SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses
ON Students.CourseID = Courses.CourseID;
    

Explanation: This query shows all students, even if they are not enrolled in any course. For those students, CourseName will be NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN returns all records from the right table, and the matched records from the left table. If no match is found, NULL values are returned for columns from the left table.

-- Example:
SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
RIGHT JOIN Courses
ON Students.CourseID = Courses.CourseID;
    

Explanation: This query shows all courses, even if no student is enrolled in them. For such courses, StudentID and Name will be NULL.

4. FULL JOIN (FULL OUTER JOIN)

The FULL JOIN returns all records when there is a match in either left or right table. If there is no match, NULL values are returned from the missing side.

-- Example:
SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.CourseID = Courses.CourseID;
    

Explanation: This query shows all students and all courses. If a student is not enrolled in any course, CourseName will be NULL. If a course has no students, StudentID and Name will be NULL.

Conclusion

SQL JOINs allow us to query and analyze data across multiple tables efficiently. Choosing the right type of JOIN depends on whether we want only matched records (INNER JOIN) or unmatched records as well (LEFT, RIGHT, FULL JOIN).