Computer Number system Note for IBPS PO

IBPS SO IT Officer Notes for SQL and DBMS

Jan 31 • IBPS Specialist Officer • 1011 Views • No Comments on IBPS SO IT Officer Notes for SQL and DBMS

IBPS SO IT Officer Notes for SQL and DBMS

SQL QUERIES

What is SQL

                   Structured query language

SQL is the language used to query all databases. It is simple learn and appears to do very little but is the heart of a successful database application.

DML  :  DATA MANIPULATION LANGUAGE

                         SELECT :- RETRIEVE  SOME RECORDS FROM TABLE

                          INSERT :- CREATE A RECORD

                          UPDATE : CHANGE OR MODIFY A RECORD

                          DELETE :DELETE RECORDS

DATA CONTROL LANGUAGE

                         GRANT  : GIVE PRIVILEGES TO USER

                         REVOKE : TAKE BACK PRIVILEGES  GRANTED FROM  USER

DATA DEFINITION LANGUAGE

                           CREATE: CREATE A TABLE, VIEW A TABLE,

                            ALTER :- MODIFY  EXISTING TABLE

                            DROP: DELETE AN ENTIRE TABLE

TRANSACTION CONTROL LANGUAGE

                            COMMIT: PERMANENTLY SAVE  ANY TRANSACTION INTO  DATABASE

                            ROLLBACK : RESTORE THE DATABASE  TO LAST COMMITTED STATE

                            SAVEPOINT: TEMPORARILY SAVE A TRANSACTION.

KEYS:

          The relationships between columns located in different tables are  usually described through the use of keys.

GROUP BY AND HAVING CLAUSE:  TO SPECIFY A SEARCH CONDITION IN A GROUP.  HAVING  is used  with select statement. Having is used in GROUP BY clause.  When  GROUP BY is not used then HAVING is behaves like a WHERE.

Database management system

Database is collection of interrelated data and set of program to access this data in a convenient and efficient way.

A DBMS stores data in such a way that it becomes easier to retrieve, manipulate, and give information.

Characteristics

A modern DBMS has the following characteristics −

  • Real-world entity − DBMS is used in real world such as school Database  of all the students.
  • Relation-based tables − DBMS allows entities and relations among them to create tables.
  • Isolation of data and application − A database system is completely different than its data. A database is an active entity, whereas data is said to be real, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process.
  • Less redundancy − DBMS follows the rules of normalization,in which data is splits a relation when any of its attributes is having redundancy in values.
  • Consistency − Consistency is a state where every relation in a database remains consistent. A DBMS can provide greater consistency as compared to other applications like file-processing systems.
  • Query Language − DBMS is equipped with query language, which makes it more easy to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data.
  • ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability. These concepts are applied on transactions, which modify data in a database.
  • Security − DBMS PROVIDE security to some extent where users are unable to access data of other users and departments

Architecture

DBMS is divided into three levels:

External view or user view or view level

It is highest level of data abstraction includes only those portion of database of concern to a user.

Conceptual view/logical level

Includes all the entities and relationship among them. It describes the schemas of database. DBA work at this level.

Internal view or physical level

This is the lowest level of DBMS. it describes how  to store data data and access these data.

Capture

Data models

Data models describes how the logical structure of a database is modeled. Data Models are fundamental entities to define abstraction in a DBMS. Data models describes how data is connected to each other and how they are processed and stored inside the system.

Entity-Relationship Model

Entity-Relationship (ER) Model is based on the concept of real-world entities and relationships among them. In E-R diagram real-world scenario into the database model, the ER Model creates entity set, relationship set and general attributes.

ER Model is  used for the conceptual design of a database.

ER Model is based on −

  • Entity − An entity  is a real-world entity having properties called attributes. Each attribute is defined by its set of values called domain. For example, in a college database, a student is considered as an entity. Student has various attributes such as name, age, class, etc.
  • Relationship − The logical group among entities is called relationship. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of connection between two entities.
  • Mapping cardinalities −
    • one to one
    • one to many
    • many to one
    • many to many

Best Books for Competitive exam preparation.

One to One: An entity of entity-set A can be connect with  one entity of entity-set B and an entity in entity-set B can be connect with  one entity of entity-set A.
Capture

One to Many: An entity of entity-set A can be connect with many number of entities of entity-set B and an entity in entity-set B can be connect with  one entity of entity-set A.

Capture

Many to One: An entity of entity-set A can be connect with one entity of entity-set B and an entity in entity-set B can be connect with any number of entities of entity-set A.

Capture

Many to Many: An entity of entity-set A can be combined with any number of entities of entity-set B and an entity in entity-set B can be combined with any number of entities of entity-set A.

Capture

Capture

Weak entity

The entity set which does not have sufficient attributes to form primary key called  weak entity.

Strong entity

The entity set which have sufficient attributes to form primary key called strong entity.

Join Best Exam Preparation group on Telegram

Generalization

In generalization, a number of entities are grouped together into one generalized entity based on their similar characteristics. For example, pigeon, house sparrow, crow  can all be generalized as Birds.

Specialization

Opposite to generalization. It is a top down approach in which top entity is broken down into lower level entity.

Aggregation

When relation between two entities behave like a single entity.

Dr Edgar F. Codd, came up with twelve rules according to him, a database must obey in order to be referred as a true relational database.

These rules can be applied on any database system

Rule 1: Information Rule

Join us on Telegram and Facebook for BANK exam is also asked in other government exam like BANK IBPS SO RRB SSC. This note has been prepared by Supriya Kundu is of one of best teacher in this field.If any question please ask in below.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

« »