Explain why it is necessary to limit the kinds of updates performed on data when referencing data through a view.
> What is the purpose of the EXPLAIN or EXPLAIN PLAN command?
> 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
> 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.
> Describe the components and structure of a typical SQL environment.
> What are some of the advantages and disadvantages of an SQL standard?
> Describe a relational DBMS (RDBMS), its underlying data model, its data storage structures, and how data relationships are established.
> Explain what capabilities the new temporal features added to the SQL standard in SQL:2011.
> What are SQL-92, SQL:1999, SQL:2011, and SQL:2016? Briefly describe how SQL:2016 differs from SQL:1999.
> What is the purpose of the EXPLAIN or EXPLAIN PLAN command?
> Explain the difference between the WHERE and HAVING clause.
> Talk with a database administrator or designer from the organization. What type of metadata does this organization maintain about its databases? Why did the organization choose to keep track of these and not other metadata? What tools are used to maintai
> What can be changed about a table definition using the SQL command ALTER? Can you identify anything about a table definition that cannot be changed using the ALTER command?
> Explain the purpose of the CHECK clause within a CREATE TABLE SQL command.
> Contrast the following terms: a. scalar aggregate; vector aggregate b. DDL; DML c. catalog; schema
> How can an SQL command be structured to allow parallel execution?
> State four rules for choosing indexes for a relational database.
> What is a materialized view, and when would it be used?
> SQL:2006 and SQL:2008 introduced a new key word, MERGE. Explain how using this key word allows one to accomplish updating and merging data into a table using one command rather than two.
> What is an identity column? Explain the benefits of using the identity column capability in SQL.
> What considerations should be kept in mind when using indexing?
> Explain why SQL is called a set-oriented language.
> Investigate whether the organization follows more of a traditional file processing approach or the database approach to organizing data. How many different databases does the organization have? Try to draw a figure, similar to Figure 1-2, to depict some
> Contrast transactional and analytical data management approaches.
> How do you determine the order in which the rows in a response to an SQL query appear? What options do you have when specifying this order?
> In what clause of a SELECT statement is an IN operator used? What follows the IN operator? What other SQL operator can sometimes be used to perform the same operation as the IN operator? Under what circumstances can this other operator be used?
> How is the HAVING clause different from the WHERE clause?
> Match the following terms to the appropriate definitions: - referential integrity constraint - SQL: 2016 - Null value - scalar aggregate - vector aggregate - catalog - schema - host language a. list of values b. description of a database c. missing or no
> If an SQL statement includes a GROUP BY clause, the attributes that can be requested in the SELECT statement will be limited. Explain that limitation.
> What is the evaluation order for the Boolean operators (AND, OR, NOT) in an SQL command? How can a query writer be sure that the operators will work in a specific, desired order?
> What is the difference between COUNT, COUNT DISTINCT, and COUNT(*) in SQL? When will these three commands generate the same and different results?
> How is the order in which attributes appear in a result table determined? How are the column heading labels in a result table changed?
> Explain and provide at least one example of how to qualify the ownership of a table in SQL. What has to occur for one user to be allowed to use a table in a database owned by another user?
> Explain the factors to be considered in deciding whether to create an index for a column in SQL.
> In this chapter, we described four important data models and their properties: enterprise, conceptual, logical, and physical. In the following table, summarize the important properties of these data models by entering a Y (for yes) or an N (for no) in ea
> What are the potential consequences of inappropriate indexing decisions?
> Explain the purpose of indexing in database implementation.
> Explain how referential integrity is established in databases that are SQL:1999 compliant. Explain how the ON UPDATE RESTRICT, ON UPDATE CASCADE, and ON UPDATE SET NULL clauses differ from one another. What happens if the ON DELETE CASCADE clause is set?
> What are the primary data integrity constraints in SQL?
> Define each of the following terms: a. data definition language b. data manipulation language c. referential integrity constraint d. relational DBMS (RDBMS) e. schema
> Demonstrate each of the anomaly types with an example.
> Describe three types of anomalies that can arise in a table and the negative consequences of each.
> What is a schema? Discuss two common methods of expressing a schema.
> List the three components of a relational data model.
> Describe the primary differences between the conceptual and logical data models.
> Consider Figure 1-15. a. What is the purpose of introducing an attribute called Product ID to the Product table? What is its data type? b. If the company wants to keep track of the total outstanding balances of customers, an attribute called â€
> Contrast the following terms: a. normal form; normalization b. candidate key; primary key c. partial dependency; transitive dependency d. composite key; recursive foreign key e. determinant; candidate key f. foreign key; primary key g. natural primary ke
> What are the benefits of the use of an enterprise key?
> Why is the natural key preserved whenever a surrogate key is created?
> Describe the difference between how a 1:M unary relationship and an M:N unary relationship are implemented in a relational data model.
> What is an enterprise key, and why is it important?
> Under what conditions must a foreign key not be null?
> What are the properties that a candidate key must satisfy?
