img
Question:
Published on: 29 May, 2024

Write short notes on:

  • Database Language
  • Metadata
  • B+ tree
Answer:

Database Language:-

The database is an intermediate link between the physical database, computer and the operating system and the users. To provide the various facilities to different types of users, a DBMS normally provides one or more spe­cialized programming languages called database languages.

DDL - Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

  • CREATE - to create objects in the database
  • ALTER - alters the structure of the database
  • DROP - delete objects from the database
  • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT - add comments to the data dictionary
  • RENAME - rename an object

DML - Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

  • SELECT - retrieve data from the a database
  • INSERT - insert data into a table
  • UPDATE - updates existing data within a table
  • DELETE - deletes all records from a table, the space for the records remain
  • MERGE - UPSERT operation (insert or update)
  • CALL - call a PL/SQL or Java subprogram
  • EXPLAIN PLAN - explain access path to data
  • LOCK TABLE - control concurrency

DCL - Data Control Language (DCL) statements. Some examples:

  • GRANT - gives user's access privileges to database
  • REVOKE - withdraw access privileges given with the GRANT command

TCL - Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT - save work done
  • SAVEPOINT - identify a point in a transaction to which you can later roll back
  • ROLLBACK - restore database to original since the last COMMIT
  • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

 

Metadata:-

Metadata is by definition "data about other data" of any type and sort in any media. It is used to facilitate the understanding, characteristics and management usage of data. For effective data management, the Metadata should include data that is coherent with the context of use.

Role: 
This Metadata is used for better access to the enormous amounts of data stored and managed by different companies. Metadata provides context for data. In data processing, for example, Metadata is definitional; it gives documentation of other data in the application or environment. The term "Metadata" should be used carefully since all data is about something and hence is "Metadata".

Importance: 
In Databases, Metadata defines data elements and attributes (Name, data type, size, etc), data could be registered about structures and records as well (Length, columns and fields). This is extremely helpful for the reliability of databases and their efficiency. In a library, for example, the data is the content of the titles, and the Metadata is about the title, the author, a description of the content, the physical location and the date of publication. In addition, it protects investment in data, helps the user in understanding data, enables discovery options, and limits Liability. All of these reasons make Metadata the backbone in understanding a DBMS.

Examples: Examples of Metadata include; Photograph: Date and time

      Camera Settings Like: Focal Length, aperture, exposure

      Web Pages: Descriptive Text, Dates, Keywords

 

B+ tree:-

Definition:

A B+ tree is a balanced tree in which every path from the root of the tree to a leaf is of the same length, and each non-leaf node of the tree has between [n/2] and [n] children, where n is fixed for a particular tree. It contains index pages and data pages. The capacity of a leaf has to be 50% or more. For example: if n = 4, then the key for each node is between 2 to 4. The index page will be 4 + 1 = 5.

B+-tree Structure:

A B+-tree is a generalization of a binary search tree (BST). The main difference is that nodes of a B+ tree will point to many children nodes rather than being limited to only two. Since the goal is to minimize disk accesses whenever we are trying to locate records, we want to make the height of the multi-way search tree as small as possible. This goal is achieved by having the tree branch in large amounts at each node.

A B+-tree of order m is a tree where each internal node contains up to m branches (children nodes) and thus store up to m-1 search key values -- in a BST, only one key value is needed since there are just two children nodes that an internal node can have. m is also known as the branching factor or the fan out of the tree.

  1. The B+-tree stores records (or pointers to actual records) only at the leaf nodes, which are all found at the same level in the tree, so the tree is always height balanced.
  2. All internal nodes, except the root, have between Ceiling (m/2) and children.
  3. The root is either a leaf or has at least two children.
  4. Internal nodes store search key values, and are used only as placeholders to guide the search. The number of search key values in each internal node is one less than the number of its non-empty children, and these keys partition the keys in the children in the fashion of a search tree. The keys are stored in non-decreasing order (i.e. sorted in lexicographical order).
  5. Depending on the size of a record as compared to the size of a key, a leaf node in a B+-tree of order m may store more or less than m records. Typically this is based on the size of a disk block, the size of a record pointer, etcetera. The leaf pages must store enough records to remain at least half full.
  6. The leaf nodes of a B+-tree are linked together to form a linked list. This is done so that the records can be retrieved sequentially without accessing the B+-tree index. This also supports fast processing of range-search queries.

Example of a B+ tree with four keys (n = 4) looks like this:

 

 

Random questions