DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

SQL – DDL – DML – DCL – TCL - Embedded SQL - Static Vs Dynamic

SQL - Views – Constraints – Triggers - Data Base security and
authorization - Query processing and optimization - Functional
Dependencies - Normalization.

SQL

SQL is Structured Query Language, which is a computer language for storing, manipulating and
retrieving data stored in relational database.

SQL is the standard language for Relation Database System. All relational database management systems
like MySQL, MS Access, and Oracle, Sybase, Informix, postgres and SQL Server use SQL as
standard database language.

Why SQL?
 Allows users to access data in relational database management systems.
 Allows users to describe the data.
 Allows users to define the data in database and manipulate that data.
 Allows to embed within other languages using SQL modules, libraries &
pre-compilers.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
 Allows users to set permissions on tables, procedures, and views

History
1970 -- Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He
described a relational model for databases.
1974 -- Structured Query Language appeared.
1978 -- IBM worked to develop Codd's ideas and released a product named System/R.
1986 -- IBM developed the first prototype of relational database and standardized by ANSI.
The first relational database was released by Relational Software and its later becoming Oracle.

SQL Process
When you are executing an SQL command for any RDBMS, the system determines the best way to carry
out your request and SQL engine figures out how to interpret the task.
There are various components included in the process. These components are Query Dispatcher,
Optimization Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles
all non-SQL queries but SQL query engine won't handle logical files.

1
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

SQL Commands

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT,
UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature.

DDL - Data Definition Language

 CREATE - Creates a new table, a view of a table, or other object in database
 ALTER - Modifies an existing database object, such as a table.
 DROP - Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

 SELECT - Retrieves certain records from one or more tables
 INSERT - Creates a record
 UPDATE - Modifies records
 DELETE - Deletes records

DCL - Data Control Language

 GRANT - Gives a privilege to user
 REVOKE - Takes back privileges granted from user

TCL - Transaction Control Language

 BEGIN TRANSACTION - Opens a Transaction.
 COMMIT - Commits a Transaction.
 ROLLBACK - Rollbacks a transaction in case of any error occurs.
 SAVEPOINT - Sets a save point within a transaction.
 SET TRANSACTION - To configure transaction-specific properties like isolation levels.

2
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
DQL - Data Query Language
 ORDER BY Clause - Used to sort the result-set in ascending or descending order.
 GROUP BY Clause - Used with aggregate functions (like COUNT, MAX, MIN, SUM, AVG) to
group the result set by one or more columns.
 JOIN Clause - to combine rows from two or more tables, based on a related column between them.
 WHERE Clause - used to filter records, It is used to extract only those records that fulfill a
specified condition
 SELECT - It is used to retrieve data from the database.

Data Definition Language

What is DDL?
DDL, which stands for Data Definition Language, is a subset of SQL (Structured Query Language)
commands used to define and modify the database structure. These commands are used to create, alter,
and delete database objects like tables, indexes, and schemas. The primary DDL commands in SQL
include:

1. CREATE: This command is used to create a new database object. For example, creating a new
table, a view, or a database.
 Syntax for creating a table: CREATE TABLE table_name (column1 datatype, column2
datatype, ...);
2. ALTER: This command is used to modify an existing database object, such as adding, deleting,
or modifying columns in an existing table.
 Syntax for adding a column in a table: ALTER TABLE table_name ADD column_name
datatype;
 Syntax for modifying a column in a table: ALTER TABLE table_name MODIFY COLUMN
column_name datatype;
3. DROP: This command is used to delete an existing database object like a table, a view, or other
objects.
 Syntax for dropping a table: DROP TABLE table_name;
4. TRUNCATE: This command is used to delete all data from a table, but the structure of the table
remains. It’s a fast way to clear large data from a table.
 Syntax: TRUNCATE TABLE table_name;
5. COMMENT: Used to add comments to the data dictionary.
 Syntax: COMMENT ON TABLE table_name IS 'This is a comment.';
6. RENAME: Used to rename an existing database object.
 Syntax: RENAME TABLE old_table_name TO new_table_name;

DDL commands play a crucial role in defining the database schema.

3
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
Data Manipulation Language (DML)
Data Manipulation Language (DML) is a subset of SQL commands used for adding (inserting), deleting,
and modifying (updating) data in a database. DML commands are crucial for managing the data within
the tables of a database. The primary DML commands in SQL include:

1. INSERT: This command is used to add new rows (records) to a table.

 Syntax: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1,
value2, value3, ...);
2. UPDATE: This command is used to modify the existing records in a table.
 Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;
 The WHERE clause specifies which records should be updated. Without it, all records in the
table will be updated.
3. DELETE: This command is used to remove one or more rows from a table.
 Syntax: DELETE FROM table_name WHERE condition;
 Like with UPDATE, the WHERE clause specifies which rows should be deleted. Omitting
the WHERE clause will result in all rows being deleted.
4. SELECT: Although often categorized separately, the SELECT command is sometimes
considered part of DML as it is used to retrieve data from the database.
 Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
 The SELECT statement is used to query and extract data from a table, which can then be used
for various purposes.

Data Control Language (DCL)

Data Control Language (DCL) is a subset of SQL commands used to control access to data in a database.
DCL is crucial for ensuring security and proper data management, especially in multi-user database
environments. The primary DCL commands in SQL include:

1. GRANT: This command is used to give users access privileges to the database. These privileges
can include the ability to select, insert, update, delete, and so on, over database objects like tables
and views.
 Syntax: GRANT privilege_name ON object_name TO user_name;
 For example, GRANT SELECT ON employees TO user123; gives user123 the permission to
read data from the employees table.

2. REVOKE: This command is used to remove previously granted access privileges from a user.
 Syntax: REVOKE privilege_name ON object_name FROM user_name;
 For example, REVOKE SELECT ON employees FROM user123; would remove user123‘s
permission to read data from the employees table.

DCL commands are typically used by database administrators. When using these commands, it’s
important to carefully manage who has access to what data, especially in environments where data
sensitivity and user roles vary significantly.

4
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

In some systems, DCL functionality also encompasses commands like DENY (specific to certain
database systems like Microsoft SQL Server), which explicitly denies specific permissions to a user, even
if those permissions are granted through another role or user group.

Remember, the application and syntax of DCL commands can vary slightly between different SQL
database systems, so it’s always good to refer to specific documentation for the database you are using.

Transaction Control Language (TCL)

