DBMS_notes roadmap by love babbar

 DATABASE:

A database is an organized collection of structured information, or data, stored electronically in a computer system.

DATASTRUCTURE USED BY DATABASE:

The most frequently used data structures for one-dimensional database indexes are dynamic tree-structured indexes such as B/B+-Trees and hash-based indexes using extendible and linear hashing.

DBMS(DATABASE MANAGEMENT SYSTEM):
A Database Management System (DBMS) is software designed to store, retrieve, update, define, and manage data in a database.

NEEDS OF DBMS:

ü  Controlling redundancy(redundancy refers to repeated instances(The situation where a data or information is stored in the database at a particular moment of time is called an instance.) of the same data.) and inconsistency. A DBMS uses data normalization to avoid redundancy and duplicates.

ü  Efficient memory management and indexing

ü  Concurrency control (Concurrency Control in Database Management System is a procedure of managing simultaneous operations without conflicting with each other.) and transaction management(used to schedule the access of data concurrently. It means that the user can access multiple data from the database without being interfered with each other.)

ü  Access Control and ease in accessing data

ü  Integrity constraints (used to maintain the quality of information)

FILE SYSTEM VS DBMS

FILE SYSTEM	DBMS
Redundant data can be present in a file system.	In DBMS there is no redundant data.
It doesn’t provide backup and recovery of data if it is lost.	It provides backup and recovery of data even if it is lost.
There is no efficient query processing in file system.	Efficient query processing is there in DBMS.
There is less data consistency in file system.	There is more data consistency because of the process of normalization.

 

DBA AND FUNCTION

Database administrators ensure databases run efficiently. Database administrators and architects create or organize systems to store and secure a variety of data.

Functions:

1.      Conceptual Architecture Schema.

2.      Authorization and Integrity Schema.

3.      Granting of User Access.

4.      Backup and Recovery Mechanisms.

5.      Monitoring Performance.

6.      Physical Database Design.

 

 

DATABASE ARCHITECTURE:

A Database Architecture is a representation of DBMS design. It helps to design, develop, implement, and maintain the database management system.

Types of DBMS Architecture

1.      Tier Architecture

2.      Tier Architecture

3.      Tier Architecture

Ø  1 Tier Architecture in DBMS is the simplest architecture of Database in which the client, server, and Database all reside on the same machine. A simple one tier architecture example would be anytime you install a Database in your system and access it to practice SQL queries.

Ø  2 Tier Architecture in DBMS is a Database architecture where the presentation layer runs on a client (PC, Mobile, Tablet, etc.), and data is stored on a server called the second tier. Two tier architecture provides added security to the DBMS as it is not exposed to the end-user directly. It also provides direct and faster communication.

Ø  3-Tier database Architecture design is an extension of the 2-tier client-server architecture in which the development and maintenance of functional processes, logic, data access, data storage, and user interface is done independently as separate modules.. A 3-tier architecture has the following layers:

1.      Presentation layer (your PC, Tablet, Mobile, etc.)

2.      Application layer (server)

3.      Database Server

DATABASE LANGUAGES:

Database languages can be used to read, store, retrieve and update the data in the database.

Types of database languages are:

·         DDL (data definition language): It is used to create schema, tables, indexes, constraints, etc. in the database. Create, alter, drop, rename, truncate, comment.

·         DML (Data Manipulation Language): It is used for accessing and manipulating data in a database. It handles user requests. Select, insert, delete, update

·         DCL (Data Control Language): It is used to retrieve the stored or saved data. The DCL execution is transactional. It also has rollback parameters. Grant, revoke.

·         TCL (transaction Control language): It is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction. Commit, rollback.

SCHEMA:

It is a diagrammatical presentation or a blueprint of how the database is constructed. It is a overall description of database.

INSTANCE:

The data stored in database at a particular moment of time is called instance of database. It is the collection of information stored in a database at a particular moment. The data for a single “instance” of a table is stored as a row.

 

SUB-SCHEMA:

A sub-schema is a subset of the schema inherit some properties that a schema has.

