Program: MBA(BA),4th Sem

Subject: DBMS
Sub Code: KMBA 406

UNIT-3
SQL:

Structured Query Language (SQL) is a standardized programming language that is

used to manage relational databases and perform various operations on the data in
them. Initially created in the 1970s, SQL is regularly used not only by database
administrators but also by developers writing data integration scripts and data
analysts looking to set up and run analytical queries.

The term SQL is pronounced ess-kew-ell or sequel.

SQL is used for the following:

 Modifying database table and index structures.

 Adding, updating and deleting rows of data.
 Retrieving subsets of information from within relational database management
systems (RDBMSes). This information can be used for transaction processing,
analytics applications and other applications that require communicating with a
relational database.
SQL queries and other operations take the form of commands written as statements
and are aggregated into programs that enable users to add, modify or retrieve data
from database tables.

Why is SQL important?

SQL is a great language to learn because it's the primary database language used
for data processing tasks and is used across various industries.

The following reasons highlight the importance of SQL:

 Backbone of the data industry. SQL is considered the backbone of the data
industry. It's widely used by data-centric professionals including data
analysts, data scientists, business analysts and database developers.
 Universal language. SQL is a universal language that is transferable to other
disciplines and languages. Learning SQL can help one understand the workings
of other languages such as Python and Java. It also makes collaboration easy, as
it has a large supportive community.
 In-demand skill. SQL knowledge is one of the most in-demand skills in the
field of data science. It appears in a significant percentage of data science job
postings, making it a prized skill for professionals in this field.
 Data manipulation. SQL is well-suited for data manipulation. It enables users
to easily test and manipulate data, making it efficient for tasks such as filtering,
sorting and aggregating data.
 Rapid query processing. SQL enables rapid query processing, enabling users
to retrieve, manipulate or store data quickly and efficiently. However,
optimizing queries for rapid processing involves a combination of proper
indexing, query optimization and database design considerations.
 Security features. SQL provides various security features such as
authentication, access control, audit trails and encryption, making it easy to
manage permissions and ensure the security of data.
 Commonality and compatibility. SQL is widely used in various IT systems
and is compatible with multiple other languages. Its commonality benefits
beginners in the profession, as they are likely to use SQL throughout their
 careers. It also contributes to ease of application and improves the production
and efficiency of businesses.
 Scalability. SQL is suitable for organizations of any size. It can easily scale up
to accommodate future growth, making it a versatile choice for small and large
businesses alike.
 Open source and community support. SQL is an open source language with a
vibrant community of developers that regularly provide updates and
troubleshooting assistance to SQL users.
 Cost-effective. Due to its open source nature, SQL is more cost-effective than
proprietary solutions, making it ideal for organizations with budget constraints.
SQL commands and syntax
SQL is, fundamentally, a programming language designed for accessing,
modifying and extracting information from relational databases. As a programming
language, SQL has commands and a syntax for issuing those commands.

SQL commands are divided into several different types, including the following:

 Data Definition Language (DDL) commands are also called data definition
commands because they are used to define data tables.
 Data Manipulation Language (DML) commands are used to manipulate data
in existing tables by adding, changing or removing data. Unlike DDL
commands that define how data is stored, DML commands operate in the tables
defined with DDL commands.
 Data Query Language consists of just one command, SELECT, used to get
specific data from tables. This command is sometimes grouped with the DML
commands.
 Data Control Language commands are used to grant or revoke user access
privileges.
 Transaction Control Language commands are used to change the state of
some data -- for example, to COMMIT transaction changes or to ROLLBACK
transaction changes.
What Is a Data Type?

SQL data types define the nature of data that can be stored in database objects, like
tables. Every table has columns, and each column has a name and data type
associated with it.

Categories of SQL Data Types

There are six primary categories of pre-defined SQL data types that you can
choose from when creating a table. These categories are as follows:
What Is an SQL operator?

An operator is a reserved word or a character that is used to query our database in a

SQL expression. To query a database using operators, we use a WHERE clause.
Operators are necessary to define a condition in SQL, as they act as a connector
between two or more conditions. The operator manipulates the data and gives the
result based on the operator’s functionality.

What Are the Types of SQL Operators?

SQL Operators