Transaction Control Language (TCL) is a subset of SQL commands used to manage transactions in a
database. Transactions are important for maintaining the integrity and consistency of data. They allow
multiple database operations to be executed as a single unit of work, which either entirely succeeds or
fails.

The primary TCL commands in SQL include:

1. BEGIN TRANSACTION (or sometimes just BEGIN): This command is used to start a new
transaction. It marks the point at which the data referenced in a transaction is logically and
physically consistent.
 Syntax: BEGIN TRANSACTION;
 Note: In many SQL databases, a transaction starts implicitly with any SQL statement that
accesses or modifies data, so explicit use of BEGIN TRANSACTION is not always necessary.
2. COMMIT: This command is used to permanently save all changes made in the current
transaction.
 Syntax: COMMIT;
 When you issue a COMMIT command, the database system will ensure that all changes made
during the current transaction are saved to the database.
3. ROLLBACK: This command is used to undo changes that have been made in the current
transaction.
 Syntax: ROLLBACK;
 If you issue a ROLLBACK command, all changes made in the current transaction are
discarded, and the state of the data reverts to what it was at the beginning of the transaction.
4. SAVEPOINT: This command creates points within a transaction to which you can later roll back.
It allows for partial rollbacks and more complex transaction control.
 Syntax: SAVEPOINT savepoint_name;
 You can roll back to a savepoint using ROLLBACK TO savepoint_name;
5. SET TRANSACTION: This command is used to specify characteristics for the transaction, such
as isolation level.
 Syntax: SET TRANSACTION [characteristic];
 This is more advanced usage and may include settings like isolation level which controls how
transaction integrity is maintained and how/when changes made by one transaction are visible
to other transactions.
TCL commands are crucial for preserving the ACID (Atomicity, Consistency, Isolation, Durability)
properties of a database, ensuring that all transactions are processed reliably. In any database operation
where consistency and integrity of data are important, these commands play a key role.

5
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Data Query Language (DQL) Commands

Data Query Language (DQL) is a subset of SQL commands used primarily to query and retrieve data
from existing database tables. In SQL, DQL is mostly centered around the SELECT statement, which is
used to fetch data according to specified criteria. Here’s an overview of the SELECT statement and its
common clauses:

1. SELECT: The main command used in DQL, SELECT retrieves data from one or more tables.
 Basic Syntax: SELECT column1, column2, ... FROM table_name;
 To select all columns from a table, you use SELECT * FROM table_name;
2. WHERE Clause: Used with SELECT to filter records based on specific conditions.
 Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
 Example: SELECT * FROM employees WHERE department = 'Sales';
3. JOIN Clauses: Used to combine rows from two or more tables based on a related column
between them.
 Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
 Syntax: SELECT columns FROM table1 [JOIN TYPE] JOIN table2 ON table1.column_name
= table2.column_name;
4. GROUP BY Clause: Used with aggregate functions (like COUNT, MAX, MIN, SUM, AVG) to
group the result set by one or more columns.
 Syntax: SELECT column1, aggregate_function(column2) FROM table_name GROUP BY
column1;
5. ORDER BY Clause: Used to sort the result set in ascending or descending order.
 Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC],
column2 [ASC|DESC];
SQL commands encompass a diverse set of categories, each tailored to a specific aspect of database
management. Whether you’re defining database structures (DDL), manipulating data (DML), controlling
access (DCL), managing transactions (TCL), or querying for information (DQL), SQL provides the tools
you need to interact with relational databases effectively. Understanding these categories empowers you
to choose the right SQL command for the task at hand, making you a more proficient database
professional.

Embedded SQL - Static Vs Dynamic SQL

Embedded SQL:
Embedded SQL is a method of combining the computing power of a programming language and the
database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline
with the program source code of the host language. The embedded SQL
statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code
library.

The output from the preprocessor is then compiled by the host compiler. This allows programmers to
embed SQL statements in programs written in any number of languages such as: C/C++, COBOL and
Fortran.

6
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
The SQL standards committee defined the embedded SQL standard in two steps:
a formalism called Module Language was defined, then the embedded SQL standard was derived from
Module Language.

Embedded SQL is a robust and convenient method of combining the computing power of a programming
language with SQL's specialized data management and manipulation capabilities.

Static Vs Dynamic SQL:

Static SQL :
The source form of a static SQL statement is embedded within an application program written in a host
language such as COBOL.

The statement is prepared before the program is executed and the operational form of the statement
persists beyond the execution of the program.

Static SQL statements in a source program must be processed before the program is compiled.
This processing can be accomplished through the DB2 precompiler or the SQL statement coprocessor.

The DB2 precompiler or the coprocessor checks the syntax of the SQL statements, turns them
into host language comments, and generates host language statements to invoke DB2.

The preparation of an SQL application program includes precompilation, the preparation of its
static SQL statements, and compilation of the modified source program.

Dynamic SQL:
Programs that contain embedded dynamic SQL statements must be precompiled like those that contain
static SQL, but unlike static SQL, the dynamic statements are constructed and prepared at run time.
The source form of a dynamic statement is a character string that is passed to DB2 by the program using
the static SQL statement PREPARE or EXECUTE IMMEDIATE.

Views in DBMS
A view in SQL is a virtual table that is based upon the result-set of an SQL statement
 A view will also have rows and columns just like a real table in a database
 Simply a view is nothing but a stored SQL Query
 A view can contain all the rows of a table or specific rows based on some condition
 SQL functions conditions and join statements to a view and present the data just like the data is
produced from a single table.

Creating a view

A view is created by selecting fields from one or more tables present in a database

7
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note:

Whenever a user creates a view, database engine recreates the data using the views SQL statement i.e. view
always shows upto date data

Consider the tables StudentDetails and StudentMarks

Student Details
S_ID NAME ADDRESS
1 Harini Kolkata
2 Preity Hyderabad
3 Divya Chennai
4 Kushi Mumbai
5 Amitha Bangalore

Student Marks
ID NAME MARKS AGE
1 Harini 96 20
2 Manisha 90 19
3 Divya 94 21
4 Kushi 92 19
5 Amitha 95 21
Simple Views in DBMS: Creating a view from a single table

In this example, we will create a view named as DetailsView from a single table StudentDetails

CREATE VIEW DetailsView AS

SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

The data present in a view can be seen just like a normal table select query

SELECT * FROM DetailsView;

8
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Output:

NAME ADDRESS
Harini Kolkata
Preity Hyderabad
Divya Chennai
Kushi Mumbai
Complex view: Creating a view from multiple tables

 In this example will create a view named MarksView by taking data from both the table’s student
