Difference between Database Administrator (DBA) and Database Engineer

1. Database Administrator (DBA) :

A database administrator is responsible for management of a database (DB). His/her duties involve administration, performance tuning, optimization, backup and recovery of a database. The DBA is more of a management profile. The DBA also enhances the Logical Design of the database. He/she may be guided by the Database Engineer in his administration.

2. Database Engineer :

A database engineer develops databases for various applications. He/she might also either work as a database administrator or supervise the database administrator. The DB engineer is more of a technical profile. His/her duty is to ensure the proper functioning of the database.

Difference between Database Administrator (DBA) and Database Engineer :

DATABASE ADMINISTRATOR	DATABASE ENGINEER
Database administrator is also known as database co-ordinator, the database engineer or a database programmer in job searches.	Database Engineer is also known as software application developer or computer software engineer.
DBA is responsible for managing the database.	Database Engineer is responsible for developing the database.
It is used to define the management aspect of the profile.	It is used to define the technical aspects of the profile.
DBA handles the performance, integrity and security of a database.	Database Engineer handles the physical and logical models of a Database.
DBA develops and manages the recovery plan and back ups.	Database Engineer identifies and handles the errors in a database system.
DBA enhances and refines the Logical Design.	Database Engineer creates the logical Design of the database.
DBA is responsible for the system’s interaction with the front end users.	Database Engineers are not concerned much with the end users.
The DBA establishes the needs of the users.	The Database Engineer designs the database system to cater to the needs of the users.
The Database Administrator is guided by the Database Engineer for effective management of the databases.	The Database Engineer guides the Database Administrator for the effective management of the databases.
The overall role of a DBA is generally narrow.	The overall role of Database Engineer is relatively broader.

Continue reading

From DBA to Data Scientist

Charlie Berger wants DBAs and database developers to click a link in their autonomous database service console that can open a new world in their careers. It’s there alongside the common data management options for downloading clients or setting passwords, and it’s called Oracle ML Users. “You click on it and now you’re going down a different hallway to the land where the data scientist people hang out,” says Berger, a senior director of product management for machine learning, AI, and cognitive analytics at Oracle. Berger, a 20-year veteran of data mining and machine learning in Oracle Database leads sessions and hands-on labs at Oracle and user group events around the country.

With Oracle Autonomous Database, when DBAs click the Oracle ML Users link, they’ll find Oracle Machine Learning-based notebooks where they can define business problems, gather and prepare data, and apply machine learning algorithms available from an extensive library. Next, the DBA, database developer, or analyst can use a simple Zeppelin notebook user interface to build and test machine learning models. Much of the work will be familiar to database experts. “Database developers perform many of these tasks already,” Berger says, “but they refer to them as ETL [extract, transform, and load], data wrangling, and moving scripts into production.”

For DBAs, this is an interesting and profitable area to explore with time that’s been freed up by using the autonomous database from Oracle. Autonomous databases deploy, tune, patch, and secure themselves with no human intervention, so a DBA can think about doing bigger and better things. DBAs know their data and know their business, and “with a little coaching in machine learning concepts, they can start building and applying predictive models to their data to help their organizations run more intelligently,” Berger says.

Berger’s experience tells him that the playing field is tilted in favor of DBAs who want to move in this direction. “In my travels, I’ve discovered that it’s easier to take the person who knows and likes SQL and teach them how to begin doing real machine learning in the database, than it is to take folks who do Python or R, and who know algorithms, and teach them about SQL to do machine learning inside the database.”

Berger believes more and more data science will happen inside the Oracle database for the simple reason that “Algorithms are small and data is big. Oracle chose to move the algorithms to the data and not the other way around,” he says. “Because we moved the algorithms inside the database, you don’t have to move ever growing amounts of data out to somebody else’s open source code and algorithms.” This simplifies the process and ensures that machine learning models move immediately into production.

That’s why Berger works to help DBAs get familiar with standard practices in data science: “You have to know what the business problem is, you have to think about your data, you have to transform the data, you have to build the models, and then you have to evaluate the models and finally deploy them.” All of that is available with Oracle Machine Learning in the database.

Berger is excited to show DBAs and database developers the power of the Oracle Machine Learning tab in Oracle Autonomous Database—giving them a ready path to bring their data expertise and their database knowledge to the highly valued business function of data science. “It’s time for DBAs to get a little more of the bright data science spotlight,” Berger concludes.

Source: oracle.com

