uni

The provided answers serve as a guide and should be used for reference. Achieving full marks requires a comprehensive understanding and application of the materia

Short Answer Questions – 2 Marks

1. What is DBMS and its characteristics?

• DBMS (Database Management System):

  A Database Management System (DBMS) is software that allows users to create, manage, and manipulate databases efficiently. It acts as an interface between the user and the database, ensuring that data is organized and accessed systematically. DBMS manages data integrity, security, and concurrency, making it easier for users to store and retrieve data.

• Characteristics of DBMS:

  • Data Integrity: Ensures the accuracy and consistency of data by enforcing constraints.
  • Data Security: Controls access to data by providing user authentication and authorization.
  • Data Independence: The structure of the database is separate from the data itself, which allows changes without affecting the data.
  • Data Redundancy: Reduces duplication of data by storing it in a structured manner.
  • Concurrency Control: Allows multiple users to access the database simultaneously without conflicts.

2. Define Database Schema and Instance.

• Database Schema:

  A database schema defines the logical structure of the database. It describes how data is organized in tables, fields, data types, and relationships. The schema remains fixed unless explicitly modified.

  Example:

  A schema for a “Student” table could include fields like Student_ID, Name, Age, and Department.

• Instance:

  An instance refers to the actual data stored in the database at a specific moment in time. It is a snapshot of the database’s contents at a particular point.

  Example:

  A particular record of a student with Student_ID = 101, Name = John, Age = 21, and Department = Computer Science is an instance.

3. What are various DDL statements used in SQL?

DDL (Data Definition Language) statements are used to define and modify the structure of a database. The common DDL statements are:

• CREATE: Used to create a new database object like a table, view, or index. Example:

  sql CREATE TABLE Student ( Student_ID INT, Name VARCHAR(50), Age INT );

• ALTER: Used to modify an existing database object. Example:

  sql ALTER TABLE Student ADD Department VARCHAR(30)

• DROP: Deletes an existing database object. Example:

  ```sql DROP TABLE Student;

  ```

• TRUNCATE: Removes all records from a table without logging individual row deletions. Example:

  ```sql TRUNCATE TABLE Student;

  ```

4. Identify various database users.

1. Database Administrator (DBA):
   • Manages the database and ensures its proper functioning.
   • Handles user access, backups, recovery, and performance tuning.
2. End Users:
   • Use the database to perform day-to-day tasks like inserting and querying data.
   • Includes casual users (use applications) and sophisticated users (write direct queries).
3. Application Programmers:
   • Write programs that interact with the database using SQL.
   • Design user-friendly interfaces to access data.
4. System Analysts:
   • Define business requirements and ensure the database structure meets business goals.
   • Collaborate with programmers and the DBA to ensure the system meets user needs.
5. Data Scientists:
   • Analyze large volumes of data to extract insights and patterns.
   • Use analytical tools and write complex queries to manipulate and interpret data.

5. Define 1NF and 2NF.

• 1NF (First Normal Form):

  A relation is in 1NF if:

  • All values in a column are atomic (indivisible).
  • There are no repeating groups or arrays in the table.

  Example:

  A table with multiple phone numbers in a single column is not in 1NF. Splitting them into separate rows would convert it to 1NF.

• 2NF (Second Normal Form):

  A relation is in 2NF if:

  • It is already in 1NF.
  • All non-key attributes are fully functionally dependent on the primary key.
  • No partial dependency is allowed.

  Example:

  A table where the “Department” is dependent only on a part of a composite key violates 2NF.

6. Explain TRC with an example.

• TRC (Tuple Relational Calculus):

  Tuple Relational Calculus is a non-procedural query language used to specify the conditions that tuples should satisfy to be retrieved from a relation. Instead of defining the steps to obtain the result, TRC defines the result set using a logical condition. The result of a TRC query is a set of tuples that satisfy the given condition.

  Example:

  {T | T ∈ Employee ∧ T.salary > 50000}

  This query retrieves all tuples (employees) from the Employee table where the salary is greater than 50000. It specifies “what to retrieve” rather than “how to retrieve.”

