Download Modern SQL-Database System Concepts-Lecture 06 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Lecture 6 of 42
Wednesday, 06 September 2006
William H. Hsu Department of Computing and Information Sciences, KSU
KSOL course page: http://snipurl.com/va Course web site: http://www.kddresearch.org/Courses/Fall-2006/CIS Instructor home page: http://www.cis.ksu.edu/~bhsu
Reading for Next Class: Sections 4.1 – 4.2, p. 121 – 132, Silberschatz et al. , 5th^ edition Sections 4.3 – 4.5, p. 133 – 145, Silberschatz et al. , 5th^ edition
Modern SQL: Cursors and Views
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
z Query 2 ∏ customer_name, branch_name ( depositor account ) ÷ρ temp(branch_name ) ({( “Downtown” ) , ( “Uptown” )}) Note that Query 2 uses a constant relation.
Bank Example Queries:
Review
Bank Example Queries:
Review
z Find all customers who have an account from at least the “Downtown” and the Uptown” branches. z Query 1
∏ customer_name (σ branch_name = “Downtown ” ( depositor account )) ∩
∏ customer_name (σ branch_name = “Uptown” ( depositor account ))
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Deletion: Review
Deletion: Review z A delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the database. z Can delete only whole tuples; cannot delete values on only particular attributes z A deletion is expressed in relational algebra by: r ← r – E where r is a relation and E is a relational algebra query.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Deletion Examples: Review
Deletion Examples: Review z Delete all account records in the Perryridge branch.
Delete all accounts at branches located in Needham.
r 1 ← σ (^) branch_city = “Needham” ( account branch ) r 2 ← ∏ branch_name, account_number, balance ( r 1 ) r 3 ← ∏ (^) customer_name, account_number ( r 2 depositor) account ← account – r 2 depositor ← depositor – r 3
Delete all loan records with amount in the range of 0 to 50
loan ← loan – σ (^) amount ≥ 0 and amount ≤ 50 ( loan )
account ← account – σ (^) branch_name = “Perryridge” ( account )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Updating: Review
Updating: Review z A mechanism to change a value in a tuple without charging all values in the tuple z Use the generalized projection operator to do this task
z Each Fi is either the I th^ attribute of r , if the I th^ attribute is not updated, or, if the attribute is to be updated F i is an expression, involving only constants and the attributes of r , which gives the new value for the attribute
r ←∏ F 1 , F 2 ,Κ, Fl ,( r )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Update Examples: Review
Update Examples: Review
z Make interest payments by increasing all balances by 5 percent.
Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent
account ← ∏ (^) account_number , branch_name , balance * 1.06 (σ (^) BAL > 10000 ( account )) ∪ ∏ (^) account_number , branch_name , balance * 1.05 (σ BAL ≤ 10000 ( account ))
account ← ∏ (^) account_number , branch_name , balance * 1.05 ( account )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Create Table with Integrity Constraints: Review
Create Table with Integrity Constraints: Review
z not null z primary key ( A 1 , ..., An )
Example: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative. create table branch ( branch_name char(15) , branch_city char(30), assets integer, primary key ( branch_name ))
primary key declaration on an attribute automatically ensures not null in SQL-92 onwards, needs to be explicitly stated in SQL-
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Drop and Alter Table Constructs: Review
Drop and Alter Table Constructs: Review
z The drop table command deletes all information about the dropped relation from the database. z The alter table command is used to add attributes to an existing relation: alter table r add A D where A is the name of the attribute to be added to relation r and D is the domain of A. All tuples in the relation are assigned null as the value for the new attribute. z The alter table command can also be used to drop attributes of a relation: alter table r drop A where A is the name of an attribute of relation r Dropping of attributes not supported by many databases
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Example Query Example Query
z Find all customers who have at least two accounts at the Perryridge branch. select distinct T.customer_name from depositor as T where not unique ( select R.customer_name from account, depositor as R where T.customer_name = R.customer_name and R.account_number = account.account_number and account.branch_name = ‘Perryridge’)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Derived RelationsDerived Relations
z SQL allows a subquery expression to be used in the from clause z Find the average account balance of those branches where the average account balance is greater than $1200. select branch_name, avg_balance from ( select branch_name, avg ( balance ) from account group by branch_name ) as branch_avg ( branch_name, avg_balance ) where avg_balance > 1200 Note that we do not need to use the having clause, since we compute the temporary (view) relation branch_avg in the from clause, and the attributes of branch_avg can be used directly in the where clause.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
With Clause With Clause
z The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs. z Find all accounts with the maximum balance
with max_balance ( value ) as select max ( balance ) from account select account_number from account, max_balance where account.balance = max_balance.value
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Complex Query using With Clause Complex Query using With Clause
z Find all branches where the total account deposit is greater than the average of the total account deposits at all branches. with branch_total ( branch _ name , value ) as select branch _ name , sum ( balance ) from account group by branch _ name with branch _ total _ avg ( value ) as select avg ( value ) from branch _ total select branch _ name from branch _ total , branch _ total_avg where branch_total.value >= branch_total_avg.value
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Modification of the Database – Insertion [1] Modification of the Database – Insertion [1]
z Add a new tuple to account insert into account values (‘A-9732’, ‘Perryridge’,1200)
or equivalently
insert into account ( branch_name, balance, account_number ) values (‘Perryridge’, 1200, ‘A-9732’)
z Add a new tuple to account with balance set to null insert into account values (‘A-777’,‘Perryridge’, null )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Modification of the Database – Insertion [2]Modification of the Database – Insertion [2]
z Provide as a gift for all loan customers of the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account insert into account select loan_number, branch_name, 200 from loan where branch_name = ‘Perryridge’ insert into depositor select customer_name, loan_number from loan, borrower where branch_name = ‘ Perryridge’ and loan.account_number = borrower.account_number z The select from where statement is evaluated fully before any of its results are inserted into the relation (otherwise queries like insert into table 1 select * from table 1 would cause problems)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Modification of the Database – Updates
Modification of the Database – Updates
z Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. Write two update statements: update account set balance = balance ∗ 1. where balance > 10000
update account set balance = balance ∗ 1. where balance ≤ 10000 The order is important Can be done better using the case statement (next slide)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Case Statement for Conditional UpdatesCase Statement for Conditional Updates
z Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%.
update account set balance = case when balance <= 10000 then balance *1. else balance * 1. end
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
View Definition View Definition
z A view is defined using the create view statement which has the form create view v as < query expression > where is any legal SQL expression. The view name is represented by v. z Once a view is defined, the view name can be used to refer to the virtual relation that the view generates. z View definition is not the same as creating a new relation by evaluating the query expression Rather, a view definition causes the saving of an expression; the expression is substituted into queries using the view.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Example Queries Example Queries
z A view consisting of branches and their customers
Find all customers of the Perryridge branch
create view all_customer as ( select branch_name, customer_name from depositor, account where depositor.account_number = account.account_number ) union ( select branch_name, customer_name from borrower, loan where borrower.loan_number = loan.loan_number )
select customer_name from all_customer where branch_name = ‘Perryridge’
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Views Defined Using Other ViewsViews Defined Using Other Views
z One view may be used in the expression defining another view z A view relation v 1 is said to depend directly on a view relation v 2 if v 2 is used in the expression defining v 1 z A view relation v 1 is said to depend on view relation v 2 if either
v 1 depends directly to v 2 or there is a path of dependencies
from v 1 to v 2
z A view relation v is said to be recursive if it depends on itself.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
View ExpansionView Expansion
z A way to define the meaning of views defined in terms of other views. z Let view v 1 be defined by an expression e 1 that may itself contain uses of view relations. z View expansion of an expression repeats the following replacement step: repeat Find any view relation v (^) i in e 1 Replace the view relation v (^) i by the expression defining v (^) i until no more view relations are present in e 1 z As long as the view definitions are not recursive, this loop will terminate
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
AssertionsAssertions
z An assertion is a predicate expressing a condition that we wish the database always to satisfy. z An assertion in SQL takes the form create assertion check z When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion This testing may introduce a significant amount of overhead; hence assertions should be used with great care. z Asserting for all X , P ( X ) is achieved in a round-about fashion using not exists X such that not P ( X )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Assertion Example Assertion Example
z Every loan has at least one borrower who maintains an account with a minimum balance or $1000. create assertion balance_constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and depositor.account_number = account.account_number and account.balance >= 1000)))
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Assertion Example Assertion Example
z The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. create assertion sum_constraint check ( not exists ( select * from branch where ( select sum ( amount ) from loan where loan.branch_name = branch.branch_name ) >= ( select sum ( amount ) from account where loan.branch_name = branch.branch_name )))
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Authorization Authorization
Forms of authorization on parts of the database:
z Read - allows reading, but not modification of data. z Insert - allows insertion of new data, but not modification of existing data. z Update - allows modification, but not deletion of data. z Delete - allows deletion of data.
Forms of authorization to modify the database schema (covered in Chapter 8): z Index - allows creation and deletion of indices. z Resources - allows creation of new relations. z Alteration - allows addition or deletion of attributes in a relation. z Drop - allows deletion of relations.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Revoking Authorization in SQL Revoking Authorization in SQL
z The revoke statement is used to revoke authorization. revoke on from z Example: revoke select on branch from U 1 , U 2 , U 3 z may be all to revoke all privileges the revokee may hold. z If includes public, all users lose the privilege except those granted it explicitly. z If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. z All privileges that depend on the privilege being revoked are also revoked.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Embedded SQL Embedded SQL
z The SQL standard defines embeddings of SQL in a variety of programming languages such as C, Java, and Cobol. z A language to which SQL queries are embedded is referred to as a host language , and the SQL structures permitted in the host language comprise embedded SQL. z The basic form of these languages follows that of the System R embedding of SQL into PL/I. z EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL END_EXEC Note: this varies by language (for example, the Java embedding uses
SQL { …. }; )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Example Query Example Query
z Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select customer_name, customer_city from depositor, customer, account where depositor.customer_name = customer.customer_name and depositor account_number = account.account_number and account.balance > :amount END_EXEC
From within a host language, find the names and cities of customers with more than the variable amount dollars in some account.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 06 Sep 2006 Kansas State University
Embedded SQL (Cont.)Embedded SQL (Cont.)
z The open statement causes the query to be evaluated EXEC SQL open c END_EXEC z The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into : cn, :cc END_EXEC Repeated calls to fetch get successive tuples in the query result z A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to ‘02000’ to indicate no more data is available z The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL close c END_EXEC Note: above details vary with language. For example, the Java embedding defines Java iterators to step through result tuples.
