Unit - 3

SQL: QUERIES, CONSTRAINTS, TRIGGERS: form of basic SQL query, UNION,

INTERSECT, and EXCEPT, Nested Queries, aggregation operators, NULL values, complex
integrity constraints in SQL, triggers and active databases.

Schema Refinement: Problems caused by redundancy, decompositions, problems related to

decomposition, reasoning about functional dependencies, FIRST, SECOND, THIRD normal
forms, BCNF, lossless join decomposition, multivalued dependencies, FOURTH normal form,
FIFTH normal form.
*************************************************************************
Form of basic SQL query

The basic form of an SQL query, specifically when retrieving data, is composed of a combination
of clauses. The most elementary form of an SQL query for data retrieval can be represented as

Syntax
SELECT [DISTINCT] column1, column2, ...
FROM tablename
WHERE condition;
Let's break it down:

1. SELECT Clause: This is where you specify the columns you want to retrieve. Use an
asterisk (*) to retrieve all columns.
2. FROM Clause: This specifies from which table or tables you want to retrieve the data.
3. WHERE Clause (optional): This allows you to filter the results based on a condition.
4. DISTINCT Clause (optional): is an optional keyword indicating that the answer should
not contain duplicates. Normally if we write the SQL without DISTINCT operator then it
does not eliminate the duplicates.

Here are the primary components of SQL queries:

● SELECT: Retrieves data from one or more tables.

● FROM: Specifies the table from which you're retrieving the data.
 ● WHERE: Filters the results based on a condition.
● GROUP BY: Groups rows that have the same values in specified columns.
● HAVING: Filters the result of a GROUP BY.
● ORDER BY: Sorts the results in ascending or descending order.
● JOIN: Combines rows from two or more tables based on related columns.

To provide a more holistic view, here are a few more SQL examples, keeping them as basic as
possible:

1. Retrieve all columns from a table:

Syntax
SELECT * FROM tablename;

2. Retrieve specific columns from a table:

Syntax
SELECT column1, column2 FROM tablename;

3. Retrieve data with a condition:

Syntax
SELECT column1, column2 FROM tablename WHERE column1 = 'value';

4. Sort retrieved data:

Syntax
SELECT column1, column2 FROM tablename ORDER BY column1 ASC;

Regular expressions in the SELECT Command

SQL provides support for pattern matching through the LIKE operator, along with the use of the
wild-card symbols.
Regular expressions: is a sequence of characters that define a search pattern, mainly for use in
pattern matching with strings, or string matching.

Examples:
Finds Names that start or ends with "a“
Finds names that start with "a" and are at least 3 characters in length.
LIKE: The LIKE operator is used in a 'WHERE' clause to search for a specified pattern in a
column wild-card: There are two primary wildcards used in conjunction with the `LIKE`
operator percent sign (%) Represents zero, one, or multiple characters
underscore sign(_) Represents a single character

Here's a breakdown of how you can use these wildcards with the `LIKE` operator:

Using `%` Wildcard

1. Find values that start with a specific pattern:

Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern%';
For example, to find all customers whose names start with "Ma":

Example
SELECT FirstName
FROM Customers
WHERE FirstName LIKE 'Ma%';
2. Find values that end with a specific pattern:

Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE '%pattern';
For instance, to find all products that end with "ing":
Example
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%ing';
3. Find values that have a specific pattern anywhere:
Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE '%pattern%';

Example, to find all books that have the word "life" anywhere in the title:
Example
SELECT BookTitle
FROM Books
WHERE BookTitle LIKE '%life%';
Using `_` Wildcard
1. Find values of a specific length where you only know some characters:
Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE 'p_ttern';
For instance, if we're looking for a five-letter word where you know the first letter is "h" and the
third letter is "l", you could use:
Example
SELECT Word
FROM Words
WHERE Word LIKE 'h_l__';
Combining `%` and `_`
We can use both wildcards in the same pattern. For example, to find any value that starts with
"A", followed by two characters, and then "o":
Example
SELECT column_name
FROM table_name
WHERE column_name LIKE 'A__o%';
Keep in mind that the actual symbols used for wildcards might vary depending on the database
system. For example, in SQL Server, the wildcard for a single character is `[?]` instead of `_`.
Always refer to the specific documentation of the database you're working with.
***************************************************************************

SQL Set Operation

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

1. Union

2. Union All

3. Intersect

4. Minus/Except

1. Union

○ The SQL Union operation is used to combine the result of two or more SQL SELECT
queries.

○ In the union operation, all the number of data type and columns must be the same in both
the tables on which the UNION operation is being applied.

○ The union operation eliminates the duplicate rows from its result set.
Syntax

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2;

Example:

The First table

ID NAME

1 Jack

2 Harry

3 Jackson

The Second table

ID NAME

3 Jackson

4 Stephan

5 David

Union SQL query will be:

SELECT * FROM First UNION SELECT * FROM Second;

The result set table will look like:

ID NAME

1 Jack

2 Harry

3 Jackson

4 Stephan

5 David

2. Union All

Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.
Syntax:

SELECT column_name FROM table1

UNION ALL

SELECT column_name FROM table2; Example:

Using the above First and Second table.

Union All query will be like:

SELECT * FROM First

UNION ALL

SELECT * FROM Second;

The result set table will look like:

ID NAME

1 Jack

2 Harry

3 Jackson

3 Jackson

4 Stephan

5 David

3. Intersect

○ It is used to combine two SELECT statements. The Intersect operation returns the common
rows from both the SELECT statements.

○ In the Intersect operation, the number of data types and columns must be the same.

○ It has no duplicates and it arranges the data in ascending order by default.

Syntax

SELECT column_name FROM table1

INTERSECT

SELECT column_name FROM table2;

Example:

Using the above First and Second table.

Intersect query will be:

SELECT * FROM First

INTERSECT

SELECT * FROM Second;

The resultset table will look like:

ID NAME

3 Jackson

4. Minus/Except

○ It combines the result of two SELECT statements. Except operator is used to display the
rows which are present in the first query but absent in the second query.

○ It has no duplicates and data arranged in ascending order by default.

Syntax:

SELECT column_name FROM table1

Except

