UG BCA -S2 - Database Management Systems, Second Internal Examination, April 2024

 

Section A

Answer any 10 questions. Each question carries 2 marks

1. Discuss database users: Database users are individuals or entities who interact with a database system. They can be categorized into different roles based on their interactions:
• End Users: They interact with the database through application programs or interfaces. They typically perform tasks like querying, updating, and generating reports.
• Database Administrators (DBAs): They manage and maintain the database system, including tasks like security, backup, recovery, and performance tuning.
• Application Programmers: They develop applications that interact with the database. They write code to perform CRUD operations (Create, Read, Update, Delete) and to implement business logic.
• System Analysts: They analyze user requirements and design the database schema and structure accordingly.
• Data Analysts: They analyze the data stored in the database to derive insights and make data-driven decisions.
2. What is Data Model? What are the different types of Data models?: A data model is a conceptual representation of the data structures and relationships within a database. It provides a framework for organizing and representing data. Different types of data models include:
• Hierarchical Model
• Network Model
• Relational Model
• Entity-Relationship Model
• Object-Oriented Model
• Object-Relational Model
• NoSQL Models (Document, Key-Value, Columnar, Graph)
3. What is the difference between procedural and nonprocedural DMLs?: Procedural DMLs require users to specify what data is needed and how to get it, meaning they involve specifying the steps to achieve the desired outcome. Nonprocedural DMLs, on the other hand, only require users to specify what data is needed, leaving the system to figure out how to retrieve it. SQL is an example of a nonprocedural DML.
4. What do you mean by the cardinality of a relationship?: Cardinality in the context of database relationships refers to the number of instances of one entity that can be associated with a single instance of another entity. It describes the maximum and minimum number of occurrences of one entity that may be associated with each occurrence of the other entity in a relationship. Common cardinality types include one-to-one, one-to-many, and many-to-many.
5. What is the domain of an Attribute?: The domain of an attribute is the set of all possible values that the attribute can take. It defines the range of values that are valid for that attribute in a database table.
6. Write about LIKE operator with an example: The LIKE operator is used in SQL queries to search for a specified pattern in a column. It is often used with wildcard characters, such as '%' (percent sign) which matches any sequence of characters, and '_' (underscore) which matches any single character. Example:

sqlCopy code

SELECT * FROM employees WHERE last_name LIKE 'Sm%';

This query selects all employees whose last names start with 'Sm'.

7. Explain the term UNIQUE: UNIQUE constraint in database design ensures that all values in a column are different. It enforces the uniqueness of values within a column, meaning no two rows can have the same value for that column.
8. Note out the aggregate functions: Aggregate functions in SQL are functions that perform a calculation on a set of values and return a single value. Common aggregate functions include:
• SUM: Calculates the sum of values in a column.
• AVG: Calculates the average of values in a column.
• COUNT: Counts the number of rows in a result set or the number of non-null values in a column.
• MAX: Returns the maximum value in a column.
• MIN: Returns the minimum value in a column.
9. What you mean by Transitive Dependency?: Transitive dependency occurs in a relational database when a non-prime attribute is functionally dependent on another non-prime attribute, rather than on the primary key. It essentially means that one non-prime attribute is determined by another non-prime attribute, which in turn is determined by the primary key.
10. Explain Boyce-Codd Normal Form: Boyce-Codd Normal Form (BCNF) is a higher level of database normalization than the third normal form (3NF). A relation is in BCNF if and only if every determinant is a candidate key. In simpler terms, in a BCNF relation, there are no non-trivial functional dependencies of attributes on anything other than a candidate key.
11. What are threats to the database: Threats to databases include:
• Unauthorized access: Unauthorized users gaining access to sensitive data.
• Data breaches: Theft or leakage of sensitive data.
• Data corruption: Intentional or unintentional alteration of data.
• Malware and viruses: Infecting databases with malicious software.
12. Which are the control measures used to protect the database?:

 

• Access control: Implementing user authentication and authorization mechanisms.
• Encryption: Encrypting data at rest and in transit to prevent unauthorized access.
• Regular backups: Performing regular backups to ensure data can be recovered in case of loss or corruption.
• Auditing and logging: Monitoring database activities and maintaining logs for security analysis.

 

 

                                                                                (10 x 2 =20 Marks)

Section B

Answer any 6 questions. Each question carries 5 marks.

 

13. Discuss the main characteristics of the database approach and how it differs from traditional file systems: The main characteristics of the database approach include:

• Data Independence: Data is logically and physically separate from applications, allowing changes to the data structure without affecting applications.
• Data Integration: Data from various sources can be integrated into a single database, facilitating efficient data management and retrieval.
• Data Sharing: Multiple users and applications can access and manipulate data concurrently, ensuring data consistency and integrity.
• Data Security: Access to data can be controlled through authentication, authorization, and encryption mechanisms, ensuring data privacy and security.
• Data Integrity: Database systems enforce data integrity constraints to maintain the accuracy and consistency of data.
• Concurrent Access: Database systems support concurrent access by multiple users, managing conflicts and ensuring data consistency.

