1 / 109

CSC 453 Database Systems Lecture

This lecture covers topics such as normalization, Boyce-Codd Normal Form (BCNF), and third normal form (3NF) in the context of database systems. It also includes examples and algorithms for achieving these normal forms.

kgrant
Download Presentation

CSC 453 Database Systems Lecture

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. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

  2. Today • Normalization-BCNF • Transactions

  3. Normalization Review |-----Primary Key----| CourseID | Quarter | Course Name | ------------------------------------------------| CSC401 | 2009-1 | Programming | CSC401 | 2009-2 | Programming | CSC453 | 2009-1 | Databases | CSC453 | 2010-1 | Databases | IS451 | 2009-2 | Web Design |

  4. |-----Primary Key----| CourseID | Quarter | InstructorID | InstrName| ---------------------------------------------------------------| CSC401 | 2009-1 | 332 | Perkovic| CSC401 | 2009-2 | 332 | Perkovic | CSC453 | 2009-1 | 495 | Malik| CSC453 | 2010-1 | 332 | Malik| IS451 | 2009-2 | 242 | Wagner|

  5. Prime Vs Non-Prime Attributes • Prime Attribute: Any attribute that is specified in the candidate key • Non-Prime Attribute: Any attribute not part of the candidate key

  6. Third Normal Form • Third Normal Form: Every non-prime attribute is fully functionally dependent on every candidate key, and no non-prime attribute is transitively dependent on any candidate key • “For every non-trivial functional dependency XA, either X is a superkey or A is a prime attribute.”

  7. Third Normal Form • A relation schema is in third nomal form (3NF) if for all: X → Y in F+ at least one of the following holds: • X → Y is trivial (i.e. X ∈ Y) • X is a superkey for R • each attribute A in Y – X is contained in a candidate key for R, i.e. A is a prime attribute.

  8. Example • R = (A,B,C,D) A→ C D BA→ C The candiate key is ? 3NF decomposition is ?

  9. 3NF Decomposition • R(A, B, C, D) • FDs: A B, C D • R(A,B,C,D,E) • FDs: CE, BC • R(A, B, C, D, E, F) • F = { AB  CD, D  A, C  EF}

  10. 3NF-Normalization Algorithm (3NF Normalization): Input: Relation R with FDs Fc Output: 3NF decomposition D of R • D = {} • For every XY in F add sub-relation Q =(XY) to D, unless • some sub-relation in D already contains all of XY: don’t add Q • some sub-relation(S) in D is contained in XY: replace S with Q(XY) • If no relation in D contains a key of R, then add new relation Q(X) on some key X of R

  11. The End Result • A collection of relations, each in 3NF • Each relation has a primary key • (We are assuming that there is only one candidate key…) • Every non-prime attribute in a relation is determined by its entire primary key • No non-prime attribute in a relation is determined by any attributes other than its entire primary key • Information can reconstructed using joins, and stored in views if desired

  12. 3NF • Pay(Employee, Grade, Salary) • F= {E  G, E  S, G  S}

  13. 3NF-Checking Order • What is the candidate key? • Is F in minimal cover? • Which FDs violate 3NF? • Decompose to 3NF. • Is the decomposition lossless? • Is it dependency preserving?

  14. Boyce-Codd Normal Form • Boyce-Codd Normal Form (BCNF): For every non-trivial functional dependency XA, it must be the case that X is a superkey • “Every determinant must contain a candidate key” • X must be a superkey even if A is a prime attribute

  15. BCNF example Pizza |Topping |Topping Type ------- |-------------|------------- 1 |mozzarella|cheese 1 |pepperoni |meat 1 |olives |vegetable 2 |mozzarella | meat 2 |sausage |cheese 2 |peppers |vegetable • Each pizza must have exactly • one of each topping type • One type of cheese • One type of meat • One type of vegetable

  16. BCNF example Pizza |Topping |Topping Type ------- |-------------|------------- 1 |mozzarella|cheese 1 |pepperoni |meat 1 |olives |vegetable 2 |mozzarella | meat 2 |sausage |cheese 2 |peppers |vegetable • Pizza can have • exactly 3 types • of topping • One type of cheese • One type of meat • One type of vegetable

  17. Decompose Pizza |Topping | ------- |-------------| 1 |mozzarella| 1 |pepperoni | 1 |olives | 2 |mozzarella | 2 |sausage | 2 |peppers | Topping |Topping Type -------------|------------- mozzarella|cheese pepperoni |meat olives |vegetable mozzarella | cheese sausage |meat peppers |vegetable

  18. BCNF Decomposition • Input: A universal relation R and a set of functional dependencies F on R • Output: A decomposition D of R into BCNF schemas with nonadditive join • Algorithm on next page • Algorithm does not guarantee dependency preservation

  19. BCNF • Decomposition Algorithm ALGORITHM BCNF (R: Relation, F: FD set) BEGIN 1. D  {R} 3. While some X → Y holds in some Ri(A1,…,An) in D and (X → Y) is not trivial, X is not a superkey of Ri Ri1  X+ ∩({A1,…,An}) Ri2  X  ({A1,…,An} - X+ ) Result  Result – {Ri}  {Ri1,Ri2} 4. Return result END

  20. BCNF Example: R = (A, B, C) F = {A → B, B → C} Is R in BCNF? A: Consider the nontrivial dependencies in F: 1. A → B, A → R (A is a key) 2. B → C, B → A (B is not a key) Therefore, R not in BCNF

  21. BCNF Example: R = R1 R2 R1 = (A, B);R2 = (B, C) F = {A → B, B → C} Are R1, R2 in BCNF? A: 1. Test R1: A → B covered, A → R1 (all other FD’s covered trivial) 2. Test R2: B → C covered, B → R2 (all other FD’s covered trivial)  R1, R2 in BCNF Q: Is the decomposition lossless?

  22. BCNF R = (A, B, C, D, E, H) F = {A → BC, E → HA} Decompose R into BCNF:

  23. BCNF Decomposition R = (A, B, C, D, E, H) F = {A → BC, E → HA} (Note: Fc = F) Decomposition #1:R = R1 R3 R4 R = (A, B, C, D, E, H) Decompose on A → BC R1 = (A, B, C) R2 = (A, D, E, H) Decompose on E → HA R3 = (A, E, H) R4 = (D, E) Q: Is this DP? A: Yes. All Fc covered by R1, R3, R4. Therefore F+ covered

  24. BCNF Decomposition (cont.) R = (A, B, C, D, E, H) F = {A → BC, E → HA} (Note: Fc = F) Decomposition #2:R = R1 R3 R5  R6 R = (A, B, C, D, E, H) Decompose on A → B R1 = (A, B) R2 = (A, C, D, E, H) Decompose on E → HA R3 = (A, E, H) R4 = (C, D, E) Decompose on E → C R5 = (C, E) R6 = (E, D) Q: Not DP. Why? A: A → C not covered by R1, R3, R5 , R6.

  25. More BCNF (cont.) Q: Can we decompose on FD’s in Fc to get a DP BCNF decomposition? A: Sometimes, BCNF + DP not possible Example: R = (J, K, L) F = {JK → L, L → K} (Fc = F) Decompose on Or: L → K JK → L JK → L L → K

  26. More BCNF (cont.) Q: Can we decompose on FD’s in Fc to get a DP BCNF decomposition? A: Sometimes, BCNF + DP not possible R = (J, K, L) F = {JK → L, L → K} Decomposition #2: Decomposition #1: R = (J, K, L) Decompose on L → K R = (J, K, L) Decompose on JK → L R1 = (L, K) R2 = (J, L) R2 = (J, K, L) R2 = (L, K) Not DP: JK → L not covered

  27. BCNF Decomposition • R(A, B, C, D, E, F) • F = { AB  CD, C  EF, D  A }

  28. BCNF Decomposition • R (S, P, Q, X, Y, N, C) • F = { S  NC, P  XY, SP  Q , QP } • Decompose to BCNF • Is it dependency preserving?

  29. Properties of Decompositions • When we work with BCNF, we must look at properties involving multiple relations: • Nonadditive (Lossless) Join: No tuples that are not in the original relation (spurious tuples) are generated when decomposed relations are joined • Dependency Preservation: Every functional dependency in the original relation is represented somewhere in the decomposition

  30. BCNF vs. 3NF • Every relation in BCNF is in 3NF • Not every relation in 3NF is in BCNF • 3NF relations that are not in BCNF fail because some prime attribute is determined by something that is not a superkey – this is allowed by 3NF but not by BCNF • Decomposing tables into BCNF can be tricky – functional dependencies can be lost!

  31. Remarks on Algorithms • Different runs may yield different results, depending on the order in which attributes and functional dependencies are considered • We must know all functional dependencies • We can’t always guarantee dependency preservation for BCNF, but we can generate a 3NF decomposition and then consider the individual relations in the result

  32. PL/SQL • A general-purpose procedural programming that includes SQL commands • PL/SQL can • create and issue SQL statements • store and process the results of queries • define procedures to respond to database events

  33. Basic Structure of Code • Simplest form is an anonymous block: declare -- variable and subprogram declarations -- every statement must end with a ; begin -- PL/SQL statements to execute --every statement must end with a ; --statements can be nested with another B/E exception -- exception handling (optional) end;

  34. Output • To display output:RAISE NOTICE ‘string %’, arguments; • Output buffer displayed in DBMS Output tab • Use View Dbms Output and ‘+’ to open tab • Single line comments with – • Multi-line with /* */

  35. Data Types • Numeric • Character • Boolean • Datetime • Data types are not case sensitive DECLARE num1 INTEGER; num2 REAL; num3 DOUBLE PRECISION; BEGIN null; END; /

  36. Declaring Variables • All variables must be declared: varName [CONSTANT] dataType [NOT NULL] [:= initialValue]; • Assignments use :=, and PL/SQL has typical arithmetic operations

  37. Scoping DECLARE -- Global variables num1 number := 95; num2 number := 85; BEGIN dbms_output.put_line('Outer Variable num1: ' || num1); dbms_output.put_line('Outer Variable num2: ' || num2); DECLARE -- Local variables num1 number := 195; num2 number := 185; BEGIN dbms_output.put_line('Inner Variable num1: ' || num1); dbms_output.put_line('Inner Variable num2: ' || num2); END; END; /

  38. Declaring Variables • Only one variable can be declared per line, but variable types can be given in terms of the domain of another variable or attribute: varName otherVar%type; varName TABLE.Attribute%type;

  39. Operators • Arithmetic operators • Relational operators • Comparison operators • LIKE, BETWEEN, IN, IS NULL • Logical operators • String operators

  40. Branching • if-then: if condition then …’true’ statements…end if; • if-else:if condition then …’true’ statements…else …’false’ statements…end if;

  41. Branching • if-elsif:if condition1 then … ‘true’ statements… elsif condition2 then … ‘false-true’ statements… elsif condition3 then … ‘false-false-true’ statements… (… as many times as needed…) else … ‘all false’ statements… end if;

  42. Case Statement CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END

  43. Case Statement • expression • Optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n) • condition_1, condition_2, ... condition_n • The conditions that must all be the same datatype. The conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further. • result_1, result_2, ... result_n • Results that must all be the same datatype. This is the value returned once a condition is found to be true.

  44. Case Statement • If no condition is found to be true, then the CASE statement will return the value in the ELSE clause. • If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

  45. Case Statement -- Multiple if-then-else better expressed by CASE DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; /

  46. Loops • General loop:loop …loop body…end loop; • Repeats until exit; is executed in loop body • While loop:while condition loop …loop body…end loop; • Repeats until condition is false

  47. Loops • For loop: for variable in [reverse] lower..upper loop …loop body… end loop; • Can only increment/decrement by one • lower always appears before upper in header

  48. Incorporating SQL Queries • Result of a query can be stored in a set of variables by adding INTO clause to query: SELECT list of attributesINTO list of variablesFROM list of tables … • Variable types must match attribute types

  49. Procedures (In Oracle) CREATE [OR REPLACE] PROCEDURE name (paramName IN [OUT] paramType …) AS …declarations…BEGIN …body of procedure…END;/ • ‘IN’ parameters are passed by value, for input only, read-only parameters • ‘OUT’ parameters are passed by reference • ‘IN OUT’ parameters are passed by reference, to return results to the calling sub-program

More Related