details and student marks
 To create a View from multiple tables just simply include multiple tables in the SELECT statement.

CREATE VIEW MarksView AS

SELECT [Link], [Link], [Link]
FROM StudentDetails, StudentMarks
WHERE [Link] = [Link];

To display data of View Marks:

SELECT * FROM MarksView;

Output:
NAME MARKS ADDRESS
Harini 96 Kolkata
Divya 94 Chennai
Kushi 92 Mumbai
Amitha 95 Bangalore

Deleting views in DBMS

 You can simply delete a view by using the Drop statement

 That view is not used anymore

Syntax:

DROP VIEW view_name;

Example
DROP VIEW MarksView;

9
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
Updating views

Views are updated only if certain conditions are met otherwise if any one of the conditions are not met
views will not be updated

Criteria for View Updating

 The select statement used in the create view statement should not include group by clause or order by
clause
 The select statement must not contain distinct keyword
 A view should not be created from nested or Complex queries
 A view should be created from a single table but if the view is created from more than one table then
it is not allowed for updating

CREATE OR REPLACE VIEW

Create or replace view statement is used to add or remove fields from existing views

Syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT column1,coulmn2,..
FROM table_name
WHERE condition;

Update the view MarksView and add the field AGE to this View from StudentMarks Table,

CREATE OR REPLACE VIEW MarksView AS

SELECT [Link], [Link], [Link], StudentMarks.
AGE
FROM StudentDetails, StudentMarks
WHERE [Link] = [Link];

Fetch all the data from MarksView now as:

SELECT * FROM MarksView;

Output
NAME ADDRESS MARKS AGE
HARINI Kolkata 96 20
Divya Chennai 94 21
Kushi Mumbai 92 19
Amitha Bangalore 95 21

10
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Inserting a row into a view

We can use insert into statement of SQL to insert a row in a view just like inserting a row in an ordinary
table

Syntax:

INSERT view_name(column1, column2 , column3,..)

VALUES(value1, value2, value3..);

Example
INSERT INTO DetailsView(NAME, ADDRESS)
VALUES("Preity","Hyderabad");

Fetch all the data from DetailsView now as,

SELECT * FROM DetailsView;

Output

NAME ADDRESS
Harini Kolkotta
Divya Chennai
Kushi Mumbai
Amitha Bangalore
Preity Hyderabad

Deleting a row from a view

 A row in a view can be deleted just like simply deleting rows from a Table using delete statement
 But remember a row in a view can be deleted only if the row is actually deleted in the original table
from which it is created

Syntax:

DELETE FROM view_name

WHERE condition;

Example
DELETE FROM DetailsView
WHERE NAME="Preity";
11
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Fetch all the data from DetailsView now as,

SELECT * FROM DetailsView;

Output:
NAME ADDRESS
Harini Kolkotta
Divya Chennai
Kushi Mumbai
Amitha Bangalore
Preity Hyderabad

Advantages and Disadvantages of Views

Advantages

 Enforce Business Rules: By placing complicated or misunderstood business logic into the view, you
can be sure to present a unified portrayal of the data which increases use and quality.
 Consistency: Once defined their calculations are referenced from the view rather than being
restated in separate queries. This makes for less mistakes and easier maintenance of code.
 Security: For example, you can restrict access to the employee table, that contains social security
numbers, but allow access to a view containing name and phone number.
 Simplicity: Databases with many tables possess complex relationships, which can be difficult to
navigate if you aren’t comfortable using Joins.
 Space: Views take up very little space, as the data is stored once in the source table.

Limitations

 Modifications: Not all views support INSERT, UPDATE, or DELETE operations. Complex multi-
table views are generally read-only.
 Performance: Hugely complex job for the database engine. That is because each time a view is
referenced, the query used to define it, is rerun.

Constraints in DBMS
In DBMS (Database Management Systems), constraints are guidelines or limitations imposed on
database tables to maintain the integrity, correctness, and consistency of the data. Constraints can be used
to enforce data linkages across tables, verify that data is unique, and stop the insertion of erroneous data.
A database needs constraints to be reliable and of high quality.

What are the Constraints of DBMS?

In DBMS, constraints refer to limitations placed on data or data processes. This indicates that only a
particular type of data may be entered into the database or that only a particular sort of operation can be
performed on the data inside.
12
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Constraints thereby guarantee data accuracy in a database management system (DBMS).

The following can be guaranteed via constraints
Data Accuracy − Data accuracy is guaranteed by constraints, which make sure that only true data is
entered into a database. For example, a limitation may stop a user from entering a negative value into a
field that only accepts positive numbers.
Data Consistency − The consistency of data in a database can be upheld by using constraints. These
constraints are able to ensure that the primary key value in one table is followed by the foreign key value
in another table.
Data integrity − The accuracy and completeness of the data in a database are ensured by constraints. For
example, a constraint can stop a user from putting a null value into a field that requires one.

Types of Constraints in DBMS

1. Domain Constraints
2. Key Constraints
3. Entity Integrity Constraints
4. Referential Integrity Constraints
5. Tuple Uniqueness Constraints

1. Domain Constraints

In a database table, domain constraints are guidelines that specify the acceptable values for a certain
property or field. These restrictions guarantee data consistency and aid in preventing the entry of
inaccurate or inconsistent data into the database.
The following are some instances of domain restrictions in a DBMS –
Data type constraints − These limitations define the kinds of data that can be kept in a column. A
column created as VARCHAR can take string values, but a column specified as INTEGER can only
accept integer values.
Length Constraints − These limitations define the largest amount of data that may be put in a column.
For instance, a column with the definition VARCHAR(10) may only take strings that are up to 10
characters long.
Range constraints − The allowed range of values for a column is specified by range restrictions. A
column designated as DECIMAL(5,2), for example, may only take decimal values up to 5 digits long,
including 2 decimal places.
Nullability constraints − Constraints on a column's capacity to accept NULL values are known as
nullability constraints. For instance, a column that has the NOT NULL definition cannot take NULL
values.
Unique constraints − Constraints that require the presence of unique values in a column or group of
columns are known as unique constraints. For instance, duplicate values are not allowed in a column with
the UNIQUE definition.
Check constraints − Constraints for checking data: These constraints outline a requirement that must
hold for any data placed into the column. For instance, a column with the definition CHECK (age > 0)
can only accept ages that are greater than zero.