• Data Redundancy: File systems often lead to data redundancy, whereas databases minimize redundancy through normalization.
• Data Independence: Database systems provide logical and physical data independence, allowing changes to be made without affecting applications.
• Data Integrity: Database systems enforce data integrity constraints to maintain data consistency, which may not be enforced in file systems.
• Data Security: Database systems offer robust security features such as access control and encryption, which are often lacking in file systems.
• Data Sharing: Database systems facilitate data sharing among multiple users and applications, whereas file systems may lack mechanisms for concurrent access and sharing.

14. What is the difference between logical data independence and physical data independence?:

• Logical Data Independence: It refers to the ability to change the logical schema of the database without changing the external schema or application programs. Changes to the logical schema include adding or modifying tables, views, or relationships. Applications are unaffected by such changes as long as the external schema remains the same.
• Physical Data Independence: It refers to the ability to change the physical storage structures or devices without affecting the logical schema or application programs. Changes to the physical schema include reorganizing data storage, indexing methods, or file organization. Applications remain unaffected as long as the external schema and logical schema remain unchanged.

15. Explain the terms Primary Key, Candidate Key, Super Key with examples:

• Primary Key: A primary key is a unique identifier for a record in a table. It must be unique for each record and cannot contain null values. Example: In a table of students, the StudentID could be the primary key.
• Candidate Key: A candidate key is a set of attributes that uniquely identifies a record in a table. A table can have multiple candidate keys, out of which one is chosen as the primary key. Example: In a table of employees, both EmployeeID and SocialSecurityNumber could be candidate keys.
• Super Key: A super key is a set of attributes that uniquely identifies a record in a table. It may contain more attributes than necessary to uniquely identify records. Example: In a table of customers, {CustomerID, Name} could be a super key because it uniquely identifies each customer, although CustomerID alone would suffice as the primary key.

16. Explain the structure of ER diagram: An Entity-Relationship (ER) diagram is a graphical representation of entities, attributes, and relationships within a database. It consists of three main components:

• Entities: Represented as rectangles, entities are objects or concepts about which data is stored. Examples include "Student," "Course," or "Employee."
• Attributes: Represented as ovals, attributes are properties or characteristics of entities. Examples include "StudentID," "CourseName," or "EmployeeName."
• Relationships: Represented as diamonds, relationships describe how entities are related to each other. Examples include "Enrolls in," "Works for," or "Teaches."

17. Explain data types used in SQL: SQL supports various data types for storing different types of data. Common data types include:

• Numeric: INTEGER, FLOAT, DECIMAL
• Character: CHAR, VARCHAR, TEXT
• Date and Time: DATE, TIME, DATETIME, TIMESTAMP
• Boolean: BOOLEAN
• Binary: BLOB, BYTEA

18. Explain SET OPERATORS in SQL with examples: SQL provides set operators to perform operations on the result sets of two or more SELECT queries. The main set operators are UNION, INTERSECT, and EXCEPT (or MINUS). Example:

-- UNION: Returns all unique rows from both result sets SELECT column1 FROM table1 UNION SELECT column1 FROM table2; -- INTERSECT: Returns rows that appear in both result sets SELECT column1 FROM table1 INTERSECT SELECT column1 FROM table2; -- EXCEPT (or MINUS): Returns rows from the first result set that are not in the second result set SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2;

19. Explain the following: a) 1NF (First Normal Form): Each column in a table must have atomic values, and each record must be unique. No repeating groups or arrays are allowed. Example: Each cell of a table should contain a single value, and there should be a primary key to uniquely identify each record. b) 2NF (Second Normal Form): It must satisfy the conditions of 1NF and all non-key attributes are fully functional dependent on the primary key. Example: If a table has a composite primary key, each non-key attribute should depend on the entire key, not just part of it. c) 3NF (Third Normal Form): It must satisfy the conditions of 2NF, and no transitive dependencies should exist. Transitive dependency occurs when a non-key attribute depends on another non-key attribute, which is not a part of the primary key. Example: If A determines B, and B determines C, then A should directly determine C, without depending on B.

 

20. Explain the anomalies in relational schema design: Anomalies in relational schema design refer to issues that arise due to improper normalization, leading to data inconsistency and redundancy. The main anomalies include:
• Insertion Anomaly: Inability to insert data into the database without adding unrelated data. For example, in a table of students and courses, you can't insert a new student until they're enrolled in at least one course.
• Update Anomaly: Inconsistencies that occur when updating data in one place but not in others, leading to data inconsistency. For example, updating a student's address in one record but not in others.
• Deletion Anomaly: Loss of unrelated data when deleting a record. For example, deleting a student who is enrolled in a course results in the loss of course information.
21. Explain the four concurrency control problems: Concurrency control problems arise in multi-user database systems where multiple transactions are executed concurrently. The four main concurrency control problems are:
• Lost Update: Occurs when two transactions try to update the same data simultaneously, resulting in one update overwriting the other, causing the loss of one update.
• Dirty Read: Occurs when one transaction reads data that has been modified by another transaction

          (6 x 5 = 30 Marks)

 

