CSC 453 Database Systems Lecture

CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

Today • Normalization-BCNF • Transactions

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 |

|-----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|

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

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.”

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.

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

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}

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

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

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

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?

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

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

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

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

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

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

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

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?

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

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

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.

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

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

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

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?

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

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!

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

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

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;

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 /* */

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

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

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; /

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;

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

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

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;

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

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.

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.

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; /

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

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

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

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

CSC 453 Database Systems Lecture