Database management system


Question 1: Why do we use indexing? Give a suitable example in support of your answer.

Ans:- As we know that what does index mean? Basically it is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and excludes all the other details in the table), yielding the possibility to store indexes in memory for a table whose data is too large to store in memory.

So for fast retrieval we use indexing as no need to see each and every entry for a particular entry.

Like we have any table and we have to apply indexing on any columnname then we write the query

Create index on tablename (columnname) or we can write it as create index <index-name> on <relation-name>.

Question 2: Under what circumstances, we will use foreign key? Give an example.

Ans:- As we know that foreign key is that points to foreign key means it refers to another table having primary key applied to it. The purpose of foreign key is to ensure referential integrity of the data. Or we can say that the values that are supposed to appear in the database are permitted.

A foreign key constraint specifies that the data in a foreign key must match the data in the primary key of the linked table.

Like we take example of if we have tables like student and employee and we have same attribute like roll no then we first apply primary key then foreign key then no repetition is allowed means no repeated roll no is allowed so we can relate two different tables by referring to another table by applying foreign key.

Question3: Produces a new relation with some of the attributes of relation and remove duplicate tuples, give two different examples.

Ans:- we have different queries to remove duplicate values as by applying intersection and by using primary key and foreign key.

If we have tables like student and employee then we have a same attribute like we have attributes student_name, roll no in table student and attributes roll no, emp_name in employee then if we enter same data like roll no 4 then if apply select roll no from student


Select roll no from employee.

Or we can apply primary key to any attribute and then foreign key then duplicate values delete automatically.

In this we can take an example of employee and department having same attribute emp_id then we can apply first primary key and then foreign key then automatically any duplicate value deleted.

PART-B Question 4: Can we use a virtual table for security purpose? Justify your answer and give an example to create a view.

Ans:- Yes we can use a virtual table for security purpose .

  • As we create view so by using view it create a virtual table which will not exist but reflects to the user that it is the original table and our original table is safe and secure.
  • If we have different attributes and we want only e few attributes to be viewed in virtual table so it secures other columns and modification not be done in other attributes.
  • If we take an example of ums then those which are only visible to us but they don't want us to see all content then we use virtual tables.

If we have original table student and have attributes like name, student_id, roll no and we have to create view having attributes like name, roll no then our student_id is secure and not visible to user and he/she don't know that student_id is here or not like this our original table is safe and virtual table act like original table. Here it is used for security purpose.

Question 5: Write the syntax of all commands which are used to define definition if data?

Ans:- We say DDL means data definition language which is used to define data and data about data(logic, constraints, rules).

Various commands used in DDL are:-

  • Create
  • Describe
  • Alter
  • Drop(delete)
  • Truncate
  • Rename

As we have different queries that are used for above commands.


For creating the relational model is create table tablename(attribute name datatype, 2ndattribute name datatype );


Desc tablename;


Alter table tablename add(attribute name datatype);


Alter table tablename drop(column column name);


Rename tablename to new tablename;

Question 6: Consider the table EMPLOYEE and Department with following fields:

Employee ( Emp_id,Emp_name, Dept_no, salary)

Department (Dept_no, Dept_name,Location)

Perform the following computations on table data:

  1. List all the employees whose location is 'Pune' and dept_name is 'Computer'.
  2. Ans: - select emp_name from employee where location= "Pune" and dept_name= "Computer".

  3. Count the total number of departments. Also count the total number of employees whose dept_name is 'computer'
  4. Ans: - select count(dept_name) from department. Select dept_name from department group by dept_name having dept_name ='computer'.

  5. Add a new department 'R&D' in the database.
  6. Ans;- insert into department(dept_name)values('R&D').

  7. List the names of employees whose salary is greater than 50000 and dept_ name is 'computer'.
  8. Ans: - select emp_name from employee having min(salary)<=50000 and dept_name='computer'.

  9. List the names of employees having maximum and minimum salary.
  10. Ans:- select emp_name from employee having max(salary).

    Select emp_name from employee having min(salary).

  11. Now change the department name from 'computer' to 'software design', wherever applicable in the table.

Ans:- update table department set dept_name='software design' where dept_name='computer'.

Please be aware that the free essay that you were just reading was not written by us. This essay, and all of the others available to view on the website, were provided to us by students in exchange for services that we offer. This relationship helps our students to get an even better deal while also contributing to the biggest free essay resource in the UK!