Continue reading

Difference between Oracle and SQL Server

1. Oracle Database

Oracle Database (commonly referred to as Oracle DBMS or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.

It is a database commonly used for running online transaction processing (OLTP), data warehousing (DW) and mixed (OLTP & DW) database workloads. Oracle Database is available by several service providers on-prem, on-cloud, or as hybrid cloud installation. It may be run on third party servers as well as on Oracle hardware (Exadata on-prem, on Oracle Cloud or at Cloud at Customer)

2. SQL Server

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.

Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. SQL server is tied to Transact-SQL, or T-SQL, the Microsoft’s implementation of SQL that adds a set of proprietary programming constructs.

Both Oracle and SQL Server, both are relational database management systems or RDBMS.

Following are the important differences between Oracle and SQL Server.

Key	Oracle	SQL Server
Developed By	Oracle Server is owned by Oracle.	SQL Server is developed by Microsoft.
Procedural Language	Oracle uses PL/SQL.	SQL Server uses T-SQL.
Usage	Oracle is complex to use.	SQL Server is simple to use.
OS Support	Windows, Linux, Solaris, HP-UX and OS X.	Windows and Linux.
Database Sharing	Users can share databases.	Users cannot share databases.
Package	Oracle supports Packages.	SQL Server has no concept of packages.
Powerful	Oracle is more powerful than SQL Server.	SQL is way less powerful than Oracle.

Continue reading

Why recovery is needed in DBMS

Basically, whenever a transaction is submitted to a DBMS for execution, the operating system is responsible for making sure or to be confirmed that all the operation which need to be in performed in the transaction have completed successfully and their effect is either recorded in the database or the transaction doesn’t affect the database or any other transactions.

The DBMS must not permit some operation of the transaction T to be applied to the database while other operations of T is not. This basically may happen if a transaction fails after executing some of its operations but before executing all of them.

Types of failures –

There are basically following types of failures that may occur and leads to failure of the transaction such as:

1. Transaction failure

2. System failure

3. Media failure and so on.

Let us try to understand the different types of failures that may occur during the transaction.

1. System crash –

A hardware, software or network error occurs comes under this category this types of failures basically occurs during the execution of the transaction. Hardware failures are basically considered as Hardware failure.

2. System error –

Some operation that is performed during the transaction is the reason for this type of error to occur, such as integer or divide by zero. This type of failures is also known as the transaction which may also occur because of erroneous parameter values or because of a logical programming error. In addition to this user may also interrupt the execution during execution which may lead to failure in the transaction.

3. Local error –

This basically happens when we are doing the transaction but certain conditions may occur that may lead to cancellation of the transaction. This type of error is basically coming under Local error. The simple example of this is that data for the transaction may not found. When we want to debit money from an insufficient balance account which leads to the cancellation of our request or transaction. And this exception should be programmed in the transaction itself so that it wouldn’t be considered as a failure.

4. Concurrency control enforcement –

The concurrency control method may decide to abort the transaction, to start again because it basically violates serializability or we can say that several processes are in a deadlock.

5. Disk failure –

This type of failure basically occur when some disk loses their data because of a read or write malfunction or because of a disk read/write head crash. This may happen during a read /write operation of the transaction.

6. Castropher –

These are also known as physical problems it basically refers to the endless list of problems that include power failure or air-conditioning failure, fire, theft sabotage overwriting disk or tapes by mistake and mounting of the wrong tape by the operator.

Continue reading

Metadata in DBMS and it’s types

Metadata is simply defined as data about data. It means it is a description and context of the data. It helps to organize, find and understand data. Let me explain to you by giving a real-world example of metadata:

Every time you take a photo with today’s cameras a bunch of metadata is gathered and saved with it. Such as

◉ File name,

◉ Size of the file,

◉ Date and time,

◉ Camera settings etc.

Meta data in Relational database:

Relational databases store and provide access not only data but also metadata in a structure called data dictionary or system catalog. It holds information about:

◉ tables,

◉ columns,

◉ data types,

◉ table relationship,

◉ constraints etc.

Data dictionary:

◉ A data dictionary is a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them.

◉ A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field. Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents.

Accessing metadata in RDBMS:

RDBMS provides access to their metadata with a set of tables or views often called system catalog or data dictionary. We can access those views using plain SQL statements.

Example:

select * 

from tables

Types of Metadata:

There are several sorts of metadata consistent with their uses and domain.

1. Technical Metadata –

This type of metadata defines database system names, tables names, table size, data types, values, and attributes. Further technical metadata also includes some constraints like foreign key, primary key, and indices.

2. Business Metadata –

It consists of the ownership of data, changing policies, business rules and regulations, and other business details. This type of metadata is said to a specific business.

3. Descriptive Metadata –

Descriptive metadata describes any file, folder, book, image, or video. It may include details of knowledge like title, author, date, size, author name, published on, and similarly others.

4. Operational Metadata –

This type includes the info which is currently under any operation. Besides, it represents the data that is used by executive-level managers to perform any task. Also, this sort of metadata is often purged, archived, or activated and may even be migrated.

Metadata in terms of data warehouse:

In terms of data warehouse, we can define metadata as follows:

◉ Metadata is a roadmap to data warehouse.

◉ Metadata in data warehouse defines the warehouse objects.

◉ Metadata acts as a directory. This directory helps the decision support system to locate the contents of a data warehouse.

Continue reading

How to use DBSAT for Oracle 20c and the Autonomous Database

“There should be no on/off button for security, it should always be on, everything should always be encrypted – there should be no option to turn security off” – Larry Ellison

If you would like to check how secure your Oracle database it, just run the free tool called Oracle Database Security Assessment Tool (DBSAT)!

First, download it for free from MOS Doc ID 2138254.1. Check also the DBSAT documentation.

The Oracle Database Security Assessment Tool is a simple command line tool showing how securely your database is configured, who are the users and what are their privileges, what security policies and controls are in place, and where sensitive data resides with the goal of promoting successful approaches to mitigate potential security risks.

DBSAT has three components: Collector, Reporter, and Discoverer.

Here are the steps of how to produce the report in Oracle Database 20c. DBSAT supports also Oracle Database versions Oracle 10.2 through Oracle 19c.

1. Make sure first you have zip, unzip and python on the database server:

[root@dbexam ~]# id

uid=0(root) gid=0(root) groups=0(root)

[root@dbexam ~]# cd /home/oracle/

 [root@dbexam oracle]# yum install -y zip unzip python

Loaded plugins: ulninfo, versionlock

mysql-connectors-community                               | 2.6 kB     00:00

mysql-tools-community                                    | 2.6 kB     00:00

mysql80-community                                        | 2.6 kB     00:00

ol7_UEKR5                                                | 2.5 kB     00:00

ol7_latest                                               | 2.7 kB     00:00

(1/3): mysql-connectors-community/x86_64/primary_db        |  68 kB   00:00

(2/3): mysql80-community/x86_64/primary_db                 | 128 kB   00:00

(3/3): mysql-tools-community/x86_64/primary_db             |  83 kB   00:00

(1/5): ol7_UEKR5/x86_64/updateinfo                         |  41 kB   00:00

(2/5): ol7_UEKR5/x86_64/primary_db                         | 7.6 MB   00:00

(3/5): ol7_latest/x86_64/updateinfo                        | 3.1 MB   00:00

(4/5): ol7_latest/x86_64/group                             | 660 kB   00:00

(5/5): ol7_latest/x86_64/primary_db                        |  30 MB   00:00

Excluding 262 updates due to versionlock (use "yum versionlock status" to show them)

Package zip-3.0-11.el7.x86_64 already installed and latest version

Package unzip-6.0-20.el7.x86_64 already installed and latest version

Package python-2.7.5-86.0.1.el7.x86_64 already installed and latest version

Nothing to do

[root@dbexam oracle]#

  

2. Create a database user for running the Security Assessment Tool. You can also run it as sysdba but I would rather have a separate user:

create user dbsat_user identified by dbsat_user;

grant create session to dbsat_user;

grant select_catalog_role to dbsat_user;

grant select on sys.registry$history to dbsat_user;

grant select on sys.dba_users_with_defpwd to dbsat_user; 

grant select on audsys.aud$unified to dbsat_user; 

grant audit_viewer to dbsat_user; 

grant capture_admin to dbsat_user;

grant dv_secanalyst to dbsat_user;

3. Run the tool:

[oracle@dbexam dbsat]$ ./dbsat collect dbsat_user/dbsat_user@//localhost:1521/xxxxx.oraclevcn.com dbsat_output

 

Database Security Assessment Tool version 2.2.1 (May 2020)

 

This tool is intended to assist you in securing your Oracle database system. You are solely responsible for your system and the effect and results of the execution of this tool (including, without limitation,

any damage or data loss). Further, the output generated by this tool may include potentially sensitive system configuration data and information that could be used by a skilled attacker to penetrate your system. You are solely responsible for ensuring that the output of this tool, including any generated reports, is handled in accordance with your company's policies.

 

Connecting to the target Oracle database...

 

SQL*Plus: Release 20.0.0.0.0 - Production on Tue Nov 10 08:35:00 2020

Version 20.2.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 20c EE High Perf Release 20.0.0.0.0 - Production

Version 20.2.0.0.0

 

Setup complete.

SQL queries complete.

.......

OS commands complete.

Disconnected from Oracle Database 20c EE High Perf Release 20.0.0.0.0 - Production

Version 20.2.0.0.0

DBSAT Collector completed successfully.

 

Calling /u01/app/oracle/product/20.0.0/dbhome_1/bin/zip to encrypt dbsat_output.json...

 

Enter password:

Verify password:

  adding: dbsat_output.json (deflated 88%)

zip completed successfully.

   

[oracle@dbexam dbsat]$

4. Generate the report:

[oracle@dbexam dbsat]$ ./dbsat report dbsat_output

 

Database Security Assessment Tool version 2.2.1 (May 2020)

 

This tool is intended to assist you in securing your Oracle database system. You are solely responsible for your system and the effect and results of the execution of this tool (including, without limitation,

any damage or data loss). Further, the output generated by this tool may include potentially sensitive system configuration data and information that could be used by a skilled attacker to penetrate your system. You are solely responsible for ensuring that the output of this tool, including any generated reports, is handled in accordance with your company's policies.

 

Archive:  dbsat_output.zip

[dbsat_output.zip] dbsat_output.json password:

  inflating: dbsat_output.json

DBSAT Reporter ran successfully.

 

Calling /usr/bin/zip to encrypt the generated reports...

 

Enter password:

Verify password:

        zip warning: dbsat_output_report.zip not found or empty

  adding: dbsat_output_report.txt (deflated 78%)

  adding: dbsat_output_report.html (deflated 84%)

  adding: dbsat_output_report.xlsx (deflated 3%)

  adding: dbsat_output_report.json (deflated 82%)

zip completed successfully.

5. Extract (for example) the .html file:

[oracle@dbexam dbsat]$ unzip dbsat_output_report.zip

Archive:  dbsat_output_report.zip

[dbsat_output_report.zip] dbsat_output_report.txt password:

  inflating: dbsat_output_report.txt

  inflating: dbsat_output_report.html

  inflating: dbsat_output_report.xlsx

  inflating: dbsat_output_report.json

6. View the report, note that most areas will probably be in PASS status, some will be with LOW RISK, some might be even classified as HIGH RISK:

Although in ADB we have no OS access, you can still run the Database Security Assessment Tool.

You need to have the Instant Client installed and then use the ADMIN database user from Autonomous DB.

Clearly, you will get “ORA-20002: Complete without OS Commands” as the execution of the Collector was on the client server instead of on the underlying DB server to which we have no access.

DBSAT on Youtube:

Source: juliandontcheff.wordpress.com

Continue reading

Types of Spatial Queries in DBMS

Any type of spatial data that is data related to location and which represents objects defined in a geometric space, is stored and maintained by Spatial Databases. These are used to handle these Spatial Databases. Spatial database mainly contain representation of simple geometric objects such as 3D objects, topological coverage, linear networks and TINs(Triangulated irregular networks).

There are mainly three types of spatial queries as given below.

1. Nearness queries:

It request objects that present near a specified location. A query to find all Hotels that lie within a given distance of a given point is an example of a nearness query. The nearest-neighbor query requests the object that is nearest to a specified point.

For example, we may want to find the nearest Railway station. Note that this query does not have to specify a limit on the distance, and hence we can ask it even if we have no idea how far the nearest Railway station lies.

2. Region queries:

It deal with spatial regions. For example, a query can ask for objects

that is present partially or completely within a fixed region. A query to find all medicine shops within the geographic boundaries of a given town or we can find all the available school in a particular city.

3. Union/Intersection:

In this type of queries, we may also request intersections and unions of regions.

For example, given region information, such as annual rainfall and population density, a query may request all regions with a low annual rainfall as well as a high population density.

In general, there is a combination of spatial and Non spatial requirements in the queries on spatial data . For example, we may want to find the nearest restaurant that has vegetarian selections and that charges less than $10 for a meal.

Since spatial data are inherently graphical, we usually query them by using a graphical query language. Results of such queries are also displayed graphically, rather than in tables. The user can invoke various operations on the interface, such as choosing an area to be viewed (e.g., by pointing and clicking on suburbs west of Manhattan), zooming in and out, choosing what to display on the basis of selection conditions (e.g., hotels with more than three stars), overlay of multiple maps (e.g., hotels with more than three stars overlaid on a map representing areas with low crime rates), and so on. The graphical interface constitutes the front end.

Extensions of SQL have been proposed to permit relational databases to store and retrieve spatial information efficiently, and also to allow queries to mix spatial and non spatial conditions. Extensions include allowing abstract data types, such as lines, polygons, and bit maps, and allowing spatial conditions, such as contains or overlaps.

Continue reading

Oracle Database Appliance X8 More Powerful, Higher Capacity, Improved Connectivity

ODA has experienced broad adoption around the world in all industries across a wide range of customers, from SMBs to Fortune 100 corporations. As a result, we continue to invest in Oracle Database Appliance by offering the latest generation hardware along with on-going software enhancements for all supported models. We are committed to maintaining Oracle Database Appliance as the database platform of choice for customers looking to realize the benefits of:

◉ Simple, Optimized, Affordable Database System with Built-in Automation

◉ Integrated full-stack patching

◉ Pay As You Grow, Capacity-On-Demand Oracle Database Licensing 

◉ Cost-effective Consolidation Platform for Databases and Applications

◉ On-premises Database Platform Offering a Path to the Cloud 

We are excited to announce Oracle Database Appliance X8!  

Key Enhancements:

◉ More Powerful

     ◉ New Intel Xeon Gold 5218, 16-core, 2.3GHz processors with fixes for Spectre and Meltdown vulnerabilities, provide CPU performance improvements. 

◉ Higher Storage Capacity

     ◉ ODA X8-2M supports up to 76.8TB NVMe storage, 50% more than X7

     ◉ ODA X8-2-HA base configuration includes 46TB SSD storage (140% more than X7), expandable to 369TB SSD (174%+ more than X7) or 596TB SSD+HDD (76%+ more than X7)

Improved Network Connectivity

     ◉ Support for up to 3 network cards per server: 25GbE SFP28 (Fiber) or 10GBase-T (copper) options

     ◉ Flexibility to separate customer database client network traffic from backup, management, or other traffic.

◉ Oracle Database 19c Support

     ◉ Run Oracle Database 19c Enterprise Edition and Standard Edition 2, the latest long-term support releases, in addition to 18c, 12g, and 11g.

Recent ODA Software Enhancement for All Supported Models:

◉ Simplified backup and restore

     ◉ The Brower User Interface enables backup and restore for both on-premise and Oracle cloud. Supports backup to local storage, NFS storage, or Object Store in Oracle Cloud.

◉ Backup and restore system configuration

     ◉ ODABR - A new backup and restore utility is available for administrators to create a local disk snapshot for ODA bare metal systems.

◉ Patching enhancements to improve security and availability

     ◉ Out of Cycle Database and OS Patching uncouples Critical Database Patches from the ODA Bundle Patch so that critical database updates and kernel RPMs can be installed independently of the current bundle patch. This allows users to implement the most current critical patches to meet security and compliance requirements as soon as they become available.

     ◉ Rolling patching has been enhanced to include shared storage and eliminate the need for planned outages for storage updates. Individual drives are patched without disruption, allowing the system to continue to run during firmware updates.

◉ Monitoring enhancements to improve operability

     ◉ The ODA Brower User Interface (BUI) allows users to display per node metrics for data collection, detailed diskgroup storage utilization, feature usage and high-water marks, and software bill of materials tracking for richer system management.

Continue reading

Simplified Data Guard Integration with Oracle Database Appliance

The ODA 19.8 Release integrates Oracle Data Guard configuration, simplifying the deployment of Data Guard between Oracle Database Appliance systems down to a single command!

Oracle Data Guard is an application that ensures high availability, data protection, and disaster recovery for enterprise data. It supports one or more standby databases to protect from failures, disasters, human error, and data corruption while providing high availability for mission-critical applications.

ODA users with basic knowledge of Oracle Data Guard can now implement standby databases in an easier and faster way, enabling them to meet one or more of the following use-cases:

◉ Maximum Availability Architecture (MAA) best practices with integrated RAC + Data Guard (ODA HA Models)

◉ Disaster Recovery for all  ODA models

◉ Enhanced availability for single-node ODA with a local Standby

◉ Reduce downtime through rolling patching/upgrade

◉ Migrating from an older to a newer generation ODA system

High-Level Overview

The high-level process to set up Data Guard between two ODA systems is shown in the following illustration. Before you can run the single configuration command, you need to:

◉ From your primary system: back up the database to either the OCI Object Store or External FRA (NFS)

◉ From your standby system: restore the backup to a standby database

◉ Once you have restored the backup to a standby, you are ready to configure Data Guard

Configuring Data Guard

The single command to configure Data Guard is shown in the next screenshot, and this example shows the CLI dialog with the required data. Prepare the data ahead of time, to make it easier to enter it.

◉ When specifying Transport Type and Data Guard protection mode, enter a supported combination, otherwise the configuration will fail. See table in the documentation for valid combinations.

◉ Keep in mind that a license is required, if you want to use Active Data Guard to extend the basic Data Guard functionality

◉ It is recommended to enable Flashback for recovery, but be aware that it will consume additional storage

◉ When done entering data, you will get a “Configure Data Guard Completed” message indicating a successful configuration

And that’s all that is needed to configure Data Guard between two ODA systems!

Configuring Data Guard between two ODA systems has been dramatically simplified down to a single command. Users can now configure Data Guard within minutes, and even less experienced administrators will have an easier time setting up Data Guard to protect their Oracle data. Database versions 11.2, 12.1, 12.2, 18.x, and 19.x are all supported.

In addition, there are other integrated operations with Data Guard that allow you to perform tasks such as:

◉ Switching roles between primary and standby to test your standby system, or upgrade it

◉ Failover a standby database to the primary when your primary system failed, or to upgrade your systems in a rolling fashion

◉ Reinstating a failed primary database, to restore your HA configuration

◉ Migrating from an older to a newer generation ODA system and retire the old one

Continue reading

Types of OLAP Systems in DBMS

There are 3 main types of OLAP servers are as following:

1. Relational OLAP (ROLAP) – Star Schema based –

The ROLAP is based on the premise that data need not to be stored multidimensionally in order to viewed multidimensionally, and that it is possible to exploit the well-proven relational database technology to handle multidimensionality of data. In ROLAP data is stored in a relational database. In essence, each action of slicing and dicing is equivalent to adding a “WHERE” clause in SQL statement. ROLAP can handle large amounts of data. ROLAP can leverage functionalities inherent in the relational database.

2. Multidimensional OLAP (MOLAP) – Cube based –

MOLAP stores data on disks in specialised multidimensional array structure. OLAP is performed on it relying on the random access capability of the arrays. Arrays element are determined by dimension instances, and the fact data or measured value associated with each cell is usually stored in the corresponding array element. In MOLAP, the multidimensional array is usually stored in a linear allocation according to nested traversal of the axes in some predetermine order.

But unlike ROLAP, where only records with non-zero facts are stored, all array elements are defined in MOLAP and as a result, the arrays generally tend to sparse, with empty elements occupying a greater part of it. Since both storage and retrieval costs are important while assessing online performance efficiency, MOLAP systems typically include provision such as advanced indexing and hashing to locate data while performing queries for handling sparse arrays. MOLAP cubes are fast data retrieval, optimal for slicing and dicing and it can perform complex calculation. All calculation are pre-generated when the cube is created.

3. Hybrid OLAP (HOLAP) –

HOLAP is a combination of ROLAP and MOLAP. HOLAP servers allows storing the large data volumes of detail data.On the one hand, HOLAP leverages the greater scalability of ROLAP. On the other hand, HOLAP leverages the cube technology for faster performance and for summary-type inforamtion. Cubes are smaller than MOLAP since detail data is kept in the relational database. The database are used to stores data in the most functional way possible.

Some other types of OLAP:

1. Web OLAP (WOLAP) –

It is a Web browser based technology. In traditional OLAP application is accessible by the client/server but in this OLAP application is accessible by the web browser. It is a three tier architecture which consist of client, middleware and database server. The most appealing features of this style of OLAP was (past tense intended, since few products categorize themselves this way) the considerably lower investment involved on the client side (“all that’s needed is a browser”) and enhanced accessibility to connect to the data. A Web based application requires no deployment on the client machine. All that is required is a Web browser and a network connection to the intranet or Internet.

2. Desktop OLAP (DOLAP) –

DOLAP stands for desktop analytical processing. In that user can download the data from the source and work with the dataset, or on their desktop. Functionality is limited compare to other OLAP application. It has cheaper cost.

3. Mobile OLAP (MOLAP) –

MOLAP is wireless functionality or mobile devices. User is work and access the data through the mobile devices.

4. Spatial OLAP (SOLAP) –

Merge capabilities of both Geographic Information Systems (GIS) and OLAP into single user interface, SOLAP egress. SOLAP is created because the data come on the form of alphanumeric, image and vector. This provides the easy and quick exploration of data that resides on a spatial database.

Continue reading

New report ranks Oracle as overall leader in enterprise cloud databases

A report published by KuppingerCole Analysts AG, Leadership Compass: Enterprise Databases in the Cloud, ranks Oracle Autonomous Database as the overall leader versus Amazon and Microsoft with leadership positions in cloud database products and technology, and cloud database innovation. In ranking Oracle as the overall leader, analysts at KuppingerCole rate Oracle’s cloud database offerings with a “strong positive” in security, functionality, deployment, interoperability, and usability.

“Oracle has been continuously developing numerous innovative database capabilities for decades,” notes lead author Alexei Balaganski, giving one of the reasons for Autonomous Database’s ranking as “overall leader” in enterprise cloud database.

Decades of innovation by Oracle product management and development teams—a process of day-in and day-out work on their customers’ most challenging use cases—has enabled Oracle to bring deep capabilities into the cloud and automate them on its second-generation cloud infrastructure.

KuppingerCole’s annual Leadership Compass report provides an overview of enterprise databases for modern, cloud native application architectures as well as data processing and analytics at scale.

The advantage of Oracle’s single multimodel converged database 

Citing the increased volumes and complexity of information that ends up being stored in the cloud, the report lauds Oracle’s unique ability to keep it simple for cloud customers: 

“With over 40 years of expertise, the company is arguably the leading solution provider for enterprise transaction processing, data warehousing, and mixed database workloads. The core technology that provides the common foundation for Oracle’s entire database services portfolio is Oracle Database, a multimodel database management system that combines a traditional relational database with NoSQL data models like JSON, key-value, and even HDFS file formats.

The report describes how Oracle has all its capabilities available in a single, multimodel database, running on highly optimized hardware, “powered by intelligent automation eliminating the human factor from database management—this is the kind of innovation customers are looking for in a cloud database.”  By contrast, Amazon offers no similar multimodel database.  In its analysis of Amazon Web Services (AWS), the report states, “… currently, AWS has a portfolio of 15 specialized fully managed database services.” 

Product Leaders are based on "the functional strength of the vendors’ solutions," says the report.

KuppingerCole believes the multimodel approach “offers more long-term benefits for customers, including significantly lower learning curve for developers, more flexibility in changing data models mid-development, and availability of all project data in one place without creating multiple data silos. A major downside of the other, tool-focused approach is, of course, the increased difficulty of consistent data management and analytics across several incompatible database engines, which has to be compensated with additional tools or services.”

 

Complementing all of this, according to the report, is Oracle’s “security by design” approach, which brings data protection and privacy controls into every layer of the company’s cloud infrastructure. Combined with “highly competitive cloud service pricing, this makes Oracle Cloud a compelling alternative to its more established competitors, especially for enterprises operating in highly regulated industries or having massive egress traffic requirements,” the report says.

Cloud innovation: Oracle Autonomous Database 

Citing Oracle’s decades-long pursuit of database innovation, the report singles out Oracle’s autonomous database, which, eliminates human labor and human error with automated provisioning, upgrades, backup, and disaster recovery with no downtime.

Introduced in 2018, Oracle Autonomous Database uses machine learning and automation to perform DBA tasks without human intervention, “not just substantially increasing security and compliance of sensitive data stored in Oracle databases, but also noticeably reducing operational costs in the cloud,” notes the report. 

Oracle offers, "the kind of innovation customers are looking for in a cloud database," says the report.

“Since 2020, Autonomous Database services are available on-premises as well (or even as a part of Oracle Dedicated Region with over 50 managed services), delivering a truly unified ‘hybrid-native’ platform for managing enterprise data at the cloud scale, while retaining the level of security and compliance previously available on-premises only.”

The report offers this outlook for Oracle’s future as a cloud service provider (CSP): 

“…Oracle Cloud is currently growing at an impressive rate, planning to surpass all other major CSPs by 2021. Co-managed and autonomous database services are a major part of the company’s cloud strategy, making Oracle Database available in a public cloud or in a Cloud@Customer private cloud format, either on commodity hardware or on highly optimized Exadata infrastructure.”

Continue reading

Use of DBMS in System Software

Here we are going to discuss about how a user interacts with a DBMS, and how the DBMS is related to system software. Using a general-purpose programming language, user can write a source program in the normal way. However, instead of writing I/O statements of the form provided by the programming language, the programmer writes commands in a data manipulation language (DML) defined for use with the DBMS. Processor may be used to convert the DML commands into programming language statements that call DBMS routines.

Using the programming language itself some DMLs are defined as a set of CALL statements. Here given are the two principal methods for user interaction with a DBMS.

Figure 1 (a): Interaction with a DBMS using a data manipulation language

Interaction with a DBMS using a query language is the another approach to DBMS.There is no need for the user to write the programs for accessing a database rather user only needs to enter the commands in a special query language defined by DBMS. These commands are processed by a query-language interpreter, which calls DBMS routines to perform the requested operations.

Each and every approach that leads to user interactivity with a DBMS has its own advantages. Results can be obtain much faster with a help of query language, because there is no need to write and debug programs, which becomes very beneficial for the non-programmers to used it efficiently. Allowing the programmer to use all the flexibility and power of a general-purpose programming language is the big advantage of DML however much effort from the user is required by this approach. Most modern database management systems provide both a query language and a DML so that a user can choose the form of interaction that best meets his or her needs.

Figure 1 (b): Interaction with a DBMS using a query language

Here are some steps to show how a typical sequence of actions is being performed by a DBMS:

◉ Step-1: The sequence of events begins when the DBMS is entered with the help of a call from application program A. We assume this call is a request to read data from the database. There are similar sequences of events for other types of database operations.

◉ Step-2: The request from program A is stated in terms of the subschema being used by A. To process a request which is being requested from program A and is stated in terms of the subschema which is being used by A, the DBMS must first examine the subschema definition.

◉ Step-3: Relationship between the subschema and the schema must be considered by the DBMS to interpret the request in terms of the overall logical database structure.

◉ Step-4: The DBMS examines the data mapping description, after determining the logical database records that must be read in terms of schema. The information regarding the need of locating the required records in the files of the database is given by this operation.

◉ Step-5: At this point, a logical request for a subschema record has been converted into physical requests by DBMS to read data from one or more files. These requests for file I/O are passed to the operating system using the types of service calls.

◉ Step-6: The operating system then issues channel and device commands to perform the necessary physical I/O operations. These I/O operations read the required records from the database into a DBMS buffer area.

◉ Step-7: All the data requested by the application program is present in central memory after the physical I/O operations have been completed. The DBMS accomplishes this conversion by again comparing the schema and the subschema.

◉ Finally, the DBMS returns control to the application program and makes available to the program a variety of status information, including any possible error indications.

For clear understanding, here is the diagram given:

Figure 2: Typical sequence of actions performed by a DBMS

Continue reading

Difference between RDBMS and DBMS

Database Management System (DBMS) is a software that is used to define, create and maintain a database and provides controlled access to the data.

Relational Database Management System (RDBMS) is an advanced version of a DBMS.

DBMS	RDBMS
DBMS stores data as file.	RDBMS stores data in tabular form.
Data elements need to access individually.	Multiple data elements can be accessed at the same time.
No relationship between data.	Data is stored in the form of tables which are related to each other.
Normalization is not present.	Normalization is present.
DBMS does not support distributed database.	RDBMS supports distributed database.
It stores data in either a navigational or hierarchical form.	It uses a tabular structure where the headers are the column names, and the rows contain corresponding values.
It deals with small quantity of data.	It deals with large amount of data.
Data redundancy is common in this model.	Keys and indexes do not allow Data redundancy.
It is used for small organization and deal with small data.	It is used to handle large amount of data.
It supports single user.	It supports multiple users.
Data fetching is slower for the large amount of data.	Data fetching is fast because of relational approach.
The data in a DBMS is subject to low security levels with regards to data manipulation.	There exists multiple levels of data security in a RDBMS.
Low software and hardware necessities.	Higher software and hardware necessities.
Examples: XML, Microsoft Access, etc.	Examples: MySQL, PostgreSQL, SQL Server, Oracle, etc.

Continue reading