GTU Database Management System (DBMS) Sem 3 Winter 2022 Previous Year Paper Solution

Q1

(a) Define following terms

  1. Data Abstraction

    Data abstraction in databases is the process of hiding the complexities of the database from the user and presenting a simpler interface. It involves presenting only necessary information, simplifying the interaction with the database.

  2. Instance

    An instance refers to the state of the database at a specific moment. It represents the actual data contained in the database at that particular time.

  3. Logical Data independence

    Logical data independence is the ability to change the logical schema of a database without altering the external schema or application programs. This means changes to the logical structure of the database (like altering tables) do not affect how the data is accessed by users or applications.

(b) List the type of database users. Explain their characteristics in brief, how they interact with DBMS?

Types of Database Users

  1. End Users:
    • Characteristics: Final consumers of data, often with limited database knowledge.
    • Interaction with DBMS: Use user-friendly interfaces, forms, and reports for data retrieval.
  2. Application Programmers:
    • Characteristics: Develop database applications, proficient in database concepts.
    • Interaction with DBMS: Embed SQL queries in code for application development.
  3. Database Administrators (DBAs):
    • Characteristics: Manage and secure the database system, deep knowledge of DBMS.
    • Interaction with DBMS: Use administrative tools for system configuration and maintenance.
  4. System Analysts and Designers:
    • Characteristics: Define database structure and requirements, plan data organization.
    • Interaction with DBMS: Work with DBMS during database design, use data modeling tools.

(c) State the advantages of Database management systems over file processing system.

  1. Data Integrity and Accuracy:
    • DBMS enforces data integrity constraints, ensuring that data remains accurate and consistent. File systems lack built-in mechanisms for maintaining data integrity, making them prone to errors and inconsistencies.
  2. Data Sharing and Security:
    • DBMS allows concurrent access by multiple users while ensuring data security through authentication and authorization mechanisms. File systems often lack security features, making it difficult to control access to data.
  3. Data Independence:
    • DBMS separates the logical and physical data structures, providing logical data independence. Changes to the database schema do not affect application programs. In contrast, file systems tightly couple data with application code, making schema changes disruptive.
  4. Data Redundancy Reduction:
    • DBMS minimizes data redundancy through normalization techniques, reducing storage space and maintenance efforts. File systems may lead to data duplication and inconsistency.
  5. Data Retrieval and Query Language:
    • DBMS provides a powerful query language (SQL) for retrieving and manipulating data. File systems lack query languages, making data retrieval complex and time-consuming.
  6. Transaction Management:
    • DBMS supports ACID properties (Atomicity, Consistency, Isolation, Durability) for transactions, ensuring data consistency even in the presence of failures. File systems do not offer transaction management capabilities.
  7. Backup and Recovery:
    • DBMS offers features for automated backup, recovery, and point-in-time restoration. File systems may require manual backup and lack comprehensive recovery options.
  8. Data Scalability and Performance Optimization:
    • DBMS can be optimized for performance with indexing, caching, and query optimization techniques. File systems often struggle to scale efficiently as data grows.
  9. Data Dictionary and Metadata:
    • DBMS maintains a data dictionary that provides a centralized repository for metadata and data definitions. File systems lack such centralized metadata management.
  10. Concurrency Control:
    • DBMS handles concurrent data access with locking and isolation mechanisms, ensuring that multiple users can work on the data simultaneously without conflicts. File systems lack built-in concurrency control.

Q2

(a) Differentiate generalization and specialization.

AspectGeneralizationSpecialization
PurposeCreates a more abstract entityCreates more specific entities
RelationshipRepresents an "is-a" relationshipRepresents an "is-a" relationship
StructureForms a hierarchyForms a hierarchy
InheritanceMay inherit attributes and behaviorsInherits attributes and behaviors

(b) What are the types of attributes used in ER diagram?

  1. Simple Attribute:
    • A simple attribute represents a single, atomic value and cannot be divided any further. For example, "Age" is a simple attribute.
  2. Composite Attribute:
    • A composite attribute is composed of multiple simple attributes, each with its meaning. For example, an "Address" attribute can be composed of sub-attributes like "Street," "City," "State," and "ZIP Code."
  3. Derived Attribute:
    • A derived attribute's value is calculated or derived from other attributes in the database. It is not stored explicitly but computed when needed. For example, "Age" can be a derived attribute calculated from the "Date of Birth."
  4. Multi-valued Attribute:
    • A multi-valued attribute can have multiple values for a single entity. For example, an entity "Person" may have a multi-valued attribute "Phone Numbers" to represent multiple phone numbers associated with that person.

