Database Management System Basic Concepts By Sumit Singh

Database Languages

Database languages are used to create and maintain database on computer. There is large number of database languages like Oracle, MS- Access, dbase, FoxPro, etc.

SQL statements commonly used in Oracle and MSA Access can be categorized as data definition languages (DDL), data control language (DCL), and data manipulation language (DML).

1. Data Definition Languages (DDL): - It is a language that allows the users to define data and their relationship to other types of data. It is mainly used to create files, databases, dictionary and tables within databases.

It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and authorization information for each table and physical storage structure of each table on the disk.

S.No.	Need And Usage	The SQL DDL Statement
1	Create schema objects	CREATE
2	Alter schema objects	ALTER
3	Delete schema objects	DROP
4	Rename schema objects	RENAME

2. Data Manipulation language (DML): - It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the table. The part of DML that involves data retrieval is called a query language.

The following table gives an overview about the usage of DML statements: -

S.No.	Need And Usage	The SQL DDL Statement
1	Remove rows from tables or views	DELETE
2	Add new rows of data into table or view	INSERT
3	Retrieve data from one or more tables	SELECT
4	Change columns values in existing rows of a table or view	UPDATE

3. Data Control Language (DCL): - DCL statements control access to data and the database using statements such as GRANT and REVOKE. A privilege can either be granted to a user with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE,EXECUTE, INSERT, INDEX etc. in addition to granting of privileges , you can also revoke it by using REVOKE command.

The following tables give an overview about the usage of DCL statements in SQL: -

S.No	Need And Usage	The SQL DDL Statement
1	Grant and take away privileges and roles	GRANT and REVOKE
2	Add a comment to the data dictionary	COMMENT

Data Base Management System

DBMS A data base management system is the software system that allows users to define, create and maintain a data and provides controlled access to the data.

A database management system (DBMS) is basically a collection of programs that enables users to store, modify, and extract information from a database as per the requirements. DBMS is an intermediate layer between programs and the data. Programs access the DBMS, which then accesses the data. There are different types of DBMS ranging from small systems that run on personal computers to huge systems that run on mainframes.

The following are examples are main examples of database applications:

• Computerized library systems
• Automated teller machines
• Flight reservation systems
• Computerized parts inventory systems

A database management system is a piece of software that provides services for accessing a database, while maintain all the required features of the data. Commercially available Database management systems in the market are dbase, FoxPro, IMS and Oracle.
These systems allow users to create, update, and extract information from their database. Compared to a manual filing system, the biggest advantages to a computerized database system are speed, accuracy and accessibility.

Components of the DBMS Environment

There are five major components in the DBMS environment

- Hardware
- Software
- Data
- Users
- Procedures

1. Hardware: - The hardware is the actual computer system used for keeping and accessing the database. Conventional DBMS hardware consists of secondary devices, usually hard disks, on which the database physically resides, together with the associated input-output devices controllers and so forth. Database run on range of machines, from Micro-computer to large mainframes. Other hardware issues for a DBMS includes database machines, which is hardware designed specially to support a database system.

2. Software: - The software is the actual DBMS. Between the physical databases itself and the users of the system of software, usually called the Database Management System or DBMS. All requests from users for access to the database are handled by the DBMS. One general function provided by the DBMS is thus the shielding of database from complex hardware-level detail.
The DBMS allows the users to communicate with the database. In a sense, it is the mediator between the database and the users. The DBMS controls the access and helps to maintain the consistency of the data. Utilities are usually included as part of the DBMS. Some of the most common utilities are writers, application development tools and other design aids.

3. Data: - It is the most important component of DBMS environment from the end users point of view. The database contains operational data and the meta-data, the “data about data”.
The database should contain all the data needed by the organization. In of the major features of databases is that the actual data are separated from the programs that use the data. A database should always be designed, built and populated for a particular audience and for a specific purpose.

4. Users: - there are a number of users who can access or retrieve data on demand using the application capacities. The users of a database system can be classified in the following groups, depending on their degrees of expertise or the mode of their interactions with DBMS. The users can be:

• Naive Users
• Online Users
• Application Programmers
• Sophisticated users
• DBA

5. Procedures: - procedures refer to the instructions and rules that govern the design and use of the database. The users of the system and the staff that manage the database require documented procedures on how to use or run the system.
These may consist of instruction on how to:

- Log onto the DBMS
- Use a particular DBMS facility or application program
- Start and stop the DBMS
- Make backup copies of the database
- Handle hardware or software failures
- Change the structure of a table, reorganize the database across multiple disks, improve performance, or archive data to secondary storage.

Standardization of DBMS -- Three Level Architecture of DBMS

An early proposal for a standard terminology and general architecture database a system was produced in 1971 by the DBTG (Data Base Task Group) appointed by the Conference on data Systems and Languages. The DBTG recognized the need for a two level approach with a system view called the schema and user view called subschema. The American National Standard Institute terminology and architecture in 1975.ANSI-SPARC recognized the need for a three level approach with a system catalog.

There are following three levels or layers of DBMS architecture:

1. Extenal Level

2. Conceptual Level

3. Internal Level

1. External Level: - External Level is described by a schema i.e. it consists of definition of logical records and relationship in the external view. It also contains the method of deriving the objects in the external view from the objects in the conceptual view.

2. Conceptual Level: - Conceptual Level represents the entire database. Conceptual schema describes the records and relationship included in the Conceptual view. It also contains the method of deriving the objects in the conceptual view from the objects in the internal view.

3. Internal Level: - Internal level indicates hoe the data will be stored and described the data structures and access method to be used by the database. It contains the definition of stored record and method of representing the data fields and access aid used.

A mapping between external and conceptual views gives the correspondence among the records and relation ship of the conceptual and external view. The external view is the abstraction of conceptual view which in turns is the abstraction of internal view. It describes the contents of the database as perceived by the user or application program of that view.

A mapping between conceptual records from the physical database.

Data Independence - Achievement of Layered Architecture of DBMS

A major objective for three- level architecture is to provide data independence, which means that upper levels are unaffected by changes in the lower levels.

1. Logical data independence

2. Physical data independence

1. Logical data independence: - Logical data independence indicates that the conceptual schema can be changed without affecting the existing schemas. The change would be absorbed by the mapping between the external and conceptual levels. Logical data independence also insulates application programs from operations such as combining two records in one or splitting an existing record in two or more records. This would require a change in the external/conceptual mapping so to leave the external view unchanged.

2. Physical data independence: - Physical data independence indicates that physical storage structures or devices could be changed without affecting conceptual schema. The change would be absorbed by the mapping between conceptual and internal levels. Physical data independence is achieved by the presence of the internal level of the database and the mapping or transformation from the conceptual level of the database to the internal level. Conceptual level to internal level mapping, therefore provides a means to go from the conceptual view to the internal view and hence to the stored data in the database (physical records).

If there is a need to change the file organization or the type of physical device used as a result of the growth in the database or new technology, a change is required in the conceptual/internal mapping between he conceptual and internal; levels. This change is necessary to maintain the conceptual level invariant. The physical data independence criterion to maintain the conceptual level does note specify storage structures or the access methods used to retrieve the data from the physical storage medium. Making the conceptual schema physical physically data independent means that the external schema, which is d3fined on the conceptual schema, is in turn physically data independent.

The logical data independence is difficult to achieve than physical data independence as it requires the flexibility in the design of the database and programmer has to foresee the future requirements or modifications in then design.