7. Define Selection and Projection.

• Selection (σ):

  The Selection operation in relational algebra is used to select specific rows (tuples) from a relation that satisfy a given condition. It filters the rows based on a condition, returning only the ones that meet the criteria.

  Example:

  σ(salary > 50000)(Employee)

  This query returns all employee records where the salary is greater than 50000.

• Projection (π):

  The Projection operation is used to extract specific columns (attributes) from a relation. It eliminates duplicate values and returns only the required attributes.

  Example:

  π(name, salary)(Employee)

  This query will return only the name and salary columns from the Employee table, ignoring the rest of the data.

8. Explain the use of Set Difference and Intersection Operation.

• Set Difference (-):

  The Set Difference operation returns the tuples that are present in one relation but not in the other. It is used to find the difference between two sets of data.

  Example:

  A - B

  If A = {1, 2, 3} and B = {2, 3, 4}, then A - B = {1} because only 1 is unique to A.

• Intersection (∩):

  The Intersection operation returns only the common tuples present in both relations. It finds overlapping data between two sets.

  Example:

  A ∩ B

  If A = {1, 2, 3} and B = {2, 3, 4}, then A ∩ B = {2, 3} because these values exist in both sets.

9. Write short notes on Domain Relational Calculus.

• Domain Relational Calculus (DRC):

  Domain Relational Calculus is a non-procedural query language used to specify the set of values from the domain that satisfy certain conditions. Unlike Tuple Relational Calculus (TRC), which works with tuples, DRC works with domain values. In DRC, variables represent the values of attributes rather than entire tuples.

  Example:

  {<d1, d2> | ∃ t ∈ Employee (t.name = d1 ∧ t.salary = d2 ∧ d2 > 50000)}

  This query returns pairs of names and salaries from the Employee table where the salary is greater than 50000.

  The result will be a set of attribute values that match the condition.

10. Define 1NF and 2NF.

• 1NF (First Normal Form):

  A relation is in 1NF if:

  • Each column contains atomic values (no sets or arrays).
  • There are no repeating groups or multivalued attributes.
  • Each row is uniquely identifiable using a primary key.

  Example:

  A table with multiple phone numbers in a single column violates 1NF. To convert it to 1NF, create separate rows for each phone number.

• 2NF (Second Normal Form):

  A relation is in 2NF if:

  • It is already in 1NF.
  • All non-key attributes are fully functionally dependent on the primary key.
  • There are no partial dependencies on part of a composite key.

  Example:

  If a table has a composite key (Student_ID, Course_ID) and the Department column depends only on Course_ID, it violates 2NF. Removing this partial dependency fixes it.

11. Explain BCNF.

• BCNF (Boyce-Codd Normal Form):

  A relation is in BCNF if:

  • It is already in 3NF.
  • For every functional dependency X → Y, X should be a super key (i.e., it must uniquely identify every tuple).

  BCNF handles cases where 3NF fails to eliminate certain anomalies caused by functional dependencies.

  Example:

  If a table contains two candidate keys, and one non-prime attribute depends on part of one candidate key, it violates BCNF. Fixing this requires reorganizing the table.

12. Define Armstrong Axioms.

Armstrong Axioms are a set of rules used to derive functional dependencies in a relation. These rules help in normalizing the database and finding closure of attributes.

1. Reflexivity:

   If Y is a subset of X, then X → Y.

   • Example: {Student_ID, Name} → Name is valid because Name is part of the left side. 2. Augmentation:

   If X → Y, then XZ → YZ (adding more attributes does not change the dependency).

   • Example: If Student_ID → Name, then Student_ID, Age → Name, Age is also valid. 3. Transitivity:

   If X → Y and Y → Z, then X → Z.

   • Example: If Student_ID → Department and Department → Head, then Student_ID → Head. 4. Union:

   If X → Y and X → Z, then X → YZ.

   • Example: If Student_ID → Name and Student_ID → Age, then Student_ID → {Name, Age}. 5. Decomposition:

   If X → YZ, then X → Y and X → Z.

   • Example: If Student_ID → {Name, Age}, then Student_ID → Name and Student_ID → Age. 6. Pseudotransitivity:

   If X → Y and WY → Z, then WX → Z.

   • Example: If Student_ID → Department and Department, College → Head, then Student_ID, College → Head.

