Object Query Language (OQL)

Object Query Language (OQL) is a query language standard for object-oriented databases modelled after SQL. OQL was developed by the Object Data Management Group (ODMG). Because of its overall complexity no vendor has ever fully implemented the complete OQL. OQL has influenced the design of some of the newer query languages like JDOQL and EJB QL, but they can't be considered as different flavours of OQL, and should be treated separately.

The key Differences Between OQL and SQL :
- OQL supports object referencing within tables. Objects can be nested within objects.
- Not all SQL keywords are supported within OQL. Keywords that are not relevant to Netcool/Precision IP have been removed from the syntax.
- OQL can perform mathematical computations within OQL statements.

General Rules of OQL :
- All complete statements must be terminated by a semi-colon.
- A list of entries in OQL is usually separated by commas but not terminated by a comma.
- Strings of text are enclosed by matching quotation marks.

OQL was designed to be object-oriented. Queries are specified using objects and their attributes (data-members). Similarly, queries return sets of objects. The complex relationships in an object model can be easily navigated, using the same class-member paradigm, used by object-oriented programming languages. This can often lead to increased performance over SQL, where resource-consuming join processes are necessary to capture relationships. Another big advantage is, that table names and column names are not necessary in the query strings, because queries are formulated using class names and attribute names and no mapping knowledge is necessary in the application.
OQL may be used as an embedded language or as a standalone query language. Both of these are supported by OpenAccess. As an embedded language, OQL queries can be used directly in your application programs. Programs can embed OQL queries, and receive results in the native data types of the programming language being used. OQL statements are simply text strings, which means that the standard string representation of your programming language is used to express the query.

Simple query :
The following example illustrates how one might retrieve the CPU-speed of all PCs with more than 64MB of RAM from a fictional PC database:
SELECT pc.cpuspeed
FROM PCs pc
WHERE pc.ram > 64

Query with grouping and aggregation :
The following example illustrates how one might retrieve the average amount of RAM on a PC, grouped by manufacturer:
SELECT manufacturer, AVG(SELECT part.pc.ram FROM partition part)
FROM PCs pc
GROUP BY manufacturer: pc.manufacturer
Note the use of the keyword partition, as opposed to aggregation in traditional SQL.

Learn software concepts - Introduction to Data Binding

Data binding is the process that establishes a connection between the application UI and business logic. If the binding has the correct settings and the data provides the proper notifications, then, when the data changes its value, the elements that are bound to the data reflect changes automatically. Data binding can also mean that if an outer representation of the data in an element changes, then the underlying data can be automatically updated to reflect the change. A typical use of data binding is to place server or local configuration data into forms or other UI controls.

Basic Data Binding Concepts :
Data binding is based on a component architecture that consists of four major pieces : the data source object (DSO), data consumers, the binding agent, and the table repetition agent. Data source objects provide the data to a page, data-consuming HTML elements display the data, and the agents ensure that both the provider and the consumer are synchronized.

Direction of the Data Flow :
The data flow of a binding can go from the binding target to the binding source and/or from the binding source to the binding target.
- One Way binding causes changes to the source property to automatically update the target property, but changes to the target property are not propagated back to the source property. This type of binding is appropriate if the control being bound is implicitly read-only.
- Two Way binding causes changes to either the source property or the target property to automatically update the other. This type of binding is appropriate for editable forms or other fully-interactive UI scenarios. Most properties default to One Way binding, but some dependency properties default to Two Way binding.
- OneWayToSource is the reverse of OneWay binding; it updates the source property when the target property changes. One example scenario is if you only need to re-evaluate the source value from the UI.

Data Source Objects
To bind data to the elements of an HTML page in Windows Internet Explorer, a DSO must be present on that page. DSOs implement an open specification that leaves it up to the DSO developer to determine the following:
- How the data is transmitted to the page. A DSO can use any transport protocol it chooses. This might be a standard Internet protocol, such as HTTP or simple file I/O. A DSO also determines whether the transmission occurs synchronously or asynchronously. Asynchronous transmission is preferred, because it provides the most immediate interactivity to the user.
- How the data set is specified. A DSO might require an Open Database Connectivity (ODBC) connection string and an Structured Query Language (SQL) statement, or it might accept a simple URL.
- How the data is manipulated through scripts. Since the DSO maintains the data on the client, it also manages how the data is sorted and filtered.
- Whether updates are allowed.

Data Consumers
Data consumers are elements on the HTML page that are capable of rendering the data supplied by a DSO. Elements include many of those intrinsic to HTML, as well as custom objects implemented as Java applets or Microsoft ActiveX Controls.
A DSO typically exposes this functionality through an object model that is accessible to scripts.

Binding Agents
The binding and repetition agents are implemented by MSHTML.dll, the HTML viewer for Internet Explorer, and they work completely behind the scenes. When a page is first loaded, the binding agent finds the DSOs and the data consumers among those elements on the page. Once the binding agent recognizes all DSOs and data consumers, it maintains the synchronization of the data that flows between them.