SELECT column_name FROM table2;

Example

Using the above First and Second table.

Except query will be:

SELECT * FROM First

Except

SELECT * FROM Second;

The result set table will look like:

ID NAME

1 Jack

2 Harry
******************************************************************************

Nested Query in SQL

A nested query in SQL contains a query inside another query. The outer query will use the result
of the inner query. For instance,
a nested query can have two SELECT statements, one on the
inner
query and the other on the outer query.

Types of Nested Queries in SQL

Nested queries in SQL can be classified into two different types:

● Independent Nested Queries

 ● Co-related Nested Queries

Independent Nested Queries

In independent nested queries, the execution order is from the innermost query to the outer query.
An outer query won't be executed until its inner query completes its execution. The outer query
uses the result of the inner query. Operators such as IN, NOT IN, ALL, and ANY are used to
write independent nested queries.

● The IN operator checks if a column value in the outer query's result is present in the inner
query's result. The final result will have rows that satisfy the IN condition.
● The NOT IN operator checks if a column value in the outer query's result is not present in
the inner query's result. The final result will have rows that satisfy the NOT IN condition.
● The ALL operator compares a value of the outer query's result with all the values of the
inner query's result and returns the row if it matches all the values.
● The ANY operator compares a value of the outer query's result with all the inner query's
result values and returns the row if there is a match with any value.
Co-related Nested Queries

In co-related nested queries, the inner query uses the values from the outer query to execute the
inner query for every row processed by the outer-related
query. The
nested
co queries run
y slowl
because the inner queryuted
is exec
for every row of the outer query's result.

How to Write a Nested Query? in SQL

We can write a nested query in SQL by nesting a SELECT statement within another SELECT
statement. The outer SELECT statement uses the result of the inner SELECT
or statement f
processing.

The general syntax of nested queries will be:

SELECT column_name [, column_name ]

FROM table1 [, table2]

WHERE column_name OPERATOR

(SELECT column_name [, column_name] FROM table1 [, table2] [WHERE]);

The SELECT query inside the brackets

is the inner
() query, and the SELECT query outside the
brackets is the outer query. The outer query uses the result of the inner query.

Examples of Nested Query in SQL

We will use the Employees and Awards table below to understand independent
-related and co
nested queries. We will be using Oracle SQL syntax in our queries.
Let's create the Employees and Awards tables:

CREATE TABLE employee (id NUMBER PRIMARY KEY, name VARCHAR2(100) NOT
NULL, salary NUMBER NOT NULL, role VARCHAR2(100) NOT NULL );

CREATE TABLE
awards (id NUMBER PRIMARY KEY, employee_id
MBER NOT NUNULL,
award_date DATE NOT NULL);

Let's add data to the tables created above:

