The following are main features offered by DBMS. Apart from these features different database management systems may offer different features. For instance, Oracle is increasing being fine-tuned to be the database for Internet applications. This may not be found in other database management systems. These are the general features of database management systems. Each DBMS has its own way of implementing it. A DBMS may have more features the features discussed here and may also enhance these features.
Support for large amount of data
Each DBMS is designed to support large amount of data. They provide special ways and means to store and manipulate large amount of data. Companies are trying to store more and more amount of data. Some of this data will have to be online (available every time).
In most of the cases the amount of data that can be stored is not actually constrained by DBSM and instead constrained by the availability of the hardware. For example, Oracle can store terabytes of data.
Data sharing, concurrency and locking
DBSM also allows data to be shared by two or more users. The same data can be accessed by multiple users at the same time – data concurrency. However when same data is being manipulated at the same time by multiple users certain problems arise. To avoid these problems, DBMS locks data that is being manipulated to avoid two users from modifying the same data at the same time.
The locking mechanism is transparent and automatic. Neither we have to inform to DBMS about locking nor we need to know how and when DBMS is locking the data. However, as a programmer, if we can know intricacies of locking mechanism used by DBMS, we will be better programmers.
While DBMS allowing data to be shared, it also ensures that data in only accessed by authorized users. DBMS provides features needed to implement security at the enterprise level. By default, the data of a user cannot be accessed by other users unless the owner gives explicit permissions to other users to do so.
Maintaining integrity of the data is an import process. If data loses integrity, it becomes unusable and garbage. DBMS provides means to implement rules to maintain integrity of the data. Once we specify which rules are to be implemented, then DBMS can make sure that these rules are implemented always.
Three integrity rules (discussed later in this chapter) – domain, entity and referential are always supported by DBMS.
Fault tolerance and recovery
DBMS provides great deal of fault tolerance. They continue to run in spite of errors, if possible, allowing users to rectify the mistake in the mean time.
DBSM also allows recovery in the event of failure. For instance, if data on the disk is completely lost due to disk failure then also data can be recovered to the point of failure if proper back up of the data is available.
Support for Languages
DBMS supports a data access and manipulation language. The most widely used data access language for RDBMS (relational database management systems) is SQL. We will discuss more about RDBMS and SQL later in this chapter.
DBMS implementation of SQL will be compliant with SQL standards set by ANSI.
Apart from supporting a non-procedural language like SQL to access and manipulate data DBMS now a days also provides a procedural language for data processing. Oracle supports PL/SQL and SQL Server provides T-SQL.
Entity and Attribute
An entity is any object that is stored in the database. Each entity is associated with a collection of attributes. For example, if you take a data of a training institute, student is an entity as we store information about each student in the database. Each student is associated with certain values such as roll number, name, course etc., which are called as attributes of the entity.
There will be relationship among entities. The relationship between entities may be one-to-one, one-to-many or many-to-many.
If you take entities student, batch and subject, the following are the possible relationships.
There is one-to-one relationship between batch and subject. One batch is associated with only one subject.
Three is one-to-many relationship between batch and student entities. One batch may contain many students.
There is many-to-many relationship between student and subject entities. A single student may take many subjects and a single subject may be taken by multiple students.
There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next...
1.Write a program to print the following format WELCOME TO PL/SQL PROGRAMMING BEGIN DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL PROGRAMMING...
Step1) Write the PL/SQL code in the note pad and save it as with extension name .sql ex: "test.sql" DECLARE x NUMBER; BEGI...
DECLARE emp_name_c CHAR(32); emp_name_v VARCHAR(32); BEGIN emp_name_c := 'sateesh.bagadhi'; emp_name_v := 'sateesh.ba...