Introduction to Databases - Complete Notes with
Explanations
Unit 1: Introduction to Databases
- Definition: A database is an organized collection of data stored and accessed electronically.
- Data vs Information: Data are raw facts (e.g., '1001, Aayusha, Pokhara'), information is processed
data (e.g., 'Student Aayusha lives in Pokhara').
- File Systems vs DBMS: File systems store data in files without structure; DBMS provides
structured management, query facilities, and ensures integrity.
- Advantages of DBMS: Redundancy control, consistency, security, multi-user support,
backup/recovery, query processing.
- DBMS vs RDBMS: RDBMS (like MySQL, Oracle) uses relational tables and SQL, DBMS may not
support relations.
- Applications: Banking, airlines, telecom, education, e-commerce, social media, healthcare.
- Users of DBMS: Database administrators, application programmers, designers, end users.
Unit 2: Data Models & Data Independence
- Data Models: Define how data is represented and organized.
- Types: Hierarchical (tree), Network (graph), Relational (tables), Object-oriented (objects), NoSQL
(non-relational).
- Schema vs Instance: Schema = definition (blueprint), Instance = snapshot of data.
- Levels of Abstraction: Physical, Logical, View.
- Data Independence: Logical (change logical schema without affecting apps) & Physical (change
storage without affecting logical).
Unit 3: Entity-Relationship (ER) Model
- Entities: Real-world objects (e.g., Student, Course).
- Attributes: Properties of entities (simple, composite, derived, multi-valued).
- Relationships: Association among entities (1:1, 1:M, M:N).
- Keys: Primary key (unique ID), Foreign key (references other table).
- Constraints: Cardinality (number of entities) and Participation (total/partial).
- Weak Entities: Depend on strong entities for identification.
- ER Diagrams: Graphical representation of entities, attributes, and relationships.
- Conversion: ER diagrams → relational schema (tables).
Unit 4: Relational Model & Relational Algebra
- Relational Model: Data represented in tables (relations).
- Components: Relation (table), Tuple (row), Attribute (column).
- Constraints: Entity integrity, Referential integrity, Domain constraints.
- Relational Algebra: A set of operations to manipulate relations.
- Operations: Selection (σ), Projection (π), Union (∪), Difference (−), Cartesian Product (×), Join
(■), Division, Intersection.
Unit 5: SQL Basics
- Structured Query Language (SQL) is standard language for RDBMS.
- DDL: CREATE, ALTER, DROP (structure definition).
- DML: INSERT, UPDATE, DELETE (data manipulation).
- DQL: SELECT for querying.
- Joins: Inner join (common rows), Left/Right join (include unmatched rows), Full join (all rows).
- Subqueries: Nested queries inside another query.
- Views: Virtual tables created from queries.
- Set Operations: UNION, INTERSECT, EXCEPT.
Unit 6: Normalization
- Normalization: Process of organizing data to reduce redundancy and anomalies.
- Anomalies: Insert, update, delete anomalies.
- Functional Dependency: Attribute depends on another attribute.
- 1NF: Eliminate repeating groups, ensure atomic values.
- 2NF: Remove partial dependency (table depends on part of composite key).
- 3NF: Remove transitive dependency (non-key attribute depends on another non-key).
- BCNF: Stronger form of 3NF, every determinant must be a candidate key.
- Decomposition: Breaking tables into smaller tables while preserving data consistency.
Unit 7: Transaction Management & Concurrency
- Transaction: Sequence of operations treated as one unit (e.g., withdraw + deposit).
- ACID Properties: Atomicity, Consistency, Isolation, Durability.
- Concurrency Problems: Lost update, dirty read, non-repeatable read, phantom read.
- Locking: Shared/exclusive locks, two-phase locking protocol.
- Deadlock: Occurs when transactions wait on each other forever.
- Isolation Levels: Read uncommitted, read committed, repeatable read, serializable.
Unit 8: Recovery & Backup
- Database failures: system crash, power failure, transaction error, disk failure.
- Recovery Techniques: Logging (write-ahead log), checkpointing.
- Undo and Redo: Restoring database consistency.
- Backup Types: Full backup, incremental, differential backups.
Unit 9: Emerging Trends
- Distributed Databases: Data stored at multiple locations but acts as one system.
- NoSQL Databases: Key-value stores, document databases, graph databases for big data.
- Data Warehousing: Central repository of integrated data for analysis.
- Data Mining: Discovering hidden patterns and knowledge from large datasets.
- Big Data: Handling large volume, velocity, and variety of data.
- Query Optimization: Techniques to make SQL queries faster.