Database System Notes
Introduction to Database Systems
A Database System is a collection of data that is organized to be easily accessed, managed, and
updated. It consists of databases, database management systems (DBMS), and applications that
interact with data.
Types of Database Systems
1. Relational Database Management System (RDBMS)
o Based on tables (relations) with structured data.
o Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
2. NoSQL Databases
o Used for handling unstructured and semi-structured data.
o Types:
▪ Document-based (MongoDB)
▪ Key-Value Stores (Redis, DynamoDB)
▪ Column-Family Stores (Cassandra)
▪ Graph Databases (Neo4j)
3. Hierarchical Databases
o Data is organized in a tree-like structure.
o Example: IBM Information Management System (IMS).
4. Network Databases
o Data is represented using records and links, allowing many-to-many relationships.
o Example: Integrated Data Store (IDS).
5. Object-Oriented Databases
o Stores data in objects, similar to object-oriented programming.
o Example: db4o.
Key Concepts in Database Systems
• Data Models: Defines how data is structured and manipulated (Relational, Hierarchical,
Network, Object-Oriented).
• Schema: The structure of a database, including tables, attributes, and relationships.
• Normalization: A technique to remove redundancy and improve data integrity (1NF,
2NF, 3NF, BCNF).
• Transactions: A sequence of operations performed as a single unit, following ACID
properties (Atomicity, Consistency, Isolation, Durability).
• Indexes: Used to speed up data retrieval operations.
• Joins: Combines data from multiple tables (INNER JOIN, LEFT JOIN, RIGHT JOIN,
FULL JOIN).
SQL (Structured Query Language)
Basic SQL Commands
• Data Definition Language (DDL): Defines and manages database structures.
o CREATE TABLE, ALTER TABLE, DROP TABLE
• Data Manipulation Language (DML): Handles data manipulation.
o INSERT INTO, UPDATE, DELETE
• Data Query Language (DQL): Fetches data.
o SELECT, WHERE, GROUP BY, ORDER BY
• Data Control Language (DCL): Manages permissions.
o GRANT, REVOKE
• Transaction Control Language (TCL): Controls transactions.
o COMMIT, ROLLBACK, SAVEPOINT
Database Management System (DBMS) vs Relational DBMS (RDBMS)
Feature DBMS RDBMS
Structure Unstructured Structured (tables)
Data Model Various Relational model
Relationships No direct support Supports relations
Scalability Limited Highly scalable
Database Design Process
1. Requirement Analysis - Understanding business needs.
2. Conceptual Design - Creating ER diagrams to define entities and relationships.
3. Logical Design - Mapping ER models to relational models.
4. Normalization - Organizing data efficiently.
5. Implementation - Creating database schema and objects.
6. Optimization - Enhancing performance with indexing and query optimization.
7. Security & Maintenance - Ensuring data integrity and security.
Database Security & Backup
• User Authentication & Authorization - Role-based access control.
• Encryption - Secure data storage and transmission.
• Backup & Recovery - Full, Incremental, and Differential backups.
• Auditing & Logging - Tracking database activity for security.
Popular Database Management Systems
• SQL-Based: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
• NoSQL-Based: MongoDB, Cassandra, Firebase.
• Cloud Databases: AWS RDS, Google Cloud Firestore, Azure SQL Database.
Conclusion
Database systems are fundamental to managing data efficiently and securely. Understanding
SQL, normalization, database design, and security principles is crucial for effective database
administration.
End of Notes
