RDBMS

Created Feb. 7, 2024 by Archana kalia

A  DBMS must provide appropriate languages and interfaces for each category of users to express database queries and updates. Database Languages are used to create and maintain database .

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

Data Definition Language

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, data dictionary and tables within databases.

  • To Create Schema objects---CREATE
  • To Alter Schema Objects ----ALTER
  • To Delete Schema Objects ----DELETE
  • To Rename Schema Objects ----RENAME

Data Manipulation Language

It is a language that provides aset of operations to support the basic datamanipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the database. The part of DML that involves data retrieval is called a query language.

  • To Remove rows from Table or Views--DELETE.
  • To Add New rows of data into table or views ---INSERT
  • To Retrieve data from one or more tables ---SELECT
  • To Change column values in existing rows of a table or views

Data Control Language

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 or it can also be revoke (taken back) it by using REVOKE command.

Grant and take away privileges and roles---Grant/Revoke.

INTANCES, SCHEMAS AND SUBSCHEMA in DBMS

The collection of information stored in the database at a particular moment is called an instance of the database. The overall design of the database is called the database schema.

The schema will remain the same while the values filled into it change from instant to instant.The data in the database at a particular moment of time is called a database state or snapshot, which is also called the current set of occurrences or instances in the database

A subschema is a subset of the schema and inherits the same property that a schema has. The plan (or scheme) for a view is often called subschema.

 Data Independence

Data Independence - Capacity to change the schema at one level of a database system  without having to change the schema at the next higher level.

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

There are two kinds of data independence:

  • Logical data independence
  •  Physical data independence

Logical data independence indicates that theconceptual schema can be changed without

affecting the existing external schemas.

Physical data independence indicates that thephysical storage structures or devices

could be changed without affecting conceptual schema.

Three Level Architecture

The Database Administrator (DBA) should be able to change the database storage structures without affecting the user's views.

 The DBA should be able to change the conceptual structure of the database without affecting all users.

External Level or View level- It is the users' view of the database. This level describes that part of the database that is relevant to each user.

Conceptual Level or Logical level-- This level describes what data is stored in the database and the relationships among the data. The middle level in the three level architecture is the conceptual level.

Internal level or Storage level--It is the physical representation of the database on the computer.The internal level is the one that concerns the way the data are physically stored on the hardware.

External/Conceptual Mapping: Each external schema is related to the conceptual schema by the external/conceptual mapping.

  Conceptual/Internal Mapping: Conceptual schema is related to the internal schema by the conceptual/internal mapping.


                                                 

Data base management system (DBMS) which follows all the 12 rule given by  Dr Edgar F. Codd  known as Relational data base management system (RDBMS)

According to Dr.Edgar .F. Codd rule are as follows

1 Information Rule :- This rule states that all information (data) ,which stored in database must be stored in table  formats,this information can be user data or meta -data.

2.Guaranteed Access rule :-   This rule states that every single data element (value) is guaranteed to be accessible logical with combination of table name and primary key .

3.Systematic Treatment of NULL value:- This rule states that NULL value in the database must be given systematic treatment i.e. primary key value never be NULL because NULL have several meaning that cause various anomaly occurs in data base

4.Active online catalog :-This rule states that the structure of whole database must be stored  in an online catalog i.e. Data Dictionary

5. Comprehensive data sub-language rule :-   This rule states that  database must have support for a language which has linear syntax which is capable of Data Definition ,Data Manipulation,Data contro

6.View Updating:  This rule states that all system should able to updates all views of database  without any anomaly in the system

7. High-level insert -Update and Delete rule:- This rule states that system must support high level insertion,updation ,deletion this must not be limited to a single row that it must support join operations to yield set of data

8. Physical data independence:   This rule states that application should not have any concern about ,how the data physically stored in the system as well as any change in physical structure must not have any impact on application 

9.Logical Data Independence:-     This rule states that logical data must be independent of its user's view as well as any change in logical data must not imply any change in the application .e.g. if two table are merged or one is split into two different table there should be no impact on the application

10.Integrity Independence:- This rule states that all integrity constraints can be independently modified without the any change in the application. this rule makes database independent of front-end application and its interface.

11.Distribution Independence:- This rule states that the end user must not be able to see that the data in distributed over various locations, end user have feel that data is located at one site only. 

12. Non-subversion rule:-  This rule states that if system has an interface that provides access to low level records ,this interface then must not be able to subvert the system and bypass security and integrity constraint

 

Return to top