Advanced DBMS | 2017 & 2016

 End Term Questions

Q Differentiate between the following: (2017) (a) Procedures and functions in PL/SQL.

Procedures and functions are both subprograms in PL/SQL that can be called by other PL/SQL blocks. However, they have some differences: A procedure is a subprogram that performs a specific action and does not return a value. It is executed using the CALL statement or as part of a larger block.

A function is a subprogram that performs a specific action and returns a value. It can be called from other PL/SQL blocks or SQL statements.

(b) Current database and temporal database.

A current database is a traditional database that stores the current state of data at any given time. It is not designed to track changes over time and does not store historical versions of data. A temporal database is a type of database that stores historical versions of data. It tracks changes to data over time and allows users to query data as it existed at any point in the past. This allows for more accurate reporting and analysis.

(c) Structured types and inheritance in SQL.

Structured types are user-defined types in SQL that can contain multiple attributes of various data types. They allow users to create custom data types that can be used in tables, views, and other database objects.

Inheritance in SQL is a feature that allows users to define new types based on existing types. It is a way of creating new types by inheriting the attributes and behaviors of existing types.

(d) A type x and a reference type ref(x). Under what circumstances would you choose to use a reference type?

A type x is a user-defined data type that can be used to define variables, columns, and other objects in the database.

A reference type ref(x) is a data type that stores a reference to an object of type x rather than the object itself. It is used when you want to store a pointer to an object rather than the object itself, which can be useful in certain situations such as when you need to pass an object as a parameter to a subprogram.

(e) Data definition language and Data manipulation language.

Data definition language (DDL) is a set of SQL commands used to define the structure of the database, including tables, views, indexes, and other objects. DDL commands include CREATE, ALTER, and DROP.

Data manipulation language (DML) is a set of SQL commands used to manipulate the data in the database, including inserting, updating, deleting, and retrieving data from tables. DML commands include SELECT, INSERT, UPDATE, and DELETE.

Q Differentiate between the following:- (2016) (a) SQL and Relational Algebra

SQL is a declarative language used for managing and querying relational databases. It provides a way to interact with data stored in tables and views using a set of commands such as SELECT, INSERT, UPDATE, and DELETE.

Relational algebra is a theoretical language used to describe operations on relational databases, such as selection, projection, and join. It is a mathematical language used to describe the behavior of relational databases.

(b) Distributed database and object oriented database

A distributed database is a database that is spread across multiple computers or sites. It is designed to handle large amounts of data and provide high availability and fault tolerance.

An object-oriented database is a database that stores data in objects rather than in tables. It is designed to provide better support for complex data structures and relationships than relational databases.

(c) Schema and Instance

A schema is a logical view of the database that describes the structure and organization of the data. It defines the tables, columns, relationships, and other database objects.

An instance is a specific realization of the schema at a particular point in time. It includes the data that is currently stored in the database.

(d) Dense and Sparse Index

A dense index is an index that contains an entry for every record in the table, regardless of whether the field being indexed is null or not. It is used when the indexed field has a high selectivity.

A sparse index is an index that contains entries only for the non-null values in the indexed field. It is used when the indexed field has a low selectivity.

Q (a) Explain 3-level architecture of DBMS. What are different types of Database users and explain network view mode of Database. (2016)

The 3-level architecture of DBMS is a design that separates the database system into three components: External level: This level represents the user's view of the database. It describes how users see the data and how they access it. It is also known as the user view or user interface.

Conceptual level: This level represents the logical view of the database. It describes the overall structure of the database and the relationships between the different data elements. It is also known as the logical view or global view.

Internal level: This level represents the physical view of the database. It describes how the data is stored on the physical storage devices, such as hard disks. It is also known as the physical view or storage view.

Different types of database users: End users: These are the people who use the database to perform specific tasks. They may not have any knowledge of the underlying database structure or design.

Application programmers: These are the people who write the programs that access and manipulate the data in the database. They need to have knowledge of the database structure and design.

Database administrators: These are the people who are responsible for managing the database system. They are responsible for tasks such as backup and recovery, security, and performance tuning.

Network view mode of Database:

The network view mode is a way of looking at the relationships between data elements in a database. It shows how data elements are related to each other through a network of interconnected data elements. It is used in network database management systems, which organize data in a hierarchical or network structure. In the network view mode, a record can have multiple parents, which allows for complex relationships between data elements.

(b) Write a trigger which modify the inventory of book, when it is issued to someone. Make assumptions if needed (2016)

Assumptions:

We have a "books" table that contains the book's name, author, and inventory count.

We have an "issues" table that contains the name of the person who issued the book and the date of issue.

Code:

CREATE TRIGGER update_inventory

AFTER INSERT ON issues

FOR EACH ROW

BEGIN

UPDATE books

SET inventory = inventory - 1

WHERE name = NEW.book_name;

END;

Explanation:

The trigger is created with the name "update_inventory" and is set to run after an insert operation on the "issues" table.

The "FOR EACH ROW" clause specifies that the trigger should run once for each row that is inserted into the "issues" table.

The code inside the trigger updates the "inventory" count of the book in the "books" table by subtracting 1 from the current count.

The WHERE clause specifies that the update should only be applied to the book that was issued (i.e., the book name in the "books" table should match the book name in the "issues" table)

Q (a) How transactions are managed in PostgreSQL? (2017)

Transaction Management in PostgreSQL:

PostgreSQL is a powerful open-source relational database management system that supports ACID properties and provides transaction management for maintaining the integrity of data. PostgreSQL uses a multiversion concurrency control (MVCC) mechanism to ensure that transactions are executed in a consistent and isolated manner.

The following are the key features of transaction management in PostgreSQL:

Atomicity: A transaction in PostgreSQL is atomic, which means that it is a single unit of work that is either completed in its entirety or rolled back completely if an error occurs.

Consistency: Transactions in PostgreSQL ensure that the database remains consistent before and after the transaction.

Isolation: Transactions in PostgreSQL are isolated from each other to prevent conflicts and inconsistencies.

Durability: PostgreSQL ensures that all committed transactions are written to disk and are durable, even in the event of a system failure.

(b) Briefly explain database management features supported by IBM (2017)

IBM provides various database management systems that support different features and functionalities. Some of the key features supported by IBM databases are:

High Availability: IBM databases support high availability and provide failover mechanisms for maintaining the uptime of critical applications.

Scalability: IBM databases are designed to scale horizontally and vertically to support growing workloads and changing business needs.

Security: IBM databases provide robust security features, including role-based access control, data encryption, and auditing, to protect sensitive data.

Performance: IBM databases are optimized for high performance and can handle large volumes of data efficiently.

Backup and Recovery: IBM databases support various backup and recovery mechanisms to ensure that data is protected and can be restored in the event of a disaster.

Data Integration: IBM databases support data integration and allow data to be easily imported and exported from different sources.

Management and Monitoring: IBM databases provide tools for managing and monitoring the database environment, including performance tuning, capacity planning, and health monitoring.

Repeated Questions :

Q What is XQuery? What is XPath?

Q (b) What is SOAP web Service? What is the use of SOAP envelope?