Question: Identify some bottlenecks in DBMS performance, and

> 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?

> Describe the three data fragmentation strategies. Give some examples of each.

> 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.

> 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?

> What are the DBA's managerial roles? Describe the managerial activities and services provided by the DBA.

> Describe and characterize the skills desired for a DBA.

> Explain how the DBA plays an arbitration role for an organization's two main assets. Draw a diagram to facilitate your explanation.

> Explain and contrast the differences and similarities between the DBA and DA.

> Explain the DBA department's internal organization, based on the DBLC approach.

> Why and how are new technological advances in computers and databases changing the DBA's role?

> How can the DBA function be placed within the organization chart? What effect(s) will such placement have on the DBA function?

> Write a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of water and a PROD_CATEGORY of sealer. FIGURE P7. 45 WATER-BASED SEALERS PROD SKU PROD DESCRIPT PROD TYPE PR

> Describe the DBA's responsibilities.

> What special considerations must you take into account when introducing a DBMS into an organization?

> Explain the difference between data and information. Give some examples of raw data and information.

> What is ADO.NET, and what two new features make it important for application development?

> How does ADO complement OLE-DB?

> Explain the OLE-DB model based on its two types of objects.

> What is OLE-DB used for, and how does it differ from ODBC?

> What steps are required to create an ODBC data source name?

> What are the three basic components of the ODBC architecture?

> What is the difference between DAO and RDO?

> Write a query to display the eight departments in the LGDEPARTMENT table.

> Define SQL data services and list their advantages.

> Summarize the main advantages and disadvantages of cloud computing services.

> Using the Internet, search for providers of cloud services. Then, classify the types of services they provide (SaaS, PaaS, and IaaS).

> Name and describe the most prevalent characteristics of cloud computing services.

> Name and contrast the types of cloud computing implementation.

> What is cloud computing, and why is it a “game changer”?

> What are ODBC, DAO, and RDO? How are they related?

> What is a JDBC, and what is it used for?

> What are XML schema definition (XSD) documents and what do they do?

> What are document type definition (DTD) documents and what do they do?

> Using the results of the query created in Problem 42, find the total value of the product inventory. The results are shown in Figure P7.43. FIGURE P7.43 Total Value of All Products in Inventory Total Value of Inventory 15084.52

> What is XML, and why is it important?

> What are scripts, and what is their function? (Think in terms of database applications development!)

> What is a Web application server, and how does it work from a database perspective?

> What does this statement mean: The Web is a stateless system? What implications does a stateless system have for database applications developers?

> Search the Internet for Web application servers. Choose one and prepare a short presentation for your class.

> What are Web server interfaces used for? Give some examples.

> What is a DataSet, and why is it considered to be disconnected?

> Give some example of database connectivity options and what they are used for.

> What are the key assumptions made by the Hadoop Distributed File System approach?

> What is polyglot persistence, and why is it considered a new approach?

> Create a query to produce the summary of the value of products currently in inventory. Note that the value of each product is produced by the multiplication of the units currently in inventory and the unit price. Use the ORDER BY clause to match the orde

> Explain why veracity, value, and visualization can also be said to apply to relational databases as well as Big Data.