Topic 8

Introduction to Database Management System

A database is an organised collection of related data. A Database Management System (DBMS) is the software that manages that data — allowing users to create, read, update, and delete information efficiently. Every organisation — from a hospital to a bank — relies on databases.

A. What is a Database & DBMS?

Definition & Purpose

What databases and DBMS are, and why they exist

TermDefinition
DataRaw, unorganised facts and figures. E.g. "Ali", "25", "Karachi" — meaningless alone.
InformationProcessed, organised data that has meaning. E.g. "Ali is 25 years old and lives in Karachi."
DatabaseAn organised collection of related data stored electronically so it can be easily accessed, managed, and updated. Like a digital filing system.
DBMSDatabase Management System — software that creates, manages, and controls access to a database. Users interact with the database through the DBMS. Examples: MySQL, Oracle, MS Access, MS SQL Server, PostgreSQL.
Traditional files vs DBMSBefore DBMS: data stored in separate flat files — led to data redundancy (repeated data) and data inconsistency. DBMS solves both problems by storing data centrally.
Who uses DBMS?Hospitals (patient records), banks (account data), schools (student records), airlines (booking systems), governments (citizen records), e-commerce (product/order data).
Data vs Information
Data = raw unorganised facts. Information = processed meaningful data.
Database
Organised collection of related data stored electronically for easy access and management.
DBMS
Software managing the database. MySQL, Oracle, MS Access, SQL Server. Eliminates redundancy and inconsistency.
Traditional Files vs DBMS
Flat files caused data redundancy (repeated data) and inconsistency. DBMS stores data centrally — solves both problems.
Who Uses DBMS?
Hospitals (patient records) · Banks (accounts) · Schools (students) · Airlines (bookings) · E-commerce (orders)
👥 Users / Applications DBMS • Query Processor • Transaction Manager • Security Manager • Storage Manager 🗄️ Database Tables · Records Fields · Data Data stored & retrieved
UsersUsers and applications send requests to the DBMS
DBMSManages queries, transactions, security, and storage
DatabaseThe actual data — tables, records, fields stored on disk
Data + Processing = Information. Traditional flat files problem = data redundancy (repeated) and data inconsistency. DBMS solves both. Examples: MySQL, Oracle, MS Access, SQL Server, PostgreSQL.

B. Key Database Terms

Essential Vocabulary

The terms every DBMS exam question uses

TermDefinitionExample
TableThe basic structure in a relational database — a grid of rows and columns where related data is stored. Also called a Relation.Students table, Products table, Orders table
Record (Row / Tuple)A single, complete entry in a table — all the data about one person or item. Each row = one record.One student's complete details (name, age, roll no)
Field (Column / Attribute)A single piece of information about each record. Each column stores one type of data.Name, Age, Address, Roll Number
Primary Key (PK)A field (or combination of fields) that uniquely identifies each record in a table. No two records can have the same primary key. Cannot be NULL.Roll Number (every student has a unique roll no)
Foreign Key (FK)A field in one table that references the Primary Key of another table — used to create relationships between tables.Student_ID in the Marks table refers to the Students table
QueryA question or request to retrieve specific data from the database. Written in SQL (Structured Query Language)."Show all students with marks > 60"
SchemaThe overall logical structure/design of a database — what tables exist, their columns, data types, and relationships.Blueprint of the entire database
IndexA data structure that speeds up searching/retrieval of records. Like a book's index — allows faster lookup.Index on Name field for fast name searches
Data RedundancyStoring the same data in multiple places. Problem with traditional flat files. DBMS eliminates this.Student address stored in both Marks and Library tables
Data IntegrityAccuracy and consistency of data in the database. DBMS enforces rules to maintain integrity.Age field cannot contain text; marks cannot exceed 100
CRUDThe four basic database operations: Create · Read · Update · DeleteINSERT · SELECT · UPDATE · DELETE (SQL commands)

Example: STUDENTS Table

🔑 Roll_No (PK) Name Age City Marks
101Ali Hassan22Karachi85
102Sara Ahmed21Lahore92
103Bilal Khan23Quetta78
104Amna Malik22Islamabad95

🔑 Roll_No is the Primary Key — unique for every student, cannot be repeated or NULL. Each row = one Record. Each column = one Field.

