2.99 See Answer

Question: What is the purpose of the EXPLAIN


What is the purpose of the EXPLAIN or EXPLAIN PLAN command?


> What is the meaning of the phrase “slowly changing dimension”?

> What are the two situations in which factless fact tables may apply?

> Explain through common examples why determining grain is critical.

> Match the following terms and definitions: - periodic data - data mart - star schema - data scrubbing - data transformation - grain - reconciled data - dependent data mart - real-time data warehouse - selection - transient data - snowflake schema a. lost

> List and describe the various situations in which it becomes essential to further normalize dimension tables.

> Explain the components of a star schema with figures and suitable examples.

> Describe the characteristics of a surrogate key as used in a data warehouse or data mart.

> Discuss the role of an enterprise data model and metadata in the architecture of a data warehouse.

> FAME (Forondo Artist Management Excellence) Inc. is an artist management company that represents classical music artists (only soloists) both nationally and internationally. FAME has more than 500 artists under its management and wants to replace its spr

> What are the key differences between data warehousing and big data approaches to analytical data management?

> What type of an impact has the significant decrease in the cost of storage space had on data warehouse and data mart design?

> Why is real-time data warehousing called active data warehousing?

> Explain how the characteristics of data for data warehousing is different from the characteristics of data for operational databases.

> List the different roles played by data marts and data warehouses in a data warehouse environment.

> What is meant by a corporate information factory?

> List the 10 essential rules for dimensional modeling.

> Define each of the following terms: a. data warehouse b. data mart c. reconciled data d. derived data e. enterprise data warehouse f. real-time data warehouse g. star schema h. snowflake schema i. grain j. conformed dimension k. static extract l. increme

> What is the role of a DBA? List various regulations and standards for physical database design and their functions.

> Identify some limitations of normalized data as outlined in the text.

> What is a translation or code table? When it should be implemented, and what are its advantages?

> FAME (Forondo Artist Management Excellence) Inc. is an artist management company that represents classical music artists (only soloists) both nationally and internationally. FAME has more than 500 artists under its management and wants to replace its spr

> What decisions have to be made to develop a field specification?

> What are the key decisions in physical database design?

> Discuss the potential advantages, technical challenges, and disadvantages of using cloud-based database provisioning.

> Describe the differences between the IaaS, PaaS, and SaaS models of cloud-based database management solutions.

> How can views be used as part of data security? What are the limitations of views for data security?

> What are the major inputs into physical database design?

> Briefly describe four components of a disaster recovery plan.

> Explain the role of encryption in data security.

> List and describe four common types of database failure.

> Briefly describe four DBMS facilities that are required for database backup and recovery.

> Research various graphics and drawing packages, such as Microsoft Office and SmartDraw, and compare the E-R diagramming capabilities of each. Is each package capable of using the notation found in this text? Is it possible to draw a ternary or higher-ord

> What are the two key types of security policies and procedures that must be established to aid in Sarbanes-Oxley compliance?

> What are the key areas of IT that are examined during a Sarbanes-Oxley audit?

> What is the difference between an authentication scheme and an authorization scheme?

> List and briefly explain how integrity controls can be used for database security.

> Explain the components of a repository system architecture. List and explain the functions supported by a repository engine.

> List and discuss five areas where threats to data security may occur.

> Contrast the following terms: a. horizontal partitioning; vertical partitioning b. repository; data dictionary c. physical file; tablespace d. before image; after image e. normalization; denormalization f. range control; null control g. transaction log;

> Contrast the uses of a data dictionary and a repository in data and database management.

> What are the different elements of a query that can be processed in parallel?

> Explain how query writers can improve query processing performance through knowledge of query optimizers.