(c) Draw an E-R diagram of following scenario. Make necessary assumptions and clearly note down the assumptions. We would like to make college’s semester fee collection system fully computerized. Fees may include your term fees , library fees, gymkhana fees etc..

OR

(c) Explain the concept of total participation, partial participation, strong entity set and weak entity set using ER diagram

Q3

(a) Define the terms

  1. Primary Key

    A primary key is a unique identifier for each record in a table, ensuring no duplicates and enabling relationships with other tables.

  2. Unique Key

    A unique key enforces uniqueness in specific table columns but doesn't serve as the primary identifier. Multiple unique keys can exist in a table.

  3. Foreign Key

    A foreign key links one table to another by referencing the primary key of another table, ensuring referential integrity in the database.

(b) Explain transitive and trivial functional dependencies.

  1. Transitive Functional Dependency:
    • A transitive functional dependency occurs in a relational database when the value of one attribute (non-prime attribute) in a table can be determined by a combination of other attributes through a series of functional dependencies.
    • In simple terms, if A → B and B → C, then there is a transitive functional dependency A → C.
    • For example, consider a table with attributes (A, B, C), where A determines B (A → B) and B determines C (B → C). In this case, there is a transitive functional dependency A → C, as the value of C can be determined indirectly through A.
  2. Trivial Functional Dependency:
    • A trivial functional dependency is a straightforward and self-evident relationship between attributes in a table.
    • It occurs when an attribute (or set of attributes) functionally determines itself or another attribute in a straightforward manner.
    • For example, if an attribute A directly determines itself (A → A), it is a trivial functional dependency. Similarly, if an attribute A directly determines another attribute B (A → B) and B is a subset of A, it is also a trivial dependency.

(c) Suppose a relational schema R (A B C D E) and set of functional dependencies

OR

Q.3 (a) What is the significance of normalization?

  1. Data Efficiency: Normalization reduces data redundancy, making storage more efficient.
  2. Data Integrity: It minimizes anomalies, ensuring data accuracy and integrity.
  3. Simplified Maintenance: Easier updates and reduced risk of errors during maintenance.
  4. Efficient Queries: Well-structured data leads to faster and more efficient queries.
  5. Scalability: Supports future growth and changing requirements.

(b) Explain Armstrong’s axioms.

Armstrong's axioms are a set of inference rules used in formal relational database theory, primarily for reasoning about functional dependencies. These axioms help derive new functional dependencies from given ones. There are three main axioms in Armstrong's axioms:

  1. Reflexivity Axiom (Reflexivity Rule):
    • This axiom states that if you have a set of attributes (X) within a relation, then X functionally determines itself. In other words, X → X.
    • For example, if you know that {A, B} → C, you can apply the reflexivity axiom to derive {A, B} → {A, B}.
  2. Augmentation Axiom (Augmentation Rule):
    • This axiom allows for adding attributes to both sides of a functional dependency. If X → Y is a functional dependency, and Z is a set of attributes, then XZ → YZ.
    • For instance, if you have A → B, you can use the augmentation axiom to deduce AB → BB.
  3. Transitivity Axiom (Transitivity Rule):
    • This axiom enables the transitive inference of functional dependencies. If you have two functional dependencies X → Y and Y → Z, you can derive X → Z.
    • For example, if you know that A → B and B → C, you can apply the transitivity axiom to conclude that A → C.

Rules of Axioms

Below are the rules of Axioms in DBMS:

  1. Decomposition

    If A→BC, then A→B and A→C

  2. Composition

    If A→B and C→D then AC→BD

  3. Union (Notation)

    If A→B and A→C then A→B

  4. Self-determination

    A→A for any given A

Q3 (c) Suppose a relational schema R (A B C D E F G H I) and set of functional dependencies

Q4

(a) List the type of storages in DBMS. Explain in brief: indexed based accessing

In a Database Management System (DBMS), there are various storage types, including:

  1. Primary Storage (RAM): Fast, volatile storage used for current processing.
  2. Secondary Storage (Disk): Persistent storage for long-term data storage.
  3. Tertiary Storage (Tape, Optical): Used for archival and backup purposes.

Indexed-Based Accessing:

  • Index: Data structure mapping data values to physical storage locations.
  • How it works: Speeds up data retrieval by quickly locating data on disk.
  • Benefits: Faster retrieval, reduced disk I/O, improved query performance.

(b) Explain authorization and authentication with respect to database security.

Authentication:

  1. Identity Verification: It confirms users' identities using methods like passwords or biometrics.
  2. Access Control: Serves as the gatekeeper, allowing entry only after successful verification.
  3. Unauthorized Access Prevention: Its main goal is to prevent unauthorized users from accessing the database.
  4. First Line of Defense: Authentication is the initial layer of security, establishing trust in user identities.

