Notes for BSc CSIT

A search algorithm that makes use of an index is called an index scan and the index structure is called an access path.  An access path specifies the path chosen by a database management system to retrieve the requested tuples from a relation.  The description of an externally described file contains the access path that describes how records are to be retrieved from the file. Records can be retrieved based on an arrival sequence (non-keyed) access path or on a keyed-sequence access path. OR,  Access Path refers to the path chosen by the system to retrieve data after a structured query language (SQL) request is executed. A query may request at least one variable to be filled up with one value or more. The formula to calculate the cost of searching algorithm selections using indices A1 (primary index, equality on key): For an equality comparison on a key attribute with a primary index, we can use the index to retrieve a single record that satisfies the corresponding equality condition. 

 The cost of the query is the time taken by the query to hit the database and return the result. It involves query processing time i.e., the time taken to parse and translate the query, optimize it, evaluate, execute and return the result to the user is called the cost of the query. Executing the optimized query involves hitting the primary and secondary memory based on the file organization method. Depending on file organization and the indexes used, the time taken to retrieve the data may vary. Query cost considers the number of different resources that are listed below: The number of disk accesses / the number of disk block transfers / the size of the table Time is taken by the CPU for executing the query For simplicity, we just use the number of block transfers from disk and the number of seeks as the cost measures of a query-evaluation plan. Suppose a query needs to seek S times to fetch a record and there are b blocks that need to be returned to the user. The disk I/O cost is cal

 Query processing and optimization are a fundamental, if not critical, part of any DBMS. To be utilized effectively, the results of queries must be available in the timeframe needed by the submitting user—be it a person, robotic assembly machine, or even another distinct and separate DBMS.  OR, Maintaining large databases with high performance is called database query optimization. A distributed database is a group of autonomous cooperating centralized databases, in that query processing requires transferring data from one system to another through a communication network. In the query optimization process, the cost is always associated with each and every query execution plan (QEP).

 QUERY OPTIMIZATION The function of a query optimization engine is to find an evaluation plan that reduces the overall execution cost of a query. We have seen in the previous sections that the costs for performing particular operations such as select and join can vary quite dramatically. Query optimization strategies for lowering the execution time of queries include  cost-based optimization, heuristic-based optimization, and semantic-based optimization. Cost-based Optimization This process of selecting a lower-cost mechanism is known as cost-based optimization. This is based on the cost of the query. The query can use different paths based on indexes, constraints, sorting methods etc. This method mainly uses the statistics like record size, number of records, number of records per block, number of blocks, table size, whether the whole table fits in a block, organization of tables, uniqueness of column values, size of columns, etc. Some of the features of cost-based optimization are as

Query Processing  Energy efficiency is an important feature in designing and executing databases. The aims of query processing are to transform a query written in a high-level language, typically SQL, into a correct and efficient execution strategy expressed in a low-level language (implementing the relational algebra), and to execute the strategy to retrieve the required data. Thus, Query Processing is the activities involved in parsing, validating, optimizing, and executing a query. The steps involved in processing a query processing is shown in the figure and they are: 1. Parsing and translation 2. Optimization 3. Evaluation 1. Parsing and translation The main work of a query processor is to convert a query string into query objects i.e., converting the query submitted by the user, into a form understood by the query processing engine. It converts the search string into definite instructions. The query parser must analyze the query language i.e., recognizing and interpreting operato

Query Processing  Query Processing is a translation of high-level queries into low-level expressions. It is a step-wise process that can be used at the physical level of the file system, query optimization, and actual execution of the query to get the result. It requires the basic concepts of relational algebra and file structure. It refers to the range of activities that are involved in extracting data from the database. It includes the translation of queries in high-level database languages into expressions that can be implemented at the physical level of the file system. Query Processing is the process of converting high-level queries to low-level expressions to extract the data from physical level file systems like databases.  OR, Query Processing would mean the entire process or activity which involves query translation into low-level instructions, query optimization to save resources, cost estimation or evaluation of query, and extraction of data from the database It involves ma

 Several Implementation Challenges Due to Enhanced Functionality of ORDBMS :- 1.  Storage & Access Method  2. Query Processing  3. Query Optimization  1.  Storage & Access Method   efficiently store ADT objects and structure objects and provide efficient indexed access to both  Large ADTs, like BLOBs(Binary Large Object), require special storage, typically in a different location on disk from the tuples that contain them  Disk-based pointers are maintained from the tuples to the objects they contain. A complication arises with array types. Arrays are broken into contiguous chunks, which are then stored in some order on a disk. 2. Query Processing   ADTs and structured types call for new functionality in processing queries   To register an aggregation function, a user must implement three methods, which we call initialize, iterate and terminate.   ADTs give users the power to add code to the DBMS; this power can be abused.  A buggy or malicious ADT method can bring down the data

