MODULE-03
QUESTIONS
1. By refereeing the following Database schema.
Employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Sup_SSN, Dno)
Department (Dname, Dnumber, Mgr_SSN, Mgr_Start_date)
Dept_Locations( Dnumber, Dlocation)
Project(Pname, Pnumber, Plocation, Dnum)
Works _On(Essn, Pno, Hours)
Dependent (Essn, Dependent_Name, Sex, Bdate, Relationship)
Write the SQL Queries for the following
(i). Retrieve the name and address of all employees who work for the ‘Research’
department.
(ii). Make a list of all project numbers for projects that involve an employee
whose last name is ‘Smith’, either as a worker or as a manager of the department
that controls the project.
(iii). List the names of managers who have at least one dependent.
(iv). Find the sum of the salaries of all employees, the maximum salary, the minimum
salary, and the average salary.
(v). For each project, retrieve the project number, the project name, and the number of
employees who work on that project.
2. With an example, explain Specifying Constraints as Assertions and triggers in SQL
3. With an example, explain the concept of View in SQL
4. What are the aggregate functions? And list the aggregate functions supported by SQL?
5.Consider the following tables:
WORKS (Pname, Cname, Salary)
LIVES (Pname, Street, City)
LOCATED_IN (Cname, City)
MANAGER (Pname, Mgrname)
Write the SQL query for the following
a. Retrieve the names of the people who working for Wipro along with the address they live
in.
b. Retrieve the names of the person who gets highest salary.
c. Find the number of employee and average salary of each company.
6. Give the syntax of assertion & triggers?
7. Illustrate insert, delete, update, alert and drop statements in SQL.
8. Explain the syntax of SELECT
9. How is view created and dropped? What problems are associated with updating views?
Module – 5
1. Define transaction. List and explain ACID Properties.
2.Briefly explain the two phase locking protocol used in concurrency control.
3. why concurrency control is needed. demonstrate with example.
4. Discuss the desirable properties of transaction.
5.what is Serializability? How can serializability be ensured? Do you need to restrict concurrent
execution of transaction to ensure serializability?
6.With a neat diagram explain the states for transaction execution.
Module – 2
1. Consider the following movie database:
Movie (Title , director , Myear , Rating)
Actors (Actor, Aage)
Acts (Actor , title)
Directors (Director, dage)
Write the following queries in relational algebra on the database given:
i) Find movies made by “hanson” after 1997.
ii) Find all actors and directors.
iii) Find “Coen`s” movie with “Me Dormand”.
iv) Find (director, actor) pairs where the director is younger than the actor.
2. Discuss the following relational algebra operations. Illustrate with an example for each:
JOIN, DIFFERENCE, SELECT, UNION.
3. Explain the steps to convert the basic ER model to relational database schema.
4. Define foreign key. Explain all possible options that can be specified when a referential
integrity constraint is violated.
