Sql development & DataBase Management

Indexes in Databases: What They Are and Why They Matter

2023-02-25T23:35:00.003+05:00

If you've ever worked with databases, you've likely heard the term "index" before. But what exactly are indexes, and why are they important? In this blog post, we'll explore the basics of indexes in databases and why they matter.

What is an Index?

An index is a data structure that is used to improve the speed of database queries. When you perform a query on a database, the database must search through all of the rows in the table to find the data you're looking for. This can be slow and inefficient, especially if the table is very large.
Indexes provide a way to speed up this process. An index is essentially a list of pointers that point to the locations of data in the table. When you perform a query on the database, the database can use the index to quickly find the data you're looking for, rather than searching through all of the rows in the table.
Why Are Indexes Important?
Indexes are important for several reasons. First and foremost, they can significantly improve the speed of database queries. By using an index, the database can quickly locate the data you're looking for, even if the table is very large.
In addition to improving query speed, indexes can also improve the performance of other database operations, such as sorting and grouping data. Without an index, the database may need to perform a full table scan to sort or group data, which can be slow and resource-intensive.
Finally, indexes can also improve the overall efficiency of the database. By using indexes, the database can avoid performing unnecessary operations, such as full table scans, which can save resources and improve performance.
Types of Indexes

There are several types of indexes that are commonly used in databases. The most common types include:

B-Tree Indexes: B-Tree indexes are the most common type of index and are used in most databases. B-Tree indexes are balanced tree structures that allow for fast searching and sorting of data.
Hash Indexes: Hash indexes are used in some databases and are particularly useful for queries that involve exact matches. Hash indexes use a hash function to map keys to values, which can be used to quickly locate data.
Bitmap Indexes: Bitmap indexes are used for columns with a small number of distinct values. Bitmap indexes use a bitmap to represent each possible value in the column, which can be used to quickly locate data.

Conclusion

Indexes are an essential component of databases and play a crucial role in improving the speed and efficiency of database queries. By understanding the basics of indexes and how they work, you can optimize your database for improved performance and efficiency. Whether you're working with a small database or a large enterprise-level database, indexes are an important tool that should not be overlooked.

Database Normalization

2020-07-17T22:12:00.001+05:00

Here we will discuss four types of Normalization and some important concepts like candidate keys, Prime & Non-Prime attributes

Database Normalization is a technique to remove or reduce redundancy from the tables in the database.

Types of Redundancy

There are two types of redundancies that can occur in database tables.

Row Level Redundancy

In this type of redundancy, there is a repetition of the same row in the same table. For example, a table name as 'STUDENT' in which data of students is entered, if we enter the same student's data multiple time then, in this case, Row Level redundancy occurs as shown below:

In the above case, there is a repetition of a student data whose name is 'Haider', this is what we called row-level redundancy.

The solution of Row Level Redundandancy

This Row-level Redundancy can be corrected by setting a primary column in the table. In this case, we set SID as the primary key column (such column must contain unique & not null values).

Now, this is the Redundancy free table.

Column Level Redundancy

This type of redundancy occurs when the data of the different entities assemble in a single table. For example in the case of a university's database, if we assemble the information of entities like 'Student', 'Course' and 'Faculty' in a single table then Column Level Redundancy occurs as shown below:

In this case, there are 3 types of anomalies that occur when we start working on this database.

Deletion Anomaly

If we want to delete any row from the given table:

Let suppose we want to delete data of student having SID=1 from the given example, we write the following query:

Delete From Student Where SID=1;

The query will be executed successfully but the problem is that along with student data all other important data related to 'Course' & 'Faculty' will also be deleted by the SQL server.

Insertion Anomaly

If we want to insert anything in the given table:

Let suppose we want to introduce a new course having CID=C4 & CName='Programing Fundamental' in the given table, we write the following query:

Insert Into Student Values (C4, 'Programing Fundamental');

But at the moment not a single student is enrolled in this course & we also don't know about the faculty related to that course. So, there is a redundancy in this table.

Update Anomaly

If we want to update anything in the given table:

Suppose we want to update the faculty name having FID= 'F1' from FName= 'Tanveer' to 'Sadiq' in the given example, we write the following query:

Update student set FName = 'Sadiq' where FID = 'F1';

The query will be executed successfully, But! as you notice the repetition of FID = 'F1', FName will be updated from 'Tanveer' to 'Sadiq' every time where FID = 'F1' and it will create a problem when data is very large i.e it is not time efficient.

The solution of Row Level Redundandancy

The solution of column-level redundancy is that we divide this single table into three separate tables as shown below. By doing so all the anomalies can be resolved.

Some Important Aspects

Candidate key

Keys that are able to determine the whole table it can be a single column or combination of more than one columns. From candidate columns, one is selected as a primary column and all others are known as alternative columns.

Prime Attribute

