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
Background image of page 1
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 );
Background image of page 2
Sign up to view the entire interaction

Top Answer

Hello Find the solutions... View the full answer

9322115.docx

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...

Sign up to view the full answer

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
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online