Database Management and Optimization
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
