- Database Management Systems (DBMS) are software systems used to store, retrieve, and run queries on data.
- A DBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database.
- In DbMS 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.
- Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.
2. What is a database?
- Database is an organized collection of data stored and accessed electronically from a computer system.
- Where databases are more complex they are often developed using formal design and modeling techniques.
- Database mostly consists of the objects (tables), and tables include of the records and fields.
- Fields are the basic units of data storage, which contain the information about a particular aspect or attribute of the entity described by the database.
- DBMS is used for extraction of data from the database in the form of the queries.
The collection of database and DBMS software together is known as a database system.
3. What are the advantages of DBMS?
- Improved data sharing
- Improved data security
- Improved data access
- Improved decision making
- Increased end-user productivity
- Redundancy control
- Restriction for unauthorized access
- Provides multiple user interfaces
- Provides backup and recovery
- Enforces integrity constraints
- Ensure data consistency
- Easy data extraction and data processing due to the use of queries
4. What is a checkpoint in DBMS? When does checkpoint occur 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.
A Checkpoint is the means in which the SQL Server Database Engine guarantees the databases consistency, where no data will be lost when the system is crashed as the dirty pages that are located in the memory and resulted from committed transaction will be redone although they are not written to the database files yet.
5. What do you mean by transparent DBMS?
The transparent DBMS is a type of DBMS which keeps its physical
structure hidden from users. Physical structure or physical storage
structure implies to the memory manager of the DBMS, and it describes
how the data stored on disk.
6. What are the unary operations in Relational Algebra?
PROJECTION and SELECTION are the unary operations in relational algebra. Unary operations are those operations which use single operands. Unary operations are SELECTION, PROJECTION, and RENAME.
7. What is RDBMS?
RDBMS stands for Relational Database Management Systems. RDBMS is a type of DataBase Management System that stores data in the form of relations (tables). Relational databases are powerful, so they require few assumptions about how data is related or how, it will be extracted from the database. RDBMS is the system that enables you to perform different operations such as- update, insert, delete, manipulate and administer a relational database with minimal difficulties.
Examples of the most popular RDBMS are MYSQL, Oracle, IBM DB2, and Microsoft SQL Server database
8. How many types of database languages are?
There are four types of database languages:
- Data Definition Language (DDL) DDL defines the statements to implement the database schema. e.g., CREATE, ALTER, DROP, TRUNCATE, RENAME, etc. All these commands are used for updating the data that?s why they are known as Data Definition Language.
- Data Manipulation Language (DML) Data Manipulation Language has a set of statements that allows users to access and manipulate the data in the database. e.g., SELECT, UPDATE, INSERT, DELETE, etc. These commands are used for the manipulation of already updated data that's why they are the part of Data Manipulation Language.
- DATA Control Language (DCL) e.g., GRANT and REVOKE. These commands are used for giving and removing the user access on the database. So, they are the part of Data Control Language.
- Transaction Control Language (TCL) e.g., COMMIT, ROLLBACK, and SAVEPOINT. These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML.
9. What do you understand by Data Model?
The Data model is specified as a collection of conceptual tools for describing data, data relationships, data semantics and constraints. These models are used to describe the relationship between the entities and their attributes.
There is the number of data models:
- Hierarchical data model
- network model
- relational model
- Entity-Relationship model and so on.
10. 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).
11. What is a degree of Relation?
The degree of relation is a number of attribute of its relation schema. A degree of relation is also known as Cardinality it is defined as the number of occurrence of one entity which is connected to the number of occurrence of other entity.
12. What is the Relationship?
The Relationship is defined as an association among two or more entities. There are three type of relationships in DBMS-
One-To-One: Here one record of any object can be related to one record of another object.
One-To-Many (many-to-one): Here one record of any object can be related to many records of other object and vice versa.
Many-to-many: Here more than one records of an object can be related to n number of records of another object
13. What are the disadvantages of file processing systems?
· Data redundancy & inconsistency.
· Difficult in accessing data.
· Data isolation.
· Data integrity.
· Concurrent access is not possible.
· Security Problems.
14. What is data abstraction in DBMS?
Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction with the database.
15. 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.
16. What is Relational Algebra?
Relational Algebra is a Procedural Query Language which contains a set of operations that take one or two relations as input and produce a new relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it is similar to the algebra which operates on the number.
There are few fundamental operations of relational algebra:
- select
- project
- set difference
- union
- rename,etc.
17. 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.
18. What is normalization?
Normalization is a process of analysing the given relation schemas according to their functional dependencies. It is used to minimize redundancy and also used to minimize insertion, deletion and update distractions. Normalization is considered as an essential process as it is used to avoid data redundancy, insertion anomaly, updation anomaly, deletion anomaly.
There most commonly used normal forms are:
- First Normal Form(1NF)
- Second Normal Form(2NF)
- Third Normal Form(3NF)
- Boyce & Codd Normal Form(BCNF)
19. What is Denormalization?
Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data. Denormalization is a part of database optimization technique. This process is used to avoid the use of complex and costly join
20. What is functional Dependency?
The functional dependency is known as database dependency and defines as the relationship which occurs when one attribute in a relation uniquely determines another attribute. It is written as A->B which means B is functionally dependent on A.
21. What is the E-R model?
E-R model is a short name for the Entity-Relationship model. This model is based on the real world. It contains necessary objects (known as entities) and the relationship among these objects. Here the primary objects are the entity, attribute of that entity, relationship set, an attribute of that relationship set can be mapped in the form of E-R diagram.
In E-R diagram, entities are represented by rectangles, relationships are represented by diamonds, attributes are the characteristics of entities and represented by ellipses, and data flow is represented through a straight line.
The Entity is a set of attributes in a database. An entity can be a real-world object which physically exists in this world.
22. What is Weak Entity set?
An entity set that doesn't have sufficient attributes to form a primary key is referred to as a weak entity set. The member of a weak entity set is known as a subordinate entity. Weak entity set does not have a primary key, but we need a mean to differentiate among all those entries in the entity set that depend on one particular strong entity set.
23. What is an attribute?
An attribute refers to a database component. It is used to describe the property of an entity. An attribute can be defined as the characteristics of the entity. Entities can be uniquely identified using the attributes. Attributes represent the instances in the row of the database.
For example: If a student is an entity in the table then age will be the attribute of that student.
24. 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
25. What is System R? How many of its two major subsystems?
System R was designed and developed from 1974 to 1979 at IBM San Jose Research Centre. System R is the first implementation of SQL, which is the standard relational data query language, and it was also the first to demonstrate that RDBMS could provide better transaction processing performance. It is a prototype which is formed to show that it is possible to build a Relational System that can be used in a real-life environment to solve real-life problems.
Following are two major subsystems of System R:
- Research Storage
- System Relational Data System
