Computer Science 455

Exam #2 review:

DRAFT

Disclaimer: I have attempted to be comprehensive in the following, but important items may have been omitted by mistake. If you see such an omission, please let me know, but you are responsible for all of the lecture material to date.

The first hour exam will be on Monday, March 27, and will cover

Date:  Chapters 4, 9 - 11, 13, Sunderraman, chapters on SQL SQL01.PPT, SQL02.PPT, SQL03.PPT, DESIGN01.PPT, DESIGN02.PPT

• Design
  • Be able to convert an entity-relationship diagram to a relational schema, specifying primary and foreign keys and probable placement of indexes
  • Be able to define and explain functional dependencies, 1NF, 2NF, 3NF, BCNF. Be able to sketch a functional dependency diagram.
  • Be able to identify violations of 2NF and 3NF and to correct them.
  • Questions on constructing ER diagrams and moving from a form to a relational schema will be on the next exam (not on this one).
• SQL
  • Be able to write some advanced queries in SQL: particularly
    • Use of tuple variables
    • Use of subqueries
    • Use of relational operators
    • Use of exists/not exists
    • Use of functions (sum, max, etc.) with having and group by
  • Be able to write statements to create and drop tables, views, and indexes.
  • Be able to explain how views are implemented.
  • Be able to write statements to insert, delete, and modify tables.

  Queries will be either with respect to the SPJ database, or to the Hoh River College database.