DBMS Review Flashcards

Database Management Systems - Module 1

Introduction & ER Model

• DBMS Concepts:

  • Efficient and secure data management software.
  • Key features: Data Abstraction, Multiple Views, Data Independence, Concurrency Control, Integrity & Security, Recovery & Backup.

• Characteristics:

  • Simplifies database creation and maintenance.
  • Allows concurrent data access for multiple users and applications.
  • Ensures controlled access & structured organization.

• Database Users:

  • Naive Users: Interact through predefined applications (e.g., ATM users).
  • Application Programmers: Write application programs (e.g., backend developers).
  • DBA (Administrator): Manages database system (e.g., IT personnel).
  • Sophisticated Users: Directly interact using SQL (e.g., data analysts).

• Data Types:

  • Structured: Fixed schema (e.g., relational tables).
  • Semi-structured: Flexible schema with tags/keys (e.g., XML, JSON).
  • Unstructured: No predefined format (e.g., images, videos).

• Data Models and Schema:

  • Data Model: Describes data organization (Relational, Hierarchical, Network, Object-Oriented).
  • Schema: Logical definition of DB structure (External, Conceptual, Internal).

• Three Schema Architecture:

  • External (User Views): Customized user view.
  • Conceptual (Logical): Logical structure (tables, relationships).
  • Internal (Physical): Physical storage format.

• Database Languages:

  • DDL (Data Definition): Create & modify schema (e.g., CREATE TABLE).
  • DML (Data Manipulation): Modify data (e.g., INSERT, UPDATE).
  • DCL (Data Control): Access permissions (e.g., GRANT, REVOKE).
  • TCL (Transaction Control): Manage transactions (e.g., COMMIT, ROLLBACK).

• Database Architectures and Classifications:

  • 2-tier, 3-tier architectures.

• ER Model:

  • Basic Concepts, Attributes, Entity Sets.
  • Relationships and Constraints: Cardinality, Participation.
  • Weak Entities, Relationships of Degree 3.
  • ER Diagram Construction.

DBMS Key Features

Data Abstraction

• Hides complexity of data storage, showing only relevant information.
• Levels:
  • Physical Level: How data is actually stored.
  • Logical Level: What data is stored and relationships.
  • View Level: How data is presented to users.
• Benefit: Users interact without needing to know internal storage details.

Multiple Views

• Different users access the same database with different perspectives.
• Example: Manager sees summary reports, clerk sees detailed entries.
• Advantage: Role-based access improves data security and usability.

Data Independence

• Ability to change the schema at one level without affecting the next higher level.
• Types:
  • Logical Data Independence: Change in logical schema doesn’t affect applications.
  • Physical Data Independence: Change in physical storage doesn’t affect logical schema.
• Importance: Enhances system flexibility and maintainability.

Concurrency Control

• Mechanism that allows multiple users to access data simultaneously without conflicts.
• Why Needed: Avoids lost updates, dirty reads, and inconsistent data.
• Techniques Used: Locking, Timestamp ordering, Optimistic concurrency control.
• Goal: Ensure data consistency in a multi-user environment.

Integrity & Security

• Integrity: Ensures accuracy and consistency using rules and constraints (e.g., primary key, foreign key).
• Security: Controls data access and modification permissions.
• Features Include: Authorization & authentication, Encryption, Access controls.
• Purpose: Protect data from unauthorized access and ensure valid entries.

Recovery & Backup

• Recovery: Restores the database to a correct state after a failure.
• Backup: Creates periodic data copies to prevent data loss.
• Techniques: Log-based recovery, Checkpoints, Shadow paging.
• Why Important: Ensures data durability and reliability in case of failures.

Types of Database Users

• Different user types interact based on role, knowledge, and access level.

Naive User

• Role: Interacts through predefined applications.
• Example: ATM users, Mobile app users, Online banking users.
• Access Level: Limited to front-end operations (withdraw, deposit, view balance).

Application Programmer

• Role: Writes application programs that interact with the database.
• Usage: APIs or embedded SQL.
• Example: Backend developers, Software engineers creating billing/HR systems.
• Access Level: Access to schemas and queries as needed for development.

DBA (Database Administrator)

• Role: Sets up, configures, and maintains the database system.
• Responsibilities: Security, backup, recovery, tuning, and user management.
• Example: IT personnel managing the database infrastructure.
• Access Level: Full access, including administrative and configuration privileges.

Sophisticated User

• Role: Directly interacts with the database using SQL or query tools.
• Tasks: Performs complex queries, analysis, and reports.
• Example: Data analysts, Business intelligence professionals.
• Access Level: Advanced access to query and analyze data without application interface.

Three Schema Architecture

• Framework for data abstraction in DBMS.
• Separates physical storage from user interaction.
• Allows storage and structure changes without affecting applications.
• Ensures data independence.

Levels:

1. Internal Schema (Storage Level):
   • Defines how data is physically stored.
   • Lowest level of abstraction.
   • Deals with physical storage, indexing, and file structures.
   • Optimized for performance and efficiency.
   • Example: Data stored in B+ Trees, Hash Indexes, or File Systems.
2. Conceptual Schema (Logical Level):
   • Defines the overall database structure.
   • Middle level of abstraction.
   • Represents entire database logically (tables, relationships, constraints).
   • Independent of physical storage and user applications.
   • Example: A relational schema defining Students (Student_ID, Name, Course).
3. External Schema (View Level):
   • Defines customized views for different users or applications.
   • Top level of abstraction.
   • Provides security by restricting access to certain data.
   • Example: A student may see only their grades, while an administrator sees all students' data.

Data Independence in Three-Schema Architecture

1. Physical Data Independence:
   • Changes in storage do not affect the conceptual schema.
   • Example: Changing a B-tree index to a Hash Index does not impact applications.
2. Logical Data Independence:
   • Changes in the conceptual schema do not affect external views.
   • Example: Adding a new column Email to a Student table does not affect existing applications.

Advantages of Three-Schema Architecture

1. Separation of Concerns:
   • Users do not need to worry about data storage details.
   • Developers can design applications independently.
2. Data Abstraction:
   • Provides different views for different user needs.
   • Maintains security and integrity.
3. Flexibility:
   • Easy to update storage mechanisms without affecting users.
   • Supports evolution of database structures.

Comparison of Schema Levels

ER Modeling – Core Concepts

• Elements: Entity, Attribute, Relationship
• Keys: Primary, Candidate, Foreign
• Cardinality: 1:1, 1:N, M:N
• Participation: Total / Partial
• Weak Entities & Identifying Relationships

Data Types

• Data can be classified into three types based on structure and organization.

Structured Data

• Overview
  • Highly organized and follows a predefined schema.
  • Stored in relational databases (RDBMS).
  • Data is arranged in tables with rows and columns.
  • Uses SQL for querying and data manipulation.
  • Examples: Employee records, Banking transactions, Inventory databases.
• Advantages:
  • Easy to store, retrieve, and query.
  • High consistency and integrity.
  • Data relationships can be well-defined.
• Disadvantages:
  • Limited flexibility due to fixed schema.
  • Not suitable for multimedia or non-tabular data.

Semi-structured Data

• Overview
  • Does not follow a strict tabular format but has some organizational elements.
  • Uses tags, attributes, or key-value pairs.
  • Common in NoSQL databases.
  • More flexible than structured data.
  • Examples: JSON, XML, YAML, Email data.
• Advantages:
  • More flexible than structured data.
  • Can represent complex relationships.
  • Suitable for web applications and APIs.
• Disadvantages:
  • Harder to query than structured data.
  • Requires specialized tools (NoSQL, XML parsers).

Unstructured Data

• Overview
  • Has no predefined format and is often difficult to store in traditional databases.
  • Cannot be easily organized into tables.
  • Requires specialized processing techniques (AI, Big Data tools).
  • Examples: Images, Videos, Social Media posts, Sensor data.
• Advantages:
  • Can store a large variety of information.
  • Suitable for multimedia and real-world data.
  • Provides deep insights via AI/ML processing.
• Disadvantages:
  • Difficult to manage and analyze.
  • Requires large storage and computational power.

Comparison of Data Types

Data Models & Schemas

• Data Model: Describes how data is organized
  • Relational
  • Hierarchical
  • Network
  • Object-Oriented
• Schema: Logical definition of DB structure
  • Types: External, Conceptual, Internal

Types of Data Models

1. Relational Model:
   • Data organized as tables (relations)
   • Most widely used (e.g., MySQL, Oracle)
2. Hierarchical Model:
   • Data organized in a tree-like structure
   • Parent-child relationships (e.g., IBM IMS)
3. Network Model:
   • Data represented as records with multiple relationships
   • Uses graphs for flexible access (e.g., CODASYL)
4. Object-Oriented Model:
   • Data as objects, supports inheritance, encapsulation
   • Used in complex applications (e.g., multimedia, CAD)

Database Architectures

• Physical centralized architecture.
• 2-tier vs 3-tier Architecture
  • 2-tier: Client - DB Server (e.g., Desktop app with local DB)
  • 3-tier: Client - App Server - DB (e.g., Web app like shopping site)
• Logical and Physical representations of 2-tier and 3-tier architectures.

Database Languages

Relationships of Degree 3 (Ternary Relationships)

• Definition: A relationship that involves three entities simultaneously
• Example: Supplies(Supplier, Part, Project) – Shows which supplier supplies which part to which project.
• Diagram Example: Entities: Doctor, Patient, Medicine; Relationship: Prescribes(Doctor, Patient, Medicine)

Quick Recap Questions

• What is TCL used for?
• Can you give an example of a DCL command?
• Describe a real-world ternary relationship.

Example Scenario

• A company has salespersons, some managing others (but each salesperson has <= 1 manager).
• Salespersons are agents for many customers; each customer has exactly one salesperson.
• Customers place multiple orders; each order is placed by exactly one customer.
• Orders list one or more items; items may appear in many orders.
• Items are assembled from parts; parts can be common across many items.
• Employees assemble items from parts.
• Suppliers supply different parts in certain quantities; a part may be supplied by different suppliers.