List and briefly explain the three steps performed during the physical design stage.
> What issues should be considered when resolving data requests in a distributed environment?
> To which transparency feature are the query optimization functions related?
> What is the objective of the query optimization functions?
> Describe the different types of database requests and transactions.
> If indexes are so important, why not index every column in every table? (Include a brief discussion of the role played by data sparsity.)
> In simple terms, the DBMS processes queries in three phases. What are those phases, and what is accomplished in each phase?
> How is the processing of SQL DDL statements (such as CREATE TABLE) different from the processing required by DML statements?
> List the balance characteristics of the customers who have made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table. The results of this query are shown in Figure P7.37. FIGURE P7.37 Ba
> What database statistics measurements are typical of tables, indexes, and resources?
> How are database statistics obtained?
> What are database statistics, and why are they important?
> What is the focus of most performance tuning activities, and why does that focus exist?
> What is database performance tuning?
> What does RAID stand for, and what are some commonly used RAID levels?
> What recommendations would you make for managing the data files in a DBMS with many tables and indexes?
> Most query optimization techniques are designed to make the optimizer’s work easier. What factors should you keep in mind if you intend to write conditional expressions in SQL code?
> What are some general guidelines for creating and using indexes?
> What are optimizer hints and how are they used?
> Using the query results in Problem 35 as your basis, write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the a
> What is the difference between a rule-based optimizer and a cost-based optimizer?
> What is SQL performance tuning?
> What are the different levels of lock granuality?
> What is a lock, and how, in general, does it work?
> What is a scheduler, what does it do, and why is its activity important to concurrency control?
> What is a transaction log, and what is its function?
> What does serializability of transactions mean?
> List and discuss the five transaction properties.
> The DBMS does not guarantee that the semantic meaning of the transaction truly represents the real-world event. What are the possible consequences of that limitation? Give an example.
> What is a consistent database state, and how is it achieved?
> Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in Figure P7.35 as your guide. (Compare this summary to the results shown in Problem 34.) FIGURE P7.35 Number of Invoices and Total Purcha
> What are the four ANSI transaction isolation levels? What type of reads does each level allow?
> What are the three types of database critical events that can trigger the database recovery process? Give some examples for each one.
> Why might it take a long time to complete transactions when an optimistic approach to concurrency control is used?
> What are some disadvantages of time-stamping methods for concurrency control?
> What is a deadlock, and how can it be avoided? Discuss several strategies for dealing with deadlocks.
> What is an exclusive lock, and under what circumstances is it granted?
> What is concurrency control, and what is its objective?
> Why might a page-level lock be preferred over a field-level lock?
> Explain the following statement: a transaction is a logical unit of work.
> What is the data dictionary's function in database design?
> Use a query to show the invoices and invoice totals as shown in Figure P7.34. (Hint: Group by the CUS_CODE.) FIGURE P7.34 Invoice Totals by Customer CUS CODE INV NUMBER Invoice Total 10011 1002 9.98 10011 1004 34.87 10011 1008 399.15 10012 1003 15
> What are business rules? Why are they important to a database designer?
> Discuss the distinction between top-down and bottom-up approaches to database design.
> What is the minimal data rule in conceptual design? Why is it important?
> Discuss the distinction between centralized and decentralized conceptual database design.
> What does the acronym DBLC mean, and what does a DBLC portray?
> What does the acronym SDLC mean, and what does an SDLC portray?
> How do systems analysis and systems development fit into a discussion about information systems?
> What three levels of backup may be used in database recovery management? Briefly describe what each of those three backup levels does.
> List and briefly explain the four steps performed during the logical design stage.
> Create a query to produce the total purchase per invoice, generating the results shown in Figure P7.33. The Invoice Total is the sum of the product purchases in the LINE that corresponds to the INVOICE. FIGURE P7.33 Invoice Totals INV NUMBER Invoic
> What factors are important in a DBMS software selection?
> List and briefly explain the activities involved in the verification of an ER model.
> What steps are required in the development of an ER diagram? (Hint: See Table 9.3.)
> What is an information system? What is its purpose?
> The relational set operators UNION, INTERSECT, and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition?
> What does it mean to say that SQL operators are set-oriented?
> Explain the difference between a regular subquery and a correlated subquery.
> What is a correlated subquery? Give an example.
> What are the three types of results a subquery can return?
> What is a subquery, and what are its basic characteristics?
> Use a query to compute the average purchase amount per product made by each customer. (Hint: Use the results of Problem 31 as the basis for this query.) Your output values must match those shown in Figure P7.32. Note that the Average Purchase Amount is e
> Using tables named T1 and T2, write a query example for each of the three join types you described in Question 2. Assume that T1 and T2 share a common column named C1. Data from Question 2: LEFT [OUTER] JOIN RIGHT [OUTER] JOIN FULL [OUTER] JOIN.
> What three join types are included in the OUTER JOIN classification?
> Given the employee information in Question 11, what is the query output for the UNION ALL query? (List the query output.) Details from Question 11: Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for th
> Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Ch
> What is the difference between UNION and UNION ALL? Write the syntax for each.
> What is a CROSS JOIN? Give an example of its syntax.
> What is dynamic SQL, and how does it differ from static SQL?
> What is embedded SQL, and how is it used?
> What is a stored procedure, and why is it particularly useful? Give an example.
> What is a trigger, and what is its purpose? Give an example.
> Modify the query in Problem 30 to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you would count three product purcha
> What is a sequence? Write its syntax.
> What string function should you use to list the first three characters of a company’s EMP_LNAME values? Give an example, using a table named EMPLOYEE.
> Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 12
> What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?
> What MS Access/SQL Server function should you use to calculate the number of days between the current date and January 25, 1999?
> Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query?
> Given the employee information in Question 11, what is the query output for the MINUS query? (List the query output.) Details from Question 11: Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three e
> Given the employee information in Question 11, what is the query output for the INTERSECT query? (List the query output.) Details from Question 11: Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for th
> What is a recursive join?
> Explain why it would be preferable to use a DATE data type to store date data instead of a character data type.
> Modify the query used in Problem 29 to produce the summary shown in Figure P7.30. FIGURE P7.30 Customer Purchase Summary CUS CODE CUS BALANCE Total Purchases 444.00 153.85 10011 0.00 10012 345.86 10014 0.00 422.77 10015 0.00 34.97 10018 216.55 70.4
> Write an example of a database request.
> What is the difference between the COUNT aggregate function and the SUM aggregate function?
> Explain why the two following commands produce different results. SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;
> Explain the difference between an ORDER BY clause and a GROUP BY clause.
> Rewrite the following WHERE clause without the use of the IN special operator. WHERE V_STATE IN (‘TN’, ‘FL’, ‘GA’)
> What are “referential constraint actions”?
> What is the difference between a column constraint and a table constraint?
> Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results, and then reset the job code to its original value. ATTRIBUTE (FIELD) NAME DATA DECLARATION
> Write the SQL code that will save the changes made to the EMP_1 table. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3)
> Assuming the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP
> Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P7.2. Figure P7.2 The contents of the EMP_1 table Figure P7.1 Structure and contents of the Ch07_ConstructCo dat
> Using the output shown in Figure P7.29 as your guide, generate the listing of customer purchases, including the subtotals for each of the invoice line numbers. (Hint: Modify the query format used to produce the listing of customer purchases in Problem 1
> Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the table below. (Note that the JOB_CODE is the FK to JOB.) ATTRIBUTE (FIELD
> Write a query to display the author ID, first name, last name, and year of birth for all authors born in the decade of the 1980s (See Figure P7.79). FIGURE P7. 79 Authors Born in the 1980s AU ID AU FNAME AU LNAME AU BIRTHYEAR 218 Rachel Beatney Wals
> Write a query to display the book number, title, year of publication, subject, and cost for all books that are on the subjects of “Middleware” or “Cloud,” and that cost more than $70
> Write a query to display the book number, title, and year of publication of all books published after 2013 and on the “Programming” subject (See Figure P7.77). FIGURE P7. 77 Newer Books on Programming BOOK NUM BOO
