functional dependency in engineering.pptx

functional depndency topic

1. What is Functional Dependency? Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,..., An, then those two tuples must have to have same values for attributes B1, B2, ..., Bn. Functional Dependency (FD)  is a constraint that determines the relation of one attribute to another attribute in a Database Management System (DBMS). Functional Dependency helps to maintain the quality of data in the database. Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-hand side attributes determine the values of attributes on the right-hand side. α β t1[ α] t1[ β] t2[ α] t2[ β] ……. ……. If α and β are the two sets of attributes in a relational table R where- α ⊆ R β ⊆ R Then, for a functional dependency to exist from α to β, If t1[α] = t2[α], then t1[β] = t2[β] f d  : α  (determinant) → β (dependent)      

2. In this example, if we know the value of Employee number, we can obtain Employee Name, city, salary, etc. By this, we can say that the city, Employee Name, and salary are functionally depended on Employee number.

3. 1. Trivial Functional Dependencies-   A functional dependency X → Y is said to be trivial if and only if Y ⊆ X. Thus, if RHS of a functional dependency is a subset of LHS, then it is called as a trivial functional dependency.   Examples-  The examples of trivial functional dependencies are- AB → A AB → B AB → AB   2. Non-Trivial Functional Dependencies-  A functional dependency X → Y is said to be non-trivial if and only if Y ⊄ X. Thus, if there exists at least one attribute in the RHS of a functional dependency that is not a part of LHS, then it is called as a non-trivial functional dependency.   Examples-  The examples of non-trivial functional dependencies are- AB → BC AB → CD

4. 1.  Trivial Functional Dependency In  Trivial Functional Dependency , a dependent is always a subset of the determinant. i.e. If  X(determinant) → Y(dependent)  and  Y is the subset of X , then it is called trivial functional dependency Here,  { roll_no , name} → name  is a trivial functional dependency, since the dependent  name  is a subset of determinant set { roll_no,name } Similarly,  roll_no → roll_no   is also an example of trivial functional dependency. 

5. 2.  Non-trivial Functional Dependency In  Non-trivial functional dependency , the dependent is strictly not a subset of the determinant. i.e. If  X → Y  and  Y   is not a subset of X , then it is called Non-trivial functional dependency. Here,  roll_no → name  is a non-trivial functional dependency, since the dependent  name  is  not a subset of  determinant   roll_no Similarly,  { roll_no , name} → age  is also a non-trivial functional dependency, since  age  is  not a subset of { roll_no , name} 

6. Armstrong’s axioms/properties of functional dependencies: Reflexivity:  If Y is a subset of X, then X→Y holds by reflexivity rule For example, { roll_no , name} → name is valid. Augmentation:  If X → Y is a valid dependency, then XZ → YZ is also valid by the augmentation rule. For example, If { roll_no , name} → dept_building is valid, hence { roll_no , name, dept_name } → { dept_building , dept_name } is also valid.→ 3. Transitivity : If X → Y and Y → Z are both valid dependencies, then X→Z is also valid by the Transitivity rule. For example, roll_no → dept_name & dept_name → dept_building , then roll_no → dept_building is also valid.

7. Dependencies in DBMS is a relation between two or more attributes. It has the following types in DBMS − Functional Dependency Fully-Functional Dependency Transitive Dependency Multivalued Dependency Partial Dependency EmpID EmpName EmpAge E01 Amit 28 E02 Rohit 31 EmpID -> EmpName Functional Dependency If the information stored in a table can uniquely determine another information in the same table, then it is called Functional Dependency. Consider it as an association between two attributes of the same relation. If P functionally determines Q, then P->Q Let us see an example − EmpName   is functionally dependent on  EmpID   because  EmpName   can take only one value for the given value of  EmpID :

8. Fully-functionally Dependency An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset. For example, an attribute Q is fully functional dependent on another attribute P, if it is Functionally Dependent on P and not on any of the proper subset of P. ProjectID ProjectCost 001 1000 002 5000 EmpID ProjectID Days  (spent on the project) E099 001 320 E056 002 190 EmpID , ProjectID , ProjectCost -> Days However, it is not fully functional dependent. Whereas the subset { EmpID , ProjectID } can easily determine the {Days} spent on the project by the employee. This summarizes and gives our fully functional dependency − { EmpID , ProjectID } -> (Days)