Introduction to Database Concurrency

DATABASE CONCURRENCY: - Database concurrency is the particular situation when a single database is being accessed by multiple programs. Databases, by design in most cases are shared resources, but in this case, they are shared across multiple applications.
Database concurrency controls ensure that transactions occur in an ordered fashion. The main job of these controls is to protect transactions issued by different users/applications from the effects of each other. They must preserve the four characteristics of database transactions: atomicity, isolation, consistency and durability. Concurrency control is one of the main issues in the study of real time database systems. In addition to satisfying consistency requirements as in traditional database systems, a real time transaction processing system must also satisfy timing constraints.

Conflicts between transactions can be detected in two ways.
Pessimistic method detects conflicts before making access to the data object. When a transaction requests access to some data item, the concurrency control manager will examine this request and will determine whether to grant the request or not.
Optimistic schemes are designed to get rid of the locking overhead. They are optimistic in the sense that they take into account the explicit assumption that conflicts among transactions are rare events. The task of concurrency control is deferred until the end of transaction when some checking for potential conflicts has to take place and will be resolved accordingly, taking into consideration the amount of progress that has been done and the nature of conflict with transactions.
When concurrency control detects a conflict among some concurrent transactions accessing the same object, a conflict resolution mechanism needs to be put on. Concurrency control manager decides which transaction (victim) to penalize (the lock holder or the requester) and chooses an appropriate action and suitable timing. Two possible actions are most used: Blocking (wait) and abort (restart). In pessimistic concurrency control either blocking or abort can be used to resolve the conflict. However, in optimistic concurrency control only aborting is appropriate since conflict has been detected after the transaction has accessed the data object and performed some computation.

OPTIMISTIC CONCURRENCY CONTROL : The basic idea of an optimistic concurrency control mechanism is that the execution of a transaction consists of three phases: read, validation and write phases. For all optimistic concurrency control (OCC) schemes a conflict is detected after the data object has been accessed. In the OCC, conflict detection and resolution are both done at the certification time when a transaction completes its execution; it requests the concurrency control manager to validate all its accessed data objects. If it has not yet been marked for abort, it enters the commit phase where it writes all its updates to the database.

Learn software concepts: Introduction to Database Encryption

Encryption can provide strong security for data, but is that enough ? Data in a database can be accessed by many systems, but developing a database encryption strategy must take many factors into consideration. Where should the encryption be performed, for example — in the database, or in the application where the data originates? Who should have access to the encryption keys? How much data must be encrypted to provide security? What’s an acceptable trade-off between data security and application performance?
Data encryption is a process of converting stored or transmitted data to a coded form in order to prevent it from being read by unauthorized person. It is an application of a specific algorithm to alter the appearance of data, making it incomprehensible to those who are not authorized to see the information.
There are 2 types of encryption algorithm: -
- Secret key or Symmetric key algorithm: -In this encryption algorithm, a single secret or private key is shared between the sender and receiver. The sender encrypts this using this key and receiver decrypts it using the same key. It is highly assumed that no one else knows the key.
- Public key or Asymmetric key algorithm: - In this algorithm, every sender and receiver has a pair of keys. One is made public to the network and called public key and the other is kept private to that node called private key. The pair is such made that if the data is encrypted with one of the keys in the pair, it can only be decrypted with other key in the pair. When a sender has to send, it encrypts the data with receiver’s public key & the receiver decrypts it with its private key.

Advice on how to overcome some of the challenges in database encryption:
- Regulatory drivers : Advanced security through database encryption is required across many different sectors, and increasingly to comply with regulatory mandates.
One approach that can help companies address the encryption challenges associated with regulation is the defense-in-depth principle which advocates many layers to strong security – ranging from physical security and access controls to rights assignment and network security, including firewalls and, crucially, encryption of both data at rest and in transit.
- Overcoming key management issues
It is important that database encryption be accompanied by key management; however, statistics show that this is also the main barrier to database encryption. It is well-recognized that key use should be restricted and that key backup is extremely important. An additional best practice rule of encryption is that the encrypted key should never be stored alongside the data it was used to encrypt. Placing encryption keys within the HSM enforces this policy.
- Separation of duties and dual control
Many organizations pay close attention to separation of duties and dual control, which is required to pass audits to show that there are internal controls protecting against rogue administrators or unauthorized employees and is often required by the various regulatory requirements discussed above.

Learn software concepts: Database Locking - Control concurrent access of the database

Locking is a procedure used to control concurrent access of the data. A lock may deny access to other transaction in order to prevent incorrect results (if multiple people are updating the same data).

Locks can be of two types: -
• Exclusive lock or Write lock
• Shared lock or Read lock

- Exclusive lock: Provides exclusive use of a data item to one transaction. Transaction has to be made exclusive to modify the value of data in a table. If the transaction has obtained an exclusive lock, then no other transaction can access the data item until the lock is released, including being able to read the data.

