Database Management systems - Module 2

1 a. Explain the entity integrity and referential integrity constraints.  Why is each considered important. Give examples.

   b. Discuss equijoin and natural join with suitable examples using relational algebra notation.

   c. Given the schema:

      Passenger (pid,pname,pgender,pcity)

      Agency (aid,aname,acity)

      Flight (fid,fdate,time src,dest)

      Booking (pid,aid,fid,fdate)

     Give relation algebra expression for the following:

1. Get the complete details of all flights to new Delhi
2. Find only the flight numbers for the passenger with paid 123 for flights to Chennai before 06/11/2020
3. Find the passenger names for those who do not have any bookings in any flights
4. Get the details of flights that are scheduled on both dates 01/12/2020 and 02/12/2020 at 16:00 hours
5. Find the details of all the male passengers who are associated with jet agency.

2 a. Explain the ER to relational mapping algorithm with suitable examples for each step.

   b. Write SQL query for the following database scheme:

        Employee (employee_name,street,city)

        Works (employee_name,company_name,salary)

        Company (company_name,city)

        Manages(employee_name,manager_name)

1. Find the name, street address and cities of residence for all employee who work for ‘First Bank Corporation’ and earn more than $10000
2. Find the names of all employees in the database who do not work for ‘First Bank Corporation’. Assume that all people work for exactly one company.
3. Find the names of all employees in the database who earn more that every employee of ‘First Bank Corporation’. Assume that all people work for at most one company.
4. Find the name of the company that has the smallest payroll
5. Find the names of all the employee in the database who live on the same cities and on the same streets as do their managers.

3 a. Discuss the characteristics of relation that make them different from ordinary tables.

   b. Discuss DIVISION operation. Find the quotient for the following :  A/B₁,A/B₂,A/B₃ where   A,B₁,B₂,B₃ are

• 
  For educators
• Help
• Sign in    c. Explain the basic datatypes available for attributes in SQL.

4 a. Explain the steps to convert the basic ER model to relational database schema.

   b. For the following relations for a book club:

       MEMBERS (member_id,name,desg,age)

       BOOKS (book_id,book_title,book_author,book_publisher,book_price)

       RESERVES (member_id,book_id,date)

       Write the SQL queries 

1. Find the names of members who are professors older than 45.
2. List the titles of books reserved by professors.
3. Find ID’s of members who have not reserved books that cost more than Rs.500
4. Find the authors and titles of books reserved on 27-may-2017
5. Find the names of members who have reserved all books

5 a. Consider the following schema and write the relational algebra :

        Sailors (sid,sname,rating,age)

        Boats (bid,bname,color)

        Reserve (sid,bid,day)

1. Retrieve the sailors name who have reserved red and green boats
2. Retrieve the sailors name with age over 20 years and reserved black boats
3. Retrieve the sailors name who have reserved green boat on Monday
4. Retrieve the number of boats which are not reserved
5. Retrieve the sailors name who is the oldest sailor with rating 10

    b. List Set theory operations used in relational data model. Explain any two with an example.

    c. Define the following 

       a) Relation state    b) Domain   c) Relation schema   d) Arity

6 a. Discuss the various types of JOIN operations with an example. Why is THETA join required?

   b. Describe the steps of an algorithm for ER-to-Relational mapping.

   c. Describe any two characteristics of relations with suitable example for each.

7. Define the following terms i) Key ii) Super key iii) Candidate key iv) Primary key v) Foreign key

8. Consider the following COMPANY database 

    EMP(Name,SSN,Salary,SuperSSN,Gender,Dno) 

    DEPT(DNum,Dname,MgrSSN,Dno) 

    DEPT_LOC(Dnum,Dlocation)

    DEPENDENT(ESSN,Dep_name,Sex) 

    WORKS_ON(ESSN,Pno,Hours) 

    PROJECT(Pname,Pnumber,Plocation,Dnum)

    Write the relational algebra queries for the following 

    (i)Retrieve the name, address, salary of employees who work for the Research department. 

    (ii) find the names of employees who work on all projects controlled by department number4. 

    iii) Retrieve the SSN of all employees who either in department no :4 or directly supervise an employee           who work in department number :4

    (iv) Retrieve the names of employees who have no dependents

    (v) Retrieve each department number, the number of employees in the department and their average salary.

9. Summarize the steps involved in converting the ER constructs to corresponding relational tables

10. Explain with example basic constraints that can be specified when a database table is created.

11. Write SQL syntax for the following with example: (i) SELECT (ii) ALTER (iii) UPDATE

12.Consider the following relation schema 

    Works(Pname,Cname,salary) 

    Lives(Pname,Street,City) 

    located_in (Cname, city) 

    Manager(Pname,Mgrname) 

    Write the SQL queries for the following

     i) Find the names of all persons who live in the city Bangalore. 

    ii) Retrieve the names of all person of "Infosys" whose salary is between Rs .50000 

    iii) Find the names of all persons who lives and work in the same city 

    iv) List the names of the people who work for “Tech M” along with the cities they live in. 

    v) Find the average salary of “Infosys” persons