UNIT 1 – INTRODUCTION, DATA INDEPENDENCE & ER MODELING
(Covers: DBMS, Data Independence, Mapping Cardinality, Keys, ER Model, EER)
Q1. Explain Database Management System (DBMS) and its advantages over File System.
A Database Management System (DBMS) is software that enables users to define, create, store, retrieve, and manage data efficiently in a database. It acts as an interface between the user and the stored data.
Limitations of File System
Advantages of DBMS
1. Reduced Redundancy – Centralized storage
2. Improved Consistency – Single copy of data
3. Data Security – Authorization & authentication
4. Concurrency Control – Multiple users simultaneously
5. Backup & Recovery – Crash recovery support
6. Data Independence – Structural changes don’t affect programs
Q2. Explain Data Independence. Discuss its types.
Data Independence is the ability to modify the database schema at one level without affecting higher levels.
Types:
(a) Physical Data Independence
Changes in physical storage do not affect logical schema
Example: Adding indexes, changing file structure
(b) Logical Data Independence
Changes in conceptual schema do not affect user views
Example: Adding a new attribute
👉 Logical data independence is harder to achieve.
Q3. Explain ER Model and Mapping Cardinality.
The Entity Relationship (ER) Model is a high-level conceptual model used for database design.
ER Components
Entity – Real-world object
Attribute – Property of entity
Relationship – Association between entities
Key – Uniquely identifies entity
Mapping Cardinality
1. One-to-One (1:1) – Person–Passport
2. One-to-Many (1:M) – Department–Employee
3. Many-to-Many (M:N) – Student–Course
M:N relationships require separate tables.
Q4. Explain Keys and Integrity Constraints.
Types of Keys
Super Key – Uniquely identifies records
Candidate Key – Minimal super key
Primary Key – Chosen candidate key
Foreign Key – References primary key
Integrity Constraints
1. Domain Integrity – Valid attribute values
2. Entity Integrity – Primary key not NULL
3. Referential Integrity – Foreign key validity
🔷 UNIT 2 – RELATIONAL DATA MODEL & CONSTRAINTS
(Covers: Relational Model, Integrity Constraints, Relational Algebra & Calculus)
Q5. Explain Relational Data Model with integrity constraints.
The Relational Data Model represents data as tables (relations).
Basic Concepts
Relation (table)
Tuple (row)
Attribute (column)
Domain
Degree & Cardinality
Integrity Constraints
Domain Constraint
Entity Integrity
Referential Integrity
Key Constraint
These constraints ensure accuracy, consistency, and reliability.
Q6. Explain Relational Algebra and Join Operations.
Relational Algebra is a procedural query language.
Operations
Selection (σ)
Projection (Ï€)
Union (∪)
Difference (−)
Cartesian Product (×)
Join Operations
Natural Join
Equi Join
Left & Right Outer Join
It forms the foundation of SQL.
Q7. Explain Relational Calculus.
Relational Calculus is a non-procedural query language.
Types
Tuple Relational Calculus (TRC) – Uses tuples
Domain Relational Calculus (DRC) – Uses domains
Focuses on what to retrieve, not how.
🔷 UNIT 3 – SQL (FOCUS: JOINS, CURSOR, TRIGGERS, CLUSTERS)
Q8. Explain INNER JOIN and SELF JOIN with examples.
INNER JOIN
Returns only matching records.
SELECT e.name, d.dept_name
FROM emp e INNER JOIN dept d
ON e.dept_id = d.dept_id;
SELF JOIN
Table joined with itself.
SELECT e1.name, e2.name
FROM emp e1, emp e2
WHERE e1.manager_id = e2.emp_id;
Q9. Explain Cursors in SQL/PL-SQL.
A cursor processes query results row by row.
Types
1. Implicit Cursor
2. Explicit Cursor
Explicit Cursor Steps
DECLARE
OPEN
FETCH
CLOSE
Used when handling multiple rows.
Q10. Explain Triggers and Clusters.
Triggers
A trigger is a block of code executed automatically on INSERT, UPDATE, DELETE.
Types:
BEFORE Trigger
AFTER Trigger
INSTEAD OF Trigger
Clusters
Group of tables stored together
Improves join performance
🔷 UNIT 4 – NORMALIZATION & DEPENDENCIES
(VERY IMPORTANT – FULL CONVERSION)
Q11. Explain Functional Dependency and its types.
If A → B, attribute A determines B.
Types
Trivial FD
Non-trivial FD
Partial Dependency
Transitive Dependency
-
Q12. Explain Normalization and all Normal Forms with conversion.
Normalization
Process of reducing redundancy and anomalies.
-
1NF
Atomic values
No repeating groups
-
2NF
In 1NF
No partial dependency
👉 Decompose table
-
3NF
In 2NF
No transitive dependency
👉 Remove non-key dependencies
-
BCNF
Stronger than 3NF
Every determinant is a candidate key
-
Q13. Explain anomalies and need of normalization.
Anomalies
Insertion anomaly
Deletion anomaly
Update anomaly
Normalization removes these issues.
-
🔷 UNIT 5 – TRANSACTIONS, CONCURRENCY & ARCHITECTURE
-
Q14. Explain Transaction Processing and ACID properties.
A transaction is a sequence of operations executed as a single unit.
ACID
Atomicity
Consistency
Isolation
Durability
-
Q15. Explain Concurrency Control and Timestamp-Based Protocol.
Concurrency control ensures correct execution of simultaneous transactions.
Timestamp-Based Protocol
Each transaction gets a timestamp
Older transaction gets priority
Deadlock-free protocol
Q16. Explain Client-Server, Parallel and Traditional Architecture.
Client-Server Architecture
Client: Interface
Server: Database processing
Two-tier & Three-tier
Parallel Architecture
Multiple processors
High throughput
Faster execution
Traditional (Centralized) Architecture
Single server
Low scalability
Simple design
No comments:
Post a Comment