Database Management Systems (DBMS)

• Study notes on Database Management Systems (DBMS) are provided.
• It includes an introduction to DBMS, its applications, advantages, ANSI SPARC database system, data abstraction, database users, and system architecture

Introduction to DBMS

• Data is like a fact that is recorded or saved, examples include name, age, gender and weight
• A database refers to logically related data that has been collected, such as a library's book database or a university's student directory.
• Management incorporates manipulation and searching of data, as well as security.
• System consists of programs or tools to manage the database, such as SQL Server Studio Express or Oracle.
• A Database Management System (DBMS) is a software creating and managing databases.
• DBMS softwares can define, manipulate, retrieve, and oversee data inside a database.
• Examples of DBMS are MS SQL Server, Oracle, My SQL, SQLite and MongoDB.

Applications of DBMS

• A DBMS serves as a digital system for maintaining records
• It is essential to store data whenever it needs to be stored
• Examples of DBMS applications include:
  • E-commerce platforms like Amazon and eBay
  • Online television streaming services like Hotstar and Amazon Prime
  • Social media platforms such as WhatsApp and Facebook
  • Banking and Insurance
  • Airline and Railway services

Advantages of DBMS

• A DBMS reduces data duplication by storing data centrally:
• It helps eliminate possible data errors.

Data Isolation

• Data is scattered across various files.
• Files can exist in different formats.
• Difficulty may arise when retrieving the right data.
• A DBMS supports accessing and retrieving appropriate data easily.

Guaranteed atomicity

• A transaction either executes completely (100%) or not at all (0%).

Integrity constraints

• Integrity constraints can be implemented
• Business rules can be implemented in the database
• The system won't allow less than Rs. 0 in the current balance

Data sharing among multiple users

• Multiple users can access the same data simultaneously.

Restricting unauthorized access

• Only authorized users can access data.

Backup and Recovery

• Features ensure data restoration during crashes.
• It has facilities to backup and restore the database in case of failure

Data vs. Information

• Data constitutes raw, unorganized facts requiring processing, for instance, students' marks.
• Information arises from processed, structured, and organized data within context to provide insights.

Basic Terms

• Data is raw, unorganized facts
  • Example is marks of students
• Information is processed, organized, structured data
  • Example is result of students passed or failed
• Metadata is data about data.
  • It includes table name, column name, data type, authorized user and user access privileges for any table is metadata
• Data dictionary is an information repository to contain metadata.
  • It includes table name, column name - e.g. EmpName, Address, Mob, Subject, Salary
• A data warehouse is a repository which stores data
• Field is a character or group of character with specific meaning
• Record/Tuple is a collection of logically related fields

Three-Level ANSI SPARC Database System Architecture

• This architecture includes three levels: internal, conceptual, and external.

Internal Level

• Details data storage at physical level.
• Deals with physical storage of data.
• Focuses on how data is stored on the storage device with indexes and the ordering of records.
• The internal view is described by the internal schema.

Conceptual Level

• It defines stored and relationships.
• It stores what data are stored in the database, and their relationships.
• Hides complexities of physical storage.
• A database administrator determines what data to keep in the database.

External Level

• Concentrates on the view.
• End users access only part of the database that they need
• Describes only part of the entire database that an end user concern or how data are viewed by each user.

Data Abstraction

• Databases have complex structures.
• Hiding internal details eases user interaction.
• Irrelevant details are hidden from user.

Mapping

• Transforming requests and results between levels in the database.

Data Independence

• Refers to the ability to modify a schema definition at one level without affecting a schema definition at the next higher level
• Physical Data Independence is the ability to modify the physical schema without requiring any change in logical (conceptual) schema
• Logical data independence is the ability to modify the conceptual schema without requiring any change in application programs.

Instances and Schemas

• Instance: Data collection at a particular moment.

• Schema: The overall design of the database.

• Instance: Changes frequently.

• Schema: Changes rarely. Database Models

• A database model defines the logical structure of a database

• It also determines the accessed and updated way of the data in a DBMS

• A Relational model is most popular by using a table-based format

Type of Database Models

• Hierarchical Model
• Network Model
• Entity-relationship Model
• Relational Model
• Object-oriented Database model

Hierarchical Model

• In hierarchical model, it organizes data into a tree-like structure, where data is connected from root to departement
• The hierarchy expands like a tree, with child nodes to the parent nodes
• Data is organized into a tree-like structure with a one-to-many relationship between different entities

Network Model

• With the network model, it is the extension of the hierarchical model
• It allows many-to-many relationships in a tree like structure

Entity-Relationship Model

• The model has relationships with dividing object into entity and its characteristics such as attribute

Relational Model

• Data is organised in two-dimensional tables
• Related with storing a common attribute

Object-Oriented database model

• This is another way to represent real objects
• Each object is object and isoloates with one another Groups related functionality

Database Langauges

• Database systems provides a data definition language to specify the database schema with data manipulation language and a data control language to control a database
• In practice, it is not separate from single database language such as the SQL
• Data-Definition language supports the creation, deletion, and modification of tables and views
• Syntax of DDL is create table account
• Data dictionary or data directory includes in addition, a special set of tables
• Data manipulation has The retrieval, insertion, deletion and modification of information stored in the database as a language to access or manipulate data
• Procedural DMLs needs a user to specify what data are needed and how to get those data

Declarative DMLS

• Also referred to as nonprocedural
• A user needs what data is needed

Data-Control Language

• A subset of SQL to control a database, including administrative privileges and saving data

Database Access from Application Programs

• A programs that are used to interact with the database
• Application programs are usually written in a host language, such as Cobol, C, C++, or Java
• Two ways of implement access
• Providing Application program with providing and retieving
• The Open Database Connectivity (ODBC) standard by Java as commonly used
• Extending host language syntax
• A charater prefaces DML with DML precompiler

Types of database users

• Naive Users are unsophisticated users
• Application Programers are people who write software
• Sophisticated Users interact with datrabase without application program
• Specialized Users are DBAs that write specialized program

Role of DBA (Database Adminstrator)

• Schema Definition where DBMS defines the logical schema
• Storage Structure and Access Method Definition on how data is represented on database
• Defines sercurity and integrity constraints
• Provide necessary data with uses
• Assistance to app programs
• Performance of dba
• Back and Recovery

Transaction Management

• A single logical unit of work
• It is essential that credit and debit and all or non ehtirety
• Atomicity, consistency, Isolation, durabiltiy

Durability

• Is permenance of data

Database system Architrcture

• A multi phase software for managing database

Query Processor

• Helps system facilite data
• Translates queries to perform

Storage manager to provides data stores in the database

• Manangae compposents to help users, maintain files, maintaiin buffers