SQL queries are used by every database administrator and user to manipulate and
access the data of database tables and views. Reserved words and characters,
which are utilized to carry out arithmetic operations, logical operations,
comparison operations, compound operations, etc., are used to manipulate and
retrieve the data.
SQL operators are what make the SQL queries more useful in data retrieval in
terms of data filtration.

Generally, there are three types of operators that are used in SQL.

1. Arithmetic Operators
2. Comparison Operators
3. Logical Operators

1. Arithmetic SQL Operators

Arithmetic operators are used to perform arithmetic operations such as addition,

subtraction, division, and multiplication. These operators usually accept numeric
operands.

Here is a table describing all the SQL Arithmetic Operators along with their
functionalities.

Operator Operation Explanation

This operator is used for adding the numeric

+ Addition or sum
operands on either side of the operator

Subtraction or This operator is used for subtracting the right

–
difference numeric operand from the left numeric operand.

This operator is used for multiplying a given

numeric value to the operand given on the other
* Multiplication
side of the operator or multiplying the numeric
columns provided on either side of the operator.

This operator is used for returning a quotient by

/ Division dividing the first operand by the second operand or
one numeric column by another.
2. Comparison SQL Operators

Comparison operators in SQL are used to check the equality of two expressions. It
checks whether one expression is identical to another. Comparison operators are
generally used in the WHERE clause of a SQL query. The result of a comparison
operation may be TRUE, FALSE or UNKNOWN. When one or both the
expression is NULL, then the operator returns UNKNOWN.

Look at the table below to know what all comparison operators are allowed in
SQL.

Operator Operation Explanation

This operator checks if the left operand is greater

> Greater than
than the right operand.

This operator checks if the left operand is less than

< Less than
the right operand.

This operator checks if the values of the operands on

= Equal to
both sides of the operator are the same.

It checks for the value of the left operand to be

Greater than
>= greater than or equal to the value of the right-hand
or equal to
operand.

It checks for the value of the left-hand operand to be

Less than or
<= less than or equal to the value of the right-hand
equal to
operand.

This operator checks for inequality and returns

< > or != Not equal to TRUE if the left and right operands are not equal to
each other.
 This operator returns TRUE if the left-hand operand
Not greater is not greater than the right-hand operand. It is
!>
than equivalent to less than or equal to the operator in
SQL.

This operator returns TRUE if the left-hand operand

!< Not less than is not equal to the right-hand operand. It is equivalent
to the greater than or equal to the operator in SQL

3. Logical SQL Operators

Logical operators are those operators that take two expressions as operands and
return TRUE or False as output. While working with complex SQL statements and
queries, comparison operators come in handy and these operators work in the same
way as logic gates do

OR: This logical OR returns TRUE if at least one of the given conditions
separated by OR is fulfilled, otherwise returns FALSE.

AND: This logical operator returns TRUE if all the mentioned conditions separated
by AND are met, otherwise returns FALSE

NOT: Logical NOT operator has been used for the negation of the output of any
logical operator. It checks for the reverse of a given logical expression.

…………………………………………………..

What is an Aggregate Function in SQL?

An aggregate function in SQL returns one value after calculating multiple column
values. We often use aggregate functions with the SELECT statement's GROUP BY
and HAVING clauses.

There are five types of SQL aggregate functions:

 Count()

 Sum()

 Avg()

 Min()

 Max()

………………………………………………………………………………….

What Are Joins?

In SQL, joins are operations used to combine rows from two or more
tables based on a related column between them. Joins are essential in
relational databases because they allow you to retrieve data that is
spread across multiple tables and present it in a unified way. This is
particularly useful in normalized databases where related data is stored
in separate tables to reduce redundancy and improve data integrity.

Why Use Joins?

1. Data Normalization: In relational databases, data is often

divided into multiple tables to eliminate redundancy. Joins allow
you to reassemble this data as needed.

2. Complex Queries: Joins enable complex queries that can extract

meaningful insights from multiple related tables.

3. Data Integrity: By linking tables through joins, you maintain data

integrity and consistency, ensuring that related data is accurate and
synchronized.

Types of SQL Joins

There are various types of SQL joins. Let’s explore some of the most
widely used ones:

1.INNER JOIN
An INNER JOIN is one of the most commonly used joins in SQL. It
returns only the rows that have matching values in both tables. If there
is no match, the row is not included in the result set.

The INNER JOIN essentially combines the data from two or more
tables based on a related column, and it filters out rows that do not
satisfy the join condition.