Long Answer Questions – 2 Marks

1. List out various Database users with their roles.

Database users can be categorized based on their interaction and responsibilities within a database system:

• Database Administrators (DBAs):
  • Roles:
    • Installation, configuration, and maintenance of the database system.
    • User management (creating accounts, assigning permissions).
    • Performance tuning and monitoring.
    • Backup and recovery.
    • Security management.
  • They have the highest level of access and control.
• Application Programmers/Developers:
  • Roles:
    • Write application code that interacts with the database.
    • Design and implement data access logic.
    • Embed SQL queries within applications.
    • They focus on the application layer.
• End Users:
  • Roles:
    • Access and manipulate data through applications or query interfaces.
    • Generate reports and perform data analysis.
    • They interact with the database through pre-built applications.
• Database Designers:
  • Roles:
    • Define the structure of the database (schemas, tables, relationships).
    • Perform data modeling and normalization.
    • Ensure data integrity and consistency.
  • They focus on the logical and physical design.
• Data Analysts/Data Scientists:
  • Roles:
    • Extract and analyze data for insights.
    • Develop data models and visualizations.
    • Perform statistical analysis and data mining.
    • They use the database to gain information.

2. List and explain different data models.

Data models represent how data is organized and related within a database. Key models include:

• Hierarchical Model:
  • Data is organized in a tree-like structure with parent-child relationships.
  • Simple to understand but inflexible.
  • Example: Early IBM Information Management System (IMS).
• Network Model:
  • An extension of the hierarchical model, allowing a record to have multiple parent records.
  • More flexible but complex.
  • Example: Integrated Data Store (IDS).
• Relational Model:
  • Data is organized into tables with rows and columns.
  • Uses SQL for data manipulation.
  • Highly flexible and widely used.
  • Example: MySQL, PostgreSQL, Oracle.
• Entity-Relationship (ER) Model:
  • A high-level conceptual model that represents entities and their relationships.
  • Used for database design.
  • Visual representation using ER diagrams.
• Object-Oriented Model:
  • Data is represented as objects with attributes and methods.
  • Supports complex data types and inheritance.
  • Example: ObjectDB, GemStone/S.
• NoSQL Models:
  • Designed for handling large volumes of unstructured or semi-structured data.
  • Includes document, key-value, column-family, and graph databases.
  • Examples: MongoDB (document), Redis (key-value), Cassandra (column-family), Neo4j (graph).

3. Explain the components of Database Architecture.

A typical database architecture consists of:

• Hardware:
  • Servers, storage devices, and network infrastructure.
  • Provides the physical resources for the database.
• Software:
  • Database Management System (DBMS): Manages the database.
  • Operating system: Provides the platform for the DBMS.
  • Application software: Interacts with the database.
• Data:
  • The actual information stored in the database.
  • Includes tables, indexes, and other database objects.
• Users:
  • Individuals who interact with the database (DBAs, developers, end users).
• Procedures:
  • Rules and guidelines for using and managing the database.
  • Includes backup and recovery procedures, security protocols, and performance tuning guidelines.
• Database Language:
  • Such as SQL, which is used to interact with the database.

4. List various relational algebra operations with one example each.

Relational algebra operations manipulate relations (tables).

• Select (σ):
  • Filters rows based on a condition.
  • Example: σage > 30(Employees) (selects employees older than 30).
• Project (π):
  • Selects specific columns.
  • Example: πname, salary(Employees) (selects name and salary columns).
• Union (∪):
  • Combines rows from two tables.
  • Example: Employees ∪ FormerEmployees.
