Scribd
Upload
115 views66 pages

Module 3-SQL VTU Notes

Module 3-SQL VTU notes

Uploaded by

Samanth
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
115 views66 pages

Module 3-SQL VTU Notes

Module 3-SQL VTU notes

Uploaded by

Samanth
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
Database Management System Module 3 Chapter 1: SQL- Advances Queries 1.1 More Complex SQL Retrieval Queries Additional features allow users to specify more complex retrievals from database Ww 4 Comparisons Involving NULL and Three-Valued Logic SQL has various rules for dealing with NULL values NULL is used to represent a missing value, but that it usually has one of three different interpretations—value Example 1. Unknown value. A person's date of birth is not known, so it is representec by NULL in the database 2. Unavailable or withheld value. A person has a nome phone but does not want it to be listed, so itis withheld and represented as NULL in the database. 3. Not applicable attribute. An attribute CcllegeDegree would be NULL for a person who has no college degrees because it does not apply to that person. Eact individual NULL value is considered to be different from every other NULL value in the various database records. When a NULL is involved in a comparison operation, the result is considered te be UNKNOWN (it may be TRUE or it may be FALSE). Hence SQL uses a three-valued logic with values TRUE, FALSE, anc UNKNOWN insteac of the standaré two-valued (Boolean) logic with values TRUE or FALSE, It is therefore necessary to define the results (or truth values) of three- valued logical expressions when the logical connectives AND, OR, and NOT are used Table 5.1 Logical Connectvas in Thice-Valued Lage @ AND TRUE FALSE UNKNOWN TRUE TRUE FALSE UNKNOWN FALSE PASE FALSE FALSE UNKNOWN UNKHOWN FALSE UNKNOWN oR TRUE FALSE UNKNOWN TRUE TRUE TRUE TRUE FALSE TRUE FALSE UNKNOWN UNKNOWN TRUE UNKNOWN UNKNOWN © Nor TRUE FALSE FALSE TRUE UNKNOWN UNKNOWN Page 1 Database Management System ‘The rows and columns represent the values of the results of comparison conditions, which woule typically appearin the WHERE clause of an SQL query, In select-project-join queries, the general rule is that only those combinations of tuples that evaluate the logical expression in the WHERE clause of the query to TRUE are selected. Tuple combinations that evaluate to FALSE or UNKNOWN are not selected SQL allows queries that check whether an attribute value is NULL using the comparison operators 1S or IS NOT. Example: Retrieve the names of all employees who do not have supervisors. SELECT Fname Lname FRON EMPLOYEE WHERE Super_ssn IS NULL; 1.1.2 Nested Queries, Tuples, and Set/Multiset Comparisons Some queries require that existing values in the database be fetched and ther usec in a comparison condition. Such queries can be conveniently formulated oy using nested queries, which are complete select-from-where blocks within the WHERE clause of another query. That other query is called the outer query Examplet: List the project numbers of projects that have an employee with last name ‘Smith’ as manager ‘SELECT DISTINCT Prumber FROM PROJECT WHERE Pnumber IN (SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='smith’); Example2: List the project numbers of projects that have an employee with last aame ‘Smith’ as either manager or as worker, SELECT DISTINCT Prumber FROM PROJECT WHERE Pnumber IN (SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='smith’) OR PnumberIN (SELECT Pno FROM WORKS_ON, EMPLOYEE WHERE Essn=Ssn AND Lname='smith’); We make use of comparison operator IN. which compares a value v with a set (or multiset) of values V and evaluates to TRUE if vis one of the elements in V. Page 2 Database Management System ‘The first nested query selects the project numbers of projects that have an employee with last name ‘Smith involved as manager. The second nested query selects the project numbers of projects that nave an employee with last name ‘Smith involved as worker. In the outer query, we use the OR logical connective to retrieve a PROJECT tuple if the PNUMBER value of that tuple is in the result of either nested query SQL allows the use of tuples of values in comparisons by placing them within parentheses, For example, the following query will select the Essns of all employees who work the same (project, hours) combination on some project that employee ‘John Smith’ (whose Ser = '123456789') works on SELECT DISTINCT Esen FROM WORKS_ON WHERE — (Pho, Hours) IN( SELECT Po, Hours FROM WORKS_ON WHERE — Essn="123456789" ); In this example, the IN operator compares the subtuple of veluesin parentheses (PnoHours) within eact tuple in WORKS_ON with the set of type-compatible tuples produced by the nested query. Nested Queries::Comparison Operators Other comparison operators can be used to compare a single value v to a set or multiset_V. The = ANY (or = SOME) operator returns TRUE if the value v is equal to some value in the set Vand is ence equivalent to IN. The two keywords ANY and SOME have the same effect. The keyword ALL can also be combined with eact of these operators. For example, the comparisor condition (v > ALL V| returns TRUE if the value v is greater thar all the values in the set (or multiset) V. For example is the following query, which returns the names of employees whose salary is greater than the salary of all the employees in department 5: SELECT Lname Fname FROM EMPLOYEE WHERE Salery > ALL ( SELECT Salary FROM EMPLOYEE WHERE Dno=5 ); In general, we can have several levels of nested queries. We can once again be faved with possible ambiguity among attribute names if attributes of the same name exist—one in a relation in the FROM clause of the outer query, and anotherin a relation in the FROM clause of the nested query The nule is that a reference to an unqualified attribute refers to the ‘elation declared in the innermost nested query. To avoid potential errors and ambiguities, create tuple variables (aliases) for all tables referenced in SQL query Page 3 Database Management System Example: Retrieve the name of each employee who has a dependent with the same first name anc s the same sex as the employee SELECT EFname, [Link] FROM EMPLOYEE AS E WHERE [Link] IN ( SELECT FROM DEPENDENT AS D WHERE [Link]=D.Dependent_name AND [Link]=[Link] ); In the above nested query we must qualify [Link] because it refers to the Sex attribute of EMPLOYEE from the outer query, and DEPENDENT also has an attribute called Sex. 1.1.3 Correlated Nested Queries ‘Whenever @ condition in the WHERE clause of a nested query references some attribute of a ‘elation declared in the outer query, the two queries are said to be correlated. Example: SELECT EFname, [Link] FROM EMPLOYEE AS E WHERE [Link] IN ( SELECT E: FROM DEPENDENT AS D WHERE [Link]=D.Dependent_name AND [Link]=[Link] ); The nested query 's evaluated once for each tuple (or combination of tuples) in the outer query. we can think of query in above example as follows: For each EMPLOYEE tuple, evaluate the nested query, which retrieves the Essn values for all DEPENDENT tuples with the same sex and name as thal EMPLOYEE tuple; f the Ssn value of the EMPLOYEE tuple is in the result of the nested query, then select that EMPLOYEE tuple. 1.1.4 The EXISTS and UNIQUE Functions in SQL EXISTS Functions The EXISTS function in SQL is used to check whether the result of a correlated nestec query is empty (contains no tuples) or not. The result of EXISTS 's a Boolean value + TRUE ifthe nested query result contains at least one tuple, or + FALSE if the nested query result contains no tuples. For example, the query to retrieve the name of each employee who has a dependent with the same first name anc is the same sex as the employee can be written using EXISTS functions as follows: SELECT EFname, [Link] Page 4 Database Management System FROM EMPLOYEE AS E WHERE EXISTS ( SELECT * FROM DEPENDENT AS D WHERE [Link]=[Link] AND [Link]=D Sex AND [Link]=D.Dependent_name) Example: List the names of managers who have alleast one dependent SELECT Fname, Lname FROM EMPLOYEE WHERE EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn=Essn ) AND EXISTS (SELECT * FROM DEPARTMENT WHERE Ssn=Mgr_ssn ); In general, EXISTS(Q) retums TRUE if there isat least one tuple in the result of the nested query Q, and it returns FALSE otherwise. NOT EXISTS Functions NOT EXISTS(Q) returns TRUE f there are no tuples in the result of nested query Q, and it returns FALSE otherwise. Example: Retrieve the names of employees who have no dependents. SELECT Fname Lname FROM EMPLOYEE WHERE NOT EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn=Essn ); For each EMPLOYEE tuple, the correlated nestec query selects all DEPENDENT tuples whose Essn value matches the EMPLOYEE Ssn; ifthe result is empty, no dependents are related to the ‘employee, so we select that EMPLOYEE tuple and retrieve its Fname anc Lname Example: Retrieve the name of each employee who works on all the projects contrdled by department number 5 SELECT Frame, Lname Page S Database Management System FROM EMPLOYEE WHERE NOT EXISTS ( ( SELECTPrumber FROM PROJECT WHERE Dnum=5) EXCEPT ( SELECT Pno FROMWORKS_ON WHERE Ssn=Essn) ) UNIQUE Functions UNIQUE(Q) returns TRUE if there are ne duplicate tuples in the result of query Q: otherwise, it retums FAI SF. This can be used to test whether the result of a nested query is @ set or a mule, 1.4.5 Explicit Sets and Renaming of Attributes in SQL IN SQL it is possible to use an explicit set of values in the WHERE clause, rather than @ nested query. Such asetis enclosed in parentheses, Example: Retrieve the Social Security numbers of all employees who work on project numbers 1, 2 or, SELECT DISTINCT Fssr FROM WORKS_ON WHERE Pno IN (1, 2, 3) In SQL it is possible to rename any attribute that appears in the result of a query by adding the qualifier AS followed by the desired new name Example: Retrieve the last name of each employee and his or her supervisor SELECT [Link] AS Employee_name [Link] AS Supervisor_name FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn=[Link] Page 6 Database Management System 1.1.6 Joined Tables in SQL and Outer Joins ‘An SQL join clause combines records from two or more tablesin a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. SQL specifies four types of JOIN 1. INNER, 2. OUTER 3, EQUNON and 4, NATURAL JOIN INNER JOIN ‘An inner join is the most common joir operation usec in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A lané 8) dasec upor the join- predicate (the condition). The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every recorc in table B)—then return all records which satisfy the join predicate Example: SELECT * FROM employee INNER JOIN department ON [Link] = department. dnumber, EQUIJOIN and NATURAL JOIN An EQUNOIN is a specific type of comparator-based join that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equijin. NATURAL JOIN is a type of EQUIJOIN where the join predicate arises implicily by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joinec table contains only one column for each pair of equally named columns. SELECT Frame, Lnamo, Addross FROM EMPLOYEE|NATURAL JOIN] DEPARTMENT WHERE —Dname="Research’s Page7 Database Management System If the names of the join attributes are not the same_in the base relations, it's possible to rename the attributes so that they match, and then to apply NATURAL JOIN. In this case, the AS construct can be used to rename a relation and all ts attributes in the FROM clause. CROSS JOIN retums the Cartesiar product of rows from tables in the join. In other words it will produce rows which combine each row from the first table with each row from the second table. OUTERJOIN ‘An outer join does not require each recorc in the two joined tables to have a matching record. The joined table retains each record-evenif no other matching record exists. Outer joins subdivide further into + Left outer joins + Right outer joins + Full outerjoins No implicit join-notation for outer joins existsin standard SQL. » LEFT OUTER JOIN » Every tuple in left table must appear in result » If no matching tuple Padded with NULL values for attributes of right table GOK SELECT ELnae AS Ene new SLrae AS Sip: nne| SE FROM EMPLOYEE AS E, EMPLOYEE AS S rao PEE ol : q WHERE [Link]; [eee eect ee Sapien bbc Q8B: SELECT —Elname AS Employee name, | OUTER JOR mari urt coi [Link] AS Supervisor_name FROM EMPLOYEE AS E|LEFT OUTER JOINJEMPLOYEE AS S JONJE Super_ssn=S.$sn); Page 8 Database Management » RIGHT OUTER JOIN » Every tuple in right table must appear in result » Ifo matching tuple Padded with NULL values for the attributes of left table » FULL OUTER JOIN » a full outer join combines the effect of applying both left and right outer joins. » Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. » For those records that do match, a single row will be produced in the result set (containing fields populated from both tables). Not all SQL implementations have implemented the new syntax of joined tables. In some systems, a different syntax was used to specify outer joins by using the comparison operators +=, =+, and +=+ for left, right, and full outer join, respectively For example, this syntax is available in Oracle. To specify the left outer join in Q8B using this syntax, we could write the query Q8C as follows: asc: SELECT [Link], [Link] FROM EMPLOYEE E, EMPLOYEE S WHERE E.Super_ssn += [Link]; Page 9 Database Management System MULTIWAY JOIN It is also possible to nest join specifications; that is, one of the tables in a join may itself be a joinec table. This allows the specification of the join of three or more tables as a single joined table, which is called a multiway join. Example: For every project located in ‘Stafford’, list the project number, the contraling department umber, and the department manager's last name,address, and birth date. SELECT Pnumber, Dnum, Lname, Address, Bdate FROM (PROJECT JOIN DEPARTMENT ON Dnum=Dnumber) JOIN EMPLOYEE ON Mgr_ssn=Ssn) WHERE Plocation='Stafford’ 1.1.7 Aggregate Functions in SQL ‘Aggregate functions are used to summarize information from multiple tuples into 2 single-tuple summary. A number of built-in aggregate functions exist! COUNT, SUM, MAX, MIN, and AVG. The COUNT function returns the number of tuples or values as specifiec in @ query. The functions SUM, MAX. MIN, and AVG can be applied to a set or multiset of numeric values and return respectively the sum, maximum value, minimum value, and average (mean) of those values. These functions can be used in the SELECTclause or in a HAVING clause (which we introduce later). The functions MAX and MIN can also be used with attributes that have nonnumeric domains the domain values nave a total ordering among one another. Examples 1. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM EMPLOYEE; 2. Find the sum of the salaries of all employees of the Research department, as well as the maximum salary, the minimum salary, and the average salaryin this department. SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber) WHERE Dname="Research’ 3. Count the number of distinct salary values in the database. ‘SELECT COUNT (DISTINCT Selary) FROM EMPLOYEE; Page 10 Database Management System 4. To retrieve the names of al employees who have two or more dependents SELECT Lname, Fname FROM EMPLOYEE WHERE ( SELECT COUNT (") FROM DEPENDENT WHERE Ssn=Essn ) >= 2; 1.1.8 Grouping: The GROUP BY and HAVING Clauses Grouping is used to create subgroups of tuples before summarization. For example, we may want to find the average salary of employees in each department or the number of employees who work on each project. In these cases we need to partition the relation into non overlapping subsets (or groups) of tuples. Each group (partition) will consist of the tuples that have the same value of some attribute(s), called the grouping attribute(s). ‘SQL has @ GROUP BY clause for this purpose. The GROUP BY clause specifies the grouping attributes, which should also appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s). Example: For each department, retrieve the department number, the number of employees in the department, and their average salary. SELECT Dno COUNT (*), AVG (Salary) FROM EMPLOYEE GROUF BY Dno; Frame [Mt [treme | Sen Salay [Superssn [Dro | Bro [Count ') [Arg (Salary) John_| B | Smith | 199486709 | [90000 aoa44sss6| 6 ms | 4 | 39260 Frankin | 1 | Wong | 333440805 | [40000] esasssoss| 9 |||] « | 3 | s1000 Ramesh| K | Narayan) 6688ecasa| | 36000] asaaess55| 5 +[t ‘55000 Tyce | A_| English | 459450455 |---| 25000 | a09405555| 5 Raut of O24 ‘Aicia_| 1 | Zaaya | 990087777 | [26000| 907664001 | 4 Tenner | S| Walase | ow7enea21 | [42000] esaseses5| 6 ‘Anmad | V_|ebbar | 967087087 | | 25000| os7esa321 | 4 Tames | € | Bong | agese6s0s| [os000[ nu 11h ‘Grouping EMPLOYEE tuple by the vaue of Do IFNULLs exist in the grouping attribute, then a separate groups created for alltuples with a NULL value in the grouping attribute, For example, if the EMPLOYEE table nad some tuples that had NULL for the grouping attribute Dno, there would be a separate group for those tuples in the result of query Page 11 Database Management System Example: For each project, retrieve the project number, the project name, and the number of ‘employees who work on that project. ‘SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname; ‘Above query shows how we can use a join conditior in conjunetion with GROUF BY. In this case the grouping and functions are applied after the joining of the two relations. HAVING provides a condition on the summary formation regarding the group of tuples associated with each value of the grouping attributes. Only the groups that satisfy the condition are retrieved in the result of the query. Example: For each project on which more than two employees work, ‘etrieve the project number. the project name, and the number of employees who work on the project. SELECT Phumber, Prame, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Prame HAVING COUNT ("> 2 Prana ae Tag [Ps [me] ise ota we nats Prose 1 veasno7e9| 1325 [|| MAING enstorotcas Prodan i ‘assaos4o3| 100 Product 2 vz9406760[ 275 Prods o ssassas3| 2 200 Prods 2 ‘asoaassss| 2) 100 Proce a eaeaasaae| 3 | 400 | |_| Proce a saaassss| 8) 100 ‘Computation | To aoasanso6 | 10100 Gomputerzaton | 10 ese07777 | 10 | 100 Gomputereaton | 10 ‘evas7ae7 | 10) 350 Reorganization [20 gaasanea8 | 20100 Reorganization [20 (e7e54391 | 00) 150 eorganizaten | 20 ‘80560856 | 20 | NULL Newbenefie 20 eveeree7 | 30 | 50 Reena 20 e7es4s97 | 80) 200 Nowienete 2 esear777 | 30) 300 Page 12 Database Management System Prams Phim o] ean Poo | Hews Frame Cant) Product 2 vasasorae | 2 [5 |) --e [rest 8 Product 2 409408403| 2 | 200 >| computeraion | 3 ProduotY 2 saea5965 | 2 ro |Recremnaaten [8 Comauterzation | 10 ‘399445555 | 10 own 3 Comeutersalion | 10 /--| 900aa7777 | 70 Rosai of 2 Comeuterzation | 10 987987967 | 10 Pmicentonl Reorganzation 20. asaaasnos | 90) Reorganization | _—_20 (987654321 | 20 i Rearganzation | 20 ‘88686556 20 ‘Newbenefis| 30 967987987 | 30 ‘Newbenefis| 30 967654921 | 30 Newbeneis a0 o0e7777 | 30 Example: For eact project, retrieve the project number, the projec! name and the number of employees from department 5 who work on the project. SELECT Phumber, Prame, COUNT (*) FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pnumber=Pno AND Ssn=i GROUP BY Pnumber, Pname; Example: For each department that has more than five employees, retrieve the department number and the number ofits employees who are making more than $40, 000. SELECT Dnumber, COUNT (*) FRON DEPARTMENT, EMPLOYEE WHERE Dnumber=Dno AND Salary>40000 AND (SELECT Ono FROM EMPLOYEE GROUP BY Dno HAVING COUNT (*) > 5) 1.1.9 Discussion and Summary of SQL Queries A retrieval query in SQL can consist of up to six clauses but only the first two—SELECT and FROM—are [Link] query can span severa lines, and is endec by @ semicolon. Query terms are separated by spaces, and parentheses can be used to group relevant parts of a queryin the standard [Link] clauses are specified ir the following order, with the clauses between square rackets [...] being optional Page 13 Database Management System SELECT FROM {WHERE } ([ GROUP BY ] [ HAVING } [ ORDER BY ]; The SELECT clause lists the attributes or functions to be retrieved. The FROM clause specifies all ‘elations (tables) needec in the query including joinec relations, out not those in nested queries ‘The WHERE clause specifies the conditions for selecting the tuples from these relations, including join conditions if needed. GROUP BY specifies grouping attributes, whereas HAVING specifies a condition on the groups being selectec vather than on the individual tuples. Finally ORDER BY specifies an order for displaying the result of a query. A query 's evaluated conceptually by first applying the FROM clause to identity all tables involved in the query or to materialize any joined tables followed by the WHERE clause to select and join tuples, and then by GROUP BY anc HAVING. ORDER BY is appliec at the end to sort the query result Fach DBMS has specid query optimization routinas to decide on an execution olan that is efficient to execute In general, there are numerous ways to specify the same query in [Link] flexibility in specifying queries has advantages and disadvantages. = The main advantage is that users can choose the technique with which they are most comfortable when specifying a query. For example, many queries may be specified with join conditions in the WHERE clause or by using joined relations in the FROM clause, or with some form of nestec queries anc the IN comparison. From the programmer's and the system's point of view regarding query optimization, it s generally preferable to write a query with as lttle nesting anc implied ordering as possible. + The disadvantage of having numerous ways of specifying the same query is that this may confuse the user, who may not know which technique to use to specify particular types of queries. Another problem is that it may be more efficient to execute a query specifiec in one way than the same query specifiec in an alternative way Page 14 Database Management System 1.2 Specifying Constraints as Assertions and Actions as Triggers 4.2.4 Specifying General Constraints as Assertions in SQL Assertions are used tc specify additional types of constraints outside scope of built-r relational model constraints. In SQL users can specify genera constraints via declarative assertions, using the CREATE ASSERTION statement of the DDL-Each assertion is given a constraint name and is specified via a condition similar to the WHERE clause of an SQL query. General form : CREATE ASSERTION CHECK () For the assertion to be satisfied, the condition specified after CHECK clause must return true. For example, to specify the constraint that the salary of an employee must not be greater than the salary of the manager of the department that the employee works for in SQL we can write the following assertion: CREATE ASSERTION SALARY_CONSTRAINT CHECK ( NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE [Link]>[Link] AND E,Dno=D Dnumber AND D.Mgt_ssn=[Link] ) ) The constraint name SALARY_CONSTRAINT is followed by the keyword CHECK, which is followed oy a condition in parentheses that must hold true on every database state for the assertion to be satisfied. The constraint name can be used later to refer to the constraint or to modify or drop it. Any WHERE clause condition can be used, but many constraints can be specified using the EXISTS and NOT EXISTS style of SQL conditions. By including this query inside a NOT EXISTS clause, the assertion will specify that the result of this query must be empty so that the condition will always be TRUE. Thus, the assertion is violated if the result of the query is not empty Example: consider the bank database with the following tables + branch (branch_name, branch_city, assets) * customer (customer_name, customer_street, customer_city) © account (account_number, branch_name, balance) + loan (lean_number, branch_name, amount) + depositor (customer_name, account_number) * borrower (customer_name, loan_number) Page 15 Database Management System 1. Write an assertion to specify the constraint that the Sum of loans taken by a customer does not exceed 100,000 CREATE ASSERTION sumofloans CHECK (100000> = ALL SELECT customer_name,sum(amount) FROM borrower b loan | WHERE b.loan_number=Lloan_number GROUP BY customer_name ); 2. Write an assertion to specify the constraint that the Number of accounts for each customer in a given branch 's at most two CREATE ASSERTION NumAccounts CHECK ( 2>=ALL SELECT customer_name,branch_name, count(") FROM account A , depositor C WHERE A.account_number = D.account_number GROUP BY customer_name, branch_name ); 1.2.2 Introduction to Triggers in SQL A trigger is a procedure that runs automatically when a certain event occursin the DBMS. In many cases it is convenient to specify the type of action to be taken when certain events occur and when certain conditions are satisfied. The CREATE TRIGGER statement is usec to implement such actions in SQL. General form: CREATE TRIGGER BEFORE | AFTER | FOR EACH ROW |FOR EACH STATEMENT WHEN () ‘trigger has three components 4, Event: When this event happens, the trigger is activated + Three event types : Insert, Update, Delete © Two triggering times Before the event After the event Page 16 Database Management System 2. Condition (optional): if the condition is true, the trigger executes, otherwise skipped 3. Action: The actions performed by the trigger Wher the Event occurs and Condition is true, execute the Action Create Trigger ABC Create Trigger XYZ Before Insert On After Update On Students Students ee This trigger is activated when an update This trigger is activated when an insert statement is issued, but before the new record is inserted Statement fe Issued and after the update is Does the trigger execute for eact updated or deleted record, or once for the entire statement 7. We define such granularity as follows Create Trigger This is the event Before| After Insert| Update|Delete For Each Row | For Each Statement — soe This is the granularity Create Trigger XYZ Create Trigger XYZ After Update ON Before Delete ON For each statement For each row This trigger is activated once (per UPDATE This triggeris activated before deleting each statement) after all records are updated record Page 17 Database Management System In the action, you may want to reference: + The new values of inserted or updated records (:new) + The old values of deleted or updated records. (sold) CreateTrigger EmpSal After Insert or Update On Employee Inside “When”, the “new” anc For Each Row —_— “old” should not have “:” When ([Link] >150,000) Begin Trigger body if ([Link] < 100,000) End; Inside the trigger body, they should have “" Examples: 1) If the employee salary increased by more than 10%, then increment the rank field by 1. In the case of Update event only, we can specify which columns Create Trigger Ei Before Update Of salary On Employee For Each Row Begin IF (:[Link] > (:[Link] 1.1) Then [Link] := :[Link] + 13 End IF; End; 1 We changed the new value of rank field The assignment operator has “" 2) Keep the bonus attribute in Employee table always 3% of the salary attribute Create Trigger EmpBonus eit two events atthe same time Before Insert Or Update On Employee For Each Row Begin [Link] := :new. salary * 0.03; End; _— The bonus value is always computec automatically Page 1€ Database Management System 3. Suppose we want to check whenever an employee's salary is greater than the salary of his or her direct supervisorin the COMPANY database = Several events can trigger this rule: + inserting a new employee recorc + changing an employee's salary or + changing an employee's supervisor = Suppose that the action to take would be to call an extemal stored procedure SALARY_VIOLATION which will notify the supervisor CREATE TRIGGER SALARY_VIOLATION BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE FOR EACH ROW WHEN ( [Link] > ( SELECT SALARY FROM EMPLOYEE WHERE SSN = NEW.SUPERVISOR_SSN)) ) INFORM_SUPERVISOR(NEW.Supervisor_ssn, [Link] ); = Thetriggeris given the name SALARY_VIOLATION, which can be used to remove or deactivate the trigger later In this example the events are: inserting a new employee record, changing an employee's salary, or changing an employee's supervisor + Theaction is to execute the stored procedure INFORM_SUPERVISOR Triggers can be usec in various applications, such as maintaining database consistency, monitoring database updates. Assertions vs. Triggers = Assertions de not modify the data, they only check certain conditions. Triggers are more powerful because the can check conditions and also modify the data * Assertions are not linked to specific tables in the database and nol linked to specific events, Triggers are linked to specific tables and specific events + All assertions can be implemented as triggers (one or more). Not all triggers can be implemented as assertions Page 16 Database Management System Example: Triggervs. Asse All new customers opening an account must have opening balance >= $100. However, once the account is opened their balance can fall below that amount. I We need triggers, assertions cannot be used Trigger Event: Before Insert Create Trigger OpeningBal Before Insert On Customer For Each Row Begin IF (:[Link] is null or :[Link] < 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Balance should be: End IF; End; 4.3 Views (Virtual Tables) in SQL 1.3.1 Concept of a Viewin SQL A view in SQL terminology is a single table thal is derived from other tables. other tables can be vase tables oF previously defined views. A view does not necessarily exist in physica form it is, considered to he @ virtua table, in contrast te nase tables, whose tuples are always physically storec in the database. This limits the possible update operations that can be applied to views, but +t does not provide any limitations on querying a view. We can think of a view as a way of specifying a table that we need to reference frequently, even thougt it may not exist physically. For example, referring to the COMPANY database. we may frequently issue queries that retrieve the employee ame and the project names that the employee works on. Rather than having to specify the join of the three tables EMPLOYEE, WORKS_ON, anc PROJECT every time we issue this query, we car define a view thal is specifiec as the result of these joins. Then we can issue queries on the view, which are specified as single table retrievals rather than as retrievals involving two joins on three tables. We call the EMPLOYEE, WORKS_ON, and PROJECT tables the defining tables of the view. Page 2C Database Management System 1.3.2 Specification of Views inSQL In SQL, the command to specify a view is CREATE VIEW. The view is given a (virtual) table name (or view name), a list of attribute names, and a query to specify the contents of the view. If none of the view attributes results from applying functions or arithmetic operations we do not nave to specify new attribute names for the view, since they would be the same as the names of the attributes of the defining tables in the default case. Example 1: CREATE VIEW WORKS_ON1 AS SELECT Fname, Lname, Pname Hours FROM EMPLOYEE, PROJECT, WORKS_ON WHERE Ssn=Essn AND Pno=Pnumber; Example 2: CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal) AS SELECT Dname, COUNT ("), SUM (Selary) FROM DEPARTMENT, EMPLOYEE WHERE Dnumber=Dno GROUP BY Dname In example 1, we did not specify any new attribute names for the view WORKS_ONI. In this case, WORKS_ON% inherits the names of the view attributes from the defining tables. EMPLOYEE, PROJECT, and WORKS_ON Example 2 explicitly specifies new attribute names for the view DEPT_INFO, using a one-to-one correspondence between the attributes specified in the CREATE VIEW clause and those specified in the SELECT clause of the query that defines the view. WoRKS ON Fea a DEPT_INFO Deptrame | Neofempe | Total sal We can now specify SOL queries on a view—or virtual table—in the same way we specify queries involving base tables. For example, to retrieve the last name and first name of all employees who work on the ‘ProductX project, we can utlize the WORKS_ON1 view and specify the query as Page 21 Database Management System SELECT =name, Lname FROMWORKS_ON1 WHERE Pname="ProductX’ The same query would require the specificatior of twc joins if specified on the base relations directly. one of the main advantages of 2 view is to simplify the specification of certain queries. Views are also used as a security and authorization mechanism, Aviewis supposed to be always up-to-date if we modify the tuples in the base tables on which the view is defined, the view must automatically reflect these changes. Hence, the view is not realizec or materialized at the time of view definition but rather at the time when we specify a query on the view Itis the responsibility of the DBMS and not the user to make sure that the view is kept up-to- date If we do not neec a view any more, we can use the DROP VIEW command to dispose of it. For example : DROP VIEW WORKS_ON1; 1.3.3 View Implementation, View Update and Inline Views ‘The problem of efficiently implementing a view for querying is complex Two main approaches have been suggested. = One strategy called query modification, involves modifying or transforming the view query (submitted by the user) into a query on the underlying base tables. For example, the query SELECT Fname Lname FROMWORKS_ON1 WHERE Pname='ProductX’ would be automatically modified to the following query by the DBMS: SELECT Fname Lname FROM EMPLOYEE, PROJECT, WORKS_ON WHERE Ssn=Essn AND Pno=Pnumber AND Pname="Productx’; ‘The disadvantage of this approach is that itis nefficient for views defined via complex queries that are time-consuming to execute, especially if multiple queries are going to be applied to the same view within a short period of time. + The second strategy, celled view materialization, involves physically creating a temporary view table when the view is first queried and keeping that table on the assumption thal other queries on the view will follow In this case, an efficient strategy for automatically updating the view table wher the dase tables are updated must be developed in order to keep the view up-to-date. Page 22 Database Management System Techniques using the concept of incremental update have been developed for this purpose, where the DBMS can determine what new tuples must be inserted, deleted, or modfied in @ materialized view table when a database update is applied to one of the defining base tables. The view's generally kept as a materialized (physically stored) table as long as itis being queried. if the view 's not queried for a certain period of time, the system may then automatically remove the physical table and recompute it from scratch when future queries reference the view. Updating of views is complicated and can be ambiguous. In general an update on a view defined on a single table without any aggregate functions can be mapped to an update on the underlying base table under certain conditions. For a view involving joins, an update operation may be mapped tc update operations on the underlying oase _rating,:c_age); Page 26 Database Management System The SQLSTATE variable should be checked for errors and exceptions after eact Embedded SQL statement,SQL provides the WHENEVER command to simplify this task: EXEC SQL WHENEVER [SQLERROR | NOT FOUND ] [CONTINUE|GOTO stmt] If SQLERROR is specified anc the value of SQLSTATE indicates an exception, control is transferrec to stmt which 's presumably responsible for error anc exception handling. Control is also transferred to stmt if NOT FOUND is specifiec and the value of SQLSTATE 's 02000, which denotes NO DATA. 2.2.2 Cursors ‘A maior problem in embedding SQL statements in a host language like C is thal an impedance mismatch occurs because SQL operates on sets of records, whereas languages like C do not cleanly support a set-of-records abstraction. The solution is to essentially provide a mechanism that allows us to retrieve rows one at a time from a relation- this mechanism is called a cursor We can declare a cursor on any relation or on any SQL query. Once a cursoris declared, we can = open t (positions the cursor just before the first row) = Fetch the next row "Move the cursor (to the next [Link] the row after the next n, to the first row or previous row etc by specifying additional parameters for the fetch command) "Close the cursor Cursor allows us to retrieve the rows in a table by positioning the cursor at a particular row anc reading its contents. Basic Cursor Definition and Usage Cursors enable us to examine, in the host language program, a collection of rows computed by an Embedded SQL statement: + We usually need to open a cursor if the embedded statement is @ SELECT. we car avoic opening a cursor if the answer contains a single row + INSERT, DELETE anc UPDATE statements require no cursor. some variants of DELETE and UPDATE use a cursor. Examples: i) \c the name and age of a sailor, specifiec by assigning a value to the host variable c_sid, declared earlier EXEC SQL SELECT [Link] INTO :c_sname, :¢_age FRON Sailaors WHERE [Link]='[Link]; Page 3¢ Database Management System The INTO clause allows us assign the columns of the single answer row to the host variable _sname and ¢_age. Therefore, we do not need a cursor to embed this query in a host language program. il) Compute the name and ages of all sailors with a rating greater than the current value of the host, variable ¢_minrating SELECT [Link],[Link] FROM sailors s WHERE [Link]>:c_minrating; The query retuns a collection of rows. The INTO clause is inadequate. The solution is to use a cursor, DECLARE sinfo CURSOR FOR SELECT [Link] FROM sailors s WHERE [Link]>:c_minrating; This code can be included n a C program and once itis executed, the cursor sinfc is defined. We can open the cursor by using the syntax OPENsinfo; ‘A cursor can be thought of as ‘pointing’ to a row in the collection of answers to the query associatec with it When the cursor is opened, itis positioned just before the first row. We can use the FETCH command to read the first row of cursor sinfointo host language variables FETCHsinfo INTO :c_sname, :¢_age; When the FETCH statement is executed, the cursor s positioned to point at the next row and the column values in the row are copied into the corresponding host variables. By repeatedly executing this FETCH statement, we can read ll the rows computed by the query, one row al time. When we are done with a cursor, we can close it CLOSE sinfo: il) To retrieve the name, address and salary of an employee specified by the variable ssn //erogram Segnent £1: 0) loop = 1+ 1) while (Loop) { 2) prompt(“Enter a Social Security Number: *, ssn) ; 3) EXEC so 4) SELECT Fname, Minit, Lname, Address, Salary 5) INTO sfnane, tminit, tlnane, raddress, :salary 6) FROM ENPLOYEE WHERE Sen = :esn ; 7) ££ (SQLCODE = = 0) print£(fname, minit, Iname, address, salary) 8) else print#(*secial Security Wunber does not exist: ", ssn) ; 9) prompt (*More Social Security Numbers (enter 1 for Yes, 0 for No): “, loop) + 10) > Page 31 Database Management System Properties of Cursors The general form of a cursor dectaration is DECLARE cursomame [INSENSITIVE] [SCROLL] CURSOR [WITH HOLD} FOR some query [ORDER BY order-itermist ] IFOR READ ONLY I FOR UPDATE ] ‘A cursor can be deciared to be a read-only cursor (FOR READ ONLY) or updatable cursor (FOR UPDATE),f it is updatable, simple variants of the UPDATE and DELETE commands allow us to update or delete the row on which the cursor is positioned. For example, if sinfo is an updatable cursor and open, we can execute the following statement UPDATE Sailors SET [Link] = [Link] -1 WHERE CURRENT of sinto; Acursoris updatable by default unless itis @ scrollable or insensitive cursor in which case it is read- only by default, If the keywore SCROLL is specified, the cursor is serollable, which means that variants of the FETCH command can be used to position the cursor in very flexible ways; otherwise, only the basic FETCH command, which retrieves the next row, is allowed If the keywore INSENSITIVE is specified, the cursor behaves as if it s ranging over a private copy of the collection of answer rows. Otherwise, and by default, other actions of some transaction could modify these rows, creating unpredictable behavior. ‘Aholdable cursor is specified using the WITH HOLD clause, and is not closed when the transactior is committed. Optional ORDER BY clause can be used to specify a sort order. The order-itermlist is a list of order- tems. An order-item is a column name, optionally followed by one of the keywords ASC or DESC Every column mentioned in the ORDER BY ciause must also appear in the selectist of the query associated with the cursor; othenwise i is not clear what columns we should sort or ORDER BY minage ASC, rating DESC ‘The answer s sorted firs! n ascending order by minage, and if several rows have the same minage value, these rows are sorted further in descending order by rating Page 32 Database Management System 8 255 3 255 7 350 Dynamic SQL. Dynamic SQL Allow construction of SQL statements on-the-fly. Consider an application such as a spreadsheet or a graphicd front-end that needs to access data from a DBMS. Such an application must accept commands from a user and, based on what the user needs, generate appropriate SQL statements to retrieve the necessary data. In such situations, we may not be able to predict in advance just what SQL statements need to be executed. SQL provides some faallities to deal with such situations; these are referred to as Dynamic SQL. Example: char ¢_salstringl] = (‘DELETE FROM Sailors WHERE rating>5"}, EXEC SQL PREPARE readytogo FROM :csqlstring; EXEC SQL EXECUTE readytogo: = The first statement declares the C variable ¢_sqlstring and initializes its value tc the string representation of an SQL command + The secon¢ statement results in this string being parsed and compiled as an SQL command, with the resulting executable bound to the SQL variable readytogo «The third statement executes the commanc 2.3 An Introduction to JDBC Embedded SQL enables the integration of SQL with a general-purpose orogramming language. A DBMS-specific preprocessor transforms the Embedded SQL statements inte function calls in the host language. The details of this translation vary across DBMSs. anc therefore even though the source code can be compiled to work with different DBMSs, the final executable works only with one specific DBMS. ODBC ane JDBC, short for Open DataBase Connectivity anc Java DataBase Connectivity, also enable the integration of SQL with a general-purpose programming language. + In contrast to Embedded SQL ODBC anc JDBC allow a single executable to access different DBMSs Without recompilation. Page 33 Database Management System = While Embedded SQL is DBMS-independent only al the source code level, applications using ODBC or JDBC are DBMS-independent at the source code leve ancat the levelof the executable * In addition, using ODBC or JDBC, an application can access not just one DBMS but several different ones simultaneously "ODBC and JDBC achieve portatility at the level of the executable by introducing an extra levelof indirection * All direct interaction with a specific DBMS happens through a DBMS-specific driver. ‘A driver is a software program that translates the ODBC or JDBC calls into DBMS-specific calls. Drivers are loaded dynamically on demand since the DBMSs the application is going to access are known only at run-time, Available drivers are registered with a driver manager a driver does ot necessarily need to interact with @ DBMS that understands SQL. It is sufficient that the driver translates the SQL commands from the application into equivalent commands that the DBMS understands, An application thal interacts with a data source through ODBC or JDBC selects a data source, dynamically loads the corresponding driver, and establishes a connection with the data source. There is no limit on the number of open connections. An application car nave several open connections to different data sources. =: ich connection nas transaction semantics. that is, changes from one connection aré visible to other connections only after the connection has committed its changes. While 2 connection is open, transactions are executed by submitting SQL statements, retrieving results, processing errors, and finally committing or rolling back. The application disconnects from the data source to terminate the interaction: 2.3.1 Architecture The architecture of JDBC has four main components * Application * Driver manager + Drivers = Data sources Page34 Database Management System Application * initiates and terminates the connection with a data source ® sets transaction boundaries, submits SCL statements and retrieves the results Driver manager * Load JDBC drivers and pass JDBC function calls from the application to the correct driver = Handles JDBC initialization and information calls from the applications anc can log all function calls * Performs some rudimentary error checking Drivers * Establishes the connection with the data source * Submits requests and returns request results * Translates data, error formats, and error codes from a form that is specific to the data source into the JDBC standarc Data sources = Processes commands from the driver and retums the results Drivers in JDBC are classifiec into four types depending on the architectural relationship between the application and the data source: Type | Bridges * This type of driver transiates JDBC function calls into function calls of another API that is not native to the DBMS. = An example is a JOBC-ODBC bridge; an application car use JDBC calls to access an ODBC compliant data source. The application loads only one driver, the bridge. = Advantage: * itis easy to piggyback the application onto an existing installation, anc no new drivers have to be installed. * Drawbacks: + The increased number of layers between data source anc application affects performance + the useris limited to the functionality that the ODBC driver supports Page 35, Database Management System Type Il Direct Translation to the Native API via Non-Java Driver: * This type of driver translates JDBC function calls directly inte methoc invocations of the API of one specific data source. * The driveris usually .written using a combination of C++ and Java it's dynamically linked and specific to the data source. * Advantage «This architecture performs significantly better than a JDBC-ODBC bridge. * Disadvantage + The database driver thal implements the AFI needs to be installed on each computer that runs the application. Type Ilk~Network Bridges = The driver talks over a network to ¢ middleware server that translates the JDBC requests into DBMS-specific methoc invocations. * In this case, the driver on the client site is not DBMS-specific. = The JDBC driver loaded by the application can be quite small, as the only functionality it needs to implement is sending of SQL statements to the middleware server. * The middleware server can then use 2 Type Il JDBC driver to connect to the data source. Type IV-Direct Translation to the Native API via Java Driver. * Instead of calling the DAMS API directly, the driver communicates with the OBMS through Java sockets + In this case, the driver on the client side 's written in Java. but it is DBMS-specific translates JDBC calls into the native API of the database system. "This solution does not require an intermediate layer and since the implementatior is all Java its performance is usually quite good. 2.4 JDBC CLASSES AND INTERFACES JDBC is @ collection of Java classes anc interfaces that enables database acoass from programs written in the Java language. it contains methods for connecting to @ remote data source, executing SQL statements, examining sets of results from SQL statements, transaction management and exception handling. The classes and interfaces are part of the [Link] package. JDBC 2.0 alsc includes the [Link] package, the JDBC Optional Package. The package [Link] adds, among other things, the capability of connection poding and the Row-Set interface. Page 36

You might also like