inner join

Syntax of INNER JOIN

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
2.LEFT JOIN
A LEFT JOIN, also referred to as a LEFT OUTER JOIN, is a type of
SQL join where all rows from the left table (table1) are returned
regardless of whether there is a match in the right table (table2). It
includes the matching rows from the right table based on a related
column specified in the ON clause of the LEFT JOIN statement.

This join condition ensures that for each row from the left table, the
LEFT JOIN includes the corresponding matching rows from the right
table in the result set. If there is no match found in the right table,
NULL values are returned for the columns from the right table.

left join

Syntax of LEFT JOIN

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
 SELECT columns specifies the columns you want to select from the
combined result set.

 table1 and table2 are the names of the tables you want to join.

 LEFT JOIN indicates that you're performing a left join.

 ON table1.common_column = table2.common_column specifies the join

condition, where common_column is the column that exists in both
tables and is used to match rows between them.

3.RIGHT JOIN

A RIGHT JOIN, also known as RIGHT OUTER JOIN, is a type of

SQL join that returns all rows from the right table (table2) and
includes matching rows from the left table (table1). The join condition
is based on a related column between the two tables, specified in the
ON clause of the RIGHT JOIN statement.

The RIGHT JOIN ensures that all rows from the right table are
retained in the result set, and for each row from the right table, it
includes the corresponding matching rows from the left table based on
the join condition. If there is no match found in the left table, NULL
values are returned for the columns from the left table.
right join

Syntax of RIGHT JOIN

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

 SELECT columns specifies the columns you want to select from the
combined result set.

 table1 and table2 are the names of the tables you want to join.

 RIGHT JOIN indicates that you're performing a right join.

 ON table1.common_column = table2.common_column specifies the join

condition, where common_column is the column that exists in both
tables and is used to match rows between them.

4.FULL JOIN
A FULL JOIN, also known as a FULL OUTER JOIN, is a type of SQL
join that returns all rows from both the left table (table1) and the right
table (table2). When there is a match between the tables based on the
join condition, the result set includes the matched rows from both
tables. When there is no match, the result set includes NULL values for
columns from the table without a match.

full join

Syntax of FULL JOIN

SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

 columns specifies the columns you want to select.

 table1 and table2 are the names of the tables you want to join.

 FULL JOIN indicates that you're performing a full outer join.

 common_column is the column that exists in both tables and is used as

the join condition.

5.CROSS JOIN

A CROSS JOIN is a type of SQL join that returns the Cartesian product
of the two joined tables. This means that each row from the first table
is combined with each row from the second table. The result set
includes all possible combinations of rows from both tables.

cross join

Example of CROSS JOIN

Let’s consider two example tables: customers and orders.

customers table:

| id | name | email |
|----|---------|--------------------|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Charlie | charlie@example.com|
| 4 | David | david@example.com |

products table:

|id | name | amount |

|----|------------|--------------|
| 1 | laptop | 150 |
| 2 | mouse | 200 |
| 3 | keyboard | 100 |
| 4 | speaker | 80 |

We want to create a Cartesian product of

the customers and products tables.
Query:

SELECT customers.name AS customer_name, products.name AS product_name,

products.amount
FROM customers
CROSS JOIN products;

Result:

The CROSS JOIN will combine each row from the customers table with
each row from the products table, resulting in the Cartesian product of
the two tables. Here is what the result will look like:

| customer_name | product_name | amount |

|---------------|--------------|--------|
| Alice | laptop | 150 |
| Alice | mouse | 200 |
| Alice | keyboard | 100 |
| Alice | speaker | 80 |
| Bob | laptop | 150 |
| Bob | mouse | 200 |
| Bob | keyboard | 100 |
| Bob | speaker | 80 |
| Charlie | laptop | 150 |
| Charlie | mouse | 200 |
| Charlie | keyboard | 100 |
| Charlie | speaker | 80 |
| David | laptop | 150 |
| David | mouse | 200 |
| David | keyboard | 100 |
| David | speaker | 80 |

The CROSS JOIN is especially useful for generating combinations and

creating test data, but it should be used with caution due to the
potential size of the result set. Understanding how to use CROSS JOIN
can greatly enhance your ability to work with and analyze relational
data.
6.SELF JOIN
A SELF JOIN is a type of SQL join where a table is joined with itself.
This is useful when you want to compare rows within the same table. It
allows you to create relationships between rows in the same table
based on certain conditions.

