What are the three levels of data abstraction?
Following are three levels of data abstraction:
Physical level: It is the lowest level of abstraction. It describes how data are stored.
Logical level: It is the next higher level of abstraction. It describes what data are stored in the database and what the relationship among those data is.
View level: It is the highest level of data abstraction. It describes only part of the entire database.
For example– User interacts with the system using the GUI and fill the required details, but the user doesn’t have any idea how the data is being used. So, the abstraction level is entirely high in VIEW LEVEL.
Then, the next level is for PROGRAMMERS as in this level the fields and records are visible and the programmers have the knowledge of this layer. So, the level of abstraction here is a little low in VIEW LEVEL.
And lastly, physical level in which storage blocks are described.
What are the integrity rules in DBMS?
Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. Those set of integrity is known as the integrity rules.
There are two integrity rules in DBMS:
Entity Integrity : It specifies that “Primary key cannot have a NULL value.”
Referential Integrity: It specifies that “Foreign Key can be either a NULL value or should be the Primary Key value of other relation
What is a checkpoint in DBMS?
The Checkpoint is a type of mechanism where all the previous logs are removed from the system and permanently stored in the storage disk.
There are two ways which can help the DBMS in recovering and maintaining the ACID properties, and they are- maintaining the log of each transaction and maintaining shadow pages. So, when it comes to log based recovery system, checkpoints come into existence. Checkpoints are those points to which the database engine can recover after a crash as a specified minimal point from where the transaction log record can be used to recover all the committed data up to the point of the crash.
What do you understand by query optimization?
The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost. The concept of query optimization came into the frame when there were a number of methods, and algorithms existed for the same task then the question arose that which one is more efficient and the process of determining the efficient way is known as query optimization.
There are many benefits of query optimization:
- It reduces the time and space complexity.
- More queries can be performed as due to optimization every query comparatively takes less time.
- User satisfaction as it will provide output fast
What is 1NF?
1NF is the First Normal Form. It is the simplest type of normalization that you can implement in a database. The primary objectives of 1NF are to:
- Every column must have atomic (single value)
- To Remove duplicate columns from the same table
- Create separate tables for each group of related data and identify each row with a unique column
What is DBMS?
DBMS is a collection of programs that facilitates users to create and maintain a database. In other words, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system. Using DBMS, we can overcome many problems such as- data redundancy, data inconsistency, easy access, more organized and understandable, and so on. There is the name of some popular Database Management System- MySQL, Oracle, SQL Server, Amazon simple DB (Cloud-based), etc.
What is 2NF?
2NF is the Second Normal Form. A table is said to be 2NF if it follows the following conditions:
- The table is in 1NF, i.e., firstly it is necessary that the table should follow the rules of 1NF.
- Every non-prime attribute is fully functionally dependent on the primary key, i.e., every non-key attribute should be dependent on the primary key in such a way that if any key element is deleted, then even the non_key element will still be saved in the database.
Define a Relation Schema and a Relation.
A Relation Schema is specified as a set of attributes. It is also known as table schema. It defines what the name of the table is. Relation schema is known as the blueprint with the help of which we can explain that how the data is organized into tables. This blueprint contains no data.
A relation is specified as a set of tuples. A relation is the set of related attributes with identifying key attributes
See this example:
Let r be the relation which contains set tuples (t1, t2, t3, …, tn). Each tuple is an ordered list of n-values t=(v1,v2, …., vn).
What is an Entity type?
An entity type is specified as a collection of entities, having the same attributes. Entity type typically corresponds to one or several related tables in the database. A characteristic or trait which defines or uniquely identifies the entity is called entity type.
For example, a student has student_id, department, and course as its characteristics.
What is DDL (Data Definition Language)?
Data Definition Language (DDL) is a standard for commands which defines the different structures in a database. Most commonly DDL statements are CREATE, ALTER, and DROP. These commands are used for updating data into the database.