Describe how to incrementally maintain the results of the following operations on both insertions and deletions: a. Union and set difference. b. Left outer join.
> Based on Figure 1-5, what are the four perspectives from which you will explore transactional systems in this book? What are the main competencies associated with each of these perspectives?
> How many students were matched with someone in the first five months of the year?
> What are the TutorIDs for tutors who have not yet tutored anyone?
> List the IDs of the tutors who are currently tutoring more than one student.
> What is the average Read score for all students? What are the minimum and maximum Read scores?
> How many tutors have a status of Temp Stop? Which tutors are active?
> Write SQL queries to answer the following questions: a. For each course included in the QUALIFIED table, list CourseID and the number of faculty members qualified to teach it. b. For each section included in the REGISTRATION table, list SectionNo and the
> Write SQL queries to answer the following questions: a. List the numbers of all sections of course ISM 3113 that are offered during the semester “I-2018.” b. List the course IDs and names of all courses that start with
> Write SQL queries to answer the following questions: a. What are the courses included in the Section table? List each course only once. b. List all students in alphabetical order by StudentName. c. List the students who are enrolled in each course in Sem
> Write SQL queries to answer the following questions: a. Which students are enrolled in Database and Networking? (Hint: Use SectionNo for each class so you can determine the answer from the REGISTRATION table by itself.) b. Which instructors cannot teach
> Write SQL queries to answer the following questions: a. How many students are enrolled in Section 2714 in the first semester of 2018? b. What are the numbers of the faculty members who are currently qualified to teach the course ISM 3113? c. Which facult
> Discuss some of the languages that are associated with Internet application development. Classify these languages according to the functionality they provide for each application. It is not necessary that you use the same classification scheme used in th
> Write SQL queries to answer the following questions: a. Which students have an ID number that is less than 50000? b. What is the name of the faculty member whose ID is 4756? c. What is the smallest section number used in the first semester of 2018?
> Write SQL commands for the following: a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the STUDENT table. b. Now write a command that will remove this student from the STUDENT table. c
> Write SQL data definition commands for each of the following queries: a. How would you add an attribute, Class, to the STUDENT table? b. How would you remove the REGISTRATION table? c. What would you need to take into account if you wanted to remove the
> To enforce referential integrity, before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table. Write an SQL assertion that will enforce this constraint.
> Use SQL to define the following view:
> Analyze the database to determine whether or not it is fully normalized.
> Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names, as needed for your SQL version). Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 chara
> A university library system is responsible for tracking information about its books and users. At present, it offers lending facilities to over 5,000 students and has a catalog exceeding 1,000 books and periodicals. It is essential for the library to eff
> A price aggregator system provides users with a onestop portal where they can compare the price of products across multiple Web sites. The portal allows users to not only compare prices but also view customer feedback and reviews of the different Web sit
> Jack Patel is a huge comic book fan who is looking to turn his hobby into a business. Spotting a gap in the market, he intends to create a comic rental business to provide locals with access to comics and tap into the growing interest in superheroes. Jac
> Identify some interactive applications around you that require access to a database to fetch content or information. Look for the middleware used in these applications. You may need to interview a systems analyst or database administrator for this.
> The following attributes form a relation that includes information about the issue and return of books by students from a university library. Students of each department in the university are authorized to issue and return the books after a specific time
> Explore the data included in Table 4-9. Assume that the primary key of this relation consists of two components: Author’s ID (AID) and Book number (BNbr). The relation includes data regarding authors, books, and publishers. In addition,
> Figure 4-40 shows an EER diagram for a university dining service organization that provides dining services to a major university. a. Transform the EER diagram to a set of relations and develop a relational schema. b. Diagram the functional dependencies
> How would Problems and Exercises 4-50 and 4-51 change if a type of fish could be supplied by multiple suppliers? Data from Problem and Exercise 4-51: For Problem and Exercise 4-50, draw the ER diagram based on the normalized relations. Data from Proble
> For Problem and Exercise 4-50, draw the ER diagram based on the normalized relations. Data from Problem and Exercise 4-50: A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants
> A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants to implement a Web-enabled database application. This would enable branch stores to enter data regarding inventory levels, o
> Examine the set of relations in Figure 4-39. What normal form are these in? How do you know this? If they are in 3NF, convert the relations into an EER diagram. What assumptions did you have to make to answer these questions?
> Figure includes an EER diagram for a medium-size software vendor. Transform the diagram into a relational schema that shows referential integrity constraints. In addition, verify that the resulting relations are in 3NF.
> Figure includes an EER diagram describing a publisher specializing in large edited works. Transform the diagram into a relational schema that shows referential integrity constraints. In addition, verify that the resulting relations are in 3NF.
> Figure includes an EER diagram describing a car racing league. Transform the diagram into a relational schema that shows referential integrity constraints. In addition, verify that the resulting relations are in 3NF.
> Search the Internet for some examples of dynamic Web sites other than e-commerce sites. What are the possible limitations of a dynamic Web site compared to a static Web site?
> Transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form.
> Figure 4-35 shows an EER diagram for Vacation Property Rentals. This organization rents preferred properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties. a. Transform the EE
> Table 4-8 shows extracts from a customer’s flight booking confirmation. The booking is identified by the booking reference, which in turn identifies the customer id, their flight origin, and final destination. This booking reference als
> The materials manager at Pine Valley Furniture Company maintains a list of suppliers for each of the material items purchased by the company from outside vendors. Table 4-7 shows the essential data required for this application. a. Draw a dependency diag
> The Public Safety office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on the campus. Table 4-6 shows a portion of this list for the fall semester. (Attribute names are abbreviated to conserve space. a. Con
> Transform Figure 2-15a, attribute version, to 3NF relations. Transform Figure 2-15b, relationship version, to 3NF relations. Compare these two sets of 3NF relations with those in Figure 4-10. What observations and conclusions do you reach by comparing th
> For your answers to the following Problems and Exercises from prior chapters, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form. a. Chapter 3, Problem and
> Transform the relational schema developed in Problem and Exercise 4-37 into an EER diagram. State any assumptions that you have made. Data from Problem and Exercise 4-37: Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a rela
> Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? c. Decompose MANIFEST into a set of 3NF relations. d. Draw a
> Table 4-4 shows a relation called GRADE REPORT for a university. Your assignment is as follows: a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? c. Decompose GRADE REPORT into a
> Suppose your university is offering some courses in business analytics: a six-month certificate course, a two-year regular program, and a three-year part-time program. You are required to design a Web form in HTML that takes students’ names, email addres
> Define each of the following terms: a. data b. information c. metadata d. enterprise resource planning e. data warehouse f. constraint g. database h. entity i. database management system j. data lake k. systems development life cycle l. prototyping m. en
> Suppose that in addition to the operations read and write, we allow an operation prod read(r, P), which reads all tuples in relation r that satisfy predicate P. a. Give an example of a schedule using the prod read operation that ex- habits the phantom ph
> List the ACID properties. Explain the usefulness of each.
> Give an example of an expression defining a materialized view and two situations (sets of statistics for the input relations and the differentials) such that incremental view maintenance is better than recompilation in one situation, and computation is bet
> Consider the query Show how to decor relate this query using the multistep version of the semi join operation.
> Suppose two relations r and shave histograms on attributes r. A and s. A, respectively, but with different ranges. Suggest how to use the histograms to estimate the size of r ⋈ s. Hint: Split the ranges of each histogram further.
> 16.20 Explain how to use a histogram to estimate the size of a selection of the form σ a ≤v(r).
> A set of equivalence rules is said to be complete if, whenever two expressions are equivalent, one can be derived from the other by a sequence of uses of the 16.2.1 complete? Hint: Consider the equivalence σ3=5(r) ≡ { }.
> Consider the two expressions σ θ (E1 ⟕ E2) and σ θ (E1 ⋈ E2). a. Show using an example that the two expressions are not equivalent in general. Give a simple condition on the predicate θ, which if satisfied will ensure that the two expressions are equivale
> Discuss the relative merits of imperative, functional, and declarative languages.
> Consider the following proposed rule for functional dependencies: If α → β and γ → β, then α → γ. Prove that this rule is not sound by showing a relation r that satisfies α → β and γ → β, but does not satisfy α → γ.
> Show how to derive the following equivalences by a sequence of transformations using the equivalence rules in Section 16.2.1. a. σθ1 ∧θ2 ∧θ3 (E) ≡ σθ1 (σθ2 (σθ3 (E))) b. σ θ ∧θ (E1 ⋈θ E2) ≡ σ θ (E1 ⋈θ (σ θ (E2))), where θ2 involves only 1 2 3 1 3 2
> Suppose you have an update query U. Give a simple sufficient condition on U that will ensure that the Halloween problem cannot occur, regardless of the execution plan chosen or the indices that exist.
> Consider a relation r (A, B, C), with an index on attribute A. Give an example of a query that can be answered by using the index only, without looking at the tuples in the relation. (Query plans that use only the index, without accessing the actual rela
> Suppose you want to get answers to r ⋈ s sorted on an attribute of r, and want only the top K answers for some relatively small K. Give a good way of evaluating the query: a. When the join is on a foreign key of r referencing s, where the foreign key att
> Suppose that a B+-tree index on (dept. name, building) is available on relation Depart mint. What would be the best way to handle the following selection? Σ (building
> Suppose you need to sort relation r using sort—merge and merge—join the result with an already sorted relation s. a. Describe how the sort operator is broken into sub operators to model the pipelining in this case. // b. The same idea is applicable even
> Explain how to split the hybrid hash-join operator into sub-operators to model pipelining. Also explain how this split is different from the split for a hash-join operator.
> Write pseudo code for an iterator that implements a version of the sort – merge algorithm where the result of the final merge is pipelined to its consumers. Your pseudo code must define the standard iterator functions open (), next (), and close (). Show w
> Suppose you have to compute a sum(C) (r) as well as A, by sum(C) (r). Describe how to compute these together using a single sorting of r.
> The hash-join algorithm as described in Section 15.5.5 computes the natural join of two relations. Describe how to extend the hash-join algorithm to compute the natural left outer join, the natural right outer join, and the natural full outer join. (Hint
> Use the definition of functional dependency to argue that each of Armstrong’s axioms (reflexivity, augmentation, and transitivity) is sound.
> List two reasons why null values might be introduced into a database.
> Estimate the number of block transfers and seeks required by your solution to Exercise 15.19 for r1 ⋈ r2, where r1 and r2 are as defined in Exercise 15.3.
> Design a variant of the hybrid merge-join algorithm for the case where both relations are not physically sorted, but both have a sorted secondary index on the join attributes.
> Why is it not desirable to force users to make an explicit choice of a query- processing strategy? Are there cases in which it is desirable for users to be aware of the costs of competing query-processing strategies? Explain your answer.
> Suppose you need to sort a relation of 40 gigabytes, with 4-kilobyte blocks, using a memory size of 40 megabytes. Suppose the cost of a seek is 5 milliseconds, while the disk transfer rate is 40 megabytes per second. a. Find the cost of sorting the relat
> An existence bitmap has a bit for each record position, with the bit set to 1 if the record exists, and 0 if there is no record at that position (for example, if the record were deleted). Show how to compute the existence bitmap from other bitmaps. Make
> What trade-offs do write-optimized indices pose as compared to B+-tree indices?
> 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 indices if a node split happened in the file organization? b. W
> Suppose you have to create 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 (say 10 characters). Explain how prefix compression can be used to maximize t
> 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 number of records retrieved n1 and the height h of the tree? b
> Why certain functional dependencies are called trivial functional dependencies?
> The solution presented in Section 14.3.5 to deal with non-unique search keys added an extra attribute to the search key. What effect could this change have on the height of the B+-tree?
> 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
> For each B+-tree of Exercise 14.3, show the steps involved in the following queries: a. Find records with a search-key value of 11. b. Find records with a search-key value between 7 and 17, inclusive.
> What is the difference between a clustering index and a secondary index?
> Some attributes of relations may contain sensitive data, and may be required to be stored in an encrypted fashion. How does data encryption affect index schemes? In particular, how might it affect schemes that attempt to store data in sorted order?
> Spatial indices that can index spatial intervals can conceptually be used to index temporal data by treating valid time as a time interval. What is the problem with doing so, and how is the problem solved?
> When is it preferable to use a dense index rather than a sparse index? Explain your answer.
> 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 object has been accessed in the last n seconds. Suppose
> Give a normalized version of the Index metadata relation, and explain why using the normalized version would result in worse performance.
> In the sequential file organization, why is an overflow block used even if there is, at the moment, only one overflow record?
> Explain what is meant by repetition of information and inability to represent in- formation. Explain why each of these properties may indicate a bad relational- database design.
> List two advantages and two disadvantages of each of the following strategies for storing a relational database: a. Store each relation in one file. b. Store multiple relations (perhaps even the entire database) in one file.
> Explain why the allocation of records to blocks affects database-system performance significantly.
> 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 “Big Bank”. b. Find the ID, name, and city of residence of each e
> In the variable-length record representation, a null bitmap is used to indicate if an attribute has the null value. a. For variable-length fields, if the value is null, what would be stored in the offset and length fields? b. In some applications, tuples ha
> Suppose you have data that should not be lost on disk failure, and the application is write-intensive. How would you store the data?
> What is scrubbing, in the context of RAID systems, and why is scrubbing important?
> 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 is in operation. Which of the RAID levels yields the le
> 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 still important, or is it irrelevant? Explain why.
> How does the remapping of bad sectors by disk controllers affect data-retrieval rates?
> List the physical storage media available on the computers you use routinely. Give the speed with which data can be accessed on each medium.
> Given two relations r(A, B, valid time) and s(B, C, valid time), where valid time de- notes the valid time interval, write an SQL query to compute the temporal Nat intervals overlap and the ∗ operator to compute the intersection of two intermural join of
