PART1
Q1: Why do we use indexing? Give a suitable example in support of your answer.
Ans1: While indexing, we are concerned with finding the data we are actually concerned with frequently and efficiently without having to request and read more disk blocks than absolutely necessary. There are several approaches to do indexing.
Ø Indexing mechanism is used to speed up accessing data.
Ø An index file consists of records usually referred to as index-entries.
Q2: under what circumstances, we will use foreign key? Give an example.
Ans2: Foreign keys are the attributes of a table, which refers to the primary key of any other table. It establishes a relationship, or constraint between two tables.
Consider two tables “STUDENT” and “BORROWED” containing the following attributes:
STUDENT BORROWED
Id
Name
1
Shivani
2
Dipti
3
Anish
Ref id
S_id
Book_name
1
1
DBMS
2
1
Maths
3
3
Physics
Here in ‘STUDENT' table each student is assigned a unique ‘Id' which can be made as primary key at the time of creation of ‘STUDENT' table.
‘BORROWED' table consists of list of borrowed books. Every record will reference a borrower's student id.
To create a relationship between student id in both tables, we will have to use the concept of foreign key.
Syntax:
Alter table borrowed add constraint fk_borrowed foreign key(S_id) references student(Id)
Using this, we defined the ‘S_id' column of ‘BORROWED' table as foreign key which refer to the primary key ‘Id' of ‘STUDENT' table or in other words, a student id must exist before they can borrow book. Student changes will automatically be handled by the Database.
Q3: Produces a new relation with some of the attributes of relation and remove duplicate tuples, give two different examples.
Ans3:
We will use the following table with duplicate PK values. In this table the primary key is the two columns (col1, col2). We cannot create a unique index or PRIMARY KEY constraint since two rows have duplicate PKs.
This procedure illustrates how to identify and remove the duplicates.
The first step is to identify which rows have duplicate primary key values:
This will return one row for each set of duplicate PK values in the table, and the table will be unique.
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
Code:
SELECT DISTINCT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
Part B
Q4: Can we use a view for security purpose? Justify your answer and give an example to create a view.
Ans4: Yes a view can be used for security purpose. A view is a virtual table which gets created on the execution of a stored query. It can be a complete table or just a part of it i.e. some columns from a table.
For example, I have a table named as customer which includes acc_no, cust_name, address, and balance as columns. I am the administrator and I don't want to give the access of balance column to my assistant. So I can create a view in which balance column will be excluded.
Create view v1
As
Select acc_no, cust_name, address from t1.
Q5: Write the syntax of all commands which are used to define definition if data?
Ans5:
This category include creating, dropping and altering, rename, truncate ,insert table commands .
CREATE TABLE : Its used in creation of table(s).
SYNTAX: CREATE TABLE <Table name> ( <Column name1> <datatype(size)>, <Column name2> <datatype(size)>, …… <Column namen> <datatype(size)>)
ALTER TABLE: If you want to modify a table definition we can use this command. This command is used to alter column definition in table.
SYNTAX: ALTER TABLE tablename MODIFY (Column definition);
DROP TABLE: This command is used to remove an existing table permanently from database.
SYNTAX: DROP TABLE < tablename>;
INSERT: This command is used to insert rows into the table.
SYNTAX: INSERT INTO tablename VALUES (value,value,….);
TRUNCATE
SYNTAX: TRUNCATE TABLE <Table name>
RENAME
SYNTAX: RENAME <Table name> TO <New Table name>
SELECT: This is used to select values or data from table
SYNTAX: SELECT column name1, column name2, …. FROM tablename
Q6: Consider the table EMPLOYEE with following fields:
Emp_id, Emp_name, Dept_no, Dept_name, Location, Salary
Perform the following computations on table data:
a) List all the employees whose location is ‘Pune' and dept_name is ‘Computer'.
b) Count the total number of departments. Also count the total number of employees whose dept_name is ‘computer'
c) Add a new department ‘R&D' in the database.
d) List the names of employees whose salary is greater than 50000.
e) List the names of employees having maximum and minimum salary
f) Now change the department name from ‘computer' to ‘software design', wherever applicable in the table.
Ans: a) Select * from EMPLOYEE where location=' Pune ' and dept_name='Computer';
b) Select count (distinct dept_no) from EMPLOYEE;
Select count (*) from EMPLOYEE
Where dept_name='Computer';
c) Alter table EMPLOYEE
Add column R&D varchar(20);
d) Select Emp_name from EMPLOYEE
Where Salary>50000;
e) Select MAX (Salary) from EMPLOYEE;
Select MIN (Salary) from EMPLOYEE;
g) Update EMPLOYEE
Set dept_name = 'software design'
Where dept_name='Computer';
