Database System Concepts 7th Edition Abraham Silberschatz, Henry F. Korth, S. Sudarshan - Solutions

Spanner provides read-only transactions a snapshot view of data, using multiversion two-phase locking.a. In the centralized multi-version 2PL scheme, read-only transactions never wait. But in Spanner, reads may have to wait. Explain why.b. Using an older timestamp for the snapshot can reduce waits,
In the majority protocol, what should the reader do if it finds different values from different copies, to (a) decide what is the correct value, and (b) to bring the copies back to consistency? If the reader does not bother to bring the copies back to consistency, would it affect correctness of the
Merkle trees can be made short and fat (like B+-trees) or thin and tall (like binary search trees). Which option would be better if you are comparing data across two sites that are geographically separated, and why?
Why is the notion of term important when an election is used to choose a coordinator? What are the analogies between elections with terms and elections used in a democracy?
For correct execution of a replicated state machine, the actions must be deterministic. What could happen if an action is non-deterministic?
Both LSM trees and buffer trees offer benefits to write-intensive workloads, compared to normal B+-trees, and buffer trees offer potentially better lookup performance. Yet LSM trees are more frequently used in Big Data settings. What is the most important reason for this preference?
Consider the optimized technique for counting the number of bits that are set in a bitmap. What are the tradeoffs in choosing a smaller versus a larger array size, keeping cache size in mind?
The stepped merge variant of the LSM tree allows multiple trees per level. What are the tradeoffs in having more trees per level?
Suppose you want to use the idea of a quadtree for data in three dimensions. How would the resultant data structure (called an octree) divide up space?
Find out all performance information your favorite database system provides. Look for at least the following: what queries are currently executing or executed recently, what resources each of them consumed (CPU and I/O), what fraction of page requests resulted in buffer misses (for each query, if
Many applications need to generate sequence numbers for each transaction.a. If a sequence counter is locked in two-phase manner, it can become a concurrency bottleneck. Explain why this may be the case.b. Many database systems support built-in sequence counters that are not locked in two-phase
Suppose you are given a relation r(a, b, c).a. Give an example of a situation under which the performance of equality selection queries on attribute a can be greatly affected by how r is clustered.b. Suppose you also had range selection queries on attribute b. Can you cluster r in such a way that
When a large number of records are inserted into a relation in a short period of time, it is often recommended that all indices be dropped, and recreated after the inserts have been completed.a. What is the motivation for this recommendation?b. Dropping and recreation of indices can be avoided by
Suppose that a database application does not appear to have a single bottleneck; that is, CPU and disk utilization are both high, and all database queues are roughly balanced. Does that mean the application cannot be tuned further? Explain your answer.
When carrying out performance tuning, should you try to tune your hardware (by adding disks or memory) first, or should you try to tune your transactions (by adding indices or materialized views) first. Explain your answer.
Why was the TPC-D benchmark replaced by the TPC-H and TPC-R benchmarks?
Explain what application characteristics would help you decide which of TPCC, TPC-H, or TPC-R best models the application.
Consider a hash function h(x) = x mod 2256, that is, the hash function returns the last 256 bits of x. Does this function havea. Collision resistanceb. Irreversibilityc. Puzzle friendliness Why or why not?
If you were designing a new public blockchain, why might you choose proof of-stake rather than proof-of-work?
If you were designing a new public blockchain, why might you choose proof of-work rather than proof-of-stake?
Explain the distinction between a public and a permissioned blockchain and when each would be more desirable.
Data stored in a blockchain are protected by the tamper-resistance property of a blockchain. In what way is this tamper-resistance more secure in practice than the security provided by a traditional enterprise database system?
In a public blockchain, how might someone determine the real-world identity that corresponds to a given user ID?
What is the purpose of gas in Ethereum?
Suppose we are in an environment where users can be assumed not to be malicious. In that case, what advantages, if any, does Byzantine consensus have over 2PC?
Explain the benefits and potential risks of sharding.
Why do enterprise blockchains often incorporate database-style access?
In what order are blockchain transactions serialized?
Since blockchains are immutable, how is a transaction abort implemented so as not to violate immutability?
Since pointers in a blockchain include a cryptographic hash of the previous block, why is there the additional need for replication of the blockchain to ensure immutability?
How is the difficulty of proof-of-work mining adjusted as more nodes join the network, thus increasing the total computational power of the network? Describe the process in detail.
Why is Byzantine consensus a poor consensus mechanism in a public blockchain?
Explain how off-chain transaction processing can enhance throughput. What are the trade-offs for this benefit?
Choose an enterprise of personal interest to you and explain how blockchain technology could be employed usefully in that business.
The algorithm to generate a canonical cover only removes one extraneous attribute at a time. Use the functional dependencies from Exercise 7.14 to show what can go wrong if two attributes inferred to be extraneous are deleted at once.
Show that there can be more than one canonical cover for a given set of functional dependencies, using the following set of dependencies:X → YZ, Y → XZ, and Z → XY.
Our discussion of lossless decomposition implicitly assumed that attributes on the left-hand side of a functional dependency cannot take on null values. What could go wrong on decomposition, if this property is violated?
An E-R diagram usually models the state of an enterprise at a point in time. Suppose we wish to track temporal changes, that is, changes to data over time. For example, Zhang may have been a student between September 2015 and May 2019, while Shankar may have had instructor Einstein as advisor from
Consider the following lattice structure of generalization and specialization A X B Y C
In SQL, foreign key constraints can reference only the primary key attributes of the referenced relation or other attributes declared to be a superkey using the unique constraint. As a result, total participation constraints on a many-to-many relationship set (or on the “one” side of a
Consider a relation such as sec course, generated from a many-to-one relationship set sec course. Do the primary and foreign key constraints created on the relation enforce the many-to-one cardinality constraint? Explain why. B A R (a) C
Consider a many-to-one relationship R between entity sets A and B. Suppose the relation created from R is combined with the relation created from A. In SQL, attributes participating in a foreign key constraint can be null. Explain how a constraint on total participation of A in R can be enforced
Suppose the advisor relationship set were one-to-one. What extra constraints are required on the relation advisor to ensure that the one-to-one cardinality constraint is enforced?
Consider the relation, r, shown in Figure 5.22. Give the result of the following query:Figure 5.22 select building, room_number, time_slot_id, count(*) from r group by rollup (building, room_number, time_slot_id)
Consider an E-R diagram in which the same entity set appears several times, with its attributes repeated in more than one occurrence. Why is allowing this redundancy a bad practice that one should avoid?
Modify the recursive query in Figure 5.16 to define a relation Figure 5.16 building room_number Garfield 359 Garfield 359 Saucon 651 Saucon 550 Painter 705 Painter 403 time_slot_id A B A C D D course_id BIO-101 1 BIO-101 2 CS-101 2 CS-319 1 MU-199 1 FIN-201 1 sec_id
Show how to enforce the constraint “an instructor cannot teach two different sections in a semester in the same time slot.” using a trigger branch (branch_name, branch_city, assets) customer (customer_name, customer_street, cust omer_city) loan (loan_number, branch_name, amount) borrower
Consider the bank database of Figure 5.21. Let us define a view branch cust as follows:Figure 5.21Suppose that the view is materialized; that is, the view is computed and stored. Write triggers to maintain the view, that is, to keep it up-to-date on insertions to depositor or account. It is not
Given a relation nyse(year, month, day, shares traded, dollar volume) with trading data from the New York Stock Exchange, list each trading day in order of number of shares traded, and show each day’s rank.
Using the relation from Exercise 5.9, write an SQL query to generate a report showing the number of shares traded, number of trades, and total dollar volume broken down by year, each month of each year, and each trading day.Exercise 5.9Given a relation nyse(year, month, day, shares traded, dollar
Express the following query in SQL using no subqueries and no set operations. select ID from student except select s_id from advisor where i ID is not null
Suppose a user creates a new relation r1 with a foreign key referencing another relation r2. What authorization privilege does the user need on r2? Why should this not simply be allowed without any such authorization?
Show how to express the coalesce function using the case construct.
Operating systems usually offer only two types of authorization control for data files: read access and write access. Why do database systems offer so many kinds of authorization?
We expect the constraint “an instructor cannot teach sections in two different classrooms in a semester in the same time slot” to hold.a. Write an SQL query that returns all (instructor, section) combinations that violate this constraint.b. Write an SQL assertion to enforce this constraint (as
Consider the employee database of Figure 4.12. Give an SQL DDL definition of this database. Identify referential-integrity constraints that should hold, and include them in the DDL definition.Figure 4.12. salary) employee (ID, person_name, street, city) works (ID, company_name, company
To test if a query specified in English has been correctly written in SQL, the SQL query is typically executed on multiple test databases, and a human checks if the SQL query result on each test database matches thethe intention of the specification in English.a. In We saw an example of an
To test if a query specified in English has been correctly written in SQL, the SQL query is typically executed on multiple test databases, and a human checks if the SQL query result on each test database matches the intention of the specification in English. a. We saw an example of an
Outer join expressions can be computed in SQL without using the SQL outer join operation. To illustrate this fact, show how to rewrite each of the following SQL queries without using the outer join expression.a. select * from student natural left outer join takesb. select * from student natural
Write the following queries in SQL:a. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outer join, and should not use
Consider the following SQL query that seeks to find a list of titles of all courses taught in Spring 2017 along with the name of the instructor.select name, title from instructor natural join teaches natural join section natural join course where semester = 'Spring' and year = 2017 What is
Using the university schema, write an SQL query to find the ID and name of each instructor who has never given an A grade in any course she or he has taught.
Using the university schema, write an SQL query to find section(s) with maximum enrollment. The result columns should appear in the order “courseid, secid, year, semester, num”.
Using the university schema, write an SQL query to find the number of students in each section. The result columns should appear in the order “courseid, secid, year, semester, num”. You do not need to output sections with 0 students.
Using the university schema, write an SQL query to find the ID and title of each course in Comp. Sci. that has had at least one section with afternoon hours.
Rewrite the preceding query, but also ensure that you include only instructors who have given at least one other non-null grade in some course.
Using the university schema, write an SQL query to find the IDs of those students who have retaken at least three distinct courses at least once. select distinct, ID from ( select course_id, ID from takes group by ID, course_id having count(*) > 1) group by ID having count(course_id) > 2
Using the university schema, write an SQL query to find the name and ID of each History student whose name begins with the letter ‘D’ and who has not taken at least five Music courses.
Using the university schema, write an SQL query to find the names and IDs of those instructors who teach every course taught in his or her department. Order result by name.
Using the university schema, use SQL to do the following: For each student who has retaken a course at least twice (i.e., the student has taken the course at least three times), show the course ID and the student’s ID. Please display your results in order of course ID and do not display duplicate
Using the university schema, write an SQL query to find the names of those departments whose budget is higher than that of Philosophy. List them in alphabetic order.
Consider the query:Rewrite this query without using the with construct. with dept_total (dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name), dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total, dept_total_avg where
Using the university schema, write an SQL query to find the name and ID of those Accounting students advised by an instructor in the Physics department.
Consider the library database of Figure 3.20.Write the following queries in SQL.a. Find the member number and name of each member who has borrowed at least one book published by “McGraw-Hill”.b. Find the member number and name of each member who has borrowed every book published by
Rewrite the where clause where unique (select title from the course) without using the unique construct.
Give an SQL schema definition for the employee database of Figure 3.19. Choose an appropriate domain for each attribute and an appropriate primary key for each relation schema. Include any foreign-key constraints that might be appropriate employee (ID, person_name, street, city) works (ID,
Show that, in SQL, <> all is identical to not in.
Consider the employee database of Figure 3.19. Give an expression in SQL for each of the following queries. a. Give all employees of “First Bank Corporation” a 10 percent raise. b. Give all managers of “First Bank Corporation” a 10 percent raise. c. Delete all tuples in the works relation
Consider the insurance database of Figure 3.17, where the primary keys are underlined. Construct the following SQL queries for this relational database.a. Find the number of accidents involving a car belonging to a person named “John Smith”.b. Update the damage amount for the car with the
Consider the bank database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database.a. Find each customer who has an account at every branch located in “Brooklyn”.b. Find the total sum of all loan amounts in the bank.c. Find the
Write the following queries in SQL, using the university schema.a. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicate names in the result.b. Find the ID and name of each student who has not taken any course offered before 2017.c. For
The SQL-like operator is case sensitive, but the lower function on strings can be used to perform case-insensitive matching. To show how, write a query that finds departments whose names contain the string “sci” as a substring, regardless of the case.
Write the following inserts, deletes, or updates in SQL, using the university schema.a. Increase the salary of each instructor in the Comp. Sci. department by 10%.b. Delete all courses that have never write the following inserts, deletes, or updates in SQL, using the university schema.c. Insert
Suppose you are given a relation grade points that provides conversion from letter grades in the takes relation to numeric scores; for example, an “A” grade could be specified to correspond to 4 points, an “A−” to 3.7 points, a “B+” to 3.3 points, a “B” to 3 points, and so on. The
Consider the bank database of Figure 2.18. Give an expression in the relational algebra for each of the following queries:a. Find each loan number with a loan amount greater than $10000.b. Find the ID of each depositor who has an account with a balance greater than $6000.c. Find the ID of each
Write the following queries in relational algebra, using the university schema.a. Find the ID and name of each instructor in the Physics department.b. Find the ID and name of each instructor in a department located in the building “Watson”.c. Find the ID and name of each student who has taken
Write the following queries in SQL, using the university schema. (We suggest you actually run these queries on a database, using the sample data that we provide on the web site of the book, db book.com. Instructions for setting up a database, and loading sample data, are provided on the above web
Consider the employee database of Figure 2.17. Give an expression in the relational algebra to express each of the following queries:a. Find the ID and name of each employee who works for “BigBank”.b. Find the ID, name, and city of residence of each employee who works for “BigBank”.c. Find
Construct a schema diagram for the bank database of Figure 2.18.Figure 2.18 branch(branch_name, branch_city, assets) customer (ID, customer_name, customer_street, customer_city) loan (loan_number, branch_name, amount) borrower (ID, loan_number) account (account number, branch_name,
Consider the bank database of Figure 2.18. Give an expression in the relational algebra for each of the following queries:a. Find the name of each branch located in “Chicago”.b. Find the ID of each borrower who has a loan in branch “Downtown”. branch(branch_name, branch_city,
Consider the time slot relation. Given that a particular time slot can meet more than once in a week, explain why day and time are part of the primary key of this relation, while end time is not.x
What is the result of first performing the Cartesian product of student and advisor, and then performing a selection operation on the result with the predicate s id = ID?
Describe at least three tables that might be used to store information in a social networking system such as Facebook.
Consider the foreign-key constraint from the dept name attribute of instructor to the department relation. Give examples of inserts and deletes to these relations that can cause a violation of the foreign-key constraint
List two features developed in the 2000s and that help database systems handle data-analytics workloads.
Explain why NoSQL systems emerged in the 2000s, and briefly contrast their features with traditional database systems.
Explain the difference between two-tier and three-tier application architectures. Which is better suited for web applications? Why?
Assume that two students are trying to register for a course in which there is only one open seat. What component of a database system prevents both students from being given that last seat?
List at least two reasons why database systems support data manipulation using a declarative query language such as SQL, instead of just providing a library of C or C++ functions to carry out data manipulation
List four applications you have used that most likely employed a database system to store persistent data.

Showing 200 - 300 of 303

Database System Concepts 7th Edition Abraham Silberschatz, Henry F. Korth, S. Sudarshan - Solutions

Step by Step Answers