13
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
Default constraints − Constraints by default: Default constraints automatically assign a value to a
column in case no value is provided. For example, a column with a DEFAULT value of 0 will have 0 as
its value if no other value is specified.

2. Key Constraints

Key constraints are regulations that a DBMS uses to ensure data accuracy and consistency in a database.
They define how the values in a table's one or more columns are related to the values in other tables,
making sure that the data remains correct.
In DBMS, there are several key constraint kinds, including −
Primary Key Constraint − A primary key constraint is an individual identifier for each record in a
database. It guarantees that each database entry contains a single, distinct value—or a pair of values—
that cannot be null—as its method of identification.
Foreign Key Constraint − Reference to the primary key in another table is a foreign key constraint. It
ensures that the values of a column or set of columns in one table correspond to the primary key
column(s) in another table.
Unique Constraint − In a database, a unique constraint ensures that no two values inside a column or
collection of columns are the same.

3. Entity Integrity Constraints

A database management system uses entity integrity constraints (EICs) to enforce rules that guarantee a
table's primary key is unique and not null. The consistency and integrity of the data in a database are
maintained by EICs, which are created to stop the formation of duplicate or incomplete entries.
Each item in a table in a relational database is uniquely identified by one or more fields known as the
primary key. EICs make a guarantee that every row's primary key value is distinct and not null. Take the
"Employees" table, for instance, which has the columns "EmployeeID" and "Name." The table's primary
key is the EmployeeID column. An EIC on this table would make sure that each row's unique
EmployeeID value is there and that it is not null.
If you try to insert an entry with a duplicate or null EmployeeID, the database management system will
reject the insertion and produce an error. This guarantees that the information in the table is correct and
consistent.
EICs are a crucial component of database architecture and assist guarantee the accuracy and
dependability of the data contained in a database.

4. Referential Integrity Constraints

A database management system will apply referential integrity constraints (RICs) in order to preserve the
consistency and integrity of connections between tables. By preventing links between entries that don't
exist from being created or by removing records that have related records in other tables, RICs guarantee
that the data in a database is always consistent.
By the use of foreign keys, linkages between tables are created in relational databases. A column or
collection of columns in one table that is used as a foreign key to access the primary key of another table.
RICs make sure there are no referential errors and that these relationships are legitimate.
Consider the "Orders" and "Customers" tables as an illustration. The primary key column in the
"Customers" database corresponds to the foreign key field "CustomerID" in the "Orders" dataset. A RIC

14
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
on this connection requires that each value in the "CustomerID" column of the "Orders" database exist in
the "Customers" table's primary key column.
If an attempt was made to insert a record into the "Orders" table with a non-existent "CustomerID" value,
the database management system would reject the insertion and notify the user of an error.
Similar to this, the database management system would either prohibit the deletion or cascade the
deletion in order to ensure referential integrity if a record in the "Customers" table was removed and
linked entries in the "Orders" table.

In general, RICs are a crucial component of database architecture and assist guarantee that the
information contained in a database is correct and consistent throughout time.

5. Tuple Uniqeness Contraints

A database management system uses constraints called tuple uniqueness constraints (TUCs) to make sure
that every entry or tuple in a table is distinct. TUCs impose uniqueness on the whole row or tuple, in
contrast to Entity Integrity Constraints (EICs), which only enforce uniqueness on certain columns or
groups of columns.
TUCs, then, make sure that no two rows in a table have the same values for every column. Even if the
individual column values are not unique, this can be helpful in cases when it is vital to avoid the
production of duplicate entries.
Consider the "Sales" table, for instance, which has the columns "TransactionID," "Date," "CustomerID,"
and "Amount." Even if individual column values could be duplicated, a TUC on this table would make
sure that no two rows have the same values in all four columns.
The database management system would reject the insertion and generate an error if an attempt was made
to enter a row with identical values in each of the four columns as an existing entry. This guarantees the
uniqueness and accuracy of the data in the table.
TUCs may be a helpful tool for ensuring data correctness and consistency overall, especially when it's
vital to avoid the generation of duplicate entries.

Conclusion
Constraints are a crucial part of every database management system, and creating and maintaining high-
quality databases requires a grasp of how to apply them effectively. To guarantee data's correctness,
consistency, and integrity, constraints in DBMS apply rules to the data. They stop data from being added,
altered, or removed that is incorrect or incomplete. EICs, RICs, TUCs, and Check Constraints are a few
different kinds of constraints. Building and maintaining high-quality databases that allow for informed
business decisions requires constraints.

Triggers:

A Trigger in Structured Query Language is a set of procedural statements which are executed
automatically when there is any response to certain events on the particular table in the database. Triggers
are used to protect the data integrity in the database.

In SQL, this concept is the same as the trigger in real life. For example, when we pull the gun trigger, the
bullet is fired.

15
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
To understand the concept of trigger in SQL, let's take the below hypothetical situation:

Suppose RAVI is the human resource manager in a multinational company. When the record of a new
employee is entered into the database, he has to send the 'Congrats' message to each new employee. If
there are four or five employees, RAVI can do it manually, but if the number of new Employees is more
than the thousand, then in such condition, he has to use the trigger in the database.

Thus, now RAVI has to create the trigger in the table, which will automatically send a 'Congrats' message
to the new employees once their record is inserted into the database.

The trigger is always executed with the specific table in the database. If we remove the table, all the
triggers associated with that table are also deleted automatically.

In Structured Query Language, triggers are called only either before or after the below events:

1. INSERT Event: This event is called when the new row is entered in the table.
2. UPDATE Event: This event is called when the existing record is changed or modified in the table.
3. DELETE Event: This event is called when the existing record is removed from the table.

Types of Triggers in SQL

Following are the six types of triggers in SQL:

1. AFTER INSERT Trigger

This trigger is invoked after the insertion of data in the table.
2. AFTER UPDATE Trigger
This trigger is invoked in SQL after the modification of the data in the table.
3. AFTER DELETE Trigger
This trigger is invoked after deleting the data from the table.
4. BEFORE INSERT Trigger
This trigger is invoked before the inserting the record in the table.
5. BEFORE UPDATE Trigger
This trigger is invoked before the updating the record in the table.
6. BEFORE DELETE Trigger
This trigger is invoked before deleting the record from the table.

Syntax of Trigger in SQL

CREATE TRIGGER Trigger_Name
[ BEFORE | AFTER ] [ Insert | Update | Delete]
ON [Table_Name]
[ FOR EACH ROW | FOR EACH COLUMN ]
AS
Set of SQL Statement

