What are the database

• What is data?

        data is known facts that can be recorded and have an implicit meaning.

• What is a Database?

        A database is a collection of related data.those are stored and accessed electronically from a computer system.

• There are four types of databases.

1. text databases
2. desktop databases
3. relational database management system
4. object-oriented database

• There are two ways to categorize the database according to the logical design.

1. Operational database
2. Database Warehouse

Example of a database

Mini-world -mini-world is some part of the real world about which data stored in a database.

Mini-world for the example: part of a school environment.

Mini world entities:

• Teacher
• Student
• Extra curriculum activities
• Results

Mini-world relationships

• Students are playing cricket.
• The teacher teaches the students.

Database

Main characteristics of Database Technology

• Self - contained nature of a database system.
• Insulation between programs and data. 
• Data abstraction.
• Support for multiple views of the data.

Additional Characteristics of Database Technology

• Data sharing among multiple users.
• Complex relationships are represented among data.
• Providing backup and recovery services.
• Restricting unauthorized access to data.

Classes of Database users on the scene

that's means persons whose job involves daily use of a large database.

• Database administrators.
• Database Designers
• end-users

                   causal end users

                   Parametric end users

• Sophisticated end users.
• System analyzers/Application programmers.
• DBMS designers and implementer s.
• Tool Developers.
• Operators and maintenance personal.

• What Is Database Management system?

   

   Database  Management System (DBMS) is a software system. It is used to create, organize and manage the database.it facilitates the creation and maintenance of a computerized database.DBMS allows the definition, creation, querying, update and administration of the database.

Examples for DBMS

• MySQL
• Maria DB
• Oracle
• PostgreSQL
• MSSQL

DBMS are using some common terminologies.

1. Tuple : tuple is the rows in the database.
2. Table : table is a collection of tuples.and also it has related information with key.therefore, table can have duplication of data tuples.
3. Schema : Schema is the structure of the relation or a table.
4. Data Redundancy : Data redundancy ensure there are no multiple occurrence of some data hence avoids data duplication.
5. Key: Keys in a table are related to identify the unique attribute of the table.

• What is Database System?

The Database Management software together with the data itself call as Database system

MySQL Database

MySQL is a open source relational database management system.The name of MySQL is consisted with two names."My" is the name of co-founder Michael Widenius's daughter."SQL" the abbreviation for "Structured Query Language".  

The original author of the MySQL is the Swedish company MySQL AB.It is bought by Sun Micro-systems. The developer is Oracle organization.the initial release was happened 24 years ago,on 23rd May 1995.the programs are written from C,C++ high level language programs.And also it is supported to the operation systems that are Linux,Solaris,mac-OS,Windows and FreeBSD. The type of the database management system RDBMS.

MySQL is a free and open-source software.It has use the General Public License (GNU) and it is available in variety proprietary of licenses.

MySQL is a component of the LAMP web application software stack.MySQL is used many database driven applications.Those are Drupe,Joomla,phpBB and WordPress. Not only that MySQL is used in many websites.those are Facebook,twitter,MediaWiki,You tube.

MySQL is worked on many system platforms.those are AX,BSDs,FreeBSD,HP-UX, eComStation, 15/OS, IRIX, Linux, macOS, Microsoft Windows, NetBSD, Nov-ell Net-ware, OpenBSD, Open-Solaris, OS/2 warp , QNX, Oracle Solaris, Symbian, Suns, SCO Open-server, SCO Unix-ware,

The client libraries are used dual-licensing distribution.they are offered GPL 2nd version.there is free supports available in different IRC channels.And also oracle has offered paid support thorough MySQL enterprises products.they are in different service scope and prices.In addition, third party organisation are existed to provide support and services.It has positive reviews and reviwers.

History of the MySQL

MySQL is founded by Davd Axmark,Alan Larsson and Michael Wideninus.

Michael Wideninus

first version of MySQL was based only on the personal usage.It was mSQL and it was written on low level language.creators were focused on too slow and inflexible.They were happened to create new SQL interface while keeping the same API as mSQL. As this difficulties developers were focused to use MySQL instead of mSQL.

MySQL is offered two different editions.

1. Open source community server
2. Proprietary enterprise server

The MySQL enterprise server use proprietary extension series which are installed as server plugins.But those are shared the version numbering system.It has built from same code base.

Major features available in MySQL