- Shared locks: Provides a read permission to the transaction. Any number of transactions can make shared lock & read the data item. This helps when the database is being read for multiple transactions, and putting a shared lock can be bad for business logic.

Basic rules for locking: -
• If a transaction has a read lock on the data item, it can read the item but cannot update it.
• If a transaction has a read lock, other transaction can obtain a read lock on the data item, but no write lock.
• If a transaction has a write lock, it can both read and update the data item.
• If a transaction has a write lock, then others can’t obtain either a read lock or a write lock on the data item.

TWO PHASE LOCKING (2PL):-One way to handle the concurrency control is 2PL mechanism.
Every transaction is divided into two phases: -
• A growing phase
• A shrinking phase or Contracting phase
In the growing phase, the transaction acquires all locks needed but can’t release any locks. The number of locks increases from zero to maximum for a transaction.
In the contracting phase, the number of locks held decreases from maximum to zero.
The transaction can acquire the locks, proceed with the execution & during the course of execution acquire additional locks as needed. But it never releases any lock until it has reached a stage where no new locks are required anymore.

2PL are of the following types: -
• Basic two-phase locking
• Conservative two-phase locking
• Strict two-phase locking
• Rigorous two-phase locking.

Lock Time-out :
Locks are held for the length of time needed to protect the resource at the level requested. If a connection attempts to acquire a lock that conflicts with a lock held by another connection, the connection attempting to acquire the lock is blocked until:
· The conflicting lock is freed and the connection acquires the lock it requested.
· The time-out interval for the connection expires.

Database Integrity

Database Integrity is the preservation of data correctly & implies the process of keeping the dbase away from accidental deletion or alteration.
There are following types of integrity constraints:-
• Entity integrity constraints
• Referential integrity constraints
• Domain integrity constraints

DATABASE SECURITY: - Database security is a measurement of confidence that the integrity of a system and its data will be preserved.
Database security is assigned to address the following issues:-
• Privacy of data elements
• Preserving policies of organization
• System related security level
• Maintaining integrity of the database

Data integrity can be compromised in a number of ways:
- Human errors when data is entered.
- Errors that occur when data is transmitted from one computer to another.
- Software bugs or viruses.
- Hardware malfunctions, such as disk crashes.
- Natural disasters, such as fires and floods.

There are many ways to minimize these threats to data integrity. These include:
- Backing up data regularly.
- Controlling access to data via security mechanisms.
- Designing user interfaces that prevent the input of invalid data.
- Using error detection and correction software when transmitting data.

* Declarative Ease
Define integrity constraints using SQL statements. For these reasons, declarative integrity constraints are preferable to application code and database triggers. The declarative approach is also better than using stored procedures, because the stored procedure solution to data integrity controls data access, but integrity constraints do not eliminate the flexibility of ad hoc data access.
* Centralized Rules
Integrity constraints are defined for tables (not an application) and are stored in the data dictionary. Any data entered by any application must adhere to the same integrity constraints associated with the table.
* Maximum Application Development Productivity
If a business rule enforced by an integrity constraint changes, then the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint.
* Superior Performance
The semantics of integrity constraint declarations are clearly defined, and performance optimizations are implemented for each specific declarative rule.
* Flexibility for Data Loads and Identification of Integrity Violations
You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking.
* The Performance Cost of Integrity Constraints
The advantages of enforcing data integrity rules come with some loss in performance.

Overview of Database Security

Database security is the set of systems, processes, and procedures that protect a database from unintended activity. Unintended activity can be categorized as authenticated misuse, malicious attacks or inadvertent mistakes made by authorized individuals or processes. Database security is also a specialty within the broader discipline of computer security. The database is the entity where all the data is stored, so protecting it from unauthorized access and change is extremely critical.
Traditionally databases have been protected from external connections by firewalls or routers on the network perimeter with the database environment existing on the internal network opposed to being located within a demilitarized zone.
Database security can begin with the process of creation and publishing of appropriate security standards for the database environment. The standards may include specific controls for the various relevant database platforms; a set of best practices that cross over the platforms; and linkages of the standards to higher level polices and governmental regulations.
One of the easiest steps to take is regarding passwords. Default or weak passwords are still often used by enterprises to protect an online asset as important as a database, but it's a problem that's easy to fix. The remedy is enforcing a strong password policy; that is, passwords must be changed regularly and be at least 10 digits long and contain both alphanumeric characters and symbols.
SQL Injection attacks pose tremendous risks to web applications that depend upon a database back-end to generate dynamic content. In this type of attack, hackers manipulate a web application in an attempt to inject their own SQL commands into those issued by the database. To prevent this type of attack, it is essential to ensure that all user-supplied data is validated before letting it anywhere near your scripts, data access routines and SQL queries, and preferably use parametrized queries. Another reason to validate and clean data received from users is to prevent cross-site scripting (XSS) attacks, which can be used to compromise a database connected to a Web server.
A database security program should include the regular review of permissions granted to individually owned accounts and accounts used by automated processes. The accounts used by automated processes should have appropriate controls around password storage such as sufficient encryption and access controls to reduce the risk of compromise.
The software used for the database, for the middle layers and for all other layers should be updated regularly with patches, updates and fixes. Falling behind in this task is pretty painful if you end up exposing holes in the software to attackers (and attackers know that a number of companies do not upgrade their systems on an immediate basis).

