Databases and DBMS Overview

• A database is an organized collection of data that is easy to access, manage, and update; student records in a university are an example.
• A DBMS (Database Management System) is a software application that interacts with a database to manage data efficiently; it is exemplified by MySQL, PostgreSQL, and MongoDB.
• Databases store large amounts of data, ensure data consistency and accuracy, and allow for quick access and manipulation.
• Data consists of raw facts and figures that can be processed to produce information.
• Information is data that is processed, organized, or structured to add meaning and context and is useful for decision-making.

Traditional File System vs. Database Management System

• Traditional file systems store data in flat files, leading to high redundancy and inconsistency, lack of data integrity and security, difficulty in data sharing, and poor scalability; examples include text files, Excel sheets, and indexed files.
• Database Management Systems centralize and share data, reduce redundancy, improve consistency, and provide better data integrity, security, and scalability.

DBMS Users

• A DBMS user interacts with data daily, updating, reading, and modifying data and can access and retrieve data from the database through DBMS applications and interfaces.
• Types of DBMS users include Database Administrators (DBA), End Users, Application Developers, System Analysts, and Database Designers.
• Database Administrators (DBA) manage and maintain the database system.
• End Users interact with the database via applications.
• Application Developers develop software that uses the database.
• Database Designers design the structure and operations of the database system.
• System Analysts analyze the requirements of end-users.

DBMS Languages

• DBMS Languages consist of commands or instructions used to communicate with a database.
• Types of DBMS Languages include Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transactional Control Language (TCL).

Data Definition Language (DDL)

• DDL deals with database schemas and descriptions of how data should reside in the database.
• DDL is used to define the database's internal structure and pattern.
• The functions for DDL are Create, Alter, and Drop.
• The functions for DDL are Truncate, Rename, and Comment.
• Create is used to create objects in the database, like the following example:

CREATE TABLE Students (
column1 INT,
column2 VARCHAR(50),
column3 INT
);

• Alter is used to change or alter the structure of database objects.
• Drop is used to delete objects from the database.
• Truncate is used to remove all records from a table.
• Rename is used to rename objects in the database.
• Comment is used for comments on the data dictionary.

Data Manipulation Language (DML)

• DML is used to manipulate data in the database using different commands.
• Common DML commands are Select, Insert, and Update.
• Further DML commands include Delete, Merge, Call, and Lock Table.
• Select is used to select data from the table based on requirements.
• Insert is used for inserting data into an existing table.
• Update is used to update data in the table based on the requirement.
• Delete is used to delete data from the table.
• Merge is used for upsert operations.
• Call is used to call a structured query language or Java sub-program.
• Lock Table can control concurrency.

Data Control Language (DCL)

• DCL controls user access to the database.
• The functions for DCL are Grant and Revoke.
• Grant is used to give users access to the database.
• Revoke is used to take back access or permissions from the user.

Transaction Control Language (TCL)

• TCL manages transactions within a database.
• The functions for TCL are Commit and Rollback.
• Commit is used to save a transaction on the database.
• Rollback is used to restore the database to its original state from the last commit.

Schema, Subschema, and Instance

• A schema is the overall structure or design of a database, serving as a blueprint that defines data organization and relationships.
• The relationship between Students and Courses is that Students are enrolled in courses.
• A subschema is a subset of the schema specifically designed for a particular application or user group; it defines how a particular user/application views the database.
• An example is that in a university database, an admin can view all student details vs a student who can view their grades and course schedule.
• An instance is the actual data stored in the database at a specific moment; it is the current state of the database.

3 Level Architecture of DBMS

• The 3 levels of architecture of the DBMS are Internal, Conceptual and External.
• The External Level is the physical storage of data and how data is stored.
• The Conceptual Level is the logical structure of the entire database and what data is stored.
• The External Level includes user views of the database and how users interact with the data.

Internal Level

• The internal level includes index tables, identifying which types of storage media to support, determining where to write log files, performing low-level authentication, and selecting what data types to implement and how.

Conceptual Level

• The conceptual level includes implementing database designs captured in an entity relationship diagram (ERD), creating indexes for commonly queried tables, assigning security attributes to tables and columns and choosing from a variety of different data types.

External Level

• The external level allows users to see only the data they need without unnecessary details, can have multiple external views for a database, and provides role-based access control.

Data Models

• Data Models define how data is logically organized, represented, and related within a database system.
• A data model describes how data is structured and how it interacts with other data.
• A data model defines entities, which are objects/things in the database, e.g., Students, Employees.
• A data model also defines Attributes, which are properties of entities, e.g., Name, Age.
• A data model also defines Relationships, which are associations between entities, e.g., A student "enrolls in" a course.
• The purpose of data models is to provide a standardized way to organize and manage data, simplify design by modeling real-world scenarios, and ensure data consistency, integrity, and security.
• Types of Data Models include Hierarchical, Network, Relational, Object-Oriented, Entity-Relationship (ER), and Semi-Structured Data Models.

Types of Database Systems

• Centralized databases store data at a single location.
• Distributed databases spread data across multiple locations but managed as a single system.
• Cloud databases are hosted on cloud platforms, e.g., Google Cloud, AWS.
• NoSQL databases are designed for unstructured/semi-structured data, e.g., MongoDB.
• Operational databases support daily operations, e.g., banking systems.
• Analytical databases are used for decision-making and analysis, e.g., data warehouses.