Question 1)a) What is data abstraction? Describe the levels of data abstraction.
(b) Consider the following relational database and give an expression in the relational algebra for each of the following queries:
employee( person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_name, manager_name)
(i) Find the company with the most employees.
(ii) find the company with smallest payroll.
(iii) Find the names and cities of residence of all employees who work for First bank corporation.
(iv) Give all managers in the database a 10 percent salary raise, unless the salary would be greater than Rs. 100,000. In such cases give only a 3 percent raise.
(c) Describe the different outer join operations with example.
Question 2)(a) Describe the test for empty relations in nested queries.
(b) Use following schema and write the queries:
Employee(Fname, Initial, Lname, ENo, DOB, Addr, Sex, Salary, SENo, DNo)
Department(DNmae, DNo, MgrENo, MgrStDt,)
Dept_Location(DNo, DLocation)
Project(PName, PNo, PLocation, DNo)
Work_on(ENo, PNo, Hours)
Dependent(ENo, Dep_Name, Sex, DOB, Rel)
i) Give all employees in the research dept a 10% salary raise.
ii) Retrieve the name of employees who have no dependents (use exists/not exists).
iii) For each project retrieve the project number, project name, number of employees from department 5 who work on the project.
iv) Retrieve a list of employees and the projects they are working on, order by dept and within dept alphabetically by last name and first name.
(c) Describe the update of a view.
(d) Describe view serializability with example.
Question 3)(a) What are attributes? Describe various types of attributes with example. How they are represented in an ERD.
(b) Consider the University database application with following details:
• An University has many departments.
• Each department has multiple instructors; one among them is head of department.
• An instructor belongs to only one department.
• Each department offers multiple courses, each of which is taught by a single instructor.
• A student may enroll for many courses offered by different departments.
Draw the ERD for the above by identifying entities, relationships, attributes and cardinality.
(c) Describe the concept of recoverable and cascade less schedules.