DATA ABSTRACTION IN DBMS:

Data Abstraction is a process of hiding unwanted or irrelevant details from the end user. It provides a different view and helps in achieving data independence which is used to enhance the security of data.

There are mainly 3 levels of data abstraction in dbms:

Physical: This is the lowest level of data abstraction. It tells us how the data is actually stored in memory. Suppose we need to store the details of an employee. Blocks of storage and the amount of memory used for these purposes are kept hidden from the user.

Logical: This level comprises the information that is actually stored in the database in the form of tables. It also stores the relationship among the data entities in relatively simple structures. We can store the various attributes of an employee and relationships.

View: This is the highest level of abstraction. Only a part of the actual database is viewed by the users. This level exists to ease the accessibility of the database by an individual user. Users view data in the form of rows and columns.

REFERENTIAL INTEGRITY:

referential integrity can be enforced by working with primary and foreign keys. Each foreign key must have a matching primary key so that reference from one table to another must always be valid.

A referential integrity constraint is specified between two tables. If any record in the table containing the Primary Key (CUSTOMER_MASTER table) is deleted, all the corresponding records of the Foreign Key (ACCOUNTS_MASTER Table) will be deleted to enforce referential integrity.

RDBMS:

The software used to store, manage, query, and retrieve data stored in a relational database is called a relational database management system (RDBMS). The RDBMS provides an interface between users and applications and the database, as well as administrative functions for managing data storage, access, and performance. stores data in a row-based table structure which connects related data elements.

RELATIONAL WORD IN RDBMS:

The 'relational' bit here refers to the relations (also commonly referred to as tables) in the database - the tables and their relationships to each other. The tables 'relate' to each other. It is these relations (tables) and their relationships that make it "relational".

DEGREE OF RELATIONSHIP:

The degree of a relationship is the number of entity types that participate(associate) in a relationship. By seeing an E-R diagram, we can simply tell the degree of a relationship

we have the following degree of relationships:

·         Unary

·         Binary

·         Ternary

·         N-ary

Unary (degree 1)

A unary relationship exists when both the participating entity type are the same. When such a relationship is present we say that the degree of relationship is 1.

Eg. Among the group of student there will be some leads who will manage different club in schools, let the lead be student

Binary (degree 2)

A binary relationship exists when exactly two entity type participates. When such a relationship is present we say that the degree is 2. This is the most common degree of relationship.

Eg. We have customer as an entity and accounts as an other entity. So, customer can have many account and there is has a relationship.

Ternary(degree 3)

A ternary relationship exists when exactly three entity type participates. When such a relationship is present we say that the degree is 3. 

Eg. We have three entity type ‘Employee’, ‘Department’ and ‘Location’. The relationship between these entities are defined as an employee works in a department, an employee works at a particular location.

N-ary (n degree)

An N-ary relationship exists when ‘n’ number of entities are participating. So, any number of entities can participate in a relationship. There is no limitation to the maximum number of entities that can participate. But, relations with a higher degree are not common. This is because the conversion of higher degree relations to relational tables gets complex.

TYPES OF RELATIONALSHIP IN DATABASE TABLE

A relational database collects different types of data sets that use tables, records, and columns. It is used to create a well-defined relationship between database tables so that relational databases can be easily stored.

·         One to One relationship

·         One to many or many to one relationship

·         Many to many relationships

One to One Relationship (1:1): It is used to create a relationship between two tables in which a single row of the first table can only be related to one and only one records of a second table. 

One to Many Relationship: It is used to create a relationship between two tables. Any single rows of the first table can be related to one or more rows of the second tables, but the rows of second tables can only relate to the only row in the first table. It is also known as a many to one relationship.

Representation of many to one relational database

Many to Many Relationship: It is many to many relationships that create a relationship between two tables. Each record of the first table can relate to any records (or no records) in the second table. Similarly, each record of the second table can also relate to more than one record of the first table. It is also represented an N:N relationship.

KEYS:

It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables.

Primary key:

It is the first key which is used to identify one and only one instance of an entity uniquely. A primary key can never have a NULL.

Create table ORDER

(

    Customer_ID int not null,

    Product_ID int not null,

    Order_Quantity int not null,

    Primary key (Customer_ID, Product_ID)

)

Foreign key:

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Super Key:

It is defined as a set of attribute within a table that uniquely identify each record within a table.

Super key is a super set of candidate key. There can be more than one super key in a table.

Roll no	Name	Phone no.	Age
1	Amreen	99783782	20
2	Rohini	91378333	20
3	Kalyani	78612342	18
4	Aparna	92383777	19

 

Here Rollno, and phone no is the set of attribute that can uniquely identify records in a table.

Rollno is unique it can be used to identify records. (rollno, name) here name is not unique but still id cant be same, hence this combination can also be a key. Phoneno. Is unique so it can also be called key.

Candidate key:

A super key without redundancy is called candidate key. Minimum set of attribute used to uniquely differentiate records of a table. Candidate key is the subset of super key. Eg. In the previous table rollno and phone no can be candidate key.

Alternate or secondary key:

A candidate key not selected as a primary key is called alternate or secondary key. Candidate key is an attribute or set of attributes that you can consider as a Primary key. Eg. In the previous table rollno is Primary, super and candidate key, But as there cannot be more than one primary key, and phoneno attribute is also unique but not a primary key so such attributes is called as Alternate or secondary key

DATABASE SCHEMA:

A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful.

• Physical Database Schema − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.

·         Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.

 

 

 

 

 

 

REALTIONAL MODEL:

It's used to represent a collection of tables as well as the relationships between them.

ER-DIAGRAM:

It's used to describe a set of objects known as entities, as well as the relationships between them.

RELATIONAL OPERATIONS:

·         SELECT

·         PROJECT

·         UNION

·         SET DIFFERENCE

·         CARTESIAN PRODUCT

·         RENAME

 

Select:

The select operation selects tuples that satisfy a given predicate. It is denoted by sigma (σ).

σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

 

 

σ BRANCH_NAME="perryride" (LOAN)  

input

Branch	Loan_no	Amount
Lockwood	l-19	2375
PerryRide	l-12	4875
Roundhill	l-09	84394
PerryRide	l-16	234992

 

Output

Branch	Loan_no	Amount
PerryRide	l-12	4875
PerryRide	l-16	234992

Project:

This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table. It is denoted by ∏.

∏ Branch, Amount (LOAN)

input

Branch	Loan_no	Amount
Lockwood	l-19	2375
PerryRide	l-12	4875
Roundhill	l-09	84394
PerryRide	l-16	234992

 

output

Branch	Loan_no
Lockwood	l-19
PerryRide	l-12
Roundhill	l-09
PerryRide	l-16

Union:

UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B. It eliminates the duplicate tuples. It is denoted by ∪.

∏ NAME (TABLE_A) ∪ ∏ NAME (TABLE_B) 

Input

TABLE_A

NAME	ID
Aa	121
Bb	612
Cc	343

 

TABLE_B

NAME	ID
Aa	121
Dd	678
Cc	343

Output:

NAME	ID
Aa	121
Bb	612
Cc	343
Dd	678

 

 

 

 

Set difference:

Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S. It is denoted by intersection minus (-).

∏ NAME (TABLE_A) - ∏ NAME (TABLE_B) 

TABLE_A

NAME	ID
Aa	121
Bb	612
Cc	343
Ee	234

 

output

TABLE_B

NAME	ID
Aa	121
Dd	678
Cc	343
Ff	343

NAME

Bb

Ee

 

Cartesian product:

The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product. It is denoted by X.

EMPLOYEE X DEPARTMENT 

input

E_NAME	ID
Aa	121
Bb	612
Cc	343

Output

Dept_NAME	ID
Sales	121
Marketing	678

E_Name	ID	Dept_Name	ID
Aa	121	Sales	121
Aa	121	Marketing	678
Bb	612	Sales	121
Bb	612	Marketing	678
Cc	343	Sales	121
Cc	343	Marketing	678

 

