Database Management System (DBMS) is software that is used to manage databases. For Example, MySQL, Oracle, etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks.
- Database: Database is a collection of inter-related data which helps in efficient retrieval, insertion, and deletion of data from the database and organizes the data in the form of tables, views, schemas, reports, etc. For Example, a university database organizes the data about students, faculty, and admin staff, etc. which helps in efficient retrieval, insertion, and deletion of data from
- Data: It helps in the creation, modification, and removal of definitions that define the organization of data in
- Data Updating: It helps in the insertion, modification, and deletion of the actual data in the
- Data Retrieval: It helps in the retrieval of data from the database which can be used by applications for various
- User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency
Advantages of DBMS Over File System
- Redundancy of data: Data is said to be redundant if the same data is copied at many places. If a student wants to change their Phone number, he has to get it updated at various Similarly, old records must be deleted from all sections representing that student.
- Inconsistency of Data: Data is said to be inconsistent if multiple copies of the same data do not match with each other. If the Phone number is different in Accounts Section and Academics Section, it will be inconsistent. Inconsistency may be because of typing errors or not updating all copies of the same
- Difficult Data Access: A user should know the exact location of the file to access data, so the process is very cumbersome and tedious. If the user wants to search the student hostel allotment number of a student from 10000 unsorted students’ records, how difficult it can
- Unauthorized Access: File Systems may lead to unauthorized access to data. If a student gets access to a file having his marks, he can change it in unauthorized
- No Concurrent Access: The access of the same data by multiple users at the same time is known as the File system that does not allow concurrency as data can be accessed by only one user at a time.
- No Backup and Recovery: The file system does not incorporate any backup and recovery of data if a file is lost or.
DBMS 3-tier Architecture
DBMS 3-tier architecture divides the complete system into three inter-related but independent modules.
- Physical Level: At the physical level, the information about the location of database objects in the data store is Various users of DBMS are unaware of the locations of these objects.
- Conceptual Level: At a conceptual level, data is represented in the form of various database tables. For Example, the STUDENT database may contain STUDENT and COURSE tables that will be visible to users but users are unaware about their
- External Level: An external level specifies a view of the data in terms of conceptual level tables. Each external level view is used to cater to the needs of a particular category of users. For Example, FACULTY of a university is interested in looking at course details of students, STUDENTS are interested in looking at all details related to academics, accounts, courses, and hostel details as well. So, different views can be generated for different.
Data independence means a change of data at one level should not affect another level. Two types of data independence are present in this architecture:
- Physical Data Independence: Any change in the physical location of tables and indexes should not affect the conceptual level or external view of data. This data independence is easy to achieve and implemented by most of the
- Conceptual Data Independence: The data at conceptual level schema and external level schema must be independent. This means a change in conceptual schema should not affect external schema. e.g.; Adding or deleting attributes of a table should not affect the user’s view of the table. But this type of independence is difficult to achieve as compared to physical data independence because the changes in conceptual schema are reflected in users.
Introduction to the Oracle Database
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is a key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage, and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads because capacity can be easily added or reallocated from the resource pools as needed.
The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.
Oracle Database Features:-
- Scalability and Performance Features
- Manageability Features
- Database Backup and Recovery Features
- High Availability Features
- Business Intelligence Features
- Content Management Features
- Security Features
- Data Integrity and Triggers
- Information Integration Features
It is an open-source relational database management system that works on many platforms. It provides multi-user access to support many storage engines and is backed by Oracle. So, you can buy a commercial license version from Oracle to get premium support services. MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.
The features of MySQL are as follows:-
- Ease of Management: The software very easily gets downloaded and also uses an event scheduler to schedule the tasks
- Robust Transactional Support: Holds the ACID (Atomicity, Consistency, Isolation, Durability) property, and also allows distributed multi-version support.
- Comprehensive Application Development: MySQL has plugin libraries to embed the database into any application. It also supports stored procedures, triggers, functions, views, and many more for application development. You can refer to the RDS Tutorial, to understand Amazon’s
- High Performance: Provides fast load utilities with distinct memory caches and table index
- Low Total Cost of Ownership: This reduces licensing costs and hardware
- Open Source & 24 * 7 Support – This RDBMS can be used on any platform and offers 24*7 support for open source and enterprise
- Secure Data Protection: MySQL supports powerful mechanisms to ensure that only authorized users have access to the
- High Availability: MySQL can run high-speed master/slave replication configurations and it offers cluster servers.
- Scalability & Flexibility: With MySQL, you can run deeply embedded applications and create data warehouses holding a humongous amount of data.
Sybase is a computer software company that develops and sells database management systems (DBMS) and middleware products. The company was founded in 1984, and the headquarters offices are in Emeryville, CA. Sybase was the first enterprise DBMS for the Linux operating system.
Sybase products have found extensive application, particularly in commercial, industrial, and military communications systems. Spanish telecommunications operator Airtel uses Sybase Adaptive Server IQ Multiplex (ASIQ) to standardize its business information. Telstra, an Australian telecommunications provider, uses data- visualization software powered by Sybase ASIQ Multiplex to optimize its mobile phone network management capacity. Primark uses Sybase MQSeries Integrator to maximize transaction rates, optimize reliability, and allow customization of inputs and outputs. Sybase claims that its MQSeries Integrator makes it possible to add new data formats to meet the needs of clients, and to accommodate diverse system architectures at client locations. Sybase SQL Anywhere, embedded in Geodyn FxView, is used in two-way communications between military command centers and soldiers in the field.
DB2 is a database product from IBM. It is a Relational Database Management System (RDBMS). DB2 is designed to store, analyze and retrieve the data efficiently. DB2 product is extended with the support of Object-Oriented features and non-relational structures with XML.
Initially, IBM had developed a DB2 product for their specific platform. Since the year 1990, it decided to develop a Universal Database (UDB) DB2 Server, which can run on any authoritative operating system such as Linux, UNIX, and Windows.
For IBM DB2, the UDB current version is 10.5 with the features of BLU Acceleration and its code name as ‘Kepler’. All the versions of DB2 till today are listed below:
- Advanced Enterprise Server Edition and Enterprise Server Edition: It is designed for mid-size to large-size business organizations. Platform – Linux, UNIX, and Windows. Table partitioning High Availability Disaster Recovery (HARD) Materialized Query Table (MQTs) Multidimensional Clustering (MDC) Connection concentrator Pure XML Backup compression Homogeneous Federations
- Workgroup Server Edition: It is designed for Workgroup or mid-size business organizations. Using this WSE you can work with – High Availability Disaster Recovery (HARD) Online Reorganization Pure XML Web Service Federation support DB2 Homogeneous Federations Homogeneous SQL replication Backup
- Express-C: It provides all the capabilities of DB2 at zero It can run on any physical or virtual system with any size of configuration.
- Express Edition: It is designed for entry-level and mid-size businesses It is a full-featured DB2 data server.
- Enterprise Developer Edition: It offers only a single application developer. It is useful to design, build and prototype the applications for deployment on any of the IBM servers. The software cannot be used for developing applications.
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is primarily designed and developed to compete with MySQL and Oracle databases. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact-SQL).
T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capabilities of declaring variables, exception handling, stored procedures, etc. SQL Server Management Studio (SSMS) is the main interface tool for SQL Server, and it supports both 32-bit and 64-bit environments.
Microsoft and Sybase released version 1.0 in 1989. However, the partnership between these two ended in the early 1990s. Microsoft maintained ownership rights to the name SQL Server. Since the 1990s, subsequent versions of SQL Server have been released including SQL Server 2000, 2005, 2008, 2012, 2014, 2016, and 2017.
SQL Server Editions
- SQL Server Enterprise: It is used in high-end, large scale and mission-critical business. It provides High-end security, Advanced Analytics, Machine Learning,
- SQL Server Standard: It is suitable for Mid-Tier applications and Data marts. It includes basic reporting and analytics.
- SQL Server WEB: It is designed for a low total cost-of-ownership option for Web hosters. It provides scalability, affordability, and manageability capabilities for small to large scale Web
- SQL Server Developer: It is similar to an enterprise edition for the non-production environment. It is mainly used for the build, test, and demo.