In the trigger syntax, firstly, we have to define the name of the trigger after the CREATE TRIGGER
keyword. After that, we have to define the BEFORE or AFTER keyword with anyone event.

Then, we define the name of that table on which trigger is to occur.

16
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
After the table name, we have to define the row-level or statement-level trigger.

And, at last, we have to write the SQL statements which perform actions on the occurring of event.

Example of Trigger in SQL

To understand the concept of trigger in SQL, first, we have to create the table on which trigger is to be
executed.

The following query creates the Student_Trigger table in the SQL database:

CREATE TABLE Student_Trigger

(
Student_RollNo INT NOT NULL PRIMARY KEY,
Student_FirstName Varchar (100),
Student_EnglishMarks INT,
Student_PhysicsMarks INT,
Student_ChemistryMarks INT,
Student_MathsMarks INT,
Student_TotalMarks INT,
Student_Percentage );
The following query shows the structure of theStudent_Trigger table:

DESC Student_Trigger;

Output:

Field Type NULL Key Default Extra

Student_RollNo INT NO PRI NULL

Student_FirstName Varchar(100) YES NULL

Student_EnglishMarks INT YES NULL

Student_PhysicsMarks INT YES NULL

Student_ChemistryMarks INT YES NULL

Student_MathsMarks INT YES NULL

Student_TotalMarks INT YES NULL

Student_Percentage INT YES NULL

17
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

The following query fires a trigger before the insertion of the student record in the table:

CREATE TRIGGER Student_Table_Marks

BEFORE INSERT ON
Student_Trigger FOR EACH ROW
SET new.Student_TotalMarks = new.Student_EnglishMarks + new.Student_PhysicsMarks +
new.Student_ChemistryMarks + new.Student_MathsMarks,
new.Student_Percentage = ( new.Student_TotalMarks / 400) * 100;

The following query inserts the record into Student_Trigger table:

INSERT INTO Student_Trigger (Student_RollNo, Student_FirstName, Student_EnglishMarks,

Student_PhysicsMarks, Student_ChemistryMarks, Student_MathsMarks, Student_TotalMarks,
Student_Percentage) VALUES ( 201, Sorya, 88, 75, 69, 92, 0, 0);

To check the output of the above INSERT statement, you have to type the following SELECT statement:
SELECT * FROM Student_Trigger;

Output:

Student Student Student Student Student Studen Studen Student

_RollNo _FirstNa _English _Physics _chemist t_Math t_Total _Percen
me Marks Marks ryMarks sMark Marks tage
s

201 Surya 88 75 69 92 324 81

Advantages of Triggers in SQL

Following are the three main advantages of triggers in Structured Query Language:

1. SQL provides an alternate way for maintaining the data and referential integrity in the tables.
2. Triggers helps in executing the scheduled tasks because they are called automatically.
3. They catch the errors in the database layer of various businesses.
4. They allow the database users to validate values before inserting and updating.

Disadvantages of Triggers in SQL

Following are the main disadvantages of triggers in Structured Query Language:

1. They are not compiled.

2. It is not possible to find and debug the errors in triggers.
3. If we use the complex code in the trigger, it makes the application run slower.
4. Trigger increases the high load on the database system.

18
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Data Base Security and Authorization

DATA BASE SECURITY

 DATABASE is a collection of information stored in a computer.
 SECURITY it is being free from danger.
 DATABASE SECURITY it is the mechanisms that protect the database against intentional or
accidental threats.

Three main Aspects

Secrecy:
 it is protecting the database from unauthorized users
 ensure that users are allowed to do the things they are trying to do
Example: polices

Integrity:
 Protecting the database from authorized users
 Ensure that what users are trying to do is correct
 Example: An Employee should be able to modify his or her own information.

Availability:
 Authorized users should be able to access data for legal purpose as necessary
Example: usage of lab.

Database security problem

 What is a threat
 It can be defined a hostile agent that ,either casually or by using
 Specialized technique
 Modify
 Delete the information managed by a DBMS

Two kinds of threat;

Non fraudulent threat

 Natural or accidental disaster
 Errors or bugs in H/W or S/W
 Human errors

Fraudulent threat
 Authorized users those who about their privileges
 Hostile agents those improper users(outsider or insiders) who attack the S/w and or H/W system
or read or write data in a database.

19
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
Database protection requirements;
 Protection from improper access
 Protection from inference
 Integrity of the database
 User authentication
 Multilevel protection
 Confinement
 Management and protection of sensitive data.

Type of security controls

 Flow control
 Inference control
 Access control

Authentication:
Authentication is the process of identifying someone's identity by assuring that the person is the
same as what he is claiming for.
It is used by both server and client. The server uses authentication when someone wants to
access the information, and the server needs to know who is accessing the information. The
client uses it when he wants to know that it is the same server that it claims to be.
The authentication by the server is done mostly by using the username and password. Other
ways of authentication by the server can also be done using cards, retina scans, voice
recognition, and fingerprints.
Authentication does not ensure what tasks under a process one person can do, what files he can
view, read, or update. It mostly identifies who the person or system is actually.

Authentication Factors:
As per the security levels and the type of application, there are different types of Authentication
factors:
 Single-Factor Authentication
Single-factor authentication is the simplest way of authentication. It just needs a username and
password to allows a user to access a system.
 Two-factor Authentication
As per the name, it is two-level security; hence it needs two-step verification to authenticate a
user. It does not require only a username and password but also needs the unique information that
only the particular user knows, such as first school name, a favorite destination. Apart from
this, it can also verify the user by sending the OTP or a unique link on the user's registered
number or email address.
 Multi-factor Authentication
This is the most secure and advanced level of authorization. It requires two or more than two
levels of security from different and independent categories. This type of authentication is usually
used in financial organizations, banks, and law enforcement agencies. This ensures to eliminate
any data exposer from the third party or hackers.

20
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Famous Authentication techniques

1. Password-based authentication
It is the simplest way of authentication. It requires the password for the particular username. If
the password matches with the username and both details match the system's database, the user
will be successfully [Link] between JDK, JRE, and J
2. Passwordless authentication
In this technique, the user doesn't need any password; instead, he gets an OTP (One-time
password) or link on his registered mobile number or phone number. It can also be said OTP-
based authentication.
3. 2FA/MFA
2FA/MFA or 2-factor authentication/Multi-factor authentication is the higher level of
authentication. It requires additional PIN or security questions so that it can authenticate the
user.
4. Single Sign-on
Single Sign-on or SSO is a way to enable access to multiple applications with a single set of
credentials. It allows the user to sign-in once, and it will automatically be signed in to all other
web apps from the same centralized directory.
5. Social Authentication
Social authentication does not require additional security; instead, it verifies the user with the
existing credentials for the available social network.

