1.

Demonstrate working of assertion and action triggers in database with an example

in a database, assertions and triggers are important mechanisms used to maintain data
integrity and automate operations.
1. Assertion
• An assertion is a condition that must always be true for the database to remain in a
consistent state.
• It is used to enforce constraints that apply across multiple tables or rows.
• Assertions are checked automatically by the DBMS whenever any update is made.
• Not all databases support assertions directly (e.g., MySQL doesn’t), but they are part
of standard SQL.
Example:
An assertion can ensure that the total salary of all employees should not exceed ₹10,00,000.
This helps maintain business rules that go beyond a single table or row.

2. Action Triggers
• A trigger is a procedural code that is automatically executed in response to specific
events on a table.
• These events can be INSERT, UPDATE, or DELETE.
• Triggers are used to enforce complex rules, automate tasks like logging, auditing, or
maintaining derived data.
Example:
When a new employee is added to the employees table, a trigger can insert a message into
an audit_log table saying “New employee added”.

[Link] the following relation schema

Works(Pname,Cname,salary)
Lives(Pname,Street,City)
located_in (Cname, city)
Manager(Pname,Mgrname)
Write the SQL queries for the following
i) Find the names of all persons who live in the city Bangalore.
ii) Retrieve the names of all person of "Infosys" whose salary is between Rs .50000
iii)Find the names of all persons who lives and work in the same city
iv)List the names of the people who work for “Tech M” along with the cities they live in.
v)Find the average salary of “Infosys” persons

i) Find the names of all persons who live in the city Bangalore

ii) Retrieve the names of all person of "Infosys" whose salary is between Rs .50000

iii) Find the names of all persons who live and work in the same city
iv) List the names of the people who work for “Tech M” along with the cities they live in

v) Find the average salary of “Infosys” persons

[Link] in detail about Normalization? Explain the 1NF,2NF,3NF with examples

Normalization is the process of organizing data in a database to reduce data redundancy and
improve data integrity. It breaks a large table into smaller, well-structured tables without
losing data.
Types of Normal Forms

1st Normal Form (1NF):

A relation is in 1NF if:
• All attributes contain only atomic (indivisible) values
• There are no repeating groups

Example:
Unnormalized Table:

StudentID Name Subjects

101 Ravi Math,Science

102 Priya English

 • The Subjects field has multiple values → violates 1NF

After 1NF:

StudentID Name Subject

101 Ravi Math

101 Ravi Science

102 Priya English

2nd Normal Form (2NF):

A relation is in 2NF if:
• It is already in 1NF
• No partial dependency (i.e., non-prime attributes depend on the full primary key)

Example:
Consider a table where the composite key is (StudentID, Subject):

StudentID Subject StudentName

101 Math Ravi

101 Science Ravi

102 English Priya

• Here, StudentName depends only on StudentID, not on the whole key → violates
2NF
After 2NF:
Student Table:

StudentID StudentName

101 Ravi

102 Priya

StudentSubjects Table:
StudentID Subject

101 Math

101 Science

102 English

3rd Normal Form (3NF):

A relation is in 3NF if:
• It is already in 2NF
• It has no transitive dependency (i.e., non-prime attribute should not depend on
another non-prime attribute)

Example:

EmpID EmpName DeptID DeptName

1 Anil D1 Sales

2 Sunil D2 HR

• DeptName depends on DeptID, not directly on EmpID → violates 3NF

After 3NF:
Employee Table:

EmpID EmpName DeptID

1 Anil D1

2 Sunil D2

Department Table:

DeptID DeptName

D1 Sales

D2 HR
4. Consider the following COMPANY database

EMP(Name,SSN,Salary,SuperSSN,Dno)

DEPT(DNum,Dname,MgrSSN,Dno)

DEPT_LOC(Dnum,Dlocation)

DEPENDENT(ESSN,Dep_name,Sex)

WORKS_ON(ESSN,Pno,Hours)

PROJECT(Pname,Pnumber,Plocation,Dnum)

Write the SQL queries for the following

i. Retrieve the name of the employee who works with same department as ravi

ii. Retrieve the number of dependents for an employee “Ravi”

iii. Retrieve the name of the managers working in location “DELHI”who has no female
dependents

iv. List female employees from Dno=20 earning more than 50000

List “CSE” department details

i. Retrieve the name of the employee who works in the same department as
'Ravi'

ii. Retrieve the number of dependents for an employee “Ravi”

iii. Retrieve the name of the managers working in location “DELHI” who have
no female dependents

