IT420: Database Management and Organization

1
IT420 Database Management and Organization

• Wrap-up
• 28 April 2006
• Adina Crainiceanu
• www.cs.usna.edu/adina

2
Final Exam

• Monday, 1330, Michelson 223
• Comprehensive
• Closed books / closed notes
• One double-sided page with notes
• NO duplicates

3
Topics Not Covered

• SQL Cursors
• ODBC

4
SQL Cursor

• Problem
• SQL SELECT returns multiple rows
• Application programs (PHP,C, C,) need to
  process the rows, one at a time
• Solution
• Establish a cursor, a pointer to first row in the
  result set
• Assign values in that row to variables
• Move the pointer to next row

5
Process Rows Example - PHP

• lt?php //query
• query "select title from songs where title
  like 'Home'"
• //process results
• results mysql_query(query)
• or die("could not retrieve rows")
• while (row mysql_fetch_array(results))
• echo 'Title '.rowtitle.' ltbrgt'
• ?gt

6
SQL Cursor Example SQL Server

• //declare cursor
• DECLARE MyCursor CURSOR FOR
• SELECT title FROM songs WHERE title like 'Home
• //process rows
• OPEN MyCursor
• FETCH NEXT FROM MyCursor INTO _at_title
• WHILE _at__at_FETCH_STATUS 0
• BEGIN
• print _at_title
• FETCH NEXT FROM MyCursor INTO _at_title
• END
• //close and free cursor
• CLOSE MyCursor
• DEALLOCATE MyCursor

7
Standards for Accessing DBMS

• OBDC (Open Database Connectivity) is the early
  standard for relational databases.
• OLE DB is Microsofts object-oriented interface
  for relational and other databases.
• ADO (Active Data Objects) is Microsofts standard
  providing easier access to OLE DB data for the
  non-object-oriented programmer.

8
The Web Server Data Environment

• A Web server needs to publish applications that
  involve different data types.

9
The Role of the ODBC Standard
10
ODBC Architecture
11
Example CodeFamiliar??

• lt?php
• connect odbc_connect("mydbs", root", "")
• query "SELECT name, surname FROM users"
• result odbc_exec(connect, query)
• while(odbc_fetch_row(result))
• name odbc_result(result, 1)
• surname odbc_result(result, 2)
• print("name surname\n")
• // close the connection
• odbc_close(connect) ?gt

12
Final Exam Main Topics

• ER Model
• Relational Model
• ER to Relational
• Normalization
• SQL
• SQL Views
• SQL Triggers
• SQL Stored Procedures
• PHP/MySQL
• Database Administration
• Storage and Indexing
• XML

13
ER Model and Relational Model

• ER
• Entities
• identifiers
• Relationships
• cardinality
• Relational model
• Tables
• Constraints

14
ER to Relational

• Transform entities in tables
• Transform relationships using foreign keys
• Specify logic for enforcing minimum cardinalities

15
Class Exercise Transform ER model into
Relational Model
16
Relational Model
Relationship lines Useful?
17
Table blueprints
18
Normalization
19
Data Redundancy
Number LastName FirstName Email Rating Wage
190 Smith John jsmith_at_usna.edu 4 25
673 Doe Jane jdoe_at_usna.edu 7 35
312 Doe Bob bred_at_usna.edu 8 40
152 Johnson Matt mat_at_usna.edu 7 35
Application constraint All employees with same
rating have the same wage (Rating?
Wage) Problems due to data redundancy?
20
Modification Anomalies

• Deletion Anomaly What if we delete all employees
  with rating 8?
• Lose wage info
• Insertion Anomaly What if we need wage for
  rating 12 with no employee having that rating?
• Cannot insert wage without employee
• Update Anomaly What if we change the wage for
  rating 7 to be 37?
• Could change for only some rows, not all

21
Update Anomalies

• The EMPLOYEE table before and after an incorrect
  update operation on Wage for Rating 7

Number LastName FirstName Email Rating Wage
190 Smith John jsmith_at_usna.edu 4 25
673 Doe Jane jdoe_at_usna.edu 7 35
312 Doe Bob bred_at_usna.edu 8 40
152 Johnson Matt mat_at_usna.edu 7 35
Number LastName FirstName Email Rating Wage
190 Smith John jsmith_at_usna.edu 4 25
673 Doe Jane jdoe_at_usna.edu 7 37
312 Doe Bob bred_at_usna.edu 8 40
152 Johnson Matt mat_at_usna.edu 7 35
22
Table decomposition
Number LastName FirstName Email Rating Wage
190 Smith John jsmith_at_usna.edu 4 25
673 Doe Jane jdoe_at_usna.edu 7 35
312 Doe Bob bred_at_usna.edu 8 40
152 Johnson Matt mat_at_usna.edu 7 35
Number LastName FirstName Email Rating
190 Smith John jsmith_at_usna.edu 4
673 Doe Jane jdoe_at_usna.edu 7
312 Doe Bob bred_at_usna.edu 8
152 Johnson Matt mat_at_usna.edu 7
Rating Wage
4 25
7 35
8 40
23
Functional Dependency (FD)

• A functional dependency the value of one (a set
  of) attribute(s) determines the value of a second
  (set of) attribute(s)
• Alpha ? MIDNName
• Alpha ? (MIDNName, MIDNClass)
• (NbHours, HourlyPrice)?Charge
• The attribute(s) on the left side of the
  functional dependency is called the determinant

24
Functional Dependencies in the SKU_DATA Table
Assuming data is representative, determine the FD
25
Functional Dependencies in the SKU_DATA Table

• SKU ? (SKU_Description, Department, Buyer)
• SKU_Description ? (SKU, Department, Buyer)
• Buyer ? Department

26
Key

• A set of columns is a key for a relation if
• 1. a) No two distinct rows can have same values
  in all key columns
