DATABASE MANAGEMENT SYSTEM (DBMS)

Grade XII Computer Science – Unit 1 | Complete Chapter Notes

1. Introduction to DBMS

1.1 Basic Terms

• Data: Raw, unprocessed facts and figures (e.g. 95, “Rahul”). Has no meaning by itself.
• Information: Processed, organized data that is meaningful and useful for decision making (e.g. “Rahul scored 95 in
Maths”).
• Database: An organized, structured collection of related data stored electronically so that it can be easily accessed,
managed and updated.
• Database Management System (DBMS): Software that enables users to create, define, store, retrieve, update and
manage data in a database in a systematic and secure way. It acts as an interface between the user/application and the
physical database.
Examples: MySQL, Oracle, MS Access, SQL Server, PostgreSQL, MongoDB.

1.2 Advantages of using DBMS

• Reduces data redundancy – same data is not repeated in multiple files.
• Ensures data consistency and integrity across the database.
• Provides data security through authentication and user permissions.
• Supports data sharing among multiple users/applications simultaneously.
• Enforces standardization of data format and structure.
• Provides backup and recovery mechanisms in case of data loss/crash.
• Allows easy and fast data retrieval using query languages (SQL).
• Maintains data independence – application programs are not affected by changes in data storage structure.
★ Exam tip: “Explain the advantages of DBMS” / “Compare DBMS to traditional file system” are repeated PYQs –
memorize at least 5–6 points with 1-line explanation each.

2. Data Definition Language (DDL) & Data Manipulation Language

(DML)

2.1 Data Definition Language (DDL)

DDL consists of SQL commands used to define, create, modify and remove the structure (schema) of database objects
such as tables. DDL commands are auto-committed – changes are saved permanently as soon as executed.
• CREATE – creates a new database/table/index.
• ALTER – modifies the structure of an existing table (add/drop/modify column).
• DROP – permanently deletes a table/database along with its structure and data.
• TRUNCATE – removes all records from a table but keeps its structure.

2.2 Data Manipulation Language (DML)

DML consists of commands used to insert, retrieve, update and delete data (records) stored inside the tables.
• SELECT – retrieves data from a table.
• INSERT – adds new record(s) into a table.
• UPDATE – modifies existing record(s).
• DELETE – removes existing record(s) from a table.
★ Exam tip: DDL = structure of database (schema) | DML = data/content inside the database. Very common 2+3 mark
question: “Define DML and DDL with example.”
3. Some Terms Related to Database
• Field (Column/Attribute): The smallest unit of data representing one characteristic of an entity, e.g. Name, Roll
No.
• Record (Row/Tuple): A complete set of related fields representing one entry/entity, e.g. one student's full data.
• File / File System: A traditional way of storing data in flat files without inter-relation; leads to redundancy and
inconsistency – the problem DBMS solves.
• Object: A real-world entity (table, view, index, procedure) stored and managed inside the database.

3.1 Keys in Database

Key Description
Candidate Key An attribute (or set) that can uniquely identify a record; a table may have multiple
candidate keys.
Primary Key The candidate key chosen to uniquely identify each record; cannot be NULL or
duplicate.
Foreign Key An attribute in one table that refers to the Primary Key of another table – used to
create relationships.
Composite Key A key made of two or more attributes combined to uniquely identify a record.
Alternate Key Candidate keys that are not chosen as the primary key.

★ Exam tip: “Which key uniquely identifies each record?” → Primary Key. “Which key matches a primary key of another
table?” → Foreign Key. Both are frequently asked MCQs.

4. Database Models
A database model defines the logical structure of a database – how data is stored, organized and related.
(a) Hierarchical Model
• Organizes data in a tree-like (parent-child) structure.
• Each child has only one parent (one-to-many relationship).
• Advantage: Fast access, simple design. Disadvantage: Difficult to represent many-to-many relationships.

(b) Network Model

• An extension of the hierarchical model where a child can have multiple parents (graph structure).
• Supports many-to-many relationships using 'links' or 'pointers'.

(c) Relational Model (RDBMS)

• Data is organized into tables (relations) consisting of rows (tuples) and columns (attributes).
• Relationships between tables are established using primary key – foreign key.
• Most widely used model today (MySQL, Oracle, SQL Server).

(d) Object-Oriented Model

• Stores data in the form of objects, similar to object-oriented programming (encapsulation, inheritance).
★ Exam tip: “Explain the different database models with example / diagram” – write all 4 models with one
advantage/disadvantage each.
5. Concept of Normalization
Normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity, by
decomposing large tables into smaller related tables following a series of rules called Normal Forms.

5.1 Purpose of Normalization

