SQL1 1

schema: 4 control processing/storage efficiency: *choice of indexes * file organizations 4 base tables& indexes  *data clustering // creating indexes *speed up random/sequential access 2 base table data.

select statement: select from where groupby having orderby

views: * provide users controlled access 2 tables * base table–table containing the raw data // dynamic view  a “virtual table” created upon request by a user * no data actually stored; instead data from base table made available 2 user // materialized view *copy or replication of data  * data actually stored *must be refreshed periodically 2 match corresponding base tables

advantages of views: *simplify query commands *assist with data security * enhance programming productivity *contain most current base table data *use little storage space* provide customized view 4 user //disadvantages of views: use processing time each time view is referenced * may or may not be directly updateable

subquery: placing an inner query (select statement) inside an outer query *noncorrelated subqueries: do not depend on data from the outer query *execute 1ce 4 the entire outer query // correlated subqueries: make use of data from the outer query *execute 1ce 4 each row of the outer query *can use exists operator // developing queries: * be familiar with the data model (entities & relationships) * understand the desired results * know the attributes desired in results * identify the entities that contain desired attributes * consider the effect on unusual data

query efficiency : * instead of select – identify the specific attributes in the select clause * limit the number of subqueries * if data is 2 be used many times, make a separate query & store it as a view

4 btr query design: * understand how indexes R used in query processing * keep optimizer statistics up-2-date * use compatible data types 4 fields * write simple queries * break complex queries into multiple simple parts * create temporary tables 4 groups of queries * combine update operations * retrieve only the data U need

ensuring transaction integrity: * transaction = a discrete unit of work that must be completely processed or not processed @ all * may involve multiple updates  if any update fails, then all other updates must be cancelled *BEGIN TRANSACTION/END TRANSACTION – Marks boundaries of a transaction // COMMIT – Makes all updates permanent // ROLLBACK – Cancels updates since the last COMMIT 

data dictionary facilities: * system tables that store metadata * users usually can view some of these tables * users R restricted from updating them

routines: program modules that execute on-demand // functions: routines that return values & take input parameters // procedures: routines that do not return values & can take input or output parameters // triggers–routines that execute in response 2 a database event (insert, update, or delete)

embedded sql hard-coded sql statements in a program written in another language such as c or java * dynamic sql ability 4 an application program 2 generate sql code on the fly, as the application is running // reasons 2 embed sql in 3gl * can create a more flexible interface 4 the user * possible performance improvement * database security improvement

client/server architectures * ne2rked computing model * processes distributed between clients & servers * client–workstation (usually a pc) that requests & uses a service * server–computer (pc/mini/mainframe) that provides a service * 4 dbms, server is a database server

application partitioning *placing portions of the application code in different locations (client vs. server) after it is written *advantages – improved performance improved interoperability * balanced workloads

2-tier database server architectures * client workstation is responsible 4 *presentation logic *data processing logic business rules logic * server performs all data storage, access, & processing  typically called a database server // characteristics of 2-tier client/server systems * departmental in scope (few users) * not mission-critical * low transaction volumes

middleware – software that allows an application 2 interoperate with other software without requiring user 2 understand & code low-level operations // API – routines that an application uses 2 direct the performance of procedures by the computer’s operating system 

steps 4 using databases via middleware apis 1. identify & register a database driver. 2. open a connection 2 a database. 3. execute a query against the database. 4. process the results of the query. 5. repeat steps 3–4 as necessary. 6. close the connection 2 the database.

thin client * an application where the client accessing application primarily provides the user interfaces & some application processing with no or limited local data storage.

web application comp1nts; database server – hosts the dbms // web server – receives & responds 2 browser requests using http protocol  // application server – software building blocks 4 creating dynamic web sites // web browser – client program that sends web requests & receives web pages 

processing in 3-tier applications * static page requests  .htm or .html requests handled by the web server * dynamic page requests requests R routed 2 the application server & returned as html considerations in 3-tier application: stored procedures  code logic embedded in dbms  improve performance * transactions  involve many database updates either all must succeed, or n1 should occur * database connections  maintaining an open connection is resource-intensive  use of connection pooling

benefits of stored procedures * performance improves 4 compiled sql statements * reduced ne2rk traffic * improved security * improved data integrity * thinner clients // benefits of 3-tier architectures * scalability * technological flexibility * long-term cost reduction * btr match of systems 2 business needs * improved customer service * competitive advantage * reduced risk

extensible markup language (xml) * a txt-based markup language uses elements, tags, attributes includes document type declarations * revolutionizes the way data R exchanged over the internet // storing xml documents *4 options:  store xml data in a relational database by shredding the xml document *store entire xml document in a large field *store the xml document using special xml columns *store the xml document using a native xml database (non-relational)

retrieving xml documents * xpath – locating data in xml documents * xquery – an xml transformation language that allows applications 2 query both relational databases & xml data

displaying xml data * extensible stylesheet language transformation (xslt) – a language used 2 transform complex xml documents & also used 2 create html pages from xml documents

web services – xml-based standards that define protocols 4 communication between software programs over the web  // UDDI – standard 4 creating & distributing web services // WSDL – XML based grammar 4 describing a web service & specifying its public interface // SOAP – xml-based communication protocol 4 sending messages between applications over the internet// Service oriented architecture (SOA) * a collection of services that communicate with each other by passing data or coordinating a business activity * highly interoperable comp1nts * leads 2 flexibility & shorter development time