Database Management System

INTRODUCTION TO DATABASE CONCEPTS AND TERMINOLOGIES

Database: A database is a structured or organised collection of data for efficient retrieval, storage, and manipulation.

DATABASE TERMINOLOGIES

  • DBMS: DBMS stands for Database Management System. It’s a software system that enables users to define, create, maintain, and control access to databases. A DBMS provides an interface for users to interact with the database for various tasks such as querying data, updating records, and managing data integrity and security. It also ensures efficient storage and retrieval of data while handling concurrency control and transaction management to maintain data consistency and reliability. Examples: Microsoft Access, MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB.
  • SQL: Structured Query Language, is a standardized programming language used for managing and manipulating relational databases. It provides a set of commands and syntax for querying, updating, and managing data in a database.
  • Relationships: Define connections between tables to maintain data integrity and enable efficient data retrieval.
  • Primary Key: A unique identifier for each record in a table.
  • Foreign Key: A field in one table that refers to the primary key in another table to establish relationships.
  • Data Types: Specify the type of data that can be stored in each field, such as Text, Number, Date/Time, etc.
  • Macros: Automate repetitive tasks by recording and running sequences of actions.

TYPES OF DATABASES

  1. Relational Database:

Organizes data into tables (relations) with rows and columns. Uses Structured Query Language (SQL) for querying and managing data. Examples include MS Access, MySQL, PostgreSQL, and Oracle Database.

  • Use Case: E-commerce Platform
  • Example: An online store uses a relational database to manage product catalogs, customer information, orders, and inventory. It leverages the structured nature of relational databases to ensure data integrity and support complex queries, such as joining tables to retrieve product details along with customer information for order processing.
  1. Non-Relational or NoSQL Database:

Encompasses various database technologies that are not based on the traditional relational database model. Can be document-based, key-value stores, column-family stores, or graph databases. Examples include MongoDB, Redis, and Apache Cassandra.

  • Use Case: Real-time Analytics and Logging
  • Example: A web application collects and analyzes user interactions, logs, and events in real-time. A NoSQL database like MongoDB can handle high-volume, rapidly changing data, making it suitable for storing and querying unstructured or semi-structured data for analytics and monitoring purposes.
  1. Network Database:

Stores data in a network model, allowing multiple record types to be linked through complex relationships. Uses pointers to navigate between records. Less common today compared to relational and NoSQL databases.

  • Use Case: Telecommunications Network Management
  • Example: A telecommunications company uses a network database to model and manage complex network structures, including interconnected nodes, links, and services. The network database allows for flexible relationships between different types of network elements and supports efficient traversal and querying of network data.
  1. Object-Oriented Database:

Stores data in the form of objects, similar to object-oriented programming concepts. Supports complex data structures and relationships. Examples include ObjectDB and ObjectStore.

  • Use Case: CAD/CAM Systems
  • Example: Computer-Aided Design and Manufacturing (CAD/CAM) systems use object-oriented databases to store and manage complex design models, parts, assemblies, and manufacturing processes. Object-oriented databases can represent these entities as objects with attributes and methods, allowing for efficient storage and retrieval of complex data structures.
  1. Hierarchical Database:

Organizes data in a tree-like structure with parent-child relationships, where each child can have multiple parents. Each record has a single parent, except for the root record. Less flexible compared to relational databases but still used in some legacy systems.

  • Use Case: File System and Directory Structures
  • Example: Operating systems use hierarchical databases to manage file systems and directory structures. Each directory can contain multiple files or subdirectories, forming a tree-like hierarchy with parent-child relationships. While modern file systems may not use hierarchical databases internally, the concept of a hierarchical structure remains relevant for organizing and accessing file data.

Each type of database has its own characteristics, strengths, and use cases, making them suitable for different application requirements and data models.

DATABASE OBJECTS

  • Tables: Store data in rows (records) and columns (fields). Each table represents an entity.
  • Queries: Retrieve specific data from tables based on criteria defined by users.
  • Forms: Provide user-friendly interfaces for data entry and display.
  • Reports: Present data in a formatted manner for printing or viewing.

RELATIONSHIP TYPES

  • One-to-One: Each record in one table corresponds to exactly one record in another table.
    • Example: Consider a database for employee information. Each employee may have only one employee identification card (ID), and each ID corresponds to only one employee. In this case, there is a one-to-one relationship between the Employee table and the EmployeeID table. Each employee record in the Employee table is associated with exactly one record in the EmployeeID table, and vice versa.
  • One-to-Many: Each record in one table can relate to multiple records in another table.
    • Example: Think of a university database system. A university has multiple departments, and each department can have many professors. In this scenario, there is a one-to-many relationship between the Department table and the Professor table. Each department (one) can be associated with multiple professors (many), but each professor may belong to only one department.
  • Many-to-Many: Multiple records in one table can relate to multiple records in another table, typically implemented using a junction table.
    • Example 1: Consider a bookstore database. Each book can be written by multiple authors, and each author can write multiple books. This represents a many-to-many relationship between the Book table and the Author table. To represent this relationship in a relational database, a junction table, often called a linking or associative table, is used. This table contains foreign keys referencing the primary keys of both the Book and Author tables. Each record in the junction table represents a specific combination of book and author, indicating which authors contributed to which books.
    • Example 2: An order can contain multiple products, and a product can be included in multiple orders. This represents a many-to-many relationship between the Order table and the Product table. A junction table, such as OrderDetails, is used to link the Order and Product tables, specifying which products are included in which orders.
Post Views: 0
error: Content is protected !!