Database Management System (DBMS)

Data

In simple words data can be facts related to any object in consideration. For example your name, age, height, weigh, etc are some data related to you. A picture , image , file , pdf etc can also be considered data. So data is everything which needed memory or in device.

Database

Database is a systematic collection of data. Or we can say a database is a collection of information that is organized so that it can be easily accessed, managed and updated.

• In Database a data is organized into rows, columns and tables. 
• In database data is indexed to make it easier to find relevant information. 
• In Database data gets updated, expanded and deleted as new information is added. 
• Databases process workloads to create and update themselves, querying the data they contain and running applications against it.

Database management system (DBMS)

DBMS stands for Database Management System. DBMS means database with management system.

That means a system used to manage database.

Database Management System is a set of programs to store, retrieve and manage data from database.

A DBMS is an integrated set of computer software that allows users to interact with one or more databases and provides access to all of the data contained in the database.

Some examples of Database software programs are:

1. Microsoft Access.
2. Microsoft Excel.
3. Microsoft SQL Server.
4. MySQL.
5. Oracle RDBMS.

Why do we need Database?

The various reasons for which we require databases are:

• To manage large amount of data.
• To manage the accuracy.
• It provides ease of updating data.
• Databases provides security groups and permission to data.
• Databases assured of accuracy and consistency of data due to the built in integrity checks and access controls.

Some applications using database:

1. Facebook
2. Amazon
3. eBay
4. Microsoft Access
5. Wikipedia
6. YouTube
7. Airlines ticket systems
8. IRCTC
9. Flipkart
10. Snapdeal

Here are some tasks users can do with a DBMS:

1. Data Definition:
   • What it does: Imagine you’re setting up a library. You need to decide how to organize books, where to put them, and what categories to use.
   • DBMS equivalent: Users can create, modify, or remove structures in the database that organize data. These structures could be like shelves and categories in a library.
2. Data Updation:
   • What it does: In our library, books are added, taken out, or moved around. This is similar to adding, modifying, or deleting data in a database.
   • DBMS equivalent: Users can insert new data, change existing data, or delete data from the database.
3. Data Retrieval:
   • What it does: When you want a specific book from the library, you retrieve it based on its category or title. Similarly, in a database, you retrieve information based on certain criteria.
   • DBMS equivalent: Users can get the data they need from the database for different applications.
4. User Administration:
   • What it does: In a library, you might have different people who can borrow books, and you need to keep track of who has what. In a database with multiple users, you need to manage who can access what.
   • DBMS equivalent: Users can register and monitor other users, maintain the security of data, make sure data is consistent, handle multiple people trying to use the data at the same time, and keep an eye on how well the system is performing.

In simple terms, a DBMS is like a super-organized librarian that helps people efficiently manage and use information in a digital environment. Users can create, change, retrieve, and manage data with the help of a DBMS.

Advantages of DBMS

1. DBMS offers a variety of techniques to store & retrieve data
2. DBMS serves as an efficient handler to balance the needs of multiple applications using the same data
3. Uniform administration procedures for data
4. Application programmers never exposed to details of data representation and storage.
5. A DBMS uses various powerful functions to store and retrieve data efficiently.
6. Offers Data Integrity and Security
7. The DBMS implies integrity constraints to get a high level of protection against prohibited access to data.
8. A DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time
9. Reduced Application Development Time

Disadvantages of DBMS

1. Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.
2. Most database management systems are often complex systems, so the training for users to use the DBMS is required.
3. In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media
4. Use of the same program at a time by many users sometimes lead to the loss of some data.
5. DBMS can’t perform sophisticated calculations

There are twelve rules formulated by E.F. Codd for RDBMS in 1970. If an RDBMS satisfies all these twelve rules, then only full benefits of the relational database can bring out results.

Twelve rules of Codd:-

Information Representation

Guaranteed Access

Systematic Treatment of Null Values

Database Description Rule

Comprehensive Data Sub-Language

View Updating

High-Level Update, Insert, Delete

