The Database Approach introduces a more structured and efficient method of data storage and retrieval compared to traditional file systems. Key characteristics include:
Example:
A university database contains all student records in one centralized system,
allowing multiple departments to access the same up-to-date data.
Data models define how data is organized, related, and manipulated. Major models include:
Example:
Relational Model Table:
Employee(EmpID, Name, Department)
(101, 'Ankit', 'HR')
(102, 'Sneha', 'IT')
DBMS Architecture defines the structure and components of a database system. Common types:
Example:
Client ↔ Application Server ↔ DBMS
Used in web-based applications for better scalability and security.
Data Independence is the ability to modify a schema definition in one level without affecting the next higher level. Types include:
Example:
Physical change: Moving data from magnetic disk to SSD does not impact the logical design.
Logical change: Adding a new column to a table without affecting existing queries.
This unit provides an essential understanding of Database Management Systems (DBMS):
Quick Recap:
✔ DBMS improves data consistency and access.
✔ Relational model is the most popular.
✔ 3-tier architecture supports large applications.
✔ Logical and physical independence ensure flexibility.
An Entity is a real-world object that is distinguishable from others. An Entity Type defines a collection of similar entities. An Entity Set is a set of entities of the same type stored in a database.
Example:
Entity Type: Student
Entity Set: {Student1, Student2, Student3}
Attributes describe properties of an entity. A Key is an attribute (or set of attributes) that uniquely identifies an entity within an entity set.
Example:
Student(ID, Name, Age)
Key: ID uniquely identifies each student.
Relationship is an association among two or more entities. Relationship Types define the set of associations among entity sets.
Example:
Student - Enrolls - Course
Relationship Type: Enrolls
Roles define the function of an entity in a relationship. Structural Constraints include:
Example:
A student can enroll in many courses (1:N cardinality).
Weak Entities are entities that cannot be uniquely identified by their own attributes alone and depend on a related strong entity.
Example:
Dependent(Name, Age) depends on Employee(ID) → weak entity.
The Enhanced E-R (EER) Model adds advanced features to basic E-R models like:
Example:
Employee → sub-classes: Manager, Engineer
Attributes like Name, ID are inherited.
This unit builds foundational understanding of how real-world data is abstracted using E-R and EER models. Key concepts:
Quick Recap:
✔ Entity = real-world object
✔ Key = unique identifier
✔ EER = advanced modeling with inheritance and generalization
Indexed Sequential Access Method (ISAM) combines sequential and direct access using indexing. Records are stored in sequence, and an index allows fast searching.
Example:
Index Table → [101 → Block 1, 102 → Block 2]
Data File → [101, 102, 103, ...]
B-Trees and B+ Trees are self-balancing tree structures used for indexing large databases.
Example:
B+ Tree:
[30 | 60]
/ | \
[10 20] [40 50] [70 80]
Hashing is a technique for direct access of records using a hash function. A Hash Function converts a key into an address.
Example:
Hash(key) = key % 10
Key = 103 → Hash = 3 → store in Bucket 3
Collision occurs when two keys hash to the same index. Common resolution techniques:
Example:
Keys 105 and 115 → both hash to index 5 → use chain at index 5.
Extendible Hashing dynamically adjusts the hash table size based on directory depth, avoiding overflows.
Example:
Binary hash → directory depth grows: 00, 01, 10, 11
Split bucket when overflow occurs.
Dynamic Hashing resizes the hash table dynamically as the data grows. It ensures constant access time and avoids performance degradation.
Example:
Hash table doubles in size when threshold is reached,
rehashes existing keys to new buckets.
This unit explains different file organization techniques for efficient data storage and access. Key takeaways:
Quick Recap:
✔ Indexed files = fast + ordered access
✔ B+ trees = efficient for range queries
✔ Hashing = fast lookup; use chaining to resolve collisions
✔ Extendible hashing = scalable directory
The Relational Data Model organizes data into relations (tables), where each relation consists of rows (tuples) and columns (attributes).
Example:
Student(ID, Name, Age)
(101, 'Raj', 20)
(102, 'Kriti', 21)
Relational Constraints ensure the accuracy and integrity of data in a relation. Types include:
Example:
Student(CourseID) references Course(CourseID)
→ Referential Integrity enforced.
Relational Algebra is a procedural query language used to query relational databases. Key operations include:
Example:
σ Age > 20 (Student)
π Name (Student)
Student ⨝ Course (Student.CourseID = Course.CourseID)
SQL (Structured Query Language) is used to manage and manipulate relational databases. It includes commands for:
Example:
CREATE TABLE Student(ID INT, Name VARCHAR(50), Age INT);
INSERT INTO Student VALUES(101, 'Ravi', 22);
SELECT * FROM Student WHERE Age > 20;
Programming with SQL involves using SQL within host languages (e.g., C, Java) or procedural extensions like PL/SQL. It allows for:
Example (PL/SQL):
BEGIN
UPDATE Student SET Age = Age + 1 WHERE ID = 101;
END;
This unit introduces fundamental aspects of the relational model and SQL:
Quick Recap:
✔ Tables = Relations
✔ Constraints = Ensure accuracy
✔ Algebra = Query logic
✔ SQL = Real-world implementation
EER (Enhanced Entity-Relationship) and ER diagrams represent the high-level design of a database. Mapping them to a relational model allows implementation in relational DBMS.
Example:
EER Diagram → Tables (Relations) in SQL
Each regular entity type is mapped to a relation. Attributes become columns and the primary key is defined.
EER:
Entity: Student(ID, Name, Age)
→ Relation: Student(ID PRIMARY KEY, Name, Age)
Binary relationships are mapped by creating a new relation or adding foreign keys, based on cardinality:
EER:
Student --Enrolls--> Course
→ Enrolls(StudentID, CourseID)
Weak entities depend on a strong entity and do not have a primary key of their own. They are mapped with a composite key that includes the primary key of the owning entity.
EER:
Dependent(Name, Age) depends on Employee(EmpID)
→ Relation: Dependent(EmpID, Name, Age), PRIMARY KEY(EmpID, Name)
There are three strategies to map inheritance (ISA relationships) from EER to relational schema:
EER:
Superclass: Person(ID, Name)
Subclass: Student(Major)
→ Option 1: Person(ID, Name), Student(ID, Major)
This unit bridges conceptual EER models with practical relational databases. Key mapping techniques:
Quick Recap:
✔ Entities → Tables
✔ Weak Entities → Composite Keys
✔ ISA → Option-based relational mapping
A Functional Dependency (FD) occurs when one attribute uniquely determines another attribute. It is the foundation of normalization.
Example:
RollNo → Name
Means: If we know RollNo, we can uniquely identify Name.
Normalization is a process of organizing data to reduce redundancy and improve integrity. The main normal forms are:
Example:
Unnormalized: Student(RollNo, Name, Subject1, Subject2)
1NF: Student(RollNo, Name, Subject)
2NF: Remove partial dependency
3NF: Ensure non-transitive dependencies only
A Transaction is a sequence of operations performed as a single logical unit of work. It should follow ACID properties:
Example:
Transfer money from Account A to B involves 2 steps:
1. Deduct from A 2. Add to B — must be atomic.
Concurrency Control manages simultaneous transaction execution to avoid conflicts. Locking ensures data consistency:
Common techniques include:
Example:
Transaction T1 reads → Shared Lock
T2 wants to write → waits for T1 to release
Recovery ensures the database returns to a consistent state after a failure. Common techniques:
Example:
Use logs to undo uncommitted transactions after a crash.
Database Security protects data from unauthorized access. Authorization controls access levels for different users.
Example:
GRANT SELECT ON Student TO user1;
REVOKE UPDATE ON Student FROM user2;
This unit covers essential database maintenance concepts:
Quick Recap:
✔ FD → key to normalization
✔ 3NF → no transitive dependency
✔ Transactions → ACID rules
✔ Locking + Recovery = Reliable DBMS
✔ Authorization = Safe access