There are two methods that the OODBMS uses to access persistent objects, virtual memory address pointers, and hash tables. A persistent object will always be ready to be invoked, and its state can and will be preserved and survive any kind of system failure. Remember, persistent objects are stored on disk and transient objects exist in RAM memory.  In an OODBMS, an object may traverse between states.  Persistent means that the object has been saved to the database whereas transient means that it hasn't been saved yet. So for example when you get an entity from a repository, that entity is persistent. When you create a new entity, it is transient until persisted.

 OBJECT IDENTITY (OID) One goal of an OIDis to maintain a direct correspondence between real-world and database objects so that objects do not lose their integrity and identity and can easily be identified and operated upon. Hence, a unique identifier is assigned to each independent object stored in the database. This unique identity is typically implemented via a unique, system-generated object identifier (OID). The value of an OID may not be visible to the external user but is used internally by the system to identify each object uniquely and to create and manage inter-object references. The OID can be assigned to program variables of the appropriate type when needed. The main property required of an OID is that it be immutable; that is, the OID value of a particular object should not change. This preserves the identity of the real-world object being represented. Hence, an ODMS must have some mechanism for generating OIDs and preserving the immutability property. It is also desirable

 Advantages of OODBMSS OODBMSS can provide appropriate solutions for many types of advanced database applications. However, there are also disadvantages. Enriched modeling capabilities: The object-oriented data model allows the 'real world' to be modeled more closely. The object, which encapsulates both state and behavior, is a more natural and realistic representation of real-world objects. An object can store all the relationships it has with other objects, including many-to-many relationships, and objects can be formed into complex objects that the traditional data models cannot cope with easily. Extensibility:  OODBMSs allow new data types to be built from existing types. The ability to factor out common properties of several classes and form them into a super-class that can be shared with sub-classes can greatly reduce redundancy within the system and is regarded as one of the main advantages of object orientation. Further, the reusability of classes promotes faster at dev

  State of an object A condition or situation during the life of an object during which it satisfies some condition performs some activity, or waits for some event is known as the state of an object. The difference between Persistent objects and transient objects are:-  Persistent Objects are those that are stored in the database [Objects created using abstract data types arrays, nested tables, etc.]. These can be used both with SQL commands and also in PL/SQL blocks. These reside in the data dictionary. Persistent objects are available to the user until they are deleted explicitly. They can be implemented as tables, columns, or attributes. The persistent object is one that outlives the process in which it is created. Remark that this does not mean that objects are stored in a database and that any recovery is guaranteed. It means better, that the lifetime of such objects persists across server process activation and deactivation cycles.  A transient objec t exists only within the sco