• Eliminate redundant (duplicate) data.
• Ensure data dependencies make logical sense (data integrity).
• Reduce anomalies during Insert, Update and Delete operations.
• Optimize storage space.

5.2 First Normal Form (1NF)

• Rule: All attributes must contain only atomic (indivisible) values – no repeating groups or multi-valued cells.
Unnormalized table (violates 1NF – Courses column has multiple values):
RollNo Name Courses
101 Ravi Maths, Science
102 Sita English

Converted to 1NF (one course per row – atomic values):

RollNo Name Course
101 Ravi Maths
101 Ravi Science
102 Sita English

5.3 Second Normal Form (2NF)

• Rule: Table must be in 1NF, and every non-key attribute must depend on the whole composite primary key – no
partial dependency (applies only when the primary key has 2+ columns).
1NF table with composite key (RollNo + CourseID). StudentName depends only on RollNo, and CourseName depends
only on CourseID – both are partial dependencies:
RollNo CourseID StudentName CourseName Marks
101 C1 Ravi Maths 85
101 C2 Ravi Science 90
102 C3 Sita English 78

Split into 2NF (partial dependencies removed – three separate tables):

Student Course Enrollment
RollNo, StudentName CourseID, CourseName RollNo, CourseID, Marks

5.4 Third Normal Form (3NF)

• Rule: Table must be in 2NF, and it must have no transitive dependency – no non-key attribute should depend on
another non-key attribute.
2NF table (single key RollNo). DeptName and HOD depend on DeptID, not directly on RollNo – transitive dependency
(RollNo → DeptID → DeptName, HOD):
RollNo Name DeptID DeptName HOD
101 Ravi D1 CS Mr. Sharma
102 Sita D2 IT Ms. Gupta

Split into 3NF (transitive dependency removed – two separate tables):

Student Department
RollNo, Name, DeptID DeptID, DeptName, HOD

★ Exam tip: Very high-weightage topic (asked almost every year, 5 marks). Write the rule in one line + draw the
before/after table for 1NF, 2NF and 3NF. Key words to always mention: atomic value (1NF), partial dependency (2NF),
transitive dependency (3NF).

6. Centralized vs Distributed Database

6.1 Centralized Database

A database that is stored and maintained at a single physical location (single server), and accessed by users through a
network.
• Advantages: Easy to maintain, better data integrity, low cost, tight security control.
• Disadvantages: Single point of failure, slower access for geographically distant users, high traffic load on server.

6.2 Distributed Database

A database in which data is stored across multiple physical locations/servers, but appears as a single database to the user.
• Advantages: Faster local access, reliability (no single point of failure), scalability, supports geographically dispersed
users.
• Disadvantages: Complex to design/manage, higher cost, harder to maintain consistency.

6.3 Difference between Centralized and Distributed Database

Basis Centralized Database Distributed Database
Data Storage Stored at a single location/server Stored across multiple locations/servers
Access Speed Slower for remote/distant users Faster – data kept close to users
Reliability Single point of failure More reliable – no single failure point
Cost Comparatively lower Higher setup and maintenance cost
Complexity Simple to design and manage Complex to design, manage and synchronize
Data Sharing Difficult over long distance Efficient for geographically dispersed users

7. Difference between DBMS and RDBMS

Basis DBMS RDBMS
Data Storage Stores data as files/navigational structure Stores data in tables (rows & columns)
Relationship No relationship between data is maintained Data is related using primary/foreign keys
Basis DBMS RDBMS
Redundancy Data redundancy is common Redundancy is minimized through
normalization
Number of Users Supports single user, generally Supports multiple users simultaneously
ACID Properties Not necessarily followed Follows ACID properties for reliability
Examples File systems, XML DB MySQL, Oracle, SQL Server, PostgreSQL

★ Exam tip: DBMS is the broader concept; RDBMS is a type of DBMS that stores data in relational (tabular) form with
defined relationships.
8. Database Security & Data Integrity

8.1 Database Security

Database security refers to the collective measures used to protect a database from unauthorized access, misuse,
modification or destruction of data.
• Authentication: verifying user identity through username/password before granting access.
• Authorization: granting specific access rights/permissions to authenticated users.
• Encryption: converting data into unreadable form to protect it during storage/transmission.
• Auditing: tracking and logging user activities on the database for accountability.
• Backup & Recovery: regularly saving copies of data to restore it after loss or crash.

8.2 Data Integrity