• or equivalently
• b) determines all of the other columns in a
  relation
• 2. This is not true for any subset of the key
• Part 2 false? A superkey
• Candidate key key
• Primary key
• Alternate key

27
Normal Forms

• 1NF A table that qualifies as a relation is in
  1NF
• 2NF A relation is in 2NF if all of its nonkey
  attributes are dependent on all of the primary
  key
• 3NF A relation is in 3NF if it is in 2NF and
  every determinant is a superkey
• Boyce-Codd Normal Form (BCNF) A relation is in
  BCNF if every determinant is a (candidate) key
• I swear to construct my tables so that all
  nonkey columns are dependent on the key, the
  whole key and nothing but the key, so help me
  Codd.

28
Eliminating Modification Anomalies from
Functional Dependencies in Relations

• Put all relations into Boyce-Codd Normal Form
  (BCNF)

29
Putting a Relation into BCNFSKU_DATA

• SKU_DATA (SKU, SKU_Description, Department,
  Buyer)
• SKU ? (SKU_Description, Department, Buyer)
• SKU_Description ? (SKU, Department, Buyer)
• Buyer ? Department
• SKU_DATA (SKU, SKU_Description, Buyer)
• BUYER (Buyer, Department)
• Where BUYER.Buyer must exist in SKU_DATA.Buyer

30
Putting a Relation into BCNFNew Relations
31
Database Administration

• Concurrency Control
• Security
• Recovery

32
Concurrency Control

• Concurrency control ensure that one users work
  does not inappropriately influence another users
  work
• No single concurrency control technique is ideal
  for all circumstances
• Trade-offs need to be made between level of
  protection and throughput

33
Atomic Transactions

• A transaction, or logical unit of work (LUW), is
  a series of actions taken against the database
  that occurs as an atomic unit
• Either all actions in a transaction occur -
  COMMIT
• Or none of them do - ABORT

34
Concurrent Transaction

• Concurrent transactions transactions that appear
  to users as they are being processed at the same
  time
• In reality, CPU can execute only one instruction
  at a time
• Transactions are interleaved
• Concurrency problems
• Lost updates
• Inconsistent reads

35
Lost Update Problem

• T1 R(item) W(item) Commit
• T2 R(item) W(item) Commit

36
Inconsistent-Read Problem

• Dirty reads read uncommitted data
• T1 R(A), W(A), R(B), W(B), Abort
• T2 R(A), W(A), Commit
• Unrepeatable reads
• T1 R(A), R(A), W(A), Commit
• T2 R(A), W(A), Commit

37
Serializable Transactions

• Serializable transactions
• Run concurrently
• Results like when they run separately
• Strict two-phase locking locking technique to
  achieve serializability

38
Deadlock

• Deadlock two transactions are each waiting on a
  resource that the other transaction holds
• Preventing deadlock
• Allow users to issue all lock requests at one
  time
• Require all application programs to lock
  resources in the same order
• Breaking deadlock
• Almost every DBMS has algorithms for detecting
  deadlock
• When deadlock occurs, DBMS aborts one of the
  transactions and rollbacks partially completed
  work

39
Optimistic versus PessimisticLocking

• Optimistic locking assumes that no transaction
  conflict will occur
• DBMS processes a transaction checks whether
  conflict occurred
• If not, the transaction is finished
• If yes, the transaction is repeated until there
  is no conflict
• Pessimistic locking assumes that conflict will
  occur
• Locks are issued before a transaction is
  processed, and then the locks are released

40
Declaring Lock Characteristics

• Most application programs do not explicitly
  declare locks due to its complication
• Mark transaction boundaries and declare locking
  behavior they want the DBMS to use
• Transaction boundary markers BEGIN, COMMIT, and
  ROLLBACK TRANSACTION
• Advantage
• If the locking behavior needs to be changed,
  only the lock declaration need be changed, not
  the application program

41
ACID Transactions

• Transaction properties
• Atomic - all or nothing
• Consistent
• Isolated
• Durable changes made by commited transactions
  are permanent

42
Consistency

• Consistency means either statement level or
  transaction level consistency
• Statement level consistency each statement
  independently processes rows consistently
• Transaction level consistency all rows impacted
  by either of the SQL statements are protected
  from changes during the entire transaction
• With transaction level consistency, a transaction
  may not see its own changes

43
Isolation Inconsistent-Read Problem

• Dirty reads read uncommitted data
• T1 R(A), W(A), R(B), W(B), Abort
• T2 R(A), W(A), Commit
• Unrepeatable reads
• T1 R(A), R(A), W(A), Commit
• T2 R(A), W(A), Commit
• Phantom reads
• Re-read data and find new rows

44
Transaction Isolation Level
45
Indexing
46
Hash Index
Constant search time Equality queries only
47
B Tree Index
logdN search time d fan-out (150) N
number of data entries Supports range queries
48
Use of Indexes To Retrieve Data
49
Class Exercise

• What index would you construct?
• 1. SELECT
• FROM Mids
• WHERE Company02
• 2. SELECT CourseID, Count()
• FROM StudentsEnroll
• WHERE Company 02
• GROUP BY CourseID

50
SOFs

• www.sof.cs.usna.edu
• Choose as password a random number between 1 and
  100
• If cannot login, try another number