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.
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.
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.
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.
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.
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 B
Related Posts
About the Author: jitesh gautam
« IBPS SO IT Officer Notes for Multi threading Models IBPS SO IT Officer Notes for DBMS Information Rule »