Attributes that help to determine the candidate keys.

Non-Prime Attribute

All the attributes other than Prime attributes are known as Prime Attributes.

Example:

Composite Key

When two or more columns are combined to uniquely determine the whole table, it forms a composite key.

Example:

Types of Normalization

There are four types of normalizations:

First Normal Form (1NF)

The table should not contain any multivalued attribute

let's take an example to understand this point.

The problem in the table

It is possible that a student may have more than 1 phone no.

But here in this case representation is wrong so we can say that this table is not in First normal form (1NF).

Normalize to First normal form (1NF)

For normalizing the above table to 1NF here are some methods shown below:

In 1st case, we create two different attributes or columns of 'Phone no' for the sake of Database Normalization as shown:
Now we can say that this table is in 1NF.
In 2nd case, we divide a single table into two different tables & relate them by using a foreign key as shown:
In this case, we create a separate table for student's phone nos and form a composite key by joining two columns 'Roll#' & 'Phone no', 'Roll#' as a foreign key gets its references from 'Student' table.
In the 3rd case, we Normalize the above example to 1NF without creating any extra table & columns as shown:
In this case, we just create extra rows and set 'Roll#' & 'Phone no' as a composite key.

Second Normal Form (2NF)

The table must be in First Normal Form.
There should be no partial dependency in the relation.

let's take an example to understand the points.

In this case, 'CustomerID' & 'StoreID' depends on each other i.e both are Prime attributes or we can say that these two attributes are Candidate keys.

The problem in the table

The first condition of 2NF is satisfied that is 'Table should be in 1NF' but the problem arises in the second condition that is described as follow:

The column or attribute name as 'Location' is fully dependent on 'StoreID' & 'StoreID' is fully dependent on 'CustomerID' so 'Location' is partially dependent on 'CustomerID' and this the violation of Second normal form (2NF).

Let's understand this point diagrammatically:

This purely the violation of Second normal form.

Normalize to 2NF

Simply, we divide the single table into two separate tables to normalize the table to 2NF as shown below:

Now both tables are in Second normal form (2NF). In the case of the first table name as 'Store', 'Location' (act as a non-prime attribute) fully dependent on 'StoreID' (act as a prime attribute). So, we can say that it is in 2NF. In the case of the second table name as 'Customer', there is no non-prime attribute, both are prime-attributes and dependent on each other.

Third Normal Form (3NF)

The table should be in 2NF.
Any non-prime attribute should not be determined by non-prime attribute.

Let's take an example to understand the points.

In this case, 'Roll#' is a candidate key & prime attribute and set as a primary key, on the other hand 'State' & 'City' are non-prime attributes. 'State' & 'City' are non-prime attribute because these attributes are not helpful in selecting a candidate key that is 'Roll#' in this example.

The problem in the table

Now come toward the point that is this table in 3NF?

In this case, Candidate Key = C.k = 'Roll#', Prime attribute = 'Roll#', Non-Prime attribute = 'State' & 'City'.

Functional dependencies: (Shown below)

This table is not in 3NF because 'state'(non-prime attribute) determines 'city'(non-prime attribute) & this is against the law of 3NF that is (Any non-prime attribute should not be determined by non-prime attribute). In the above fig. first functional dependency is a valid but second functional dependency is not valid according to Third normal form (3NF).

BCNF(Boyce Codd Normal Form)

The table should be in 3NF.
L.H.S of each Functional Dependency (FD) should be a candidate key or super key.

Let's take an example to understand the points.

Description of table

