Exam 2 Review

The text is the main source, with an emphasis on the topics presented in lecture since Exam 1.  Questions will typically be multiple choice or problem solving, with a few open-ended or true-false questions focusing primarily on the following:

• chapter 9
  • describe a main activity of the database system development lifecycle
  • compare and contrast a centralized approach to a view integration approach
  • describe and differentiate conceptual, logical and physical with regard to database design phases
• chapter 11 and 12
  • when given a business scenario, design an ER diagram using the UML-type tools provided in the book
  • compare and contrast ER entities, attributes, and relationships with relational model relations, attributes and referential integrity
  • identify potential problems with ER models and recommend how to correct them
  • convert an ER model to a relational model
  • demonstrate an understanding of specialization, generalization, aggregation and composition
• chapter 13 and 14
  • describe advantages and disadvantages of normal forms
  • convert a 1NF relation into a BCNF relation and describe the difference between each level along the way
  • identify a 4NF relation
  • describe the characteristics of functional dependencies
  • use inference rules presented in the text to derive minimal sets of functional dependencies when given either business rules or a given set of attributes and functional dependencies
  • use functional dependencies to identify keys for a relational model
• chapter 15-17
  • when given a description of an business enterprise, be able to generally describe how to use database design methodology to create conceptual, logical and physical models
• chapter 19
  • differentiate between mandatory access control and discretionary access control
  • identify and describe the capabilities and risks of GRANT and REVOKE on tables and views
• chapter 20
  • describe and differentiate ACID properties
  • describe  the steps involved in concurrency control and the problems to be avoided
  • create a nonserial schedule from a serial schedule
  • test for conflict serializability and view serializability
  • describe the process and advantages of two-phase locking
  • differentiate conservative two-phase locking from two-phase locking
  • describe the application of redo, undo in database recovery, with or without checkpointing
  • describe the rationale for a steal, no-force policy
• chapter 21
  • describe the purpose for query optimization strategies
  • describe the major steps of query processing
  • use transformation rules to optimize a SQL statement
  • understand the application of heuristics to query optimization
  • describe the main strategies for implementing join operations
  • describe the placement of relations within trees for execution strategies
• chapter 30
  • describe advantages of XML
  • differentiate between elements, attributes and entity references in XML
  • describe the purpose of a DTD for a well-formed, valid XML document
• chapters 31-33
  • differentiate between a data warehouse and a data mart
  • describe the advantages of a data warehouse
  • differentiate between the features of OLTP and OLAP
  • describe and differentiate a fact table from a dimension table
  • identify a star schema versus a snowflake schema
  • write a SQL query using ROLLUP and CUBE and illustrate the difference in results
• chapter 34
  • describe the four main operations and their associated data mining techniques
  • provide examples of where data mining applications can be used
  • identify the phases of CRISP-DM
  • differentiate between classification and value prediction