OBJECT STRUCTURE The structure of an object refers to the properties that an object is made up of. These properties of an object are referred to as an attribute. Thus, an object is a real-world entity with certain attributes that makes up the object structure. Also, an object encapsulates the data code into a single unit which in turn provides data abstraction by hiding the implementation details from the user. Any object has two properties i.e. state & behavior hence, it's similar to the program variable but the object has a complex structure. There are two types of objects: Transient object Persistent object Transient object These objects exist during the execution but destroy once the program terminates. Persistent object These objects persist & store in the database even if the program terminates. Whenever any object is created DBMS assigns a unique identifier called object identity & it's abbreviated as OID. 

 The relational model with object database enhancements is sometimes referred to as the object-relational model. The following are some of the object database features that have been included in SQL: Some type of constructors has been added to specify complex objects. These include the row type, which corresponds to the tuple (or struct) constructor. An array type for specifying collections is also provided. Other collection type constructors, such as set, list, and bag constructors, were not part of the original SQL/Object specifications in SQL: 99 but were later included in the standard in SQL: 2008. A mechanism for specifying object identity through the use of reference type is included. Encapsulation of operations is provided through the mechanism of user-defined types (UDTs) that may include operations as part of their declaration. These are somewhat similar to the concept of abstract data types that were developed in programming languages. In addition, the concept of user-defined

 Complex objects  Complex objects are built from simpler ones by applying constructors to them. The simplest objects are objects such as integers, characters, byte strings of any length, booleans, and floats (one might add other atomic types). There are various complex object constructors: tuples, sets, bags, lists, and arrays are examples.  The difference between structured and unstructured complex are as follows:- Unstructured complex object These are provided by a DBMS and permit the storage and retrieval of large objects that are needed by the database application. Typical examples of such objects are bitmap images and long text strings (such as documents); they are also known as binary large objects, or BLOBs for short. This has been the standard way by which Relational DBMSs have dealt with supporting complex objects, leaving the operations on those objects outside the RDBMS. Structured complex object This differs from an unstructured complex object in that the object's struc

 The object-relational  database is an improved version of the object-oriented database. It provides a solution to the issues users face in object-oriented databases; some of these issues include the cost of computing resources, possibility of design errors, and data inconsistency. However, it is also important to note that the object-relational database is based on both the relational model and the object-oriented database model. Furthermore, these databases support objects and inheritance and provide a better interface for many object-oriented languages. Users can also use data model extensions with custom data types and methods. Moreover, companies such as Microsoft, Oracle, and Sybase have object-relational versions of their products. These databases are more applicable for applications that consist of a large number of short-lived transactions on data items with complicated structures.

Object-oriented database (OODB)   An object-oriented database (OODB) stores data in objects. An object is an item that contains data, as well as the actions that read or process the data. A Student object, for example, might contain data about a student such as Student ID, First Name, Last Name, Address, and so on. It also could contain instructions about how to print a student transcript or the formula required to calculate a student’s grade point average. Object-oriented databases have several advantages compared with relational databases: they can store more types of data, access this data faster, and allow programmers to reuse objects. An object-oriented database stores unstructured data more efficiently than a relational database. Unstructured data includes photos, video clips, audio clips, and documents. When users query an object-oriented database, the results often are displayed more quickly than the same query of a relational database. If an object already exists, programmers

  When compared to a relational database management system, an object-oriented database stores complex data and relationships between data directly, without mapping to relational rows and columns whereas a relational database stores information in tables with rows and columns. OR, The difference Between Relational databases and Object Oriented databases is that a relational database is a database that stores data in tables that consist of rows and columns. Each row has a primary key and each column has a unique name. A file processing environment uses the terms file, record, and field to represent data. While an object-oriented database (OODB) stores data in objects. An object is an item that contains data, as well as the actions that read or process the data. A Student object, for example, might contain data about a student such as Student ID, First Name, Last Name, Address, and so on. OR, The main difference between Object Oriented Database and Object Relational Database is that Obje

 Relational Database A relational database is a database that stores data in tables that consist of rows and columns. Each row has a primary key and each column has a unique name. A file processing environment uses the terms file, record, and field to represent data. A relational database uses terms different from a file processing system. A developer of a relational database refers to a file as a relation, a record as a tuple, and a field as an attribute. A user of a relational database, by contrast, refers to a file as a table, a record as a row, and a field as a column. In addition to storing data, a relational database also stores data relationships. A relationship is a link within the data. In a relational database, you can set up a relationship between tables at any time. The tables must have a common column (field). In a relational database, the only data redundancy (duplication) exists in the common columns (fields). The database uses these common columns for relationships. Man

 The ODMG· Object Model The ODMG object model is the data model upon which the object definition language (ODL) and object query language (OQL) are based. It is meant to provide a standard data model for object databases, just as SQL describes a standard data model for relational databases. It also provides a standard terminology in a field where the same terms were sometimes used to describe different concepts. 

 Objects and Literals Objects and literals are the basic building blocks of the object model. The main difference between the two is that an object has both an object identifier and a state (or current value), whereas a literal has a value (state) but no object identifier. In either case, the value can have a complex structure. The object state can change over time by modifying the object value. A literal is basically a constant value, possibly having a complex structure, but it does not change. An  object has five aspects: identifier, name, lifetime, structure, creation.  1. The object identifier is a unique system-wide identifier (or Object_id). Every object must have an object identifier. 2. Some objects may optionally be given a unique name within a particular ODMS—this name can be used to locate the object, and the system should return the object given that name. Obviously, not all individual objects will have unique names. Typically, a few objects, mainly those that hold collecti