9. Transitive Dependency When an indirect relationship causes functional dependency it is called Transitive Dependency. If  P -> Q and Q -> R is true, then P-> R is a transitive dependency. Multivalued Dependency When existence of one or more rows in a table implies one or more other rows in the same table, then the Multi-valued dependencies occur. If a table has attributes P, Q and R, then Q and R are multi-valued facts of P. It is represented by double arrow ->-> Example:  Suppose there is a bike manufacturer company which produces two colors(white and black) of each model every year. Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL and independent of each other. In this case, these two columns can be called as multivalued dependent on BIKE_MODEL. The representation of these dependencies is shown below: BIKE_MODEL   →  →  MANUF_YEAR   BIKE_MODEL   →  →  COLOR   This can be read as "BIKE_MODEL multidetermined MANUF_YEAR" and "BIKE_MODEL multidetermined COLOR". BIKE_MODEL MANUF_YEAR COLOR M2011 2008 White M2001 2008 Black M3001 2013 White M3001 2013 Black M4006 2017 White M4006 2017 Black

10. Normalization A large database defined as a single relation may result in data duplication. This repetition of data may result in: Making relations very large. It isn't easy to maintain and update data as it would involve searching many records in relation. Wastage and poor utilization of disk space and resources. The likelihood of errors and inconsistencies increases. So to handle these problems, we should analyze and decompose the relations with redundant data into smaller, simpler, and well-structured relations that are satisfy desirable properties. Normalization is a process of decomposing the relations into relations with fewer attributes.

11. What is Normalization? Normalization is the process of organizing the data in the database. Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies. Normalization divides the larger table into smaller and links them using relationships. The normal form is used to reduce redundancy from the database table.

12. First Normal Form- A given relation is called in First Normal Form (1NF) if each cell of the table contains only an atomic value. OR A given relation is called in First Normal Form (1NF) if the attribute of every tuple is either single valued or a null value. The following relation is not in 1NF- Student_id Name Subjects 100 Akshay Computer Networks, Designing 101 Aman Database Management System 102 Anjali Automata, Compiler Design However, This relation can be brought into 1NF. This can be done by rewriting the relation such that each cell of the table contains only one value. Student_id Name Subjects 100 Akshay Computer Networks 100 Akshay Designing 101 Aman Database Management System 102 Anjali Automata 102 Anjali Compiler Design

13. 1. First Normal Form – If a relation contain composite or multi-valued attribute, it violates first normal form or a relation is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is  singled valued attribute . Example 1 –  Relation STUDENT in table 1 is not in 1NF because of multi-valued attribute STUD_PHONE. Its decomposition into 1NF is?

14. Decomposed table: Example 2 – Convert into 1NF

15. 2. Second Normal Form – To be in second normal form, a relation must be in first normal form and relation must not contain any partial dependency. A relation is in 2NF if it has  No Partial Dependency,  i.e. ,  no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. Partial Dependency –   If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.

16. In case there is a partial dependency, we will remove that attribute from the relation that is partially dependent. In this table, you can note that many subjects come with the same subject fee. Three things are happening here: The SUBJECT_FEE won’t be able to determine the values of CAND_NO or SUBJECT_NO alone; The SUBJECT_FEE along with CAND_NO won’t be able to determine the values of SUBJECT_NO; The SUBJECT_FEE along with SUBJECT_NO won’t be able to determine the values of CAND_NO; Thus, We can conclude that the attribute SUBJECT_FEE is a non-prime one since it doesn’t belong to the candidate key here {SUBJECT_NO, CAND_ID} ; But, on the other hand, the SUBJECT_NO – > SUBJECT_FEE, meaning the SUBJECT_FEE depends directly on the SUBJECT_NO, and it forms the candidate key’s proper subset. Here, the SUBJECT_FEE is a non-prime attribute, and it depends directly on the candidate key’s proper subset. Thus, it forms a partial dependency. Conclusion:  The relation mentioned here does not exist in 2NF. CAND_ID SUBJECT_NO SUBJECT_FEE 111 S1 1000 222 S2 1500 111 S4 2000 444 S3 1000 444 S1 1000 222 S5 2000

17. Table 1 CAND_NO SUBJECT_NO 111 S1 222 S2 111 S4 444 S3 444 S1 222 S5 Table 2 SUBJECT_NO SUBJECT_FEE S1 1000 S2 1500 S3 1000 S4 2000 S5 2000 Now, the tables are in their Second Normal Form. Note:  The Second Normal Form tries to reduce any redundant data from getting stored in the system’s memory. For instance, if we take an example of about 100 candidates taking the S1 subject, then we don’t have to store their fees as 1000 as a record for all the 100 candidates. Rather, we can store them all at once in the second table as  the subject fee for S1 is 1000