Authorization:
Authorization is the process of granting someone to do something. It means it a way to check if
the user has permission to use a resource or not.
It defines that what data and information one user can access. It is also said as AuthZ.
The authorization usually works with authentication so that the system could know who is
accessing the information.
Authorization is not always necessary to access information available over the internet. Some
data available over the internet can be accessed without any authorization, such as you can read
about any technology from here.

Authorization Techniques:
 Role-based access control
RBAC or Role-based access control technique is given to users as per their role or profile in the
organization. It can be implemented for system-system or user-to-system.
 JSON web token
JSON web token or JWT is an open standard used to securely transmit the data between the
parties in the form of the JSON object. The users are verified and authorized using the
private/public key pair.

SAML
SAML stands for Security Assertion Markup Language. It is an open standard that provides
authorization credentials to service providers. These credentials are exchanged through
digitally signed XML documents.

21
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
OpenID authorization
It helps the clients to verify the identity of end-users on the basis of authentication.

OAuth
OAuth is an authorization protocol, which enables the API to authenticate and access the
requested resources.

Difference chart between Authentication and Authorization

Authentication Authorization
Authentication is the process of Authorization is the process of
identifying a user to provide access giving permission to access the
to a system. resources.
In this, the user or client and server In this, it is verified that if the user is
are verified. allowed through the defined policies
and rules.
It is usually performed before the It is usually done once the user is
authorization. successfully authenticated.
It requires the login details of the It requires the user's privilege or
user, such as user name & password, security level.
etc.
Data is provided through the Token Data is provided through the access
Ids. tokens.
Example: Entering Login details is Example: After employees
necessary for the employees to successfully authenticate themselves,
authenticate themselves to access the they can access and work on certain
organizational emails or software. functions only as per their roles and
profiles.
Authentication credentials can be Authorization permissions cannot be
partially changed by the user as per changed by the user. The
the requirement. permissions are given to a user by
the owner/manager of the system,
and he can only change it.

22
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
QUERY PROCESSING IN DBMS

Query Processing is the activity performed in extracting data from the database. In query
processing, it takes various steps for fetching the data from the database.

The steps involved are:

1. Parsing and translation
2. Optimization
3. Evaluation

The query processing works in the following way:

Parsing and Translation
 strategies differing in performance, and the process of choosing a reasonably efficient one is
known as query optimization.
 The code generator generates the code to execute the plan. The runtime database
processor runs the generated code to produce the query result.
 Relational algebra is well suited for the internal representation of a query.

The translation process in query processing is similar to the parser of a query. When a user
executes any query, for generating the internal form of the query, the parser in the system checks the
syntax of the query, verifies the name of the relation in the database, the tuple, and finally the required
attribute value. The parser creates a tree of the query, known as 'parse-tree.' Further, translate it into the
form of relational algebra. With this, it evenly replaces all the use ofthe views when used in the query.

23
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
It is done in the following steps:

Step-1:

Parser: During parse call, the database performs the following checks- Syntax check, Semantic check
and Shared pool check, after converting the query into relational algebra.
Parser performs the following checks as (refer detailed diagram):
1. Syntax check – concludes SQL syntactic validity. Example:
SELECT * FORM employee
Here error of wrong spelling of FROM is given by this check.
2. Semantic check – determines whether the statement is meaningful or not. Example: query
contains a table name which does not exist is checked by this check.
3. Shared Pool check – Every query possess a hash code during its execution. So, this check
determines existence of written hash code in shared pool if code exists in shared pool then
database will not take additional steps for optimization and execution.

Hard Parse and Soft Parse –

If there is a fresh query and its hash code does not exist in shared pool then that query has to
pass through from the additional steps known as hard parsing otherwise if hashcode exists then query
does not passes through additional steps. It just passes directly to execution engine (refer detailed
diagram). This is known as soft parsing.
Hard Parse includes following steps – Optimizer and Row source generation.
Step-2:
Optimizer: During optimization stage, database must perform a hard parse atleast for one unique DML
statement and perform optimization during this parse. This database never optimizes DDL unless it
includes a DML component such as subquery that require optimization.

It is a process in which multiple query execution plan for satisfying a query are examined and
Most efficient query plan is satisfied for execution.
Database catalog stores the execution plans and then optimizer passes the lowest cost plan for
execution.

Step-3:
Execution Engine: Finally runs the query and display the required result.
Thus, we can understand the working of a query processing in the below-described diagram:

Suppose a user executes a query. As we have learned that there are various methods of extracting the
data from the database. In SQL, a user wants to fetch the records of the employees whose salary is greater
than or equal to 10000. For doing this, the following query is undertaken:

SELECT EMP_NAME FROM EMPLOYEE WHERE

SALARY>10000;
Thus, to make the system understand the user query, it needs to be translated in the form of relational
algebra. We can bring this query in the relational algebra form as:

24
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
o σsalary>10000 (πEmp_Name(Employee))
o πEmp_Name(σsalary>10000 (Employee))

After translating the given query, we can execute each relational algebra operation by using different
algorithms. So, in this way, a query processing begins its working.

Evaluation
For this, with addition to the relational algebra translation, it is required to annotate the translated
relational algebra expression with the instructions used for specifying and evaluating each operation.
Thus, after translating the user query, the system executes a query evaluation plan.

Query Evaluation Plan

o In order to fully evaluate a query, the system needs to construct a query evaluation plan.
o A query evaluation plan defines a sequence of primitive operations used for evaluating a query.
The query evaluation plan is also referred to as the query execution plan.
o A query execution engine is responsible for generating the output of the given query. It takes
the query execution plan, executes it, and finally makes the output for the user query.

Optimization

o The cost of the query evaluation can vary for different types of queries. Although the system is
responsible for constructing the evaluation plan, the user does need not to write their query
efficiently.
o Usually, a database system generates an efficient query evaluation plan, which minimizes its
cost. This type of task performed by the database system and is known as Query Optimization.
o For optimizing a query, the query optimizer should have an estimated cost analysis of each
operation. It is because the overall operation cost depends on the memory allocations to several
operations, execution costs, and so on.
Finally, after selecting an evaluation plan, the system evaluates the query and produces the output of
the query.

