What is a subquery and when is it used

Assignment Help Database Management System
Reference no: EM13778576

Part A

1. A lock on a larger data element increases both system overhead and waiting by transactions.

True False

2. When transactions T1 and T2 are in a deadlock, one of the transactions will eventually execute to completion and let the other one continue execution.

True False

3. When an update to the database takes place, the time of the update, the values of the data before and after the update, and the type of lock used are recorded in the transaction log.

True False

4. To restore a database using the immediate update approach, both undo and redo operations may be necessary, whereas with the deferred update approach only redo operations are used.

True False

5. Which of the following is an example of lost update?

A. The system assigns the last aisle seat on a flight to John whereas Peter receives a window seat, although both indicated preference for an aisle seat

B. John and Peter receive each a window seat as they both requested

C. John is initially assigned the last window seat, but this seat is later on assigned to Peter

D. None of the above

6. Julia has a lock on the savings account and Bill is trying to perform a conflicting action on the data:

A. Bill's transaction is granted permission to perform the desired action

B. Bill's transaction must wait until Julia's lock is released

C. Bill's transaction must wait until Julia has read the savings account value

D. None of the above

7. Julia has a shared lock on the savings account and Bill is trying to perform an action requesting a shared lock on the same data:

A. Bill's transaction is granted permission to perform the desired action

B. Bill's transaction must wait until Julia's lock is released

C. Bill's transaction must wait until Julia has read the savings account value

D. None of the above

8. Julia has an exclusive lock on the savings account and Bill is trying to perform an action requesting a shared lock on the same data:

A. Bill's transaction is granted permission to perform the desired action

B. Bill's transaction must wait until Julia's lock is released

C. Bill's transaction must wait until Julia has read the savings account value

D. None of the above

9. Which of the following will cause a deadlock?

A. Transaction T1 requests an exclusive lock on data A at the same time as transaction T2

B. Transaction T1 places an exclusive lock on data A, then transaction T2 requests a shared lock on data A

C. Transaction T1 places an exclusive lock on data A, then requests one on data B. Simultaneously, transaction T2 places an exclusive lock on data B, then requests one on data A

D. Transaction T1 and T2 modify data A without any lock

10. In the two-phase locking protocol:

A. A transaction only acquires a lock after the previously acquired lock has been released

B. A transaction acquires locks as needed. After it starts releasing locks, it does not acquire new locks

C. A transaction acquires all needed locks at once, then releases them after completion

D. The transaction locks the full database, then releases the locks

11. Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee) under the immediate update mode. The transaction terminates abnormally after 10 rows have been updated in the database (and the normal operations have taken place on the log). What should the recovery procedure perform to make sure the transaction completes as desired?

A. Redo

B. Redo followed by execution of the transaction again

C. Undo

D. Undo followed by execution of the transaction again

12. Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee) under the deferred update mode. The transaction terminates abnormally after the new values of 10 rows have been written to the log. What should the recovery procedure perform to make sure the transaction completes as desired?

A. Execute the transaction again

B. Redo followed by execution of the transaction again

C. Undo

D. Undo followed by execution of the transaction again

13. Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee) under the immediate update mode and completes before a checkpoint. The system crashes after the checkpoint. What should the recovery procedure perform to make sure the transaction completes as desired?

A. Nothing

B. Redo followed by execution of the transaction again

C. Undo

D. Undo followed by execution of the transaction again

14. Consider a transaction that updates each of the 100 employees' salaries in a table (there is one row per employee) under the immediate update mode and completes after a checkpoint but before a system failure. The system crashes after the checkpoint. What should the recovery procedure perform to make sure the transaction completes as desired?

A. Nothing

B. Redo forward from checkpoint

C. Undo

D. Undo followed by execution of the transaction again

15. With database locking, a(n) _______________ lock must be obtained before reading a database item, and a(n) ___________ lock must be obtained before writing to a database item.________________________________________

CUSTOMER

In table CUSTOMER, CID is the primary key (Customer ID).

RENTALS

In the table RENTALS, RTN provides the rental number (the primary key), CID is the customer's unique id, PICKUP is the city where the car was picked up, and Return is the city where the car was returned.

RENTCOST

RENTCOST shows the base cost of renting a given MAKE for one day.

CITYADJ

If the return city of table RENTALS is the one listed in table CITYADJ, the cost of the rental is multiplied by FACTOR and by DAYS shown in table RENTLENGTH below.

