Chapter 8: Database Concepts

CBSE Unit: Unit 3, Database Management (20 marks shared with SQL) Marks Weightage: ~4-6 marks (theory foundation for SQL chapter) Priority: HIGH, foundational theory, direct questions in board exams

Key Concepts

8.1 File System, A file is a container to store data in a computer (text, CSV, programs, images, audio/video), Files can be accessed directly but require application programs to process data through software, Example: School stores student details in one file, attendance in another file

8.2 Limitations of File System

Limitation	Explanation	Example
Difficulty in Access	No built-in mechanism to retrieve data; need custom programs	Cannot easily generate reports without writing code
Data Redundancy	Same data duplicated in different files	Student name stored in both STUDENT and ATTENDANCE files
Data Inconsistency	Same data in different files does not match	Name spelling changed in one file but not another
Data Isolation	No link/mapping between related files	STUDENT and ATTENDANCE files maintained separately with no connection
Data Dependence	Changing file structure requires changing all programs	Adding a new field requires modifying every program that accesses the file
Controlled Data Sharing	Difficult to enforce access control	Cannot restrict a teacher to read-only access on attendance file

8.3 Database Management System (DBMS)

Database: A collection of logically related data organized in tables
DBMS: Software used to create and manage databases, Examples of DBMS: MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB, DBMS provides an abstract view of data and serves as an interface between database and end users/application programs
Querying: Retrieving data from a database through special commands

8.4 Key Concepts in DBMS

(A) Database Schema, The design/structure of a database (skeleton), Represents: table names, fields/columns, data types, constraints, relationships, Also called visual or logical architecture

(B) Data Constraint, Restrictions/limitations on the type of data that can be inserted, Example: Mobile number must be exactly 10 digits, non-negative, Constraints ensure accuracy and reliability of data

(C) Meta-data / Data Dictionary, Database schema + constraints stored in a database catalog

Meta-data = data about the data

(D) Database Instance, The snapshot/state of the database at any given time, Empty when schema is first defined; changes with insertions/updates/deletions, A schema can have many instances at different times

(E) Query, A request to a database for obtaining information, Can retrieve data from one table or combination of tables, Written using a query language (SQL)

(F) Data Manipulation

Insertion: Adding new records (e.g., new student joins)
Deletion: Removing records (e.g., student leaves school)
Update: Modifying existing records (e.g., guardian changes phone number)

(G) Database Engine, Underlying component/set of programs used by DBMS to create database and handle queries

8.5 Relational Data Model, Most commonly used data model, Tables are called relations

Each table has multiple columns (unique names) and rows, Each row represents a related set of values
Relations in a database are associated with each other through common attributes (linking attributes)

Key Terminology

Term	Definition	Example
Relation	A table in the database	GUARDIAN table
Attribute	A column/field of a relation	GUID, GName, GPhone, GAddress
Tuple	A row/record of a relation	One guardian's complete data
Domain	Set of allowed values for an attribute (data type)	RollNumber domain = set of integers
Degree	Number of attributes (columns) in a relation	GUARDIAN has degree 4
Cardinality	Number of tuples (rows) in a relation	GUARDIAN with 5 rows has cardinality 5

8.6 Three Important Properties of a Relation

Property 1 (Attributes):

Each attribute has a unique name
Sequence of attributes is immaterial (column order does not matter)

Property 2 (Tuples):

Each tuple must be distinct (no two identical rows), Sequence of tuples is immaterial (row order does not matter)

Property 3 (State/Values):

All values in an attribute must be from the same domain (same data type), Each value must be atomic (indivisible into meaningful subparts)
No multi-valued attributes (e.g., cannot have multiple phone numbers in one field)
NULL represents unknown or non-applicable values

8.7 Keys in a Relational Database

Candidate Key, Attribute(s) that take distinct values and can uniquely identify tuples, A relation can have one or more candidate keys, Example: In GUARDIAN, both GUID and GPhone are candidate keys

Primary Key, The candidate key chosen by the database designer to uniquely identify tuples, Only ONE primary key per table, Remaining candidate keys become Alternate Keys

Example: GUID chosen as primary key; GPhone becomes alternate key

Composite Primary Key, Primary key consisting of more than one attribute (when no single attribute can uniquely identify tuples), Example: In ATTENDANCE, {RollNumber, AttendanceDate} together form the composite primary key

Foreign Key, An attribute whose value is derived from the primary key of another relation

Used to represent the relationship between two relations, Can take NULL values (if not part of primary key of its own table), The table containing the foreign key = Referencing/Foreign Relation
The table whose primary key is referenced = Referenced/Primary/Master Relation
Example: GUID in STUDENT table is a foreign key referencing GUID in GUARDIAN table

Important Definitions

Database: A collection of related tables (relations)
DBMS: Software to create, manage, and manipulate databases
Schema: The design/structure of a database
Instance: The snapshot of a database at any given time
Meta-data: Data about the data (schema + constraints stored in catalog)
Query: A request to a database for information retrieval
Relation: A table in a relational database
Attribute: A column/field in a relation
Tuple: A row/record in a relation
Domain: Set of permissible values for an attribute
Degree: Number of attributes in a relation
Cardinality: Number of tuples in a relation
Primary Key: Attribute that uniquely identifies each tuple
Candidate Key: Any attribute that CAN uniquely identify tuples
Alternate Key: Candidate key NOT chosen as primary key
Composite Primary Key: Primary key made of more than one attribute
Foreign Key: Attribute that references the primary key of another table
Data Redundancy: Same data stored in multiple places
Data Inconsistency: Same data in different places does not match
NULL: Special value representing unknown/not applicable data

Common Board Exam Question Patterns

Define terms (1 mark each): Relation, Tuple, Attribute, Domain, Degree, Cardinality, Primary Key, Foreign Key, Candidate Key, Alternate Key
Differentiate between (2 marks): Primary Key vs Foreign Key, Degree vs Cardinality, Database Schema vs Database Instance, Candidate Key vs Primary Key
Identify keys from a given table (2-3 marks): Given a relation schema, identify primary key, candidate key, foreign key, alternate key, composite key
Limitations of file system (2-3 marks): List and explain any 3-4 limitations
File System vs DBMS (2 marks): How does DBMS overcome file system limitations
Properties of a relation (2 marks): Explain any two properties
Can NULL be assigned? questions: Whether NULL can be in primary key (NO) or foreign key (YES)
Degree and Cardinality calculation: Given a table, state degree and cardinality
Design a database: Given a scenario, identify tables, attributes, primary keys, and foreign keys

Key Points Students Miss

Primary key CANNOT be NULL but foreign key CAN be NULL
Degree = number of columns, Cardinality = number of rows (students often confuse these)
Sequence of tuples and attributes is immaterial in a relation (two tables with same data in different order are equivalent)
Each attribute value must be atomic (no multi-valued fields allowed)
Alternate key is simply the candidate key that was NOT selected as primary key
A composite primary key is needed when no single attribute can uniquely identify rows (like AttendanceDate + RollNumber)
Foreign key references the PRIMARY KEY of another table, not just any attribute
Data Redundancy leads to Data Inconsistency, this cause-effect relationship is important
Schema vs Instance: Schema is the design (does not change often), Instance is the actual data at a point in time (changes frequently)
NULL is NOT the same as 0 or empty string, NULL means unknown/not applicable