DBMS stands for Database Management system. It is system software for creating and managing databases system. It is manipulates the data itself, the data format, field names, record structure and file structure. It is mainly used for ability to store related data across multiple tables. Since there are so many database management systems available, it is important for there to be a way for them to communicate with each other.
Their main database organizations are:
Relational DBMS
Flat Database
Hierarchical Database
Object oriented Database
A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose. DBMS is a collection of programs that enables user to create and maintain a database. In other words, it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications. Example: MySQL, Oracle, SQLs server, dBASE, Foxpro, DB2, etc. There are four types of database languages: DMS language is that enable user to access or manipulate data as organized by appropriate data model. Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data. Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data. Normalization is a process of analyzing the given relation schemas according to their functional dependencies. It is used to minimize redundancy and also minimize insertion, deletion and update distractions. Advantages of normalizing database are as follows: Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data. They are three levels of abstraction: Physical level: The lowest level of abstraction describes how data are stored. Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data. View level: The highest level of abstraction describes only part of entire database. Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API, Structured Query Language (SQL). Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through. There are two Integrity rules. Data independence means that “the application is independent of the storage structure and access strategy of data”. In other words, the ability to modify the schema definition in one level should not affect the schema definition in the next higher level. Two types of Data Independence: A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary. Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence. Extension: It is the number of tuples present in a table at any instance. This is time dependent. Intension: It is a constant value that gives the name, structure of table and the constraints laid on it. Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing. Enterprise management is used for managing the information whose framework is known as Data Warehousing. Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting. Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that: A collection of conceptual tools for describing data, data relationships data semantics and constraints. This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes. Define B-trees. A data structure in the form of tree which stores sorted data and searches, insertions, sequential access and deletions are allowed in logarithmic time. Fragmentation can be defined as a database feature of server that promotes control on data which is stored at table level by the user. Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning. Splitting of one table which is large into smaller database entities logically is called database partitioning. Its benefits are: Atomicity: It’s an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS. Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships. A-Atomicity C-Consistency I-Isolation D-Durability For More: A query is a statement requesting the retrieval of information. The portion of a DML that involves information retrieval is called a query language. A data model is a conceptual representation of the data structures that are required by a database. Types of Data models: Record based Data model Object based data model Physical data model An entity-relationship diagram (or entity-relationship model) is a visual representation of data which is represented as entities, attributes and relationships are set between entities. DDL stands for Data Definition Language. It is used to specify the database schema by the definitions. DDL Commands: DML stands for Data Manipulation Language. It is a language used to access or manipulate the data in the database. DML Commands: DCL stands for Data Control language. It is used to access the stored or saved data. It is mainly used for revoking and granting user access on a database. DCL Commands: SYSTEM: It is used to creating a session, table, etc. are all types of system privilege. OBJECT: It is used to any command or query to work on tables comes under object privilege. DCL is used to define two commands. GRANT: It allows only specific user to perform specific tasks. REVOKE: It allows the cancellation of the previously granted permissions or the denied one. TCL stands for Transaction control Language. It is used to manage the transactions in the database. It is used to run the changes made by DML statements. TCL Commands: COMMIT: It is used to save the transaction in the database. ROLLBACK: It is used to restore the database to the last committed state. SAVEPOINT: it is used to store the transaction temporarily such that one can easily rollback to the transaction when needed. SET TRANSACTION: It is used to changes the transaction options like isolation level and what rollback segment to use. Query decomposition is the first phase of query processing. Query decomposition maps a distributed calculus query into an algebraic query on global relations. The techniques used at this layer are those of the centralized DBMS. since relation distribution is not yet considered at this point. The primary targets of query decomposition are to transform a high-level query into a relational algebra query and to check that the query is syntactically and semantically correct. The typical stages of query decomposition are analysis, normalization, semantic analysis, simplification, and query restructuring. Data Dictionary can be defined as a DBMS component which stores the definition of characteristics of data and relationships. This “data about data” are labeled as metadata. Data Dictionary provides the DBMS with its self-describing characteristic. Data Replication is a process in which data is copied to different locations, to one site or node, either computers or servers, in order to surge the improvement of the data. 5 types of Data Replications are: It is a normal form used in database normalization which needs particularly that the database contains no restrictions other than domain constraints and key constraints. It is a non-procedural language. It is used for selecting those tuples that satisfy the given condition. Precedence graph is also known as serialization graph or conflict graph, It is used for testing Conflict Serializability of a schedule in the condition that forms the setting of concurrency control in databases. Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly. Functional dependency is a set of constraints between two attributes in a relation. It says that if two tuples have same values for attributes A1, A2… An, then those two tuples must have to have same values for attributes B1, B2, …, Bn. It is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-hand side attributes determine the values of attributes on the right-hand side. 4 Types of Functional Dependencies: 1NF: As per the rule of first normal form, A table is in first normal form (1NF) if and only if all columns contain only atomic values, that is, each column can have only one value for each row in the table. 2NF: A table is in second normal form (2NF) if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. 3NF: A table is in third normal form (3NF) if and only if for every nontrivial functional dependency X->A, where X and A are either simple or composite attributes, one of two conditions must hold. BSNF: BSNF stands for Boyce and Codd Normal Form. It is a higher version of the 3NF. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied: R(table) must be in 3rd Normal Form and, for each functional dependency ( X → Y ), X should be a super Key. 4NF: A table is said to be in the Fourth Normal Form(4NF) when, it is in the Boyce-Codd Normal Form.And, it doesn’t have Multi-Valued Dependency. 5NF: 5NF is also known as project-join normal form (PJ/NF). A table is said to be in the fifth Normal Form(5NF) when, It is in the Fourth Normal Form (4NF). And, it doesn’t have no join dependency and also the joining must be lossless. Serializability is the concept in a transaction that helps to identify which non-serial schedule is correct and will maintain the database consistency. It relates to the isolation property of transaction in the database. It is the concurrency scheme where the execution of concurrent transactions is equivalent to the transactions which execute serially. HAVING is used in select statement for an aggerated function or to specify the condition of a group. WHERE clause is used before grouping. The WHERE clause doesn’t contain aggerated functions. Foreign key is a key of one table, which points to the primary key in second table. It has a relationship with primary key in another table. When primary key is very large and complex, then Artificial keys are used. In DBMS,This rule is a concurrency control mechanism that is implemented to maintain and manage the concurrent transactions and processes in the database management system. It is an enhanced version of the timestamp ordering protocol that is useful for the concurrency control process. ACID Properties of Transaction: A-Atomic: Process all of a transaction or none of it; transaction cannot be further subdivided (like an atom) C-Consistent: Data on all systems reflects the same state I-Isolated: Transactions do not interact/interfere with one another; transactions act as if they are independent D-Durable: Effects of a completed transaction are persistent What is database?
What is DBMS?
What are main users of DBMS?
What are the advantages of DBMS?
What are the important components of DBMS?
What are the uses of DBMS?
How many types of database languages are?
What is DML (Data Manipulation Language)?
What is normalization?
What are the advantages of normalizing a database?
What is Denormalization?
Describe the levels of data abstraction?
What is RDBMS?
What is a database transaction?
Define the integrity rules?
What is Data Independence?
What is a view? How it is related to data independence?
What is extension and intension?
What is Data Warehousing?
What do you mean by Index hunting?
How does Index hunting help in improving query performance?
What is Data Model?
What is Object Oriented model?
Can you define Fragmentation.
What is Database partitioning?
What is the importance of partitioning?
What is Atomicity and Aggregation?
What are ACID Properties?
What is Columnar DBMSs?
What is a query?
What is Data Model?
What is an ER diagram in DBMS?
Can you explain DDL? And Commands?
Can you explain DML? And Commands?
Can you explain DCL? And Commands?
Can you explain TCL? And Commands?
What is Query decomposition in DBMS?
What is a Data Dictionary in DBMS?
What is Data Replication?
What are the types of data Replications?
What is A Domain Key normal form?
What is Tuple relational calculus in DBMS?
What is Precedence Graph?
What is a Normalization in DBMS?
What is the functional dependency? And types?
What are the commonly used normal forms? And Explain?
What is the Serializability in DBMS?
What are uses of HAVING and WHERE clause?
List the keys in DBMS?
What is Foreign Key?
When to use Artificial keys?
What is Thomas write Rule in DBMS?
What are the ACID Properties of Transaction?
Related posts:
- CouchDB Interview Questions and Answers Apache CouchDB is an open source database and NoSQL or...
- Database Interview Questions and Answers Database Interview Questions and Answers for beginners Database is a...
- DB2 Interview Questions and Answers Database 2 (DB2) is a Relational database management system developed...
- MongoDB Interview Questions and Answers MongoDB Interview Questions :MongoDB is a free and open source...
- PL/SQL Interview Questions and Answers PL-SQL stands for Procedural Language Extensions to SQL (Structured Query...
- SQL Server 2008 Interview Questions and Answers Top SQL Server 2008 Interview Questions and Answers: Below, we...
- SQL Server Interview Questions and Answers Top SQL Server Interview Questions and Answers: Below, we have...
- SQLite Interview Questions and Answers SQLite is a software library for relational database management system.SQLite...