Fundamentals Of Database Systems 7th Edition Ramez Elmasri, Shamkant Navathe - Solutions

Develop a pseudo-language-style algorithm for describing the dynamic programming procedure for join-order selection.
Develop cost functions for an algorithm that consists of two SELECTs, a JOIN, and a final PROJECT, in terms of the cost functions for the individual operations.
What is meant by semantic query optimization? How does it differ from other query optimization techniques?
What are the special features of query optimization in Oracle that we did not discuss in the chapter?
What are semi-join and anti-join? What are the join selectivity and join cardinality parameters associated with them? Provide appropriate formulas.
Discuss the different types of parameters that are used in cost functions. Where is this information kept?
Discuss the cost components for a cost function that is used to estimate query execution cost. Which cost components are used most often as the basis for cost functions?
What is the optimization approach based on dynamic programming? How is it used during query optimization?
What is meant by cost-based query optimization?
How many different join orders are there for a query that joins 10 relations? How many left-deep trees are possible?
How does a query tree represent a relational algebra expression? What is meant by an execution of a query tree? Discuss the rules for transformation of query trees, and identify when each rule should be applied during optimization.
What is meant by the term heuristic optimization? Discuss the main heuristics that are applied during query optimization.
What is a query execution plan?
Under what conditions is pipelined parallel execution of a sequence of operations prevented?
What are the parallel implementations of join?
What are the three types of parallel architectures applicable to database systems? Which one is most commonly used?
What is the iterator concept? What methods are part of an iterator?
How are outer join and non–equi-join implemented?
Discuss alternative ways of eliminating duplicates when a “SELECT Distinct ” query is evaluated.
Discuss the different algorithms for implementing each of the following relational operators and the circumstances under which each algorithm can be used: SELECT, JOIN, PROJECT, UNION, INTERSECT, SET DIFFERENCE, CARTESIAN PRODUCT.Give examples of a conjunctive selection and a disjunctive selection
How are large tables that do not fit in memory sorted? Give the overall procedure.
Discuss semi-join and anti-join as operations to which nested queries may be mapped; provide an example of each.
Discuss the reasons for converting SQL queries into relational algebra queries before optimization is done.
Outline an algorithm for deletion from a B+-tree.
Suppose that several secondary indexes exist on nonkey fields of a file, implemented using option 3 of Section 17.1.3; for example, we could have secondary indexes on the fields Department_code, Job_code, and Salary of the EMPLOYEE file of Exercise 17.18. Describe an efficient way to search for and
Algorithm 17.1 outlines the procedure for searching a nondense multilevel primary index to retrieve a file record. Adapt the algorithm for each of the following cases:a. A multilevel secondary index on a nonkey nonordering field of a file.Assume that option 3 of Section 17.1.3 is used, where an
Under which of the categories mentioned in Question 13.1 do XML documents fall? What about self-describing data?
Describe the mismatch between processor and disk technologies.
What are the main goals of the RAID technology? How does it achieve them?
How does disk mirroring help improve reliability? Give a quantitative example.
What characterizes the levels in RAID organization?
What are the highlights of the popular RAID levels 0, 1, and 5?
What are storage area networks? What flexibility and advantages do they offer?
Describe the main features of network-attached storage as an enterprise storage solution.
What are SATA, SAS, and FC protocols?
What are solid-state drives (SSDs) and what advantage do they offer over HDDs?
What is the function of a buffer manager? What does it do to serve a request for data?
What are some of the commonly used buffer replacement strategies?
What are optical and tape jukeboxes? What are the different types of optical media served by optical drives?
What is automatic storage tiering? Why is it useful?
What is object-based storage? How is it superior to conventional storage systems?
Suppose that only 80% of the STUDENT records from Exercise 16.28 have a value for Phone, 85% for Major_dept_code, 15% for Minor_dept_code, and 90% for Degree_program; and suppose that we use a variable-length record file. Each record has a 1-byte field type for each field in the record, plus the
Load the records of Exercise 16.31 into expandable hash files based on extendible hashing. Show the structure of the directory at each step, and the global and local depths. Use the hash function h(K) = K mod 128.Exercise 16.31What are optical and tape jukeboxes? What are the different types of
Load the records of Exercise 16.31 into an expandable hash file, using linear hashing. Start with a single disk block, using the hash function h0 = K mod 20, and show how the file grows and how the hash functions change as the records are inserted. Assume that blocks are split whenever an overflow
Compare the file commands listed in Section 16.5 to those available on a file access method you are familiar with.
Suppose that we have an unordered file of fixed-length records that uses an unspanned record organization. Outline algorithms for insertion, deletion, and modification of a file record. State any assumptions you make.
Suppose that we have an ordered file of fixed-length records and an unordered overflow file to handle insertion. Both files use unspanned records. Outline algorithms for insertion, deletion, and modification of a file record and for reorganizing the file. State any assumptions you make.
Suppose that we have a hash file of fixed-length records, and suppose that overflow is handled by chaining. Outline algorithms for insertion, deletion, and modification of a file record. State any assumptions you make.
Write pseudocode for the insertion algorithms for linear hashing and for extendible hashing.
Write program code to access individual fields of records under each of the following circumstances. For each case, state the assumptions you make concerning pointers, separator characters, and so on. Determine the type of information needed in the file header in order for your code to be general
Define the following terms: indexing field, primary key field, clustering field, secondary key field, block anchor, dense index, and nondense (sparse) index.
What are the differences among primary, secondary, and clustering indexes? How do these differences affect the ways in which these indexes are implemented? Which of the indexes are dense, and which are not?
Why can we have at most one primary or clustering index on a file, but several secondary indexes?
How does multilevel indexing improve the efficiency of searching an index file?
What is the order p of a B-tree? Describe the structure of B-tree nodes.
What is the order p of a B+-tree? Describe the structure of both internal and leaf nodes of a B+-tree.
How does a B-tree differ from a B+-tree? Why is a B+-tree usually preferred as an access structure to a data file?
Explain what alternative choices exist for accessing a file based on multiple search keys.
What is partitioned hashing? How does it work? What are its limitations?
What is a grid file? What are its advantages and disadvantages?
Show an example of constructing a grid array on two attributes on some file.
What is a fully inverted file? What is an indexed sequential file?
How can hashing be used to construct an index?
What is the concept of function-based indexing? What additional purpose does it serve?
What is the difference between a logical index and a physical index?
What is column-based storage of a relational database?
Write PHP Web interfaces for the queries specified in Exercise 6.18.
Repeat Laboratory Exercises 8.34 through 8.38 in domain relational calculus (DRC) by using the DRC interpreter.Exercises 8.34Specify and execute the following queries in relational algebra (RA) using the RA interpreter on the COMPANY database schema in Figure 5.5.a. List the names of all employees
Rewrite the domain relational calculus queries that followed Q0 in Section 8.7 in the style of the abbreviated notation of Q0A, where the objective is to minimize the number of domain variables by writing constants in place of variables wherever possible.
Specify the following queries in relational algebra on the database schema given in Exercise 5.15:Exercise 5.15Consider the following relations for a database that keeps track of business trips of salespersons in a sales office:SALESPERSON(Ssn, Name, Start_year, Dept_no)TRIP(Ssn, From_city,
Specify the following queries in relational algebra on the database schema given in Exercise 5.14:a. List the Order# and Ship_date for all orders shipped from Warehouse# W2.b. List the WAREHOUSE information from which the CUSTOMER named Jose Lopez was supplied his orders. Produce a listing: Order#,
Consider the LIBRARY relational database schema shown in Figure 8.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 8.14, as in the notation of Figure 5.7. Write down relational expressions for the following
Define the following terms with respect to the domain calculus: domain variable, range relation, atom, formula, and expression.
Define the following terms with respect to the tuple calculus: tuple variable, range relation, atom, formula, and expression.
Discuss the meanings of the existential quantifier (∃) and the universal quantifier (∀).
In what sense does relational calculus differ from relational algebra, and in what sense are they similar?
How are the OUTER JOIN operations different from the INNER JOIN operations? How is the OUTER UNION operation different from UNION?
What is the FUNCTION operation? For what is it used?
What role does the concept of foreign key play when specifying the most common types of meaningful join operations?
List the operations of relational algebra and the purpose of each.
Consider the following view, DEPT_SUMMARY, defined on the COMPANY database in Figure 5.6:State which of the following queries and updates would be allowed on the view. If a query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give
Specify the following views in SQL on the COMPANY database schema shown in Figure 5.5.a. A view that has the department name, manager name, and manager salary for every departmentb. A view that has the employee name, supervisor name, and employee salary for each employee who works in the
In SQL, specify the following queries on the database in Figure 5.5 using the concept of nested queries and other concepts described in this chapter.a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.b. Retrieve the
Discuss how NULLs are treated in comparison operators in SQL. How are NULLs treated when aggregate functions are applied in an SQL query? How are NULLs treated if they exist in grouping attributes?
Consider the AIRLINE relational database schema shown in Figure 5.8, which describes a database for airline flight information. Each FLIGHT is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. Each FLIGHT_LEG has scheduled arrival and
Discuss the various reasons that lead to the occurrence of NULL values in relations.
Discuss the characteristics of relations that make them different from ordinary tables and files.
Why are tuples in a relation not ordered?
Which of the following EER diagrams is/are incorrect and why? State clearly any assumptions you make.a.b.c. E1 E R 1 E2
Design a database to keep track of information for an art museum. Assume that the following requirements were collected:■ The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist (if known), a Year (when it was created, if known), a Title, and a Description. The
Specify the following queries in relational algebra on the database schema in Exercise 5.17:a. For the salesperson named ‘Jane Doe’, list the following information for all the cars she sold: Serial#, Manufacturer, Sale_price.b. List the Serial# and Model of cars that have no options.c. Consider
Consider the two tables T1 and T2 shown in Figure 8.15. Show the results of the following operations:a. T1 T1.P = T2.A T2b. T1 T1.Q = T2.B T2c. T1 T1.P = T2.A T2d. T1 T1.Q = T2.B T2e. T1 ∪ T2f. T1 (T1.P = T2.A AND T1.R = T2.C) T2Figure 8.15 TABLE T1 TABLE T2 PaR A в с 10 a 10 b 6 15 b 8. 25 3
Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 5.6.a. Retrieve the names of all employees in department 5 who
Show the result of each of the sample queries in Section 8.5 as it would apply to the database state in Figure 5.6.Figure 5.6 ΕMPLOYEΕ Sen 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 333445555 1955-12-08 638 Voss, Houston, TX 999887777 1968-01-19 3321 Castle, Spring, TX Fname
When is a query language called relationally complete?
What is meant by a safe expression in relational calculus?
Discuss the various types of inner join operations. Why is theta join required?
Discuss how each of the following constructs is used in SQL, and discuss the various options for each construct. Specify what each construct is useful for.a. Nested queriesb. Joined tables and outer joinsc. Aggregate functions and groupingd. Triggerse. Assertions and how they differ from triggersf.
Specify the following queries on the database in Figure 5.5 in SQL. Show the query results if each query is applied to the database state in Figure 5.6.a. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for
Describe conceptually how an SQL retrieval query will be executed by specifying the conceptual order of executing each of the six clauses.

Showing 300 - 400 of 606

Fundamentals Of Database Systems 7th Edition Ramez Elmasri, Shamkant Navathe - Solutions

Step by Step Answers