18. Example -  Consider following functional dependencies in relation  R (A,  B , C,  D ) In the above relation, AB is the only candidate key and there is no partial dependency, i.e., any proper subset of AB doesn’t determine any non-prime attribute.

19. 3. Third Normal Form – A given relation is said to be in its third normal form when it’s in 2NF but has no transitive partial dependency. Meaning, when no transitive dependency exists for the attributes that are non-prime, then the relation can be said to be in 3NF. In a relation that is in 1NF or 2NF, when none of the non-primary key attributes transitively depend on their primary keys, then we can say that the relation is in the third normal form of 3NF. Rules Followed in 3rd Normal Form in DBMS We can say that a relation is in the third normal form when it holds any of these given conditions in case of a functional dependency P -> Q that is non-trivial: P acts as a super key. Q acts as a non-prime attribute. Meaning, every element of Q forms a part of a candidate key.

20. To normalize a 2NF to 3NF, we have to determine if we have a transitive dependency in the table. In case a transitive dependency exists, then we remove those attributes that are transitively dependent from the relations. Note:  If P -> Q and Q -> R are two functional dependencies, then P -> R is known as a transitive dependency. When normalizing a 2NF relation to 3NF, we remove these transitive dependencies. CAND_NO CAND_NAME CAND_STATE CAND_COUNTRY CAND_AGE 1 TINA MAHARASHTRA INDIA 18 2 ANJALI RAJASTHAN INDIA 17 3 RAHUL RAJASTHAN INDIA 19 In the relation CANDIDATE given above: Functional dependency Set: {CAND_NO -> CAND_NAME, CAND_NO ->CAND_STATE, CAND_STATE -> CAND_CUNTRY, CAND_NO -> CAND_AGE} So, Candidate key here would be: {CAND_NO} For the relation given here in the table, CAND_NO -> CAND_STATE and CAND_STATE -> CAND_COUNTRY are actually true. Thus, CAND_COUNTRY depends transitively on CAND_NO. This transitive relation violates the rules of being in the 3NF. So, if we want to convert it into the third normal form, then we have to decompose the relation CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_COUNTRY, CAND_AGE) as: CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_AGE) STATE_COUNTRY (STATE, COUNTRY)

21. A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency. 3NF is used to reduce the data duplication. It is also used to achieve the data integrity. If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form. A relation is in third normal form if it holds at least one of the following conditions for every non-trivial function dependency X → Y. X is a super key. Y is a prime attribute, i.e., each element of Y is part of some candidate key. EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY 222 Harry 201010 UP Noida 333 Stephan 02228 US Boston 444 Lan 60007 US Chicago 555 Katharine 06389 UK Norwich 666 John 462007 MP Bhopal

22. Super key in the table above: {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on   Candidate key:  {EMP_ID} Non-prime attributes:  In the given table, all attributes except EMP_ID are non-prime. Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID). It violates the rule of third normal form. That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key. EMP_ID EMP_NAME EMP_ZIP 222 Harry 201010 333 Stephan 02228 444 Lan 60007 555 Katharine 06389 666 John 462007

23. EMP_ZIP EMP_STATE EMP_CITY 201010 UP Noida 02228 US Boston 60007 US Chicago 06389 UK Norwich 462007 MP Bhopal EMPLOYEE_ZIP table: Boyce Codd normal form (BCNF) BCNF is the advance version of 3NF. It is stricter than 3NF. A table is in BCNF if every functional dependency X → Y, X is the super key of the table. For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

24. Example:  Let's assume there is a company where employees work in more than one department. EMPLOYEE table: EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO 264 India Designing D394 283 264 India Testing D394 300 364 UK Stores D283 232 364 UK Developing D283 549 In the above table Functional dependencies are as follows: EMP_ID  →  EMP_COUNTRY   EMP_DEPT  →   {DEPT_TYPE, EMP_DEPT_NO}   Candidate key: {EMP-ID, EMP-DEPT} le to pay record $391.5 million privacy settlement! The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys. To convert the given table into BCNF, we decompose it into three tables:

25. EMP_ID EMP_COUNTRY 264 India 264 India EMP_COUNTRY table: EMP_DEPT DEPT_TYPE EMP_DEPT_NO Designing D394 283 Testing D394 300 Stores D283 232 Developing D283 549 EMP_DEPT table: EMP_ID EMP_DEPT D394 283 D394 300 D283 232 D283 549 EMP_DEPT_MAPPING table: Functional dependencies: EMP_ID   →    EMP_COUNTRY   EMP_DEPT   →   {DEPT_TYPE, EMP_DEPT_NO}   Candidate keys: For the first table:  EMP_ID For the second table:  EMP_DEPT For the third table:  {EMP_ID, EMP_DEPT}