INSERT INTO employees VALUES (1, 'Augustine Hammond',

loper');
10000, 'Deve

INSERT INTO employees VALUES (2, 'Perice

', 10000,
Mundford
'Manager'
);

INSERT INTO employees VALUES (3, 'Cassy Delafoy', 30000, 'Developer');

INSERT INTO employees VALUES (4, 'Garwood Saffen', 40000, 'Manager');

INSERT INTO employees VALUES (5, 'Faydra Beaves', 50000, 'Developer');

INSERT INTO awardsUES(1,

VAL 1, TO_DATE('2022
-04-01', 'YYYY
-MM-DD'));

INSERT INTO awards VALUES(2, 3, TO_DATE('2022

-05-01', 'YYYY
-MM-DD'));

Employees

id name salary Role

1 Augustine Hammond 10000 Developer

2 Perice Mundford 10000 Manager

3 Cassy Delafoy 30000 Developer

id name

1 Augustine Hammond

3 Cassy Delafoy

Example 3:
ALL

Select all Developers who earn more than all the

Managers

SELECT * FROM
employees

WHERE role = 'Developer'

AND salary > ALL (SELECT salary FROM employees WHERE role = 'Manager');

Outpu
t
id name salary role

5 Faydra Beaves 50000 Developer

Explanatio
n

The developer with id 5 ns

ear(50000) more than all the managers: 2 (10000) and 4 (40000)

Example 4: ANY

Select all Developers who

earn more than any Manager

SELECT * FROM employ

ees

WHERE role = 'Developer

'

AND salary > ANY (SELECT salary FROM employees WHERE role = 'Manager');
Output

id Name salary role

5 Faydra Beaves 50000 Developer

3 Cassy Delafoy 30000 Developer

Explanation

The developers with id 3 and 5 earn more than any manager:

● The developer with id 3 earns (30000) more than the manager with id 2 (10000)
● The developer with id 5 earns (50000) more than the managers with id 2 (10000)
and 4
(40000)

Co-related Nested Queries

 Select all employees whose salary is above the average salary of employees in their
role.

SELECT * FROM employees

emp1

WHERE salary > (SELECT AVG(salary) FROM employees emp2 WHERE [Link] =

[Link]);

Output

id Name salary role

4 Garwood Saffen
40000 Manager

5 Faydra Beaves50000Developer
Explanatio
n

The manager with id 4 earns more than the average salary of all managers (25000), and the
developer with id 5 earns more than the average salary of all developers (30000).
y The inner quer
is executed for all rows fetched by the outer query. The inner query uses the role value
([Link])
of every outer query's row ([Link] = [Link]).

We can find the average salary of managers and developers using the below query:

SELECT role, AVG(salary)

FROM employees GROUP BY role;

 role avg(salary)

Developer 30000

Manager 25000

************************************************************************

SQL Aggregate Functions

○ SQL aggregation function is used to perform the calculations on multiple rows of a single
column of a table. It returns a single value.

○ It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT FUNCTION

○ The COUNT function is used to Count the number of rows in a database table. It can work
on both numeric and non-numeric data types.

○ COUNT function uses the COUNT(*) that returns the count of all the rows in a specified
table. COUNT(*) considers duplicate and Null.
Syntax
1. COUNT(*)
2. or
3. COUNT([ALL|DISTINCT] expression)

Sample table:

PRODUCT_MAST
PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50
Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example: COUNT()

SELECT COUNT(*)

FROM
PRODUCT_MAST;

Output:
10

Example: COUNT with WHERE

SELECT COUNT(*)

FROM PRODUCT_MAST;

WHERE RATE>=20;

Output:
7

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY)

FROM PRODUCT_MAST;

Output:
3

Example: COUNT() with GROUP BY

SELECT COMPANY, COUNT(*)

FROM PRODUCT_MAST

GROUP BY COMPANY;

Output:
Com1 5

Com2 3
Com3 2

Example: COUNT() with HAVING

SELECT COMPANY, COUNT(*)

FROM PRODUCT_MAST

GROUP BY COMPANY

HAVING COUNT(*)>2;

Output:
Com1 5

Com2 3

2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on numeric fields
only.

Syntax
SUM()

or

SUM([ALL|DISTINCT] expression)

Example: SUM()
SELECT SUM(COST)

FROM PRODUCT_MAST;

Output:
670

Example: SUM() with WHERE

SELECT SUM(COST)

FROM PRODUCT_MAST

WHERE
QTY>3;

Output:
320

Example: SUM() with GROUP BY

SELECT SUM(COST)

FROM PRODUCT_MAST

WHERE QTY>3

GROUP BY COMPANY;

Output:
Com1 150

Com2 170

Example: SUM() with HAVING

SELECT COMPANY, SUM(COST)

FROM PRODUCT_MAST

GROUP BY COMPANY
 HAVING SUM(COST)>=170;

Output:
Com1 335

Com3 170

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function
returns the average of all non-Null values.

Syntax
AVG()

or

AVG([ALL|DISTINCT] expression)

Example:
SELECT AVG(COST)

FROM PRODUCT_MAST;

Output:
67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines
the largest value of all selected values of a column.

Syntax
MAX()
 or

MAX([ALL|DISTINCT]
expression)

Example:
SELECT MAX(RATE)

FROM PRODUCT_MAST;

30

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines
the smallest value of all selected values of a column.

Syntax
MIN()

or

MIN([ALL|DISTINCT]
expression)

Example:
SELECT MIN(RATE)

FROM PRODUCT_MAST;

Output:

10
****************************************************************************
NULL values in SQL
In SQL there may be some records in a table that do not have values or data for every field and
those fields are termed as a NULL value.

NULL values could be possible because at the time of data entry information is not available. So
SQL supports a special value known as NULL which is used to represent the values of attributes
that may be unknown or not apply to a tuple. SQL places a NULL value in the field in the
absence of a user-defined value.

So, NULL values are those values in which there is no data value in the particular field in the
table.

Importance of NULL Value

● It is important to understand that a NULL value differs from a zero value.
● A NULL value is used to represent a missing value, but it usually has one of three
different interpretations:
● The value unknown (value exists but is not known)
● Value not available (exists but is purposely withheld)
● Attribute not applicable (undefined for this tuple)
● It is often not possible to determine which of the meanings is intended. Hence, SQL
does not distinguish between the different meanings of NULL.

Principles of NULL values

● Setting a NULL value is appropriate when the actual value is unknown, or when a
value is not meaningful.
● A NULL value is not equivalent to a value of ZERO if the data type is a number and
is not equivalent to spaces if the data type is a character.
● A NULL value can be inserted into columns of any data type.
● A NULL value will evaluate NULL in any expression.
● Suppose if any column has a NULL value, then UNIQUE, FOREIGN key, and
CHECK constraints will be ignored by SQL.
Test for NULL Values:

SQL allows queries that check whether an attribute value is NULL. Rather than using = or to
compare an attribute value to NULL, SQL uses IS and IS NOT. This is because SQL considers
each NULL value as being distinct from every other NULL value, so equality comparison is not
appropriate.

Now, consider the following Employee Table.

Query:

CREATE TABLE Employee (Fname VARCHAR(50),Lname

VARCHAR(50),SSN VARCHAR(11),Phoneno VARCHAR(15),Salary FLOAT);

INSERT INTO Employee (Fname, Lname, SSN, Phoneno, Salary)

VALUES

('Shubham', 'Thakur', '123-45-6789', '9876543210', 50000.00),

('Aman', 'Chopra', '234-56-7890', NULL, 45000.00),

('Aditya', 'Arpan', NULL, '8765432109', 55000.00),

('Naveen', 'Patnaik', '345-67-8901', NULL, NULL),

('Nishant', 'Jain', '456-78-9012', '7654321098', 60000.00);

Output:
The IS NULL Operator
Suppose we find the Fname and Lname of the Employee having no Super_ssn then the query will
be:

Query:

SELECT Fname, Lname FROM Employee WHERE SSN IS NULL; Output:

The IS NOT NULL Operator

Now if we find the Count of the number of Employees having SSNs.

Query:

SELECT COUNT(*) AS Count FROM Employee WHERE SSN IS NOT NULL;

Output:

Updating NULL Values in a Table

We can update the NULL values present in a table using the UPDATE statement in SQL. To do
so, we can use the IS NULL operator in the WHERE clause to select the rows with NULL values
and then we can set the new value using the SET keyword.

Let’s suppose that we want to update SSN in the row where it is NULL.

Query:

UPDATE Employee

SET SSN = '789-01-2345'

WHERE Fname = 'Aditya' AND Lname = 'Arpan';

select* from Employee; Output:

**************************************************************************

Complex Integrity Constraints in SQL

Integrity constraints in SQL are rules that help ensure the accuracy and reliability of data in the

database. They ensure that certain conditions are met when data is inserted, updated, or deleted.

While primary key, unique, and foreign key constraints are commonly discussed and used, SQL
allows for more complex constraints through the use of CHECK and custom triggers. Here are
some examples of complex integrity constraints:

1. Using CHECK Constraints

● Ensuring a range: You might want a column to only have values within a certain range.
Example:

CREATE TABLE Employees (

ID INT PRIMARY KEY,

Age INT CHECK >=

(Age
18 AND Age
<= 30)

);

Pattern matching: Ensurendata

a column
i matches a particular format.

Example:

CREATE TABLE Students (

IDINT PRIMARY KEY,

Email VARCHAR(255) CHECK (Email LIKE '%@%.%')

);

2. Comp
osite Primary and Foreign Keys

These are cases where the uniqueness or referential integrity constraint is applied over
more
than one
column.

Example
:
CREATE TABLE OrderDetails (

OrderID INT,

ProductID INT,

Quantity INT,
 PRIMARY KEY (OrderID, ProductID),

FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),

FOREIGN KEY (ProductID) REFERENCES Products(ProductID));

3. Using Stored Procedures

Sometimes, instead of direct data manipulation on tables, using stored procedures can help
maintain more complex integrity constraints by wrapping logic inside the procedure. For
instance, you could have a procedure that checks several conditions before inserting a record.

4. Triggers:

● Triggers are blocks of code that are automatically executed in response to specific events,
such as insertions, updates, or deletions in a table.
● They can be used to enforce complex business rules or constraints that cannot be easily
defined using standard SQL constraints.
● For example, you might use a trigger to update the last_updated timestamp whenever a
row in a table is modified.
● A trigger is a procedure which is automatically invoked by the DBMS in response to
changes to the database, and is specified by the database administrator (DBA).
● A database with a set of associated triggers is generally called an active database.
Parts of trigger
A triggers description contains three parts, which are as follows − Event

− An event is a change to the database which activates the trigger.

Condition − A query that is run when the trigger is activated is called as a condition.

Action −A procedure which is executed when the trigger is activated and its condition is true.

Use of trigger
Triggers may be used for any of the following reasons −

To implement any complex business rule, that cannot be implemented using integrity constraints.

Triggers will be used to audit the process. For example, to keep track of changes made to a table.

Trigger is used to perform automatic action when another concerned action takes place.

Types of triggers

The different types of triggers are explained below −

Statement level trigger − It is fired only once for DML statements irrespective of the number of
rows affected by the statement. Statement-level triggers are the default type of trigger.

Before-triggers − At the time of defining a trigger we can specify whether the trigger is to be
fired before a command like INSERT, DELETE, or UPDATE is executed or after the command
is executed. Before triggers are automatically used to check the validity of data before the action
is performed. For instance, we can use before trigger to prevent deletion of rows if deletion
should not be allowed in a given case.

After-triggers − It is used after the triggering action is completed. For example, if the trigger is
associated with the INSERT command then it is fired after the row is inserted into the table.

Create database trigger

To create a database trigger, we use the CREATE TRIGGER command. The details to be given at
the time of creating a trigger are as follows −

● Name of the trigger.

● Table to be associated with.
● When trigger is to be fired: before or after.
● Command that invokes the trigger- UPDATE, DELETE, or INSERT.
● Whether row-level triggers or not.
 ● Condition to filter rows.
● PL/SQL block is to be executed when trigger is fired.

The syntax to create database trigger is as follows −

CREATE TRIGGER trigger_name

[BEFORE | AFTER]
[INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW | FOR EACH COLUMN]
TRIGGER body;
Example:
Creating Before Insert Trigger Creating
the Orders Table:
CREATE TABLE Orders (OrderID INT PRIMARY KEY, OrderDate DATE,TotalAmount
DECIMAL(10, 2));

CREATE TRIGGER bi_order

BEFORE INSERT
ON
Orders
FOR EACH ROW
SET [Link] = [Link] * 5;
Insert into Orders values (101,’2023-08-10’,500);
Insert into Orders values (102,’2023-07-15’,400);
Insert into Orders values (103,’2023-05-03’,300);

select * from orders;

 Creating After Insert Trigger
Suppose we have created a table named "student_info" as follows:

CREATE TABLE student_info(stud_id int PRIMARY KEY,

stud_code varchar (15) DEFAULT NULL, stud_name
varchar (35) DEFAULT NULL, subj varchar (25)
DEFAULT NULL, marks int DEFAULT NULL, phone
varchar (15) DEFAULT NULL); insert some records into
this table
INSERT INTO student_info values(1,101,'Sai','DBMS',68,9912345670);
INSERT INTO student_info values(2,102,'Varun','OS',70,9945675671);
INSERT INTO student_info values(3,103,'Rahul','FLAT',90,9891075672);
INSERT INTO student_info values(4,104,'Bharath','FLAT',90,9768975673);
INSERT INTO student_info values(5,105,'Srinivas','FLAT',85,9345675674);
INSERT INTO student_info values(6,106,'Kumar','SE',90,9761234675);
INSERT INTO student_info values(7,107,'Sushanth','SE',83,9768123476);
INSERT INTO student_info values(8,108,'Jayanth','SE',85,9768923477);
SELECT * FROM STUDENT_INFO;
Again, we will create a new table named "student_detail" as follows:
CREATE TABLE student_detail (stud_id int PRIMARY KEY,
stud_code varchar (15) DEFAULT NULL, stud_name varchar (35)
DEFAULT NULL, subj varchar (25) DEFAULT NULL, marks int
DEFAULT NULL, phone varchar (15) DEFAULT NULL,
Lasinserted Time);
Next, we will use a CREATE TRIGGER statement to create an after_insert_details trigger on
the student_info table. This trigger will be fired after an insert operation is performed on the
table.
Create Trigger after_insert_details
AFTER INSERT
ON student_info
FOR EACH ROW
INSERT INTO student_detail VALUES (new. Stud_id, new. Stud_code, new.
stud_name, new. subj, new. marks, new. phone, CURTIME ());

Call the AFTER INSERT trigger:

We can use the following statements to invoke the above-created trigger:

INSERT INTO student_info values(9,109,'Sruthi','Java',67,9764567478);

The table that has been modified after the update query executes is student_detail. We can verify
it by using the SELECT statement as follows:

AFTER DELETE TRIGGER

create table student (id int primary key, name varchar (20), age int, Phone bigint);

insert into student Values(1,'Ravi',16,9912199121); insert into student

Values(2,'Seetha',18,9912199456); insert into student

Values(3,'Geetha',19,9912199789);

Select * from student;

AFTER DELETE TRIGGER

create table student (id int primary key, name varchar (20),age int, Phone bigint);

insert into student Values(1,'Ravi',16,9912199121); insert into student

Values(2,'Seetha',18,9912199456); insert into student

Values(3,'Geetha',19,9912199789);

Select * from student;

after delete on student for each row insert into student_backup
values ([Link],[Link],[Link],[Link]); delete from student where
id=1; select *from student_backup;

select *from student;

AFTER UPDATE TRIGGER

update student set name='Rishi' where id=2;

Select * from student;

id name age phone

2 Rishi 18 9912199456

3 Geetha 19 9912199789

create trigger after_update_student

after update on student for each row insert into student_backup

values([Link],[Link],[Link],[Link]); update student set
name='Seetha' where id=2; select *from student_backup;

select *from student;

Active databases.

Active Database

The active database supports the preceding application by moving the reactive behavior from the
application into the DBMS. Active databases are thus able to monitor and react to specific
circumstances of relevance to an application. An active database system must provide a
knowledge model i.e. description mechanism and an execution model for supporting this
behavior. And for this we need triggers which have predefined action when situations occur.
An active Database is a database consisting of a set of triggers. These databases are very difficult
to be maintained because of the complexity that arises in understanding the effect of these
triggers. In such a database, DBMS initially verifies whether the particular trigger specified in
the statement that modifies the database) is activated or not, prior to executing the statement.

If the trigger is active then DBMS executes the condition part and then executes the action part
only if the specified condition is evaluated to true. It is possible to activate more than one trigger
within a single statement.

In such a situation, DBMS processes each of the triggers randomly. The execution of an active
part of a trigger may either activate other triggers or the same trigger that Initialized this action.
Such types of triggers that activate themselves are called ‘recursive triggers’. The DBMS
executes such chains of triggers in some predefined manner but it affects the concept of
understanding.
Features of Active Database

● It possesses all the concepts of a conventional database i.e. data modeling facilities, query
language, etc.
● It supports all the functions of a traditional database like data definition, data
manipulation, storage management, etc.
● It supports the definition and management of ECA rules.
● It detects event occurrences.
● It must be able to evaluate conditions and execute actions.
● It means that it has to implement rule execution.
Advantages of Active Database

● It Enhances traditional database functionalities with powerful rule processing capabilities.

● Enable a uniform and centralized description of the business rules relevant to the
information system.
● Avoids redundancy of checking and repair operations.
● A suitable platform for building a large and efficient knowledge base and expert systems.

******************************************************************************
Schema Refinement:

Problems caused by redundancy:

Redundancy means having multiple copies of the same data in the database. This problem arises
when a database is not normalized. Suppose a table of student details attributes is: student Id,
student name, college name, college rank, and course opted.

Student_ID Name Contact College Course Rank

100 Himanshu 7300934851 GEU [Link] 1

101 Ankit 7900734858 GEU [Link] 1

102 Ayush 7300936759 GEU [Link] 1

103 Ravi 7300901556 GEU [Link] 1

As it can be observed that values of attribute college name, college rank, and course are being
repeated which can lead to problems. Problems caused due to redundancy are:

● Insertion anomaly
● Deletion anomaly
● Updation anomaly
Insertion Anomaly

If a student detail has to be inserted whose course is not being decided yet then insertion will not
be possible till the time course is decided for the student.

Student_ID Name Contact College Course Rank

100 Himanshu 7300934851 GEU 1

This problem happens when the insertion of a data record is not possible without adding some
additional unrelated data to the record.

Deletion Anomaly

If the details of students in this table are deleted, then the details of the college will also get
deleted which should not occur by common sense. This anomaly happens when the deletion of a
data record results in losing some unrelated information that was stored as part of the record that
was deleted from a table.

It is not possible to delete some information without losing some other information in the table as
well.

Updation Anomaly

Suppose the rank of the college changes then changes will have to be all over the database which
will be time-consuming and computationally costly.
Student_ID Name Contact College Course Rank

100 Himanshu 7300934851 GEU [Link] 1

101 Ankit 7900734858 GEU [Link] 1

102 Ayush 7300936759 GEU [Link] 1

103 Ravi 7300901556 GEU [Link] 1

All places should be updated, If updation does not occur at all places then the database will be in
an inconsistent state.
Redundancy in a database occurs when the same data is stored in multiple places. Redundancy
can cause various problems such as data inconsistencies, higher storage requirements, and slower
data retrieval.

Problems Caused Due to Redundancy Data

Inconsistency:

Redundancy can lead to data inconsistencies, where the same data is stored in multiple locations,
and changes to one copy of the data are not reflected in the other copies. This can result in
incorrect data being used in decision-making processes and can lead to errors and inconsistencies
in the data.

Storage Requirements:

Redundancy increases the storage requirements of a database. If the same data is stored in
multiple places, more storage space is required to store the data. This can lead to higher costs and
slower data retrieval.

Update Anomalies:

Redundancy can lead to update anomalies, where changes made to one copy of the data are not
reflected in the other copies. This can result in incorrect data being used in decision-making
processes and can lead to errors and inconsistencies in the data.

Performance Issues:

Redundancy can also lead to performance issues, as the database must spend more time updating
multiple copies of the same data. This can lead to slower data retrieval and slower overall
performance of the database.

Security Issues:
Redundancy can also create security issues, as multiple copies of the same data can be accessed
and manipulated by unauthorized users. This can lead to data breaches and compromise the
confidentiality, integrity, and availability of the data.

Maintenance Complexity:

Redundancy can increase the complexity of database maintenance, as multiple copies of the same
data must be updated and synchronized. This can make it more difficult to troubleshoot and
resolve issues and can require more time and resources to maintain the database.

Data Duplication:

Redundancy can lead to data duplication, where the same data is stored in multiple locations,
resulting in wasted storage space and increased maintenance complexity. This can also lead to
confusion and errors, as different copies of the data may have different values or be out of sync.

Data Integrity:

Redundancy can also compromise data integrity, as changes made to one copy of the data may
not be reflected in the other copies. This can result in inconsistencies and errors and can make it
difficult to ensure that the data is accurate and up-to-date.

Usability Issues:

Redundancy can also create usability issues, as users may have difficulty accessing the correct
version of the data or may be confused by inconsistencies and errors. This can lead to frustration
and decreased productivity, as users spend more time searching for the correct data or correcting
errors.

Disadvantages of Redundant Data

Increased storage requirements:

Redundant data takes up additional storage space within the database, which can increase costs
and slow down performance.

Inconsistency:

If the same data is stored in multiple places within the database, there is a risk that updates or
changes made to one copy of the data may not be reflected in other copies, leading to
inconsistency and potentially incorrect results.

Difficulty in maintenance:

With redundant data, it becomes more difficult to maintain the accuracy and consistency of the

data. It requires more effort and resources to ensure that all copies of the data are updated

correctly. Increased risk of errors:

When data is redundant, there is a greater risk of errors in the database. For example, if the same
data is stored in multiple tables, there is a risk of inconsistencies between the tables.

Reduced flexibility:

Redundancy can reduce the flexibility of the database. For example, if a change needs to be made
to a particular piece of data, it may need to be updated in multiple places, which can be
timeconsuming and error-prone.

******************************************************************************

Decompositions

● When a relation in the relational model is not in appropriate normal form then the
decomposition of a relation is required.

● In a database, it breaks the table into multiple tables.

 ● If the relation has no proper decomposition, then it may lead to problems like loss of
information.

● Decomposition is used to eliminate some of the problems of bad design like anomalies,
inconsistencies, and redundancy.

Types of Decomposition

Decomposition is of two major types in DBMS:

1. Lossless Decomposition

2. Dependency Preservation

Lossless Decomposition

● If the information is not lost from the relation that is decomposed, then the decomposition
will be lossless.

● The lossless decomposition guarantees that the join of relations will result in the same
relation as it was decomposed.

● The relation is said to be lossless decomposition if natural joins of all the decomposition
give the original relation.

Example: EMPLOYEE_DEPARTMENT table:

60 Jack 40 Noida 678 Testing

The above relation is decomposed into two relations EMPLOYEE and DEPARTMENT
EMPLOYEE table:
EMP_ID EMP_NAME EMP_AGE EMP_CITY

22 Denim 28 Mumbai

33 Alina 25 Delhi

46 Stephan 30 Bangalore

52 Katherine 36 Mumbai

60 Jack 40 Noida
DEPARTMENT table

DEPT_ID EMP_ID DEPT_NAME

827 22 Sales

438 33 Marketing

869 46 Finance

575 52 Production

678 60 Testing
Now, when these two relations are joined on the common column "EMP_ID", then the resultant
relation will look like:

Employee Department

EMP_ID EMP_NAME EMP_AGE EMP_CITY DEPT_ID DEPT_NAME

22 Denim 28 Mumbai 827 Sales

33 Alina 25 Delhi 438 Marketing

46 Stephan 30 Bangalore 869 Finance

52 Katherine 36 Mumbai 575 Production

60 Jack 40 Noida 678 Testing

Hence, the decomposition is Lossless join decomposition.

Dependency Preserving

● It is an important constraint of the database.

 ● In the dependency preservation, at least one decomposed table must satisfy every
dependency.

● If a relation R is decomposed into relation R1 and R2, then the dependencies of R either
must be a part of R1 or R2 or must be derivable from the combination of functional
dependencies of R1 and R2.

● For example, suppose there is a relation R (A, B, C, D) with a functional dependency set
(A->BC). The relational R is decomposed into R1(ABC) and R2(AD) which is
dependency preserving because FD A->BC is a part of relation R1(ABC).

***************************************************************************
Functional Dependency in DBMS

What is Functional Dependency in DBMS?

Relational database is a collection of data stored in rows and columns. Columns represent the
characteristic of data while each row in a table represents a set of related data, and every row in

the table has the same structure. The row is sometimes referred to as a tuple in DBMS.

Example: Employee table below. It contains attributes as column values, namely

Employee_Id,Employee_Name,Employee_Department,Salary
Employee Table
Employee_Id Employee_Name Employee_Department Salary

1 Ryan Mechanical 5,000

2 Justin Biotechnology 5,000

3 Andrew Computer Science 8,000

4 Felix Human Resource 10,000

● Functional Dependency in DBMS, as the name suggests, is the relationship between
attributes(characteristics) of a table related to each other.
 ● A relation consisting of functional dependencies always follows a set of rules called RAT
rules. They were proposed by William Armstrong in 1974.

● It helps in maintaining the quality of data in the database, and the core concepts behind
database normalization are based on functional dependencies.

How to Denote a Functional Dependency in DBMS?

A functional dependency is denoted by an arrow “→”. The functional dependency of A on B is

represented by A → B.

Consider a relation with four attributes A, B, C and D,

R (ABCD)

1. A → BCD

2. B → CD

● For the first functional dependency A → BCD, attributes B, C and D are functionally
dependent on attribute A.

● Function dependency B → CD has two attributes C and D functionally depending upon

attribute B.

Sometimes everything on the left side of functional dependency is also referred to as a

determinant set, while everything on the right side is referred to as dependent attributes.

● Functional dependency can also be represented diagrammatically like this,

 ● Pointing arrows determines the dependent attribute and the origin of the arrow determines
the determinant set.

Types of Functional Dependencies in DBMS

1. Trivial functional dependency

2. Non-Trivial functional dependency

3. Multivalued functional dependency

4. Transitive functional dependency

Trivial Functional Dependency in DBMS

● In Trivial functional dependency, a dependent is always a subset of the determinant. In

other words,
a functional dependency is called trivial if the attributes on the right side
are the subset of the attributes on the left side of the functional
y. dependenc

● X → Y is called a trivial functional dependency if Y is the subset of X.

● For example, considerEmployee

the table below.

Employee_IdName Age

1 Zayn 24

2 Phobe 34

3 Hikki 26

4 David 29

● Here, {Employee_Id, Name} → { Name } is a Trivial functional dependency, since the

dependent Name is the subset of the determinant { Employee_Id, Name }.

● {Employee_Id} → { Employee_Id }, { Name } → { Name } and { Age } → { Age } are

also Trivial.
 ● Here, {Employee_Id} → { Name} is a non-trivial functional dependency because
Name(dependent) is not a subset of Employee_Id(determinant).

● Similarly, {Employee_Id, Name} → { Age } is also a non-trivial functional dependency.

Multivalued Functional Dependency in DBMS
● In Multivalued functional dependency, attributes in the dependent set are not dependent on
each other.

● For example, X → { Y, Z }, if there exists no functional dependency between Y and Z,

then it is called Multivalued functional dependency.

● For example, consider the Employee table below.

 5 Phobe LM 21

● Here, { Employee_Id → Department } and { Department → Street Number } holds true.

 Hence, according to the axiom of transitivity, { Employee_Id → Street Number } is a
valid functional dependency.

Armstrong’s Axioms/Properties of Functional Dependency in DBMS

William Armstrong in 1974 suggested a few rules related to functional dependency. They are
called RAT rules.

1. Reflexivity: If A is a set of attributes and B is a subset of A, then the functional

dependency A → B holds true.

0 For example, { Employee_Id, Name } → Name is valid.

2. Augmentation: If a functional dependency A → B holds true, then appending any

number of the attribute to both sides of dependency doesn't affect the dependency. It
remains true.

0 For example, X → Y holds true then, ZX → ZY also holds true.

○ For example, if { Employee_Id, Name } → { Name } holds true then,

{ Employee_Id, Name, Age } → { Name, Age }

3. Transitivity: If two functional dependencies X → Y and Y → Z hold true, then X → Z

also holds true by the rule of Transitivity.

0 For example, if { Employee_Id } → { Name } holds true and { Name } → {

Department } holds true, then { Employee_Id } → { Department } also holds true.

Advantages of Functional Dependency in DBMS

Let's discuss some of the advantages of Functional dependency,

● It is used to maintain the quality of data in the database.

● It expresses the facts about the database design.

● It helps in clearly defining the meanings and constraints of databases.

● It helps to identify bad designs.

 ● Functional Dependency removes data redundancy where the same values should not be
repeated at multiple locations in the same database table.

● The process of Normalization starts with identifying the candidate keys in the relation.
Without functional dependency, it's impossible to find candidate keys and normalize the
database.

******************************************************************************

Normalization

A large database defined as a single relation may result in data duplication. This repetition of data
may result in:

○ Making relations very large.

○ It isn't easy to maintain and update data as it would involve searching many records in
relation.

○ Wastage and poor utilization of disk space and resources.

○ The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with redundant data
into smaller, simpler, and well-structured relations that satisfy desirable properties.
Normalization is a process of decomposing the relations into relations with fewer attributes.

What is Normalization?

○ Normalization is the process of organizing the data in the database.

○ Normalization is used to minimize the redundancy from a relation or set of relations. It is
also used to eliminate undesirable characteristics like Insertion, Update, and Deletion
Anomalies.

○ Normalization divides the larger table into smaller and links them using relationships.

○ The normal form is used to reduce redundancy from the database table.
Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate
anomalies leads to data redundancy and can cause data integrity and other problems as the
database grows. Normalization consists of a series of guidelines that helps to guide you in
creating a good database structure.

Data modification anomalies can be categorized into three types:

○ Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a
relationship due to lack of data.

○ Deletion Anomaly: The delete anomaly refers to the situation where the deletion of data
results in the unintended loss of some other important data.

○ Updation Anomaly: The update anomaly is when an update of a single data value requires
multiple rows of data to be updated.

Types of Normal Forms:

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

4. Boyce-Codd Normal Form (BCNF)

5. Fourth Normal Form (4NF)

6. Fifth Normal Form (5NF)

First Normal Form (1NF)

The First Normal Form (1NF) is the first step in the normalization process of organizing data
within a relational database to reduce redundancy and improve data integrity. A relation (table) is
said to be in 1NF if it adheres to the following rules:

1. Atomic Values:

● Each attribute (column) contains only atomic (indivisible) values. This means values in
each column are indivisible units and there should be no sets, arrays, or lists.

● For example, a column called "Phone Numbers" shouldn't contain multiple phone
numbers for a single record. Instead, we'd typically break it into additional rows or
another related table.

2. Primary Key:

● Each table should have a primary key that uniquely identifies each row. This ensures that
each row in the table can be uniquely identified.

3. No Duplicate Rows:

● There shouldn’t be any duplicate rows in the table. This is often ensured by the use of the
primary key.

4. Order Doesn't Matter:

● The order in which data is stored doesn't matter in the context of 1NF (or any of the
normal forms). Relational databases don't guarantee an order for rows in a table unless
explicitly sorted.

5. Single Valued Attributes:

● Columns should not contain multiple values of the same type. For example, a column
"Skills" shouldn't contain a list like "Java, Python, C++" for a single record. Instead,
these skills should be split across multiple rows or placed in a separate related table.

Example for First Normal Form (1NF)

Consider a table with a structure:

The table above is not in 1NF because the "Subjects" column contains multiple
values.

To transform it to 1NF:

Now, each combination of "Student_ID" and "Subject" is unique, and every attribute contains
only
atomic values, ensuring the table is in 1NF.
Achieving 1NF is a fundamental step in database normalization, laying the foundation for further
normalization processes to eliminate redundancy and ensure data integrity.

Second Normal Form (2NF)

The Second Normal Form (2NF) is the next stage in the normalization process after the First
Normal Form (1NF). A relation is in 2NF if:

1. It is already in 1NF:
 ● This means the relation contains only atomic values, there are no duplicate rows, and it
has a primary key.

2. No Partial Dependencies:

● All non-key attributes (i.e., columns that aren't part of the primary key) should be
functionally dependent on the *entire* primary key. This rule is especially relevant for
tables with composite primary keys (i.e., primary keys made up of more than one
column).
● In simpler terms, no column should depend on just a part of the composite primary key.

Example for Second Normal Form

Let's consider a table that keeps track of the courses that students are enrolled in, with the faculty
who teach those courses:

Here, a combination of `Student_ID` and `Course_ID` can be considered as a primary key

because a student can be enrolled in multiple courses, and each course might be taken by many
students.

However, you'll notice that `Course_Name` and `Faculty` depend only on `Course_ID` and not
on the combination of `Student_ID` and `Course_ID`. This is a partial dependency.

To bring the table to 2NF, we need to remove the partial dependencies:

Now, the `StudentCourse` table relates students to courses, and the `Course` table holds
information about each course. There are no more partial dependencies.

It's worth noting that while 2NF does improve the structure of our database by reducing
redundancy and eliminating partial dependencies, it might not eliminate all anomalies or
redundancy. Further normalization forms (like 3NF and BCNF) address additional types of
dependencies and potential issues.

Third Normal Form (3NF)

The Third Normal Form (3NF) is a further step in the normalization process after achieving
Second Normal Form (2NF). A relation is considered to be in 3NF if:

1. It is already in 2NF:

● This means the relation has no partial dependencies of non-key attributes on the primary
key.

2. No Transitive Dependencies:

● All non-key attributes are functionally dependent only on the primary key and not on any
other non-key attributes. If there is a dependency of one non-key attribute on another
 nonkey attribute, it is called a transitive dependency, and such a dependency violates
3NF.
● Simply put, in 3NF, non-key attributes should not depend on other non-key attributes;
they should only depend on the primary key.

Example for Third Normal Form (3NF)

Consider a table storing information about products sold by different vendors:

In the table above, `Product_ID` is the primary key. We can see that `Vendor_Address` depends
on `Vendor_Name` rather than `Product_ID`, which represents a transitive dependency.

To convert this table to 3NF, we can split it into two tables:

Now, the `Product` table has `Product_ID` as the primary key, and all attributes in this table
depend only on the primary key. The `Vendor` table has `Vendor_Name` as its primary key, and
the address in this table depends only on the vendor name.

This normalization eliminates the transitive dependency and reduces redundancy. If we need to
change a vendor's address, we now only have to make the change in one place in the `Vendor`
table.

To further refine the database structure, we might proceed to other normalization forms like
BCNF, but 3NF is often sufficient for many practical applications and strikes a good balance
between minimizing redundancy and maintaining a manageable schema.

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is an advanced step in the normalization process, and it's a
stronger version of the Third Normal Form (3NF). In fact, every relation in BCNF is also in 3NF,
but the converse isn't necessarily true. BCNF was introduced to handle certain anomalies that
3NF does not deal with.

A relation is in BCNF if:

1. It is already in 3NF.

2. For every non-trivial functional dependency

X→Y, X is a superkey. This essentially means that the only determinants in the relation are
superkeys.

Here, "non-trivial" means that Y is not a subset of X, and a "superkey" is a set of attributes that
functionally determines all other attributes in the relation.

Example for Boyce-Codd Normal Form (BCNF)

Consider a university scenario where professors supervise student theses in various topics. Now,
let's assume each professor can only supervise one topic, but multiple professors can supervise
the same topic.
Here:

● Each professor is associated with exactly one topic.

● The primary key is {Student, Professor}, meaning a professor can supervise multiple
students, but each student has one thesis and thus one topic.
● There's a functional dependency {Professor} → {Topic} since each professor supervises
only one topic.

Now, observe that {Professor} is not a superkey (because the primary key is a combination of
Student and Professor), but it determines another attribute in the table (Topic). This violates the
definition of BCNF.

To bring this table into BCNF, we can decompose it into two tables:
This decomposition eliminates the partial dependency and ensures that the only determinants are
superkeys, making the structure adhere to BCNF.

In practice, BCNF is a highly normalized form, and while it can minimize redundancy, it can also
increase the complexity of the database design. Designers often have to make trade-offs between
achieving higher normal forms and maintaining simplicity, depending on the specific use case
and requirements of the system.

Fourth Normal Form(4NF):

The Fourth Normal Form (4NF) is an advanced level in the normalization process, aiming to
handle certain types of anomalies which aren't addressed by the Third Normal Form (3NF).
Specifically, 4NF addresses multi-valued dependencies.

A relation is in 4NF if:

1. It is already in 3NF.

2. No multi-valued dependencies exist. A multi-valued dependency occurs when an attribute

depends on another attribute but not on the primary key.

To clarify, consider a relation R with attributes X, Y, and Z. We say that there is a multi-valued
dependency from X to Y, denoted

X Y, if for a single value of X, there are multiple values of Y associated with it, independent of
Z.

Example for Fourth Normal Form (4NF)

Let's illustrate 4NF with a scenario involving students, their hobbies, and the courses they've
taken:
In the table:
● For student `S1`, there are two hobbies (`Painting` and `Hiking`) and two courses (`Math`
and `Physics`), resulting in a combination of every hobby with every course.

● This design suggests a multi-valued dependency between `Student_ID` and `Hobby`, and
also between `Student_ID` and `Course`.

To bring the table to 4NF, we can decompose it into two separate tables:
With this separation:

● The `StudentHobbies` table lists the hobbies of each student.

● The `StudentCourses` table lists the courses taken by each student.

There are no more multi-valued dependencies. This setup not only reduces redundancy but also
prevents the possibility of certain types of inconsistencies and anomalies in the data.

For most practical applications, normalization up to 3NF or BCNF is often adequate. However,
when specific types of redundancy or data anomalies are a concern, proceeding to 4NF or even
5NF can be beneficial.

Fifth normal form (5NF)

The Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), is a further
step in the normalization process. It aims to address redundancy arising from certain types of join
dependencies that aren't covered by earlier normal forms.

A relation is in 5NF or PJNF if:

1. It is already in BCNF.

2. Every non-trivial join dependency in the relation is implied by the candidate keys.

A join dependency occurs in a relation R when it is always possible to reconstruct R by joining

multiple projections of R. A join dependency is represented as {R1, R2, ..., Rn} R, which
means that when R is decomposed into R1, R2, ..., Rn, the natural join of these projections
results in the original relation R.

The join dependency is non-trivial if none of the projections Ri is equal to R.

Example for Fifth Normal Form (5NF)

Consider a relation involving suppliers, parts, and projects:

Assume the following constraints for our example:

1. Every part supplied for a project is supplied by all suppliers supplying any part for that
project.
2. Every part supplied by a supplier is supplied by that supplier for all projects to which that
supplier supplies any part.

Given the above constraints, the following join dependencies exist on the table:

● {Supplier, Part} SupplierPartsProjects

● {Supplier, Project} SupplierPartsProjects
● {Part, Project} SupplierPartsProjects
To decompose the relation into 5NF: