Structure Query Language (SQL) | RDBMS

There are many DBMSs present in the market. Do we have to learn all the languages to manipulate the data? The answer to this question is NO! We don't have to learn all the languages. We have a common language named SQL (Structure Query Language). RDBMS also uses SQL to access the database. Users use many applications and those applications use SQL to interact with DBMS to manipulate the data. 

 

What is SQL (Structured Query Language)?

SQL is an ANSI (American National Standard Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update the data in the database. They are not case-sensitive in nature. 

Some key points to write better SQL queries:

• Use pascal notation for object name. Example: Products, Customers
• Use the singular form of nouns for the Column name. Example: FirstName, Address
• Each table must have a primary key. 
• Use upper case for all SQL keywords. Example: SELECT, UPDATE, INSERT, DELETE
• Do not use white space in identifiers.
• Use parentheses to increase readability.
• Indent code to improve readability.
• Use ANSI joins instead of old-style joins.
• Do not use SELECT *
• Always use table aliases when your SQL statement involves more than one table.
• Do not use column numbers in the ORDER BY clause.
• Always use column list in INSERT statements.

These key points might be confusing for you because we have not started learning how to write SQL queries access and insert data in our database. But before moving to that, it is important for us to understand components of SQL

Components of SQL.

SQL is divided into four different components. Let us understand each of them one by one:

DDL (Data Definition Language).

Data Definition Language deals with the structure of Database objects and commands are CREATE, ALERT, TRUNCATE, DROP.

• CREATE: used to create new Database objects like table, view, and stored procedure.
• ALERT: used to modify the existing structure of the database objects.
• TRUNCATE: used to remove all the data from database objects.
• DROP: used to remove the database object from the database.
• RENAME: used to change the name of the existing object.

DML (Data Manipulation Language).

Data Manipulation Language deals with the manipulation of the data in the Database objects and the commands are SELECT, INSERT, UPDATE.

• INSERT: used to insert data into a table.
• UPDATE: used to update existing data within a table.
• DELETE: used to delete records from the table.

TCL (Transaction Control Language).

Transaction Control Language deals with transaction management and the commands are COMMIT, ROLLBACK.

• COMMIT: used to end the current transaction by making all pending data changes permanent. 
• ROLLBACK: used to ends the current transaction by discarding all pending data changes.
• SAVEPOINT: used to mark a savepoint within the current transaction.

DCL (Data Control Language).

Data Control Language deals with providing access privilege of the data by using command GRANT, REVOKE.

• GRANT: used to give user access privilege to the database.
• REVOKE: used to revert back the user access privilege to the database.

DQL (Data Query Language).

DQL command is used to perform quarries to fetch the data from the database within the schema object. We can use the DQL command with JOIN to get the data from multiple tables at one time using Primary key and Foreign key relations. Example: SELECT

So these are the few important components of SQL and in our further articles, we are going to learn the syntax of all the SQL commands with their practical examples. 

Relational Data Model and Its Properties.

In a relational data model, the data is stored in the form of tables with rows and columns. There are a few important terminologies that we all should know before moving forward with the relational data model. 

Let's look at the terminologies used in Relational Data Model:

• Relation refers to the table and Cardinality refers to the number of rows in the table.
• Tuples refer to the row of the table.
• Attributes refer to the column of the table.
• Degree refers to the number of columns in the table.
• Domain refers to the range of values that can be stored for an attribute.

Now the most important thing to understand in Relational Data Model is how we are going to establish a relationship between one table with one or more other tables? The two keys which play a very important role in building a relationship are the primary key and the foreign key. Let us understand these two important terms in more detail.

To build a relation of one table with another table, the table must contain one column which contains a unique key for each row of the table called the primary key. This primary key column must be present in another table as a foreign key column. We can use different kinds of JOIN operations to get the data from more than one table. 

The primary key column cannot hold null values and there should be only one primary key column in each table. 

The foreign key column may contain null values and there can be more than one foreign key column in a single table.(alert-passed)

There are a few important properties of the Relational Data Model that we should kind in mind before we start working with the Relational Database.

• No duplicate Tuples are allowed.
• Tuples are unordered.
• Attributes are unordered.
• Attributes values are atomic.

Relational Database Management System.

A Relational Database Management System is system software that lets us Create, Update and Administer a Relational Database. RDBMS uses SQL (Structured Query Language) to access the database. The few best examples of RDBMS are Oracle, DB2, MYSQL, etc. 

We will learn more about RDBMS and SQL in our next article. You can add your valuable feedback in the comment section below.

Next: Structure Query Language (SQL)

RDBMS Introduction | RDBMS Tutorial

RDBMS stands for Relational Database Management System. It is a system that is used by software to store, manage, query, or retrieve data from the database in which data is stored in the form of tables and one table might be connected with many other tables using primary and foreign key relations. RDBMS provides us the interface between software and database to manage and perform the required operations on the database. Before starting with RDBMS concepts, it is important to understand a few important terms.

What is Information System?

Data with its meaning is referred to as information, where the data means raw fact. In specific, an information system is an organized collection of hardware, software, supplies, and procedures and people who store, process and provide access to information.

What is File Based System?

When information is stored in flat files, which are maintained by the file system under the operating system control. Application programs go through the file system in order to access these flat files. Records consist of various fields, which are delimited by a space, comma, pipe, or any special character, etc.

Maintaining records in a File based system is great and we are able to free up all that space by moving all the data on the computer.(alert-success) 

There are many disadvantages of using the traditional file-based system like:

The application develops in an ad-hoc manner.

Data redundancy, because data can be duplicated in two or more files.

Data isolation, which means all the related data are scattered in various files having a different file format, and hence, writing a new application becomes difficult in retrieving data.

In File Based System it is difficult to produce reports across sales, product, and customer data because they are maintained on a separate file system. (alert-error)

What is Database?

A database is a shared collection of logically related data and the description of this data, designed to meet the needs of an organization. 

Advantages of Database Approach:

• Centralization of Information Management.
• Data is shared by different groups of users and application programs.
• Representation of complex relationship between data.
• Integrity Constraint handling.
• Advanced facility for backup and recovery.

What is Database Management System?

Database Management System (DBMS) is software that helps in defining, creating, and maintaining the database that provides controlled access to the database.

Advantages of Database Management System:

• Shared file system.
• Enforcement of Security. 
• Enforcement of development and maintaining standards.
• Reduction of redundancy.
• Avoidance of inconsistency across files.
• Maintenance of integrity.
• Data Independence. 

• Authentication- Whether the right user has the right to access the database.
• Authorization- Whether the right user has the right to access the database.

We can categorize database users as follows:

Application programmers or Ordinary users: Developer who writes application programs to interact with the database. Application programs can be written in many programming languages like C++, JAVA, C#, or any high-level programming language. Such a program access the database by issuing the appropriate request like SQL statement to DBMS.

Sophisticated users: Users who interact with the system by forming their requests in a database query language. Each such query is submitted to a query processor whose function is to break down the DML statement into instructions that the storage management understands.

End Users: Users who interact with the system by invoking one of the permanent application programs that have been written previously. 

 

DBA(Database Administer): User who manages the database like installation of DB, managing user and DB performance. 

Data Model.

A Data Model is a way of explaining the logical layout of the data and the relationship of various parts to each other on the whole. Different types of Data Models are:

• Hierarchical models refer to storing the data by a tree structure. This model handles only parent-child relationships which are one-to-many relationships. It is not easy to perform, insert, update and delete operations in this model.

• In-Network models data is represented as a graph which Nodes and Edges. It addresses many to many relationships. It has a very complex design.

• Relational Data Model is a widely used data model. The data is stored in the form of tables with rows and columns and it is easy to use because there is no usage of a pointer. Data access is faster than other models. This data model uses the relational algebra concept. 

So this was the basic introduction of database and its type and why do we need a Relational Database Management System. You can add more value to this post by giving your valuable feedback in the comment section below.

Next: Relational Data Model and Its Properties.