Syntax of SELF JOIN

SELECT t1.column1, t2.column2

FROM table t1
JOIN table t2 ON t1.common_column = t2.common_column;

 t1 and t2 are aliases for the same table.

 common_column is the column that exists in both instances of the table

and is used as the join condition.

The SELF JOIN is a powerful SQL technique that enables you to join a
table with itself, allowing for comparisons and relationships between
rows within the same table. Understanding how to use SELF JOIN can
be beneficial in various scenarios, especially when dealing with
hierarchical data or comparing records within a dataset.
……………………………………….
PL/SQL:
Cursor:

A cursor is a named control structure used by an application program to point to and

select a row of data from a result set. Instead of executing a query all at once, you can
use a cursor to read and process the query result set one row at a time.

A cursor in a PL/SQL context is treated as a WITH HOLD cursor. For more information
about WITH HOLD cursors, see “DECLARE CURSOR statement”.
Each cursor has a set of attributes that enables an application program to test the state
of the cursor.

These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT.

%ISOPEN
This attribute is used to determine whether a cursor is in the open state. When a
cursor is passed as a parameter to a function or procedure, it is useful to know
(before attempting to open the cursor) whether the cursor is already open.
%FOUND
This attribute is used to determine whether a cursor contains rows after the
execution of a FETCH statement. If FETCH statement execution was successful, the
%FOUND attribute has a value of true. If FETCH statement execution was not
successful, the %FOUND attribute has a value of false. The result is unknown when:
 The value of cursor-variable-name is null
 The underlying cursor of cursor-variable-name is not open
 The %FOUND attribute is evaluated before the first FETCH statement was
executed against the underlying cursor
 FETCH statement execution returns an error

The %FOUND attribute provides an efficient alternative to using a condition handler

that checks for the error that is returned when no more rows remain to be fetched.

%NOTFOUND
This attribute is the logical opposite of the %FOUND attribute.
%ROWCOUNT
This attribute is used to determine the number of rows that have been fetched since
a cursor was opened.

Example

Command> DECLARE
CURSOR c1 IS
SELECT last_name, salary, hire_date, job_id FROM employees
WHERE employee_id = 120;
--declare record variable that represents a row
--fetched from the employees table
employee_rec c1%ROWTYPE;
BEGIN
-- open the explicit cursor
-- and use it to fetch data into employee_rec
OPEN c1;
FETCH c1 INTO employee_rec;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
CLOSE c1;
END;
 /
Employee name: Weiss

PL/SQL procedure successfully completed.

…………………..

TRIGGERS:

Triggers are procedures that are stored in the database and implicitly run, or fired,
when something happens.

Traditionally, triggers supported the execution of a PL/SQL block when

an INSERT, UPDATE, or DELETE occurred on a table or view. Starting with Oracle8i,
triggers support system and other data events on DATABASE and SCHEMA.

Designing Triggers

Use the following guidelines when designing your triggers:

 Use triggers to guarantee that when a specific operation is performed, related

actions are performed.
 Do not define triggers that duplicate features already built into Oracle
Database. For example, do not define triggers to reject bad data if you can do
the same checking through declarative integrity constraints.
 Limit the size of triggers. If the logic for your trigger requires much more than
60 lines of PL/SQL code, it is better to include most of the code in a stored
procedure and call the procedure from the trigger.
 Use triggers only for centralized, global operations that should be fired for the
triggering statement, regardless of which user or database application issues the
statement.
 Do not create recursive triggers. For example, creating
an AFTER UPDATE statement trigger on the Emp_tab table that itself issues
an UPDATE statement on Emp_tab, causes the trigger to fire recursively until it has
run out of memory.
 Use triggers on DATABASE judiciously. They are executed
for every user every time the event occurs on which the trigger is created.

Creating Triggers

Triggers are created using the CREATE TRIGGER statement. This statement can be used
with any interactive tool, such as SQL*Plus or Enterprise Manager. When using an
interactive tool, a single slash (/) on the last line is necessary to activate
the CREATE TRIGGER statement.

The following statement creates a trigger for the Emp_tab table.

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :new.sal - :old.sal;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
/

The trigger is fired when DML operations (INSERT, UPDATE, and DELETE statements) are
performed on the table. You can choose what combination of operations should fire
the trigger.

……………………….
Procedure in PL/SQL:
PL/SQL procedure syntax

A PL/SQL procedure is a reusable unit that encapsulates the specific business

logic of the application. Technically speaking, a PL/SQL procedure is a
named block stored as a schema object in the Oracle Database.

The following illustrates the basic syntax of creating a procedure in PL/SQL:

CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)

IS
[declaration statements]

BEGIN

[execution statements]

EXCEPTION

[exception handler]

END [procedure_name ]; Code language: SQL (Structured Query Language)

(sql)
PL/SQL procedure header

A procedure begins with a header that specifies its name and an optional
parameter list.

Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode
specifies whether a parameter can be read from or written to.
IN

An IN parameter is read-only. You can reference an IN parameter inside a

procedure, but you cannot change its value. Oracle uses IN as the default
mode. It means that if you don’t specify the mode for a parameter explicitly,
Oracle will use the IN mode.
OUT

An OUT parameter is writable. Typically, you set a returned value for

the OUT parameter and return it to the calling program. Note that a procedure
ignores the value that you supply for an OUT parameter.
INOUT

An INOUT parameter is both readable and writable. The procedure can be read
and modified.

Note that OR REPLACE option allows you to overwrite the current procedure with
the new code.

PL/SQL procedure body

Similar to an anonymous block, the procedure body has three parts. The
executable part is mandatory whereas the declarative and exception-handling
parts are optional. The executable part must contain at least one executable
statement.

1) Declarative part

In this part, you can declare variables, constants, cursors, etc. Unlike
an anonymous block, a declaration part of a procedure does not start with
the DECLARE keyword.

2) Executable part
This part contains one or more statements that implement specific business
logic. It might contain only a NULL statement.

3) Exception-handling part

This part contains the code that handles exceptions.

Creating a PL/SQL procedure example

The following procedure accepts a customer id and prints out the customer’s
contact information including first name, last name, and email:

CREATE OR REPLACE PROCEDURE print_contact(

in_customer_id NUMBER
)
IS
r_contact contacts%ROWTYPE;
BEGIN
-- get contact based on customer id
SELECT *
INTO r_contact
FROM contacts
WHERE customer_id = p_customer_id;

-- print out contact's information

dbms_output.put_line( r_contact.first_name || ' ' ||
r_contact.last_name || '<' || r_contact.email ||'>' );

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;
Code language: SQL (Structured Query Language) (sql)

Executing a PL/SQL procedure

The following shows the syntax for executing a procedure:

EXECUTE procedure_name( arguments);

……………………………………………………………………..
What is Functional Dependency in
DBMS
Functional dependency in a database management system refers to the relationship
between attributes in a table, where one attribute uniquely determines another. It helps
maintain data accuracy and consistency by ensuring that the value of one attribute (the
determinant) defines the value of another attribute (the dependent).

Armstrong’s Axioms OR Properties of

Functional Dependency in DBMS
Armstrong’s Axioms are a set of rules used to infer all possible functional dependencies
in a database. They are used to make sure the database is consistent and well
structured. These properties such as reflexivity, augmentation, and transitivity are
essential for understanding and managing functional dependencies during
normalization.

Reflexivity:
If an attribute or set of attributes is a subset of another, it determines itself.
Example: If A is part of AB, then AB → A.

Augmentation:
Adding extra attributes to both sides of a dependency keeps it valid.
Example: If A → B, then AC → BC.

Transitivity:
If one attribute depends on another, and that one depends on a third, the first can
determine the third.
Example: If A → B and B → C, then A → C.
Types of Dependency in DBMS
In DBMS, functional dependencies assist in describing how attributes of a table are
related to one another. Different types of dependency in DBMS exist, and each is
extremely important in the nature of the data being arranged and the accuracy's
consistency. It is important for the database to be designed and normalised to these
types of specialisations.

Trivial Functional Dependency

A trivial functional dependency occurs when an attribute or a set of attributes (Coumn)
determines itself or any subset of itself. In other words, the dependency is obvious and
doesn’t provide any useful information about the relationship between attributes.

Let’s go back to the student table from earlier:

Student_ID Name Department

142 Alice Physics

 Student_ID Name Department

243 Bob Physics

344 Bob Mathematics

445 Charlie Mathematics

546 Charlie Mathematics

747 Alice Computer Science

These can be considered as trivial functional dependencies:

Student_ID → Student_ID

This is a trivial functional dependency because Student_ID trivially determines itself. All
attributes will always functionally determine themselves; therefore, this is a trivial
dependency.

Name → Name

Similarly, Name trivially determines Name. Like before, an attribute always determines
itself.

Non-Trivial Functional Dependency

A non-trivial functional dependency occurs when an attribute or set of attributes
determines another attribute. This dependency is also not self-evident. Also the right-
hand side of the dependency adds meaningful information that isn't already contained
within the left-hand side. Consider the table:
 Student_ID Name Department

142 Alice Physics

243 Bob Physics

344 Bob Mathematics

445 Charlie Mathematics

546 Charlie Mathematics

747 Alice Computer Science

Student_ID → Name, Department

Here, Student_ID uniquely determines both Name and Department. This is a non-trivial
functional dependency because the left-hand side (Student_ID) determines two other
attributes (Name and Department), which are not subsets of Student_ID. The value of
Student_ID is enough to uniquely determine the corresponding Name and Department,
making this a valid and meaningful dependency.

Multivalued Functional Dependency

A multivalued functional dependency occurs if one attribute or a set of attributes
determines a set of values of another attribute, but dependant set values are mutually
independent. That is, if the intersection of one attribute for a relation results in multiple
values of each attribute in the other relation, each value can exist independently of the
other values.
Consider the same student table again:

Student_ID Name Department

142 Alice Physics

243 Bob Physics

344 Bob Mathematics

445 Charlie Mathematics

546 Charlie Mathematics

747 Alice Computer Science

Student_ID → Name, Department

Here, Student_ID determines a set of values for both Name and Department. The
dependency indicates that for each unique Student_ID, there is a corresponding set of
Name and Department values. Importantly, changes in Name or Department do not
affect the other attributes. So each Student_ID maps to these values independently.

Transitive Functional Dependency

A transitive functional dependency is said to be when an attribute indirectly determines
another attribute through a third attribute. In other words, if A → B and B → C, then A →
C becomes a transitive dependency.

In Table, we can identify a potential Transitive Functional Dependency:

 Student_ID Name Department

142 Alice Physics

243 Bob Physics

344 Bob Mathematics

445 Charlie Mathematics

546 Charlie Mathematics

747 Alice Computer Science

Student_ID → Name

Name → Department

From these two functional dependencies, we can infer the transitive dependency:

Student_ID → Department

Since Student_ID → Name and Name → Department, we can conclude that Student_ID
→ Department through a transitive relationship.

Fully Functional Dependency

A Fully Functional Dependency occurs when an attribute is functionally dependent on
the entire primary key and not just on a part of it. This type of dependency has every
attribute in the table rely entirely on the primary key for its value.
In the table, since we have single attributes like Student_ID acting as the determinant,
we can observe the following Fully Functional Dependency:

Student_ID Name Department

142 Alice Physics

243 Bob Physics

344 Bob Mathematics

445 Charlie Mathematics

546 Charlie Mathematics

747 Alice Computer Science

Student_ID → Name, Department

The attribute Name is fully dependent on Student_ID because knowing the Student_ID
alone is sufficient to uniquely determine the corresponding Name. Similarly, the
Department is also fully dependent on Student_ID, as each Student_ID uniquely
determines the associated Department.

Partial Functional Dependency

A Partial Functional Dependency occurs when an attribute is dependent on only a part
of a composite primary key and not the entire key. This type of dependency typically
arises in tables with composite keys. Here, some non-key attributes rely on only one
part of the composite key.

Student_ID Name Department

142 Alice Physics

243 Bob Physics

344 Bob Mathematics

445 Charlie Mathematics

546 Charlie Mathematics

747 Alice Computer Science

In the table, since we are working with single attributes (like Student_ID) as primary
keys, no Partial Functional Dependency exists. However, if the table had a composite
key like a combination of Student_ID and Department, a partial dependency could
occur.

For example:

Suppose Student_ID and Department together form a composite primary key, and the
attribute Name depends only on Student_ID. In this case:

Student_ID → Name

(Student_ID, Department) → Name

Advantages of Functional Dependency
in DBMS
Functional dependencies are very important in any formation of a database and are
very advantageous in several ways.

1. Data Normalization: Functional dependencies assist normalisation as they help

