1. Data and Information

Concept Definition Example
Data Raw, unprocessed facts and figures without context 85, Aryan, XII-A, 2024
Information Processed, organised data that carries meaning "Aryan of Class XII-A scored 85 marks in 2024"
Database An organised, structured collection of related data stored electronically so it can be easily accessed, managed and updated A school database storing student records, exam marks, attendance

2. Traditional File System vs Database Management System

Before databases, organisations stored data in flat files (text files, spreadsheets). This approach had serious limitations that DBMS was designed to overcome.

Limitations of Traditional File Processing System

Limitation Problem Example
Data Redundancy Same data stored multiple times in different files — wastes storage Student name and address stored in both Admission file and Fee file
Data Inconsistency Copies of data get out of sync — one updated, others not Address updated in Admission file but not in Fee file → two different addresses
Difficulty in Accessing Data No easy way to query or filter — need custom programs for each report "Find all students who scored above 90 in Maths" requires writing a new program
Data Isolation Data scattered in different files and formats — hard to combine Student data in .txt, marks in .csv, fees in .xls — difficult to generate a combined report
Integrity Problems No automatic enforcement of data constraints Age stored as negative number, duplicate admission numbers — no validation
Atomicity Problems Partial updates leave data in inconsistent state if system crashes mid-operation Bank transfer: ₹500 deducted from Account A but not added to Account B due to crash
Concurrent Access Anomalies Two users updating same file simultaneously causes data corruption Two clerks editing the same student record simultaneously
Security Problems Difficult to enforce access controls at attribute level Clerk should not see salary data — no easy way to restrict file access to specific columns

What is a Database Management System (DBMS)?

A DBMS is software that enables users to define, create, maintain and control access to a database. It acts as an interface between the database and end users or application programs.

Examples: MySQL, Oracle, MS Access, PostgreSQL, SQLite

Advantages of DBMS over File System

Advantage How DBMS Achieves It
Reduced Data Redundancy Centralised storage — data stored once, referenced elsewhere using keys
Data Consistency Single source of truth — updating data in one place reflects everywhere
Easy Data Access SQL provides a powerful, standardised query language for any data retrieval need
Data Integrity Constraints (PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY) enforce valid data automatically
Data Security User access controls — GRANT/REVOKE privileges at table or column level
Concurrent Access Transaction management and locking mechanisms prevent data corruption during simultaneous access
Backup and Recovery DBMS provides tools for regular backups and recovery from hardware/software failures
Data Independence Physical storage changes don't affect application programs (logical-physical independence)

3. The Relational Data Model

The Relational Model (proposed by E.F. Codd, 1970) organises data into tables (relations) consisting of rows and columns. It is the most widely used database model and is the basis of MySQL, Oracle, and PostgreSQL.

Key Terminology of the Relational Model

Formal Term Common Term Definition
Relation Table A 2-dimensional structure with rows and columns representing an entity (e.g., Student table)
Tuple Row / Record A single row in a table representing one instance of the entity
Attribute Column / Field A named property of the entity (e.g., Name, Age, Marks)
Domain Set of valid values The set of all permitted values for an attribute (e.g., Domain of Grade = {A, B, C, D, F})
Degree Number of columns Total number of attributes in a relation
Cardinality Number of rows Total number of tuples in a relation at any given time

Example Relation: STUDENT

StudentID Name Age Class
101 Aryan 17 XII-A
102 Priya 16 XI-B
103 Rohan 17 XII-A
  • Degree = 4 (four attributes: StudentID, Name, Age, Class)
  • Cardinality = 3 (three tuples/rows)
  • Domain of Age = Set of positive integers (e.g., 10–20 for school students)
  • Primary Key = StudentID (uniquely identifies each student)

4. Keys in a Relational Database

Keys are attributes (or combinations of attributes) used to identify tuples uniquely and to establish relationships between tables.

Key Type Definition Constraints Example
Primary Key Attribute or set of attributes that uniquely identifies each tuple in a relation NOT NULL; UNIQUE; Only ONE per table StudentID in STUDENT table; AdmNo in Admission table
Candidate Key Any attribute (or minimal set) that can serve as a primary key — all are unique and non-null UNIQUE; NOT NULL; Can be multiple per table Both StudentID and Email could be candidate keys (both unique)
Alternate Key Candidate keys that are not chosen as the primary key UNIQUE; NOT NULL If StudentID is chosen as PK, then Email is an Alternate Key
Foreign Key Attribute in one table that references the Primary Key of another table — creates a link between tables Value must match an existing PK value in referenced table (referential integrity) StudentID in MARKS table references StudentID (PK) in STUDENT table

Foreign Key — Illustrated

Consider two related tables:

STUDENT (Parent Table)
StudentID (PK) Name Class
101AryanXII-A
102PriyaXI-B
MARKS (Child Table)
ExamID (PK) StudentID (FK → STUDENT) Marks
E0110185
E0210291

StudentID in MARKS is a Foreign Key — it must contain a value that exists in the StudentID column of STUDENT. This enforces referential integrity — you cannot insert a MARKS record for a StudentID that doesn't exist in STUDENT.

5. Properties of a Relation

A valid relation in the relational model must satisfy these properties:

  • Each relation has a unique name: No two tables can have the same name in a database.
  • Each attribute (column) has a unique name within that relation.
  • Each cell contains exactly one atomic value: No multi-valued attributes (First Normal Form — 1NF).
  • Each tuple is unique: No two identical rows — at minimum, the primary key distinguishes them.
  • Order of tuples is immaterial: Rows have no inherent order in the relational model.
  • Order of attributes is immaterial: Columns can be in any order without affecting the meaning of the relation.
  • Attribute values are from the same domain: All values in a column must be of the defined data type and domain.

6. Database Schema and Instance

Concept Definition Analogy
Schema The structure/design of the database — table names, column names, data types, constraints. Does not change frequently. Blueprint of a building (structure defined before construction)
Instance The actual data stored in the database at a particular point in time. Changes with every INSERT, UPDATE, DELETE. The building after construction with furniture inside (changes over time)

Example: STUDENT(StudentID INT, Name VARCHAR(30), Age INT, Class VARCHAR(10)) — this is the schema. The actual rows (101, Aryan, 17, XII-A) etc. are the instance.

7. Types of Database Users

User Type Role
Database Administrator (DBA) Manages the entire database — creates schema, grants permissions, ensures backup and recovery, optimises performance
Application Programmers Write programs that interact with the database (e.g., Python-MySQL applications)
Sophisticated (Power) Users Write SQL queries directly without programming — analysts, data scientists
Naïve (End) Users Interact through applications (forms, menus) — unaware of underlying database structure