Unit 1: Database & Database Users – Core Concepts & Roles

1.1 What Is a Database?

A database is a logically organized, persistent collection of data that supports efficient retrieval, insertion, update, and deletion. It is not a file; it is a structured repository that can be queried, indexed, and secured.

1.1.1 Key Terminology

1.2 Data Abstraction and Data Models

Data abstraction is the process of hiding lower‑level details from higher‑level users. It is achieved through three layers:

A data model is a formal representation of data and its relationships. Common models:

1.2.1 Classification of DBMSs by Data Model

1.3 Schemas, Instances, and Database State

• Schema: The definition of the database structure (tables, columns, constraints).
• Instance: A snapshot of the database at a particular time, i.e., the actual data stored.
• Database State: The current instance; changes over time as transactions occur.

1.3.1 Example

Consider a simple banking database:

Customer (CustomerID, CustomerName, Address, Phone, Email)
Borrow (CustomerID, LoanNumber)
Loan (LoanNumber, LoanType, Amount)

• Schema: The three table definitions above.
• Instance: A specific set of rows, e.g., CustomerID=101 with name "Ramesh".
• State: The entire set of rows at a given moment.

1.4 Database Users and Their Roles

1.4.1 Interaction Flow

1. Developers design the schema and write application code.
2. DBAs deploy the schema, set up security, and monitor performance.
3. Application users access data through the application layer.
4. Data scientists query the database for analytics.

1.5 Traditional File Processing vs Database Approach

1.6 Normalization – Eliminating Redundancy

Normalization is a systematic process of decomposing tables to reduce redundancy and avoid update anomalies.

1.6.1 1NF (First Normal Form)

• Rule: Each column must contain atomic (indivisible) values; no repeating groups.
• Example: Customer(PhoneNumbers) → split into separate rows or a child table.

1.6.2 2NF (Second Normal Form)

• Rule: In 1NF, all non‑key attributes must depend on the entire primary key (no partial dependency).
• Example: Order(OrderID, CustomerID, CustomerName) → move CustomerName to Customer table.

1.6.3 3NF (Third Normal Form)

• Rule: In 2NF, no transitive dependency (non‑key → non‑key).
• Example: Employee(EmployeeID, DeptID, DeptName) → separate Department table.

Worked Example – Banking Database

Original table:

Borrow (CustomerID, CustomerName, LoanNumber, LoanType, Amount)

1. 1NF: Ensure atomic columns – already satisfied.
2. 2NF: Remove partial dependency on CustomerID:
   • Customer (CustomerID, CustomerName)
   • Loan (LoanNumber, LoanType, Amount)
   • Borrow (CustomerID, LoanNumber)
3. 3NF: Remove transitive dependency:
   • No further changes needed; each non‑key attribute depends only on its own key.

Resulting schema eliminates redundancy and update anomalies.

1.7 ACID Properties – Foundations of Reliable Transactions

1.8 Database Recovery Techniques

When a failure occurs, recovery ensures the database returns to a consistent state.

1.9 Summary of Advantages and Disadvantages of DBMS

1.10 Exam Tip

• Understand the hierarchy: Know the difference between data, database, DBMS, database system, and catalog.
• Be ready to classify: Given a data model, identify the DBMS type and list its strengths/weaknesses.
• Normalization drills: Practice converting a denormalized table into 1NF, 2NF, 3NF; be able to explain each step.
• ACID & recovery: Memorize the four ACID properties and give a concise example of each.
• User roles: Match responsibilities to user types; be able to explain why each role is essential.
• Past question patterns: Many questions ask for classification, schema design, or writing simple SQL queries; practice writing clear, concise answers.

Good luck!