Midterm%20Review

1
Midterm Review
2
Main Topics

• ER model
• Relational model
• Relational Database Design (Theory)

3
Symbols of ER Diagram
4
Symbols of ER Diagram (Cont.)
5
E-R Diagram of a Bank
6
Relational Model

• Relations schema and instances
• Attributes single-valued, domain, keys
• Set of records no duplicates, no order
• Formal query languages
• Relational algebra (RA)
• Tuple relational calculus (TRC)
• Automatic conversion between relational and ER

7
Relational Algebra

• Basic operations
• Selection ( ? )
• Projection ( ? )
• Cross-product ( x )
• Set difference ( - )
• Union ( ? )
• Intersection
• join
• Ignore Aggregate functions, outer, natural and
  equi-joins (all join conditions will be given)

8
Tuple Relational Calculus

• Basic operations
• Tuple Variable
• Predicates, for-all, exists, x -gt y
• Set difference ( - )
• Union ( ? )
• Intersection

9
SQL Query Block

• SELECT select-clause
• FROM from-clause
• WHERE where-clause
• ORDER BY order-by-expression
• GROUP BY group-by-attributes
• HAVING condition-for-each-group
• Query blocks may be nested in FROM and WHERE
• May be connected using UNION, INTERSECT, and
  EXCEPT
• Questions will involve 1 level of nesting of
  subqueries
• Select From select Where . ..... .AND
  Select .. AND .
  Select ..

No further Select within these Select statements
10
SQL Features

• Duplicates DISTINCT.
• Aggregation queries (e.g., max, sum) return a
  single value, unless there is a group by
• Group by / Having
• Aggregation on an attribute in SELECT returns one
  value for each group
• All non-aggregation attributes in SELECT must
  appear in GROUP BY
• If a attribute appears in GROUP BY, it may not
  necessarily appear in SELECT

11
Relational Database Design

• 1NF
• Functional Dependences
• Armstrongs Axioms
• Closure of a set of FD, Closure of an attribute
  set
• Various kinds of keys
• Decomposition
• 3NF and BCNF
• Lossless Join, FD preserving, etc.