RENTLENGTH

RENTLENGTH shows the number of days for the rental number (RTN) shown in table RENTALS. In a database used in reality, this table would be merged with the RENTALS table.

16. SELECT DISTINCT CID, CNAME

FROM CUSTOMER

WHERE CID IN

(SELECT CID FROM RENTALS WHERE MAKE IN ('FORD', 'TOYOTA'))

The CNAMEs shown by the execution of this query are:

A. BLACK

B. BLACK, JONES

C. BLACK, JONES, MARTIN

D. BLACK, JONES, MARTIN, VERNON

17. SELECT DISTINCT CUSTOMER.CID, CNAME

FROM CUSTOMER, RENTALS, RENTCOST

WHERE CUSTOMER.CID = RENTALS.CID

AND RENTALS.MAKE = RENTCOST.MAKE AND NOT EXISTS

(SELECT * FROM RENTALS R, RENTCOST C

WHERE R.MAKE = C.MAKE

AND RENTALS.CID = R.CID

AND RENTCOST.COST <> C.COST)

The meaning of this query is:

A. List all customers with more than one car make rented

B. List all customers who have only rented one make

C. List all customers with one or more rentals for which the cost of each car make rented is the same

D. None of the above

18. SELECT MAKE

FROM RENTALS, CUSTOMER

WHERE RENTALS.CID = CUSTOMER.CID

AND RESID_CITY = 'HEMET'

GROUP BY MAKE

HAVING COUNT (DISTINCT RENTALS.CID) =

(SELECT COUNT(*) FROM CUSTOMER

WHERE RESID_CITY = 'HEMET')

The execution of this query produces the following number of rows:

A. 0

B. 1

C. 2

D. 3

19. SELECT MAKE

FROM RENTALS, CUSTOMER

WHERE RENTALS.CID = CUSTOMER.CID

AND RESID_CITY = 'HEMET '

GROUP BY MAKE

HAVING COUNT (DISTINCT RENTALS.CID) =

(SELECT COUNT(*) FROM CUSTOMER

WHERE RESID_CITY = 'HEMET')

The meaning of this query is the following:

A. List all makes of cars rented to customers residing in Hemet

B. List all makes of cars rented to at least one customer residing in Hemet

C. List all makes of cars rented to all customers residing in Hemet

D. None of the above

20. SELECT CID, CNAME FROM CUSTOMER

WHERE 0 =

(SELECT COUNT(*) FROM RENTALS

WHERE CUSTOMER.CID = RENTALS.CID)

What is the interpretation of this query?

A. List the customers who do not have rentals

B. List the customers who have one rental

C. List the customers who have 0 or more rentals

D. List the customers who have 1 or more rentals

21. SELECT CNAME, DATE_OUT, RTN

FROM CUSTOME

WHERE CUSTOMER.CID = RENTALS.CID

AND BIRTHPLACE IN ('ERIE', 'CARY') AND EXISTS

(SELECT * FROM RENTCOST

WHERE COST < 40 AND RENTALS.MAKE= RENTCOST.MAKE)

The CNAMEs shown by the execution of this query are:

A. SIMON

B. GREEN, BLACK, SIMON

C. GREEN

D. GREEN, SIMON

22. Data warehouse data are organized and summarized by table, such as CUSTOMER and ADDRESS.

True False

23. In most implementations, the data warehouse and OLAP are stand-alone, independent environments.

True False

24. ____________ query optimization takes place at compilation time.

a. Dynamic
c. Automatic
b. Static
d. Manual

25. Implementing BI in an organization involves capturing not only business data (internal and external) but also the ____.

a. Metadata
c. information
b. Facts
d. rules

Part B

Q 1. Let us consider the following transactions which are being performed online by students of DBST 651 using https://my.umuc.edu. Individual transactions are illustrated in the diagram shown below. Based upon your understanding and reading of Transaction Management & Concurrency Control chapter answer the following:

What is needed to restart if transaction (T3) is aborted with a rollback after checkpoint before the system failure? Explain restart of work assuming the recovery manager using deferred update.

Same as #1 but assume recovery manager is using immediate update approach.

Assuming recovery manager is using deferred update approach. Explain how the restart will impact all the transactions shown in the timeline diagram (illustrated above - T1 to T9).

Same as #3 but the recovery manager is using immediate update approach.

Discuss the impact of restart of work if in case there is a device failure?

