Database Management Systems - BSCS Notes

Database Management Systems (DBMS) — Complete BSCS Notes

Basic Database Concepts

Database: A database is an organized collection of related data stored in a structured form so it can be easily accessed, managed, and updated.
Example: A university database storing students, teachers, courses, and marks.
DBMS: Database Management System is software that helps create, store, retrieve, update, and manage databases.
Examples: MySQL, Oracle, SQL Server, PostgreSQL.
Data: Raw facts such as names, numbers, or marks.
Example: Ali, 23, 3.45 CGPA.
Information: Processed and meaningful data.
Example: Result card of a student.

Entity Relationship Model

Entity: A real-world object or thing.
Example: Student, Course, Teacher.
Attribute: Properties of an entity.
Example: Student has Name, Roll No, Department.
Relationship: Association between entities.
Example: Student enrolls in Course.
Cardinality: Defines relationship count such as One-to-One, One-to-Many, Many-to-Many.
Example: One teacher teaches many students.

Relational Data Model & Algebra

Relation: A table in database.
Example: Students table.
Tuple: A row in a table.
Example: One student record.
Attribute: A column in table.
Example: Name column.
Relational Algebra: Mathematical operations on relations such as Selection, Projection, Join.
Example: Select all students with CGPA > 3.0.

SQL and RDBMS

SQL: Structured Query Language used to interact with relational databases.
DDL: CREATE, ALTER, DROP
Example: CREATE TABLE Students(...)
DML: INSERT, UPDATE, DELETE
Example: INSERT INTO Students VALUES(...)
DQL: SELECT
Example: SELECT * FROM Students;
RDBMS: Stores data in related tables using primary and foreign keys.

Functional Dependencies & Normal Forms

Functional Dependency: One attribute depends on another.
Example: RollNo → StudentName.
1NF: All attributes contain atomic values only.
2NF: Removes partial dependency.
3NF: Removes transitive dependency.
BCNF: Every determinant must be a candidate key.

Transactions, Concurrency & Recovery

Transaction: A complete unit of work in DBMS.
Example: ATM withdrawal.
ACID: Atomicity, Consistency, Isolation, Durability.
Concurrency Control: Prevents conflicts when multiple users access same data simultaneously.
Example: Locking and timestamp methods.
Recovery: Restores database after crash using logs and checkpoints.

Database Security & Authorization

Authentication: Verifying user identity through username and password.
Authorization: Giving specific access rights.
Example: Admin can delete data, student can only view.
Security: Includes encryption, roles, privileges, and backup.

Physical Database Design

Storage & File Structure: Defines how data is physically stored on disk.
Indexed Files: Speeds up searching through indexes.
B-Trees: Balanced tree structure used for fast searching and insertion.
Dense Index: Every record has an index entry.
Variable Length Records: Records with fields of different sizes.
Efficiency & Tuning: Improves performance using indexing and query optimization.

Small Group Project

Library Management System / Student DB