View the step-by-step solution to:

# Department of Computer Science 1411365 DB Design and Implementation Spring 2015-2016 Assignment 3 Posted date: 30, 2016 Due date: Develop the steps...

Department of Computer Science

1411365 DB Design and Implementation

Spring 2015-2016

HW3

Department of Computer Science 1411365 DB Design and ImplementaTon Spring 2015-2016 TOTAL: Assignment 3 Posted date: Apr. 30, 2016 Due date: May. 17, 2016 Objectives After completing this assignment, you will be able to: Develop the steps of query optimization. Understand transaction processing Identifying conFict serializable transactions Achieved Outcomes: D and E HW Exc. % Mark 1 6 2 4
Exercise 1: For the following queries: Query A. List the project names and the manager’s last name of the controlling department of projects controlled by department number 3 and located in “Sharjah”. Query B. Find the names of employees who work on all the projects controlled by department number 5. Develop the following : 1) Write the SQL query. 2) Translate the SQL query into Relational Algebra query. 3) Create an initial (canonical) Query Tree. 4) Move SELECT operations down the query tree, if applicable. 5) Apply the more restrictive SELECT operations Frst, if applicable. 6) Replace CARTESIAN PRODUCT and SELECT with JOIN operations, if applicable. 7) Move PROJECT operations down the query tree, if applicable. Exercise 2: Consider the three transactions T 1, T 2, and T 3, and the schedules S 1 and S 2 given below. Draw the serializability (precedence) graphs for S 1 and S 2, and state whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s). T 1: r 1 ( X ); r 1 ( Z ); w 1 ( X ); T 2: r 2 ( Z ); r 2 ( Y ); w 2 ( Z ); w 2 ( Y ); T 3: r 3 ( X ); r 3 ( Y ); w 3 ( Y ); S 1: r 1 ( X ); r 2 ( Z ); r 1 ( Z ); r 3 ( X ); r 3 ( Y ); w 1 ( X ); w 3 ( Y ); r 2 ( Y ); w 2 ( Z ); w 2 ( Y ); S 2: r 1 ( X ); r 2 ( Z ); r 3 ( X ); r 1 ( Z ); r 2 ( Y ); r 3 ( Y ); w 1 ( X ); w 2 ( Z ); w 3 ( Y ); w 2 ( Y );

Hello Find the solutions... View the full answer

Exercise 1 Query A. List the project names and the manager’s last name of the controlling department of
projects controlled by department number 3 and located in “Sharjah”.
Result: PNAME...

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents