S

DBMS_Unit1

Course Overview

IILM University offers a course titled Data Base Management System (UCS2001) led by Pulkit Dwivedi. This course is structured to facilitate comprehensive learning of database concepts and tools.

Course Outcomes

At the conclusion of this course, students will be expected to:

  • CO1: Understand database design methodology and create ER-models for basic applications.

  • CO2: Apply query processing techniques in real-time database problems.

  • CO3: Identify and resolve redundancy in database tables using normalization techniques.

  • CO4: Grasp the concepts of transactions, their processing, and a variety of management issues like data integrity, security, and recovery.

  • CO5: Comprehend concurrency control protocols and recovery algorithms, ultimately implementing a small database project using appropriate tools.

Recommended Textbooks

  • Database Concepts by Korth, Silbertz, Sudarshan (McGraw Hill)

  • Fundamentals of Database Systems by Elmasri, Navathe (Addison Wesley)

  • Database Management Systems by Ramakrishnan (McGraw Hill)

Reference Books

  • An Introduction to Database Systems by Date C J (Addison Wesley)

  • Database Management System by Majumdar and Bhattacharya (TMH)

Unit 1: Introduction to DBMS

Topics Covered

  1. Database and DBMS Introduction

  2. Comparison: Database System vs File System

  3. Database System Architecture

  4. Data Models

  5. Overall Database System Structure

  6. ER Model Concepts and Notations

  7. Mapping Constraints

  8. Keys

  9. Generalization, Aggregation, Specialization

  10. Conversion of ER Diagram to Tables

Definitions

Data

  • Raw, unprocessed facts and figures collected for analysis. Examples include numbers, words, measurements, and observations.

Information

  • Processed data that has been organized to provide meaning and context (e.g., average age).

Database

  • A structured collection of related data that allows for easy access and management.

Database Management System (DBMS)

  • A software solution for creating and managing databases, enabling users to store, process, and analyze data effectively. DBMS offers protection, security, and consistency in multi-user environments.

Examples of DBMS

  • MySQL, Oracle, SQL Server, IBM DB2, PostgreSQL, Amazon SimpleDB.

Database System vs File System Comparison

Aspect

File System

Database System

Purpose

Store and organize files

Store, manage, retrieve structured data

Structure

Hierarchical with directories

Tabular (tables, rows, columns)

Access

File paths

SQL or other query languages

Complexity

Simple operations

Complex operations

Examples

Windows File System, NTFS

MySQL, PostgreSQL

Usage Scenarios

General file storage

Enterprise applications

Characteristics of DBMS

  • Data stored in tables, reduced redundancy, data consistency, multi-user support, query language, security features.

Advantages of DBMS

  • Minimizes data redundancy and allows for easy data retrieval. Enables quick data integration into various applications and offers cloud storage capabilities.

Disadvantages of DBMS

  • Complexity in management, costs associated with licensed DBMS, and larger system sizes.

Components of DBMS

Hardware

  • Physical components like computers, hard disks, and input/output channels necessary for storing and accessing data.

Software

  • The core program that facilitates all operations and user interactions with the database.

Data

  • The primary resource that DBMS manages, including both user data and metadata (data about data).

Procedures

  • General instructions for using the DBMS, including setup, user management, and report generation.

Database Access Language

  • A specialized language used to interact with the database for operations like querying, updating, and deleting data.

Users of DBMS

  1. Database Administrators (DBA): Manage security, licenses, and user access.

  2. Application Programmers: Develop and design DBMS components.

  3. End Users: Perform operations to store, retrieve, and modify data.

DBMS Architectures

Single-Tier Architecture

  • All components reside on a single server, best for individual access where changes are infrequent.

Two-Tier Architecture

  • Client-server model allowing multiple users to access the database. Improved performance and maintainability.

Three-Tier Architecture

  • Involves a middle application layer that interacts with both client and servers, enhancing security and integrity while increasing complexity.

Data Models in DBMS

  • Help visualize the logical structure through symbols and text for effective database design. Types include:

    • Hierarchical Model

    • Network Model

    • Entity-Relationship (ER) Model

    • Relational Model

    • Object-Oriented Model

    • Object-Relational Model

Entity-Relationship Model (ER) Overview

  • ER diagrams indicate the structure of a database, depicting entities (independent objects with attributes) and their relationships.

Key Components of ER Diagrams

  • Entities: Independent objects (e.g., Employee, Company).

  • Attributes: Characteristics (e.g., Employee ID).

  • Relationships: Connections between entities (e.g., "works in").

Types of Relationships

  • One-to-One, One-to-Many, Many-to-One, and Many-to-Many relationships illustrate how entities interact.

Keys in DBMS

Types of Keys

  1. Super Key: Any combination of attributes that uniquely identifies records.

  2. Candidate Key: Minimal set of attributes required for uniqueness.

  3. Primary Key: A specific candidate key chosen for uniqueness and non-null requirement.

  4. Alternate Key: Candidate keys not chosen as a primary key.

  5. Foreign Key: Refers to primary key in another table.

  6. Composite Key: Formed by combining multiple columns.

  7. Surrogate Key: Artificially generated key for uniqueness.

Summary of Keys Characteristics

  • Business logic, uniqueness, and referential integrity are vital in identifying records and maintaining consistency.