Database Management Systems: An Overview

Overview of Database Management System

• A database is a critical component in any automated or computer-based information system.
• Effective database design requires consideration of every aspect.

Topics Covered

• Legacy systems using conventional file management.
• Problems with file management.
• Various data models.
• Database evolution through DBMS.
• Database users.
• Advantages and disadvantages of file organizations.
• RDBMS concepts and keys.
• Factors impacting database performance.

Keywords

• Database
• Conventional Data Processing System
• Data Independence
• Levels of Abstraction
• Data Models (Network, Hierarchical, Relational)
• DBMS
• RDBMS
• Keys
• Database Performance
• Database Users

Introduction

• Cycle time for information retrieval can be reduced.
• Information can be stored without deterioration over time.
• A small workforce can be employed for updating information.
• Easy storage of voluminous information centralized.
• Data redundancy can be minimized.
• The system can be made person independent.

Data File Basics

• Data File indicates a file where transaction details or raw facts about an activity are stored.
• Headings for items collected are Fields, Attributes, or Column Names.
• Actual details collected are Records, Tuples, or Rows.
• The entire structure is a Table or Relation or a Data File.
• The number of fields is the degree of the table.
• The number of records is the cardinality of the table.

Conventional File Management/Processing System

• Before DBMS, organizations stored information using file processing systems.
• Permanent records are stored in files.
• Application programs extract and add records.

Disadvantages of File Processing System

• Data redundancy and inconsistency
• Difficulty in accessing data
• Data isolation
• Integrity problems
• Atomicity problems
• Concurrent-access anomalies
• Security problems

Problems with Conventional File Management

• Files for applications have no logical data independence.
• High redundancy and inconsistencies in output.
• Limited abstraction levels.
• Weak integrity and security.
• Lacks simplicity.
• High development and maintenance costs.
• Complex information retrieval.
• Cannot meet instantaneous management information needs.
• Limited interfacing facilities.

Database Management System (DBMS)

• DBMS comprises interrelated data and programs for managing data organization, storage, and retrieval.
• DBMS adds, modifies, and extracts data.
• DBMS accepts data requests from clients and sends data back.
• The primary goal of DBMS is to be convenient and efficient.
• The database system ensures the safety of stored information, even after system crashes or unauthorized access attempts.

Main Elements of Database System

• Database: Collection of related tables
• Tables: Collection of related records
• Queries: Instructions that retrieve and display data
• View: Virtual table defined by a query
• Stored procedure: Set of pre-compiled query statements
• Forms: Present data from a table or query
• Reports: Formatted display with header, body, and footer
• Macros: Automate database tasks
• Modules: Container of any programming code in a database
• Relationship: Association between two tables

Data Processing Tasks

• Recording
• Storing
• Classifying
• Sorting
• Retrieving
• Querying
• Generating Reports
• Communicating

Three Levels of Abstraction

• Database organization has three levels: internal, conceptual, and external.

The Internal Level of Abstraction

• Physical organization comprises data, storage, and accessing techniques.
• Storage devices, blocks, buffers, buckets, tracks, sectors, cylinders, pointers, and addressing methods are included.
• Hashing, indexing, and B-Trees are also included.
• Physical layout can be isolated so modifications do not affect the logical structure.
• It leads to physical data independence.
• Physical organization is described by physical description languages and known as the internal level.

Conceptual Level of Abstraction

• The global or logical organization describes the entire database system, including entities, record types, data types, relationships, constraints, and controls.
• It is the conceptual representation of the logical database organization.
• The conceptual level hides physical complexity from application Programmers.
• Conceptual views implemented via conceptual schemas (Data Description Languages).
• Tasks include designing, implementing authorization, relationships and setting data integrity controls.
• Database administrators are responsible for designing, implementing, and maintaining the global logical organization.

External Level of Abstraction

• The database is a centralized storage for diverse information needs across different organizational levels.
• Users, whether domain specialists or application programmers, require specific data so their logical data structure is called external logical organization.
• The view the user has is called external views.
• DBMS offers multiple data views, allowing customization.
• External views are embedded via subschemas.
• The DBA (database administrator) maintains the database resource.

Data Models

• Data Models enable the manner in which data is stored and organized within a database system

Relational Model

• Collection of tables to represent data and relationships.
• Each table features multiple columns, each with a unique name.
• Relational databases don't link records physically.
• Tables use a common field to match rows, no pointers or links.

Network Model