26. Example— Take a look at these functional dependencies in the relation A (P, Q, R, S, T) Here, P -> QR, RS -> T, Q -> S, T -> P In the relation given above, all the possible candidate keys would be {P, T, RS, QR}. In this case, the attributes that exist on the right sides of all the functional dependencies are prime.

27. 4. Boyce-Codd Normal Form (BCNF) – A relation R is in BCNF if R is in Third Normal Form and for every FD, LHS is super key. A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key. Example 1 –  Find the highest normal form of a relation R(A,B,C,D,E) with FD set as {BC->D, AC->BE, B->E}

28. Find all possible candidate keys of the relation. Divide all attributes into two categories: prime attributes and non-prime attributes. Check for 1 st  normal form then 2 nd  and so on. If it fails to satisfy the n th  normal form condition, the highest normal form will be n-1. Example 1. Find the highest normal form of a relation R(A,B,C,D,E) with FD set {A->D, B->A, BC->D, AC->BE} Step 1. As we can see, (AC)+ ={A, C, B, E, D} but none of its subsets can determine all attributes of relation, So AC will be the candidate key. A can be derived from B, so we can replace A in AC with B. So BC will also be a candidate key. So there will be two candidate keys {AC, BC}. Step 2. The prime attribute is those attribute which is part of candidate key {A, B, C} in this example and others will be non-prime {D, E} in this example. Step 3. The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attributes. The relation is not in the 2nd Normal form because A->D is partial dependency (A which is a subset of candidate key AC is determining non-prime attribute D) and the 2nd normal form does not allow partial dependency. So the highest normal form will be the 1st Normal Form.

29. Example 2. Find the highest normal form of a relation R(A,B,C,D,E) with FD set as {BC->D, AC->BE, B->E} Step 1. As we can see, (AC)+ ={A,C,B,E,D} but none of its subsets can determine all attributes of relation, So AC will be the candidate key. A or C can’t be derived from any other attribute of the relation, so there will be only 1 candidate key {AC}. Step 2. The prime attribute is those attribute which is part of candidate key {A,C} in this example and others will be non-prime {B,D,E} in this example. Step 3. The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attributes. The relation is in 2nd normal form because BC->D is in 2nd normal form (BC is not a proper subset of candidate key AC) and AC->BE is in 2nd normal form (AC is candidate key) and B->E is in 2nd normal form (B is not a proper subset of candidate key AC). The relation is not in 3rd normal form because in BC->D (neither BC is a super key nor D is a prime attribute) and in B->E (neither B is a super key nor E is a prime attribute) but to satisfy 3rd normal for, either LHS of an FD should be super key or RHS should be a prime attribute. So the highest normal form of relation will be the 2nd Normal form.

30. Example 3.  Find the highest normal form of a relation  R(A,B,C,D,E)  with  FD set {B->A, A->C, BC->D, AC->BE}   Step 1.    As we can see, (B) +  ={B,A,C,D,E}, so B will be candidate key. B can be derived from AC using AC->B (Decomposing AC->BE to AC->B and AC->E). So AC will be super key but (C) +  ={C} and (A) +  ={A,C,B,E,D}. So A (subset of AC) will be candidate key. So there will be two candidate keys {A,B}.  Step 2.   The prime attribute is those attribute which is part of candidate key {A,B} in this example and others will be non-prime {C,D,E} in this example.  Step 3 .  The relation R is in 1 st  normal form as a relational DBMS does not allow multi-valued or composite attributes.  The relation is in 2 nd  normal form because B->A is in 2 nd  normal form (B is a superkey ) and A->C is in 2 nd  normal form (A is super key) and BC->D is in 2 nd  normal form (BC is a super key) and AC->BE is in 2 nd  normal form (AC is a super key).  The relation is in 3 rd  normal form because the LHS of all FD’s is super keys. The relation is in BCNF as all LHS of all FD’s are super keys. So the highest normal form is BCNF. 

