Reference no: EM132856340

MET CS 669 Database Design and Implementation for Business - Boston University

Lab - Subqueries and Distributed Databases

Overview of the Lab

In this lab we learn to work with subqueries, which significantly extend the expressional power of queries. Through the use of subqueries, a single query can extract result sets that could not be extracted without subqueries. Subqueries enable the query creator to ask the database for many complex structures in a single query. This lab teaches you the mechanics crafting SQL queries that harness the power of subqueries to handle more complex use cases.

From a technical perspective, together, we will learn:
• what correlated and uncorrelated subqueries are and the theory supporting both.
• to use subqueries that return a single value, a list of values, and a table of values.
• to use subqueries that use aggregation.
• to address use cases by using uncorrelated subqueries in the column select list, the where clause, and the from clause.
• to address use cases by using correlated subqueries and an EXIST clause in the WHERE clause.
• to simulate horizontal, vertical, and mixed fragmentation and defragmentation for distributed databases.

As a reminder, for each step that requires SQL, make sure to capture a screenshot of the command and the results of its execution.Further, make sure to eliminate unneeded columns from the result set, to name your columns something user-friendly and human readable, and to format any prices as currencies.

1. Create the tables in the schema, including all of their columns, datatypes, and constraints, and populate the tables with data. You can do so by executing the DDL and DML above in your SQL client. You only need to capture one or two demonstrative screenshots for this step. No need to screenshot execution of every line of code (that could require dozens of screenshots).

2. Write two queries which together retrieve the price of a digital thermometer in London. The first query will retrieve the currency ratio for the currency accepted in London. Your second query will hardcode the currency ratio retrieved in the first query, in order to determine the price of the thermometer in London. The first query should be dynamic in that the needed currency should be looked up rather than hardcoded. That is, the currency should be obtained by looking up the currency the store location accepts, not hardcoded by manually eyeballing the tables yourself.

3. In step 2, you determined the price of a digital thermometer in London by writing two queries. For this step, determine the same by writing a single query that contains an uncorrelated subquery.Explain:

a. how your solution makes use of the uncorrelated subquery to help retrieve the result
b. how and when the uncorrelated subquery is executed in the context of the outer query, and
c. the advantages of this solution over your solution for step 2.

4. Imagine a charity in London is hosting a fundraiser to purchase medical supplies for organizations that provide care to people in impoverished areas. The charity is targeting both people with average income as well a few wealthier people, and to this end asks for a selection of products both groups can contribute to purchase. Specifically, for the average income group, they would like to know what products cost less than 25 Euros, and for the wealthier group, they would like to know what products cost more than 300 Euros.

a. Develop a single query to provide them this result, which should contain uncorrelated subqueries and should list the names of the products as well as their prices in Euros.
b. Explain how what each subquery does, its role in the overall query, and how the subqueries were integrated to give the correct results.

Note that the Euro monetary prefix is €.

5. Imagine that Denisha is a traveling doctor who works for an agency that sends her to various locations throughout the world with very little notice. As a result, she needs to know about medical supplies that are available in all store locations (not just some locations). This way, regardless of where she is sent, she knows she can purchase those products. She is also interested in viewing the alternate names for these products, so she is absolutely certain what each product is.

Note: It is important to Denisha that she can purchase the product in any location; only products sold in all stores should be listed, that is, if a product is sold in some stores, but not all stores, it should not be listed.

a. Develop a single query to list out these results, making sure to use uncorrelated subqueries where needed (one subquery will be put into the WHERE clause of the outer query).
b. Explain how what each subquery does, its role in the overall query, and how the subqueries were integrated to give the correct results.

In your thinking about how to address this use case, one item should be brought to your attention - the phrase "all store locations". By eyeballing the data, you can determine the number of locations and hardcode that number, which will satisfy Denisha's request at this present time; however, as the number of locations change over time (with stores opening or closing), such hardcoding would fail. It's better to dynamically determine the total number of locations in the query itself so that the results are correct over time.

6. For this problem you will write a single query to address the same use case as in step 5, but change your query so that the main uncorrelated subquery is in the FROM clause rather than in the WHERE clause. The results should be the same as in step 5, except of course possibly row ordering which can vary. Explain how you integrated the subquery into the FROM clause to derive the same results as step 5.

7. For this problem you will write a single query to address the same use case as in step 5, but change your query to use a correlated query combined with an EXISTS clause. The results should be the same as in step 5, except of course possibly row ordering which can vary. Explain:

a. how your solution makes use of the correlated subquery and EXISTS clause to help retrieve the result
b. how and when the correlated subquery is executed in the context of the outer query.

8. For this problem you will write a query to address the same use case as in step 5, except you will create and use a viewin the FROM clause in place of the subquery. The results should be the same as in step 5, except of course possibly row ordering which can vary.

9. Compare and contrast the construction of the four different queries you developed in steps 5-8, which all address the same use case. What advantages and disadvantages do each construction have over the others? Which do you prefer and why?

Section Two - Distributed Databases

In this section, you explore some distributed database concepts by simulating fragments and communication between distributed database instances, through use of SQL.

10. In this step, you simulate horizontal fragmentation and defragmentation.Complete the following substeps.

a. Create a table that has at least twelve rows and five columns. Make sure the table has a primary key.
b. Define three views that simulate three horizonal fragments based upon some reasonable criteria. Show each view's contents.
c. To simulate defragmentation, write and execute a query that combines the views to re-create the original table.

11. In this step, you simulate vertical fragmentation and defragmentation. Complete the following substeps.
a. Starting with the same logical table as in #10, define two views that simulatetwovertical fragments based upon some reasonable column separation.Show each view's contents.
b. To simulate defragmentation, write and execute a query that combines the views to re-create the original table.

12. In this step, you simulate mixed fragmentation. Complete the following substeps.
a. Create 6 views, where each view represents a combination of one horizontal and one vertical fragment from #10 and #11. All fragments should be covered by the 6 views. Show each view's contents.
b. To simulate defragmentation, write and execute a query that combines the views to re-create the original table.

Attachment:- Database Design.rar