• Intersection (∩):
  • Returns rows common to two tables.
  • Example: FullTimeEmployees ∩ Managers.
• Difference (-):
  • Returns rows from the first table that are not in the second.
  • Example: Employees - TerminatedEmployees.
• Cartesian Product (×):
  • Combines every row from one table with every row from another.
  • Example: Departments × Employees.
• Rename (ρ):
  • Renames a relation or attribute.
  • Example: ρNewEmployees(Employees).
• Join (⋈):
  • Combines rows from two tables based on a related column.
  • Example: Employees ⋈Employees.dept_id = Departments.dept_id Departments.

5. Explain select, project and Cartesian product operations in relational algebra with an example?

• Select (σ):
  • Filters rows based on a condition.
  • Example:
    • Table: Students (id, name, age, major)
    • σmajor = ‘Computer Science’(Students)
    • Result: Returns all rows where the major is ‘Computer Science’.
• Project (π):
  • Selects specific columns.
  • Example:
    • Table: Students (id, name, age, major)
    • πname, major(Students)
    • Result: Returns a table with only the name and major columns.
• Cartesian Product (×):
  • Combines every row from one table with every row from another.
  • Example:
    • Table: Departments (dept_id, dept_name)
    • Table: Employees (emp_id, emp_name, dept_id)
    • Departments × Employees
    • Result: Returns a table with all possible combinations of rows from Departments and Employees.

6. List various relational algebra operations with one example each.

(This is a repeat of question 4, so the answer is the same)

7. Explain select, project and Cartesian product operations in relational algebra with an example?

(This is a repeat of question 5, so the answer is the same)

8. Write an overview of NoSQL.

NoSQL (Not Only SQL) databases are designed to handle large volumes of unstructured, semi-structured, and structured data. They offer flexibility, scalability, and high performance. Key characteristics include:

• Schema-less or flexible schemas.
• Horizontal scalability (distributed architecture).
• Support for various data models (document, key-value, column-family, graph).
• Emphasis on availability and partition tolerance (CAP theorem).
• Suitable for use cases involving big data, real-time data, and cloud computing.
• Types include:
  • Key-value stores
  • Document databases
  • Column-family stores
  • Graph databases.

9. What are set operators? Explain various set operations that can be performed using a query.

Set operators combine the results of two or more SELECT queries. They include:

• UNION:
  • Combines the results of two SELECT queries, removing duplicate rows.
  • Example: SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
• UNION ALL:
  • Combines the results of two SELECT queries, including duplicate rows.
  • Example: SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;
• INTERSECT:
  • Returns rows that are common to both SELECT queries.
  • Example: SELECT column1 FROM table1 INTERSECT SELECT column1 FROM table2;
• MINUS (or EXCEPT):
  • Returns rows from the first SELECT query that are not in the second.
  • Example: SELECT column1 FROM table1 MINUS SELECT column1 FROM table2;

10. Define 1NF and 2NF.

• 1NF (First Normal Form):
  • Each column contains atomic values (indivisible).
  • There are no repeating groups of columns.
  • Example: A table where each cell has only one value, not a list of values.
• 2NF (Second Normal Form):
  • It must be in 1NF.
  • All non-key attributes are fully functionally dependent on the entire primary key. This means that if the primary key is a composite key (made up of multiple columns), every non-key attribute must depend on all of those columns, not just some of them.
  • Example: If a table has columns (StudentID, CourseID, CourseName, Instructor), and (StudentID, CourseID) is the primary key, then CourseName and Instructor must depend on both StudentID and CourseID. If Instructor only depended on CourseID, the table would not be in 2NF.

11. Explain select, project and Cartesian product operations in relational algebra with an example?

(This is a repeat of question 5, so the answer is the same)

• Select (σ):
  • Filters rows based on a condition.
  • Example:
    • Table: Students (id, name, age, major)
    • σmajor = ‘Computer Science’(Students)
    • Result: Returns all rows where the major is ‘Computer Science’.