Authorization:

  1. Permissions and Privileges: Determines what actions users can perform on data, such as read, write, or delete.
  2. Granular Control: Offers detailed control over who accesses specific data, ensuring security.
  3. Role-Based Access: Associates roles with permissions, simplifying user management.
  4. Data Protection: Safeguards sensitive data by limiting access to authorized users.

(c) Which type of queries would be solved by Division operator? Explain with examples.

The division operator in relational algebra is used to solve certain types of queries that involve finding sets of values that, when combined, match all values in another set.

Division Operator (÷):

  • The division operator takes two relations: R (dividend) and S (divisor)
  • It returns a new relation containing values from R that, for each unique combination of values in S, have all possible values in S associated with them.

Example 1 - University Course Enrollment: Suppose we have two relations:

  • Students(StudentID,StudentName) with student information.
  • Courses(CourseID,CourseName) with course information.
  • Enrollments(StudentID,CourseID) with records of which students are enrolled in which courses.

We can use the division operator to find students who have enrolled in all courses. The query would be:

StudentsWithAllCourses=Students÷(Courses−Enrollments)

The result would be a relation containing students who are enrolled in all available courses.

Example 2 - Job Applicants with Required Skills: Consider two relations:

Applicants(ApplicantID,ApplicantName) with applicant information.

Skills(SkillID,SkillName) with required skills for a job.

ApplicantSkills(ApplicantID,SkillID) with records of which skills each applicant possesses.

The division operator can help find applicants who possess all the required skills for a job. The query would be

QualifiedApplicants=Applicants÷Skills

This would give us a list of applicants who meet all the skill requirements for a specific job.

OR

Q.4 (a) Write short note on : Hashing technique

Hashing is a data organization technique that efficiently stores and retrieves data records using a hash function. This technique is especially useful for quick data access in DBMS, reducing the need for extensive searching. Hashing in DBMS is applied to achieve rapid record retrieval, improving database performance.

It is commonly used for implementing indexing structures like hash tables, ensuring fast and direct access to specific data records within a database.

(b) Explain ACID properties of transaction.

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are fundamental properties that ensure the reliability and integrity of database transactions:

  1. Atomicity: Transactions are treated as indivisible units, ensuring that all operations either succeed together or fail together.
  2. Consistency: Transactions bring the database from one valid state to another, preserving data integrity.
  3. Isolation: Concurrent transactions are isolated to prevent interference and maintain data consistency.
  4. Durability: Once a transaction is committed, its changes are permanent and survive system failures.

(c) With neat diagram steps involved in query processing.

Q5

(a) Explain the concept of deadlock in brief

Deadlock is a situation in a multi-process or multi-threaded system where two or more processes are unable to proceed because they are each waiting for the other to release a resource. In a deadlock, processes become stuck, and the system cannot make any progress.

Three essential conditions must be met for a deadlock to occur:

  1. Mutual Exclusion: Processes must contend for exclusive access to resources, meaning that only one process can use a resource at a time.
  2. Hold and Wait: Processes must hold at least one resource while waiting for another resource, creating a situation where they are not releasing resources they already hold.
  3. Circular Wait: There must be a circular chain of two or more processes, each waiting for a resource that is held by the next process in the chain.

(b) Explain GRANT and REVOKE commands with suitable example

GRANT and REVOKE are SQL commands used for managing database privileges, controlling access to database objects like tables, views, and procedures. Here's a brief explanation with an example:

  1. GRANT:
    • The GRANT command allows a database administrator or owner to give specific privileges to a user or role on a particular database object.
    • Example: Granting SELECT privilege on a "Customers" table to the "SalesTeam" role.
GRANT SELECT ON Customers TO SalesTeam;
  • This command allows members of the "SalesTeam" role to perform SELECT queries on the "Customers" table.
  1. REVOKE:
    • The REVOKE command reverses the privileges granted to a user or role, taking away specific access rights.
    • Example: Revoking the SELECT privilege from the "SalesTeam" role on the "Customers" table.
REVOKE SELECT ON Customers FROM SalesTeam;

This command removes the SELECT privilege from the "SalesTeam" role for the "Customers" table, preventing them from querying it.

(c) Write a PL/SQL function which takes 3 integer numbers as a parameters and return an average of same.

CREATE OR REPLACE FUNCTION CalculateAverage(
    num1 IN NUMBER,
    num2 IN NUMBER,
    num3 IN NUMBER
) RETURN NUMBER
IS
    total NUMBER;
    average NUMBER;
BEGIN
    -- Calculate the total sum of the three numbers
    total := num1 + num2 + num3;

    -- Calculate the average
    average := total / 3;

    -- Return the average
    RETURN average;
