12.1 Using the data distribution scheme shown in Figure 12.8 for the distributed University example, describe at least two strategies for each of the following queries.
Find the class number and the name of all students enrolled in all classes with schedule MWF9.
Find the class number, ID of the teacher, and the campus of all classes being taken by Rosemary Hughes, who is a student at the North campus.
12.2 Consider the query “Find the class number, schedule, and last name of all enrolled students for all classes being taught by Professor Smith of the North campus.” The query is entered at the North campus.
Describe a strategy that does not use the semijoin.
Describe a semijoin strategy for the same query.
12.3 For the distributed Department, Faculty schema described in Section 12.7.2:
Consider the query “For each faculty member, print the first name, last name, and department name.”
Give two different strategies for processing this query without using a semijoin.
Give a strategy using a semijoin.
Consider the query “For each faculty member, print the first name, last name, department name, and chairperson name.”
Explain why this query is more complex than the previous one.
Devise an efficient strategy for executing this query.
12.4 A database is to store listing information for a residential real estate agency that uses a multiple listing service. A multiple listing service is a cooperative service in which different real estate agencies agree to list and show one another’s properties for sale, dividing the sales commission. People who wish to sell homes contact one of the agencies and register with only that agency. Properties to be offered for sale are visited by an agent (the listing agent) who collects information. Once listed, the property can be sold by any agent in any agency (the selling agent). However, when a binder (a declaration of a buyer’s intention to purchase) and a deposit are placed on the property, no agent can show the property to another prospective buyer. If no contract follows within a month, or if the buyer retracts the bid, the property becomes available again. If the sale goes through and a contract is signed by all parties, the binder status and the listing status are marked as sold, and the contract date is recorded in the binder table. At the end of each month, the listing and binder information for all houses sold that month (i.e., for which contracts have been signed) are removed from their corresponding tables, and the information is put in the Sold table. Prospective buyers can register with only one agent. When the property is sold, the commission is divided between the listing agent and the selling agent, who can be the same person. The commission is always 10% of the selling price. Assume the following schema is used:
The attribute features in ActiveListing is a description of the property containing at most 100 characters. The specialRequests attribute in ProspectiveBuyer lists any special needs of the client.
Chose a data distribution plan for this data and justify your choice, using the criteria listed in Section 12.5.
Write a fragmentation schema as follows: Decide how the data should be fragmented to fit the distribution plan. Use relational algebra or SQL commands to create the fragments. For each fragment, identify the location(s) where it will be stored.
Explain how the following query would be answered: Find the names and addresses of all prospective buyers and their agents for a new listing. It is a residence (i.e., type = residence), colonial style, with four bedrooms and an asking price of $800,000.
Explain how the following query would be answered: Find all the houses that are suitable to show a prospective buyer who is interested in a two-story residence with three bedrooms and two baths that costs under $500,000. For each house, show all the listing information, plus the name and telephone number of the listing agent.
Explain how the following query would be answered: For all houses sold last month for which Jane Hayward was the listing agent, find the name of the selling agent.
12.5 Assume that a chain of computer stores that sells computers, components, and parts uses the following global schema for a relational database that keeps information about items at each store:
The company has 20 stores that stock about 15,000 different items. Each item comes from only one supplier, but there are different suppliers for different items. The database keeps track of what items are in what store, what items have been reordered at each store, and the reorder point, which is the number of items that is the minimum each store wishes to keep in stock. When the quantity on hand falls to the reorder point, a new order is placed, unless the item has already been reordered. Each store serves customers in its own geographical area, but if an item is out of stock, the manager can obtain the item from another store. All information is currently stored in a central database. We wish to design a distributed system to make operations more efficient.
Choose a data distribution plan and justify your choice.
Write a fragmentation scheme, using relational algebra or SQL to create the fragments. For each fragment, identify the location(s) where it will be stored.
Explain how the following query would be answered: Find the total quantity of item number 1001 on hand in all stores.
Assume a customer at store A has requested a large quantity of an item that is out of stock in store A. Explain how the database will enable the manager of that store to locate stores that would provide enough items to fill the order, assuming no single store would have sufficient quantities to fill the entire order. Write the SQL or relational algebra query for the request.
12.6 Identify and discuss five benefits of blockchain technology.
12.7 Describe the importance of a consensus algorithm to the development of trust in a blockchain. In addition to the Proof of Work (PoW) consensus algorithm described in Section 12.8.1, investigate consensus algorithms known as Proof of Stake (PoS) and Proof of Activity (PoA). How are they different from PoW?
12.8 Smart contracts have expanded blockchain technology from the transfer of monetary tokens on the Internet to other applications that provide business logic for the interaction of the blockchain participants. Investigate how blockchain technology with the use of smart contracts can be used to support fully decentralized, P2P computing for the following applications.
smart homes
smart cities
smart grids
autonomous cars
government
health care
finances
media distribution and rights management
12.9 Investigate the energy consumption attributed to blockchain technology. How do you think the benefits compare to the costs?