Learn software concepts: Structured Query Language (SQL)

The structured query language (SQL) is the language used to query and manipulate information within a SQL Server database. SQL is actually an ISO and ANSI standardised language. However, a lot of RDBMS software use their own proprietary extensions within their own Transact-SQL (T-SQL) variant of SQL.

The basic building block of the structured query language is the SQL statement. Using statements, information in a database can be manipulated and queried.
* CREATE - a data structure.
* SELECT - read one or more rows from a table.
* INSERT - one or more rows into a table.
* DELETE - one or more rows from a table.
* UPDATE - change the column values in a row.
* DROP - a data structure.

Language Structure :
SQL is a keyword based language. Each statement begins with a unique keyword. SQL statements consist of clauses which begin with a keyword. SQL syntax is not case sensitive.
The other lexical elements of SQL statements are:
* names -- names of database elements: tables, columns, views, users, schemas; names must begin with a letter (a - z) and may contain digits (0 - 9) and underscore (_)
* literals -- quoted strings, numeric values, date time values.
* delimiters -- + - , ( ) = < > <= >= <> . * / || ? ;
Basic database objects (tables, views) can optionally be qualified by schema name. A dot -- ".", separates qualifiers: schema-name . table-name
Column names can be qualified by table name with optional schema qualification.

Syntax of Simple SELECT : SELECT column FROM tablename
- Using "Where"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000;
- Compound Conditions
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY < 40000 OR BENEFITS < 10000;
- Using "IN"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff');
- Using "Between"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000;
- Using "LIKE"
SELECT EMPLOYEEIDNO
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'L%';

Learn software concepts: Introduction to Relational Databases

Relational databases are probably the most common type of database used for general-purpose tasks. In a relational database, information is grouped according to its type, generally in tables (see below). For example, in a database designed to hold fleet information you may include a table of employees and a table of vehicles.
- In addition to separating information according to its data structure, a relational database allows relationships to be created. A relationship defines a possible link between data types; the actual linkage of data is dependent upon the information held.
- Relational databases use the concept of normalization. Normalization is a design technique that minimizes the duplication of information. It also reduces the risk of errors. By using relationships, the duplication required can be lessened or eliminated completely.
A Relational model is the basis for any relational database management system (RDBMS). A relational model has mainly three components:
- A collection of objects or relations.
- Operators that act on the objects or relations.
- Data integrity methods.

Elements of a Relational Database Schema :
There are several key elements to a relational database. Each of these forms a part of the database's schema. The schema is the logical data model that determines the information that may be stored in the database and how it is to be arranged. To design a database we need three things:
- Table : A table is one of the most important ingredient to design the database. It is also known as a relation, and is a two dimensional structure used to hold related information. A database consists of one or more tables.
- Rows : A table contains rows. Rows are collection of instance of one thing.
- Columns : A table contains the columns. Columns contains all the information of a single type. Each column in a table is a category of information referred to as a field.
- Indexes : One of the greatest benefits of holding information in a database is the ability to quickly retrieve it. When querying a database, it is possible to apply criteria to ask for a specific set of rows.
- Keys : A primary key is a single column, or group of several columns (compound key), that can be used to uniquely identify rows in a table. Each table in a database may have a single primary key. Once defined, no two rows in the table may contain matching data in the primary key columns. Foreign keys are used when defining relationships between tables. A foreign key is a single column, or group of columns, in a table that reference the primary key in another table. This creates a link between the two tables.
- Constraints : Constraints are rules that are applied to the information in a database. These are usually used to enforce business rules upon the tabular data.
- Views : Views provide the useful concept of virtual tables. A view gathers specific information from one or more sources and presents it in the format of a single table. The information may be filtered within the view to remove unnecessary information.
- Stored Procedures : A stored procedure is a predefined set of statements that can be executed when required. Stored procedures provide the main means of creating programs within SQL Server databases.

Domain and Integrity Constraints :
* Domain Constraints
o limit the range of domain values of an attribute
o specify uniqueness and `nullness' of an attribute
o specify a default value for an attribute when no value is provided.
* Entity Integrity
o every tuple is uniquely identified by a unique non-null attribute, the primary key.
* Referential Integrity
o rows in different tables are correctly related by valid key values (`foreign' keys refer to primary keys).

Learn software terms: Temporal Database Concepts

A temporal database is a database with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language. More specifically the temporal aspects usually include valid-time and transaction-time. These attributes go together to form bitemporal data.
* Valid time denotes the time period during which a fact is true with respect to the real world.
* Transaction time is the time period during which a fact is stored in the database.
* Bitemporal data combines both Valid and Transaction Time.

- Temporal DBMS manages time-referenced data, and times are associated with database entities.
- Modeled reality.
- Database entities.
- Fact: any logical statement than can meaningfully be assigned a truth value, i.e., that is either true or false.
- Valid Time (vt).
- Valid time is the collected times when the fact is true.
- Possibly spanning the past, present & future.
- Every fact has a valid time.
- Transaction Time (tt).
- The time that a fact is current in the database.
- Maybe associated with any database entity, not only with facts.
- TT of an entity has a duration: from insertion to deletion.
- Deletion is pure logical operation.
- Time domain may be discrete or continuous.
- Typically assume that time domain is finite and discrete in database.
- Assume that time is totally ordered.
- Uniqueness of “NOW”.
- The current time is ever-increasing.
- All activities is happed at the current time.
- Current time separates the past from the future.
- “NOW” <> “HERE”.
- Time cannot be reused!
- A challenge to temporal database management.

Most applications of database technology are temporal in nature:
- Financial applications : portfolio management, accounting & banking.
- Record-keeping applications : personnel, medical record and inventory management.
- Scheduling applications : airline, car, hotel reservations and project management.
- Scientific applications : weather monitoring.

Learn Software Terms: Database Recovery Techniques - Overview

The main goal of recovery is to ensure the atomicity property of a transaction. If a transaction fails before completing its execution, the recovery mechanism has to make sure that the transaction has no lasting effects on the database. The different approaches to recovery :
- Deferred Update : These techniques postpone any actual updating of the database on disk until a transaction reaches its commit point. The transaction force writes the log to disk before recording the updates in the database. This approach, when used with certain concurrency control methods, is designed never to require transaction rollback, and recovery simply consists of redoing the operations of transactions committed after the last checkpoint from the log.
Disadvantage : Too much buffer space may be needed, since updates are kept in the buffers and are not applied to disk until a transaction commits.
Deferred update can lead to a recovery algorithm known as NO-UNDO/REDO. Immediate update techniques may apply changes to the database on disk before the transaction reaches a successful conclusion. Any changes applied to the database must be first recorded in the log and force-written to disk so that these operations can be undone if necessary.
- Another algorithm, known as UNDO/NO-REDO, can also be developed for immediate update if all transaction actions are recorded in the database before commit.
- There is another technique, called shadow paging technique, which keeps track of old database pages by using a shadow directory. This technique, which is classified as NO UNDO/NO-REDO, does not require a log in single-user systems but still needs the log for multiuser systems.
- ARIES, a specific recovery scheme is used in some of IBM's relational database products.
- Two-phase commit protocol is used for recovery from failures involving multi-database transactions.
- Recovery from catastrophic failures is typically done by backing up the database and the log to tape. The log can then be backed up more frequently than the database, and the backup log can be used to redo operations starting from the last database backup.

Learn software terms: How is shadow paging performed ?

Shadow paging is an alternative to log-based recovery techniques, which has both advantages and disadvantages. It may require fewer disk accesses, but it is hard to extend paging to allow multiple concurrent transactions. The paging is very similar to paging schemes used by the operating system for memory management.

How Shadow Paging is performed ?
- To commit a transaction :
* Flush all modified pages in main memory to disk.
* Output current page table to disk.
* Make the current page table the new shadow page table, as follows:
1. Keep a pointer to the shadow page table at a fixed (known) location on disk.
2. To make the current page table the new shadow page table, simply update the pointer to point to current page table on disk.
- Once pointer to shadow page table has been written, transaction is committed.
- No recovery is needed after a crash: New transactions can start right away, using the shadow page table.
- Pages not pointed to from current/shadow page table should be freed (garbage collected).

Advantages of shadow-paging technique:
- No overhead of writing log records.
- Recovery is trivial.

