DBMS Study Notes PDF

Introduction to DBMS ==================== A Database Management System (DBMS) is a collection of programs that enable users to define, create, maintain, and control access to the database. Functions of DBMS: 1\. Data Storage Management: The DBMS stores large amounts of data and manages the memory...

Introduction to DBMS ==================== A Database Management System (DBMS) is a collection of programs that enable users to define, create, maintain, and control access to the database. Functions of DBMS: 1\. Data Storage Management: The DBMS stores large amounts of data and manages the memory allocation for each piece of data. 2\. Data Retrieval and Update: DBMS efficiently retrieves, updates, and deletes data records. 3\. Data Security: It ensures that data is accessible only to authorized users by implementing security features like authentication and authorization. 4\. Data Integrity: Ensures the accuracy and consistency of data in the database by enforcing rules and constraints. 5\. Data Backup and Recovery: DBMS provides mechanisms to back up data and recover it after failure. 6\. Concurrency Control: DBMS allows multiple users to access and manipulate the data simultaneously while ensuring consistency. DBMS Architecture ================= Types of DBMS Architecture: 1\. 1-Tier Architecture: Direct access to the database. Simple systems with no separation between user and database. 2\. 2-Tier Architecture: User interface (client) communicates directly with the database server. 3\. 3-Tier Architecture: Includes a middle layer (application server) between the user interface and the database. DBMS Layers: External Schema (View): User-specific views of the data. Conceptual Schema (Logical): The logical structure of the entire database. Internal Schema (Physical): How data is actually stored on disk. Data Models =========== Types of Data Models: 1\. Hierarchical Data Model: Data is represented in a tree-like structure with parent-child relationships. 2\. Network Data Model: Uses a graph structure where each node can have multiple parents. 3\. Relational Data Model: Data is stored in tables (relations). Tables consist of rows (tuples) and columns (attributes). 4\. Object-Oriented Data Model: Data is stored as objects, similar to objects in object-oriented programming. ER Model: The Entity-Relationship Model is a high-level conceptual data model used for designing databases visually. Entity: Represents real-world objects (e.g., Student, Course). Attribute: Properties of an entity (e.g., Name, Age). Relationship: Defines how entities are related (e.g., A student enrolls in a course). ER Model and Database Design ============================ ER Diagram Symbols: Entity: Rectangles Attributes: Ellipses Relationship: Diamonds Primary Key: Underlined attributes ER Design Process: 1\. Identify Entities: Recognize real-world objects that require storage. 2\. Identify Relationships: Find how entities are related. 3\. Determine Attributes: Define the characteristics of entities and relationships. 4\. Convert ER Diagram to Relational Schema: Convert the ER diagram to tables for implementation. Relational Model ================ Basic Concepts: 1\. Relation (Table): A collection of data organized into rows and columns. 2\. Tuple (Row): A single record in a table. 3\. Attribute (Column): A field or characteristic of the entity. 4\. Domain: The set of allowable values for an attribute. Keys in Relational Model: 1\. Primary Key: Uniquely identifies each record in a table. 2\. Foreign Key: A field that links one table to another. 3\. Candidate Key: Any set of attributes that can uniquely identify a record. 4\. Composite Key: A combination of two or more attributes that uniquely identify a record. SQL (Structured Query Language) =============================== DDL (Data Definition Language): 1\. CREATE: Defines new tables and databases. 2\. ALTER: Modifies an existing table (e.g., adding or deleting columns). 3\. DROP: Deletes a table or database. DML (Data Manipulation Language): 1\. SELECT: Retrieves data from tables. 2\. INSERT: Adds new data into a table. 3\. UPDATE: Modifies existing data. 4\. DELETE: Removes data from a table. Join Operations: 1\. Inner Join: Returns rows when there is a match in both tables. 2\. Left Join: Returns all rows from the left table, and matching rows from the right table. 3\. Right Join: Returns all rows from the right table, and matching rows from the left table. 4\. Full Outer Join: Returns all rows when there is a match in one of the tables. Normalization ============= Normalization is the process of organizing data to reduce redundancy and dependency. Normal Forms: 1\. 1NF (First Normal Form): Ensures all attributes contain atomic values. 2\. 2NF (Second Normal Form): Removes partial dependencies. 3\. 3NF (Third Normal Form): Removes transitive dependencies. Transactions and Concurrency Control ==================================== Transactions: A transaction is a sequence of operations performed as a single unit. ACID Properties: 1\. Atomicity: Ensures that the transaction is either completed fully or not at all. 2\. Consistency: The database remains in a valid state before and after the transaction. 3\. Isolation: Ensures that operations in one transaction are not visible to others until complete. 4\. Durability: Once a transaction is committed, the changes are permanent. Indexing ======== What is an Index? An index is a data structure that improves the speed of data retrieval operations. Types of Indexes: 1\. Single-Level Index: Simple index structure, links directly to the data. 2\. Multi-Level Index: A hierarchical index structure that points to other indexes. 3\. Hash Indexing: Uses a hash function to map search keys to data locations. Backup and Recovery =================== Backup Strategies: 1\. Full Backup: A complete copy of the entire database. 2\. Incremental Backup: Only the changes made since the last backup are copied. 3\. Differential Backup: Only the changes made since the last full backup are copied. Recovery Methods: 1\. Log-Based Recovery: Uses logs to redo or undo transactions. 2\. Shadow Paging: Maintains an alternate copy of the database for recovery purposes. Distributed Databases ===================== Advantages: 1\. Increased availability: Data is available from multiple locations. 2\. Improved performance: Data access is faster since it is closer to the user. 3\. Fault tolerance: Distributed systems are more resilient to failures. Challenges: 1\. Data consistency: Maintaining consistency across distributed systems can be complex. 2\. Concurrency control: Ensuring that simultaneous transactions do not result in data anomalies.