Table / Relation
Grid of rows and columns storing related data. Basic unit of a relational database.
Record = Row = Tuple
One complete entry. All data about one person or item.
Field = Column = Attribute
One type of information (Name, Age, City). Each column stores one data type.
Primary Key (PK)
Uniquely identifies each record. Cannot be NULL or repeated. E.g. Roll Number.
Foreign Key (FK)
Links one table to another. References the PK of another table. Creates relationships.
Query
A request to retrieve specific data from the database. Written in SQL. E.g. "Show all students with marks > 60".
Schema
The overall logical structure/blueprint of the database — what tables exist, their columns, data types, and relationships.
Index
Data structure that speeds up searching. Like a book index — allows faster lookup without scanning every record.
Data Redundancy & Integrity
Redundancy = same data in multiple places (problem). Integrity = accuracy and consistency enforced by DBMS constraints.
CRUD
Create · Read · Update · Delete — the four basic database operations.
⚡ Exam Tips — Row = Record = Tuple. Column = Field = Attribute. Primary Key = uniquely identifies each record, cannot be NULL. Foreign Key = links two tables. Table = Relation. CRUD = Create, Read, Update, Delete. Data Redundancy = repeated data (problem of flat files).

C. Types of Database Models

How Data is Organised

Five models — each with a different structure

ModelStructureKey FeatureExample
Hierarchical Tree structure — parent/child relationships. One parent can have many children. Data accessed from root to leaf. Fast access but rigid — child can only have ONE parent. Like an org chart. IBM IMS, early mainframe databases
Network More flexible than hierarchical — a child record can have multiple parents. Uses a graph structure. Flexible relationships but complex to design and navigate. IDS (Integrated Data Store), IDMS
Relational (RDBMS) Data stored in tables (relations). Tables linked by Primary Keys and Foreign Keys. Uses SQL. Most popular today. Simple, flexible, powerful. Data independence. Easy to query. MySQL, Oracle, MS SQL Server, MS Access, PostgreSQL
Object-Oriented Data stored as objects (like OOP in programming). Objects have attributes and methods. Good for complex data (multimedia, CAD). Combines database with OOP concepts. ObjectDB, db4o
NoSQL Not a relational table structure. Used for unstructured/semi-structured data. Types: document, key-value, graph, column. Highly scalable. Used for big data and real-time web applications. MongoDB (document), Redis (key-value), Cassandra
Hierarchical Model
Tree structure, parent-child. One parent per child. Fast but rigid. IBM IMS.
Network Model
Graph structure. A child can have multiple parents — more flexible than hierarchical but complex to design.
Relational Model (RDBMS)
Tables linked by keys. Uses SQL. Most popular today. MySQL, Oracle, MS Access.
Object-Oriented Model
Data stored as objects with attributes and methods. Good for complex/multimedia data. Combines OOP with databases.
NoSQL
Not table-based. For big data and real-time web apps. Types: document, key-value, graph. MongoDB, Redis, Cassandra.
⚡ Exam Tips — Relational DBMS = most widely used today = uses tables + SQL. RDBMS examples: MySQL, Oracle, MS Access, SQL Server. The relationship between tables = using Primary Key and Foreign Key. NoSQL = MongoDB (document store).

D. SQL — Structured Query Language

The Language of Databases

SQL is used to communicate with relational databases

SQL CommandCategoryPurposeExample
SELECTDQL / DMLRetrieve data from a tableSELECT * FROM Students;
INSERT INTODMLAdd new records to a tableINSERT INTO Students VALUES (105,'Usman',22);
UPDATEDMLModify existing recordsUPDATE Students SET Marks=90 WHERE Roll_No=101;
DELETEDMLRemove records from a tableDELETE FROM Students WHERE Roll_No=103;
CREATE TABLEDDLCreate a new tableCREATE TABLE Students (Roll_No INT PRIMARY KEY, Name VARCHAR(50));
DROP TABLEDDLDelete an entire tableDROP TABLE Students;
WHEREClauseFilter results based on a conditionSELECT * FROM Students WHERE Marks > 80;
ORDER BYClauseSort results in ascending (ASC) or descending (DESC) orderSELECT * FROM Students ORDER BY Marks DESC;
SQL CategoryFull NameCommandsPurpose
DDLData Definition LanguageCREATE, DROP, ALTER, TRUNCATEDefine/modify database structure (tables, schemas)
DMLData Manipulation LanguageINSERT, UPDATE, DELETE, SELECTManipulate data within tables
DCLData Control LanguageGRANT, REVOKEControl user access permissions
TCLTransaction Control LanguageCOMMIT, ROLLBACK, SAVEPOINTManage database transactions
SQL
Structured Query Language. The standard language for managing and querying relational databases.
SELECT
Retrieve data. SELECT * FROM Students; — retrieves all records. Add WHERE to filter: WHERE Marks > 80
INSERT / UPDATE / DELETE
INSERT INTO = add record. UPDATE SET = modify record. DELETE FROM = remove record. All use WHERE to target specific rows.
CREATE / DROP TABLE
CREATE TABLE defines a new table with columns. DROP TABLE permanently deletes the entire table. Both are DDL commands.
WHERE & ORDER BY
WHERE filters rows by condition. ORDER BY sorts results — ASC (ascending) or DESC (descending).
DDL vs DML
DDL = structure (CREATE, DROP, ALTER). DML = data (SELECT, INSERT, UPDATE, DELETE).
DCL & TCL
DCL = Data Control (GRANT, REVOKE user permissions). TCL = Transaction Control (COMMIT, ROLLBACK).
SELECT with WHERE
SELECT * FROM Students WHERE Marks > 80; — retrieves all students scoring above 80.
⚡ Exam Tips — SQL = Structured Query Language. SELECT = retrieve data. INSERT = add data. UPDATE = modify data. DELETE = remove data. DDL commands change structure. DML commands change data. WHERE = condition filter. ORDER BY = sort results.