In this case Candidate key ={Roll#, Reg#}

Functional Dependency(FD): (Shown below)

So we can say that both the conditions of BCNF are fulfiled in the given example, i.e table is in 3NF & L.H.S of each functional dependencies are candidate keys as shown above.

Database Management System VS SQL

2020-07-14T23:02:00.004+05:00

To run any computer application, Database is the most essential. And for this, one must have a clear idea about the difference between DBMS software and SQL tool. In this article, you’ll be better able to understand this important aspect.

First of all, what is actually a Database also known as DB? The answer is, Database is a collection of data which allows the storage & manipulation of data. The reason we prefer a database over storing data in a random text file is actually the better management system. To manage large data, there is a need for a proper system, as per the requirement of the application. Such a system is called Database Management System.

On the other hand, Structured Query Language is a Programming Language basically used to access the DB. Structured Query Language is such a command; you write to manipulate data. It is the most popular programming language for the Database Management Systems to date.

Therefore, in a linear structure, first comes the DB, then DBMS, then SQL tools and finally comes the clients and application. SQL tools are concerned with manipulating data whereas Database Management Systems are used for managing the data in the DB. In simple words, these tools create a link between users and application.

Database Management System Software VS SQL Tools

Types

DBMS software application basically varies on the type of system. It includes 4 types. These are

Hierarchical
Network
Relational
Object-Oriented Relational

All of these are quite well known, depending on the client as well as the application, as to which DBMS type is used. The system software is also determined accordingly. Hierarchical type is good for small applications as it has a typical tree and node concept. Network type works best when there are too many relations of an application. A Relational type is mostly used with its tabular data concept. The OOR type is primarily involved in storing new data.

Structured Query Language mostly uses a Relational Database. The programing language depends on the operation, the tool is about to perform. Some of these operations include manipulation, maintenance & optimization etc. MySQL, Oracle, MS SQL Server and likewise are some of the popular software, while some well-known management tools include Navicat Monitor, Adminer, DBComparer etc.

Learning Curve

The learning curve for both DBMS & SQL is somehow different. Database Management System software applications are almost similar as far as the user interface goes. Therefore, if the DBMS type is the same, the learning curve is not that steep. However, the learning curve of Structured Query Language is flat because once you have learnt it, you can use it on any Database Management System software with a few modifications. However, tools vary a lot depending on the purpose they serve, so one needs to put effort to learn them in full details.

In simple words, the learning curve depends on the type of software or tool. However, SQL tools are easy to learn as they are limited in their functionalities and quite specific in use. But you might be handling multiple tools, and therefore, you have to put it all together. On the contrary, Database Management System software will take much investment in time & effort to master in it.

Functions

DBMS software is an application for creating & managing the database. It offers you the systematic user interface to create, retrieve, and manage data. However, Structured Query Language is basically used to access different DB.

DBMS does not support distributed property while SQL does. Also, normalization is not available in DBMS but present in SQL. However, you can use multiple SQL tools to analyze the data you have in your already created DB.

Even though Structured Query Language is somewhat a smaller part of the entire system, it is not totally engulfed by DBMS. There are certain features that are exclusive in SQL tools and you cannot do them with any other tool available in the management system.

Conclusion

Both DBMS software as well as SQL tool holds much importance in any application. DBMS is of no use without SQL language and vice versa. It is more likely to be said as DBMS is an IDE and Structured Query Language is the language to wire on the IDE. DBMS software is the system, and these tools are what you need to run it. These tools are dependent on the DB and cannot function without the presence of any DB.

Assisted By Ms. Anoosha Khurram

MS SQL SERVER & MYSQL SELECT database with DISTINCT, WHERE & ORDER BY Clause

2020-07-14T01:03:00.004+05:00

The SELECT statement in SQL syntax is used to retrieve data from the tables that exist in the database that we want to retrieve. Displays this result in tabular format.

Syntax:

SELECT column_name FROM table_name;

The semicolon is used at the end of the query just to show that our query is completed, for example, If you write two or more select queries than each query is separated by a semicolon. And FROM is used to connect to the respective table we want to deal with. The syntax of MS SQL SERVER and MYSQL SELECT database is the same.

Demo DataBase:

Here is a demo database tables to help you better understand.

Here are the Structured Query Language queries to create your own tables just like this table. Just copy it and paste it in your SQL server or MySQL workbench and start practising on MS SQL SERVER or MYSQL SELECT database with DISTINCT, WHERE & ORDER BY Clause.

1ST:

CREATE TABLE DPT ( DPTNO INT, DNAME VARCHAR(30), DptLoc VARCHAR(20));

INSERT INTO DPT VALUES (1, 'ACCOUNTING', 'NEW YORK' ),(2, 'RESEARCH', 'DALLAS'),(3, 'SALES', 'CHICAGO' ),(4, 'OPERATIONS', 'BOSTON' ),(5, 'DBM', 'CANADA' );

2ND:

CREATE TABLE SALGRADE (GRADE INT ,LOSAL INT ,HISAL INT );

INSERT INTO SALGRADE VALUES(1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999)

Cases of SELECT Query:

Retrieve all the data from a table:

To retrieve all the data from the above table name as DPT we use the following query:

SELECT * FROM DPT;

the same Syntax is used in both MS SQL Server & MYSQL as shown below:

Practical Example:

MYSQL WORKBENCH retrieve all data from a table

MS SQL SERVER retrieve all data from a table

Retrieve One Column from a table:

Write a statement that displays DNAME from DPT.

SELECT DNAME FROM DPT;

Practical Example:

MYSQL retrieve data

SQL SERVER retrieves a single column

Retrieve multiple Columns from a table:

Write a statement that displays the columns DPTNO and DNAME from DPT.

SELECT DPTNO, DNAME FROM DPT;

Practical Example:

MYSQL WORKBENCH fetch multiple columns

SQL SERVER fetch multiple columns

Retrieve data from multiple tables:

Write a Query that displays the data from both tables mentioned above.

SELECT * FROM DPT, SALGRADE;

[NOTE]

Both the tables are separated by a comma. This simple Query is working in the form that if both tables contain 5 & 5 rows than the result contain 5*5=25 rows this case of the statement is also known as CROSS JOIN that we will learn later.

Practical Example:

MYSQL DATABASE from multiple tables

MS SQL data from multiple tables.

CLAUSES in SQL:

With Distinct Clause

The distinct clause is used to eliminate duplicate rows from the result of a Statement. If Distinct is not used, all rows are returned including duplicates.

Write a query to display all distinct LOC from DPT.

SELECT DISTINCT LOC FROM DPT;

Practical Example:

MYSQL DATABASE distinct statement

SQL SERVER difference b/w Simple & Distinct Statement

With Where Clause

Where clause is used to retrieve data from a table conditionally. It can appear only after From clause.

Example:

Write a query that displays records of departments located in NEW YORK from 1st table.

SELECT * FROM DPT WHERE Loc = 'NEW YORK ' ;

Practical Example:

MYSQL WHERE clause

SQL SERVER WHERE clause

[NOTE]

Structured Query Language uses single quotes around text values. Most database systems also accept double-quotes. Numeric values should not be enclosed in quotes.

With Order BY Clause

The Order BY clause is used to sort the row. The process of arranging data or records in a sequence is called SORTING. A sort can be ascending or descending. Structured query language uses ASC keyword to specify ascending sort and DESC keyword for descending sort, and by default, data is arranged in ascending order. Order BY must be the last clause in the Select statement.

Example:

Display records of departments located in NEW YORK from DPT table in descending order by DPTNO.

SELECT * FROM DPT WHERE Loc = 'NEW YORK ' ORDER BY DPTNO DESC ;

Practical Example

MYSQL WORKBENCH ORDER BY

SQL SERVER ORDER BY

Conclusion:

In this session, we have learned the bases of SQL development that is how to fetch data from the tables given to us and we also learn how to apply any conditions to our query by using WHERE clause, that is the most important keyword of Structured Query Language, in most cases, we have to deal with this keyword and in the end, we learn how to use ORDER BY clause, when we want our output in a specific arranged order we use it.

What is Sql

2020-07-09T18:21:00.015+05:00

Here we learn about important aspects of the structured query language, its importance, its use, and its relation with databases.

SQL is a domain-specific language used in programming and to handle data in a database management system. In simple words, it is used to communicate with databases. Writing and executing the way you render data from the database is called a query. The code you use to write the queries is a structured query language.

What is meant by domain-specific?

Here is a quick definition to help you understand domain-specific languages, but first you need to understand what a general-purpose language or GPL is, these are programming dialects that can be used to tackle a wide variety of problems using one of these languages. e.g C++, C#, Java, Ruby, Python, Rust.

domain-specific languages or DSL for short are created to work on a known problem they are often created within an organization that wants a way to define their solutions using a syntax that more closely matches their work model. DSLs are abstractions that make it easier to express a problem and define a solution. They can be used to better communicate between programmers and non-programmers because the syntax is typically more human-readable. Since DSLs can better guess at what you are trying to do then a GPL they are better able to check for common mistakes and misuses, a couple of examples of DSL are HTML and SQL there is a lot of other dsls out there but many of them are developed privately by companies and are never released publicly.

Why SQL is a domain-specific language?

Structured Query Language, is used to work with data in DB. It is used by every relational database management system, abbreviated DBMS, even if there are some slight degree differences across platforms. And it can not be used for any other purpose other than DB handling like other general-purpose languages.

SQL is easy to learn:

It is not a programming language, it is a query language.
It is almost close to simple English text.

Syntax:

Select Column_Name from Table_Name;

Use of SQL:

It is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard dialect for RDBMS(relational database management systems). SQL statements are used to perform tasks such as delete data, update data, retrieve data, and delete data from a database. These tasks are commonly known as CURD(create, update, retrieve, and delete).In the database world, you are interacting with DB that do not use traditional relational structures, most DBMS try hard to create query languages that look as similar as structured query language because it is so common in the database world. So, no matter what DB platform you want to work with, it will advance your career.

Major RDBMS:

There are many types of relational databases such as...

Oracle
MySQL
Ms SQL Server
PostgreSQL
SQLite... etc

All the relational databases use SQL as there core language, but the syntax of each database is different to some extent. The names mentioned above are RDB (relational databases) and SQL is used in these RDBs to deal with the database. Both MySQL and MS SQL Server are widely used in enterprise database systems. MySQL is an open-source RDBMS and is offered by Oracle, while MySQL Server is a Microsoft product.

History of SQL:

It was first developed in the 1970s by IBM researchers Raymond Boyce and Donald Chamberlain. Now it is known as the Sequel, was created in 1970 following the publication of Edgar Frank Todd's paper, "A Relationship Model of Data for Large Shared Data Banks."

Search ResultsFeatured snippet from the