Exam 1 Review

The text is the main source, with an emphasis on the topics presented in lecture. At a minimal level, be aware of the following:

• chapter 1
  • note the pros and cons for file-based and database-based systems

• chapter 2
  • describe the three-level ansi-sparc architecture and the reasons for using it
  • compare and contrast the data models as presented
  • identify the functions of a dbms
  • describe the architecture of a multi-user dbms, particularly for client-server environments

• chapter 3
  • describe the relational model and be able to apply it to a "real-world" situation
  • describe the mathematical foundations of the relational model
  • differentiate between a relation and a relational schema
  • identify and describe the properties and components of relations
  • describe the purpose of the various components of the relational model and how they affect the various types of integrity

• chapter 4
  • identify and compare when to use the various operations of the relational algebra and what they accomplish
  • be able to graphically draw a diagram of how the various operations affect a set
  • be able to apply and translate between the relational algebra, the relational calculus, query-by-example and structure query language for a written "human language" request
  • compare and contrast tuple relational calculus and domain relational calculus
  • identify a "safe" query with regard to the relational calculus

• chapters 5 and 6
  • be able to write sql dml statements for specific human language requests
  • be able to translate sql dml statements into a human language request
  • be able to rewrite sql dml statements that produce the same result set
  • differentiate between a ddl and dml statement
  • be able to interpret the impact of a sql ddl statement on the various types of integrity
  • identify the advantages and disadvantages of views
  • describe how the process of view resolution works and contast it with view materializaton
  • describe when a view would be good to use
  • be able to set permissions using sql
  • be able to discern a diagram of privilege assignment and removal

Question possibilities:

Your company’s production manager want to access the names of all employees and their salary from data contained in the tables listed below; write SQL for allowing appropriate access.

Table: Employee; Columns: EmployeeID, EmpName, Salary, DeptID

Table: Department; Columns: DeptID, Department

Write a relational algebra statement, a tuple relational calculus statement and a SQL statement that would list the franchise and city where there is a franchise with no supplier in the same city.