2023年11月25日

数据库代写 | W4111 – Introduction to Databases

本次数据库代写是根据现有的sql脚本，回答下面的问题
W4111 – Introduction to Databases  
Section 02, Fall 2019  
Overview  
This assignment requires written answers to questions. You may have to copy and paste some  
SQL statements or scripts into the answer document you submit. You may also have to insert  
diagrams.  
There are 20 questions worth 5 points each. A homework assignment contributes 10 points to  
your final score. Dividing your score on this assignment by 10 yields the points credited to your  
final score.  
Question 1  
Question: Suppose you have data that should not be lost on disk failure, and the application is  
write-intensive. How would you store the data? (Note: Your answer should consider differences  
between primarily sequential writes and primarily random writes).  
Answer:  
Question 2  
Question: Both database management systems (DBMS) and operating systems (OS) provide  
access to files and implement optimizations for file data access. A DBMS provides significantly  
better performance for data in databases. How does the DBMS do this? What information does  
it use? How does it optimize access? Provide some examples.  
Answer:

Question 3  
Question: Briefly explain CHS addressing and LBA for disks. Which approach is more common  
and why? Is it possible to convert a CHS address to an LBA. If yes, how?  
Answer:  
Question 4  
Question: Explain why the allocation of records to blocks affects database-system performance  
significantly.  
Answer:  
Question 5  
Question: Give benefits and disadvantages of variable length record management versus fixed  
length record management  
Answer:  
Question 6  
Question: Build and draw a B+ tree after inserting the following values. Assume the maximum  
degree of the B+ tree is 3.  
Values: 3, 11, 12, 9, 4, 6, 21, 9, 15, 2  
Answer:

Question 7  
Question: Perform the same insertions in the same order for a hash index. Assume that:  
1
2
3
4
5
. The size of the hash table is 13.  
. The hash function is simple modulo.  
. The size of a bucket is one entry.  
. The size of each bucket is one value.  
. The index algorithm uses linear probing to resolve conflicts/duplicates.  
Answer:  
Question 8  
Question: When is it preferable to use a dense index rather than a sparse index? Explain your  
answer.  
Answer:  
Question 9  
Question: Since indexes improve search/lookup performance, which not create an index on  
very combination of columns?  
Answer:  
Question 10  
Question: Consider the table below. Add indexes that you think are appropriate for the table  
and explain your choices. You may use MySQL Workbench to add the indexes. Paste the  
resulting create statement in the answer section.

Choosing indexes is not possible without understanding use cases/access patterns. Define five  
use cases and the index you define to support the use case. See the answer section for an  
example.  
CREATE TABLE IF NOT EXISTS `customers` (  
id` INT(11) NOT NULL AUTO_INCREMENT,  
company` VARCHAR(50) NULL DEFAULT NULL,  
last_name` VARCHAR(50) NULL DEFAULT NULL,  
first_name` VARCHAR(50) NULL DEFAULT NULL,  
email_address` VARCHAR(50) NULL DEFAULT NULL,  
job_title` VARCHAR(50) NULL DEFAULT NULL,  
business_phone` VARCHAR(25) NULL DEFAULT NULL,  
home_phone` VARCHAR(25) NULL DEFAULT NULL,  
mobile_phone` VARCHAR(25) NULL DEFAULT NULL,  
fax_number` VARCHAR(25) NULL DEFAULT NULL,  
address` LONGTEXT NULL DEFAULT NULL,  
city` VARCHAR(50) NULL DEFAULT NULL,  
state_province` VARCHAR(50) NULL DEFAULT NULL,  
zip_postal_code` VARCHAR(15) NULL DEFAULT NULL,  
country_region` VARCHAR(50) NULL DEFAULT NULL)  
Answer:  
Use Case 1: A user wants to be able find a customer(s) by country_region; country_region and  
state_province; country_region, state_province, city; just by city.  
<Your index definitions go here>  
Question 11  
Question: Assume that:  
1
2
3
. The query processing engine can use four blocks in the buffer pool to hold disk blocks.  
. The records are fixed size, and each block contains 3 records.  
. There are two relations are R and S. Their formats on disk are:

Explain how a partition hash join would perform a natural join. You should illustrate your  
explanation using diagrams of the form below for various steps in the processing:  
Step N:  
The notation (n,X) means the record in file/relation X with value n for the key. Ti is a temporary  
file/relation that is created during the processing. You will likely have to create more than one  
temporary files.  
Answer:  
Question 12  
Question: Give three reasons why a query processing engine might use a sort-merge join  
instead of a hash join? What are the key differences sort-merge and hash join?  
Answer:  
Question 13  
Question:

Answer:  
Question 14  
Question:  
Rewrite/transform the following query into an equivalent query that would be significantly more  
efficient.  
select  
people.playerid, people.nameLast, people.throws,  
batting.teamid, batting.yearid, ab, h, rbi  
from  
(people join batting using(playerid))  
where teamid=’BOS’ and yearID=’1960′;  
Answer:  
Question 15  
Question: Suppose that a B+-tree index on (dept_name, building) is available on relation  
department. (Note: This data comes from the database at  
https://www.db-book.com/db7/index.html)  
What would be the best way to handle the following selection?  
Answer:  
Question 16  
Question: Consider the following relational algebra expression

This is a project on the result of a natural join on R and S. Assume that column a comes from R,  
column b comes from S and that c is the join column. Also assume that both R and S have  
many large columns. Write an equivalent query that will be more efficient, and explain why.  
Answer:  
.
Question 17  
Question:  
Answer:  
Question 18  
Question: Explain the difference between a serial schedule and a serializable schedule.  
Answer:

Question 19  
Question: What are the benefits and disadvantages of strict two phase locking?  
Answer:  
Question 20  
Question: Outline the no-steal and force buffer management policies.  
Answer: