Questions and Answers of Database System Concepts

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
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
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
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
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
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
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.
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
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
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)
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
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
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
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
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
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
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
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
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,
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
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
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
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
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
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
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
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)
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
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
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
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
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
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
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
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
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
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)
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
List four applications you have used that most likely employed a database system to store persistent data.

Showing 200 - 300 of 303