• Cross-Platform support.
• Triggers
• Cursors
• A board subset of ANSI SQL 99
• Extensions
• Information schema
• A set of SQL mode options to control run time behavior
• Distributed transaction processing support
• Online Data Definition Language
• Transactions with savepoints
• SSL support
• Query catching
• sub-selects
• Build in replication support
• Embedded database library
• Unicode support
• Full text indexing and searching
• Multiple storage engines
• Native storage engines
• commit grouping and gathering multiple transactions from multiple connections

There are some limitations in MySQL

• When we are using some storage engines,MySQL doesn't comply with the full SQL standard for some implemented functionality.
• Triggers are limited to one per action or timing.
• There are many inbuilt functionalities in MySQL.  

Project forks

MySQL is existed many variety project forks.

• Current

          MariaDB

          Percona server for MySQL

• Abandoned

      Drizzle

      Web scale SQL

      

There are some user interfaces

• Graphical user intrfaces

        MySQL workbench

        Adminer

        ClusterControl

        Database workbench

        DBeaver

        DBEdit

        HeidiSQL

        LibreOffice Base

        Navicat

        phpMyAdmin

        Sequel pro

        SQLBuddy

        SQLyog

        Toad for MySQL

         Webmin

      

       

• Command-line interfaces

MariaDB Database

MariaDB server is a one of the most popular database server in the world.It was made by original founders MySQL database.The users include google,wikipedia and wordpress.com.

The data  of the MariaDB runs in a wide array of applications as data into structures information such as ranging from banking to websites.The advantages MriaDB over MySQL, it is fast , scalable and robust,storage engines have rich ecosystem,plugins.Therefore MariaDB uses in wide variety of uses cases.

MariaDB has developed as open source software and as a relational database.For access the data it provides the interface of SQL.MariaDB is included the GIS and JSON features.

MariaDB database server has published as free and open source software under the license version 2.

Reason for calling as MariaDB

MySQL has come from Monty's first daughter My.Like that it continues the tradition after naming it from name of the Monty's  younger daughter Maria.The name Maria was given initially to a storage engine.

Foundation to the MariaDB

The foundation of the MariaDB is supported to continuity and open collaboration in the MariaDB ecosystem.it has guaranteed that,there is a global contact point for collaboration.And also,community can always rely upon the server.

MariaDB is created by Michael Widenius. He is called as Monty.He was born on 3 March 1962.He is the main author of the original version of MySQL database.He is a founding member of the MySQL AB company and MariaDB corporation AB.

MariaDB database is used the high level languages that are, C,C++,Perl and Bash.

MariaDB is intended to maintain with high compatability with MySQL.It includes as new features,storage engines like Aria,ColumnStore and MyRocks.It is a third party software.The API and the protocol are compatible with those used by MySQL.

Cloud deployment of MariaDB

• MariaDB is a supported database in Microsoft Azure.
• It is a supported database in Amazon RDS.
• And also,it is a supported database in Alibaba Cloud.

MariaDB is used at ServiceNow, DBS Bank,Google,Mozilla and the Wikimedia Foundation.Several Linux and BSD distributions are included in MariaDB. Such as Arch Linux, Manjaro, Debian, Fedora, Red Hat Enterprise Linux, CentOS, Mageia, open-SUSE, SUSE Linux Enterprise Server, OpenBSD and FreeBSD

Microsoft SQL

Microsoft SQL server is a relational database management system.It is developed by Microsoft.It is a software product with the primary function of storing and retrieving data as a database server.those software applications run on same computer or on another computer across a network.

This program has written on C and C++ languages.Operating Systems that are run this,Linux,Microsoft Windows Server and Microsoft Windows.This software is available in many languages.License are in as a proprietary software.

History of the MSSQL

Ashton-Tate and Sybase were Joined Microsoft to create a variant of Sybase SQL Server in 1988.It was the first version of the Microsoft SQL and the entry of the database market.It was competing against the oracle,IBM,Inform-ix,Ingres and Sybase.In 1993 Sybase ND Microsoft parted ways and each pursued its own designs.miscrosoft SQL 7.0 wasa major rewrite of the older Sybase.It was coded in C.After fe years User Mode Sheduling was introduced to handle the SQL.It was better than the Windows Preemptive multi threading. and also,it was included a multi dimensional database product.It was called as SQL OLAP service.It was the last version to run on the DEC Alpha platform.

Architecture of the MSSQL