iv. List female employees from Dno = 20 earning more than 50000

v. List “CSE” department details

5. Explain the concept of views in SQL with suitable example?

1. Definition:
A view is a virtual table in SQL that is derived from one or more base tables using a SELECT
query.
It does not store data physically but displays data stored in other tables.

2. Purpose of Views:
• To simplify complex queries
• To provide data security by restricting access to specific rows or columns
• To present customized data to different users
• To ensure logical data independenc

3. Characteristics of Views:
• A view is not stored physically; only its definition is stored.
• Views can be used like regular tables in SELECT statements.
• Views can sometimes be used to INSERT, UPDATE, or DELETE records (only if the view
is updatable).
4. Syntax to Create a View:

Example:
Employee Table:
EmpID Name Dept Salary

101 Ravi IT 60000

102 Priya HR 50000

103 Amit IT 55000

6. Summarize the informal guidelines related with Relational Database Design

Relational database design must ensure data integrity, eliminate redundancy, and avoid
anomalies.
To achieve this, certain informal design guidelines are followed before applying formal
normalization techniques.

1. Avoid Redundant Information:

• Redundant data wastes space and can lead to inconsistencies.
• Example: Storing employee department name repeatedly in every record instead of
referencing a separate department table.

2. Avoid Insertion, Deletion, and Update Anomalies:

• Insertion anomaly: Can’t insert data unless other unrelated data is also available.
• Deletion anomaly: Deleting a record causes loss of valuable data.
• Update anomaly: Changing data in one place requires multiple updates elsewhere.
Good design avoids these anomalies by properly structuring the schema.

3. Use of Null Values Should Be Minimized:

• Excessive nulls indicate poor design.
• Nulls make queries complex and may lead to misinterpretation.
Design separate tables or optional relations instead of using too many null fields.

4. Ensure Logical Attribute Grouping:

 • Attributes that describe a single concept should be grouped in the same relation.
• Avoid mixing unrelated information in the same table.
Example: Don’t mix employee data and project data in one table.

5. Use Meaningful Primary Keys:

• Choose primary keys that are stable and uniquely identify records.
• Avoid using composite keys unless necessary.

6. Avoid Repeating Groups:

• Tables should not have repeating columns (like Subject1, Subject2).
• Each repeating value should be placed in a separate row — this aligns with 1NF.
•

7. Informal Guidelines for Database Schema Design

1. Introduction:
Informal guidelines are general rules followed during database design to prevent anomalies
and ensure data integrity before applying formal normalization techniques.

2. Guidelines with Examples:

a) Avoid Redundant Information:
• Repeated data causes inconsistencies and waste of space.
• Example: Storing department name in every employee record.

b) Avoid Insertion, Update, and Deletion Anomalies:

• Update anomaly: Changing department name in many records.
• Insertion anomaly: Cannot insert a new employee without department info.
• Deletion anomaly: Deleting last employee deletes department record.

c) Avoid Null Values:

 • Too many nulls indicate poor structure.
• Example: Optional fields like project info should be stored in a separate table.

d) Use Logical Grouping of Attributes:

• Group related attributes in one table.
• Example: Employee details should not be mixed with project details in the same
table.

e) Choose Stable and Meaningful Primary Keys:

• Avoid attributes that may change.
• Example: Use EmpID instead of mobile number as a primary key.

3. Conclusion:
Following informal guidelines results in a well-structured, anomaly-free, and efficient
relational schema, preparing it for formal normalization.

[Link] BCNF with suitable example?

1. Definition:
A relation is in Boyce-Codd Normal Form (BCNF) if:
For every non-trivial functional dependency X → Y, X is a super key.
It is a stronger version of 3NF.

2. Need for BCNF:

Even if a relation is in 3NF, it may still have anomalies if a non-super key functionally
determines other attributes.

3. Example:
Relation:
Course(Course_ID, Instructor, Room)
Functional Dependencies:
 • Course_ID → Instructor

• Room → Instructor (Room is not a super key)

4. Anomaly:
If the same room always has the same instructor, but that instructor teaches multiple
courses, we may face update anomalies.

5. Decomposition to BCNF:
Split the table into:
• Room_Info(Room, Instructor)
• Course_Info(Course_ID, Room)
Now, both tables follow BCNF rules — all functional dependencies have a super key on the
left side.

6. Conclusion:
BCNF ensures a more strict and clean database design by removing dependencies that can
cause anomalies. It results in high-quality schema design beyond 3NF.