Example:
SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > (SELECT MAX
(SALARY) FROM EMPLOYEE WHERE DNO=5);
The inner block
(SELECT MAX (SALARY) FROM EMPLOYEE WHERE DNO=5)
 Translated in: ∏ MAX SALARY
(σDNO=5(EMPLOYEE)) The Outer block
SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > C
 Translated in: ∏ LNAZME, FNAME (σSALARY>C
(EMPLOYEE)) (C represents the result returned from the inner block.)
 The query optimizer would then choose an execution plan for each block.
 The inner block needs to be evaluated only once. (Uncorrelated nested query).
 It is much harder to optimize the more complex correlated nested queries.
25
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
External Sorting
It refers to sorting algorithms that are suitable for large files of records on disk that do not fit entirely in
main memory, such as most database files..

ORDER BY.
Sort-merge algorithms for JOIN and other operations (UNION, INTERSECTION). Duplicate
elimination algorithms for the PROJECT operation (DISTINCT).

Typical external sorting algorithm uses a sort-merge strategy:

Sort phase: Create sort small sub-files (sorted sub-files are called runs).
Merge phase: Then merges the sorted runs. N-way merge uses N memory buffers to buffer input runs,
and 1 block to buffer output. Select the 1st record (in the sort order) among input buffers, write it to the
output buffer and delete it from the input buffer. If output buffer full, write it to disk. If input buffer
empty, read next block from the corresponding run. E.g. 2-waySort-Merge

Functional Dependency
The functional dependency is a relationship that exists between two attributes. It typically exists between
the primary key and non-key attribute within a table.

X → Y
The left side of FD is known as a determinant, the right side of the production is known as a dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.

26
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION
Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we
know the Emp_Id, we can tell that employee name associated with it.

Functional dependency can be written as:

Emp_Id → Emp_Name
We can say that Emp_Name is functionally dependent on Emp_Id.

A Functional Dependency in DBMS is a fundamental concept that describes the relationship between
attributes (columns) in a table. It shows how the values in one or more attributes determine the value in
another. In layperson's terms, it describes how data in one column or set of columns can relate to data in
another column. It helps to maintain the quality of the data in DBMS.
Functional Dependency is represented in the form of an equation. Here, you have a set of attributes (A, B,
C, etc.) and an arrow (->) denoting the Dependency. For example, if we have a table of employee data
with columns "EmployeeID," "FirstName," and "LastName," we can express a functional dependency
like this:

EmployeeID -> FirstName, LastName.

Another important term you should know is Partial dependency in DBMS, which is a Database
Management system (DBMS) concept that describes a specific type of dependency between attributes
(columns) within a relational database table.

How to Denote a Functional Dependency in DBMS?

In DBMS, you denote functional dependencies using a notation. It contains two main components: the
left-hand side (LHS) and the right-hand side (RHS) of an arrow (->).
For example, if we have a table with attributes "A," "B," and "C," and attribute "A" determines the values
of attributes "B" and "C," you would denote it as
A -> B, C
This notation indicates that the value(s) in attribute "A" determines the value(s) in attributes "B" and "C."
In other words, if you know the value of "A," you can determine the values of "B" and "C."

Types of Functional Dependencies in DBMS

Here are some of the important types of Functional Dependency In DBMS
1. Trivial Functional Dependency
A trivial functional dependency in DBMS occurs when an attribute or set of attributes (columns) on the
left-hand side (LHS) of a functional dependency arrow (->) already determines the attributes on the right-
hand side (RHS) without any extra information.
Suppose we have a table of students with attributes "StudentID" and "StudentName." In this case, if we
state the functional dependency as
StudentID -> StudentName,

27
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

This is a trivial functional dependency. Because within a single "StudentID," there can be only one
corresponding "StudentName." In other words, the value of "StudentID" determines the value of
"StudentName" without any more information or conditions.

2. Non-trivial Functional Dependency

A non-trivial functional dependency is a specific type of dependency between attributes (columns) in a

table. Here, the relationship is not obvious or trivial. It conveys meaningful information about how the
values in one set of attributes determine the values in another.

To illustrate this concept, let's consider an example with a table of student data:

Student Id Student Name Student DOB Class

101 Alice 1995-05-15 10A

102 Bob 2000-03-20 10B

103 Carol 1999-07-10 10A

104 Dave 2000-01-05 10B

We want to express a functional dependency based on the student's birthdate (StudentDOB) and class
(Class). A non-trivial functional dependency in this case would be
StudentDOB, Class -> StudentName.

This functional dependency means that given a combination of a student's date of birth and class, you can
uniquely determine their name. It's non-trivial because it provides valuable information about the
relationship between attributes in the table.

3. Multivalued Functional Dependency

A multivalued functional dependency in a database occurs when one or more attributes determine
multiple unrelated sets of values in another attribute. It shows that changes in the determining attributes
can lead to various combinations of values in the dependent attribute, indicating complex relationships
within the data.
28
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Here's a simple example:

Student_ID Student_Name Courses_Enrolled

1 Alice {Math, English}

2 Bob {Science, History}

3 Carol {Math, Science}

In this case, the multivalued dependency in DBMS holds:

 Alice is Student 1 enrolled in {Math, English}.

 Bob is Student 2 enrolled in {Science, History}.
 Carol is Student 3 enrolled in {Math, Science}.

4. Transitive Functional Dependency

A transitive functional dependency in DBMS is a relational database table's relationship between
attributes (columns). It occurs when one attribute's value determines another's value through an
intermediary (a third) attribute.

Example:
Consider a database table called "Student_Info" with the following attributes:
Student_ID (unique identifier for each student),
Student_Name,
Student_Address
Student_City.

In this example, we can assume that Student_Address depends on Student_City, and Student_City
depends on Student_ID. This creates a transitive dependency in DBMS, where Student_ID indirectly
determines Student_Address.

29
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Normalization

Need of Normalization (Consequences of Bad Design-Insert, Update &Delete Anomalies)

 Normalization
 First Normal Form
 Second Normal Form
 Third Normal Form
 BCNF

Database normalization is a database schema design technique, by which anexisting schema is

modified to minimize redundancy and dependency of data.

Normalization split a large table into smaller tables and define relationshipsbetween them
to increases the clarity in organizing data.

Some facts about database normalization

 The words normalization and normal form refer to the structure of adatabase.
 Normalization was developed by IBM researcher E.F. Codd In the 1970s.
 Normalization increases the clarity in organizing data in Database.

