Database Management System

A database management system (DBMS) is a collection of computer programs, designed to store, modify, and extract information from a database. There are many different types of DBMS, ranging from small systems that run on personal computers to huge systems that run on mainframes.

           Technically speaking, it is a software system that uses a standard method of cataloging, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. Some of the common software packages that provide DBMS functionality include: Ingres, Microsoft Access, MongoDB, MS-SQL Server, MS FoxPro, MySQL, NoSQL, Oracle, Postgres and SQLite.
           From a technical standpoint, DBMS can differ widely. The terms relational, network, flat, and hierarchical all refer to the way a DBMS organizes information internally. The internal organization can affect how quickly and flexibly you can extract information.

• Hierarchical DBMS : A DBMS is said to be hierarchical if the relationships among data in the database are established in such a way that one data item is present as the subordinate of another one or a sub unit.
• Network DBMS : A DBMS is said to be a Network DBMS if the relationships among data in the database are of type many-to-many. The relationships among many-to-many appears in the form of a network. Thus the structure of a network database is extremely complicated because of these many-to-many relationships in which one record can be used as a key of the entire database. A network database is structured in the form of a graph that is also a data structure.
• Relational DBMS : A DBMS is said to be a Relational DBMS or RDBMS if the database relationships are treated in the form of a table. There are three keys on relational DBMS: relation, domain and attributes.
• Object-oriented DBMS : Object-oriented databases use small, reusable chunks of software called objects. The objects themselves are stored in the object-oriented database.

Major features of DBMS:

• Data Availability : One of the principle advantages is that the same business data can be made available to different employees anytime anywhere.
• Minimized Redundancy : Information in DBMS is kept concise and appears just once. This capability reduces data redundancy.
• Better data integration : Wider access to well-managed data promotes an integrated view of the organization’s operations and a clearer view of the big picture. It becomes much easier to see how actions in one segment of the company affect other segments.
• Minimized data inconsistency : Data inconsistency exists when different versions of the same data appear in different places. The probability of data inconsistency is greatly reduced in a properly designed database.
• Improved Data Security : DBMS allows user authentication through passwords. It also provides a system of permissions to restrict user access to certain data resources.
• Increased end-user productivity : The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy.

SQL Commands

• Create a table
• Display the structure of a table
• Add columns to a table
• Alter a table to make a primary key
• Drop a column from a table
• Alter a table to change the size of a column
• Add a constraint
• Insert data in to a table
• Update data in the table
• Convert all first characters of all data in a column to upper case
• Display contents of a table
• Sort the table in ascending order
• Sort the table in descending order
• Insert a new row into the table
• Delete a tuple from the table
• Use of UNION
• Use of UNION ALL
• Use of INTERSECT
• Use of MINUS
• Use of Equijoin
• Use of Left Outer Join
• Use of Right Outer Join

Views and Sub-Queries

PL/SQL Programs

• PL/SQL program to print the Factorial of a number
• PL/SQL program to print the n^th Fibonacci number
• PL/SQL program to evaluate Sine series
• PL/SQL program to convert a decimal number to binary
• PL/SQL program to check whether the given text is a Palindrome
• PL/SQL program to generate the water bill of the customer
• PL/SQL program to update and display the bank account details of the customer
• PL/SQL program to update the details of items in a stock

PL/SQL Functions

• PL/SQL function to find the factorial of a number
• PL/SQL function to find the quantity in stock for a given item number
• PL/SQL function to determine if an item exists in stock

PL/SQL Procedures

• PL/SQL procedure to display the name and salary of an employee
• PL/SQL procedure to add and display the remarks of a student according to his marks
• PL/SQL procedure to increase the salary for a given employee
• PL/SQL procedure to deposit or withdraw from an account
• PL/SQL procedure to display specific details of an employee
• PL/SQL procedure to update the transaction details                
• PL/SQL procedure to update the details of items in a stock

Triggers

• Trigger which will not permit update on Saturdays and during non-office hours
• Trigger which will create an audit system for clients during update, deletion or insertion
• Trigger which will restrict the salary of employees in a particular department
• Trigger which will restrict the salary of an employee when the employee details are updated.
• Trigger which will invoked before updating the stock details
• Trigger which will simulate an electronic voting system