DBMS Unit I — Introduction & Relational Model Summary & Study Notes

🔹 Overview of Database Management Systems

A Database Management System (DBMS) is a software system that manages a collection of interrelated data and a set of programs to access those data. The stored collection of data is called the database. A DBMS facilitates defining, constructing, manipulating, and sharing databases among users and applications.

🔸 Key Properties of a Database

A database represents some aspect of the real world (the miniworld or universe of discourse). It is a logically coherent collection of data with inherent meaning, designed and populated for a specific purpose and an intended group of users.

🔹 Problems with File-Processing Systems

Storing information in OS files (file-processing systems) causes several issues:

• Data redundancy and inconsistency: Multiple files may duplicate the same data in different formats.
• Data isolation: Multiple files and formats make integrated access difficult.
• Difficulty in accessing data: New tasks often require writing new programs.
• Integrity problems: Constraints (e.g., account balance > 0) become embedded in application code and are hard to change.
• Atomicity of updates: Partial updates on failures can leave the database inconsistent (e.g., fund transfer must fully succeed or not happen at all).
• Concurrent access: Uncontrolled concurrent operations can produce inconsistencies.
• Security problems: File systems lack centralized mechanisms for access control.

🔸 Advantages of DBMS over File Systems

Using a DBMS provides:

• Data independence
• Efficient data access
• Data integrity and security
• Centralized data administration
• Concurrency control and crash recovery
• Reduced application development time

Common applications include banking transactions, airline reservations, university registration, sales and retail, manufacturing, and human resources.

🔹 DBMS Architectures

A DBMS architecture defines how users interact with the database. Common architectures:

• 1-Tier: Client, server and database reside in the same application/system.
• 2-Tier: Basic client-server model where the client application interacts directly with the database server (APIs like ODBC, JDBC).
• 3-Tier: Client interacts with an application server, which communicates with the database. Query processing and transaction management occur on the server side.

🔸 Three-Schema Architecture (Levels of Abstraction)

DBMS hides physical details through levels of abstraction to simplify user interaction:

• Physical Level (Internal Schema): Describes how data are stored on storage media.
• Logical Level (Conceptual Schema): Describes what data are stored and relationships among them for a community of users.
• View Level (External Schema): Contains user-specific views; each external schema shows part of the database relevant to a user group.

🔹 Database Schema vs Instance

• Database schema: The description of the database (structure) specified during design; changes infrequently.
• Database instance (state): The data stored in the database at a particular moment.

The DBMS stores schema descriptions and constraints as metadata.

🔸 Data Independence

Data independence is the ability to change the schema at one level without changing schemas at higher levels. Two types:

• Physical (internal) data independence: Change physical storage (e.g., indexing, file format) without affecting the logical schema or applications.
• Logical (conceptual) data independence: Change the logical schema (e.g., split/merge tables) without affecting external schemas or application programs. This is harder to achieve than physical independence.

How it is achieved: the three-level architecture maps internal, conceptual, and external levels so changes at one level do not directly impact others.

🔹 Database Languages

DBMS provides languages to define, manipulate, and control data:

• Data Definition Language (DDL): Define schema and database structures.
• Data Manipulation Language (DML): Query and update data.
• Data Control Language (DCL): Manage permissions and transactions.

A widely used language is SQL (Structured Query Language) for DDL, DML, and DCL tasks.

🔸 Data Models — Overview

A data model shows the logical structure of a database, relationships, and constraints. Major models include:

• Hierarchical model
• Network model
• Relational model
• Entity-Relationship (E-R) model
• Object-oriented model
• Object-relational model

🔹 Hierarchical Data Model

Organizes data in a tree-like structure with parent-child relationships. Each record has at most one parent and many children, forming one-to-many relationships. Navigation uses pointers. Deleting a parent may remove children.

🔸 Network Data Model

A generalization of the hierarchical model. Records can have multiple parents, forming a graph. It supports many-to-many relationships and multiple access paths, often implemented via linked lists and pointers.

🔹 Relational Data Model (E. F. Codd, 1970)

Represents data as a collection of relations (tables). Each relation has attributes (columns) drawn from domains (sets of permitted values). A tuple (row) represents a specific instance. Key concepts:

• Relation schema: R(A1, A2, ..., An) — relation name and attribute list.
• Relation instance: A set of tuples r = {t1, t2, ... , tm} where each tuple t = <v1, v2, ..., vn> and vi ∈ dom(Ai).
• Degree (arity): Number of attributes in the schema.
• Cardinality: Number of tuples (rows) in the relation.

Example: Student(sid: string, name: string, login: string, age: integer, gpa: real).

🔸 ER (Entity-Relationship) Model

Models the real world as entities (distinct objects) and relationships among them. Entities have attributes and each entity set requires a unique identifier (key). ER diagrams graphically represent schema structure.

🔹 Object-Oriented and Object-Relational Models

• Object-oriented model: Database as a collection of objects with associated methods; supports complex data types, multimedia, and links.
• Object-relational model: Hybrid that extends relational tables with object features (complex types, methods) to gain flexibility while preserving relational simplicity.

🔸 Core Relational Concepts

• Table: Physical storage of a relation with rows (tuples) and columns (attributes).
• Tuple: A single row representing one record.
• Attribute domain: Set of permissible values for an attribute (datatype and constraints).
• Relation key: Attribute(s) that uniquely identify tuples; a relation can have multiple candidate keys and one primary key.

🔹 Integrity Constraints

Constraints restrict data values and relationships to ensure correctness and consistency. Main types:

• Domain constraints: Specify valid sets of values for attributes (e.g., datatype, allowed range).
• Entity integrity: Primary key attributes cannot be NULL because they uniquely identify tuples.
• Referential integrity: If a foreign key in table A references the primary key of table B, then every non-NULL foreign key value must exist as a primary key value in table B.
• Key constraints: Ensure uniqueness for key attributes; candidate keys and primary key selection.

Importance of constraints:

• Ensure accuracy and consistency of data.
• Prevent data errors like duplication or invalid values.
• Enforce different types of data integrity (entity, referential, domain).
• Improve the reliability of queries, reports, and the system overall.

🔸 Transactions, Concurrency, and Recovery (Basics)

A DBMS provides mechanisms for transactions (atomic sequences of operations), concurrency control (safe multi-user access), and crash recovery to maintain consistency and durability after failures.

🔹 Summary — Why DBMS Matter

A DBMS centralizes data management, enforces integrity through constraints, supports efficient and concurrent access, and provides data independence across abstraction levels. These capabilities make DBMS essential for modern applications like banking, reservations, education systems, retail, manufacturing, and HR.