1 / 50

IT420: Database Management and Organization

IT420: Database Management and Organization. Wrap-up 28 April 2006 Adina Cr ă iniceanu www.cs.usna.edu/~adina. Final Exam. Monday, 1330, Michelson 223 Comprehensive Closed books / closed notes One double-sided page with notes NO duplicates. Topics Not Covered. SQL Cursors ODBC.

gardinerl
Download Presentation

IT420: Database Management and Organization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT420: Database Management and Organization Wrap-up 28 April 2006 Adina Crăiniceanu 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 Kroenke, Database Processing

  3. Topics Not Covered • SQL Cursors • ODBC Kroenke, Database Processing

  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 Kroenke, Database Processing

  5. Process Rows Example - PHP <?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: '.$row[title].' <br>'; } ?> Kroenke, Database Processing

  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 @title WHILE @@FETCH_STATUS = 0 BEGIN print @title FETCH NEXT FROM MyCursor INTO @title END //close and free cursor CLOSE MyCursor DEALLOCATE MyCursor Kroenke, Database Processing

  7. Standards for Accessing DBMS • OBDC (Open Database Connectivity) is the early standard for relational databases. • OLE DB is Microsoft’s object-oriented interface for relational and other databases. • ADO (Active Data Objects) is Microsoft’s standard providing easier access to OLE DB data for the non-object-oriented programmer. Kroenke, Database Processing

  8. The Web Server Data Environment • A Web server needs to publish applications that involve different data types. Kroenke, Database Processing

  9. The Role of the ODBC Standard Kroenke, Database Processing

  10. ODBC Architecture Kroenke, Database Processing

  11. Example Code…Familiar?? <?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); ?> Kroenke, Database Processing

  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 Kroenke, Database Processing

  13. ER Model and Relational Model • ER: • Entities • identifiers • Relationships • cardinality • Relational model • Tables • Constraints Kroenke, Database Processing

  14. ER to Relational • Transform entities in tables • Transform relationships using foreign keys • Specify logic for enforcing minimum cardinalities Kroenke, Database Processing

  15. Class Exercise: Transform ER model into Relational Model Kroenke, Database Processing

  16. Relational Model Relationship lines Useful? Kroenke, Database Processing

  17. Table blueprints Kroenke, Database Processing

  18. Normalization Kroenke, Database Processing

  19. Data Redundancy Application constraint: All employees with same rating have the same wage (Rating Wage) Problems due to data redundancy? Kroenke, Database Processing

  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 Kroenke, Database Processing

  21. Update Anomalies • The EMPLOYEE table before and after an incorrect update operation on Wage for Rating = 7 Kroenke, Database Processing

  22. Table decomposition Kroenke, Database Processing

  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 Kroenke, Database Processing

  24. Functional Dependencies in the SKU_DATA Table Assuming data is representative, determine the FD Kroenke, Database Processing

  25. Functional Dependencies in the SKU_DATA Table SKU  (SKU_Description, Department, Buyer) SKU_Description  (SKU, Department, Buyer) Buyer  Department Kroenke, Database Processing

  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 Kroenke, Database Processing

  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.” Kroenke, Database Processing

  28. Eliminating Modification Anomalies from Functional Dependencies in Relations • Put all relations into Boyce-Codd Normal Form (BCNF): Kroenke, Database Processing

  29. Putting a Relation into BCNF:SKU_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 Kroenke, Database Processing

  30. Putting a Relation into BCNF:New Relations Kroenke, Database Processing

  31. Database Administration • Concurrency Control • Security • Recovery Kroenke, Database Processing

  32. Concurrency Control • Concurrency control: ensure that one user’s work does not inappropriately influence another user’s work • No single concurrency control technique is ideal for all circumstances • Trade-offs need to be made between level of protection and throughput Kroenke, Database Processing

  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 Kroenke, Database Processing

  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 Kroenke, Database Processing

  35. Lost Update Problem • T1: R(item) W(item) Commit • T2: R(item) W(item) Commit Kroenke, Database Processing

  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 Kroenke, Database Processing

  37. Serializable Transactions • Serializable transactions: • Run concurrently • Results like when they run separately • Strict two-phase locking – locking technique to achieve serializability Kroenke, Database Processing

  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 Kroenke, Database Processing

  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 Kroenke, Database Processing

  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 Kroenke, Database Processing

  41. ACID Transactions • Transaction properties: • Atomic - all or nothing • Consistent • Isolated • Durable – changes made by commited transactions are permanent Kroenke, Database Processing

  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 Kroenke, Database Processing

  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 Kroenke, Database Processing

  44. Transaction Isolation Level Kroenke, Database Processing

  45. Indexing Kroenke, Database Processing

  46. Hash Index Constant search time Equality queries only Kroenke, Database Processing

  47. B+ Tree Index ~logdN search time d – fan-out (~150) N – number of data entries Supports range queries Kroenke, Database Processing

  48. Use of Indexes To Retrieve Data Kroenke, Database Processing

  49. Class Exercise What index would you construct? 1. SELECT * FROM Mids WHERE Company=02 2. SELECT CourseID, Count(*) FROM StudentsEnroll WHERE Company = 02 GROUP BY CourseID Kroenke, Database Processing

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

More Related