Assignment No-02

01. What is Normalization?

Normalization is a database design technique that organizes tables in a way that reduces
redundancy and dependency of data.
It divides large tables into smaller tables and links them using relationships. The main goal
of normalization is to decompose
complex relations into simpler ones to ensure that the database is efficient and easy to
manage.

Normalization involves several stages or normal forms:

- First Normal Form (1NF): A relation is in 1NF if it contains only atomic (indivisible) values
and each column contains values of a single type.
- Second Normal Form (2NF): A relation is in 2NF if it is in 1NF and all non-key attributes
are fully dependent on the primary key.
- Third Normal Form (3NF): A relation is in 3NF if it is in 2NF and all the attributes are only
dependent on the primary key.

02. What is anomalies? Explain types of anomalies in DBL.

An insert
anomaly
Anomalies in databases are issues that arise when the database is not properly normalized. occurs when
There are three main types of anomalies: certain
instructor to a new course without students information
- Insertion Anomaly: This occurs when certain data cannot be inserted into the database cannot be
without the presence of other data. For example, inserted into
if a student needs to be added to a course database but there is no existing entry for that the database
because it
student in the student table, it causes an insertion anomaly.
depends on
other data.
- Deletion Anomaly: This occurs when deleting data results in the unintended loss of This typically
additional data. For example, if deleting a student’s happens
course enrollment also removes the student's information entirely, it leads to a deletion when the
anomaly. table
schema
- Update Anomaly: This occurs when multiple instances of the same data are not updated requires data
consistently. For example, if a student's address is to be entered
stored in multiple tables, and only one instance is updated, it results in an update anomaly.
in a specific
way.

A delete anomaly occurs when the deletion of data leads to the unintended loss of other valuable
data. This happens when related pieces of data are stored in the same table.

An update anomaly occurs when a change to data in one place requires changes in multiple
places to maintain consistency. If all places are not updated properly, the data becomes
m_ssn change
inconsistent.
03. Explain the 1NF, 2NF, 3NF with example.

First Normal Form (1NF):

- Definition: A table is in 1NF if all its columns contain atomic, indivisible values, and each
column contains values of a single type.
- Example: Consider a student table with columns for student ID, name, and enrolled
courses:

StudentID | Name | Courses

--------------------------------
1 | Alice | Math, Science
2 | Bob | English

In 1NF, the table should be broken down so that each value is atomic:

StudentID | Name | Course

---------------------------
1 | Alice | Math
1 | Alice | Science
2 | Bob | English

Second Normal Form (2NF):

- Definition: A table is in 2NF if it is in 1NF and all non-key attributes are fully functional
dependent on the primary key.
- Example: Consider the above table in 1NF. To achieve 2NF, we need to ensure that all non-
key attributes depend only on the primary key:

Students Table:
StudentID | Name
----------------
1 | Alice
2 | Bob

Courses Table:
StudentID | Course
-------------------
1 | Math
1 | Science
2 | English

Third Normal Form (3NF):

- Definition: A table is in 3NF if it is in 2NF and all the attributes are only dependent on the
primary key.
- Example: Consider a table that includes student ID, course, and instructor. To achieve 3NF,
we need to remove transitive dependencies:

Students Table:
StudentID | Name
----------------
1 | Alice
2 | Bob

Courses Table:
Course | Instructor
----------------------
Math | Dr. Smith
Science | Dr. Johnson
English | Dr. Clark

Enrollments Table:
StudentID | Course
-------------------
1 | Math
1 | Science
2 | English

04. Explain Informal Design guidelines for relation schema design. Examine the
Cursor with PGM.

Informal design guidelines for relational schema design include:

1. Clear Attribute Semantics: Ensure each attribute has a clear meaning and purpose.
Attributes should be understandable and should represent only one concept.
2. Reduce Redundancy: Minimize redundant data storage to avoid anomalies and ensure
efficient updates. Use normalization to split tables as necessary.
3. Avoid NULLs: Design tables to minimize the use of NULL values, as they can lead to
ambiguous interpretations and require special handling.
4. Ensure Referential Integrity: Use foreign keys to maintain consistency between related
tables. This ensures that relationships between tables are valid and consistent.

Cursor in SQL:
A cursor is a database object that allows row-by-row processing of the result set. Cursors
are useful when you need to perform operations on each row individually.

Example of a Cursor in PL/SQL:

```sql
DECLARE
CURSOR student_cursor IS SELECT student_name FROM students;
student_name VARCHAR2(50);
BEGIN
OPEN student_cursor;
LOOP
FETCH student_cursor INTO student_name;
EXIT WHEN student_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(student_name);
END LOOP;
CLOSE student_cursor;
END;
```
This PL/SQL block declares a cursor named `student_cursor` that retrieves the
`student_name` from the `students` table. The cursor is opened, and a loop fetches each row,
printing the `student_name` until all rows are processed.

05. Demonstrate working of assertion and triggers in SQL. Explain proper

examples.

Assertion:
An assertion is a condition specified in SQL that must always be true for the database.
However, not all database systems support assertions directly.
Instead, similar constraints can be implemented using triggers or check constraints.

Trigger:
A trigger is a set of SQL statements that automatically execute when a specified event occurs
in the database, such as an insert, update, or delete operation.

Example of a Trigger:
```sql
CREATE TABLE audit_log (
log_id INT PRIMARY KEY,
student_id INT,
action VARCHAR(50),
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_after_insert

AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO audit_log (student_id, action)
VALUES (:NEW.student_id, 'INSERT');
END;
```
This trigger, `trg_after_insert`, is defined on the `students` table. It automatically inserts a
record into the `audit_log` table whenever a new row is inserted into the `students` table,
logging the `student_id` and action performed.

06. Demonstrate the Debit/Credit transaction with a transaction diagram &

explain state of transaction.

A debit/credit transaction involves transferring funds between two accounts, ensuring that
the total amount remains consistent.

Example of Debit/Credit Transaction:

1. Begin Transaction:
```sql
BEGIN;
```

2. Debit Account A:
```sql
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';
```

3. Credit Account B:
```sql
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';
```

4. Commit Transaction:
```sql
COMMIT;
```

State of Transaction:
- Active: The transaction is in progress and operations are being performed.
- Partially Committed: All operations are complete, but changes are not yet saved to the
database.
- Committed: The transaction is successfully completed, and all changes are permanently
saved.
- Failed: The transaction encountered an error, and the operations cannot proceed.
- Aborted: The transaction is rolled back, and all changes made during the transaction are
undone.

Transaction Diagram:
```plaintext
+-------------------------+
| Start Transaction |
+-------------------------+
|
V
+-------------------------+
| Debit Account A |
+-------------------------+
|
V
+-------------------------+
| Credit Account B |
+-------------------------+
|
V
+-------------------------+
| Commit Transaction |
+-------------------------+
|
V
+-------------------------+
| End Transaction |
+-------------------------+
```
The diagram represents the flow of a debit/credit transaction, showing the sequence of
operations from starting the transaction to committing and ending it.