eliminate redundancy and reduce anomalies. These enable one to determine the main
and potential keys through which data can be sorted and placed in other small, easily
manageable tables.
2. Query Optimization: Functional dependencies make it easy to minimise the number of
attributes involved and the query derivation form. They allow the system to detect
needed attributes and optimal routes for accessing the data, enhancing query general
performance.
3. Consistency of Data: Functional dependencies ensure data consistency by preventing
redundancy and inconsistency. Updates to one attribute remain accurate across all
dependent attributes, maintaining coherence in the database.
4. Data Quality Improvement: Functional dependencies are important to ensure that data
is reliable, current, and contains all relevant information. General dependencies
minimise errors or variations and improve the quality of data used for analysis and
decision-making.
5. Data Integrity: Defining functional dependencies makes certain rules relating to
attributes that make the relationship within the database valid and accurate. This helps
in avoiding the entry of wrong or two sets of figures for a particular item.
6. Efficient Storage: With the help of normalisation, the functional dependencies lead to
the reduction of redundant data and thus can make efficient use of storage units.
Dividing information into many small, ordered tables can also be less complex, and data
in these tables are easier to optimise for usage.
 7. Ease of Maintenance: It is easy to update and maintain databases constructed with
functional dependencies. Updates are characterised and always expectant because they
have a predictive effect on any change of schema.

Normalization in DBMS
Normalization in DBMS is a technique using which you can organize the data in the
database tables so that:

 There is less repetition of data,

 A large set of data is structured into a bunch of smaller tables,
 and the tables have a proper relationship between them.

DBMS Normalization is a systematic approach to decompose (break down) tables to

eliminate data redundancy(repetition) and undesirable characteristics like Insertion
anomaly in DBMS, Update anomaly in DBMS, and Delete anomaly in DBMS.

It is a multi-step process that puts data into tabular form, removes duplicate data, and
set up the relationship between tables.

Need of Normalization in DBMS?

Normalization is required for,

 Eliminating redundant(useless) data, therefore handling data integrity, because

if data is repeated it increases the chances of inconsistent data.
 Normalization helps in keeping data consistent by storing the data in one table
and referencing it everywhere else.
 Storage optimization although that is not an issue these days because Database
storage is cheap.
 Breaking down large tables into smaller tables with relationships, so it makes the
database structure more scalable and adaptable.
 Ensuring data dependencies make sense i.e. data is logically stored.

Types of DBMS Normal forms

Normalization rules are divided into the following normal forms:

1. First Normal Form

2. Second Normal Form
3. Third Normal Form
Note: BCNF to DKNF are not in your syllabus

1. First Normal Form (1NF)

For a table to be in the First Normal Form, it should follow the following 4 rules:

1. It should only have single(atomic) valued attributes/columns.

2. Values stored in a column should be of the same domain.
3. All the columns in a table should have unique names.
4. And the order in which data is stored should not matter.

example.

If we have an Employee table in which we store the employee information along with
the employee skillset, the table will look like this:

emp_id emp_name emp_mobile emp_skills

1 John Tick 9999957773 Python, JavaScript

2 Darth Trader 8888853337 HTML, CSS, JavaScript

3 Rony Shark 7777720008 Java, Linux, C++

The above table has 4 columns:

 All the columns have different names.

 All the columns hold values of the same type like emp_name has all the
names, emp_mobile has all the contact numbers, etc.
 The order in which we save data doesn't matter
 But the emp_skills column holds multiple comma-separated values, while as per
the First Normal form, each column should have a single value.

Hence the above table fails to pass the First Normal form.
So how do you fix the above table? There are two ways to do this:

1. Remove the emp_skills column from the Employee table and keep it in some
other table.
2. Or add multiple rows for the employee and each row is linked with one skill.

1. Create Separate tables for Employee and Employee Skills

So the Employee table will look like this,

emp_id emp_name emp_mobile

1 John Tick 9999957773

2 Darth Trader 8888853337

3 Rony Shark 7777720008

And the new Employee_Skill table:

emp_id emp_skill

1 Python

1 JavaScript
 emp_id emp_skill

2 HTML

2 CSS

2 JavaScript

3 Java

3 Linux

3 C++

2. Add Multiple rows for Multiple skills

You can also simply add multiple rows to add multiple skills. This will lead to repetition
of the data, but that can be handled as you further Normalize your data using the
Second Normal form and the Third Normal form.