Section C

 

Answer any 2 questions. Each question carries 15 marks

22. System architecture of DBMS: The system architecture of a Database Management System (DBMS) typically comprises several components, each playing a crucial role in managing and accessing data efficiently. Here's a simplified overview:

• Users: These are the individuals or applications interacting with the database system.
• Query Processor: It handles user queries and translates them into low-level instructions that the DBMS can understand.
• Transaction Manager: Manages transactions, ensuring their atomicity, consistency, isolation, and durability (ACID properties).
• Buffer Manager: Responsible for caching data in memory, reducing the need for frequent disk access, thus improving performance.
• File Manager: Controls access to the physical database files on disk, including reading, writing, and organizing data.
• Data Manager: Coordinates the storage and retrieval of data from the underlying storage system, managing indexes, and data structures.
• Security Manager: Ensures data security by enforcing access control policies, authentication, and encryption.
• Concurrency Control: Handles concurrent access to data, preventing conflicts and ensuring data consistency.
• Backup and Recovery Manager: Manages backup and recovery operations to safeguard against data loss or corruption.
• Database Administrator (DBA): Responsible for overall management and maintenance of the database system, including schema design, performance tuning, and security.

23. Short notes on (a) DDL and DML commands used in SQL:

• DDL (Data Definition Language): DDL commands are used to define, modify, and delete database objects such as tables, indexes, and views. Common DDL commands include CREATE, ALTER, and DROP. Examples include creating tables, modifying table structures, and deleting tables.
• DML (Data Manipulation Language): DML commands are used to manipulate data within the database. Common DML commands include SELECT, INSERT, UPDATE, and DELETE. Examples include querying data from tables, inserting new records, updating existing records, and deleting records.

(b) SQL statement to create STUDENT table consisting of name and mark:

sqlCopy code

CREATE TABLE STUDENT ( name VARCHAR(50), mark INT );

24. Indexing: Indexing is a database optimization technique used to improve the speed of data retrieval operations on a table. It involves creating an index data structure that contains keys extracted from one or more columns of the table, along with pointers to the corresponding rows in the table.

• Types of Indexes:
• Primary Index: Created on the primary key column(s) of a table.
• Secondary Index: Created on non-primary key column(s) to speed up queries.
• Clustered Index: Determines the physical order of rows in a table.
• Non-Clustered Index: Stores index entries separately from the actual data rows.
• Advantages:
• Faster Data Retrieval: Indexes allow the database system to quickly locate the rows that satisfy the search criteria.
• Improved Performance: By reducing the number of disk I/O operations, indexing improves overall query performance.
• Efficient Data Modification: Although indexes speed up data retrieval, they may slow down data modification operations like INSERT, UPDATE, and DELETE.
• Disadvantages:
• Increased Storage Overhead: Indexes require additional storage space to store index data structures.
• Overhead in Maintenance: Indexes need to be updated whenever the indexed columns are modified, leading to overhead during data modification operations.
• Index Fragmentation: Over time, indexes may become fragmented, reducing their efficiency and requiring periodic maintenance.

25. Transaction and its states with a neat diagram:

• Transaction: A transaction is a logical unit of work consisting of one or more database operations that must be executed atomically, consistently, isolated from other transactions, and durably stored. Transactions ensure data integrity and consistency in a database system.
• States of a Transaction: A transaction goes through several states during its execution:
1. Active: The initial state where the transaction is actively executing operations.
2. Partially Committed: The transaction has executed all its operations successfully and is waiting to be committed.
3. Committed: The transaction has been successfully completed, and its changes are permanently stored in the database.
4. Failed: The transaction encountered an error or exception during execution and cannot proceed further.
5. Aborted: The transaction has been rolled back to its initial state, and any changes made by the transaction are undone.

• ACID Properties of Transaction:
• Atomicity: A transaction is atomic, meaning it is either fully executed or not executed at all. Partially completed transactions are rolled back.
• Consistency: Transactions bring the database from one consistent state to another consistent state. All integrity constraints are preserved.
• Isolation: Transactions are isolated from each other, ensuring that concurrent execution does not interfere with each other's operations.
• Durability: Once a transaction is committed, its changes are durable and remain unaffected by system failures.

These properties collectively ensure the reliability and integrity of transactions in a database system.

 

                                                                                

(2 X 15 = 30 Marks)