Data integrity refers to the accuracy, consistency and reliability of data stored in a database throughout its lifecycle.
• Entity Integrity: ensures the primary key of a table is unique and not NULL.
• Referential Integrity: ensures foreign key values match a valid primary key value in the related table (or are
NULL).
• Domain Integrity: ensures all values in a column fall within a defined valid range/data type (constraints).
• User-defined Integrity: additional business rules defined by the user/organisation, not covered by the above three.

9. Database Administrator (DBA)

The DBA is the person responsible for the overall management, control, security and performance of the database
system.

Roles / Responsibilities of DBA

1. Installing and configuring the DBMS software.
2. Creating and maintaining the database schema (design).
3. Managing user accounts, access rights and security permissions.
4. Monitoring database performance and optimizing queries.
5. Taking regular backups and handling recovery after failure/crash.
6. Ensuring data integrity and enforcing constraints.
7. Planning for storage capacity and future growth.
★ Exam tip: “Who is DBA? List the roles of DBA” is a very frequently repeated 5-mark question – memorize at least 5
points.

10. Introduction to SQL

SQL (Structured Query Language) is a standard language used to create, manage, and manipulate relational databases –
to define structures, insert/update/delete data, and retrieve information through queries.

10.1 What can SQL do?

• Execute queries and retrieve data from a database (SELECT).
• Create new databases, tables, views, indexes and stored procedures.
• Insert, update and delete records within tables.
• Set permissions/grant access on tables, views and procedures.
• Enforce constraints/rules to maintain data integrity.
10.2 Advantages of SQL
• Simple, English-like syntax – easy to learn and use.
• No coding required for basic data operations.
• Standardized language supported by almost all RDBMS.
• Capable of handling large amounts of data efficiently.
• Portable – can run on different platforms/computers/servers.

10.3 SQL Data Types (commonly used)

Data Type Description
INT / INTEGER Whole numbers (e.g. Roll No, Age)
FLOAT / DECIMAL Numbers with decimal points (e.g. Marks, Price)
CHAR(n) Fixed-length character string
VARCHAR(n) Variable-length character string (e.g. Name)
DATE Stores date values (YYYY-MM-DD)
BOOLEAN Stores TRUE/FALSE values

10.4 Overview of Fundamental SQL Commands

(a) DDL Commands
CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(25), Class INT );
ALTER TABLE Student ADD Email VARCHAR(30); DROP TABLE Student;

(b) Specifying Constraints

• NOT NULL: ensures a column cannot have a NULL (empty) value.
• UNIQUE: ensures all values in a column are different from each other.
• PRIMARY KEY: combination of NOT NULL + UNIQUE; uniquely identifies each record.
• FOREIGN KEY: links a column to the primary key of another table to maintain referential integrity.
• CHECK: restricts the values that can be entered into a column based on a condition.
• DEFAULT: sets an automatic default value for a column when no value is specified.
CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(25) NOT NULL, Email
VARCHAR(30) UNIQUE, Age INT CHECK (Age > 0), ClassID INT DEFAULT 1, FOREIGN KEY
(ClassID) REFERENCES Class(ClassID) );

(c) Index Statements

CREATE INDEX idx_name ON Student (Name); DROP INDEX idx_name ON Student;
An index speeds up data retrieval from a table but slightly slows down INSERT/UPDATE operations (extra maintenance
overhead).
(d) DML Commands
INSERT INTO Student (RollNo, Name, Class) VALUES (101, 'Ravi', 12); SELECT Name, Class
FROM Student; SELECT * FROM Student WHERE Class = 12; UPDATE Student SET Class = 11
WHERE RollNo = 101; DELETE FROM Student WHERE RollNo = 101;
• SELECT statement: retrieves specified columns/rows from one or more tables.
• WHERE clause: filters records based on a specified condition (e.g. WHERE Class = 12).

10.5 MySQL and SQL Server

• MySQL: a free, open-source RDBMS widely used for web applications; uses SQL as its query language.
• SQL Server: a commercial RDBMS developed by Microsoft, commonly used in enterprise applications.
Quick Revision Summary
• Data → Information → Database → DBMS: build-up of concepts.
• DDL defines structure (CREATE, ALTER, DROP); DML manipulates data (SELECT, INSERT, UPDATE,
DELETE).
• Primary Key uniquely identifies a record; Foreign Key links two tables.
• 4 Database Models: Hierarchical, Network, Relational, Object-Oriented.
• Normalization: 1NF (atomic values) → 2NF (no partial dependency) → 3NF (no transitive dependency).
• Centralized DB = single location; Distributed DB = multiple locations, higher reliability.
• RDBMS = DBMS + tabular structure + relationships (keys) + ACID properties.
• Data Integrity types: Entity, Referential, Domain, User-defined.
• DBA manages security, backup, performance and schema of the database.
• SQL constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.