1.
Introduction to Database Systems
1.1 Concept of Data, Information, Database, and Database Management System
(DBMS)
● Data: Raw facts (e.g., a student’s name: "Alex", roll number: 101).
● Information: Processed data with meaning (e.g., "Alex with roll number 101
scored 85%").
● Database: Organized collection of data in tables (e.g., a school database with
student details).
● DBMS: Software to manage databases (e.g., MySQL, Oracle) for storing,
retrieving, and updating data.
Example: A database is like a school library—books are data, the catalog is the
database, and the librarian is the DBMS!
Exam Tip: Define each term with an example.
1.2 Limitations of File System
● File System: Storing data in files (e.g., Excel sheets).
● Limitations:
1. Data Redundancy: Same data repeated (e.g., student info in multiple
files).
2. Data Inconsistency: Updates in one file don’t reflect in others.
3. No Security: Anyone can access files.
4. No Concurrent Access: Multiple users can’t work simultaneously.
5. Slow Retrieval: Searching files takes time.
Example: Storing notes in separate notebooks without labels—finding a
topic is tough!
Exam Tip: List 5 limitations with examples.
1.3 Advantages and Disadvantages of Database System
● Advantages:
1. Reduces redundancy and inconsistency.
2. Supports concurrent access.
3. Ensures security with permissions.
4. Faster data retrieval.
5. Easy to update.
● Disadvantages:
1. High setup cost (software, hardware).
2. Complex to design.
3. Risk of data loss if the system crashes.
Example: A database is like a magical backpack—it holds everything
neatly, but if you lose it, everything’s gone!
Exam Tip: List 4–5 advantages and 2–3 disadvantages with examples.
1.4 Application of Database System
● Applications:
1. Schools: Student records, marks, attendance.
2. Banks: Accounts, transactions.
3. E-commerce: Orders, products (e.g., Amazon).
4. Hospitals: Patient records.
5. Social Media: User profiles (e.g., Instagram).
Example: A database is the brain of your favorite app, remembering your
likes and posts!
Exam Tip: Mention 5 real-world applications with examples.
1.5 Types of Database Users
● Types:
1. End Users: Use the database via apps (e.g., students checking marks).
2. Database Administrators (DBA): Manage the database (e.g., set
permissions).
3. Application Programmers: Write programs for the database.
4. System Analysts: Design the database structure.
Example: A database is a pizza party—end users eat, DBAs ensure
enough pizza, programmers cook, and analysts plan the menu!
Exam Tip: Define each user type with their role.
1.6 DBMS Architecture
● 3-Tier Architecture:
1. External Level: What the user sees (e.g., a student sees their marks).
2. Conceptual Level: Overall structure (e.g., tables for students, marks).
3. Internal Level: How data is stored (e.g., files, indexes).
Example: A restaurant—you (external) see the menu, the chef
(conceptual) knows recipes, and the kitchen (internal) has ingredients.
Exam Tip: Draw a 3-tier diagram and explain each level.
1.7 Database Model
● Models:
1. Hierarchical: Tree-like structure (e.g., parent-child).
2. Network: Graph-like structure (more flexible).
3. Relational: Tables (most common, e.g., MySQL).
4. Object-Oriented: Data as objects (used in programming).
Example: A relational model is like your class timetable—subjects in
tables!
Exam Tip: Focus on the relational model and briefly explain others.
1.8 Database Schema
● Schema: Structure of the database (e.g., table names, columns).
● Types:
1. Physical Schema: How data is stored on disk.
2. Logical Schema: Design of tables and relationships.
3. View Schema: What users see (subset of data).
Example: A schema is a blueprint of your house, showing where rooms
(tables) are!
Exam Tip: Explain types with examples.
2. Entity-Relationship Model (ER-Model)
2.1 Illustrate ER-Model
● ER Model: Diagram showing entities and relationships.
● Components:
1. Entity: Object (e.g., Student).
2. Relationship: Connection (e.g., Student enrolls in Course).
3. Attributes: Properties (e.g., Name, Roll No).
Example: An ER model is a family tree—people (entities) are related
(relationships) with traits (attributes).
Exam Tip: Draw an ER diagram (e.g., Student-Course).
2.2 Components of ER-Model
● Components:
1. Entity: Rectangle (e.g., Student).
2. Relationship: Diamond (e.g., Enrolls).
3. Attributes: Oval (e.g., Name).
4. Connecting Lines: Show relationships.
Example: An ER model is a treasure map—entities are islands,
relationships are bridges, attributes are treasures!
Exam Tip: List components and draw a diagram.
2.3 Entity, Weak Entity, Entity Set
● Entity: Single object (e.g., a student named Alex).
● Weak Entity: Depends on another (e.g., Dependent of an Employee).
● Entity Set: Group of entities (e.g., all Students).
Example: A weak entity is a baby bird—it needs its parent (strong entity)!
Exam Tip: Explain the difference with examples.
2.4 Attributes and Types
● Attributes: Properties of an entity.
● Types:
1. Simple: Not divisible (e.g., Age).
2. Composite: Divisible (e.g., Name: First Name + Last Name).
3. Single-Valued: One value (e.g., Roll No).
4. Multi-Valued: Multiple values (e.g., Phone Numbers).
5. Derived: Calculated (e.g., Age from Date of Birth).
Example: Attributes describe your pet—name (simple), color
(single-valued), tricks (multi-valued)!
Exam Tip: List all types with examples.
2.5 Relationship and Types
● Types:
1. One-to-One: Person to Passport.
2. One-to-Many: Teacher to Students.
3. Many-to-Many: Students to Courses.
Example: One-to-many is a teacher with many students—she teaches
all, but each student has one teacher!
Exam Tip: Draw examples for each type.
2.6 Mapping Cardinalities
● Cardinalities:
1. 1:1: One-to-One (e.g., Person to Aadhar Card).
2. 1:N: One-to-Many (e.g., Department to Employees).
3. N:M: Many-to-Many (e.g., Students to Courses).
Example: Mapping is a dance party—1:1 is a couple, 1:N is a leader with
a group, N:M is a big group dance!
Exam Tip: Explain each cardinality with a diagram.
2.7 Keys in DBMS
● Keys:
1. Primary Key: Uniquely identifies (e.g., Roll No).
2. Foreign Key: Links tables (e.g., Dept ID in Employee).
3. Candidate Key: Possible primary keys (e.g., Roll No or Email).
4. Super Key: Set of columns to identify (e.g., Roll No + Name).
Example: A primary key is your school ID—unique to you!
Exam Tip: Define each key with examples.
3. Relational Model
3.1 Relational Model
● Relational Model: Data in tables (relations) with rows (tuples) and columns
(attributes).
● Features:
1. Each row is unique (primary key).
2. Columns have unique names.
3. Values in a column are of the same type.
Example: A table is your class attendance sheet—rows are students,
columns are details!
Exam Tip: Explain the structure with an example.
3.2 Key Concepts in Relational Model
● Concepts:
1. Tables: Store data (e.g., Student table).
2. Tuple: Row (e.g., one student’s record).
3. Cardinality: Number of rows.
4. Column/Attribute: Field (e.g., Name).
5. Degree: Number of columns.
6. Domain: Possible values (e.g., Age: 0–100).
7. Relational Instance: Data at a specific time.
8. Relational Schema: Structure (e.g., Student(Roll No, Name)).
9. Relational Key: Primary or foreign key.
Example: A table is a treasure chest—rows are treasures, columns are
labels, keys unlock it!
Exam Tip: Define each term with an example.
3.3 Properties of Relations
● Properties:
1. Each row is unique.
2. Columns have unique names.
3. Values in a column are from the same domain.
4. Order of rows/columns doesn’t matter.
5. Each cell has a single value (atomicity).
Example: A table is a Lego set—each piece fits perfectly, no duplicates in
a row!
Exam Tip: List all properties with an example.
3.4 Mapping ER-Model to Relational Model
● Steps:
1. Convert entities to tables.
2. Convert attributes to columns.
3. Use primary keys.
4. For relationships, create foreign keys or new tables (many-to-many).
Example:
● ER: Student enrolls in Course (many-to-many).
● Relational: Student(Roll No, Name), Course(Course ID, Name), Enrollment(Roll
No, Course ID).
Example: Mapping is turning a sketch (ER) into a building (tables)!
Exam Tip: Practice converting an ER diagram to tables.
4. SQL (Structured Query Language) Overview
4.1 Illustrate SQL
● SQL: Language to interact with relational databases.
● Uses:
1. Create tables (DDL).
2. Insert, update, delete data (DML).
3. Query data (SELECT).
4. Control access (DCL).
Example: SQL is a magic wand—say the spell (query), and the database
responds!
Exam Tip: Define SQL and list its uses.
4.2 Types of SQL
● Types:
1. DDL: Define structure (e.g., CREATE, ALTER).
2. DML: Manage data (e.g., INSERT, UPDATE).
3. DCL: Control access (e.g., GRANT).
4. TCL: Manage transactions (e.g., COMMIT).
5. DQL: Retrieve data (e.g., SELECT).
Example: SQL types are a chef’s tools—DDL builds the kitchen, DML
cooks, DQL serves!
Exam Tip: List all types with one example command.
4.3 Commands of DDL, DCL, and DML
● DDL: CREATE TABLE Student (RollNo INT, Name VARCHAR(50));
● DML:
○ INSERT INTO Student VALUES (101, 'Alex');
○ UPDATE Student SET Name = 'Alexa' WHERE RollNo = 101;
○ DELETE FROM Student WHERE RollNo = 101;
● DCL: GRANT SELECT ON Student TO User1;
Example: DDL builds a toy house, DML plays with toys, DCL decides who can
play!
Exam Tip: Write 2–3 commands for each type.
4.4 SQL Clauses
● Clauses:
1. SELECT: Retrieve data (e.g., SELECT * FROM Student;).
2. WHERE: Filter (e.g., SELECT * FROM Student WHERE RollNo = 101;).
3. GROUP BY: Group data (e.g., SELECT Dept, COUNT(*) FROM
Employee GROUP BY Dept;).
4. HAVING: Filter groups (e.g., HAVING COUNT(*) > 5;).
5. ORDER BY: Sort (e.g., SELECT * FROM Student ORDER BY Name;).
Example: SQL clauses sort candies—SELECT picks them, WHERE
chooses red ones, ORDER BY arranges by size!
Exam Tip: Write a query using multiple clauses.
4.5 SQL Joins
● Joins:
1. INNER JOIN: Matches records
(e.g., SELECT * FROM Student INNER JOIN Course ON
Student.RollNo = Course.RollNo;).
2. LEFT JOIN: All records from the left table.
3. RIGHT JOIN: All records from the right table.
4. FULL JOIN: All records from both tables.
Example: Joins are a school dance—INNER JOIN pairs matching
partners, LEFT JOIN includes all boys!
Exam Tip: Write queries for INNER and LEFT JOIN.
4.6 SQL Views
● View: Virtual table from a query.
● Example: CREATE VIEW TopStudents AS SELECT * FROM Student WHERE
Marks > 90;
● Uses: Simplify queries, restrict access.
Example: A view is a window—you see part of the room without touching it!
Exam Tip: Explain views and write a CREATE VIEW query.
5. Relational Database Design
5.1 Functional Dependency
● Functional Dependency (FD): Relationship between attributes (e.g., RollNo →
Name).
● Example: RollNo → Name, Marks in a Student table.
Example: FD is a magic rule—know the RollNo, find the name!
Exam Tip: Define FD with an example.
5.2 Normalization
● Normalization: Removes redundancy and anomalies (insertion, deletion,
update).
● Steps:
1. 1NF: No repeating groups (atomic values).
2. 2NF: 1NF + no partial dependency.
3. 3NF: 2NF + no transitive dependency.
Example: Normalization organizes your messy desk—everything gets its
place!
Exam Tip: Explain the purpose and list normal forms.
5.3 Types of Normalization
● Example:
○ Unnormalized: Student(RollNo, Name, Courses).
○ 1NF: Split Courses into rows.
○ 2NF: Remove partial dependency (split into Student and Course tables).
○ 3NF: Remove transitive dependency (separate Name if dependent).
Example: Normalizing sorts toys—1NF puts them in boxes, 2NF labels
boxes, 3NF organizes by type!
Exam Tip: Show normalization steps with a table.
General Exam Tips for Maximum Marks
1. Diagrams: Draw ER diagrams, tables, and architecture diagrams neatly.
2. Examples: Use real-world examples for every concept.
3. SQL Queries: Practice DDL, DML, and joins.
4. Definitions: Write clear definitions with keywords.
5. Practice: Solve past papers and create sample ER models and tables.
6. Database Transaction
6.1 Illustrate Transaction
● Transaction: A sequence of operations (e.g., read, write) that are treated as a
single unit in a database. It must either complete fully or not happen at all.
● Example: When you transfer money from Account A to Account B:
1. Deduct $100 from Account A.
2. Add $100 to Account B.
Both steps must happen together, or the transaction fails.
● Key Idea: Transactions ensure data consistency even if something goes wrong
(e.g., a system crash).
Fun Example: A transaction is like baking a cake—you mix ingredients, bake, and
decorate. If the oven breaks midway, you don’t serve half a cake; you start over!
Exam Tip: Define a transaction and provide a real-world example (e.g., banking).
6.2 Explain Concurrency in Transaction
● Concurrency: Multiple transactions running at the same time in a database.
● Why It’s Needed: Allows multiple users to work simultaneously (e.g., many
people accessing a bank database at once).
● Problems with Concurrency:
1. Lost Update: Two transactions update the same data, and one overwrites
the other (e.g., two users book the same seat).
2. Dirty Read: A transaction reads uncommitted data (e.g., seeing a balance
before a transfer is complete).
3. Inconsistent Retrieval: Reading partial changes during a transaction.
● Solution: Use concurrency control (e.g., locks, timestamps) to manage
access.
Fun Example: Concurrency is like a shared kitchen—multiple chefs (transactions) cook
at the same time, but they need rules to avoid mixing up recipes!
Exam Tip: Explain concurrency, list 2–3 problems, and mention a solution like locking.
6.3 Describe ACID Properties
● ACID Properties: Rules to ensure reliable transactions.
1. Atomicity: All operations in a transaction complete, or none do (e.g., both
debit and credit happen in a money transfer).
2. Consistency: Transaction brings the database from one valid state to
another (e.g., total money in the bank stays the same after a transfer).
3. Isolation: Transactions are independent—intermediate changes aren’t
visible to others (e.g., no one sees a half-done transfer).
4. Durability: Once a transaction is committed, changes are saved, even if
the system crashes (e.g., a completed transfer is recorded).
Fun Example: ACID is like a superhero team for transactions—Atomicity ensures all or
nothing, Consistency keeps things balanced, Isolation hides the mess, and Durability
saves the day!
Exam Tip: Define each ACID property with a banking example.
6.4 Elaborate State of Transaction
● Transaction States:
1. Active: Transaction is executing (e.g., deducting money from Account A).
2. Partially Committed: All operations are done, but not yet saved (e.g.,
money deducted and added, waiting to commit).
3. Committed: Transaction is complete, changes are saved (e.g., transfer is
finalized).
4. Failed: Transaction cannot proceed (e.g., insufficient funds).
5. Aborted: Transaction is undone (rolled back) after failure (e.g., money is
returned to Account A).
Fun Example: A transaction’s journey is like a student’s exam—Active (writing),
Partially Committed (finished writing), Committed (submitted), Failed (made a mistake),
Aborted (exam canceled)!
Exam Tip: Draw a transaction state diagram and explain each state with an example.
7. Database Backup, Recovery, and Security
7.1 Discuss Backup
● Backup: A copy of the database to restore it if something goes wrong (e.g.,
system crash, data corruption).
● Importance:
1. Protects against data loss.
2. Helps recover after failures.
3. Ensures business continuity.
● Example: A bank backs up its transaction data daily to recover if the system
fails.
Fun Example: A backup is like saving your game progress—if you lose a level, you can
restart from the last save point!
Exam Tip: Define backup, explain its importance, and give an example.
7.2 Discuss Type of Backup
● Types of Backup:
1. Full Backup: Copies the entire database (e.g., backing up all student
records).
■ Pros: Easy to restore.
■ Cons: Takes more time and space.
2. Incremental Backup: Copies only the data changed since the last backup
(e.g., only new student records).
■ Pros: Faster, less space.
■ Cons: Slower to restore (needs full backup + increments).
3. Differential Backup: Copies data changed since the last full backup (e.g.,
all changes since the last full backup).
■ Pros: Faster to restore than incremental.
■ Cons: Takes more space than incremental.
4. Mirror Backup: Exact copy of the database (e.g., a duplicate of the entire
system).
■ Pros: Quick to restore.
■ Cons: No history of changes.
Fun Example: Backups are like your study notes—Full Backup is copying all notes,
Incremental is adding new pages daily, Differential is copying all changes since the
start, and Mirror is a photocopy of everything!
Exam Tip: List all types, explain their pros and cons, and give examples.
7.3 Explain Reasons for Database Failure
● Reasons for Failure:
1. Hardware Failure: Disk crash, power outage (e.g., server shuts down
during a transaction).
2. Software Failure: Bugs in the DBMS or OS (e.g., a glitch corrupts data).
3. Human Error: Accidental deletion (e.g., a user deletes a table).
4. Network Failure: Connection issues (e.g., a transaction stops midway).
5. Malicious Attacks: Hacking, viruses (e.g., ransomware locks the
database).
Fun Example: Database failures are like a school event going wrong—lights go out
(hardware), the schedule has errors (software), someone spills juice (human error), or a
prank disrupts everything (attack)!
Exam Tip: List 4–5 reasons with examples.
7.4 Illustrate Methods of Database Backup
● Methods:
1. Manual Backup: Copying data manually (e.g., exporting a table to a file).
2. Automated Backup: Scheduled backups using DBMS tools (e.g., daily
backup at midnight).
3. Cloud Backup: Storing backups on the cloud (e.g., using Google Drive or
AWS).
4. Tape Backup: Storing on magnetic tapes (older method, used for large
databases).
5. Hot Backup: Backup while the database is active (e.g., during
transactions).
6. Cold Backup: Backup when the database is offline (e.g., system is shut
down).
Fun Example: Backup methods are like saving your drawings—Manual is sketching by
hand, Automated is a printer saving daily, Cloud is storing online, Tape is an old
sketchbook, Hot is drawing while painting, and Cold is waiting till you’re done!
Exam Tip: List 5–6 methods with examples and explain when each is used.
7.5 Discuss the Concept of Recovery, Redo/Undo
● Recovery: Restoring the database to a consistent state after a failure.
● Redo: Reapplying committed changes (e.g., a transaction that was completed
but not saved due to a crash).
● Undo: Rolling back uncommitted changes (e.g., a transaction that was
incomplete during a crash).
● How It Works:
○ DBMS uses a log file to track transactions (e.g., “deduct $100 from
Account A”).
○ After a crash, the system checks the log:
■ Redo: Reapplies committed transactions.
■ Undo: Reverses uncommitted transactions.
Fun Example: Recovery is like fixing a puzzle—Redo puts back pieces you finished,
Undo removes pieces you didn’t finish, and the log is your instruction manual!
Exam Tip: Define recovery, explain redo and undo with a transaction example, and
mention the role of the log file.
7.6 Illustrate Database Security
● Database Security: Protecting the database from unauthorized access,
corruption, or theft.
● Methods:
1. Authentication: Verify user identity (e.g., username and password).
2. Authorization: Grant specific permissions (e.g., only admins can delete
data).
3. Encryption: Encode data to prevent unauthorized access (e.g., encrypt
passwords).
4. Access Control: Restrict access to specific tables (e.g., students can’t
access teacher data).
5. Auditing: Track user actions (e.g., log who accessed the database).
Fun Example: Database security is like a school locker—Authentication is your key,
Authorization decides what you can store, Encryption hides your secrets, Access
Control limits who can open it, and Auditing checks who touched it!
Exam Tip: List 4–5 security methods with examples.
7.7 Discuss Common Threats in Database
● Threats:
1. Unauthorized Access: Hackers gaining entry (e.g., stealing student
records).
2. SQL Injection: Malicious code in queries (e.g., bypassing login with a
harmful query).
3. Data Theft: Stealing sensitive data (e.g., credit card details).
4. Data Corruption: Accidental or intentional damage (e.g., a virus alters
data).
5. Denial of Service (DoS): Overloading the database to crash it (e.g., too
many fake requests).
6. Insider Threats: Employees misusing access (e.g., a staff member leaks
data).
Fun Example: Database threats are like school pranks—someone sneaks in
(unauthorized access), messes with the schedule (SQL injection), steals snacks (data
theft), spills paint (corruption), or causes chaos (DoS)!
Exam Tip: List 5–6 threats with examples and suggest one prevention method for each.
