Reference: Riccardi, G, Database Management with Web Site Development Applications , Addison-Wesley, 2003

1. Reference: Riccardi, G, Database Management with Web Site Development Applications, Addison-Wesley, 2003

2. Advanced Issues in Web Site Design and Implementation • Forms Checking with Client-Side JavaScript • Error Handling in Server-Side JavaScript • Transactions and Transaction Management • Backup and Recovery from Failures • Security in Information Systems • Stored Procedures and Functions

3. Forms Checking with Client-Side JavaScript • Want to give users immediate feedback on simple errors and omissions on their forms • Client-side JavaScript provides this capability • Go to the new user creation page to see forms checking in action • View page source to see the JavaScript embedded in the HTML

4. Validating Form Data • Function validateCustomer in file client.js • Check all fields of the new customer form for simple errors • Click on “New Account” without filling if fields to see warning • Install function as value of the onsubmit attribute • <form onsubmit=“return validateCustomer()“ name=“customer” … > • Access to form fields through document object and form object document.customer • document.customer.accountId.value • When user clicks the submit button: • Function is called • If function returns false, warning posted, no submission • If function returns true (or fails to return at all!) form submitted • Check for valid email using match method of String variable email • email.match(/.+@.+/ ) means at sign (@) with at least one character (.+) before and after

5. Calling the Validation Function During Form Submission • We need to force the function to be included in HTML document and not consumed by the ASP processor • Script included in a script tag instead of <% %> • <script type="text/javascript" language="javascript" > // function definitions</script> • The script tag tells ASP to include the code in the output HTML document • Some help available for client-side debugging • Netscape Navigator has JavaScript Console • Internet Explorer has Microsoft Script Debugger

