Database Management Systems (DBMS): Concepts and Applications

Posted on Oct 2, 2024 in Computers

  1. What is the Purpose and Application of DBMS?

    • Purpose of DBMS:

      A Database Management System (DBMS) is software designed for efficient data storage, retrieval, and management. It provides users and applications with secure, organized, and efficient data access.

    • Applications of DBMS:

      • Banking: Banks utilize DBMS for managing customer accounts, transactions, and loans.
      • Education: Schools and universities employ DBMS to store student information, exam results, and course registrations.
      • E-commerce: Online stores use DBMS to track inventory, customer orders, and deliveries.

  2. Disadvantages of File Processing

    • Data Redundancy: Identical data may be stored in multiple files, leading to wasted storage space.
    • Data Inconsistency: Updating data in one file without updating it in others results in mismatched information.
    • Limited Data Sharing: File-based systems hinder data sharing between different applications.
    • Poor Security: Limited control over file access can create security risks.
    • Lack of Flexibility: Modifying or adding data to a file processing system is challenging.

  3. Three-Level Architecture of DBMS

    DBMS employs a three-level architecture to differentiate data storage, organization, and presentation to users:

    • Internal Level: Focuses on the physical storage of data (how data is stored on disk).
    • Conceptual Level: Represents the logical structure of the entire database (tables, relationships, etc.).
    • External Level: Presents customized views of the data based on specific user or application needs.

    Example:

    In a library database, the internal level stores book data in files. The conceptual level defines the structure of books (title, author, category). The external level provides different views, such as a librarian’s view of borrowed books and a student’s view of available books.

    Advantages:

    • Data Independence: Changes at the internal level do not impact the conceptual or external levels.
    • Enhanced Security: Users only access the data they require.
    • Improved Efficiency: Optimized internal storage facilitates faster data access.

  4. 1NF, 2NF, and 3NF: A Comparison

    • 1NF (First Normal Form): Data is organized in tables with atomic (indivisible) values in each cell. Repeating data groups are prohibited.
    • 2NF (Second Normal Form): Builds upon 1NF, ensuring that all non-primary key fields depend on the entire primary key, eliminating partial dependencies.
    • 3NF (Third Normal Form): Extends 2NF by eliminating transitive dependencies, where non-key fields depend on other non-key fields.
    • Why 3NF is Stronger: It minimizes redundancy more effectively than 1NF and 2NF by ensuring non-key field independence, enhancing data consistency and reducing anomalies.

  5. Boyce-Codd Normal Form (BCNF)

    • BCNF: A stricter version of 3NF, requiring that for every dependency, the left-hand side must be a superkey (a key uniquely identifying each record).
    • Difference from 3NF: BCNF addresses specific cases not handled by 3NF. While 3NF tables may still have anomalies, BCNF eliminates them entirely.

  6. Functional Dependency and Its Types

    • Functional Dependency: A relationship between two attribute sets in a database. For instance, knowing a student’s ID uniquely determines their name, indicating that the student ID functionally determines the name.
    • Types of Functional Dependency:
      • Full Dependency: All parts of the key are necessary to determine other fields (e.g., StudentID and CourseID together determine the grade).
      • Partial Dependency: Only a part of the composite key determines another attribute (e.g., StudentID alone determines student name).
      • Transitive Dependency: A non-key attribute depends on another non-key attribute (e.g., DepartmentID determines DepartmentName, which in turn determines something else).

  7. Role and Types of Keys in DBMS

    • Role of Keys: Keys uniquely identify records in a table and establish relationships between tables in a relational database.
    • Types of Keys:
      • Primary Key: A field (or combination of fields) uniquely identifying each record in a table (e.g., student ID).
      • Foreign Key: A field in one table referencing the primary key of another table, creating a relationship between them (e.g., a student’s department ID linking to the department table).
      • Candidate Key: A field capable of uniquely identifying records but not chosen as the primary key.
      • Composite Key: A primary key composed of two or more fields (e.g., StudentID and CourseID forming a unique identifier for course enrollment).

Group C (Answer any two questions)

  1. Importance of Normalization in DBMS

    • Importance of Normalization: Normalization minimizes redundancy, ensures data consistency, and enhances database efficiency.
      • Reduce data redundancy: Avoids storing the same data multiple times, saving space and preventing inconsistencies.
      • Improve data integrity: Maintains data accuracy and consistency during updates.
      • Increase query performance: Optimizes database structure for faster queries.
      • Facilitate data maintenance: Simplifies database updates by focusing on relevant parts.
    • 1NF Example: A table where students and their courses are listed in multiple columns (e.g., one student listed twice for two courses). 1NF separates repeated courses into individual rows.
    • 2NF Example: A table with student names and courses, where a student’s name appears multiple times for different courses. 2NF removes partial dependencies by creating separate tables for students and courses.
    • 3NF Example: 3NF eliminates transitive dependencies. For instance, a table storing student, department, and course details would be split into separate tables for each entity.

  2. Relational Algebra Statements

    Given Database System: (Provide the database schema here)

    • a) Select the name and location of all restaurants:

      π Rname, Rlocation (Restaurant)

    • b) Find the working hours of the cook named “Sita”:

      σ Cname = ‘Sita’ (π Workinghrs (WorksAt))

    • c) Select the name of the food cooked by “Ramesh”:

      π Fname (σ Cname = ‘Ramesh’ (Food))

    • d) Use join to select the names of restaurants where food of category “breakfast” is available:

      π Rname (σ Category = ‘breakfast’ (Restaurant ⋈ Food ⋈ WorksAt))

    • e) Find the name of cooks who work at “KFC”:

      π Cname (σ Rname = ‘KFC’ (WorksAt))