• Data are represented by collections of records.
• Relationships among data are represented by links or pointers.
• Records are organized as arbitrary graphs.

Hierarchical Model

• The hierarchical model shares similarity with the network model
• Data, records, and links reflect data relationship.
• Records are structured as collections of trees, not arbitrary graphs.

Relational Databases

• Stores data in different tables.
• Tables are related to provide information.
• Suited for large applications and ad-hoc queries
• Normalization decomposes attributes into manageable tables.
• Links them using common fields.
• A Relational DataBase Management System (RDBMS) handles relational data.
• RDBMS aids in creating, updating, and administering relational databases via query statements from users or applications.

Keys in RDBMS

• A key identifies unique instances or sets table relationships.
• It can be a single column or a set of columns.

Types of Keys in RDBMS

• Primary Key
• Foreign Key
• Composite Key
• Candidate Key
• Primary Key: A column or set of columns with unique values that identify an instance.
• Restrictions: One primary key, no NULL values

Foreign Key

• Links two tables in an RDBMS.
• It matches the primary key value of another table, ensuring the values are listed in the related primary key column to maintain a relationship.
• Foreign keys maintain referential integrity.
• Referential integrity specifies automated column validation against the referenced table.
• Preserves the defined table relationship when data is deleted, modified, or inserted.

Factors Determining Database Performance

• Maintainability affects performance.
• Normalizing the database improves maintainability.
• Splitting data into Customer and Transaction tables.
• Adding rules would be more efficient.
• Reduces the time needed to backup and restore.

Availability

• Availability of the database hinges on server uptime and data accessibility.
• Views on tables enables readily availability.
• A well-normalized database minimizes update implementation time, reducing downtime.
• The backup and restore component safeguards critical data on database servers and backs up and restores data across system problems.

Scalability

• Normalization boosts Database Scalability.
• Scalability reflects database performance amid rising users or transactions.
• A scalable database promptly responds to user demands without delay.

Extensibility

• Normalized databases enable adding tables, creating views, and modifying business rules.
• Makes the database extensible.
• Enables implementation of changes and enhancements with minimum effort.
• Ensures minimal disruption to database structure.

Security

• Effective design secures data and prevents unauthorized access.
• Database design enforces views, grants, and revokes permissions.
• Most DBMS provides security via protection from accidental destruction.
• Reconstruction of damaged data.
• Tamperproof and authorization subsystems.
• And monitoring and auditing subsystems.

DataBase Users

• Database Designer
• Database Administrator
• System Analyst and Application Programmers
• End/Domain Users

Database Designer

• In Computer Based Information Systems (CBIS), conducting data needs analysis in the organization is essential.
• The Database Designer identifies stored data, storage strategies, and overall structure design.
• In most cases, job function is done by the DataBase Administrator.

Database Administrator (DBA)

• A database administrator maintains the database environment.
• In large organizations, DBAs are assisted by groups of professionals.
• Maintain and manage database structure.
• The DBA has global view of the entire database.
• The DBA is expected to possess complete technical knowledge.
• The DBA is expected to have knowledge of the organization structure, economics, stakeholders, and their requirements.

Job responsibilities of DBA

• Access Authorization
• Modification
• Monitoring the usage
• Coordination among different users
• Performance tuning
• Planning security systems
• Applying integrity checks

Systems Analyst Responsibilities

• They study requirements and designs.
• Application Programmers transform designs.
• Reduce implementation time and programming effort.
• Coordination between systems analyst and DBA ensures utilization of DBMS capabilities.

End Users

• Casual Users navigate with easy-to-use console to extract data.
• Domain Users include bank terminals operators, Data Entry operators, Sales Managers, Accountants, etc
• Sophisticated End Users include Engineers, Technicians, Analysts, Designers, and Students.

Summary

• Databases are essential for any Computer Based Information System.
• Information stored digitally is a file.
• Business transaction details stored for future retrieval are Data Files.
• Data collection and storage in matrix form.
• Columns are fields, rows are records.
• Related tables are a database.
• Programs supervise data flow for Database Management Systems.
• Relational model deconstructs transaction details into smaller tables.
• Relational database tables map with keys.
• Key identifying instances or sets relations.
• The Network and Hierarchical Models can be used as data structures.
• Data is represented as links or pointers in the Network Model.
• Hierarchical model organizes records as trees versus arbitrary graphs.
• A well-designed database helps maintainability, availability, scalability, extensibility, and security aspects of information system.