DBMS QUIZ 1

Database Management System

is a software system designed to manage and organized data in structured manner.

Data Modeling

A DBMS provides tools for creating and modifying data models that define the structure and relationships of the data in a database.

Data storage and retrieval

A DBMS id responsible for storing and retrieving data from the database, offering various methods for searching and querying data.

Concurrency Control

A DBMS includes mechanisms for controlling concurrent acces, ensuring that multiple users can access data without conflicts.

Data integrity and security

A DBMS enforces data integrity and security constraints, including controls on data vaues and access data restrictions.

Backup and recovery

A DBMS provides mechanism for backing up and recovering data in case of a system failure.

RDBMS

organizes data in tables with rows and columns, relating data through primary and foreign keys

NoSQL

organizes data in key value pairs, documents, graphs or column-based structures designed for large-scale, high performance scenarios

Database

is a collection of interrelated data, which aids in the efficient retrieval, insertion, and deletion of data, organizingit into tables, news, schemas, ad reports.

Data Definition Language (DDL)

deals with database schemas and describes how data should reside in the database.

CREATE

To create a database and its objects (e.g. tables, indexes, views)

ALTER

To modify the structure of an existing database

DROP

To delete objects from the database

TRUNCATE

To remove all records from a table, including allocated spaces

COMMENT

To add comments to the data dictionary

RENAME

To rename an object

Data Manipulation Language (DML)

encompasses common SQL statements like SELECT, INSERT, UPDATE, DELETE etc., used to store, modify, retrieve and delete data in a database.

SELECT

To retrieve data from a database

INSERT

To add data into a table

UPDATE

To modify existing data within a table

DELETE

To remove records from a database table

MERGE

For UPSERT operations (insert and updates)

CALL

To invoke a PL/SQL or java subprogram

EXPLAIN PLAN

To interpret the data access path

LOCK TABLE

For concurrency control

Data Control Language (DCL)

serves as an access specifier for the database, allowing for permission management

GRANT

To provide permissions for executing DML commands

REVOKE

To withdraw permissions for executing DML commands

Transactional Control Language (TCL)

manages transactional data and transactions

ROLLBACK

To cancel or undo changes made in the database

COMMIT

To apply or save changes in the database

SAVEPOINT

To save data temporarily in the database

Enterprise Information

Used in sales, accounting, human resources, manfacturing, and online retailers.

Banking and Finance Sector

Banks maintain customer details, accounts, loans and transactions

University

Maintains information about students, courses, grades and staff roles

Airlines

Manages reservations and schedules

Telecommunications

Manages prepaid and postpaid billing

File system

manages data through files on a hard disk, allowing users to create, delete, and update those files

Redundancy of data

Data may be copied in multiple locations, complicating updates

Inconsistency of data

Different copies of the same data may not match, leading to confusion

Difficult data access

Users must know exact file locations, which can be cumbersome

Unauthorized access

File systems mey permit unauthorized data access

No concurrent access

Data can only be access by one user at a time

No backup and recovery

File systems mechanisms for data recovery

Data organization

A DBMS organizes and stores data systematically, facilitating easy retrieval

Data integrity

Mechanisms enforce data integrity constraints and access controls

Concurrent access

Allows multiple users to access data simultaneously

Data security

Tools manage data security, including control and encryption

Complexity

Setting up and maintaining a DBMS can be complex and requires speialized knowledge

Performnace overhead

DBMS can introduce perfromance overhead, particularly under high concurrency

Scalability

DBMS may limit scalability due to locking and synchronization mechanisms

Cost

The cost of purchasing and maintaining a DBMS can be high

Limited use cases

Not all applications require the reliability and security a DBMS provides

Database Architecture

a database stores critical information for quick and secure data access

1-Tier Architecture

the database is directly accessible to user, the client, server and databse are on the same machine

Simple Architecture

Easy to set up with only one machine

Cost-Effective

No additional hardware is required

Easy to Implement

Mostly used in small projects

2-Tier Architecture

the client communicates directly with the database server

Easy to access

Facilitates fast data retrieval

Scalable

Easily saclable by adding clients or upgrading hardware

Low Cost

Cheaper that 3-tier architecture

Easy Deployment

Simpler to deploy than 3-tier architecture

Simple

Understandable due to its two-component structure

3-Tier Architecture

an applicattion server acts as an intermediary between the client and the database, enhancing communication and data processing

Enhanced Scalability

Disrupted deployment of application servers improves scalability

Data Integrity

Maintains data integrity by separating client and server interactions

Security

Reduces unauthorized data access via an intermediary layer

More Complex

Increased complexity compared to 2-tier architecture

Difficult to interact

Interaction is complicated due to presence of middle layer