Advanced DBMS | End Term 2018
ETCS-423
Q Give Notes the following :
(a) PL/SQL Database Objects
PL/SQL Database Objects: PL/SQL (Procedural Language/Structured Query Language) is a database programming language used to develop database applications. PL/SQL Database Objects are programmatic units that enable you to store and organize your code. Examples of PL/SQL Database Objects include procedures, functions, triggers, packages, and views.
(b) Temporal Database
Temporal Database: A temporal database is a type of database that stores time-varying data, or data that changes over time. Temporal databases can store historical data as well as current data, making them useful for applications that require time-based analysis, such as financial or medical applications.
(c) Cursors
Cursors: A cursor is a database object that enables you to traverse through the results of a SELECT statement and process each row individually. Cursors are often used in stored procedures and functions to process data row-by-row.
(d) Document Schema
Document Schema: A document schema is a set of rules that define the structure and format of a document. In the context of databases, document schemas are used to define the structure of documents stored in a NoSQL database, such as MongoDB.
(e) X-Query & X-Path
X-Query & X-Path: XQuery and XPath are both query languages used to extract data from XML documents. XQuery is a more powerful language that supports more complex queries, while XPath is a simpler language used to select specific elements or attributes within an XML document.
(f) Triggers
Triggers: A trigger is a database object that is automatically executed in response to a specific event, such as an INSERT, UPDATE, or DELETE statement. Triggers are often used to enforce business rules or maintain data integrity in a database.
(g) Schema and Sub-Schema
Schema and Sub-Schema: A schema is a logical container for database objects, such as tables, views, and procedures. It is a way to organize and group database objects together. A sub-schema is a subset of a larger schema, often used to limit the visibility of certain objects to specific users or applications.
Q2 (a) Explain in the difference between Parallel database architecture & Distributed database architecture.
Parallel Database Architecture vs Distributed Database Architecture:
Parallel database architecture is designed to process large volumes of data quickly by dividing a task into smaller pieces and processing them simultaneously on multiple processors. In a parallel database architecture, each processor has its own memory and disk storage, and data is partitioned across the processors.
Distributed database architecture, on the other hand, is designed to store and process data across multiple computers that are connected through a network. In a distributed database architecture, data is stored on multiple servers and can be accessed and processed from any location with network access. Distributed databases can provide better scalability, fault tolerance, and data availability than single-server databases.
(b) Differentiate between fragmentation and replication. Mention all types of fragmentation and replication techniques.
| Technique | Fragmentation | Replication |
|---|---|---|
| Definition | Dividing a database into smaller pieces and distributing them across multiple servers or nodes | Creating multiple copies of data and distributing them across multiple servers |
| Goal | Improve performance by reducing the amount of data that needs to be transferred between servers | Improve availability, scalability, and fault tolerance |
| Types | Horizontal fragmentation, vertical fragmentation | Snapshot replication, transactional replication, merge replication |
Types of Fragmentation:
Horizontal Fragmentation: In horizontal fragmentation, the rows of a table are divided into smaller subsets and distributed across different servers. Each server contains a subset of the rows, and the subset is usually defined by a condition, such as a range of values in a particular column.
Vertical Fragmentation: In vertical fragmentation, the columns of a table are divided into smaller subsets and distributed across different servers. Each server contains a subset of the columns, and the subset is usually defined by the type of data or the frequency of access.
Types of Replication:
Snapshot Replication: In snapshot replication, a complete copy of the data is created at the publisher and distributed to the subscribers at a specific time. The data is then synchronized periodically, typically on a scheduled basis.
Transactional Replication: In transactional replication, changes to the data are tracked in real-time and propagated to the subscribers as they occur. This technique is typically used in high-transaction environments, where changes need to be applied quickly and accurately.
Merge Replication: In merge replication, changes to the data are tracked at both the publisher and subscriber, and then merged together to create a consistent view of the data. This technique is typically used in environments where data needs to be synchronized between multiple locations, such as mobile devices or remote offices.
Q Explain in detail PL/SQL. Also explain Function, Packages & Trigger. Give example of each.
PL/SQL (Procedural Language/Structured Query Language) is a programming language designed specifically for use with Oracle's relational database management system (RDBMS). It is a procedural language, meaning that it allows developers to write programs that execute a series of steps in a specific order. PL/SQL is often used to develop database applications, and it provides the following key features: 1. Support for variables and data types 2. Control flow constructs (such as IF-THEN-ELSE statements and FOR loops) 3. Error handling using exceptions 4. Interaction with SQL commands (such as SELECT, INSERT, UPDATE, and DELETE)
Function: A PL/SQL function is a program that returns a single value. Functions can be used in SQL statements and in other PL/SQL code. Here is an example of a simple function that calculates the area of a circle:
CREATE OR REPLACE FUNCTION calc_area (radius NUMBER)
RETURN NUMBER
IS
pi NUMBER := 3.14159;
BEGIN
RETURN pi * radius * radius;
END;
Packages: A package is a collection of related functions, procedures, and other database objects that can be used together as a single unit. Packages can help to organize your code and make it easier to manage. Here is an example of a simple package that contains two functions:
CREATE OR REPLACE PACKAGE my_package AS
FUNCTION add_nums (a NUMBER, b NUMBER) RETURN NUMBER;
FUNCTION multiply_nums (a NUMBER, b NUMBER) RETURN NUMBER;
END my_package;
CREATE OR REPLACE PACKAGE BODY my_package AS
FUNCTION add_nums (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN a + b;
END;
FUNCTION multiply_nums (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN a * b;
END;
END my_package;
In this example, we assume that the database of employees has a table called employees with columns for employee_id, salary, and department_id. We also assume that we want to give a bonus of $500 to employees in department 10 whose current salary is less than $5000.
The cursor emp_cursor selects all employees in department 10, and the loop iterates over each row returned by the cursor. The FETCH statement retrieves the employee_id and salary for the current row, and the IF statement checks whether the employee's salary is less than $5000. If it is, the UPDATE statement adds the bonus amount to the employee's salary.
Finally, the cursor is closed with the CLOSE statement. This example demonstrates how a cursor can be used to iterate over a result set and perform actions on each row based on certain conditions.
Q What do you understand by Authorization?
Authorization is the process of granting or denying access to resources based on the identity and privileges of the user or system requesting access. In other words, it is the process of determining whether a user or system has the necessary permissions to perform a certain action or access a certain resource. Authorization is a critical part of any secure system, as it ensures that only authorized users or systems are able to access sensitive data or perform critical actions. The process of authorization typically involves the following steps: Authentication: The user or system is verified to ensure that they are who they claim to be. This is typically done using a username and password, digital certificates, or biometric authentication. Authorization policy evaluation: Once the user or system has been authenticated, their access privileges are checked against an authorization policy. This policy specifies which resources they are allowed to access and what actions they are permitted to perform. Access decision: Based on the results of the policy evaluation, the system either grants or denies access to the requested resource. Authorization policies can be defined at various levels of granularity, from individual files or database records to entire systems or networks. In some cases, authorization policies may be dynamic and may change based on the context of the access request or the identity of the user. Overall, authorization is a critical component of any secure system, as it ensures that only authorized users or systems are able to access and manipulate sensitive data and resources.
Q Difference between JDBC & ODBC.
JDBC (Java Database Connectivity) and ODBC (Open Database Connectivity) are two different application programming interfaces (APIs) used to interact with relational databases from different programming languages. Here are the key differences between JDBC and ODBC:
Language Support: JDBC is used for Java programming language, while ODBC is used for C and C++ programming languages.
Platform Support: JDBC is primarily used for connecting to databases from Java applications running on any operating system, while ODBC can be used from various programming languages on Windows and Unix-based platforms.
Native vs. Middleware: JDBC is a native API, meaning that the API is developed using Java programming language and is specific to the platform it is running on. ODBC is a middleware API, meaning that it uses an intermediary layer (driver manager) between the application and the database to translate the ODBC calls to database-specific calls.
Performance: As JDBC is a native API, it tends to have better performance than ODBC, which relies on an intermediary driver manager for translation. However, ODBC drivers can be optimized for specific databases, which can lead to better performance in some cases.
Ease of use: JDBC is generally considered easier to use than ODBC, as it provides a simple and intuitive API for Java programmers. ODBC can be more complex, as it involves setting up a driver manager and configuring data source names.
Both JDBC and ODBC are widely used APIs for connecting to databases from different programming languages. While JDBC is primarily used for Java programming language and provides a native API, ODBC is used for various programming languages and provides a middleware API that relies on an intermediary driver manager.
Q Explain with an example the all kinds of Integrity Constraints Domain Constraints. How are they enforced in Oracle?
Domain constraints are a type of integrity constraint that restricts the allowable values for a column in a database table to a specific domain or set of values. There are several types of domain constraints, including: NOT NULL: This constraint requires that the column cannot contain null values.
UNIQUE: This constraint requires that the column contains only unique values.
PRIMARY KEY: This constraint combines the NOT NULL and UNIQUE constraints, and also requires that the column contains no null values and serves as the primary key for the table.
CHECK: This constraint allows you to define a condition that the column values must meet. For example, you can define a CHECK constraint that requires that a column contain only positive values.
In Oracle, domain constraints are enforced using triggers, which are pieces of PL/SQL code that are executed automatically in response to certain events, such as the insertion, update, or deletion of a row in a table. The trigger code can check the values of the column being modified and take appropriate action if the values violate the domain constraint. Here is an example of a domain constraint in Oracle: CREATE TABLE employees ( employee_id NUMBER(10) PRIMARY KEY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, email VARCHAR2(100) UNIQUE, salary NUMBER(10,2) CHECK (salary > 0) );
This creates a table named "employees" with several domain constraints. The employee_id column is defined as the primary key for the table and cannot contain null values. The first_name and last_name columns also cannot contain null values. The email column must contain unique values. The salary column is defined with a CHECK constraint that requires it to contain only positive values.
Q How the query can be oplimized before processing. Why "Join" is considered as most expensive operation.
Query optimization is the process of improving the performance of a database query by reducing the amount of time and resources required to execute the query. There are several techniques that can be used to optimize queries, including:
Indexing: Indexes can be created on columns used in the WHERE clause of a query to speed up the search for matching rows.
Query rewriting: This involves modifying the query to simplify it or eliminate unnecessary operations.
Partitioning: Large tables can be partitioned into smaller, more manageable pieces to improve query performance.
Caching: The results of frequently executed queries can be cached in memory to reduce the need to re-execute the query.
The JOIN operation is often considered the most expensive operation in query processing because it involves combining data from multiple tables. This can be a resource-intensive operation, especially if the tables being joined are large. To optimize queries that involve joins, it is important to ensure that the tables being joined are properly indexed and that the query is written in a way that minimizes the amount of data that needs to be processed.
Q Explain in detail XML & its Structure.
XML stands for "eXtensible Markup Language". It is a markup language that is used to describe and store data in a structured format that is both human-readable and machine-readable. XML provides a way to define the structure of documents and data using tags that are similar to those used in HTML. However, unlike HTML, which is designed for presenting information on the web, XML is designed for describing data.
The structure of an XML document consists of a hierarchy of elements that are enclosed in tags. Each element can have attributes that provide additional information about the element. Here is an example of an XML document: <?xml version="1.0" encoding="UTF-8"?> <catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth look at creating applications with XML.</description> </book> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5.95</price> <publish_date>2000-12-16</publish_date> <description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.</description> </book> <!-- More books here --> </catalog>
In this example, the XML document defines a catalog of books. The top-level element is "catalog", which contains a list of "book" elements. Each "book" element has several child elements, such as "author", "title", "genre", "price", "publish_date", and "description". The "book" elements also have an "id" attribute that provides a unique identifier for each book.
Q Differentiate between HTML, DHTML and XML. What are cookies?
HTML, DHTML, and XML are all markup languages, but they have different purposes and characteristics.
HTML (Hypertext Markup Language) is a markup language that is used to create web pages. HTML documents describe the structure and content of a web page, and are used by web browsers to display the page to the user. DHTML (Dynamic HTML) is a combination of HTML, CSS, and JavaScript that allows for dynamic web content and interactive effects. DHTML can be used to create animations, rollover effects, and other interactive elements on a web page. XML (eXtensible Markup Language) is a markup language that is used to describe and store data in a structured format. XML documents are used for exchanging data between different systems and applications, and can be used to represent any type of data. Cookies are small text files that are stored on a user's computer by a web browser. Cookies are used to remember user preferences and settings, and can be used to store information about a user's activity on a website. Cookies can also be used for tracking and advertising purposes. When a user visits a website, the website can set a cookie in the user's browser, and the browser will send the cookie back to the website on subsequent visits. Cookies can be either persistent or session-based, depending on how long they are stored in the user's browser. Persistent cookies can be stored for a long period of time, while session cookies are deleted when the user closes the browser.
Q What do you understand by Object relation database?
An object-relational database (ORD) is a type of database management system (DBMS) that combines the characteristics of both object-oriented programming (OOP) and relational database models. It is designed to handle complex data structures that go beyond the simple tables used in relational databases. In an ORD, data is stored as objects and relationships between the objects are defined using a relational model. This allows for more flexible and powerful data modeling and querying than is possible with a purely relational database.
Q Difference between Object Oriented & Object relational database.
The main difference between an object-oriented database (OODB) and an object-relational database (ORD) is that an OODB is designed to store and manage complex data structures in the form of objects, while an ORD is designed to combine the benefits of both object-oriented and relational databases. Here are some of the key differences:
Data modeling: In an OODB, data is modeled as objects, which have properties and methods. In an ORD, data can be modeled using a combination of object-oriented and relational modeling techniques, allowing for more complex and flexible data structures.
Querying: OODBs use object-oriented query languages, such as OQL, while ORD systems use SQL or extensions of SQL to support object-relational features.
Performance: OODBs are designed for fast object access and manipulation, while ORD systems are optimized for efficient querying and data retrieval.
Integration: OODBs are often used in environments where the application programming language is also object-oriented, while ORD systems can be used in a wide range of programming environments.
Therefore, an OODB is designed specifically for managing complex object-oriented data structures, while an ORD is designed to provide the benefits of both object-oriented and relational databases for handling complex data structures.
Q What are SQL Standards? Also explain standard of Interoperability & Integration.
Q What do you understand by Web Services? Explain SOAP in detail
Web Services:
Web services are software systems designed to support interoperable machine-to-machine interactions over a network. They allow different applications to communicate with each other using standard web protocols, such as HTTP and XML. Web services are widely used in enterprise computing to support the integration of different systems and applications.
SOAP (Simple Object Access Protocol):
SOAP is a messaging protocol used to exchange structured data between different applications over a network. It is an XML-based protocol that defines a standard way to describe the structure of the message and the operations that can be performed on it. SOAP is typically used to implement web services, allowing different applications to communicate with each other using standard web protocols.
The SOAP message structure consists of an envelope, header, and body. The envelope contains the message information, such as the version of SOAP being used and the destination of the message. The header contains any additional information about the message, such as security or routing information. The body contains the actual message data, which can be in any format that can be serialized as XML.
Therefore, SQL standards ensure that SQL databases and applications are consistent across different platforms and vendors. Interoperability and integration standards ensure that SQL systems can work together with other systems and technologies. Web services use SOAP as a messaging protocol to enable communication between different applications over a network.
-x-x-x-x-x-
mojj
ReplyDelete