Disadvantages of shadow-page technique:
- Commit overhead : The commit of a single transaction using shadow paging requires multiple blocks to be output -- the current page table, the actual data and the disk address of the current page table. Log-based schemes need to output only the log records.
- Data fragmentation : Shadow paging causes database pages to change locations (therefore, no longer contiguous.
- Garbage collection : Each time that a transaction commits, the database pages containing the old version of data changed by the transactions must become inaccessible. Such pages are considered to be garbage since they are not part of the free space and do not contain any usable information. Periodically it is necessary to find all of the garbage pages and add them to the list of free pages. This process is called garbage collection and imposes additional overhead and complexity on the system.

Learn software terms: Overview of Shadow Paging

A computer system, like any other mechanical or electrical system is subject to failure. There are a variety of causes, including disk crash, power failure, software errors, a fire in the machine room, or even sabotage. Whatever the cause, information may be lost. The database must take actions in advance to ensure that the atomicity and durability properties of transactions are preserved. An integral part of a database system is a recovery scheme that is responsible for the restoration of the database to a consistent stage that existed prior to the occurrence of the failure.

Shadow paging is a technique used to achieve atomic and durable transactions, and provides the ability to manipulate pages in a database. During a transaction, the pages affected by the transaction are copied from the database file into a workspace such as volatile memory, and modified in that workspace. When a transaction is committed, all of the pages that were modified by the transaction are written from the workspace to unused pages in the database file. During execution of the transaction, the state of the database exposed to the user is that is which the database existed prior to the transaction, since the database file still contains the original versions of the modified pages, as they existed before being copied into the workspace if a user accesses the database before the transaction is complete, or upon recovery of failure, it will appear as though the transaction has not occurred.

- Shadow paging is an alternative to log-based recovery; this scheme is useful if transactions execute serially.
- Basic Idea: Maintain two page tables during the lifetime of a transaction – the current page table, and the shadow page table.
- Store the shadow page table in nonvolatile storage, such that state of the database prior to transaction execution may be recovered.
* Shadow page table is never modified during execution.
- Initially, both the page tables are identical. Only current page table is used for data item accesses during execution of the transaction.
- Whenever any page is about to be written for the first time
* A copy of this page is made onto an unused page.
* The current page table is then made to point to the copy.
* The update is performed on the copy.

Designing of Multimedia Databases

Many inherent characteristics of multimedia data have direct and indirect impacts on the design of multimedia databases. These include : the huge size of MMDBs, temporal nature, richness of content, complexity of representation and subjective interpretation. The major challenges in designing multimedia databases arise from several requirements they need to satisfy such as the following:
- Manage different types of input, output, and storage devices.
- Handle a variety of data compression and storage formats.
- Support different computing platforms and operating systems.
- Integrate different data models.
- Offer a variety of user-friendly query systems suited to different kinds of media.
- Handle different kinds of indices.
- Develop measures of data similarity that correspond well with perceptual similarity.
- Provide transparent view of geographically distributed data.
- Adhere to real-time constraints for the transmission of media data.
- Synchronize different media types while presenting to user.

DIFFICULTIES INVOLVED WITH MULTIMEDIA DATABASES :
The difficulty of making these different types of multimedia databases readily accessible to humans is:
- The tremendous amount of bandwidth they consume.
- Creating Globally-accepted data-handling platforms, such as Joomla, and the special considerations that these new multimedia database structures require.
- Creating a Globally-accepted operating system, including applicable storage and resource management programs need to accommodate the vast Global multimedia information hunger.
- Multimedia databases need to take into accommodate various human interfaces to handle 3D-interactive objects, in an logically-perceived manner.
- Accommodating the vast resources required to utilize artificial intelligence to it's fullest potential- including computer sight and sound analysis methods.
- The historic relational databases do not conveniently support content-based searches for multimedia content.

Multimedia databases are essential for efficient management and effective use of huge amounts of data. The diversity of applications using multimedia data, the rapidly changing technology, and the inherent complexities in the semantic representation, interpretation and comparison for similarity pose many challenges. MMDBs are still in their infancy.

Introduction to Multimedia Databases

Multimedia data typically means digital images, audio, video, animation and graphics together with text data. The acquisition, generation, storage and processing of multimedia data in computers and transmission over networks have grown tremendously in the recent past. Multimedia data are blessed with a number of exciting features. They can provide more effective dissemination of information in science, engineering , medicine, modern biology, and social sciences. It also facilitates the development of new paradigms in distance learning, and interactive personal and group entertainment. It loosely fall into three main categories:
* Static media (time-independent, i.e. images and handwriting).
* Dynamic media (time-dependent, i.e. video and sound bytes).
* Dimensional media (i.e. 3D games or computer-aided drafting programs- CAD).

TYPES OF MULTIMEDIA DATABASES :
There are numerous different types of multimedia databases :
* The Authentication Multimedia Database (also known as a Verification Multimedia Database, i.e. retina scanning), is a 1:1 data comparison.
* The Identification Multimedia Database is a data comparison of one-to-many (i.e. passwords and personal identification numbers.
* A newly-emerging type of multimedia database, is the Biometrics Multimedia Database; which specializes in automatic human verification based on the algorithms of their behavioral or physiological profile.

CONTENTS OF MULTIMEDIA DATABASES :
A multimedia database needs to manage several different types of information pertaining to the actual multimedia data. They are:
* Media data - This is the actual data representing images, audio, video that are captured, digitized, processes, compressed and stored.
* Media format data - This contains information pertaining to the format of the media data after it goes through the acquisition, processing, and encoding phases. For instance, this consists of information such as the sampling rate, resolution, frame rate, encoding scheme etc.
* Media keyword data - This contains the keyword descriptions, usually relating to the generation of the media data. For example, for a video, this might include the date, time, and place of recording , the person who recorded, the scene that is recorded, etc This is also called as content descriptive data.
* Media feature data - This contains the features derived from the media data. A feature characterizes the media contents. For example, this could contain information about the distribution of colors, the kinds of textures and the different shapes present in an image. This is also referred to as content dependent data.

Relationships And Relationship Sets

A relationship is an association between several entities.
A relationship set is a set of relationships of the same type.
A relationship type R among n entity types E1, E2,....., En defines a set of associations or a relationship set among entities from these types.
In ER diagrams, relationship types are displayed as diamond-shaped boxes, which are connected by straight lines to the rectangular boxes representing the participating entity types. The relationship name is displayed in the diamond-shaped box.

RELATIONSHIP DEGREE : The degree of a relationship type is the number of participating entity types. A relationship type of degree two is called binary, and one with degree three is called ternary.




ROLE NAMES and RECURSIVE RELATIONSHIPS :
Role names signifies the role that a participating entity from the entity type plays in each relationship instance, and helps to explain what the relationship means.
Role names are not necessary in relationship types where all the participating entity types are distinct, since each entity type name can be used as a role name. However, in some cases, the same entity type participates more than once in a relationship type in different roles. In such cases the role name becomes essential for distinguishing the meaning of each participation. Such relationships are called recursive relationships.


MAPPING CONSTRAINTS :
An E-R scheme may define certain constraints to which the contents of a database must conform.
* Mapping Cardinality : It expresses the number of entities to which another entity can be associated via a relationship. For binary relationship sets between entity sets A and B, the mapping cardinality must be one of:
1. One-to-one: An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.
2. One-to-many: An entity in A is associated with any number in B. An entity in B is associated with at most one entity in A.
3. Many-to-one: An entity in A is associated with at most one entity in B. An entity in B is associated with any number in A.
4. Many-to-many: Entities in A and B are associated with any number from each other.
The appropriate mapping cardinality for a particular relationship set depends on the real world being modeled.
* Existence Dependencies: if the existence of entity X depends on the existence of entity Y, then X is said to be existence dependent on Y. (Or we say that Y is the dominant entity and X is the subordinate entity.)
For example,
o Consider account and transaction entity sets, and a relationship log between them.
o This is one-to-many from account to transaction.
o If an account entity is deleted, its associated transaction entities must also be deleted.
o Thus account is dominant and transaction is subordinate.

Conceptual Data Models for Database Design

Conceptual modeling is an important phase in designing a successful database application. The database design process consists of a number of steps listed below:

Step 1: Requirements Collection and Analysis
- Prospective users are interviewed to understand and document data requirements
This step results in a concise set of user requirements, which should be detailed and complete.
- The functional requirements should be specified, as well as the data requirements. Functional requirements consist of user operations that will be applied to the database, including retrievals and updates.
- Functional requirements can be documented using diagrams such as sequence diagrams, data flow diagrams, scenarios, etc.



Step 2: Conceptual Design / Data Modeling
- Once the requirements are collected and analyzed, the designers go about creating the conceptual schema.
- Conceptual schema: concise description of data requirements of the users, and includes a detailed description of the entity types, relationships and constraints.
- The concepts do not include implementation details; therefore the end users easily understand them, and they can be used as a communication tool.
- The conceptual schema is used to ensure all user requirements are met, and they do not conflict.

Step 3: Database Design
- Two sub-steps called Database Logical Design which define a database in a data model of a specific DBMS and Database Physical Design which define the internal database storage structure are defined.
- It also defines file organization or indexing techniques.

Step 4: Database Implementation
- Many DBMS systems use an implementation data model, so the conceptual schema is transformed from the high-level data model into the implementation data model.
- This step is called logical design or data model mapping, which results in the implementation data model of the DBMS.

Step 5: Physical Design
- Internal storage structures, indexes, access paths and file organizations are specified.
- Application programs are designed and implemented.

DBMS Three-Schema Architecture and Data Independence

WHAT IS DBMS ?
- To be able to carry out operations like insertion, deletion and retrieval, the database needs to be managed by a substantial piece of software; this software is usually called a Database Management System(DBMS).
- A DBMS is usually a very large software package that enables many different tasks including the provision of facilities to enable the user to access and modify information in the database.
- Data Description Languages (DDL) and Data Manipulation Languages (DML) are needed for manipulating and retrieving data stored in the DBMS. These languages are called respectively.

An architecture for database systems, called the three-schema architecture was proposed to help achieve and visualize the important characteristics of the database approach.

THE THREE-SCHEMA ARCHITECTURE:
The goal of the three-schema architecture is to separate the user applications and the physical database. In this architecture, schemas can be defined at 3 levels :
1. Internal level or Internal schema : Describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database.
2. Conceptual level or Conceptual schema : Describes the structure of the whole database for a community of users. It hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints. Implementation data model can be used at this level.
3. External level or External schema : It includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user is interested in and hides the rest of the database from user. Implementation data model can be used at this level.



IMPORTANT TO REMEMBER :
Data and meta-data
- three schemas are only meta-data(descriptions of data).
- data actually exists only at the physical level.
Mapping
- DBMS must transform a request specified on an external schema into a request against the conceptual schema, and then into the internal schema.
- requires information in meta-data on how to accomplish the mapping among various levels.
- overhead(time-consuming) leading to inefficiencies.
- few DBMSs have implemented the full three-schema architecture.

DATA INDEPENDENCE

The disjointing of data descriptions from the application programs (or user-interfaces) that uses the data is called data independence. Data independence is one of the main advantages of DBMS. The three-schema architecture provides the concept of data independence, which means that upper-levels are unaffected by changes to lower-levels. The three schemas architecture makes it easier to achieve true data independence. There are two kinds of data independence.

- Physical data independence
* The ability to modify the physical scheme without causing application programs to be rewritten.
* Modifications at this level are usually to improve performance.

- Logical data independence
* The ability to modify the conceptual scheme without causing application programs to be rewritten.
* Usually done when logical structure of database is altered.

Logical data independence is harder to achieve as the application programs are usually heavily dependent on the logical structure of the data. An analogy is made to abstract data types in programming languages.

Database System Concepts - Data Model, Schemas and Database state

A data model is a collection of concepts that can be used to describe the structure of a database. By structure of the database we mean the data types, relationships, and constraints that should hold on the data. Most data models also include a set of basic operations for specifying retrievals and updates on database.

Categories of Data Models:
- High level or Conceptual data models : These models provide concepts that are close to the way many users perceive data. They use concepts such as entities, attributes, and relationships. An entity represents a real-world object or concept such as an employee or a project. An attribute represents property of interest that describes an entity such as employee's salary or name. A relationship represents an interaction among the entities.
- Representational data models : These models provides concept that may be understood by end users but that are not too far removed from the way data is organized within the computer. They are used most frequently in traditional commercial DBMSs and they include the widely used relational model as well as the network and hierarchical models. These models represent data by using record structures and hence are sometimes called record-based data models.
- Low level or Physical data models : These models provide concepts that describe the details of how the data is stored in the computer by representing information such as record formats, record orderings, and access paths. An access path is a structure that makes the search for particular database records efficient.

Schemas:
The description of a database in any data model is called the database schema which is specified during the database design and is not expected to change frequently. A displayed is called a schema diagram.


A schema diagram displays only some aspects of a schema, such as names of record types and data items, and some types of constraints.

Database State or Iinstance: The actual data in a database changes every time data is inserted, deleted, or modified. The data in the database at a particular moment in time is called a database state or a snapshot. It is also called the current set of occurrences or instances in the database.

Distinguish between Database State and Database Schema:
When a new database is defined, we specify its database schema only to the DBMS. At this point, the corresponding database state is empty state. The initial state of the database is got when the database is first populated or loaded with the initial data. From then on, every time an update operation is applied to the database, we get another database state.

Introduction to Databases

Databases play an important role in almost all areas where they are used including business, engineering, medicine, law, education, and library science, to name a few.
A database is a collection of related data, where data means recorded facts. A typical database represents some aspect of the real world and is used for specific purposes by one or more groups of users. Databases are not specific to computers. Examples of non-computerized databases abound: phone book, dictionaries, almanacs, etc. A database has the following implicit properties :
1. A database represents some aspect of the real world.
2. A database is a logically coherent collection of data with some inherent meaning.
3. A databse is designed, built, and populated with data for a specific purpose.
4. A databse can be of any size and of varying complexity.
5. A database may be generated and maintained manually or it may be computerized.
A database management system (DBMS) is a collection of programs that enables users to create and maintain a database. The DBMS is a general-purpose software system that facilitates the process of defining, construction, and manipulating databases for different applications.
Defining a database involves specifying the data types, structures, and constraints for the data to be stored in the database.
Constructing a database is the process of storing the data itself on some storage medium that is controlled by the DBMS.
Manipulating a database includes such functions as querying the database to retrieve specific data, updating the database and generating the reports from the data.



CHARACTERSTICS THAT DISTINGUISH DATABASE APPROACH FROM TRADITIONAL FILE-PROCESSING APPLICATIONS :
- Existence of a catalog : It contains information such as structure of each file, the type and storage format of each data item and various constraints on the data. The information stored in catalog is called meta-data.
- Program data independence : In traditional file processing, the structure of a file is embedded in the access programs, so any changes to the structure of a file may require changing all programs that access this file. By contrast, the structure of data files is stored in DBMS catalog separately from access programs. This property is called program data independence.
- Program operation independence: Users can define operations on data as part of database applications. An operation is specified in two parts - interface of operation : includes operation name and data types of its arguments, implementation of operation : specified separately and can be changed without affecting the interface. This is called
program operation independence.
- Data abstraction : The characteristic that allows program data independence and program operation independence is called data abstraction.
- Support of multiple user views.
- Sharing of data among multiple transactions.

Main Categories of Database users are :
- Administrators.
- Designers.
- End users.
- System analysts and application programmers.
- DBMS system designers and implementers.
- Tool Developers.
- Operators and maintenance personnel.

Advantages of using Databases :
- Potential for enforcing standards.
- Reduced application development time.
- Flexibility.
- Availability of up-to-date information to all users.
- Economies of sale.