Database Management Systems Overview

Course Information

• Course code: 68241102
• Course name: Database Management System
• Instructor: Dr. Parul Saxena
• Program: MCA
• Department: CSE
• Institution: MITS, Gwalior

Course Outcomes

• CO1: Students will differentiate database systems from file systems by identifying the features offered by database systems.
• CO2: Defining terminology, features, classifications, and characteristics of database systems.
• CO3: Designing principles for logical database design, utilizing the Entity-Relationship (E-R) method and normalization approach.
• CO4: Evaluating database storage structures and access techniques.
• CO5: Identifying issues related to transaction processing and concurrency control.
• CO6: Analyzing information storage problems and creating information models, expressed as Entity-Relationship diagrams and data dictionaries.

Chapter 1: Introduction

• Purpose of Database Systems
• Data View
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Transaction Management
• Storage Management
• Database Administrator (DBA)
• Database Users
• Overall System Structure

Database Management System (DBMS)

• Collection of interrelated data
• Set of programs for data access
• Contains info about a particular enterprise, business, or project
• Provides a convenient and efficient environment for use.
• Database applications include banking, airlines, universities, sales, manufacturing, and human resources.

Purpose of Database Systems (Drawbacks of File Systems)

• Data redundancy and inconsistency.
• Multiple file formats and duplication of information.
• Data inconsistency leading to unreliable and/or meaningless information.
• Difficulty in accessing data.
• Need for a new program for any new task.
• Data isolation.
• Integrity problems (e.g., constraints like account balance > 0).

Purpose of Database Systems (Drawbacks of File Systems - Continued)

• Atomicity of updates.
• Failures can leave the database in an inconsistent state.
• Concurrent access by multiple users.
• Uncontrolled concurrent access can lead to inconsistencies.
• Security problems

Levels of Abstraction

• Physical level: Describes how data records are stored.
• Logical level: Describes data stored within the database and relationships among the data. Includes structural definition and constraints.
• View level: Application programs hide data type details. Security information may be hidden such as salaries.

Instances and Schemas

• Instance: The actual content of a database at a particular point in time.
• Schema: The logical structure of a database.
• Physical Data Independence: Modifying physical schema without changing the logical schema.
• Logical Data Independence: Modifying logical schema or data definition without requiring changes to application programs.

Data Definition Language (DDL)

• Specifies notation for defining the database schema.
• Includes commands like CREATE, DROP, ALTER, RENAME.
• DDL compiler generates tables which are stored in the data dictionary.
• Data dictionary contains metadata (data about data).

Example Relational Database

• Tables examples of customers, accounts, and depositors.

Data Manipulation Language (DML)

• Language for accessing and manipulating data.
• Also known as query language.
• Two classes: procedural and non-procedural.
  • Procedural: user specifies what data is needed and how to retrieve it.
  • Non-procedural: user specifies what data is needed, but not how to retrieve it.
• SQL: widely used non-procedural language.

Database Users

• Application programmers: Interact with the database using DML calls (back-end programmers).
• Sophisticated users: Form requests in query language (analysts, engineers, scientists).
• Naive users: Invoke permanent application programs (e.g. bank tellers, web users).

Database Administrator (DBA)

• Coordinates activities of the database system.
• Has a good understanding of the enterprise's information resources and needs.
• Duties include: Schema definition; Storage structure and access method definition; Schema and physical organization modification; Grant user authority to access the database; specifying integrity constraints; monitoring performance & responding to changes in requirements.

Transaction Management

• Transaction: A collection of operations that performs a single logical function in a database application.
• Transaction-management component ensures that the database remains in a consistent state despite system failures (e.g. power failures).
• Concurrency-control manager: Controls interaction among concurrent transactions.

Storage Management

• Storage manager: Provides interfaces between low-level data stored in the database and application programs/queries.
• Tasks: interaction with file manager; efficient storing, retrieving, and updating of data.

System Structure

• Diagram shows different components and their interactions within a database system. Components include naive users, application programmers, sophisticated users, DBA, different modules that interpret DML and DDL commands.

Data Models

• Collection of tools for describing data, relationships, semantics, and constraints.
• Entity-Relationship model
• Relational model
• Object-oriented and older models (network, hierarchical)

Entity-Relationship Model (E-R)

• Model of the real world, with entities (objects).
• Includes relationships between entities (e.g. accounts held by customers).
• Widely used for database design.
• Diagrams illustrate entities, attributes and relationships between entities.

Entity-Relationship Model (E-R) - Continued

• Design of databases described in E-R Model usually converted into the Relational Model, which is used for data storage and processing.

DBMS Schema

• Design of a database; physical, logical, and view schemas.
• Diagram of course, student, and section tables showing relationships and attributes.
• Schema represents a structural view of the database; it does not contain actual data.

DBMS Instance

• Data stored in the database at a particular moment in time.
• Value of variables in the DBMS Schema at a specific time.
• Changes over time as data is added or deleted.