31. Fourth Normal Form (4NF) : Any relation is said to be in the fourth normal form when it satisfies the following conditions: It must be in  Boyce Codd Normal Form (BCNF) . It should have no  multi-valued dependency . A multi-valued dependency is said to occur when there are two attributes in a table which depend on a third attribute but are independent of each other. For a functional dependency X->Y there will be a multi-valued dependency if there exists multiple values of Y for a single value of X. Thus if a relation is in BCNF and also it does not have any kind of multi-valued dependency then that relation will be in 4NF. In order to denote a multi-valued dependency, “->->” this sign is used. In this relation Student-ID 1 has thus opted for two courses and has two hobbies. Similarly Student-ID 2 has opted for two courses and has two hobbies. Thus it contains multi-valued dependencies. It is not in 4NF and in order to convert it into 4NF it can be decomposed into two relations:

32. Student-ID Course Hobby 100 Science Dancing 100 Maths Singing 101 C# Dancing 101 PHP Singing Student-ID Hobby 100 Dancing 100 Singing 101 Dancing 101 Singing Student-ID Course 100 Science 100 Maths 101 C# 101 PHP

33. Fifth Normal Form (5NF) : Any relation in order to be in the fifth normal form must satisfy the following conditions: It must be in Fourth Normal Form (4NF). It should have no join dependency and also the joining must be lossless. In the fifth normal form the relation must be decomposed in as many sub-relations as possible so as to avoid any kind of redundancy and there must be no extra tuples generated when the sub-relations are combined together by using natural join. A relation in 5NF cannot be decomposed further without any kind of modification in the meaning or facts. 5NF is also known as Project Join Normal Form (PJNF). Student-ID Mobile Number Hobby 123 9999900000 Dancing 124 9999900000 Singing 124 9999900000 Dancing 123 8975622122 Singing 123 9999900000 Singing Student-ID Mobile Number 123 9999900000 123 8975622122 124 9999900000 Student-ID Hobby 123 Dancing 123 Singing 124 Singing 124 Dancing Mobile Number Hobby 9999900000 Dancing 9999900000 Singing 8975622122 Singing Thus if natural join is performed on all the three relations then there will be no extra tuples. Hence R1, R2 and R3 are in fifth normal form (5NF).

34. PL/SQL

35. Oracle PL/SQL  is an extension of SQL language that combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries. PL/SQL ensures seamless processing of SQL statements by enhancing the security, portability, and robustness of the Database. Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object-oriented concepts. The PL/SQL Full form is “Procedural Language extensions to SQL”. n PL/SQL, a block without any name is called Anonymous Block. PL/ SQL block consists of various functions, library, procedures, trigger, packages etc. The following points should be remembered while writing a PL/SQL program – In PL/SQL the semicolon (;) is placed at the end of an SQL statement or PL/SQL control statement. Section keyword DECLARE, BEGIN and EXECUTION are not followed by semicolons. END keyword and all other PL/SQL statements require a semicolon to terminate the statements.

36. The PL/SQL architecture mainly consists of following three components: PL/SQL Block PL/SQL Engine Database Server PL/SQL block: This is the component which has the actual PL/SQL code. This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors) It also contains the SQL instruction that used to interact with the database server. All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.

37. Following are the different type of PL/SQL units. Anonymous Block Function Package Specification Trigger Type Type Body PL/SQL Engine PL/SQL engine is the component where the actual processing of the codes takes place. PL/SQL engine separates PL/SQL units and SQL part in the input The separated PL/SQL units will be handled by the PL/SQL engine itself. The SQL part will be sent to database server where the actual interaction with database takes place. It can be installed in both database server and in the application server. Database Server: This is the most important component of Pl/SQL unit which stores the data. The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server. It consists of SQL executor which parses the input SQL statements and execute the same. SQL PL/SQL SQL is a single query that is used to perform DML and DDL operations. PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc. It is declarative, that defines what need to be done, rather than how things need to be done. PL/SQL is procedural that defines how the things needs to be done. Execute as a single statement. Execute as a whole block. Mainly used to manipulate data. Mainly used to create an application. Interaction with a Database server. No interaction with the database server. Cannot contain PL/SQL code in it. It is an extension of SQL, so that it can contain SQL inside it.

38. What is PL/SQL block? In PL/SQL, the code is not executed in single line format, but it is always executed by grouping the code into a single element called Blocks. Block Structure PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are different sections of PL/SQL blocks. Declaration section Execution section Exception-Handling section

39. Declaration Section This is the first section of the PL/SQL blocks. This section is an optional part. This is the section in which the declaration of variables, cursors, exceptions, subprograms, pragma instructions and collections that are needed in the block will be declared. This section starts with the keyword ‘DECLARE’ for triggers and anonymous block. For other subprograms, this keyword will not be present. Instead, the part after the subprogram name definition marks the declaration section. This section should always be followed by execution section.

