Questions and Answers of Database System Concepts

Suppose there is a transaction that has been running for a very long time but has performed very few updates.a. What effect would the transaction have on recovery time with the recovery algorithm of
Consider the RDF representation of information from the university schema as shown in Figure 8.3. Write the following queries in SPARQL.a. Find the titles of all courses taken by any student named
Testing for SQL-injection vulnerability:a. Suggest an approach for testing an application to find if it is vulnerable to SQL injection attacks on text input.b. Can SQL injection occur with forms of
Explain how multiple operations can be executed on a stream using a publish subscribe system such as Apache Kafka.
Show the structure of the file of Figure 13.4 after each of the following steps:a. Insert (24556, Turnamian, Finance, 98000).b. Delete record 2.c. Insert (34556, Thompson, Music, 67000).
Suppose there is a relation r(A, B, C), with a B+-tree index with search key (A, B).a. What is the worst-case cost of finding records satisfying 10 < A < 50 using this index, in terms of the
The class of operations which release locks early are called logical operations. Once such lower-level locks are released, such operations cannot be undone by using the old values of updated data
Compare log-based recovery with the shadow-copy scheme in terms of their overheads for the case when data are being added to newly allocated disk pages.
In the ARIES recovery algorithm:a. If at the beginning of the analysis pass, a page is not in the checkpoint dirty page table, will we need to apply any redo records to it? Why?b. What is RecLSN, and
Database systems are typically implemented as a set of processes (or threads) accessing shared memory.a. How is access to the shared-memory area controlled?b. Is two-phase locking appropriate for
Suppose you wish to handle a workload consisting of a large number of small transactions by using shared-nothing parallelism.a. Is intraquery parallelism required in such a situation? If not, why,
Rewrite the queryselect *from section natural join classroomwithout using a natural join but instead using an inner join with a using condition.
Write an SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no subqueries and no set operations (use an outer join).
Write an SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no subqueries and no set operations (use an outer join).
Under what circumstances would the query select * from student natural full outer join takes natural full outer join courseinclude tuples with null values
Show how to define a view tot credits (year, num credits), giving the total number of credits taken in each year.
Explain why, when a manager, say Satoshi, grants an authorization, the grant should be done by the manager role, rather than by the user Satoshi.
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
Suppose user A, who has all authorization privileges on a relation r, grants select on relation r to public with grant option. Suppose user B then grants select on r to A. Does this cause a cycle in
Suppose you were asked to define a class Meta Display in Java, containing a method static void printable(String r); the method takes a relation name r as input, executes the query “select * from
Repeat Exercise 5.13 using ODBC, defining void printable(char *r) as a function instead of a method.Exercise 5.13 Suppose you were asked to define a class MetaDisplay in Java, containing a
The execution of a trigger can cause another action to be triggered. Most database systems place a limit on how deep the nesting can be. Explain why they might place such a limit.
Consider two entity sets A and B that both have the attribute Xa. If the two Xs are completely unrelated, how should the design be improved?b. If the two Xs represent the same property and it is one
Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of
Using the functional dependencies of Exercise 7.6, compute the canonical cover Fc.Exercise 7.6Compute the closure of the following set F of functional dependencies for relation schema R = (A, B, C,
Consider the schema R = (A, B, C, D, E, G) and the set F of functional dependencies:R is not in BCNF for many reasons, one of which arises from the functional dependency AB → CD. Explain why AB →
Give a lossless decomposition into BCNF of schema R of Exercise 7.1.Exercise 7.1.Suppose that we decompose the schema R = (A, B, C, D, E) into(A, B, C)(A, D, E).Show that this decomposition is a
Although the BCNF algorithm ensures that the resulting decomposition is lossless, it is possible to have a schema and a decomposition that was not generated by the algorithm, that is in BCNF, and is
Consider the schema R = (A, B, C, D, E, G,H) and the set F of functional dependencies:AB → CDD → CDE → BDEH → ABAC → DCUse the 3NF decomposition algorithm to generate a 3NF decomposition of
Show that every schema consisting of exactly two attributes must be in BCNF regardless of the given set F of functional dependencies.
Given a relational schema r(A, B, C, D), does A →→ BC logically imply A →→ B and A →→ C? If yes prove it, or else give a counter example.
Normalize the following schema, with given constraints, to 4NF.books(accessionno, isbn, title, author, publisher)users(userid, name, deptid, deptname)accessionno → isbnisbn → titleisbn →
Provide information about the student named Shankar in our sample university database, including information from the student tuple corresponding to Shankar, the takes tuples corresponding to Shankar
Consider the E-R diagram in Figure 8.7 showing entity set instructor. Give an SQL schema definition corresponding to the E-R diagram, treating phone number as an array of 10 elements, using Orac/8le
Show how to represent the matrices used for computing PageRank as relations. Then write an SQL query that implements one iterative step of the iterative technique for finding PageRank; the entire
Suppose the student relation has an attribute named location of type point, and the classroom relation has an attribute location of type polygon. Write the following queries in SQL using the Post GIS
Consider the schemas for the table people, and the tables students and teachers, which were created under people. Give a relational schema in third normal form that represents the same information.
Suppose you wish to perform keyword querying on a set of tuples in a database, where each tuple has only a few attributes, each containing only a few words. Does the concept of term frequency make
Explain the terms CRUD and REST.
XSS attacks:a. What is an XSS attack?b. How can the referrer field be used to detect some XSS attacks?
Consider the following query using the tumbling window operator:Give an equivalent query using normal SQL constructs, without using the tumbling window operator. You can assume that the timestamp can
Suppose your company has built a database application that runs on a centralized database, but even with a high-end computer and appropriate indices created on the data, the system is not able to
Consider the E-R diagram in Figure 8.9, which contains specializations, using subtypes and subtables.a. Give an SQL schema definition of the E-R diagram.b. Give an SQL query to find the names of all
Consider a carelessly written web application for an online-shopping site, which stores the price of each item as a hidden form variable in the web page sent to the customer; when the customer
Web sites that want to get some publicity can join a web ring, where they create links to other sites in the ring in exchange for other sites in the ring creating links to their site. What is the
Write a servlet and associated HTML code for the following very simple application: A user is allowed to submit a form containing a value, say n, and should get a response containing n “*”
The netstat command (available on Linux and on Windows) shows the active network connections on a computer. Explain how this command can be used to find out if a particular web page is not closing
Why is it important to open JDBC connections using the try-with-resources (try (…){ … } ) syntax?
What is an SQL injection attack? Explain how it works and what precautions must be taken to prevent SQL injection attacks.
Many web sites today provide rich user interfaces using Ajax. List two features each of which reveals if a site uses Ajax, without having to look at the source code. Using the above features, find
Consider another carelessly written web application which uses a servlet that checks if there was an active session but does not check if the user is authorized to access that page, instead depending
A database relation may have the values of certain attributes encrypted for security. Why do database systems not support indexing on encrypted attributes? Using your answer to this question, explain
Consider the Oracle Virtual Private Database (VPD) feature described in Section 9.8.5 and an application based on our university schema.a. What predicate (using a subquery) should be generated to
What is multifactor authentication? How does it help safeguard against stolen passwords?
Hackers may be able to fool you into believing that their web site is actually a web site (such as a bank or credit card web site) that you trust. This may be done by misleading email, or even by
Suppose you wish to create an audit trail of changes to the takes relation.a. Define triggers to create an audit trail, logging the information into a relation called, for example, takes trail. The
Explain what is a challenge-response system for authentication. Why is it more secure than a traditional password-based system?
Suppose you need to store a very large number of small files, each of size say 2 kilobytes. If your choice is between a distributed file system and a distributed key-value store, which would you
Suppose you need to store data for a very large number of students in a distributed document store such as MongoDB. Suppose also that the data for each student correspond to the data in the student
Consider the star schema from Figure 11.2. Suppose an analyst finds that monthly total sales (sum of the price values of all sales tuples) have decreased, instead of growing, from April 2018 to May
Give pseudocode for computing a join r ⋈r. A=s. A s using a single MapReduce step, assuming that the map() function is invoked on each tuple of r and s. Assume that the map() function can find the
What is the conceptual problem with the following snippet of Apache Spark code meant to work on very large data. Note that the collect() function returns a Java collection, and Java collections (from
Fill in the blanks below to complete the following Apache Spark program which computes the number of occurrences of each word in a file. For simplicity we assume that words only occur in lowercase,
Suppose you wish to model the university schema as a graph. For each of the following relations, explain whether the relation would be modeled as a node or as an edge:(i) student(ii)
Apache Spark:a. How does Apache Spark perform computations in parallel?b. Explain the statement: “Apache Spark performs transformations on RDDs in a lazy manner.”c. What are some of the benefits
Suppose you wish to store utility bills for a large number of users, where each bill is identified by a customer ID and a date. How would you store the bills in a key-value store that supports range
Give four ways in which information in web logs pertaining to the web pages visited by a user can be used by the web site.
One of the characteristics of Big Data is the variety of data. Explain why this characteristic has resulted in the need for languages other than SQL for processing Big Data.
Suppose a stream can deliver tuples out of order with respect to tuple timestamps. What extra information should the stream provide, so a stream query processing system can decide when all tuples in
Draw a diagram that shows how the classroom relation of our university example as shown in Appendix A would be stored under a column-oriented storage structure.
Consider the takes relation. Write an SQL query that computes a cross-tab that has a column for each of the years 2017 and 2018, and a column for all, and one row for each course, as well as a row
Classification can be done using classification rules, which have a condition, a class, and a confidence; the confidence is the percentage of the inputs satisfying the condition that fall in the
Why is column-oriented storage potentially advantageous in a database system that supports a data warehouse?
Consider each of the takes and teaches relations as a fact table; they do not have an explicit measure attribute but assume each table has a measure attribute reg_ count whose value is always 1. What
The organization of parts, chapters, sections, and subsections in a book is related to clustering. Explain why, and to what form of clustering.
Some databases use magnetic disks in a way that only sectors in outer tracks are used, while sectors in inner tracks are left unused. What might be the benefits of doing so?
Flash storage:a. How is the flash translation table, which is used to map logical page numbers to physical page numbers, created in memory?b. Suppose you have a 64-gigabyte flash storage system, with
A power failure that occurs while a disk block is being written could result in the block being only partially written. Assume that partially written blocks can be detected. An atomic block write is
A database administrator can choose how many disks are organized into a single RAID 5 array. What are the trade-offs between having fewer disks versus more disks, in terms of cost, reliability,
Storing all blocks of a large file on consecutive disk blocks would minimize seeks during sequential file reads. Why is it impractical to do so? What do operating systems do instead, to minimize the
Operating systems try to ensure that consecutive blocks of a file are stored on consecutive disk blocks. Why is doing so very important with magnetic disks? If SSDs were used instead, is doing so
RAID systems typically allow you to replace failed disks without stopping access to the system. Thus, the data in the failed disk must be rebuilt and written to the replacement disk while the system
What is scrubbing, in the context of RAID systems, and why is scrubbing important?
Consider the relations section and takes. Give an example instance of these two relations, with three sections, each of which has five students. Give a file structure of these relations that uses
It is important to be able to quickly find out if a block is present in the buffer, and if so where in the buffer it resides. Given that database buffer sizes are very large, what (in-memory) data
Suppose your university has a very large number of takes records, accumulated over many years. Explain how table partitioning can be done on the takes relation, and what benefits it could offer.
PostgreSQL normally uses a small buffer, leaving it to the operating system buffer manager to manage the rest of the main memory available for file system buffering. Explain (a) what is the benefit
Standard buffer managers assume each block is of the same size and costs the same to read. Consider a buffer manager that, instead of LRU, uses the rate of reference to objects, that is, how often an
Indices speed query processing, but it is usually a bad idea to create indices on every attribute, and every combination of attributes, that are potential search keys. Explain why.
Construct a B+-tree for the following set of key values: (2, 3, 5, 7, 11, 17, 19, 23, 29, 31)Assume that the tree is initially empty and values are added in ascending order.Construct B+-trees for the
Suppose you have a relation r with nr tuples on which a secondary B+-tree is to be constructed.a. Give a formula for the cost of building the B+-tree index by inserting on record at a time. Assume
What would the occupancy of each leaf node of a B+-tree be if index entries were inserted in sorted order? Explain why.
The leaf nodes of a B+-tree file organization may lose sequentially after a sequence of inserts.a. Explain why sequentially may be lost. b. To minimize the number of seeks in a sequential scan,
Suppose you are given a database schema and some queries that are executed frequently. How would you use the above information to decide what indices to create?
Consider the instructor relation shown in Figure 14.1.a. Construct a bitmap index on the attribute salary, dividing salary values into four ranges: below 50,000, 50,000 to below 60,000, 60,000 to
In write-optimized trees such as the LSM tree or the stepped-merge index, entries in one level are merged into the next level only when the level is full. Suggest how this policy can be changed to
What trade offs do buffer trees pose as compared to LSM trees?
The solution presented to deal with nonunique search keys added an extra attribute to the search key. What effect could this change have on the height of the B+-tree?
Suppose you have to create a B+-tree index on a large number of names, where the maximum size of a name may be quite large (say 40 characters) and the average name is itself large. Explain how prefix
Suppose a relation is stored in a B+-tree file organization. Suppose secondary indices store record identifiers that are pointers to records on disk.a. What would be the effect on the secondary

Showing 1 - 100 of 303