Informatics Practices: Class 11 — Chapter 7: Database Concepts

Introduction & File System Concepts

Modern data management requires moving from manual record-keeping systems to electronic databases to store, organize, and retrieve information efficiently.

Limitations of Manual Record Keeping

Using manual registers (e.g., a school attendance register) presents several operational challenges:

Redundant Data Entry: Student details (Roll number and name) must be re-entered when promoted to a new class or register.
Inconsistency: Writing student details repeatedly on monthly pages can lead to spelling errors, skipped records, or mismatched names.
Data Loss Vulnerability: Physical registers are susceptible to being lost, stolen, or damaged.
Consolidation Errors: Manual calculations of attendance percentages or academic performance are prone to human error.

The File System Approach

A file is a logical container on a computer storage device used to store data. File contents can include plain \text, program code, comma-separated values ( CSV ), images, audio, video, or web pages.

While files allow electronic storage, accessing and manipulating data within them requires custom application programs to parse and process the file contents.

Example: School Student and Attendance Files

Consider two separate files maintained on a computer system:

Table 7.1: STUDENT File (Maintained by Office Staff)

RollNumber	SName	SDateofBirth	GName	GPhone	GAddress
1	Atharv Ahuja	2003-05-15	Amit Ahuja	5711492685	G-35, Ashok Vihar, Delhi
2	Daizy Bhutia	2002-02-28	Baichung Bhutia	3612967082	Flat no. 5, Darjeeling Appt., Shimla
3	Taleem Shah	2002-02-28	Himanshu Shah	4726309212	26/77, West Patel Nagar, Ahmedabad
4	John Dsouza	2003-08-18	Danny Dsouza	NULL	S -13, Ashok Village, Daman
5	Ali Shah	2003-07-05	Himanshu Shah	4726309212	26/77, West Patel Nagar, Ahmedabad
6	Manika P.	2002-03-10	Sujata P.	3801923168	HNO-13, B- block, Preet Vihar, Madurai

Table 7.2: ATTENDANCE File (Maintained by Class Teacher)

AttendanceDate	RollNumber	SName	AttendanceStatus
2018-09-01	1	Atharv Ahuja	P
2018-09-01	2	Daizy Bhutia	P
2018-09-01	3	Taleem Shah	A
2018-09-01	4	John Dsouza	P
2018-09-01	5	Ali Shah	A
2018-09-01	6	Manika P.	P
2018-09-02	1	Atharv Ahuja	P
2018-09-02	2	Daizy Bhutia	P
2018-09-02	3	Taleem Shah	A
2018-09-02	4	John Dsouza	A
2018-09-02	5	Ali Shah	P
2018-09-02	6	Manika P.	P

Limitations of a File System

As the volume of data and the number of files grow, a traditional file system exhibits several critical limitations:

(A) Difficulty in Access: Files do not have built-in query mechanisms. To retrieve data in a specific format, developers must write custom application programs. If a new query requirement arises, a new program must be written.
(B) Data Redundancy: Redundancy refers to the duplication of the same data across multiple files. For example, student names ( SName ) are stored in both the STUDENT and ATTENDANCE files. Similarly, guardian details are duplicated for siblings (e.g., Roll numbers 3 and 5). This wastes storage space and complicates updates.
(C) Data Inconsistency: When redundant data is updated in one location but not in another, the database becomes inconsistent. For example, correcting a spelling mistake in the STUDENT file but failing to update it in the ATTENDANCE file leads to conflicting records.
(D) Data Isolation: Files are stored in isolation with no logical links or mappings between them. Retrieving combined information (e.g., finding the attendance of a student along with their guardian's phone number) requires writing complex programs to manually map records across files.
(E) Data Dependence: The structure of data inside a file is tightly coupled with the application programs that access it. If the file format or schema changes (e.g., adding a new field), all associated application programs must be modified.
(F) Controlled Data Sharing: It is difficult to enforce fine-grained access control in a file system. For instance, a teacher should have write access to the attendance file, while office staff and parents should only have read access. File systems lack robust mechanisms to enforce such user-specific permissions.

Database Management System (DBMS)

A Database Management System (DBMS) is a software suite designed to create, manage, store, modify, and retrieve data from a database. It acts as an interface between the database and end-users or application programs, providing an abstract view of the data by hiding physical storage details.

Technical Profile: Database Management System (DBMS)

Architecture Layer / Tier: Middleware / Data Access Layer interfacing between physical storage and application programs.
Operational Mechanics: Uses structured query languages (e.g., SQL) to execute queries, manage transactions, and enforce data integrity constraints.
System Impact & Execution Outputs: Eliminates data redundancy, ensures data consistency, provides data independence, and enforces robust security and access controls.

Real-Life Applications of DBMS

Application Domain	Database Contents & Managed Entities
Banking	Customer information, account details, loan details, transaction histories, etc.
Crop Loan	Kisan credit card data, farmer's personal data, land area, cultivation details, loan history, repayment schedules.
Inventory Management	Product details, supplier information, stock levels, customer orders, delivery tracking.
Organisation Resource Management	Employee records, salary details, department structures, branch locations, performance metrics.
Online Shopping	Item descriptions, user login credentials, shopping carts, user preferences, transaction logs.

Transitioning from File System to DBMS

To convert the school file system into a relational database, we apply normalization principles to eliminate redundancy:

Remove Redundant Attributes: Remove SName from the ATTENDANCE table, as it can be retrieved dynamically by joining with the STUDENT table using RollNumber .
Split Tables to Avoid Redundancy: Split the STUDENT file into two tables: STUDENT and GUARDIAN . This ensures guardian details for siblings are stored only once.
Introduce Unique Identifiers: Create a unique Guardian ID ( GUID ) to uniquely identify each guardian, and use it as a linking attribute (foreign key) in the STUDENT table.

"Shifting from a file system to a DBMS involves a trade-off between operational efficiency and initial setup costs."

Socio-Technical Analysis & Core Systemic Impact: Shifting to a DBMS incurs high initial costs, including purchasing sophisticated hardware and software licenses, training users to write queries, and ongoing recurrent costs for database administrators, regular backups, and disaster recovery operations. However, it guarantees data integrity, security, and concurrent access control.

Key Concepts in DBMS

To manage data efficiently, a DBMS relies on several foundational concepts and structural definitions:

(A) Database Schema: The logical design or skeleton of the database. It defines the structure of tables, their columns, data types, constraints, and the relationships among different tables. The schema is static and rarely changes.
(B) Data Constraint: Restrictions or rules imposed on database columns to ensure data accuracy and reliability. Examples include:
- NOT NULL : Ensures a column cannot have empty values.
- UNIQUE : Ensures all values in a column are distinct.
- Value range constraints (e.g., a mobile number must be exactly 10 digits).
(C) Meta-data or Data Dictionary: Data about data. The DBMS stores the database schema, table definitions, and constraints in a centralized repository called the Database Catalog or Data Dictionary .
(D) Database Instance: The actual state or snapshot of a database at a specific point in time, containing the populated data. While the schema is static, the instance changes constantly as data is inserted, updated, or deleted.
(E) Query: A formal request made to a database to retrieve specific information. Queries are written in a query language such as Structured Query Language ( SQL ).
(F) Data Manipulation: The process of modifying database state through three core operations:
- Insertion: Adding new records (e.g., registering a new student).
- Deletion: Removing records (e.g., removing a student who left the school).
- Update: Modifying existing records (e.g., updating a guardian's phone number).
(G) Database Engine: The core software component of a DBMS responsible for storing, retrieving, processing, and securing data.

Relational Data Model

The Relational Data Model represents data in the form of inter-related tables called relations . It is the most widely used data model in modern commercial databases.

Relational Model Terminology

Relation: A table containing rows and columns.
Attribute: A named column in a relation representing a characteristic or property of the entity (also called a field ).
Tuple: A row in a relation representing a single, complete record of an entity.
Domain: The set of all permissible values that an attribute can hold. Domains are typically defined using data types (e.g., integer, character string, date).
Degree: The total number of attributes (columns) in a relation.
Cardinality: The total number of tuples (rows) in a relation.

Example: GUARDIAN Relation Analysis

Let us analyze the GUARDIAN relation schema and its properties:

GUARDIAN(GUID, GName, GPhone, GAddress)

GUID	GName	GPhone	GAddress
444444444444	Amit Ahuja	5711492685	G-35, Ashok Vihar, Delhi
111111111111	Baichung Bhutia	3612967082	Flat no. 5, Darjeeling Appt., Shimla
101010101010	Himanshu Shah	4726309212	26/77, West Patel Nagar, Ahmedabad
333333333333	Danny Dsouza	NULL	S -13, Ashok Village, Daman
466444444666	Sujata P.	3801923168	HNO-13, B- block, Preet Vihar, Madurai

Degree of GUARDIAN: 4 (Attributes: GUID, GName, GPhone, GAddress)
Cardinality of GUARDIAN: 5 (5 distinct rows/tuples)

Three Important Properties of a Relation

Properties of Attributes:
- Each attribute in a relation must have a unique name.
- The sequence/order of attributes in a relation is completely immaterial.
Properties of Tuples:
- Each tuple in a relation must be distinct (no duplicate rows are allowed).
- The sequence/order of tuples in a relation is immaterial.
Properties of State/Values:
- All values in a given attribute column must belong to the same domain (same data type).
- Each data value must be atomic (indivisible into smaller meaningful subparts). For example, a phone number column should contain a single, complete number, not multiple numbers or comma-separated lists.
- No attribute can have multiple values in a single tuple.
- A special value NULL is used to represent values that are unknown, missing, or non-applicable.

Keys in a Relational Database

Keys are attributes or sets of attributes used to uniquely identify tuples within a relation and establish relationships between tables.

1. Candidate Key

A relation can have one or more attributes that take unique values across all tuples. Any such attribute (or minimal set of attributes) that can uniquely identify a row is called a Candidate Key .

Example: In the GUARDIAN table, both GUID and GPhone are candidate keys because no two guardians will share the same ID or phone number.

2. Primary Key

The specific candidate key chosen by the database designer to uniquely identify the tuples in a relation is called the Primary Key . It must be unique and cannot contain NULL values.

Any candidate keys that are not chosen as the primary key are called Alternate Keys .

Example: If GUID is selected as the primary key for GUARDIAN , then GPhone becomes the alternate key.

3. Composite Primary Key

If no single attribute in a relation can uniquely identify a tuple, a combination of two or more attributes is selected to act as the primary key. This is called a Composite Primary Key .

Example: In the ATTENDANCE table, RollNumber is repeated on different dates, and AttendanceDate is repeated for different students. However, the combination of {RollNumber, AttendanceDate} is unique for every record and serves as the composite primary key.

4. Foreign Key

A Foreign Key is an attribute in a relation whose values are derived from the primary key of another relation. It is used to establish a link or relationship between the two tables.

Referencing Relation (Foreign Relation): The table containing the foreign key.
Referenced Relation (Primary/Master Relation): The table containing the primary key being referenced.

Example: In the STUDENT table, the attribute GUID is a foreign key referencing the GUID primary key in the GUARDIAN table. This links each student to their respective guardian.

Database Schema Diagram

The relationships between tables are represented visually using schema diagrams, where arrows originate from the foreign key and point to the referenced primary key:

  
    STUDENT (
    
    RollNumber
  
  , SName, SDateofBirth, GUID)
  |                                   |
  |                                   +---> GUARDIAN (
  
  GUID

, GName, GPhone, GAddress)
v
ATTENDANCE (

AttendanceDate

,

RollNumber

, AttendanceStatus)

Informatics Practices: Class 11 — Chapter 7: Database Concepts

🧭 Navigation Assistant