PART-AQuestion 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:-
As we have different queries that are used for above commands.Create:-
For creating the relational model is create table tablename(attribute name datatype, 2ndattribute name datatype );Describe:-
Alter table tablename add(attribute name datatype);Delete:-
Alter table tablename drop(column column name);Rename:-
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:
- List all the employees whose location is 'Pune' and dept_name is 'Computer'.
- Count the total number of departments. Also count the total number of employees whose dept_name is 'computer'
- Add a new department 'R&D' in the database.
- List the names of employees whose salary is greater than 50000 and dept_ name is 'computer'.
- List the names of employees having maximum and minimum salary.
- Now change the department name from 'computer' to 'software design', wherever applicable in the table.
Ans: - select emp_name from employee where location= "Pune" and dept_name= "Computer".
Ans: - select count(dept_name) from department. Select dept_name from department group by dept_name having dept_name ='computer'.
Ans;- insert into department(dept_name)values('R&D').
Ans: - select emp_name from employee having min(salary)<=50000 and dept_name='computer'.
Ans:- select emp_name from employee having max(salary).
Select emp_name from employee having min(salary).
Ans:- update table department set dept_name='software design' where dept_name='computer'.