END;
/

We can use this PL/SQL function by passing three integer values as parameters, and it will return the average of those numbers. For example:

DECLARE
    result NUMBER;
BEGIN
    result := CalculateAverage(10, 20, 30);
    DBMS_OUTPUT.PUT_LINE('Average: ' || result);
END;

OR

Q.5 (a) Differentiate between Conflict and View Serializability with respect to transaction(any three differences) .

  1. Focus:
    • Conflict Serializability: Focuses on preventing conflicts (read and write) between concurrent transactions.
    • View Serializability: Focuses on ensuring that the final state of the database, considering all transactions, is equivalent to some serial execution.
  2. Criteria:
    • Conflict Serializability: Determines serializability based on conflict operations (read and write conflicts) between transactions.
    • View Serializability: Considers the overall view of transactions and their read/write sets to establish equivalence in the final state.
  3. Example:
    • In Conflict Serializability, it's about avoiding conflicts like reads and writes on the same data.
    • View Serializability is concerned with the overall effect of transactions, not just conflicts, and aims to achieve an equivalent final state.

(b) Enlist types of joins. Explain each with SQL syntax.

Types of Joins in SQL:

  1. INNER JOIN:

    • Explanation: An INNER JOIN returns only the rows that have matching values in both tables. It filters out rows that don't have matching values in the joined columns.
    • SQL Syntax:
    SELECT * FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

  2. LEFT JOIN (or LEFT OUTER JOIN):

    • Explanation: A LEFT JOIN returns all rows from the left table (table1), along with the matching rows from the right table (table2). If there are no matches in the right table, NULL values are returned.
    • SQL Syntax:
    SELECT * FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

  3. RIGHT JOIN (or RIGHT OUTER JOIN):

    • Explanation: A RIGHT JOIN returns all rows from the right table (table2), along with the matching rows from the left table (table1). If there are no matches in the left table, NULL values are returned.
    • SQL Syntax:
    SELECT * FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

  4. FULL OUTER JOIN:

    • Explanation: A FULL OUTER JOIN returns all rows from both tables. If there are no matches in either table, NULL values are returned in the columns of the table that doesn't have a match.
    • SQL Syntax:
    SELECT * FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

  5. CROSS JOIN (or Cartesian Join):

  • Explanation: A CROSS JOIN returns the Cartesian product of rows from both tables, resulting in every possible combination of rows.
  • SQL Syntax:
SELECT * FROM table1
CROSS JOIN table2;
  1. SELF JOIN:
  • Explanation: A SELF JOIN is used when you want to join a table with itself. It's often used to compare rows within the same table.
  • SQL Syntax:
SELECT a.column_name, b.column_name
FROM table_name a, table_name b
WHERE a.common_column = b.common_column;

(c)

TABLE Worker(WORKER_ID INT NOT NULL PRIMARY

KEY,FIRST_NAME CHAR(25), LAST_NAME CHAR(25),SALARY INT(15),JOINING_DATE DATETIME,DEPARTMENT CHAR(25));

TABLE Bonus(WORKER_REF_ID INT,BONUS_AMOUNT

INT(10),BONUS_DATE DATETIME,FOREIGN KEY (WORKER_REF_ID),REFERENCES Worker(WORKER_ID));

TABLE Title(WORKER_REF_ID INT,WORKER_TITLE

CHAR(25),AFFECTED_FROM DATETIME,FOREIGN KEY (WORKER_REF_ID)REFERENCES Worker(WORKER_ID)); Consider above 3 tables ,assume appropriate data and solve following SQL queries

  1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>
SELECT FIRST_NAME AS WORKER_NAME FROM Worker;
  1. Write an SQL query to fetch “FIRST_NAME” from Worker table in uppercase
SELECT UPPER(FIRST_NAME) FROM Worker;
  1. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.
SELECT * FROM Worker ORDER BY FIRST_NAME ASC;
  1. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets
SELECT * FROM Worker WHERE LENGTH(FIRST_NAME) = 6 AND RIGHT(FIRST_NAME, 1) = 'h';
  1. Write an SQL query to print details of the Workers who are also Managers.
SELECT w.* FROM Worker w
INNER JOIN Title t ON w.WORKER_ID = t.WORKER_REF_ID
WHERE t.WORKER_TITLE = 'Manager';
  1. Write an SQL query to fetch departments along with the total salaries paid for each of them.
SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY
FROM Worker
GROUP BY DEPARTMENT;
  1. Write an SQL query to fetch the names of workers who earn the highest salary
SELECT FIRST_NAME
FROM Worker
WHERE SALARY = (SELECT MAX(SALARY) FROM Worker);