E. Advantages & Disadvantages of DBMS

DBMS vs Traditional File System

Why DBMS replaced flat file storage

Advantage of DBMSExplanation
No Data RedundancyData stored once in a central location — no repetition in multiple files.
Data ConsistencyBecause data is stored once, updating it updates everywhere — no contradictory information.
Data SharingMultiple users can access the same database simultaneously with proper access controls.
Data SecurityDBMS provides user authentication, access levels, and encryption to protect sensitive data.
Data IntegrityRules (constraints) ensure data accuracy — e.g. age cannot be negative, email must be unique.
Backup & RecoveryDBMS automatically backs up data and can recover it after system failures.
Easy QueryingSQL allows complex data retrieval with simple commands — no programming required.
Data IndependenceApplications don't need to know how data is physically stored — changes to storage don't break applications.
Disadvantage of DBMSExplanation
High costDBMS software (Oracle, SQL Server) can be very expensive. Hardware requirements are also higher.
ComplexityDesigning and administering a database requires trained specialists (Database Administrators — DBAs).
Single point of failureIf the central database server fails, all users lose access. Requires good backup and recovery systems.
PerformanceFor very simple applications, a full DBMS may be slower than a simple flat file.
No Redundancy & Consistency
Data stored once centrally — no repetition. Updating it updates everywhere — no contradictions.
Data Sharing
Multiple users can access the same database simultaneously with proper access control.
Data Security
User authentication, access levels, and encryption protect sensitive data from unauthorised users.
Data Integrity
Constraints ensure accuracy — age cannot be negative, email must be unique, marks cannot exceed 100.
Backup & Recovery
DBMS automatically backs up data and can restore it after system failures or crashes.
Easy Querying & Data Independence
SQL allows complex retrieval with simple commands. Applications don't need to know how data is physically stored.
Key Disadvantages
High cost · Complexity (needs DBA) · Single point of failure if server goes down
DBA
Database Administrator — the person responsible for designing, maintaining, backing up, and securing the database.
⚡ Exam Tips — Main advantage of DBMS = eliminates data redundancy and inconsistency. DBA = Database Administrator. Main disadvantage = high cost and complexity. DBMS provides security through user authentication and access control.

Quick Fire Revision

  • DBMS stands forDatabase Management System
  • Database = organised collection ofRelated data
  • Data vs InformationData = raw facts · Information = processed meaningful data
  • Table is also calledRelation
  • Row is also calledRecord or Tuple
  • Column is also calledField or Attribute
  • Primary Key uniquelyIdentifies each record — cannot be NULL or repeated
  • Foreign KeyLinks one table to another (references PK of another table)
  • CRUD stands forCreate, Read, Update, Delete
  • SQL stands forStructured Query Language
  • SQL SELECT used toRetrieve/read data from a table
  • SQL INSERT used toAdd new records
  • SQL UPDATE used toModify existing records
  • SQL DELETE used toRemove records
  • DDL stands forData Definition Language (CREATE, DROP, ALTER)
  • DML stands forData Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
  • Most popular database modelRelational (RDBMS) — uses tables + SQL
  • RDBMS examplesMySQL, Oracle, MS Access, SQL Server, PostgreSQL
  • NoSQL exampleMongoDB (document database)
  • Data Redundancy meansSame data stored in multiple places (problem of flat files)
  • Main advantage of DBMSEliminates redundancy and inconsistency
  • DBA stands forDatabase Administrator
  • Hierarchical model structureTree (parent-child, one parent only)
  • Query filters records usingWHERE clause
PreviousCyber-Attacks & Ethical Hacking Back toAll Topics
Key