DBMS PRELIM

Database Management System (DBMS)

is a software system that is designed to manage and organize data in a structured manner

Data modeling

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

Data storage and retrieval

A DBMS is responsible for storing and retrieving data from the database and can provide various methods for searching and querying the data.

Concurrency control

A DBMS provides mechanisms for controlling concurrent access to the database, to ensure that multiple users can access the data without conflicting with each other

Data integrity and security

A DBMS provides tools for enforcing data integrity and security constraints, such as constraints on the values of data and access controls that restrict who can access the data

Backup and recovery

A DBMS provides mechanisms for backing up and recovering the data in the event of a system failure.

RDBMS

Data is organized in the form of tables and each table has a set of rows and columns. The data are related to each other through primary and foreign keys.

NoSQL

Data is organized in the form of key-value pairs, documents, graphs, or column-based. These are designed to handle large-scale, high-performance scenarios.

Database

is a collection of interrelated data which helps in the efficient retrieval, insertion, and deletion of data from the database and organizes the data in the form of tables, views, schemas, reports, etc.

Data Definition Language (DDL)

which deals with database schemas and descriptions, of how the data should reside in the database.

CREATE

to create a database and its objects like (table, index, views, store procedure, function, and triggers)

ALTER

alters the structure of the existing database

DROP

delete objects from the database

TRUNCATE

remove all records from a table, including all spaces allocated for the records are removed

COMMENT

add comments to the data dictionary

RENAME

rename an object

Data Manipulation Language (DML)

which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete, and update data in a database.

SELECT

retrieve data from a database

INSERT

insert data into a table

UPDATE

updates existing data within a table

DELETE

Delete all records from a database table

MERGE

UPSERT operation (insert or update)

CALL

call a PL/SQL or Java subprogram

EXPLAIN PLAN

interpretation of the data access path

LOCK TABLE

concurrency Control

Data Control Language (DCL)

which acts as an access specifier to the database.

GRANT

grant permissions to the user for running DML (SELECT, INSERT, DELETE…) commands on the table

REVOKE

revoke permissions to the user for running DML (SELECT, INSERT, DELETE…) command on the specified table

Transactional Control Language (TCL)

which acts as a manager for all types of transactional data and all transactions.

Roll Back

Used to cancel or Undo changes made in the database.

Commit

It is used to apply or save changes in the database.

Save Point

It is used to save the data on the temporary basis in the database.

Data Retrieval Language (DRL)

which is used for retrieval of data. It can also be said as DML.

Enterprise Information

Sales, accounting, human resources, Manufacturing, online retailers.

Banking and Finance Sector

Banks maintaining the customer details, accounts, loans, banking transactions, credit card transactions. Finance: Storing the information about sales and holdings, purchasing of financial stocks and bonds.

University

Maintaining the information about student course enrolled information, student grades, staff roles.

Airlines

Reservations and schedules.

Telecommunications

Prepaid, postpaid bills maintenance.

Redundancy of data

Data is said to be redundant if the same data is copied at many places.

Inconsistency of Data

Data is said to be inconsistent if multiple copies of the same data do not match each other.

Difficult Data Access

A user should know the exact location of the file to access data, so the process is very cumbersome and tedious.

Unauthorized Access

File Systems may lead to unauthorized access to data

No Concurrent Access

The access of the same data by multiple users at the same time is known as concurrency.The file system does not allow concurrency as data can be accessed by only one user at a time.

No Backup and Recovery

The file system does not incorporate any backup and recovery of data if a file is lost or corrupted

Data organization

A DBMS allows for the organization and storage of data in a structured manner, making it easy to retrieve and query the data as needed.

Data integrity

A DBMS provides mechanisms for enforcing data integrity constraints, such as constraints on the values of data and access controls that restrict who can access the data

Data security

A DBMS provides tools for managing the security of the data, such as controlling access to the data and encrypting sensitive data.

Backup and recovery

A DBMS provides mechanisms for backing up and recovering the data in the event of a system failure.

Data sharing

A DBMS allows multiple users to access and share the same data, which can be useful in a collaborative work environment.

Complexity

DBMS can be complex to set up and maintain, requiring specialized knowledge and skills.

Performance overhead

The use of a DBMS can add overhead to the performance of an application, especially in cases where high levels of concurrency are required.

Scalability

The use of a DBMS can limit the scalability of an application since it requires the use of locking and other synchronization mechanisms to ensure data consistency.

Cost

The cost of purchasing, maintaining, and upgrading a DBMS can be high, especially for large or complex systems.

Limited Use Cases

Not all use cases are suitable for a DBMS, some solutions do not need high reliability, consistency or security and may be better served by other types of data storage.

DBMS Architecture

helps users to get their requests done while connecting to the database.

1-Tier Architecture

the database is directly available to the user, the user can directly sit on the DBMS and use it that is, the client, server, and Database are all present on the same machine.

2-Tier Architecture

is like a basic client-server model. The application at the client end directly communicates with the database on the server side.

3-Tier Architecture

there is another layer between the client and the server

Entity

anything that has attributes, real-life objects

Attributes

Entities are represented by means of their properties

Simple attribute

are atomic values, which cannot be divided further. For example, student's phone-number is an atomic value of 10 digits.

Composite attribute

are made of more than one simple attribute. For example, a student's complete name may have first_name and last_name.

Derived attribute

are attributes, which do not exist physical in the database, but there values are derived from other attributes presented in the database

Single-valued attribute

contain on single value. For example: Social_Security_Number

Multi-value attribute

may contain more than one values. For example, a person can have more than one phone numbers, email_addresses etc.

Key

is an attribute or collection of attributes that uniquely identifies an entity among entity set

Super Key

Set of attributes (one or more) that collectively identifies an entity in an entity set

Candidate Key

Minimal super key

Primary Key

This is one of the candidate key chosen by the database designer to uniquely identify the entity set.

Relationship

The association among entities

Relationship Set

Relationship of similar type

Cardinality

defines the number of entities in one entity set which can be associated to the number of entities of other set via relationship set.

One-to-one

one entity from entity set A can be associated with at most one entity of entity set B and vice versa.

One-to-many

One entity from entity set A can be associated with more than one entities of entity set B but from entity set B one entity can be associated with at most one entity.

Many-to-one

More than one entities from entity set A can be associated with at most one entity of entity set B but one entity from entity set B can be associated with more than one entity from entity set A.

Many-to-many

one entity from A can be associated with more than one entity from B and vice versa

Binary relationship

When only one instance of entity is associated

Data model

tells how the logical structure of a database is modeled and are fundamental entities to introduce abstraction in DBMS.

Entity-Relationship model

is based on the notion of real world entities and relationship among them.

Domain

Every attribute is defined by its set of values

Database schema

skeleton structure of and it represents the logical view of entire database. It tells about how the data is organized and how relation among them is associated.