Q 2.Using DBST651.sql script which is available under the final exam to respond the following SQL problems:

Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution.

a. For Colorado customers compute the average amount of their orders and the number of orders placed. The result should include the customer number, customer last name, average order amount and the number of orders placed.

b. For Colorado customers compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include customer number, customer last name and the number of unique products ordered.

c. For each employee with a commission less than 0.04, compute the number of orders taken and the average number of products per order. The results should include the employee number, employee last name, number of orders taken and the average number of products per order.

d. For each Connex product compute the number of unique customers who ordered the product in Jan 2007. The results should include the product number, product name and the number of unique customers.

Q 3.

Answer the following in your own words not exceeding ONE page:

a. Explain the differences between a centralized and decentralized approach to database design. Make sure to provide suitable examples to understand your response.

b. What command is used to save changes to the database? What is the syntax for this command? Illustrate with an example.

c. What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal queries? Illustrate with examples different types of sub-query and co-related subqueries (nested etc).

d. What is a view? What is the command used to create a view. Illustrate the command with an example.

Reference no: EM13778576

Questions Cloud

Evaluate the different cost management factors : Evaluate the different cost management factors in an IT project management context. Identify how project management improves the success of information technology projects.
Overcoming socioeconomic challenges : According to Ruby Payne (2005), socioeconomic status impacts students' school experiences. Payne contends that all students need emotional resources to achieve academic success.
How the catalyst works : Preparation, structure and characterization, details about the catalyzed reaction, details on how the catalyst works (describe the catalytic cycle, where available) and application of the process.
Case- sentencing- to punish or to reform : Case: Sentencing: To Punish or to Reform? Research it on the internet and provide a case briefing on it. The variables that need to be discussed for this exercise are: case, facts, issue, holding, majority opinion reasoning, rule of law, applicati..
What is a subquery and when is it used : What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal queries? Illustrate with examples different types of sub-query and co-related subqueries (nested etc).
The cognitive-emotional and physical effects stress : What are some of the connections of stress to disease?Explain two to three effective strategies that can reduce stress.Describe how the strategies can reduce the negative effects of cognitive, emotional, and physical aspects of stress.Include a fi..
Governmental health care organizations issues : How does the information presented on each report affect investor relations? Why do you feel these statements are presented differently?
Budget deficit related to the foreign trade deficit : Using some of the concepts you learned from the module, discuss the questions below: How is the U.S. budget deficit related to the foreign trade deficit?
Highlight the role of the various stakeholders : Please discuss and highlight the role of the various stakeholders in this case. How does this case reflect the important issue of balancing the legal rights of the individual and the rights of society as a whole?

Reviews

Write a Review

 

Database Management System Questions & Answers

  What is the total i/o cost for sorting this file

How many sorted subfiles will there be after the initial pass of the sort, and how long will each subfile be?

  Explain normal form is denormalized table

Which field or fields cause a table to no longer be in third normal form? In which normal form is denormalized table?

  You are hired by northwind as a database administrator you

you are hired by northwind as a database administrator. you are required to integrate some database script solutions

  Write down a 350-word summary explaining two of the most

write a 350-word summary describing two of the most popular database vendors in the marketplace. answer the following

  Create the database that contains each of the relations

Find all Governors General of Australia who were Barons at the time of their appointment. List them by Title and name and date that they were appointed to the position. Order the list by ascending date of appointment.

  Eplain how you can use a cve number to learn more about

1- when you should test web applications for known vulnerabilities? provide at least two examples using the sdlc

  Develop proposal recommends hardware and operating system

Papers should be created with Microsoft Word in the APA style and have a title page, short abstract, body, and conclusion. The "body" should contain between 1000 and 2000 words and include a Works Cited page, headers and other appropriate APA ele..

  Virtual private databasesearching web specially oracle and

virtual private databasesearching web specially oracle and microsoft websites and via talking with any database

  List course along with names of students from database table

List the courses (D-code and C-no), along with the names of the students who are currently taking them. List all the courses (D-code and C-no) that John (i.e., S-Name=''John'') got 'A' grade.

  Types of keys used in relational database

Explain with examples different types of keys used in relational database, how they help relating different tables.

  The primary causes of the data breach

Case Study: Data Breaches and Regulatory Requirements, Describe the data breach incident and the primary causes of the data breach

  Part a sql queriesuse the classicmodels database

part a sql queriesuse the classicmodels database classicmodels.sql from the itc114 interact resources site.answer the

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd