Describe the three data fragmentation strategies. Give some examples of each.
> Write the SQL command to change the movie year for movie number 1245 to 2010.
> Write a query that displays the first and last name of every patron (See Figure P7.66). (50 rows) FIGURE P7. 66 All Patron Names PAT FNAME PAT LNAME robert Kelsey Cedric carter Коch Baldwin Vera Alvarado Alan Martin Cory Peggy Tony Betsy Barry Marsh
> Write the SQL command to save the rows inserted in Problem 97.
> The following tables provide a very small portion of the data that will be kept in the database. This data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that w
> Write the SQL code to create the table structures for the entities shown in Figure P7.96. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attri
> Write a query to display the total value of all books in the library (See Figure P7.95). FIGURE P7. 95 Total of All Books Library Value 1499
> Write a query to display the author ID and the number of books written by that author. Sort the results in descending order by number of books, then in ascending order by author ID (See Figure P7.94). FIGURE P7. 94 Number of Books per Author AU ID
> Write a query to display the subject and the number of books in each subject. Sort the results by the number of books in descending order, then by subject name in ascending order (See Figure P7.93). FIGURE P7. 93 Number of Books per Subject BOOK SU
> Write a query to display the number of different patrons who have ever checked out a book (See Figure P7.92). FIGURE P7. 92 Different Patrons to Checkout a Book DIFFERENT PATRONS 33
> Write a query to display the lowest book cost in the system (See Figure P7.91). FIGURE P7. 91 Least Expensive Book Price Least Expensive 49.95
> Write a query to display the highest book cost in the system (See Figure P7.90). FIGURE P7. 90 Most Expensive Book Price Most Expensive 129.95
> What basic database design strategies exist, and how are such strategies executed?
> Write a query that displays the book title, cost and year of publication for every book in the system. FIGURE P7. 65 The Ch07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date ---H- P
> Write a query to display the number of books that are available (not currently checked out) (See Figure P7.89). FIGURE P7. 89 Number of Books not Currently Checked Out Available Books 14
> Write a query to display the number of different book subjects in the FACT system (See Figure P7.88). FIGURE P7. 88 Number of Different Subjects Number of Subjects 4
> Write a query to display the number of books in the FACT system (See Figure P7.87). FIGURE P7. 87 Number of Books Number of Books 20
> Write a query to display the author ID, first name, last name, and year of birth for all authors. Sort the results in descending order by year of birth, and then in ascending order by last name (See Figure P7.86). (Note that some DBMS sort NULLs as bein
> Write a query to display the checkout number, book number, patron ID, check out date, and due date for all checkouts that have not yet been returned. Sort the results by book number (See Figure P7.85). FIGURE P7. 85 Unreturned Checkouts CHECK NUM B
> Write a query to display the author ID, first and last name of all authors whose year of birth is known (See Figure P7.84). FIGURE P7. 84 Authors with Known Birth Year AU ID AU FNAME AU_LNAME Reeves 185 Benson 218 Rachel Beatney 251 Hugo Bruer 273 R
> Write a query to display the author ID, first and last name of all authors whose year of birth is unknown (See Figure P7.83). FIGURE P7. 83 Authors with Unknown Birth Year AU ID AU FNAME AU LNAME 229 Carmine Salvadore 262 Xia Chiang 559 Rachel McGil
> Write a query to display the patron ID, first and last name, and patron type for all patrons whose last name begins with the letter “C” (See Figure P7.82). FIGURE P7. 82 Patrons Whose Last Name Starts with â€
> Write a query to display the patron ID, first and last name of all patrons that are students (See Figure P7.81). (44 rows) FIGURE P7. 81 Student Patrons PAT ID PAT FNAME PAT LNAME 1166 Vera Alvarado 1171 Peggy 1172 Tony 1174 Betsy Marsh Miles Malone
> Write a query to display the book number, title, and year of publication for all books that contain the word “Database” in the title, regardless of how it is capitalized (See Figure P7.80). FIGURE P7. 80 Book Titles Co
> Write a query to display the manager name, department name, department phone number, employee name, customer name, invoice date, and invoice total for the department manager of the employee who made a sale to a customer whose last name is Hagan on May 18
> What is the relationship between the SDLC and the DBLC?
> Create a stored procedure named prc return_video enter data about the return of videos that had been rented. The procedure should satisfy the following requirements. a. The video number will be provided as a parameter. b. Verify the video number exists
> Create a stored procedure named prc_new_detail to insert new rows in the DETAILRENTAL table. The procedure should satisfy the following requirements. a. The video number will be provided as a parameter. b. Verify the video number exists in the VIDEO tab
> Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table. The procedure should satisfy the following conditions. a. The membership number will be provided as a parameter. b. Use a Count() function to verify that the membersh
> Create a sequence named rent_num_seq to start with 1100, increment by 1, and not cache any values.
> Create a trigger named trg_mem_balance that will maintain the correct value in the membership balance in the MEMBERSHIP table when videos are returned late. The trigger should execute as an AFTER trigger when the due date or return date attributes are u
> Create a trigger named trg_late_return that will write the correct value to DETAIL_DAYSLATE in the DETAILRENTAL table whenever a video is returned. The trigger should execute as a BEFORE trigger when the DETAIL_RETURNDATE or DETAIL_DUEDATE attributes ar
> Update the PRICE table to place the values shown in the following table in the PRICE_RENTDAYS attribute. PRICE_CODE PRICE_RENTDAYS 1 5 2 3 3 5 4 7
> Alter the PRICE table to include an attribute named PRICE_RENTDAYS to store integers up to 2 digits. The attribute should not accept null values, and should have a default value of 3.
> Update the VID_STATUS attribute of the VIDEO table using a subquery to set the VID_STATUS to “OUT” for all videos that have a null value in the DETAIL_RETURNDATE attribute of the DETAILRENTAL table.
> Write a query to display the brand ID, brand name, brand type, and average price of products for the brand that has the largest average product price. FIGURE P7. 63 Brand with highest average price BRAND ID BRAND NAME BRAND TYPE AVGPRICE 29 BUSTERS
> Write a query to display the largest average product price of any brand. FIGURE P7. 62 Largest average brand price LARGEST AVERAGE 22.59
> Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name. FIGURE P7. 61 Total value of invoices completed by ea
> Write a query to display the vendor ID, vendor name, brand name, and number of products of each brand supplied by each vendor. Sort the output by vendor name and then by brand name. FIGURE P7. 60 Number of products of each brand supplied by each vendor
> Describe the following: TCP/IP, Router, HTML, HTTP, and URL.
> Write a query to display the department number, department name, department phone number, employee number, and last name of each department manager. Sort the output by department name. FIGURE P7. 59 Department managers DEPT PHONE EMP NUM EMP LNAME 8
> Write a query to display the customer code, first name, last name, and sum of all invoice totals for customers with cumulative invoice totals greater than $1,500. Sort the output by the sum of invoice totals in descending order. FIGURE P7. 58 List of cu
> Write a query to display the employee number, first name, last name, and largest salary amount for each employee in department 200. Sort the output by largest salary in descending order. FIGURE P7. 57 Largest salary amount for each employee in departmen
> Write a query to display the department number and most recent employee hire date for each department. Sort the output by department number. FIGURE P7. 56 Most recent hire in each department DEPT NUM MOSTRECENT 200 6/8/2005 250 12/15/2015 280 4/16/
> Write a query to display the brand ID, brand name, and average price of products of each brand. Sort the output by brand name. (Results are shown with the average price rounded to two decimal places.) FIGURE P7. 55 Average price of products of each bran
> Write a query to display the total inventory—that is, the sum of all products on hand for each brand ID. Sort the output by brand ID in descending order. FIGURE P7. 54 Total inventory of each brand of products BRAND ID TOTALINVENT
> Write a query to display the number of products within each base and type combination. FIGURE P7. 53 Number of products of each base and type PROD BASE PROD TYPE NUMPRODUCTS Solvent Exterior 67 Solvent Interior 83 Water Exterior 39 Water Interior 6
> Write a query to display the number of products in each category that have a water base. FIGURE P7. 52 Number of water-based products in each category PROD CATEGORY NUMPRODUCTS Cleaner 2 Filler Primer 16 Sealer 1 Тоp Сoat 81
> Write a query to display a brand name and the number of products of that brand that are in the database. Sort the output by the brand name. FIGURE P7. 51 Number of products of each brand BRAND NAME NUMPRODUCTS BINDER PRIME 27 BUSTERS 25 FORESTERS B
> What is data replication, and what are the three replication strategies?
> Explain the need for the two-phase commit protocol. Then describe the two phases.
> Define and explain the different types of distribution transparency.
> Write a query to display the employee number, last name, e-mail address, title, and department name of each employee whose job title ends in the word “ASSOCIATE.” Sort the output by department name and employee title.
> Suppose you are asked to evaluate a DBMS in terms of lock granularity and the different locking levels. Create a simple database environment in which these features would be important.
> List and explain the transparency features of a DDBMS.
> What are the components of a DDBMS?
> What is a fully distributed database management system?
> Explain the difference between distributed database and distributed processing.
> What are the disadvantages of the DDBMS?
> What are the advantages of the DDBMS?
> List and discuss some of the factors that influenced the evolution of the DDBMS.
> Describe the evolution from centralized DBMSs to distributed DBMSs.
> Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.
> What type of integrity is enforced when a primary key is declared?
> Write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2015, and July 31, 2015. If a customer purchased more than one such product, display the
> Explain why the following command would create an error, and what changes could be made to fix the error. SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;
> In a SELECT query, what is the difference between a WHERE clause and a HAVING clause?
> Describe and contrast the information needs at the strategic, tactical, and operational levels in an organization. Use examples to explain your answer.
> Define security and privacy. How are these two concepts related?
> Using a single sentence, explain the role of databases in organizations. Then explain your answer.
> How and why did database management systems become the organizational data management standard in organizations? Discuss some of the advantages of the database approach over the file-system approach.
> Suppose that you are a DBA. What data dimensions would you describe to top-level managers to obtain their support for endorsing the data administration function?
> In Oracle, what is a database profile?
> In Oracle, what is a data-file? How does it differ from a file systems file?
> Explain the interactions among end user, data, information, and decision-making. Draw a diagram and explain the interactions.
> Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 83731, 83745, and 84039. Sort the output by employee number and salary â&#
> In Oracle, what is a database role?
> In Oracle, what is a tablespace?
> What is the tool used by Oracle to create users?
> How have cloud-based data services affected the DBA’s role?
> Identify and explain some of the critical success factors in the development and implementation of a good data administration strategy.
> Briefly explain the concepts of information engineering (IE) and information systems architecture (ISA). How do these concepts affect the data administration strategy?
> What characteristics do a CASE tool and a DBMS have in common? How can these characteristics be used to enhance the data administration?
> Using SQL statements, give some examples of how you would use the data dictionary to monitor the security of the database.
> Define the concept of a data dictionary, and discuss the different types of data dictionaries. If you were to manage an organization's entire data set, what characteristics would you look for in the data dictionary?
> What are security vulnerabilities? What is a security threat? Give some examples of security vulnerabilities that exist in different IS components.
> Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 300 or have the title “CLERK I.” Sort the output by last name and then by first name.
> What is data quality, and why is it important?
> What are the levels of data confidentiality?
> How do you normally define security? How is your definition of security similar to or different from the definition of database security in this chapter?
> What are the typical activities involved in the maintenance of the DBMS and its utilities and applications? Would you consider application performance tuning to be part of the maintenance activities? Explain your answer.
> Identify some bottlenecks in DBMS performance, and then propose some solutions used in DBMS performance tuning.
> Why are testing and evaluation of the database and applications not done by the same people who are responsible for the design and implementation? What minimum standards must be met during the testing and evaluation process?
> Describe the activities that are typically associated with the design and implementation services of the DBA technical function. What technical skills are desirable in the DBA's personnel?
> Assume that your company assigned you the responsibility of selecting the corporate DBMS. Develop a checklist for the technical and other aspects involved in the selection process.
> Discuss the importance and characteristics of database data backup and recovery procedures. Then describe the actions that must be detailed in backup and recovery plans.
> Protecting data security, privacy, and integrity are important database functions. What activities are required in the DBA's managerial role of enforcing these functions?
> Write a query to display the first name, last name, and e-mail address of employees hired from January 1, 2001, to December 31, 2010. Sort the output by last name and then by first name. FIGURE P7. 46 Employees hired from 2003–2012
> Explain the DBA's managerial role in the definition and enforcement of policies, procedures, and standards.
> Define dirty data and identify some of its sources.
> What DBA activities are used to support end users?
