Once the design of database is completed and a particular DBMS is chosen to implement a particular database project, the first thing we have to do is to specify a particular internal and conceptual schemas for the database and any mappings between the two. In many DBMS where there is no strict separation of levels are maintained through data definition language(DDL), is used by the database designers and by DBA to define both schemas. The DBMSs have a DDL compiler whose main function is to process data definition language statements in order to get the descriptions of the schema usually constructs and stores the schema description in that particular DBMS catalog.
In DBMSs where there is a clear separation is maintained between internal levels and conceptual level, the DDL is used to write only the conceptual schema. Another commonly used language is the storage definition language(SDL), is used to specify internal schema. The mapping between two schemas may be specified in either one of these languages. For a true three-schema architecture, we would need a third language, the view definition language (VDL), to specify user views and their mappings to the conceptual schema, but in the most DBMSs the data definition language is used to define both external and conceptual schemas.
Once the database schemas are compiled and populated with data, users must have some means to manipulate the database. Typical manipulation includes retrieval, insertion, deletion and modification of data. The DBMS provides a set of operations or languages called data manipulation language (DML) for these purposes.
In current DBMS the preceding types of languages usually used to define and modify the datas are not usually considered distinct; but as a comprehensive integrated language is used that includes conceptual schemas defining the constructor, view definition and data manipulation. Storage definition are particularly kept different, since it is used to fine tuning the physically storage structures the performance of the database system, which is usually done by the DBA staff.
DMLs are of two type. To specify complex database operation in concise manner, non procedural DML is used. Many DBMSs allow high level data manipulation statements either to be entered interactively from a display monitor or terminal or to be embedded in a general purpose programming language. But in latter case, data manipulation statements must be identified from within the program so the extraction is simplified by a precompiler and processed by the DBMS. A low level DML must be embedded in a general purpose programming language. This type of data manipulation language usually retrieves individual records or objects from the database and process each separately. Hence it needs to use constructs of programming language , example looping, to process and retrieve each record from a set of records. Low level DML are known as record at a time DML because of this property. Top level data manipulation languages , such as SQL can specify and retrieve so much datas in a single data manipulation language statement and are hence called set-at-a-time or set-oriented DMLs.