Physical Data Independence

Logical Data Independence

The Distribution Rule

Non-Subversion

Integrity Rule

1. Information Representation rule

In the relational model, all information should be explicitly and logically represented by entering the data values in the form of tables. The information such as table, view and column names should all be in table form. Data stored in data dictionary should also be in tabular form.

2. Guaranteed Access rule

This rule refers to the fact that the table can be taken as a storage structure and at the inter section of each column and row, there will necessarily be only one specific value of data item (or null). Every value of data item must be logically addressable by using a combination of table-name, primary-key- value and column-name.

 

3. Systematic Treatment of Null Values rule

In relational database management system null values should be supported for the representation of missing and inapplicable information. The database management system must have a consistent method for representing null values. For example, null values for numeric data must be distinct from zero or any other numeric value and for character data, it must be different from a string of blanks.

4. Database Description rule

The description of a database is stored and maintained in the form of tables. This allows the users with appropriate authority to query information using similar ways and using the same languages. This implies that a data dictionary should be present within the RDBMS that is constructed of tables and/or views that can be examined using the structured query language (SQL). 3.5.5 Comprehensive Data Sub-Language.

5. Comprehensive Data Sub-Language

The RDBMS must be completely manageable through its own extension of SQL. The SQL should support Data Definition, Views, Data Manipulation, Integrity Constraints and Transaction Boundary.

6. View Updating rule:

Any view that can be defined using combination of base tables, and theoretically updatable, must also be capable of being updated by the RDBMS.

7. High-Level Updates Insert, Delete rule

An RDBMS must do more than just be able to retrieve relational data sets. It must also be possible to insert, update and delete data as a relational set.

8. Physical Data Independence rule

Changes made to physical storage, representation or access methods do not require changes to be made to the application programs used to manipulate data in tables.

9. Logical Data Independence rule

Application programs should not be affected by the changes made to the base tables. Changes made to tables should not require changes to be made to application programs.

10. The Distribution rule

An RDBMS package must have distribution independence. Thus, RDBMS package must make it possible for the database to be distributed across multiple computers even though they are having heterogeneous platforms both for hardware and operating system. This is one of the most attractive aspects of the RDBMS. Database systems built on the relational framework are well suited for today’s Client/Server database design.

11. Non-Subversion rule

If the RDBMS supports facilities allowing application programs to operate on tables a row at a time, then an application program using this type of database access is prevented from bypassing entity-integrity or referential- integrity constraints that may be defined for the database.

12. Integrity rule

Integrity constraints specific to a particular relational database must be definable in SQL or some other data sub-language. These integrity constraints must be storable in the catalogue and not in the application programs.

SQL Function

Five Important aggregate functions are SUM, AVG, MIN, MAX and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.

SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.

AVG () gives the average of the given column.

MIN () gives the smallest figure in the given column.

MAX () gives the largest figure in the given column.

COUNT () gives the number of rows satisfying the conditions

1. SUM( )

The SUM function returns the total sum of a column. NULL values are not included in the calculation.

Syntax:

SELECT SUM (column) FROM table

For example:

SELECT SUM (RollNumber) FROM Student;

2. AVG( )

The AVG function returns the average value of a column in a selection. NULL values are not included in the calculation.

Syntax :

SELECT AVG (column) FROM table

For example:

SELECT AVG(RollNumber) FROM Student;

3. MIN( )

The MIN function returns the lowest value in a column. NULL values are not included in the calculation.

Syntax :

SELECT MIN(column) FROM table;

For example:

SELECT MIN(RollNumber) FROM Student;

4. MAX( )

The MAX function returns the highest value in a column. NULL values are not included in the calculation.

Syntax :

SELECT MAX(column) FROM table;

For example:

SELECT MAX(RollNumber) FROM Student;

5. COUNT( )

The keyword COUNT can be used together to count the number of distinct results.

Syntax :

SELECT COUNT (column) FROM table;

For example:

SELECT COUNT (RollNumber) FROM Student;