9. With neat state transition diagram explain different states for transaction execution.
A transaction is a sequence of operations performed as a single logical unit of work in a
database.
To ensure data consistency and integrity, every transaction passes through various states
during its execution.

Transaction States:
1. Active
2. Partially Committed
3. Committed
4. Failed
5. Aborted
1. Active State:
• The transaction starts executing.
• Operations like read, write, or calculations are performed.
• It remains active until the last statement is executed.

2. Partially Committed State:

• After the final statement of the transaction is executed.
• All changes are temporarily stored but not yet permanently saved.

3. Committed State:
• All the changes made by the transaction are permanently saved in the database.
• The transaction is successful and complete.

4. Failed State:
• If a system crash or error occurs during execution or before commit.
• Transaction fails and cannot proceed to commit.

5. Aborted State:
• The transaction is rolled back (undo all changes).
• System restores database to the original state.
• A new transaction may be started (optional retry).
10. With syntax and suitable example, explain the SQL commands INSERT, UPDATE, ALTER
AND DELETE.
1. INSERT
Used to add new rows (records) into a table.
Syntax:

2. UPDATE
Used to modify existing records in a table.
Syntax:
3. ALTER
Used to change the structure of a table, such as adding or deleting columns, or modifying
column data types.
Syntax:

Example:
4. DELETE
Used to remove existing rows from a table.
Syntax:

11. What are triggers? Explain with syntax and suitable example
A trigger is a special kind of stored procedure that is automatically invoked or executed
when a specified event occurs in a database table or view. Triggers are mainly used to
maintain the integrity of data, enforce business rules, and automate system tasks like
auditing or logging.
Characteristics of Triggers:
• Executed automatically on INSERT, UPDATE, or DELETE events.
• Cannot be called or executed manually.
• Can be defined to execute BEFORE or AFTER the event.
• Operates on row-level or statement-level, depending on the DBMS.
 BEFORE or AFTER: When the trigger should fire relative to the triggering event.
INSERT | UPDATE | DELETE: Event that fires the trigger.
FOR EACH ROW: Executes once for each affected row.

Explanation:
• The trigger log_employee_insert is set to activate AFTER a new row is inserted into
the employees table.
• It uses the NEW keyword to access the values of the newly inserted row.
 • A record is then inserted into employee_log to keep track of the change along with
the current timestamp using NOW().

Benefits of Using Triggers:

• Enforce complex business rules automatically.
• Maintain consistency and integrity without manual intervention.
• Help in data auditing and automatic logging.
• Reduce repetitive code in applications.

12. With syntax, explain the schema change commands (DROP and ALTER) in SQL.
In SQL, schema change commands are used to modify the structure of a database, such as
adding, deleting, or altering tables and columns.
1. ALTER Command
The ALTER command is used to modify the structure of an existing table. It can be used to:
• Add a new column
• Modify an existing column
• Rename a column or table
• Drop a column
2. DROP Command
The DROP command is used to completely delete a database object such as a table, view, or
database. Once dropped, the data and structure are permanently removed.
 13. Difference between NoSQL Graph database and Neo4j with an example

Feature NoSQL Graph Database Neo4j (as a Specific Graph DB)

A category of NoSQL databases using A specific, widely-used NoSQL graph

Definition
graph model database

Examples Neo4j, Amazon Neptune, OrientDB Only Neo4j

Query Language Varies (Gremlin, SPARQL, CQL, etc.) Uses Cypher Query Language (CQL)

Optimized native graph storage and

Storage Engine Varies by implementation
processing

Community & Large active community and

Depends on the product
Support commercial support

Use Cases Generic: graphs, relationships, etc. Social networks, fraud detection, etc.

14. What is document based NOSQL systems? Explain basic operations CRUD in MongoDB

Document-Based NoSQL System – Definition

A document-based NoSQL system is a type of non-relational database that stores data as
documents in formats like JSON, BSON (Binary JSON), or XML.
Each document represents a record and is self-describing, meaning it contains both the data
and the structure. This makes it more flexible and schema-less, ideal for applications with
evolving or complex data.
Features of Document-Based NoSQL Systems
• Schema-less: No fixed structure; documents can have varying fields.
• High performance for read/write operations.
• Nested data support (arrays, sub-documents).
• Scalable across distributed systems.
• Commonly used in web apps, content management systems, real-time analytics,
etc.
CRUD Operations in MongoDB
CRUD stands for:
• C – Create
• R – Read
• U – Update
• D – Delete

1. Create – Add new documents

2. Read – Retrieve documents

3. Update – Modify existing documents

4. Delete – Remove documents