What is RDBMS (Relational Database Management System):
RDBMS stands for Relational Database Management System.
All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access are based on RDBMS.
It is called Relational Database Management System (RDBMS) because it is based on the relational model introduced by E.F. Codd.
Cardinality:
The total number of tuples at any one time in a relation is known as the table's cardinality. The relation whose cardinality is 0 is called an empty table.
NULL Values:
The NULL value of the table specifies that the field has been left blank during record creation. It is different from the value filled with zero or a field that contains space.
Data Integrity:
There are the following categories of data integrity exist with each RDBMS:
Entity integrity: It specifies that there should be no duplicate rows in a table.
Domain integrity: It enforces valid entries for a given column by restricting the type, the format, or the range of values.
Referential integrity specifies that rows cannot be deleted, which are used by other records.
User-defined integrity: It enforces some specific business rules defined by users. These rules are different from the entity, domain, or referential integrity.
Difference between DBMS and RDBMS :
DBMS | RDBMS |
|---|---|
DBMS applications store data as file. | RDBMS applications store data in a tabular form. |
In DBMS, data is generally stored in either a hierarchical form or a navigational form. | In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables. |
Normalization is not present in DBMS. | Normalization is present in RDBMS. |
DBMS does not apply any security with regards to data manipulation. | RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property. |
DBMS is meant to be for small organization and deal with small data. it supports single user. | RDBMS is designed to handle large amount of data. it supports multiple users. |
DBMS does not support distributed database. | RDBMS supports distributed database. |
Examples of DBMS are file systems, xml etc. | Example of RDBMS are mysql, postgre, sql server, oracle etc. |
Types of DBMS Architecture:
Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two types like: 2-tier architecture and 3-tier architecture.
1-Tier Architecture:
In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response.
2-Tier Architecture:
The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API's like: ODBC, JDBC are used.
The user interfaces and application programs are run on the client-side.
The server side is responsible to provide the functionalities like: query processing and transaction management.
To communicate with the DBMS, client-side application establishes a connection with the server side.
3-Tier Architecture:
The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.
The application on the client-end interacts with an application server which further communicates with the database system.
End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
The 3-Tier architecture is used in case of large web application.
Data Models:
Data Model is the modeling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction.
1) Relational Data Model:
2) Entity-Relationship Data Model:
3) Object-based Data Model:
4) Semistructured Data Model:
Data Independence:
Logical Data Independence
Physical Data Independence
Types of Database Languages:
1. Data Definition Language (DDL):
DDL stands for Data Definition Language. It is used to define database structure or pattern.
It is used to create schema, tables, indexes, constraints, etc. in the database.
Using the DDL statements, you can create the skeleton of the database.
Create: It is used to create objects in the database.
Alter: It is used to alter the structure of the database.
Drop: It is used to delete objects from the database.
Truncate: It is used to remove all records from a table.
Rename: It is used to rename an object.
Comment: It is used to comment on the data dictionary.
2. Data Manipulation Language (DML):
DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests.
Select: It is used to retrieve data from a database.
Insert: It is used to insert data into a table.
Update: It is used to update existing data within a table.
Delete: It is used to delete all records from a table.
Merge: It performs UPSERT operation, i.e., insert or update operations.
Call: It is used to call a structured query language or a Java subprogram.
Explain Plan: It has the parameter of explaining data.
Lock Table: It controls concurrency.
3. Data Control Language (DCL):
DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
The DCL execution is transactional. It also has rollback parameters.
Grant: It is used to give user access privileges to a database.
Revoke: It is used to take back permissions from the user.
4. Transaction Control Language (TCL):
TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction.
Commit: It is used to save the transaction on the database.
Rollback: It is used to restore the database to original since the last Commit.
SQL:
SQL stands for Structured Query Language. It is used for storing and managing data in relational database management system (RDMS). In RDBMS data stored in the form of the tables.
It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.
All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language.
SQL allows users to query the database in a number of ways, using English-like statements.
SQL Commands:
SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.
1. Data Definition Language (DDL):
CREATE TABLE TABLE_NAME (COLUMN_NAMES DATATYPES [ ...]);
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);
DROP TABLE table_name [cascade constraint];
DROP TABLE EMPLOYEE;
ALTER TABLE table_name ADD column_name column-definition;
ALTER TABLE STU_DETAILS ADD (ADHAR_NUM VARCHAR2 (15));
ALTER TABLE STU_DETAILS ADD ADHAR_NUM, NAME;
ALTER TABLE table_name MODIFY (column definitions....);
ALTER TABLE STU_DETAILS MODIFY (ADHAR_NUM VARCHAR2 (20));
ALTER TABLE STU_DETAILS MODIFY (ADHAR_NUM VARCHAR2 (20) NOT NULL);
TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.
TRUNCATE TABLE table_name;
TRUNCATE TABLE EMPLOYEE;
Rename <OLD_TABLENAME> to <NEW_TABLENAME>;
Rename STU_NAME to STUDENT_NAME;
2. Data Manipulation Language:
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... valueN), (value1, value2, value3, .... valueN);
INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS"), ("Raman", "DBMS"), ("Priya", "DBMS");
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
DELETE FROM table_name [WHERE condition];
DELETE FROM javatpoint WHERE Author="Sonoo";
SELECT expressions FROM TABLES WHERE conditions;
SELECT emp_name FROM employee WHERE age > 20;
3. Data Control Language:
GRANT <obj_priv> ON <obj_name> To <username>;
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
REVOKE <obj_priv> ON <obj_name> FROM <username>;
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
4. Transaction Control Language:
COMMIT;
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;
ROLLBACK;
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;
SAVEPOINT SAVEPOINT_NAME;
Rollback TO <save_point_name>
DELETE FROM CUSTOMERS WHERE AGE = 15;
SAVEPOINT A;
DELETE FROM CUSTOMERS WHERE AGE = 35;
ROLLBCAK TO A;
SQL Arithmetic Operators:
Operator | Description | Example |
+ | It adds the value of both operands. | a+b will give 30 |
- | It is used to subtract the right-hand operand from the left-hand operand. | a-b will give 10 |
* | It is used to multiply the value of both operands. | a*b will give 200 |
/ | It is used to divide the left-hand operand by the right-hand operand. | a/b will give 2 |
% | It is used to divide the left-hand operand by the right-hand operand and returns reminder. | a%b will give 0 |
SQL Logical Operatos:
Operator | Description |
ALL | It compares a value to all values in another value set. |
AND | It allows the existence of multiple conditions in an SQL statement. |
ANY | It compares the values in the list according to the condition. |
BETWEEN | It is used to search for values that are within a set of values. |
IN | It compares a value to that specified list value. |
NOT | It reverses the meaning of any logical operator. |
OR | It combines multiple conditions in SQL statements. |
EXISTS | It is used to search for the presence of a row in a specified table. |
LIKE | It compares a value to similar values using wildcard operator. |
SQL SELECT Statement:
SELECT * FROM table_name;
SELECT * FROM EMPLOYEE;
SELECT column_name1, columnN FROM table_name;
SELECT EMP_ID FROM EMPLOYEE;
SELECT EMP_NAME, SALARY FROM EMPLOYEE;
SELECT DISTINCT CITY, SALARY FROM EMPLOYEE;
SQL INSERT Statement:
The SQL INSERT statement is used to insert a single or multiple data in a table. In SQL, You can insert the data in two ways:
Without specifying column name
By specifying column name
INSERT INTO TABLE_NAME VALUES (value1, value2, value 3, .... Value N);
INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);
INSERT INTO TABLE_NAME [(col1, col2, col3,.... col N)] VALUES (value1, value2, value 3, .... Value N);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);
SQL Update Statement:
UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;
UPDATE table_name SET column_name = value WHERE condition;
UPDATE EMPLOYEE SET EMP_NAME = 'Emma' WHERE SALARY = 500000;
UPDATE table_name SET column_name = value1, column_name2 = value2 WHERE condition;
UPDATE EMPLOYEE SET EMP_NAME = 'Kevin', City = 'Boston' WHERE EMP_ID = 5;
DELETE FROM table_name WHERE some_condition;
DELETE FROM EMPLOYEE WHERE EMP_NAME = 'Kristen';
DELETE FROM EMPLOYEE WHERE AGE= 30;
DELETE * FROM table_name; or DELETE FROM table_name;
DELETE FROM EMPLOYEE;
SQL Aggregate Functions:
SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
It is also used to summarize the data.
Types of SQL Aggregation Function:
1. COUNT FUNCTION:
COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.
Syntax:
SELECT COUNT(*) FROM PRODUCT_MAST;
SELECT COUNT(*) FROM PRODUCT_MAST; WHERE RATE>=20;
2. SUM Function:
Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
Syntax:
SUM() or SUM( [ALL|DISTINCT] expression )
SELECT SUM(COST) FROM PRODUCT_MAST;
SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;
3. AVG function:
The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.
Syntax:
AVG() or AVG( [ALL|DISTINCT] expression )
SELECT AVG(COST) FROM PRODUCT_MAST;
4. MAX Function:
MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
Syntax:
MAX() or MAX( [ALL|DISTINCT] expression )
SELECT MAX(RATE) FROM PRODUCT_MAST;
5. MIN Function:
MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
Syntax:
MIN() or MIN( [ALL|DISTINCT] expression )
SELECT MIN(RATE) FROM PRODUCT_MAST;
SQL JOIN:
As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to combine two or more tables".
Types of SQL JOIN:
INNER JOIN
NATURAL JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
SELF JOIN
CARTESIAN JOIN
SQL Set Operation:
Types of Set Operation:
Union
UnionAll
Intersect
Minus
1. Union:
The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied.
The union operation eliminates the duplicate rows from its resultset.
2. Union All:
Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting the data.
3. Intersect:
It is used to combine two SELECT statements. The Intersect operation returns the common rows from both the SELECT statements.
In the Intersect operation, the number of datatype and columns must be the same.
It has no duplicates and it arranges the data in ascending order by default.
4. Minus:
It combines the result of two SELECT statements. Minus operator is used to display the rows which are present in the first query but absent in the second query.
It has no duplicates and data arranged in ascending order by default.
Thank You
Comments