• Project (π):
  • Selects specific columns.
  • Example:
    • Table: Students (id, name, age, major)
    • πname, major(Students)
    • Result: Returns a table with only the name and major columns.
• Cartesian Product (×):
  • Combines every row from one table with every row from another.
  • Example:
    • Table: Departments (dept_id, dept_name)
    • Table: Employees (emp_id, emp_name, dept_id)
    • Departments × Employees
    • Result: Returns a table with all possible combinations of rows from Departments and Employees.

12. Explain the advantages of UNION operation with example?

The UNION operation in SQL combines the result sets of two or more SELECT statements and removes duplicate rows.

• Advantages:
  • Consolidated Data: It allows you to combine data from multiple tables or queries into a single result set, providing a unified view.
  • Simplified Reporting: Instead of running multiple queries and manually combining the results, you can use UNION to get the combined data in one go, simplifying report generation.
  • Data Aggregation: Useful for aggregating data from different sources that have the same structure.

• Example:

  • Suppose you have two tables: CustomersUSA and CustomersEurope, both with columns CustomerID and Name.

  • CustomersUSA:

    CustomerID	Name
    1	John
    2	Alice

  • CustomersEurope:

    CustomerID	Name
    3	Bob
    1	John

  • Query:

    ```sql SELECT CustomerID, Name FROM CustomersUSA UNION SELECT CustomerID, Name FROM CustomersEurope;

    ```

  • Result:

    CustomerID	Name
    1	John
    2	Alice
    3	Bob

    (Note that the duplicate “John” is removed)

    ---

13. Write about four types of NoSQL Databases with example.

NoSQL databases are designed to handle various data types and large volumes of data. Here are four main types:

• Key-Value Stores:
  • Data is stored as key-value pairs.
  • Optimized for fast read and write operations.
  • Simple and scalable.
  • Example: Redis, Memcached, Amazon DynamoDB.
  • Use Case: Caching, session management, storing user preferences.
• Document Databases:
  • Data is stored in document format (e.g., JSON, BSON).
  • Flexible schema, allowing for nested data.
  • Good for content management and semi-structured data.
  • Example: MongoDB, CouchDB.
  • Use Case: Content management systems, product catalogs, user profiles.
• Column-Family Stores:
  • Data is organized into columns and column families.
  • Highly scalable and efficient for read/write operations on large datasets.
  • Suitable for analytical workloads.
  • Example: Apache Cassandra, HBase.
  • Use Case: Time-series data, logging, large-scale data analytics.
• Graph Databases:
  • Data is stored as nodes and edges, representing relationships.
  • Optimized for querying complex relationships.
  • Useful for social networks, recommendation systems, and fraud detection.
  • Example: Neo4j, Amazon Neptune.
  • Use Case: Social networks, recommendation engines, fraud detection, knowledge graphs.

14. Differentiate SQL with NoSQL

Here’s a table summarizing the key differences between SQL and NoSQL databases:

15. Explain pitfalls of RDBD.

RDBD stands for Relational Database. While Relational Databases are very useful, they also have some pitfalls:

• Scalability Limitations:
  • Vertical scaling (adding more resources to a single server) can become expensive and has limits.
  • Horizontal scaling (distributing data across multiple servers) can be complex to implement with traditional RDBMS.
• Performance Issues with Large Data:
  • Complex joins and transactions can become slow with massive datasets.
  • Query optimization becomes crucial and challenging.
• Schema Rigidity:
  • The fixed schema can make it difficult to adapt to changing data requirements.
  • Schema migrations can be time-consuming and disruptive.
• Object-Relational Impedance Mismatch:
  • Mapping objects in object-oriented programming languages to relational tables can be complex and lead to performance overhead.
• Handling Unstructured Data:
  • RDBMS are not well-suited for storing and processing unstructured data like text, images, and videos.
• Complexity:
  • Designing and maintaining a relational database, especially for complex applications, can be challenging.
• Cost:
  • Commercial RDBMS can be expensive, and even open-source options may require significant expertise to manage effectively.