Normalization of a Database is achieved by following a set of rulescalled ‘forms’ in

creating the database.

Normalization in DBMS

Normalization is a process of organizing the data in database to avoid data redundancy, insertion
anomaly, update anomaly and deletion anomaly. Normalization is a database design technique
which organizes tables in a manner that reduces redundancy and dependency of data. It divides
larger tables to smaller tables and links them using relationships.

Normalization is also the process of simplifying the design of a databaseso that it achieves the
optimal structure.

30
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Anomalies in DBMS
There are three types of anomalies that occur when the database is not normalized.
1. Insertion Anomaly
2. Update Anomaly
3. Deletion Anomaly
Let us assume we have Employee table as given below.

Update anomaly: Update anomaly is something when we are trying to update some records in
table, and that update is causing data inconsistency.
For example, in the above table we have two records for EmpId 100 as he belongs to two
departments of the company. If we want to update the address of Rock then we have to update
the same in two rows or the data will become inconsistent. If somehow, the correct address gets
updated in one department but not in other then as per the database, Rock would be having two
different addresses, which is not correct and would lead to inconsistent data.

Insert anomaly: Insert anomaly is something when we are not able to insert data into tables due
to some constraints. Suppose a new employee joins the company, who is under training and
currently not assigned to any department then we would not be able to insert the data into the
table if Emp_Dept field doesn’t allow nulls.

Delete anomaly: Delete anomaly is something when we delete somedata from the table, and due
to that delete operation we loss some other useful data.

For example, if at a point of time the company closes the department 103 then deleting the rows
that are having Emp_Dept as 103 would also delete the information of employee Peter since she
is assigned only to this department.
31
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Normalization is a method to remove all these anomalies and bring the database to a consistent
state.

We have below normal forms which are used to eliminate or reduce redundancy in database
tables.
1. First normal form(1NF)
2. Second normal form(2NF)
3. Third normal form(3NF)
4. Boyce-Codd normal form (BCNF)
ADVANTAGES OF NORMALIZATION
Here we can see why normalization is an attractive prospect in RDBMS concepts.
1) A smaller database can be maintained as normalization eliminates the duplicate data. Overall
size of the database is reduced as a result.
2) Better performance is ensured which can be linked to the above point. As databases become
lesser in size, the passes through the data becomes faster and shorter thereby improving response
time and speed.
3) Narrower tables are possible as normalized tables will be fine-tuned and will have lesser
columns which allows for more data records per page.
4) Fewer indexes per table ensures faster maintenance tasks (index rebuilds).
5) Also realizes the option of joining only the tables that are needed.

DISADVANTAGES OF NORMALIZATION
1) More tables to join as by spreading out data into more tables, the need to join table’s
increases and the task becomes more tedious. The database becomes harder to realize as well.
2) Tables will contain codes rather than real data as the repeated data will be stored as lines of
codes rather than the true data. Therefore, there is always a need to go to the lookup table.
3) Data model becomes extremely difficult to query against as the data model is optimized for
applications, not for ad hoc querying. (Ad hoc query is a query that cannot be determined before
the issuance of the query. It consists of an SQL that is constructed dynamically and is usually
constructed by desktop friendly query tools.). Hence it is hard to model the database without
knowingwhat the customer desires.
4) As the normal form type progresses, the performance becomes slower and slower.
5) Proper knowledge is required on the various normal forms to execute the normalization
process efficiently. Careless use may lead to terrible design filled with major anomalies and data
inconsistency.

32
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Database normalization rules

Database normalization process is divided into following the normal form:

First Normal Form (1NF)

1NF (First Normal Form) Rules

 Each table cell should contain a single value.
 Each record needs to be unique.
Example:
Sample Employee table, it displays employees are working with multiple
departments.

Employee Age Department

Melvin 32 Marketing, Sales

Edward 45 Quality Assurance

Alex 36 Human Resource

Employee Age Department

Melvin 32 Marketing

Melvin 32 Sales

Edward 45 Quality Assurance

Alex 36 Human Resource

Employee table following 1NF:

33
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Second Normal Form(2NF)

A table is said to be in 2NF if:

1. Table is in 1NF
2. It has no Partial Dependency, i.e., no non-prime attribute is dependent on any proper
subset of any candidate key of the table.

First we will understand what are Prime and Non-prime attributes.

Prime attribute − An attribute, which is a part of the candidate key, isknown as a prime attribute.
Non-prime attribute − An attribute, which is not a part of the candidatekey, is said to be a non-
prime attribute.
For example, we have following table which is having employee data.

Above table is in 1NF as all columns are having atomic [Link] Emp_Id and
Dept_Id are the prime attributes.
As per 2NF rule Emp_Name and Dept_Name must be dependent upon both prime attributes, but
here Emp_name can be identified by Emp_Id and Dept_Name can be identified by Dept_Id
alone. So here partial dependency exists. To make this relation in 2NF we have to break above
table as:

34
DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

35
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Third Normal Form(3NF)

For a relation to be in Third Normal Form it must satisfy the following −

1. It must be in Second Normal form
2. No non-prime attribute is transitively dependent on prime keyattribute.
For example, we have below table for storing employee data.

In above relation Emp_Id is the only prime key attribute.

Now If we see City can be identified by Emp_Id as well as ZIP. ZIP is nota prime attribute,
and also it is not a super key. So we hold below 2 relationships here.

Emp_Id -> ZIP (ZIP can be identified by Emp_Id)ZIP -> City (City
can be identified by ZIP)

Therefore, below transitive dependency is true for above relation.Emp_Id -> ZIP -> City

To convert this relation into 3NF we wil break this into 2 relations as:

36
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any of the
other non-key columns to change

Consider the table Changing the non-key column Full Name may change Salutation.

The entity should be considered already in 2NF and no column entry shouldbe dependent on
any other entry (value) other than the key for the table.

If such an entity exists, move it outside into a new table.

3NF is achieved are considered as the database is normalized.

37
 DBMS - Module II - QUERY LANGUAGE & OPTIMIZATION

Boyce and Codd Normal Form (BCNF)

Boyce and Codd Normal Form is a higher version of the Third Normal form. This
form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which
does not have multiple overlapping candidate keys is said to be in BCNF. For a table to
be in BCNF, following conditions must be satisfied:

 R must be in 3rd Normal Form

 and, for each functional dependency ( X → Y ), X should be a superKey.

------------------------------------------------------------------------------------------------------------------

38