> Interview one person from a key business function, such as finance, human resources, or marketing. Concentrate your questions on the following items: How does he or she retrieve data needed to make business decisions? From what kind of system (personal d

> What role can a query optimizer play in the selection of an optimal set of indexes?

> Database servers frequently use one of the many parallel processing architectures. Discuss which elements of a query can be processed in parallel.

> Explain why an index is useful only if there is sufficient variety in the values of an attribute.

> Discuss the trade-off between improved performance for retrieval through use of indexes and degraded performance due to updates of indexed records in a file.

> State 10 rules of thumb for choosing indexes.

> How is storage space in a database divided logically by the DBMS? What is the role of a DBA in managing it?

> Match the following terms to the appropriate definitions: - extent hashing algorithm - rollback - index - checkpoint facility - physical record - pointer - data type - physical file - database recovery a. a detailed coding scheme for representing organi

> Compare the features of the four families of file organization.

> Which index is most suitable for decision support and transaction processing applications that involve online querying? Explain your answer.

> Interview a systems analyst or database analyst and ask questions about how that organization uses data modeling and design tools in the systems development process. Concentrate your questions on how data modeling and design tools are used to support dat

> Explain data replication, forms of partitioning, and their areas of application.

> Explain the reasons why some experts are against the practice of denormalization.

> Why would a database administrator create multiple tablespaces? What is its architecture?

> Explain why it makes sense to first go through the normalization process and then denormalize.

> Explain why normalized relations may not comprise an efficient physical implementation structure.

> Describe three ways to handle missing field values.

> What is a partition view in Oracle? What are its limitations?

> Why must access frequencies be more precisely defined?

> Define each of the following terms: a. file organization b. heap file organization c. sequential file organization d. indexed file organization e. hashed file organization f. denormalization g. composite key h. secondary key i. data type j. data dictiona

> What are the typical components in a Python program that enables a dynamic Web site contain?

> Interview a systems analyst or database analyst and ask questions about the typical composition of an information systems development team. Specifically, what role does a database analyst play in project teams? Is a database analyst used throughout the s

> What are the advantages of PHP? Discuss the drawbacks of PHP and JSP. What is the role of MVC in overcoming these drawbacks?

> What are the six common steps needed to access databases from a typical program?

> What is database-oriented middleware? What does it consist of?

> Describe the advantages and disadvantages of three-tier architectures.

> Contrast the following terms: a. two-tier architecture; three-tier architecture b. fat client; thin client c. optimistic concurrency control; pessimistic concurrency control d. deadlock prevention; deadlock resolution e. shared lock; exclusive lock f. tw

> List several major advantages of the client/server architecture compared with other computing approaches.

> Match each of the following terms with the most appropriate definition: - client server system - application program interface (API) - fat client - database server - middleware - three-tier architecture thin client - database security - lock granularity

> Define each of the following terms: a. application partitioning b. application program interface (API) c. client/server system d. middleware e. three-tier architecture f. locking g. versioning h. deadlock

> Explain some possible purposes of creating a view using SQL. In particular, explain how a view can be used to reinforce data security.

> Explain how to combine queries using the UNION clause.

> Interview systems and database analysts at this organization. Ask them to describe their systems development process. Which does it resemble more: the systems development life cycle or prototyping? Do they use methodologies similar to both? When do they

> Explain the following statement regarding SQL: Any query that can be written using the subquery approach can also be written using the joining approach but not vice versa.

> Explain the relationship between EXISTS and correlated subqueries.

> What are some of the purposes for which you would use correlated subqueries?

> When is it better to use a subquery instead of a join? Answer: Correlated subqueries use the result of the outer query to determine the processing of the inner query. Thus, the inner query varies for each row referenced in the outer query.

> Discuss the differences between an equi-join, natural join, and outer join.

> Research a NoSQL database such as MondoDB or Firebase. Why is it important to consider a metadata-based schema when working with NoSQL databases?

> Discuss the new commands that have been incorporated into SQL2007 and SQL2016, and identify the commands they have replaced in previous versions of SQL.

> What are the key new features of SQL introduced in SQL:2016?

> What is the purpose of the temporal extensions to SQL that were introduced in SQL:2011?

> What are the potential security implications of using embedded or dynamic SQL?

> Consider a major database in this organization, such as one supporting customer interactions, accounting, or manufacturing. What is the architecture for this database? Is the organization using some form of client/server architecture? Interview informati

> Match the following terms to the appropriate definition: - equi-join - derived table - natural join - correlated subquery - outer join - trigger a. returns all records of designated table b. keeps redundant columns c. utilizes values from main query in t

> Provide three reasons for embedding SQL in a 3GL.

> Discuss the differences between triggers and stored procedures.

> Explain three procedures to enforce data integrity.

> What can Persistent Stored Modules be used for?

> Why is it not possible to update a base table via update commands against a view?

> Describe an example in which you would want to use a derived table.

> Explain the use of derived tables.

> Explain the purpose of the WITH CHECK OPTION in a CREATE VIEW SQL command.

> When should we use joining and sub-query techniques?

> Determine the company’s use of intranet, extranet, or other Web-enabled business processes. For each type of process, determine its purpose and the database management system that is being used in conjunction with the networks. Ask what the company’s pla

> Explain why it is necessary to limit the kinds of updates performed on data when referencing data through a view.

> Define each of the following terms: a. dynamic view b. correlated subquery c. materialized view d. base table e. join f. equi-join g. self-join h. outer join i. virtualized table

> Explain the three classes of SQL commands and when they would be used.

2.99

See Answer