In this Post, You wil learn about 12th class dbms Questions And Answer with Easy Understandable for Students. “A Database Management System (DBMS) is a software system that enables users to define, create, maintain, and control access to databases.”
12th Class DBMS Questions Answers
- What is Data
Ans: Data is a collection of raw facts which have not been processed to reveal useful information.
- What is Database?
Ans:- A database is an organized collection of structured information, or data, typically stored electronically in a computer system.
- Write four properties of database
Ans:- A database has the following properties
- A database is a representation of some aspect of the real world also called mini world.
- Whenever there are changes in this mini world they are also reflected in the database.
- It is designed, built and populated with data for specific purpose.
- It can be of any size and complexity .
- It can be maintained manually or it may be computerized.
- Why need for a database?
Ans In traditional file processing, data is stored in the form of files. A number of application programs are written by programmers to insert, delete, modify and retrieve data from these files.
1. Data Redundancy: Same information is stored in more than one file. This would result in wastage of space.
2. Data Inconsistency: If a file is updated then all the files containing similar information must be updated else it would result in inconsistency of data.
3. Lack of Data Integration: As data files are independent, accessing information out of multiple files becomes very difficult.
Q:-5 What is DBMS
Ans:- A database management system is a collection of programs that enables users to create, maintain and use a database.
Q:-6 Which operation performed on a database?
Ans:- The various operations that need to be performed on a database are as follows:
1. Defining the Database: It involves specifying the data type of data that will be stored in the database and also any constraints on that data.
2. Populating the Database: It involves storing the data on some storage medium that is controlled by DBMS.
3. Manipulating the Database: It involves modifying the database, retrieving data or querying the database, generating reports from the database etc.
4. Sharing the Database: Allow multiple users to access the database at the same time.
5. Protecting the Database: It enables protection of the database from software/hardware failures and unauthorized access.
6. Maintaining the Database: It is easy to adapt to the changing requirements.
Some examples of DBMS are – MySQL, Oracle, DB2, IMS, IDS etc.
Q:- Difference between Data and Meta-data.
Ans: Data is raw information, whereas metadata is the context of that information.
The table row and column headers in a spreadsheet are examples of metadata as they offer context to the data.
Q:- Write 3 characteristics of database management systems.
Ans:- The main characteristics of a DBMS are as follows:
- Self-describing Nature of a Database System[1]: DBMS contains not only the database but also the description of the data that it stores. This description of data is called meta-data.
Meta-data is stored in a database catalogue or data dictionary . It contains the structure of the data and also the constraints that are imposed on the data.
- Insulation Between Programs and Data: Since the definition of data is stored separately in a DBMS, any change in the structure of data would be done in the catalogue and hence programs which access this data need not be modified. This property is called Program-Data Independence.
- Sharing of Data: A multiuser environment allows multiple users to access the database simultaneously . Thus a DBMS must include concurrency control software to allow simultaneous access of data in the database without any inconsistency problems.
Q:- What are the types of DBMS users?
Ans:- DBMS is used by many types of users depending on their requirements and interaction with the DBMS. There are mainly four types of users:
- End Users: Users who use the database for querying, modifying and generating reports as per their needs. They are not concerned about the working and designing of the database.
2. Database Administrator (DBA): As the name implies, the DBA administers the database and the DBMS. The DBA is responsible for authoring access, monitoring its use, providing technical support, acquiring software and hardware resources.
3. Application Programmers: Application programmers write application programs to interact with the database. These programs are written in high level languages and SQL to interact with the database.
4. System Analyst: System analyst determines the requirements of the end users and then develops specifications to meet these requirements. A system analyst plays a major role in the database design and all the technical, economic and feasibility aspects.
12th Class DBMS Questions Answers
Q:- Write any 5 advantage and disadvantage of DBMS
Ans:- Following are the advantages of using a DBMS:
1. Reduction in Redundancy: Data in a DBMS is more concise because of the central repository of data. All the data is stored at one place. There is no repetition of the same data. This also reduces the cost of storing data on hard disks or other memory devices.
2. Improved Consistency: The chances of data inconsistencies in a database are also reduced as there is a single copy of data that is accessed or updated by all the users.
3. Improved Availability: Same information is made available to different users. This helps sharing of information by various users of the database.
4. Improved Security: Though there is improvement in the availability of information to users, it may also be required to restrict the access to confidential information. By making use of passwords and controlling users’ database access rights, the DBA can provide security to the database.
5. User Friendly: Using a DBMS, it becomes very easy to access, modify and delete data. It reduces the dependency of users on computer specialists to perform various data related operations in a DBMS because of its user friendly interface.
Limitations of using DBMS Approach
The two main disadvantages of using a DBMS:
1. High Cost: The cost of implementing a DBMS system is very high. It is also a very time-consuming process which involves analyzing user requirements, designing the database specifications, writing application programs and then also providing training.
2. Security and Recovery Overheads: Unauthorized access to a database can lead to threat to the individual or organization depending on the data stored.
Q:- Explain the terms RDBMS.
Ans:- A relational database management system (RDBMS) is a program used to create, update, and manage relational databases. Some of the most well-known RDBMSs include MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.
Q:- Explain the following terms – Row, Column, table, domain, degree, cardinality
Ans:- Row – A row is a single group of related data within a table. A row is called a Tuple.
Column – In a relational database, a column is a set of data values of a particular type, one value for each row of the database. A column is called an Attribute.
Table :- A table is called as a Relation. A table is a collection of related data held in a table format within a database. It consists of columns and rows.
Domain :- A domain is a set of values that can be stored in a column of a database table.
Degree :– The number of attributes in a relation is called the Degree of a relation.
Cardinality :– The number of rows in a relation is called the Cardinality of a relation.
Q:- Write any four name of DBMS software
Ans:- Oracle, MySQL, Microsoft SQL Server, PostgreSQL.
Q:- Explain Super Key, Candidate Key, Primary Key, Foreign Key
Ans:- Super Key:- A Super key is a single key or a group of multiple keys that can uniquely identify tuples in a table. Super keys are a superset of Candidate keys.
Candidate Key :- A column or a group of columns which can be used as the primary key of a relation is called a Candidate key because it is one of the candidates available to be the primary key of the relation. Candidate keys are a subset of Super keys.
Primary Key :- The group of one or more columns used to uniquely identify each row of a relation is called its Primary Key.
Foreign Key:- A primary key of a base table when used in some other table is called as Foreign Key.
Alternate Key :- An alternate key in a Database Management System (DBMS) serves as a candidate key that is not selected as the primary key. Its primary purpose is to provide an alternative unique identifier for a record within a table. These are basically secondary Candidate Keys that can uniquely identify a row in a table. So, Alternate Keys are also sometimes known as “Secondary Keys”.
Q:- What is constraints and explain domain constraints, key constraints, Null value constraint, Not Null Constraint, Default, Check constraint, entity integrity constraint, Referential integrity constraint.
Ans:- Constraints, are restrictions on the values, stored in a database based on the requirements.
For example, in the relation EMPLOYEE, the Employee_ID must be a 4-digit number, the Date_of_Birth must be such that the birth year > 1985.
Domain Constraint: It specifies that the value of every attribute in each tuple must be from the domain of that attribute. For example, the Employee_ID must be a 4-digit number. Hence a value such as “12321” or “A234” violates the domain constraint as the former is not 4-digit long and the latter contains an alphabet.
Key Constraint :- A column or a combination of columns which can be used to identify one or more rows (tuples) in a table is called a key of the table.
Null Value Constraint : Sometimes it is required that certain attributes cannot have null values. For example, if every EMPLOYEE must have a valid name then the Name attribute is constrained to be NOT NULL.
Not Null constraint : The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
Default Constraint: The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
Check Constraint: The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a column it will allow only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
Entity integrity constraint : This constraint specifies that primary key of a relation cannot have null value. The reason behind this constraint is that we know primary key contains no duplicates.
Referential integrity constraint : This constraint is specified between two relations (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid.
Q:- What is SQL?
Ans:- SQL is a language that is used to manage data stored in a RDBMS. It comprises of a Data Definition Language (DDL) and a Data Manipulation Language (DML) where DDL is a language which is used to define structure and constraints of data and DML is used to insert, modify and delete data in a database.
SQL commands are used to perform all the operations.
Q:- Which commonly used data types in DBMS.
Ans:
| Data type | Meaning | Example |
| CHAR (n) | Fixed length character string. ‘n’ is the number of characters. | CHAR(5):“Ashok” “Vijay” |
| VARCHAR(n) | Variable length character string. ‘n’ is the maximum number of characters in the string. | V ARCHAR(15): “Vijay Kumar” “Ashok Sen” |
| DA TE | Date in the form of YYYY -MM-DD | DA TE: ‘2014-03-20’ |
| INTEGER | Integer number | 23 56789 |
| DECIMAL(m, d) | Fixed point number m represents the number of significant digits that are stored for values and d represents the number of digits that can be stored following the decimal point. If d is zero or not specified then the value does not contains any decimal part. | DECIMAL(5,2) : 999.99 -567.78 DECIMAL(5) : 23456 99999 |
Q:- Write a practice with sql commands –
Ans:-
- Create database
CREATE DATABASE School;
- Create table
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20),
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);
- Drop table
Drop Database School;
- Show Table – SHOW TABLES command which displays all the tables created in the current database.
Show tables;
- DESC Command – In MySQL, if you want to look at the structure and description of the tables created, DESC command can be used.
DESC teacher;
12th Class DBMS Questions Answers
Q:- What are Database Constraints ?
Ans:- DBMS can enforce several constraints for smooth operations on databases. These constraints can be specified while creating the table as shown below:
- NOT NULL: An attribute value may not be permitted to be NULL. For example, the First name of the T eacher cannot be NULL. Hence NOT NULL constraint can be specified in this case.
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);
- DEFAULT : If a user has not entered a value for an attribute, then default value specified while creating the table is used.
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER
);
- CHECK: In order to restrict the values of an attribute within a range, CHECK constraint may be used. For example Dept_No of any teacher must not exceed 1 10. This can be specified as follows:
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER CHECK (Dept_No<=110)
);
- KEY CONSTRAINT : Primary Key of a table can be specified in two ways. If the primary key of the table consist of a single attribute, then the corresponding attribute can be declared primary key along with its description.
CREATE TABLE TEACHER
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER
);
- REFERENTIAL INTEGRITY CONSTRAINT– This constraint is specified by using the foreign key clause. This clause contains the foreign key and the primary key referred to by this foreign key along with the name of the relation.
CREATE TABLE Department
(
Dept_ID INTEGER PRIMARY KEY,
Dept_Name VARCHAR(20) NOT NULL
);
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID)
);
Q;- What are the following commands under referential integrity constraint?
Ans:-
- ON DELETE
- ON UPDATE
Actions that can be taken are as follows:
- SET NULL
- CASCADE
- RESTRICT
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON
DELETE SET NULL ON UPDATE SET NULL
);
Thus if a department with a given value of Dept_ID is deleted in Department table, then the corresponding tuples that contains the deleted value for Dept_No attribute in Teacher table would be set to NULL. Similarly , if Dept_ID value is updated then also the corresponding attribute in Teacher table would be set to NULL.
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON
DELETE CASCADE ON UPDATE CASCADE
);
In the above table, if a department with a given value of the Dept_ID attribute in Department table is deleted, then the corresponding rows in the Teacher table would also be deleted. However if Dept_ID value is updated in the Department table, the change in corresponding value is also reflected in
Teacher Table.
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON
DELETE RESTRICT ON UPDATE RESTRICT
);
RESTRICT option will reject the delete or update operation for the referenced table if there are one or more related foreign key values in a referencing table, i.e, you cannot delete or update a department if there are teachers who belong to that department.
12th Class DBMS Questions Answers
Q;- What is Self-Referencing Tables?
Ans:- A foreign key constraint can reference columns within the same table. These tables are called as self-referencing tables. For example, consider a table Employee that contains five columns: Employee_ID, Name, Age, Salary and Manager_ID. Because the manager is also an employee, there is a foreign key relationship between the Manager_ID and Employee_ID as shown below:
CREATE TABLE Employee
(
Employee_ID INTEGER PRIMARY KEY,
Name VARCHAR(30),
Age INTEGER,
Salary DECIMAL(10,2),
Manager_ID INTEGER,
FOREIGN KEY (Manager_ID) REFERENCES Employee
(Employee_ID)
);
- Naming of Constraint : Constraints can be named in the Create Table command. The advantage is that named constraints can be easily deleted or updated using the Alter Table command. A constraint can be named by using the keyword CONSTRAINT followed by the name of the constraint and its specification. For example consider the following Create Table command:
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
CONSTRAINT TEACHER_PK PRIMARY KEY (Teacher_ID),
CONSTRAINT TEACHER_FK FOREIGN KEY (Dept_No) REFERENCES
Department(Dept_ID) ON DELETE SET NULL ON UPDATE SET NULL
);
In the above table, the primary key constraint is named as TEACHER_PK and the foreign key constraint is named as TEACHER_FK.
- DROP Table Command – This command is used to delete tables. For example, suppose you want to drop the Teacher table then the command would be:
DROP TABLE Teacher CASCADE;
Thus Teacher table would be dropped and with the CASCADE option, i.e. all the constraints that refer this table would also be automatically dropped.
If the requirement is that the table should not be dropped if it is being referenced in some other table then RESTRICT option can be used as shown below:
DROP TABLE Teacher RESTRICT;
- Alter Table Command – This command is used to modify the base table definition. The modifications that can be done using this command are:
- Adding a column: Suppose we want to add a column Age in the Teacher table. Following command is used to add the column:
ALTER TABLE Teacher ADD Age INTEGER;
- Dropping a column: A column can be dropped using this command but one must specify the options (RESTRICT or CASCADE) for the drop behavior.
ALTER TABLE Teacher DROP Dept_No CASCADE;
- Altering a Column: A column definition can also be altered. For example – dropping the default value or defining a new default value. For example, in the Teacher table the default value of Salary is 40000. If you want to drop this default value or change this value to 30000 then it can be done by using the following commands:
ALTER TABLE Teacher ALTER Salary DROP DEFAULT;
ALTER TABLE Teacher ALTER Salary SET DEFAULT 30000;
- Dropping keys: A foreign key/primary key/key can be dropped by using ALTER TABLE command. For example if you want to delete the foreign key TEACHER_FK in the Teacher table then following command can be used:
ALTER TABLE Teacher DROP FOREIGN KEY TEACHER_FK;
Primary key can be dropped by using the command:
ALTER TABLE Teacher DROP PRIMARY KEY TEACHER_PK;
- Adding a Constraint: If you want to add the foreign key constraint TEACHER_FK back, then the command would be:
ALTER TABLE Teacher ADD CONSTRAINT TEACHER_FK FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID) ON DELETE SET NULL ON UPDATE SET NULL;
- Insert Command – This command is used to insert a tuple in a relation. We must specify the name of the relation in which tuple is to be inserted and the values.
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
CONSTRAINT TEACHER_PK PRIMARY KEY (Teacher_ID),
);
To insert a tuple in the Teacher table INSERT command can be used as shown below:
INSERT INTO Teacher VALUES (101,”Shanaya”, “Batra”, ‘F’, 50000, ‘1984-08-11’, 1);
- UPDATE Command – This command is used to update the attribute values of one or more tuples in a table. For example in the T eacher table, we want to update the Salary of teacher with Teacher_ID=101 to 55000. This can be done using the following
command:
UPDATE Teacher
SET Salary=55000
WHERE Teacher_ID=101;
- DELETE Command – In order to delete one or more tuples, DELETE command is used. If we want to delete the tuple for T eacher with ID=101 the command would be:
DELETE FROM Teacher WHERE Teacher_ID=101;
- Select Command – The SELECT Command is used to retrieve information from a database. There are various ways in which the SELECT command can be used. Syntax of SELECT Command is as follows:
SELECT <attribute list>
FROM <table list>
WHERE <condition>
Q:- What is Query in MySql?
Ans:- In relational database management systems, a query is any command used to retrieve data from a table. In Structured Query Language (SQL), queries are almost always made using the SELECT statement.
Q:- Write a queries are following tables –
- – To Retrieve all the information about Teacher with ID=101. In this query we have to specify all the attributes in the SELECT clause.
SELECT *
FROM Teacher
WHERE Teacher_ID=101;
- Query: To find the names of all teachers earning more than 50000.
SELECT First_Name,Last_Name
FROM Teacher
WHERE salary > 50000;
- Query: To display Teacher_ID,First_Name,Last_Name and Dept_No of teachers who belongs to department number 4 or 7.
SELECT Teacher_ID,First_Name,Last_Name, Dept_No
FROM Teacher
WHERE Dept_No = 4 OR Dept_No = 7;
- Query: To retrieve names of all the teachers and the names and numbers of their respective departments.
SELECT First_Name, Last_Name, Dept_ID, Dept_Name
FROM Teacher, Department;
- Query: To retrieve names of all the teachers who belong to Hindi department.
SELECT First_Name, Last_Name
FROM Teacher, Department
WHERE Department. Dept_ID=Teacher. Dept_ID AND
Dept_Name=”Hindi”;
- Query: To retrieve names of all the teachers starting from letter ‘S’.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE “S%”;
- Query: To retrieve names of all the teachers having 6 characters in the first name and starting with ‘S’.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE “S_ _ _ _ _”;
- Query: To retrieve names of all the teachers having at least 6 characters in the first name.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE “_ _ _ _ _ _%”;
- Query: To list the names of teachers in alphabetical order.
SELECT First_Name, Last_Name
FROM Teacher
ORDER BY First_Name, Last_Name;
- Query: To list the names of all the Departments in the descending order of their names.
SELECT Dept_Name
FROM Department
ORDER BY Dept_Name DESC;
- Query: To retrieve the names and department numbers of all the teachers ordered by the Department number and within each department ordered by the names of the teachers in descending order.
SELECT First_Name, Last_Name, Dept_No
FROM Teacher
ORDER BY Dept_No ASC, First_Name DESC, Last_Name DESC;
- Query: To retrieve all the details of those employees whose last name is not specified.
SELECT *
FROM Teacher
WHERE Last_Name IS NULL;
- Query: To retrieve the names of all the departments having female teachers.
SELECT DISTINCT Dept_Name
FROM Department
WHERE Dept_ID IN (Select Dept_No
FROM Teacher
WHERE Gender = ‘F’)
Aggregate Function :- Sometimes it is required to apply certain mathematical functions on group of values in a database. Such functions are called Aggregate Functions. For example retrieving the total number of teachers in all the Departments. Following are the commonly used built-in
aggregate functions:
- COUNT- It counts the numbers of tuples in the result of the query .
- SUM– It finds the sum of all the values for a selected attribute which has numeric data type.
- MAX –It finds the maximum value out of all the values for a selected attribute which has numeric data type.
- MIN – It finds the minimum value out of all the values for a selected attribute which has numeric data type.
- AVG – It finds the average value of all the values for a selected attribute which has numeric data type
Query: To find total salary of all the teachers
SELECT SUM(Salary) AS Total_Salary
FROM Teacher;
Query: To find the maximum and minimum salary.
SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS
Min_Salary
FROM Teacher;
Query: To count the number of teachers earning more than Rs 40000.
SELECT COUNT(Salary)
FROM Teacher
WHERE Salary > 40000;
Query :- To retrieve the number of teachers in “Computer Science” Department
SELECT COUNT(*) AS No_of_Computer_Science_Teachers
FROM Department, Teacher
WHERE Dept_Name = “Computer Science”AND Dept_No=Dept_ID;
[1] Self-describing Nature – A database system is self-describing because it not only contains data, but also metadata about the data. This self-describing nature of DBMS makes it totally different from a traditional file-based system.
Conclusion
In conclusion, a Database Management System (DBMS) is a crucial technology that facilitates efficient data management and retrieval, supporting the organization of large volumes of data while ensuring security, consistency, and data integrity. With features like data independence, concurrency control, and backup mechanisms, DBMSs empower organizations to handle complex data-driven operations seamlessly. As data continues to grow exponentially, the role of DBMS in optimizing database performance, enhancing decision-making, and improving scalability will remain pivotal in both academic and industrial settings.