40. Execution Section Execution part is the main and mandatory part which actually executes the code that is written inside it. Since the PL/SQL expects the executable statements from this block this cannot be an empty block, i.e., it should have at least one valid executable code line in it. Below are few more characteristics of this part. This can contain both PL/SQL code and SQL code. This can contain one or many blocks inside it as a nested block. This section starts with the keyword ‘BEGIN’. This section should be followed either by ‘END’ or Exception-Handling section (if present) It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.

41. Exception-Handling Section: The exception is unavoidable in the program which occurs at run-time and to handle this Oracle has provided an Exception-handling section in blocks. This section can also contain PL/SQL statements. This is an optional section of the PL/SQL blocks. This is the section where the exception raised in the execution block is handled. This section is the last part of the PL/SQL block. Control from this section can never return to the execution block. This section starts with the keyword ‘EXCEPTION’. This section should always be followed by the keyword ‘END’.

42. DECLARE --optional <declarations> BEGIN --mandatory <executable statements. At least one executable statement is mandatory> EXCEPTION --optional <exception handles> END; --mandatory / Note:  A block should always be followed by ‘/’ which sends the information to the compiler about the end of the block.

43. PL/SQL identifiers There are several PL/SQL identifiers such as variables, constants, procedures, cursors, triggers etc. Variables: Like several other programming languages, variables in PL/SQL must be declared prior to its use. They should have a valid name and data type as well. Syntax for declaration of variables: variable_name datatype [NOT NULL := value ]; SQL> SET SERVEROUTPUT ON; SQL> DECLARE var1 INTEGER; var2 REAL; var3 varchar2(20) ; BEGIN null; END; / Output: PL/SQL procedure successfully completed.

44. SET SERVEROUTPUT ON : It is used to display the buffer used by the dbms_output . var1 INTEGER  :  It is the declaration of variable, named  var1  which is of integer type. There are many other data types that can be used like float, int , real, smallint , long etc. It also supports variables used in SQL as well like NUMBER( prec , scale), varchar , varchar2 etc. PL/SQL procedure successfully completed. :  It is displayed when the code is compiled and executed successfully. Slash (/) after END; :  The slash (/) tells the SQL*Plus to execute the block. 1.1) INITIALISING VARIABLES: The variables can also be initialized just like in other programming languages. SQL> SET SERVEROUTPUT ON; SQL> DECLARE var1 INTEGER := 2 ; var3 varchar2(20) := ‘Hello' ; BEGIN null; END; / Output: PL/SQL procedure successfully completed. Explanation: Assignment operator (:=) : It is used to assign a value to a variable.

45. Displaying Output: The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user to display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. SQL> SET SERVEROUTPUT ON; SQL> DECLARE var varchar2(40) := ‘Hello' ; BEGIN dbms_output.put_line ( var ); END; / Output: Hello PL/SQL procedure successfully completed. Explanation: dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.

46. Using Comments: Like in many other programming languages, in PL/SQL also, comments can be put within the code which has no effect in the code. There are two syntaxes to create comments in PL/SQL : Single Line Comment: To create a single line comment , the symbol – – is used. Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is used. Example to show how to create comments in PL/SQL : SQL> SET SERVEROUTPUT ON; SQL> DECLARE -- I am a comment, so i will be ignored. var varchar2(40) := ‘Hello' ; BEGIN dbms_output.put_line ( var ); END; / Output: Hello PL/SQL procedure successfully completed.

47. Taking input from user: SQL> SET SERVEROUTPUT ON; SQL> DECLARE -- taking input for variable a a number := &a; -- taking input for variable b b varchar2(30) := &b; BEGIN null; END; / Output: Enter value for a: 24 old 2: a number := &a; new 2: a number := 24; Enter value for b: ‘ HelloWorld ' old 3: b varchar2(30) := &b; new 3: b varchar2(30) := ‘ HelloWorld '; PL/SQL procedure successfully completed.

48. --PL/SQL code to print sum of two numbers taken from the user. SQL> SET SERVEROUTPUT ON; SQL> DECLARE -- taking input for variable a a integer := &a ; -- taking input for variable b b integer := &b ; c integer ; BEGIN c := a + b ; dbms_output.put_line ('Sum of '||a||' and '||b||' is = '||c); END; / Enter value for a: 2 Enter value for b: 3 Sum of 2 and 3 is = 5 PL/SQL procedure successfully completed.