emp_id emp_name emp_mobile emp_skill

1 John Tick 9999957773 Python

 emp_id emp_name emp_mobile emp_skill

1 John Tick 9999957773 JavaScript

2 Darth Trader 8888853337 HTML

2 Darth Trader 8888853337 CSS

2 Darth Trader 8888853337 JavaScript

3 Rony Shark 7777720008 Java

3 Rony Shark 7777720008 Linux

3 Rony Shark 7777720008 C++

If you want to learn about the First Normal Form in detail, check out DBMS First
Normal Form tutorial.

2. Second Normal Form (2NF)

For a table to be in the Second Normal Form,

1. It should be in the First Normal form.

2. And, it should not have Partial Dependency.
Watch this YouTube video to understand Second Normal Form (if you like videos) - DBMS Second
Normal Form 2NF with Example

Let's take an example to understand Partial dependency and the Second Normal Form.

What is Partial Dependency?

When a table has a primary key that is made up of two or more columns, then all the
columns(not included in the primary key) in that table should depend on the entire
primary key and not on a part of it. If any column(which is not in the primary key)
depends on a part of the primary key then we say we have Partial dependency in the
table.

Confused? Let's take an example.

If we have two tables Students and Subjects, to store student information and
information related to subjects.

Student table:

student_id student_name branch

1 Akon CSE

2 Bkon Mechanical

Subject Table:

subject_id subject_name

1 C Language
 subject_id subject_name

2 DSA

3 Operating System

And we have another table Score to store the marks scored by students in any subject
like this,

student_id subject_id marks teacher_name

1 1 70 Miss. C

1 2 82 Mr. D

2 1 65 Mr. Op

Now in the above table, the primary key is student_id + subject_id, because both these
information are required to select any row of data.

But in the Score table, we have a column teacher_name, which depends on the subject
information or just the subject_id, so we should not keep that information in
the Score table.

The column teacher_name should be in the Subjects table. And then the entire system
will be Normalized as per the Second Normal Form.

Updated Subject table:

 subject_id subject_name teacher_name

1 C Language Miss. C

2 DSA Mr. D

3 Operating System Mr. Op

Updated Score table:

student_id subject_id marks

1 1 70

1 2 82

2 1 65

To understand what is Partial Dependency and how you can normalize a table to 2nd
normal form, jump to the DBMS Second Normal Form tutorial.

3. Third Normal Form (3NF)

A table is said to be in the Third Normal Form when,

 1. It satisfies the First Normal Form and the Second Normal form.
2. And, it doesn't have Transitive Dependency.

Watch this YouTube video to understand the Third Normal Form (if you like videos) - DBMS Third
Normal Form 3NF with Example

What is Transitive Dependency?

In a table we have some column that acts as the primary key and other columns
depends on this column. But what if a column that is not the primary key depends on
another column that is also not a primary key or part of it? Then we have Transitive
dependency in our table.

Let's take an example. We had the Score table in the Second Normal Form above. If we
have to store some extra information in it, like,

1. exam_type
2. total_marks

To store the type of exam and the total marks in the exam so that we can later calculate
the percentage of marks scored by each student.

The Score table will look like this,

student_id subject_id marks exam_type total_marks

1 1 70 Theory 100

1 2 82 Theory 100

2 1 42 Practical 50

 In the table above, the column exam_type depends on

both student_id and subject_id, because,
o a student can be in the CSE branch or the Mechanical branch,
o and based on that they may have different exam types for different
subjects.
 o The CSE students may have both Practical and Theory for Compiler Design,
o whereas Mechanical branch students may only have Theory exams for
Compiler Design.
 But the column total_marks just depends on the exam_type column. And
the exam_type column is not a part of the primary key. Because the primary key
is student_id + subject_id, hence we have a Transitive dependency here.

How to Transitive Dependency?

You can create a separate table for ExamType and use it in the Score table.

New ExamType table,

exam_type_id exam_type total_marks duration

1 Practical 50 45

2 Theory 100 180

3 Workshop 150 300

We have created a new table ExamType and we have added more related information
in it like duration(duration of exam in mins.), and now we can use the exam_type_id in
the Score table.

Here is the DBMS Third Normal Form tutorial. But we suggest you first study the
second normal form and then head over to the third normal form.