Announcements

1
Announcements

• Project Due Friday 500 PM
• Group Presentations
• 10 minutes
• Describe project (ERD), discuss any challenges,
  show us some queries
• Final Monday, December 20 _at_ 9am in KH006
• Optional early exam Friday, December 17 _at_ 1pm in
  KH006

2
Final Review

• Lecture 24

3
Approximate Composition of the Exam

• 70 - Chapters 3-6, and embedded programs
  (excluding relational algebra)
• 10 - Special Topics
• Not all questions will be worth the same
• Youll have to answer enough to add up to the
  right point value
• No answering your own question
• 10-15 Chapters 2, 8, IR, and relational algebra
  (Short answers, T/F, fill in the blank, multiple
  choice)
• 5-10 Physical Storage

4
Review Physical Storage -Application

• A disk block is 2048B
• A record is 450B
• There are 10,000 records
• What is the block factor?
• What is the number of blocks needed to store
  entire table?

5
Example B Tree
270 300
100 200
15 60
230 270
1 8
60 75 80
200 215
230 240
15 25 30
1
8
120 150
100 115
150 165
120 145
6
Rules for Constructing a B Tree

• If the root is not a leaf, it must have at least
  two children
• If the tree is order n, each interior node (that
  is, all nodes except the root and leaf nodes),
  must have between n/2 and n occupied pointers
  (and children). If n/2 is not an integer, roundup
  to determine the minimizes number of pointers

7
Rules for Constructing a B Tree

• The number of key values contained in a non-leaf
  node is 1 less than the number of pointers
• If the tree has order n, the number of occupied
  key values in a leaf node must be between (n-1)/2
  and n-1. If (n-1)/2 is not an integer, round up
  to determine the minimum number of occupied key
  values.
• The tree must be balanced, that is, every path
  from the root node must have the same length.

8
Symbols used in E-R Diagram

• Entity rectangle
• Attribute oval
• Relationship diamond
• Link - line

9
Scenario Organization of Olympic Facilities

• The Olympic facilities are divided into sports
  complexes. Sports complexes are divided into
  one-sport and multisport types. Multisport
  complexes have areas of the complex designed for
  each sport with a location indicator (e.g.
  center, NE corner, etc.). A complex has a
  location, chief organizing individual, total
  occupied area, and so on. Each complex holds a
  series of events (e.g., the track stadium may
  hold many different races). For each event there
  is a planned date, duration, number of
  participants, number of officials, and so on. A
  roster of all officials will be maintained
  together with the list of events each official
  will be involved in. The two types of facilities
  (one-sport and multisport) will have different
  types of information. For each type, the
  approximate budget will be kept.

10
Olympic EER Diagram
11
Olympic Relations
12
Functional Dependencies and Normalization
13
Olympic DDL and SQL