📘 Database Systems (CS301)
1. Introduction to Database Systems
A database is an organized collection of related data stored electronically and accessed using
a Database Management System (DBMS). The purpose of a database system is to store large
volumes of data efficiently, ensure data consistency, reduce redundancy, and allow multiple
users to access data simultaneously.
Before databases, organizations relied on file-based systems, which suffered from serious
limitations such as data duplication, inconsistency, poor security, and difficult maintenance.
Database systems were introduced to overcome these issues and provide a centralized,
reliable data storage solution.
In Pakistani universities such as FAST and LUMS, Database Systems is a core subject
because databases form the backbone of almost all modern software applications.
2. Database vs File-Based System
2.1 File-Based System
Data stored in separate files
High redundancy
Data inconsistency
Difficult data sharing
Weak security
2.2 Database System
Centralized data storage
Reduced redundancy
Improved consistency
Easy data sharing
Strong security mechanisms
3. Database Management System (DBMS)
A DBMS is software that allows users to create, store, manipulate, and retrieve data from
databases.
3.1 Functions of DBMS
Data storage management
Data manipulation
Security enforcement
Concurrency control
Backup and recovery
3.2 Popular DBMS
MySQL
Oracle
Microsoft SQL Server
PostgreSQL
4. Database Users
Types of Users:
1. Database Administrators (DBA)
2. Application Programmers
3. End Users
4. Database Designers
Each user type has different responsibilities and access privileges.
5. Data Models
A data model defines how data is structured, stored, and related.
5.1 Hierarchical Model
Tree-like structure
Parent-child relationships
5.2 Network Model
Graph-based structure
Multiple parent nodes
5.3 Relational Model
Data stored in tables
Most widely used model
6. Relational Model Concepts
6.1 Relation
A table consisting of rows and columns.
6.2 Tuple
A row in a table.
6.3 Attribute
A column in a table.
6.4 Domain
Set of allowed values for an attribute.
7. Keys in Database
7.1 Primary Key
Uniquely identifies each record.
7.2 Foreign Key
Establishes relationship between tables.
7.3 Candidate Key
Potential primary keys.
7.4 Composite Key
Combination of attributes.
8. Entity Relationship (ER) Model
The ER model visually represents database structure.
8.1 Components
Entity
Attribute
Relationship
8.2 Types of Attributes
Simple
Composite
Multivalued
Derived
8.3 Cardinality
One-to-One
One-to-Many
Many-to-Many
9. ER Diagrams
ER diagrams are used to design databases before implementation.
Symbols:
Rectangle (Entity)
Oval (Attribute)
Diamond (Relationship)
10. Relational Algebra
Relational algebra is a procedural query language.
Operations:
Selection
Projection
Union
Intersection
Difference
Join
11. Structured Query Language (SQL)
SQL is used to interact with relational databases.
11.1 DDL (Data Definition Language)
CREATE
ALTER
DROP
11.2 DML (Data Manipulation Language)
INSERT
UPDATE
DELETE
11.3 DQL (Data Query Language)
SELECT
11.4 DCL (Data Control Language)
GRANT
REVOKE
12. SQL Constraints
Constraints enforce rules on data.
Types:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
13. Views
A view is a virtual table based on a SQL query.
Advantages:
Data abstraction
Security
Simplified queries
14. Indexing
Indexes improve data retrieval speed.
Types:
Primary index
Secondary index
Clustered index
Non-clustered index
15. Database Normalization
Normalization organizes data to reduce redundancy.
Normal Forms:
1NF (Atomic values)
2NF (No partial dependency)
3NF (No transitive dependency)
BCNF
16. Denormalization
Denormalization improves performance by introducing redundancy.
Used in:
Data warehouses
High-performance systems
17. Transactions
A transaction is a logical unit of work.
ACID Properties:
Atomicity
Consistency
Isolation
Durability
18. Concurrency Control
Ensures correct results when multiple users access data.
Techniques:
Locking
Timestamping
19. Deadlocks
A deadlock occurs when transactions wait indefinitely.
Handling:
Prevention
Avoidance
Detection
Recovery
20. Database Security
Security ensures protection of data.
Techniques:
Authentication
Authorization
Encryption
Auditing
21. Backup and Recovery
Backup protects against data loss.
Types:
Full backup
Incremental backup
Differential backup
Recovery restores the database after failure.
22. Distributed Databases
Data stored across multiple locations.
Advantages:
Reliability
Scalability
Availability
23. Database Applications
Banking systems
Airline reservation systems
University management systems
E-commerce platforms
24. Role of Databases in Modern Systems
Databases are used in:
Web applications
Mobile apps
Cloud computing
Big data systems
25. Conclusion
Database Systems is a fundamental subject that equips students with knowledge to design,
implement, and manage data-driven applications. A strong understanding of databases is
essential for software engineers, data engineers, and system architects.
Algorithms & Data Structures
University: MIT
Course: 6.006 – Introduction to Algorithms
1. Course Overview
Algorithms are step-by-step procedures to solve computational problems efficiently. This
course emphasizes time complexity, correctness, and optimal design.
2. Asymptotic Analysis
Big-O (O) – Upper bound (worst case)
Big-Ω (Ω) – Lower bound (best case)
Big-Θ (Θ) – Tight bound
Common complexities:
O(1): Constant
O(log n): Binary search
O(n): Linear scan
O(n log n): Merge sort
O(n²): Nested loops
3. Core Data Structures
Arrays, Linked Lists
Stacks, Queues
Trees (Binary Tree, BST, AVL)
Heaps (Min/Max Heap)
Hash Tables
4. Key Algorithms
Sorting: Merge Sort, Quick Sort, Heap Sort
Searching: Binary Search
Graph: BFS, DFS, Dijkstra, Kruskal
Dynamic Programming: Knapsack, LCS
5. Applications
Search engines
Network routing
Compiler optimization
Algorithms & Data Structures
(MIT – 6.006)
1. Introduction to Algorithms
An algorithm is a finite sequence of well-defined steps to solve a computational problem. A
good algorithm is correct, efficient, and scalable. Algorithms are language-independent and
focus on logic rather than syntax.
2. Time & Space Complexity
Time complexity measures how execution time grows with input size.
Space complexity measures extra memory usage.
Asymptotic notations:
Big-O (O): Worst-case performance
Big-Ω (Ω): Best-case performance
Big-Θ (Θ): Average/tight bound
3. Data Structures
Array: Fixed-size, fast indexing, costly insertion/deletion.
Linked List: Dynamic size, slow access, easy insertion.
Stack: LIFO, used in recursion and expression evaluation.
Queue: FIFO, used in scheduling.
Tree: Hierarchical structure, efficient searching.
Heap: Complete binary tree used for priority queues.
Hash Table: Fast lookup using hashing.
4. Algorithms
Sorting: Merge sort (stable, O(n log n)), Quick sort (fast average case).
Searching: Binary search requires sorted data.
Graph Algorithms:
o BFS – shortest path (unweighted)
o DFS – cycle detection
o Dijkstra – shortest path (weighted)
Dynamic Programming: Breaks problems into overlapping subproblems.
5. Real-World Applications
Navigation systems
Databases
AI and optimization problems
1. Introduction
Database Systems (CS301) is a crucial course in computer science that introduces students to
design, implementation, and management of databases. Databases store, organize, and
retrieve large amounts of structured data efficiently.
In Pakistani universities, CS301 is usually taught using MySQL, Oracle, or PostgreSQL
and includes SQL (Structured Query Language), relational database design, and
normalization. Students learn to model real-world data, implement relational schemas, and
execute queries for data manipulation.
Database systems are widely used in real-world applications such as banking, online
shopping, healthcare systems, social networks, and enterprise software, making CS301
essential for aspiring software developers, data analysts, and database administrators.
2. Learning Objectives
By the end of CS301, students should be able to:
1. Understand database fundamentals:
Learn the purpose, advantages, and types of databases (relational, NoSQL,
hierarchical, network).
2. Model data using ER diagrams:
Represent real-world entities, attributes, and relationships using Entity-Relationship
(ER) models.
3. Design relational databases:
Transform ER diagrams into relational schemas and understand primary and foreign
keys.
4. Apply normalization techniques:
Reduce redundancy and improve data integrity using 1NF, 2NF, 3NF, BCNF.
5. Write SQL queries:
Perform data definition, data manipulation, and data retrieval using SQL commands.
6. Understand transactions and concurrency:
Manage multiple users accessing a database simultaneously, ensuring ACID
properties.
7. Implement indexing and views:
Optimize query performance using indexing, and create virtual tables with views.
8. Understand database security and integrity:
Apply constraints, access control, and backup strategies to protect data.
3. Important Topics and Concepts
3.1 Introduction to Databases
A database is an organized collection of data that can be easily accessed, managed, and
updated.
Database Management System (DBMS): Software to create, manage, and interact
with databases. Examples: MySQL, Oracle, PostgreSQL
Advantages: Data consistency, integrity, security, reduced redundancy, and multi-
user access
3.2 Types of Databases
1. Relational Databases (RDBMS): Store data in tables with rows and columns.
2. NoSQL Databases: Non-relational, used for unstructured or semi-structured data
(MongoDB, Cassandra).
3. Hierarchical Databases: Data organized in a tree-like structure.
4. Network Databases: Data stored as records connected by links.
3.3 ER Model (Entity-Relationship Model)
ER modeling is used to design a database conceptually.
Entities: Objects or things (e.g., Student, Course)
Attributes: Properties of entities (e.g., Name, ID, Age)
Relationships: Associations between entities (e.g., Enrolled in)
Cardinality: Defines the number of instances in relationships (1:1, 1:N, M:N)
Example: A Student entity with attributes StudentID, Name, and Age can have a
relationship Enrolls with Course entity.
3.4 Relational Model
The relational model stores data in tables (relations).
Primary Key: Uniquely identifies each record in a table
Foreign Key: Establishes a link between two tables
Integrity Constraints: Ensure accuracy and consistency of data
Example Table: Student
StudentID Name Age
1 Ali 20
2 Sara 21
3.5 SQL Basics
SQL (Structured Query Language) is used to interact with databases.
1. Data Definition Language (DDL): CREATE, ALTER, DROP
2. Data Manipulation Language (DML): INSERT, UPDATE, DELETE
3. Data Querying: SELECT statements with conditions
Example: SQL Queries
-- Create table
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
-- Insert records
INSERT INTO Student VALUES (1, 'Ali', 20);
-- Select data
SELECT * FROM Student WHERE Age > 19;
3.6 Advanced SQL Concepts
Joins: Combine data from multiple tables
o Inner Join, Left Join, Right Join, Full Join
Aggregation Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
Group By and Having: Group records and filter aggregated results
Subqueries: Queries within queries for complex data retrieval
Example: Inner Join
SELECT [Link], [Link]
FROM Student
INNER JOIN Enrollment ON [Link] = [Link]
INNER JOIN Course ON [Link] = [Link];
3.7 Normalization
Normalization reduces redundancy and improves consistency:
1. 1NF: No repeating groups; atomic values
2. 2NF: No partial dependency on a part of primary key
3. 3NF: No transitive dependency
4. BCNF: Stronger version of 3NF
Example:
A table with StudentID, CourseName, Instructor may violate 2NF if Instructor depends
only on CourseName, not StudentID. Normalization separates the tables.
3.8 Indexing
Purpose: Speed up data retrieval
Types: Single-column, composite, unique, full-text
Example: Index on StudentID allows faster searching
3.9 Transactions and Concurrency
Transaction: Sequence of operations treated as a single unit
ACID Properties:
o Atomicity: All or nothing
o Consistency: Database remains valid
o Isolation: Transactions do not interfere
o Durability: Changes persist after commit
Concurrency Control: Prevents conflicts in multi-user environments
3.10 Views and Stored Procedures
Views: Virtual tables created from queries
Stored Procedures: Predefined SQL code executed repeatedly
Advantages: Reusability, abstraction, security
Example: Creating a View
CREATE VIEW YoungStudents AS
SELECT Name, Age FROM Student WHERE Age < 25;
3.11 Database Security
User Authentication: Control access using usernames and passwords
Privileges: GRANT and REVOKE commands
Backup and Recovery: Regular backups ensure data protection against failures
3.12 Database Design Best Practices
Follow ER modeling before implementation
Apply normalization to reduce redundancy
Use appropriate data types and constraints
Maintain indexes for efficient retrieval
Implement transactions and error handling for integrity
3.13 Real-World Applications
Banking Systems: Account management, transactions
E-Commerce: Product catalogs, orders, customer data
Healthcare Systems: Patient records, appointments
Education Systems: Student enrollment, course management
Social Media: User profiles, posts, friend connections
4. Summary
Database Systems (CS301) provides students with the skills to design, implement, and
manage relational databases efficiently. By mastering ER modeling, relational schemas,
SQL, normalization, transactions, indexing, and security, students can develop scalable
and reliable software systems.
Hands-on experience with SQL, queries, joins, views, and stored procedures reinforces
practical problem-solving skills. CS301 also prepares students for advanced topics such as
data warehousing, big data, and distributed databases, forming the backbone of data-
driven applications in the real world.
1. Introduction
Database Systems (CS301) is a crucial course in computer science that introduces students to
design, implementation, and management of databases. Databases store, organize, and
retrieve large amounts of structured data efficiently.
In Pakistani universities, CS301 is usually taught using MySQL, Oracle, or PostgreSQL
and includes SQL (Structured Query Language), relational database design, and
normalization. Students learn to model real-world data, implement relational schemas, and
execute queries for data manipulation.
Database systems are widely used in real-world applications such as banking, online
shopping, healthcare systems, social networks, and enterprise software, making CS301
essential for aspiring software developers, data analysts, and database administrators.
2. Learning Objectives
By the end of CS301, students should be able to:
1. Understand database fundamentals:
Learn the purpose, advantages, and types of databases (relational, NoSQL,
hierarchical, network).
2. Model data using ER diagrams:
Represent real-world entities, attributes, and relationships using Entity-Relationship
(ER) models.
3. Design relational databases:
Transform ER diagrams into relational schemas and understand primary and foreign
keys.
4. Apply normalization techniques:
Reduce redundancy and improve data integrity using 1NF, 2NF, 3NF, BCNF.
5. Write SQL queries:
Perform data definition, data manipulation, and data retrieval using SQL commands.
6. Understand transactions and concurrency:
Manage multiple users accessing a database simultaneously, ensuring ACID
properties.
7. Implement indexing and views:
Optimize query performance using indexing, and create virtual tables with views.
8. Understand database security and integrity:
Apply constraints, access control, and backup strategies to protect data.
3. Important Topics and Concepts
3.1 Introduction to Databases
A database is an organized collection of data that can be easily accessed, managed, and
updated.
Database Management System (DBMS): Software to create, manage, and interact
with databases. Examples: MySQL, Oracle, PostgreSQL
Advantages: Data consistency, integrity, security, reduced redundancy, and multi-
user access
3.2 Types of Databases
1. Relational Databases (RDBMS): Store data in tables with rows and columns.
2. NoSQL Databases: Non-relational, used for unstructured or semi-structured data
(MongoDB, Cassandra).
3. Hierarchical Databases: Data organized in a tree-like structure.
4. Network Databases: Data stored as records connected by links.
3.3 ER Model (Entity-Relationship Model)
ER modeling is used to design a database conceptually.
Entities: Objects or things (e.g., Student, Course)
Attributes: Properties of entities (e.g., Name, ID, Age)
Relationships: Associations between entities (e.g., Enrolled in)
Cardinality: Defines the number of instances in relationships (1:1, 1:N, M:N)
Example: A Student entity with attributes StudentID, Name, and Age can have a
relationship Enrolls with Course entity.
3.4 Relational Model
The relational model stores data in tables (relations).
Primary Key: Uniquely identifies each record in a table
Foreign Key: Establishes a link between two tables
Integrity Constraints: Ensure accuracy and consistency of data
Example Table: Student
StudentID Name Age
1 Ali 20
2 Sara 21
3.5 SQL Basics
SQL (Structured Query Language) is used to interact with databases.
1. Data Definition Language (DDL): CREATE, ALTER, DROP
2. Data Manipulation Language (DML): INSERT, UPDATE, DELETE
3. Data Querying: SELECT statements with conditions
Example: SQL Queries
-- Create table
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
-- Insert records
INSERT INTO Student VALUES (1, 'Ali', 20);
-- Select data
SELECT * FROM Student WHERE Age > 19;
3.6 Advanced SQL Concepts
Joins: Combine data from multiple tables
o Inner Join, Left Join, Right Join, Full Join
Aggregation Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
Group By and Having: Group records and filter aggregated results
Subqueries: Queries within queries for complex data retrieval
Example: Inner Join
SELECT [Link], [Link]
FROM Student
INNER JOIN Enrollment ON [Link] = [Link]
INNER JOIN Course ON [Link] = [Link];
3.7 Normalization
Normalization reduces redundancy and improves consistency:
1. 1NF: No repeating groups; atomic values
2. 2NF: No partial dependency on a part of primary key
3. 3NF: No transitive dependency
4. BCNF: Stronger version of 3NF
Example:
A table with StudentID, CourseName, Instructor may violate 2NF if Instructor depends
only on CourseName, not StudentID. Normalization separates the tables.
3.8 Indexing
Purpose: Speed up data retrieval
Types: Single-column, composite, unique, full-text
Example: Index on StudentID allows faster searching
3.9 Transactions and Concurrency
Transaction: Sequence of operations treated as a single unit
ACID Properties:
o Atomicity: All or nothing
o Consistency: Database remains valid
o Isolation: Transactions do not interfere
o Durability: Changes persist after commit
Concurrency Control: Prevents conflicts in multi-user environments
3.10 Views and Stored Procedures
Views: Virtual tables created from queries
Stored Procedures: Predefined SQL code executed repeatedly
Advantages: Reusability, abstraction, security
Example: Creating a View
CREATE VIEW YoungStudents AS
SELECT Name, Age FROM Student WHERE Age < 25;
3.11 Database Security
User Authentication: Control access using usernames and passwords
Privileges: GRANT and REVOKE commands
Backup and Recovery: Regular backups ensure data protection against failures
3.12 Database Design Best Practices
Follow ER modeling before implementation
Apply normalization to reduce redundancy
Use appropriate data types and constraints
Maintain indexes for efficient retrieval
Implement transactions and error handling for integrity
3.13 Real-World Applications
Banking Systems: Account management, transactions
E-Commerce: Product catalogs, orders, customer data
Healthcare Systems: Patient records, appointments
Education Systems: Student enrollment, course management
Social Media: User profiles, posts, friend connections
4. Summary
Database Systems (CS301) provides students with the skills to design, implement, and
manage relational databases efficiently. By mastering ER modeling, relational schemas,
SQL, normalization, transactions, indexing, and security, students can develop scalable
and reliable software systems.
Hands-on experience with SQL, queries, joins, views, and stored procedures reinforces
practical problem-solving skills. CS301 also prepares students for advanced topics such as
data warehousing, big data, and distributed databases, forming the backbone of data-
driven applications in the real world.