The protocol layer implements the external interface to SQL server.All operations can be invoked from communicating to it via Microsoft defined format.It is called as Tabular Data stream(TDS).TDS is a application layer protocol.It is used to transfer data between the database server and the client.TDS packet can be encased in other transport protocols also.Those are TCP/IP.named pipes and shared memory.

Data storage is a database and it has collection of tables with typed columns.The sever of SQL is supported different data types such as integer,float,decimal,character,binary.And also it allows user-defined composite types to be defined and  used.And also tables, a database can contain other objects such as views,stored procedures,indexes and contains.SQL data base can contain maximum of 2 to the power 31 objects and maximum of 2 to the power 60 files.

the storage space has allocated  to a database is divided into sequentially numbered pages each 8kb in size. Page type define the data contain in the page.Page is the basic unit of an I/O operation,space is actually manage in terms of an extent which consists of 8 pages.The data in columns can be moved to a new page and can replace with a pointer to the data.


Buffer management

SQL server buffers pages in RAM to minimize disk I/O.Any 8KB page can be buffered to the memory.It is called as buffer cache.The buffer cache is managed by the buffer manager.The page is updated on the disk by the buffer manager.

Concurrency and locking

SQL server allows to multiple clients to use the same database concurrency.It need to control concurrent access to shared data.SQL server provides two modes of concurrency control.

• Pessimistic concurrency

         Pessimistic concurrency is used SQL server controls concurrent access by using locks.

• Optimistic concurrency

                Optimistic concurrency is a control mechanism,it is similar to the multi version concurrency control used in databases.

Services of the MSSQL

• Machine Learning Services
• Service Blocker
• Replication Services

            Transaction replication

             Merge replication

             Snapshot replication

• Analysis Services
• Reporting services
• Notification Services
• Integration Services  
• Full text search Services
• SQLCMD
• Visual Studio
• SQL server management studio
• SQL server operation studio
• Business intelligence Development Studio

Interfaces of MSSQL

Oracle Database

Oracle Database is a proprietary multi-model database management system.It is commonly referred to as oracle RDBMS. And also simply known as Oracle.It is produced and market by Oracle corporation.

Oracle Database is commonly used for running Online Transaction Processing(OLTP),Data Warehousing(DW) and mixed of above two database workloads.

History of the Oracle Database

This is found by Larry Ellison and his two friends and former co-workers,Bob Miner and Ed Oates.First they started a consultancy called Software Development Laboratory(SDL). SDL was developed the original version of the Oracle Software.The name of the company was come from the code name of a CIA-funded project Ellison had worked.At the same time formerly employed by Ampex.

Different between Oracle and MSSQL database

There are so many differences among Oracle database(Relational database management system) and the Microsoft Database.Both of Oracle and MSSQL are widely and most popular databases.How ever,there are some similarities also between those two.And also there are a number of key differences.

When we consider about the language,both systems are used the structured query language or SQL.And also MSSQL is used Transact SQL or T-SQL.It is an extension of the SQL.It has developed by sybase. The Oracle is used PL/SQL .It means Procedural Language SQL.Both languages has same facilities but different syntax and capabilities.The main different of both languages are, store variables,store procedure and the building functions.Oracle can group procedure together from PL/SQL.But MSSQL can't do that option.PL/SQL is complex and more powerful. T-SQL is simple and easier to handle.

When we consider about the Transaction control,it is a very considerable different among both of oracle and the MSSQL. Transaction means,a group of operations or tasks can be get with together as a single unit.SQL queries can modify the records that,updated at the same time,when those are failed to update any single record among the set of data. MSSQL is executed and committed each task individually. And also it is difficult to roll back changes if any errors are occurred.when we think about the Oracle,each new database connection is treated as  new transaction.The changes are made only in  the memory because queries are executed and commands are issued.There is no committed since an explicit commit statement is given. After commit the next command is issued and the initiated the new transaction.After that the process begins again.It is useful to error control and it is flexible.

When we consider about the Organization of Database Objects, MSSQL is organized all objects,tables,views and procedures by the database name.And also,in SQL server each database has private unshared disk file on the server.When we go to Oracle,all the database objects has grouped by schemas.those are subset collection of database object and and all the database objects are shared among all schemas and users.

Finally we can come to a decision,both Oracle and MSSQL servers are powerful RDBMS options.Both of them can be used in roughly equivalent  ways.

Interfaces of the Oracle Database