Rename:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to STUDENT1.

ρ(STUDENT1, STUDENT)  

 

 

JOINS:

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Here are the different types of the JOINs in SQL:

(INNER) JOIN:

Returns records that have matching values in both tables

Orders

Order_id	Customer_id
45321	2
14233	4
12133	1

 

Customers:

Customer_id	name
1	Aa
2	Bb
4	Cc

Output:

SELECT Orders.Order_id, Customers.name //it will display
FROM Orders
INNER JOIN Customers ON Orders.Customer_id = Customers.Customer_id;

 

Order_id	Name
45321	Bb
14233	Cc
12133	Aa

LEFT (OUTER) JOIN:

Returns all records from the left table, and the matched records from the right table.

Customers:

Customer_id	Name
1	Aa
2	Bb
3	Cc

 

Orders:

order_id	Customer_id
1120	1
2132	8
3832	3

Output:

SELECT Customers.Name, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Name	Order_id
Aa	1120
Bb	Null
Cc	3832

 

RIGHT (OUTER) JOIN:

 Returns all records from the right table, and the matched records from the left table.

Customers:

Customer_id	Name
1	Aa
2	Bb
3	Cc

 

Orders:

order_id	Customer_id
1120	1
2132	8
3832	3

SELECT customers.Name, Orders.Order_id
FROM Customer
RIGHT JOIN orders
ON customers.id = orders.customer.id
ORDER BY orders.order_id;

Customer_id	Order_id
Aa	1120
	2132
Cc	3832

 

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

 

VIEWS:

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database.

CREATE VIEW AS students_view SELECT students.id, students.name FROM students WHERE id<5;

TRIGGERS:

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

create trigger Trigger_name

(before | after)

[insert | update | delete]

on [table_name] 

[for each row] 

[trigger_body]

CREATE trigger adds before

INSERT on marks FOR EACH ROW

SET marks.mark = marks.mark+10;

INSERT INTO marks VALUES (4, 50);

 

SQL injection

SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.

DELETE VS TRUNCATE

DELETE is a DML(Data Manipulation Language) command and is used when we specify the row(tuple) that we want to remove or delete from the table or relation. The DELETE command can contain a WHERE clause. If WHERE clause is used with DELETE command then it remove or delete only those rows(tuple) that satisfy the condition otherwise by default it removes all the tuples(rows) from the table.

DELETE FROM TableName 

WHERE condition; 

 

TRUNCATE is a DDL(Data Definition Language) command and is used to delete all the rows or tuples from a table. Unlike the DELETE command, TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data page is recorded. Unlike the DELETE command, the TRUNCATE command is fast. We cannot rollback the data after using the TRUNCATE command.

TRUNCATE TABLE  TableName;

 

 

 

SQL PRIVILEGES:

SUBQUERY:

It is a nested query embedded within a where clause.

SELECT * 

   FROM CUSTOMERS 

   WHERE ID IN (SELECT ID 

         FROM CUSTOMERS 

         WHERE SALARY > 4500) ;

 

CLUSTERED AND NON-CLUSTERED:

Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML operations on Table by User. Cursors are used to store Database Tables. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are explained as following below.

1.      Implicit Cursors:
Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors are allocated by SQL SERVER when the user performs DML operations.

2.      Explicit Cursors :
Explicit Cursors are Created by Users whenever the user requires them. Explicit Cursors are used for Fetching data from Table in Row-By-Row Manner.

QUERIS TO PRACTICE:

1. maximum salary:

SELECT MAX(salary) FROM emp_table;

2. finding Nth highest salary

To find nth highest salary we should find n highest element.

So,

SELECT * FROM (SELECT DISTINCT sal FROM emp ORDER BY sal DESC) WHERE rownum<=3;

Minus

SELECT * FROM (SELECT DISTINCT sal FROM emp ORDER BY sal DESC) WHERE rownum<=2;

DISTINCT to eliminate same values.

DECS to make it in decreasing order.

Rownum<= 3 for displaying first 3 elements.

To display only 3^rd highest salary we will minus remaining 2.