Introduction to Database Management Systems

What is a Database Management System (DBMS)?

• A collection of interrelated data, programs to access the data, and tools to manage it
• Goal is to store and retrieve data efficiently
• Data is organized in tables, views, schema, reports etc.

What does the Database Management System do?

• Provides a systematic way to access, update, and delete data
• Supports multi-user access
• Ensures data consistency through normalization
• High security, data is stored in a structured format
• Data redundancy is reduced
• Includes data backup and recovery processes

Examples of Database Applications

• Banking: transactions
• Airlines: reservations, schedules
• Universities: registration, grades
• Sales: customers, products, purchases
• Railway reservations
• Telecommunications
• Human resources: employee records, salaries, tax deductions

History of Database Systems

• 1950s and 1960s:
  • First DBMS, Integrated Data Source (IDS)
  • Data processing used magnetic tapes for storage
  • Sequential access only, punched cards were used for input
• Late 1960s and 1970s:
  • IBM's Information Management Systems (IMS) introduced
  • Direct data access with hard disks became possible
  • High-performance transaction processing

Relational Database Model

• Became popular in the 1980s, accepted as the main database paradigm
• SQL became the industry standard during the 1980s
• Object-oriented database systems emerged during the 1980s

Evolution of Database Systems

• 1990s:
  • Powerful query languages, like Oracle, SQL Server, DB2, Informix developed
• 2000s:
  • XML and XQuery standards emerged
  • Automated database administration became more common

Drawbacks of Using File Systems to Store Data

• Data redundancy and inconsistency:
  • Multiple file formats, duplication of information across files
• Difficulty in accessing data:
  • Need to write new programs for each task
• Data isolation:
  • Multiple files and formats contribute to isolation
• Integrity problems:
  • Integrity constraints, like account balance > 0, were not explicitly stated
  • Hard to modify or add new constraints
• Atomicity of updates:
  • Failures may leave database in an inconsistent state with partial updates
  • Example: Transfer of funds from one account to another should either complete or not happen at all
• Concurrent access by multiple users:
  • Concurrent access crucial for performance, but can lead to inconsistencies
  • Example: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time
• Security problems:
  • Difficult to provide user access to some, but not all, data.

Database Systems as a Solution

• Database systems provide solutions to all the above problems
• Offer better data management capabilities than file systems

Comparing File Systems and Database Management Systems

• File Systems:
  • Do not support multi-user access
  • Less data consistency
  • No security
  • Unstructured data storage
  • High data redundancy
  • No data backup and recovery
  • Low cost
  • Application failure does not impact the system
  • Data cannot be shared
  • No concurrency facility
  • Example: NTFS, EXT
• Database Management Systems:
  • Support multi-user access
  • Higher data consistency through normalization
  • High security
  • Structured data storage
  • Low data redundancy
  • Data backup and recovery processes available
  • Higher cost
  • Database failure affects all dependent applications
  • Data can be shared
  • Concurrency facility
  • Example: Oracle, MySQL, MS SQL Server, DB2, Microsoft Access

File System

• High data redundancy
• No data backup and recovery process
• Easy to handle
• Less expensive than DBMS
• Application failure does not affect other applications
• Data cannot be shared easily
• No concurrency support
• Examples: NTFS, EXT

Database Management System (DBMS)

• Software system for creating and managing databases
• Provides systematic data access, update, and deletion
• Supports multi-user access
• High data consistency due to normalization
• Highly secure
• Used for storing structured data
• Low data redundancy
• Data backup and recovery is available
• Complex to handle
• More expensive than file system
• Database failure impacts all dependent applications
• Data can be shared as it is stored centrally
• Concurrency support
• Examples: Oracle, MySQL, MS SQL Server, DB2, Microsoft Access

Advantages of a DBMS

• Data Independence: DBMS provides an abstract view of data, hiding storage details.
• Efficient Data Access: DBMS utilizes advanced techniques for efficient data storage and retrieval.
• Data Integrity and Security: DBMS enforces data integrity constraints and access controls.
• Data Administration: Centralized data administration improves efficiency in multi-user environments.
• Concurrent Access and Crash Recovery: DBMS handles concurrent access, ensuring data consistency and recovering from system failures.
• Reduced Application Development Time: High-level data interface accelerates application development.

Disk Storage

• Components of a DBMS in Disk Storage
  • Data Files: Store data using the operating system's file system.
  • Data Dictionary: Contains information about database object structures (metadata).
  • Indices: Used for fast and efficient data retrieval.

Types of Database Users

• Database Administrator (DBA): Responsible for overall DBMS management, including defining schema, security, backup, recovery, monitoring, and performance.
• Database Designers: Design database structures for data sharing.
• System Analyst: Analyze requirements of end users to meet their needs.
• Application Programmers: Develop applications that interact with the database.
• Naive Users/Parametric Users: Unsophisticated users who rely on developed applications without deep DBMS knowledge.
• Sophisticated Users: Users who have DBMS knowledge and interact with the database directly but do not write programs.
• Casual Users/Temporary Users: Users who interact with the database for short periods.

Data Backup and Recovery

• The DBA ensures data recovery in case of loss or integrity issues through backup and disaster management.

The DBA's Technical Role

• The DBA is involved in technical aspects including:
  • 1.Evaluating, Selecting, and Installing DBMS and Utilities: Choosing the appropriate DBMS, utilities, and hardware based on organizational needs.
  • 2.Designing and Implementing Databases and Applications: Contributing to database modeling, design, and ensuring transaction quality and integrity.
  • 3.Testing and Evaluating Databases and Applications: Conducting tests and evaluating the database's performance and functionality.
  • 4.Operating the DBMS, Utilities, and Applications: Managing system support, monitoring performance, implementing backup and recovery strategies, and security auditing.
  • 5.Training and Supporting Users: Providing technical training on the DBMS and its utilities for application programmers.
  • 6.Maintaining the DBMS, Utilities and Applications: Performing ongoing maintenance tasks like managing physical storage devices, upgrading software, and data migration services.

Database Design and ER Model

• Database Design: The process of organizing data to implement a database for efficient storage, retrieval, and manipulation.
• ER Model: A data modeling approach used in database design.

Database Design Steps

• 1. Requirements Analysis: Understanding the data to be stored, applications to be built, and operational requirements.
• 2. Conceptual Database Design: Developing a high-level description of the data to be stored and constraints.
• 3. Logical Database Design: Choosing a DBMS and converting the conceptual design into a database schema.
• 4. Schema Refinement: Analyzing the database schema and identifying potential problems for refinement.

Strong Entity

• Represents a real-world object with its own unique identifier (primary key).
• Depicted by a rectangle.

Weak Entity

• Depends on a strong entity for its existence.
• Represented by a double rectangle.
• Does not have a primary key, instead, it has a partial key.
• The primary key is formed by combining the strong entity's primary key and the weak entity's partial key.

Relationship

• Association between two entities.
• Shown by a diamond shape.
• Represents how entities are connected to each other.

ER - Relationships

• Relationship Set: A collection of similar relationships.
• Attributes can be properties of a relationship set.

Attributes

• Descriptive properties of an entity.
• Represented by ellipses.
• Linked to entities with lines.

Types of Attributes

• Simple Attribute: Atomic and cannot be broken down further (e.g., student's age).
• Composite Attribute: Composed of multiple simple attributes (e.g., student's address).
• Derived Attribute: Calculated from other attributes (e.g., average student age).
• Single-Valued Attribute: Has a single value (e.g., student's name).
• Multi-Valued Attribute: Can have multiple values (e.g., student's hobbies).