6. Error Handling in Server-Side JavaScript • What happens if user types incorrect SQL in the sql form Web page • conn.Execute will detect the error and raise an exception • Program that calls conn.Execute must catch the exception • Exceptions in JavaScript managed by try-catch statement • Code from sqlExec.asp • try{// we may get an error in executing the query results = executeSQL(sqlQuery,numRowsAffected ); printTable(results,Response); }catch (exception) { Response.write("<table><center>\n"); Response.write("<caption>Unable to process query</caption>\n"); Response.write("<tr><th>Error returned from database</th><td>"); Response.write(exception.description+"<br>\n"); Response.write(numRowsAffected+"<br>\n"); Response.write("</tr></table></center>\n"); } • Click the Submit Query button with no SQL text to see the result

7. Transactions and Transaction Management • Concurrency is when multiple users are interacting with a single database server at the same time • These interactions may interfere with each other • A transaction is a logical unit of database activity that consists of a single user (or application) executing one or more SQL statements • The transaction manager is code within the database server that enforces certain rules of behavior for transactions • The transaction manager may deny access to a transaction that does not follow the rules. • A transaction can end in one of two ways • Commit operation ends the transaction and makes all changes permanent • Rollback operation ends the transaction and removes all changes • Concurrency control is the process of managing the interactions of concurrent transactions .

8. Example of Concurrent User Interference in BigHit Online • Example of concurrency problem from Table 15.1 • Two customers trying to create Sale entities concurrently • Both try to use the same salesId value • One of the insert statements fails because of duplicate key • Additional examples in Table 15.2 on page 388

9. General Theory of Database Transactions • Database theory and practice have identified four crucial properties of transactions: the ACID properties. • Atomicity: All of the updates of a transaction are successful, or no update takes place. • Consistency: Each transaction should leave the database in a consistent state. Properties such as referential integrity (foreign key consistency) must be preserved. • Isolation: Each transaction, when executed concurrently with other transactions, should have the same effect as if it had been executed by itself. • Durability: Once a transaction has completed successfully, its changes to the database should be permanent. Even serious failures should not affect the permanence of a transaction. • The transaction manager is responsible for enforcing the ACID properties • When a transaction issues a request that will violate one of the ACID properties, the transaction manager will • Block the request and • Issue a rollback operation on the transaction

10. Transaction Management in ASP with SQL Server • The ASP Connection object has transaction control methods • BeginTrans: create a new transaction • CommitTrans: end the current transaction and commit changes • RollbackTrans: end the current transaction and remove all changes • Default transaction mode is explicit commit • Each SQL statement is executed as a single transaction • If the application does not call BeginTrans before calling Execute, the SQL statement will execute as a transaction • Full understanding of this material requires careful reading of the text

11. Backup and Recovery from Failures • Goal of recovery is to • Respond to an error, • Restore the database to a state that is known to be correct, and • Put the database back in service as quickly as possible • Sources of errors • The database server computer crashes • The database server program crashes • A database client computer crashes • A client program crashes • The network connection between client and server fails • A transaction executes a rollback operation • A transaction executes an illegal operation • One or more transactions introduce errors into the database • Data on a disk drive is corrupted • Response to errors depends on source and severity

12. Backups and Checkpoints • A backup is a copy of the contents of a database at a specific time. • Contains sufficient information to allow restoration. • A database that has been restored from a backup has the contents that it had when the backup was created. • A checkpoint is an operation that forces the database on the disk to be in a state that is consistent with all of the committed transactions. • It includes flushing the contents of disk caches so that the disk is up to date. • Checkpoints are needed because database systems try to keep files in memory and only update the disk when necessary. • The contents of a file on the disk may be inconsistent because changes made to the in-memory copy of the file are not automatically applied to the disk copy.

13. Transaction Logs • Recovery from a complete failure can be accomplished by restoring the most recent backup and then repeating changes of the committed transactions. • Need the backup plus a record of committed transactions • A transaction log is a file that records the actions of all transactions as they occur • An entry in a transaction log consists of the following items: • The unique transaction ID that is automatically assigned to a transaction when it starts execution • The name of the action performed (e.g. read, write, commit, rollback) • The object that is referenced by the action, if any • The value of the object before the action • The value of the object after the action • Thus, the log contains sufficient information to repeat the changes of committed transactions

14. Security in Information Systems • Security requires enforcing rules of user interaction • For example, BigHit online may want • Each user must be registered as a customer. • A customer is allowed to fetch information about his purchases. • A customer is allowed to modify the database through the Web applications. • A customer is not allowed to fetch information about other customers. • A customer is not allowed to modify the database except through the Web applications. • A customer is not allowed to see information about inventory or employees • Database servers allow database designers to • Identify users • Specify security rules • Enforce security rules

15. Security in Database Management Systems • We want to create reliable and secure systems • Forms validation, as in Section 15.1, helps minimize user errors • Error handling, as in Section 15.2, helps minimize program errors • Transaction management, as in Section 15.3, helps minimize concurrency errors • Security and access control restricts what applications can do • Each application logs in as a user • Each user has specific privileges to read and write database objects • Database server has support for three types of security • Account security for the validation of users • Access security for protection of database objects • Operating system security for database and file protections

16. User Authorization for Database Servers • SQL supports creation of users and assignment of attributes • createuser Jane identifiedby crockette; //userid and password • createuser Dick identifiedby go-man-go; • alteruser Jane quotaunlimitedon USERS;// no size restrictions • dropuser Jane;// delete user • alteruser Dick accountlock;// keep user but block login • alteruser Dick identifiedby stop-please;// change password • SQL Server does not support these user operations • All user creation must be done through the Enterprise Manager or through direct manipulation • Not standard SQL

17. Protection of Database Objects • SQL databases define a collection of privileges that may be granted to users • Rread, update, append, create, and drop access to databases, schemas, tables, views and other objects. • SQL grant and revoke statements assign privileges to users • grantinserton Customer to Jane; • grantselecton Customer topublic; • grantallon Employee to Jane; • revokedeleteon Employee from Jane; • grantupdateon Customer(street, city, state, zip) to Jane; • createrole Clerk notidentified; • grantallon Sale, SaleItem to Clerk; • By default • Database administrators have full rights on all objects • The owner of an object (e.g. a table) has full rights • No other user has any right to any access to an object unless explicitly granted

18. Stored Procedures and Functions • A stored procedure or function is a block of code that is • Added to the server • Given a name • Can be called by applications • Can have privileges not granted to users • Example of definition of derived attribute • createfunction numberRented (@accId int)return intasselect count(*) from Rental where Customer.accountId = @accId;

19. Creating Sales with a Stored Function • Define function that makes a new Sale entity with a unique salesId • createfunction newSale (@accountId varchar) returns intasbegindeclare @newId int; set @newId = (select max(salesId)+1 from Sale);insertinto Sale (salesId, accountId) values (@newId, @accountId); return @newId;end newSale; • newSale executes as a transaction • Grant newSale access to the Sale table and clerk access to newSale • grantexecuteon newSale to clerk; • grant inserton Sale to newSale; • revokeinserton Sale to clerk; • Call newSale from JavaScript • var salesRecordset = conn.Execute(“newSale”); • salesRecordset will have one row with one column containing the salesId value of the new sale