Q1: Traditional File System vs.
Database Management System (DBMS)
Differences
Database Management System
Feature Traditional File System
(DBMS)
Storage Structure Data stored in separate files Data stored in tables within databases
Data High redundancy due to Controlled redundancy through
Redundancy duplicate data normalization
Ensures data consistency through
Data Integrity Limited integrity checks
constraints
Provides authentication, authorization,
Data Security Minimal security features
and encryption
Slow and complex file
Data Retrieval Efficient retrieval using SQL queries
searching
Difficult due to file locking Allows concurrent access with
Multi-user Access
issues transaction control
Backup & Automated backup and recovery
Manual and error-prone
Recovery mechanisms
Storing records in CSV or Managing student records in MySQL or
Example
TXT files PostgreSQL
Why DBMS is Better
Better Data Consistency: Enforces rules to maintain accuracy.
Efficient Querying: Uses SQL for quick retrieval.
Minimized Redundancy: Uses normalization.
Enhanced Security: Provides user authentication.
Backup & Recovery: Automated processes ensure safety.
Q2: DDL and DML Commands in SQL
1. DDL (Data Definition Language)
CREATE (Defines a table)
CREATE TABLE students (
rollno INT PRIMARY KEY,
name VARCHAR(50),
subject VARCHAR(50),
state VARCHAR(50)
);
ALTER (Modifies a table)
ALTER TABLE students ADD COLUMN age INT;
2. DML (Data Manipulation Language)
INSERT (Adds new records)
INSERT INTO students (rollno, name, subject, state)
VALUES (1, 'John Doe', 'Math', 'Gujarat');
UPDATE (Modifies existing records)
UPDATE students SET state = 'Maharashtra' WHERE rollno = 1;
Q3: SQL Queries
(a) Create Table
CREATE TABLE student (
rollno INT PRIMARY KEY,
name VARCHAR(50),
subject VARCHAR(50),
state VARCHAR(50)
);
(b) Insert Records
INSERT INTO student (rollno, name, subject, state) VALUES
(1, 'Alice', 'Physics', 'Gujarat'),
(2, 'Bob', 'Math', 'Delhi'),
(3, 'Charlie', 'Chemistry', 'Gujarat');
(c) Display students from Gujarat
SELECT name FROM student WHERE state = 'Gujarat';
(d) Use ORDER BY and DISTINCT
SELECT DISTINCT subject FROM student ORDER BY subject ASC;
Q4: SQL Clauses and Operators
(a) GROUP BY and HAVING
GROUP BY groups similar data.
HAVING filters aggregated results.
Example:
SELECT subject, COUNT(*) AS total_students FROM student
GROUP BY subject HAVING COUNT(*) > 1;
(b) Logical Operators: AND, OR, NOT
AND: Both conditions must be true.
OR: At least one condition must be true.
NOT: Negates a condition.
Example:
SELECT * FROM student WHERE state = 'Gujarat' AND subject = 'Physics';
(c) Special Operators: IN, BETWEEN, LIKE
IN selects values within a set.
SELECT * FROM student WHERE state IN ('Gujarat', 'Delhi');
BETWEEN selects a range.
SELECT * FROM student WHERE rollno BETWEEN 1 AND 3;
LIKE searches patterns.
SELECT * FROM student WHERE name LIKE 'A%';
Q5: Dr. E.F. Codd’s Rules for RDBMS
Dr. Edgar F. Codd formulated 12 rules to define a Relational Database Management System
(RDBMS). Key rules include:
1. Information Rule – All data must be stored in tables.
2. Guaranteed Access Rule – Each data element must be uniquely accessible.
3. Systematic Treatment of NULL Values – Distinction between missing values and
empty values.
4. Dynamic Online Catalog – Metadata should be managed like other data.
5. Comprehensive Data Sublanguage – Must support SQL for access.
6. View Updating Rule – Allows modifications via views.
7. High-Level Insert, Update, Delete – Should support set operaptions.
8. Physical Data Independence – Changes in physical storage shouldn’t affect the
application.
9. Logical Data Independence – Changing schema shouldn’t disrupt existing
applications.
10. Integrity Independence – Constraints must be separate from application logic.
11. Distribution Independence – Should work regardless of database distribution.
12. Non-Subversion Rule – No bypass of relational integrity using lower-level languages
