www.android.universityupdates.in | www.universityupdates.in | https://telegram.

me/jntuh

Syntax:

Explanation of syntax:

create trigger [trigger_name]: Creates or replaces an existing trigger with the

trigger_name.
[before | after]: This specifies when the trigger will be executed.
{insert | update | delete}: This specifies the DML operation.
on [table_name]: This specifies the name of the table associated with the trigger.
[for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for
each row being affected.
[trigger_body]: This provides the operation to be performed as trigger is fired .

BEFORE and AFTER of Trigger:

BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

EXAMPLE:

nb negative
balance value in to accounts table. Before inserting, the trigger is activated if the condition is
true. When a trigger activated, the action part of the trigger is get executed.

9. NORMALIZATION
Normalization is the process of minimizing the redundancy from a relation or set of
relations.
It is used to eliminate the Insertion, Update and Deletion Anomalies.
Normalization divides the larger table into the smaller table and links them using
relationship.
Normalization is done with the help of different normal form.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

The inventor of the relational model Edgar Codd proposed the theory of normalization with
the introduction of the First Normal Form, and he continued to extend theory with Second and
Third Normal Form. Later he joined Raymond F. Boyce to develop the theory of Boyce-Codd
Normal Form. In software industry, they are using only up to third normal form and sometimes
Boyce-Codd Normal Form.

Redundancy means having multiple copies of same data in the database. This problem arises
when a database is not normalized. Redundancy leads the following problems.

Wastage of Memory: Disk space is wasted due to storing same copy multiple times.
Storage cost increases: When multiple copies of same data is stored, need more disk
space and storage cost increases.
Update anomaly: When Address of student is stored at several places; a change in the
address must be made in all the places. Changing the address at some places and leaving
other places leads to inconsistency problem.
Insertion Anomaly: The nature of a database may be such that it is not possible to add a
required piece of data unless another piece of unavailable data is also added. For
example, a library database cannot store the details of a new student until that student has
taken atleast one book from the library.
Deletion Anomaly: When some data is deleted, it also deletes other data automatically.
For example, deleting a book details from a library database, it also delete the student
details who have taken the book previously.

10. 1NF (FIRST NORMAL FORM)

A relation (table) is said to be in first normal form if and only if:

Each table cell contains only atomic values (single value).

Each record needs to be uniquely identified by the primary key.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

1NF Example:

HTNO FIRST NAME LAST NAME MOBILE

9999988888
501 Jhansi Rani
7777799999
8888888881
502 Ajay Kumar
7897897897
503 Priya Verma 9898989898

The above table is not in 1NF because 501 and 502 is having two values in mobile column. If we
add a new column as alternative mobile number to the above table, then for 503 alternative
mobile number is NULL. Moreover, if a student
column is meaningless. It is better to add extra rows. If we add extra row for each 501 and 502
then the table looks like

HTNO FIRST NAME LAST NAME MOBILE

501 Jhansi Rani 9999988888
501 Jhansi Rani 7777799999
502 Ajay Kumar 8888888881
502 Ajay Kumar 7897897897
503 Priya Verma 9898989898

But the above table violates primary key constraint. Therefore instead of adding either columns
or rows, the best solution is to split the table into two tables as shown below. If we do as shown

HTNO FIRST LAST HTNO MOBILE

NAME NAME 501 9999988888
501 Jhansi Rani 501 7777799999
502 Ajay Kumar 502 8888888881
502 7897897897
503 Priya Verma 503 9898989898

11. 2NF (SECOND NORMAL FORM)

A relation is said to be in 2-NF if and only if

It should be in 1-NF (First Normal Form)

There should not be any partial functional dependencies

2NF Example:

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

HTNO Name DOB DeptNo DeptName Location

501 Jhansi 30-10-1998 05 CSE A-Block
502 Ajay 24-12-1999 05 CSE A-Block
410 Priya 12-03-2000 04 ECE B-Block
120 Rahul 30-10-1998 01 CIVIL C-Block
415 Smitha 18-06-1999 04 ECE B-Block

The above table is not in 2NF because there exist partial function dependencies. HTNO is a
key attribute in the above table. If every non-key attribute fully dependent on key attribute,
then we say it is fully functional dependent. Consider the below diagram. {Name, DOB,
DeptNo, DeptName, Location} depends on HTNO. But {DeptName, Location} also depends
on DeptNo.

It is clear that DeptName and Location not only depends upon HTNO but also on DeptNo.
So, there exists partial function dependency. This partial functional dependency can be
removed by splitting the above table into two tables as follows.

HTNO Name DOB DeptNo DeptNo DeptName Location

501 Jhansi 30-10-1998 05 05 CSE A-Block
502 Ajay 24-12-1999 05
410 Priya 12-03-2000 04 04 ECE B-Block
120 Rahul 30-10-1998 01 01 CIVIL C-Block
415 Smitha 18-06-1999 04

12. 3NF (THIRD NORMAL FORM)

A relation (table) is in third normal form if and only if it satisfies the following conditions:

It is in second normal form

There is no transitive functional dependency

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

Transitive functional dependency means, we have the following relationships in the table: A
is functionally dependent on B , and B is functionally dependent on C . In this
case, C is transitively dependent on A via B
.

3NF Example:
Consider the following book details table example:

BOOK_DETAILS
BookID GenreID GenreType Price
1 1 Gardening 250.00
2 2 Sports 149.00
3 1 Gardening 100.00
4 3 Travel 160.00
5 2 Sports 320.00

The above table is not in 3NF because there exist transitive dependency. In the table able,
BookID determines GenreID { BookID GenreID }
GenreID determines GenreType. { GenreID GenreType }
BookID determines GenreType via GenreID. { BookID GenreType }
It implies that transitive functional dependency is existing and the structure does not satisfy
third normal form. To bring this table in to third normal form, we split the table into two as
follows:
BOOK_DETAILS
BookID GenreID Price
1 1 250.00 GENRE_DETAILS
2 2 149.00 GenreID GenreType
3 1 100.00 1 Gardening
4 3 160.00 2 Sports
5 2 320.00 3 Travel

13. BOYCE CODD NORMAL FORM (BCNF)

A relation (table) is said to be in the BCNF if and only if it satisfy the following conditions:

It should be in the Third Normal Form.

super key.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

non-prime
attribute, if B is a prime attribute.

Example: Below we have a Patient table of a hospital. A patient can go to hospital many
times to take treatment. On a single day many patients can take treatment.
PatientID Name EmailID AdmittedDate Drug Quntity
101 Ram ram@gmail.com 30/10/1998 A-10 10
102 Jhon jho@gmail.com 30/10/1998 X-90 10
101 Ram ram@gmail.com 10/06/2001 X-90 20
103 Sowmya sam@gmail.com 05/03/2002 Y-30 15
102 Jhon jho@gmail.com 05/03/2002 A-10 15

In the above table, {PateintID, AdmittedDate} acts as Primary key. But if we know the
EmailID value, we can find PatientID value.

That is EmailID PatientID.

In the above dependency, EmailId is non-prime attribute and PatientID is a prime

attribute. Therefore the above table is not in BCNF. In order to bring the table into BCNF,
we split it into two tables as shown below.

PatientID Name AdmittedDate Drug Quntity PatientID EmailID

101 Ram 30/10/1998 A-10 10 101 ram@gmail.com
102 Jhon 30/10/1998 X-90 10 102 jho@gmail.com
101 Ram 10/06/2001 X-90 20 103 sam@gmail.com
103 Sowmya 05/03/2002 Y-30 15
102 Jhon 05/03/2002 A-10 15

In other words we can also define BCNF as there should not be any overlapping between candidate
keys. If you consider the original table (before splitting), we can get two candidate keys {PateintID,
AdmittedDate} and {EmailID, AdmittedDate}.

As there exist overlapping in the candidate keys, the table is not in BCNF. To bring it into BCNF,
we split into two tables as shown above.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

14. 4-NF (FOURTH NORMAL FORM)

A relation is said to be in 4-NF if and only if it satisfies the following conditions

It should be in the Third Normal Form.

The table should not have any Multi-valued Dependency.

What is Multi-valued Dependency?

A table is said to have multi-valued dependency, if the following three conditions are true.

i. A table should have at-least 3 columns for it to have a multi-valued dependency.

ii. For any dependency A B, if there exists multiple value of B for a single value of A,
then the table may have multi-valued dependency. It is represented as A B.
iii. In a relation , if there is a multi-valued dependency between A and B, then B
and C should be independent of each other.

If all these three conditions are true for any relation (table), then it contains multi-valued
dependency. The multi-valued dependency can be explained with an example. Let the Relation R
containing three columns A, B, C and four rows s, t, u, v.

A B C
s a1 b1 c1
t a1 b1 c2
u a1 b2 c1
v a1 b2 c2
If s(A) = t(A) = u(A) = v(A)
s(B) = t(B) and s(B) = v(B)
s(C) = u(C) and t(C) = v(C) , then there exist multi-valued dependency.

Example: Consider the below table with columns , and

.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

As shown in the above figure, if 501 opted for subjects like Java and C# and hobbies
of 501 are Cricket and Dancing. Similarly, If 502 opted for subjects like Python and
Android and hobbies of 501 are Chess and Singing, then it can be written into a table with
three columns as follows:
HTNO Subject Hobby
501 Java Cricket
501 Java Dancing
501 C# Cricket
501 C# Dancing
502 Python Chess
502 Python Singing
502 Android Chess
502 Android Singing

As there exist multi valued dependency, the above table is decomposed into two tables such
that

HTNO Subject HTNO Hobby

501 Java 501 Cricket
501 C# 501 Dancing
502 Python 502 Chess
502 Android 502 Singing

Now these tables (relations) satisfy the fourth normal form.

15. 5NF
A relation is said to be in 5-NF if and only if it satisfies the following conditions

It should be in the Fourth Normal Form.

The table should not have any join Dependency and joining should be lossless.

5NF is also known as Project-join normal form (PJ/NF).

A table is decomposed into multiple small tables to eliminate redundancy, and when
we re-join the decomposed tables, there should not be any loss in the original data or
shold not create any new data. In simple words, joining two or more decomposed table
should not lose records nor create new records.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

Example: Consider a table which contains a record of Subject, Professor and Semester in
three columns. The primary key is the combination of all three columns. No column itself is not
a candidate key or a super key.

In the table, DBMS is taught by Ravindar and Uma Rani in semester 4, DS by Sindhusha and
Venu in sem 3. In this case, the combination of all these fields required to identify valid data.

So to make the table into 5NF, we can decompose it into three relations,

Subject Professor Semester

C Srilatha 2
DBMS Ravindar 4
DS Sindhusha 3
DBMS Uma Rani 4
CN Srikanth 5
DS Venu 3
WT Srinivas 5

The above table is decomposed into three tables as follows to bring it into 5-NF.

Subject Professor Semester Professor Semester Subject

C Srilatha 2 Srilatha 2 C
DBMS Ravindar 3 Ravindar 4 DBMS
DS Sindhusha 5 Sindhusha 3 DS
DBMS Uma Rani 3 Uma Rani 5 CN
CN Srikanth 5 Srikanth 5 WT
DS Venu 2 Venu
WT Srinivas 5 Srinivas

16. LOSS LESS JOIN DECOMPOSITION

Decomposition of a relation R into R1 and R2 is lossless-join decomposition if at least one of
the following functional dependencies are in F+ (Closure of functional dependencies)

Consider a relation R which is decomposed into sub relations R1 and R2 .

This decomposition is called lossless join decomposition when we join R1 and R2 and
if we get the same relation R that was decomposed.
For lossless join decomposition, we always have: R 1 R 2

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

 www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh

Example 1: Consider the following relation R( A , B , C ). Let this relation is decomposed

into two sub relations R1( A , B ) and R2( B , C )

R R1 R2
A B C A B B C
1 2 1 1 2 and 2 1
2 5 3 2 5 5 3
3 3 3 3 3 3 3

Now, let us check whether this decomposition is lossless or not. For lossless decomposition,
we must have: R1 R2 = R . Now, if we perform the natural join ( ) of the sub relations
R1 and R2 , we get
A B C
1 2 1
This relation is same as the original relation R.
2 5 3
3 3 3

Thus, we conclude that the above decomposition is lossless join decomposition. This is
because the resultant relation after joining the sub relations is same as the decomposed
relation. No extraneous tuples (rows) appear after joining of the sub-relations.

Example 2: Consider the following relation R( A , B , C ). Let this relation is decomposed

into two sub relations R1( A , C ) and R2( B , C )

R R1 R2
A B C A C B C
1 2 1 1 1 and 2 1
2 5 3 2 3 5 3
3 3 3 3 3 3 3

Now, let us check whether this decomposition is lossless or not. For lossless decomposition,
we must have: R1 R2 = R . Now, if we perform the natural join ( ) of the sub relations
R1 and R2 , we get
A B C
1 2 1
This relation is not same as the original relation R.
2 5 3
2 3 3
3 5 3
3 3 3

Thus, we conclude that the above decomposition is not lossless join decomposition. This is
because the resultant relation after joining the sub relations is not same as the decomposed
relation. Extraneous tuples (rows) appear after joining of the sub-relations.

www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh