CSC 453 Database Systems Lecture

1. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

2. Today • Review-Normalization • Normalization-BCNF • Recursion • PL/SQL

3. Prime/Non-prime attributes • A  BC, B  C, D  B • AB  C, C  D, CE  A

4. Consider the following relations R(Sname, Semail, Course, Instructor) Students(Semail, Sname) Courses(Course, Instructor) Taking(studentEmail, Course)

5. R(Sname, Semail, Course, Instructor) Students(SEmail, Sname) Courses(Course, Instructor) Taking(SEmail, Course)

6. Motivation for Normal Forms • Identify a “good” schema • For some definition of good • Avoid anomalies, redundancy • Many normal forms • 1st • 2nd • 3rd • BCNF

7. First Normal Form (1NF) • No multi‐valued attributes allowed • Counter-example • Course(name, instructor, [student,email]*) • Redundancy in non‐list attributes

8. Removing Multi-Valued Attributes • Remove the multi-valued attribute from the relation • Create a new relation with the primary key of the original relation and the multi-valued attribute • For each tuple in original relation with k values, add k tuples to the new relation • Primary key of new relation contains all attributes; primary key of original relation becomes foreign key in new relation referencing original relation

9. Second Normal Form (2NF) • Second Normal Form: 1NF, plus every non-prime attribute in the relation is determined by the entire primary key (but not by any subset) • To get 2NF, eliminate partial dependencies on the primary key • XY is apartial dependency if ZY for some subset Z of X

10. Converting 1NF to 2NF • (EmpID, EmpLName, EmpFName, Dept, ProjCode, Hours) • Functional Dependencies • EmpID  EmpLName, EmpFName, Dept • EmpID, ProjCode  Hours

11. 1NF vs 2NF Data

12. Converting 1NF to 2NF • Decompose (EmpID, EmpLName, EmpFName, Dept, ProjCode, Hours) • Remove the offending FD • EmpID EmpLName, EmpFName, Dept • Two tables • (EmpID, EmpLName, EmpFName, Dept) • (EmpID, ProjCode, Hours)

13. Towards 2NF

14. Towards 2NF EmpID  EmpLName, EmpFName, Dept EmpID, ProjCode  Hours

15. Counterexample • Movies(title, year, star, studio, studioAddress, salary) • FD: title, year ‐> studio; studio ‐> studioAddress; star‐>salary

16. Removing Partial Dependencies • Find all dependencies where a subset of the primary key determines some non-prime attribute(s) Starting with the smallest subset, do the following: 1. Remove all attributes on the right-hand side from the relation and put them in a new relation 2. Add the attributes in the determinant(l.h.s) to the new relation; make them the primary key, and make them a foreign key in the original relation referencing the new table 3. Remove from any remaining partial dependencies any attributes removed from the original relation

17. Third Normal Form (3NF) • Third Normal Form: 2NF, plus every non-prime attribute in the relation is determined only by the primary key of the relation. • To get 3NF, eliminate transitive dependencies on the primary key • XY is atransitive dependency if XZ and ZY for some Z that is disjoint from X

18. Counterexample • Movies(title, year, star, studio, studioAddress, salary) • FD: title, year ‐> studio; studio ‐> studioAddress; star‐>salary

19. Converting 2NF to 3NF • Schema: (First , Last, Address, City, State, Zip) • First, Last  Address, City, State, Zip • Transitive functional dependency: • Zip  City, State

20. 2NF Data Table

21. Decompose the Tables

22. Decompose the Tables ZipCode  City, State First, Last  First, Last, Address, ZipCode

23. 3NF Decomposition • Input: A universal relation R and a set of functional dependencies F on R • Output: A decomposition D of R into 3NF schemas with dependency preservation and nonadditive join

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

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

26. 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}

27. 3NF Decomposition • R={A, B, C, D, E, F} • F = { AB  CD, C  EF, D  A } • Non-prime determines a prime attribute

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

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

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

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

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

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

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

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

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

37. 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?

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

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

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

41. 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?

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

43. 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!

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

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

46. 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;

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

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

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

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